Need to place the directory list onto each cell on excel-CodePudding

I've been trying to grab a list of directory names and place it in each cell. I'm trying my best to learn nested loops in the process. Every time I try it just place the last directory name in each cell. I've been trying multiple variations to see if anything fixes, but I get the same output.

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
import os

path = 'C:\\Users\\jstju\\OneDrive\\Documents\\Test\\_test_list.xlsx'
wb = Workbook()
ws = wb.active
path2 = "C:\\Users\\jstju\\OneDrive\\Documents\\Test\\Script"


def columns():
    column_A = get_column_letter(1)
    column_B = get_column_letter(2)
    column_C = get_column_letter(3)
    column_D = get_column_letter(4)
    column_E = get_column_letter(5)

    cols2 = 15
    os.chdir(path2)
    dir_list = os.listdir(path2)

    for col in range(2, 16):

        cols_A = column_A   str(col)
        ws[cols_A] = col

        cols_B = column_B   str(col)
        ws[cols_B] = cols2   col

        cols_C = column_C   str(col)
        ws[cols_C] = f"=CONCATENATE(A{col},B{col})"

        cols_D = column_D   str(col)
        ws[cols_D] = '"'

        for i in dir_list:
            char = ""
            col_E = column_E   str(col)
            ws[col_E] = char   i


columns()
wb.save(path)

CodePudding user response：

If I understand, you want to take a list of names in a directory and place one in each cell in a column of an Excel worksheet in a newly created workbook.

You can do that this way:

from openpyxl import Workbook
import os

wb = Workbook()
ws = wb.active

path2 = "./Sampledir"
cwd = os.getcwd()
os.chdir(path2)
dir_list = os.listdir('.')
for x in dir_list:
    ws.append([x])

os.chdir(cwd)
path = './_test_list.xlsx'
wb.save(path)

In my test case, ./Sampledir looks like this:

C:\python>dir .\Sampledir
 Volume in drive C is OS
 Volume Serial Number is 4EDD-194A

 Directory of C:\python\Sampledir

05/28/2022  05:26 PM    <DIR>          .
05/28/2022  05:26 PM    <DIR>          ..
05/28/2022  05:25 PM                 0 sample1.txt
05/28/2022  05:25 PM                 0 sample2.txt
               2 File(s)              0 bytes
               2 Dir(s)  243,106,267,136 bytes free

C:\python>

The output spreadsheet looks like this:

UPDATE:

To add the directory contents starting at an arbitrary row and column, you can replace the for loop above with this:

topRow, leftColumn = 2, 3
for i, x in enumerate(dir_list):
    ws.cell(row=i   topRow, column=leftColumn, value=x)