CODEWITHSUNDEEP
pythonopenpyxl
Srini San
Srini San

Reputation: 11

How to format columns in excel?

I would like to ask the community for help on formatting a column in Excel using OpenpyXL 3.0.3.

One of my program creates a CSV file and I want to convert to XL with all the formatting and save it as XL. My output files can have more than 500K Rows and 10 to 12 columns

My Sample csv Data:

trans_key,sqnc_nbr
201712127590004004159,10
201712112310000101519,23
201712127590000209181,10
201712190250000708589,34

I don't know how to format column B to number

with open('/home/Pythonscripts/testcsv.csv', 'r') as file:
reader = csv.reader(file)
data = list(reader)

for i in data:
ws.append(i)  

wb.save("/home/Pythonscripts/csvtoxl.xlsx")  

from openpyxl import load_workbook
  wb = load_workbook( '/home/Pythonscripts/csvtoxl.xlsx' )
  ws = wb[ wb.sheetnames[0] ]
  cell11 = ws.cell(2, 1)
  cell11.number_format = '0'
wb.save( '/home/talendsvcacc/eJob/Rewards/Pythonscripts/csvtoxl_sample.xlsx' )
wb.close()

Upvotes: 1

Views: 76

Answers (1)

Dror Av.
Dror Av.

Reputation: 1214

If that is the only issue just iterate over the cells in the column setting them to the correct format.

for col_cells in worksheet.iter_cols(min_col=2, max_col=2):
    for cell in col_cells:
        cell.number_format = '#,##0'

Upvotes: 1

Related Questions