I am trying to format my output excel file columns C to F as Percentage on every sheet but I think I am using the wrong code.

The code I'm using now without trying to convert it to percentage:

wb = openpyxl.Workbook()

del wb[wb.sheetnames[0]]

for txt_file in pathlib.Path(files).glob('*.txt'):
    with open(txt_file) as data:
        ws = wb.create_sheet(title=os.path.basename(txt_file.stem))
        for row in csv.reader(data, delimiter='\t'):
            ws.append(row)
                   
wb.save(output)

I have tried to add this code before ws.append(row)

ws.cell['C:F'].number_format = '0.00%' 

but it return error: TypeError: 'method' object is not subscriptable.

The example of text file I am currently using:

Active Monitor Availability
Device: server, Date Range: Today
Device  Monitor Up  Maintenance Unknown Down
EMAIL   Ping    1   0   0   0
WEBSITE HTTP    1   0   0   0
SYSTEM  Ping    0.9985145767349999  0   0   0.001485423264

Thanks Everyone!

CodePudding user response：

There are 2 things that you need to take care of converting the data into percentage.

1. The csv.reader() and ws.append() moves the data to the excel file, but all data is in text format. So, you need to identify the range where the numerical data is present and convert that to numbers.
2. Use the number_format to set the format to percentage.

The updated code is here... note that I tested this with just one file, so I am hoping that your code is taking care of multiple files within the for loop.

wb = openpyxl.Workbook()
del wb[wb.sheetnames[0]]

for txt_file in pathlib.Path('./').glob('log.txt'): #Just picked one specific file in local directory as no data provided on file counts, etc.
    with open(txt_file) as data:
        ws = wb.create_sheet(title=os.path.basename(txt_file.stem))
        for row in csv.reader(data, delimiter='\t'):
            ws.append(row)

    for row in ws.iter_rows(min_row = 4, max_row=ws.max_row, min_col=3, max_col=6): #Edit the range you want to modify, used the range provided in sample data 
        for cell in row:
            cell.number_format = '0.00%'
            if cell.value != "":
                cell.value = float(cell.value)
    wb.save('log.xlsx')
## End FOR - This will run just once, you will need to change the files names as required

Output excel