Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to achieve the most appropriate column width of Excel by Python". The editor shows you the operation process through an actual case. The method of operation is simple and fast, and it is practical. I hope that this article "how to achieve the most appropriate column width of Excel in Python" can help you solve the problem.

The most suitable column width of Excel (openpyxl)

Python's Pandas module is a sharp tool to deal with Excel, especially the processing and preservation of Excel is very convenient, but only want to export Excel automatically adjust column width or row height, really can not do ah, especially after processing also need to adjust the column width, very inconvenient.

So the openpyxl module must help.

This is the simplest implementation from openpyxl import load_workbookwb = load_workbook ('test.xlsx') ws = wb [wb.sheetnames [0]] # Open the first sheetws.column_dimensions [' A']. Width = 20.0 # adjust column A wide ws.row _ dimensions [1] .height = 40 # adjust row 1 height ws.save ('test.xlsx')

But what if you let any Excel adjust the column width for every sheet? I wrote a function like this.

Get the maximum value of each column for each sheet and set it to the column width

From openpyxl import load_workbookfrom openpyxl.utils import get_column_letter def reset_col (filename): wb=load_workbook (filename) for sheet in wb.sheetnames: ws=wb [sheet] df=pd.read_excel (filename Sheet) .fillna ('-') Df.locl [len (df)] = list (df.columns) # append the title line to the last line for col in df.columns: index=list (df.columns) .index (col) # column sequence number letter=get_column_letter (index+1) # column letter collen= DF [col] .apply (lambda x:len (str (x). Encode ()). Max () # gets the maximum length of this column when However, you can also use min to get the minimum value mean to get the average ws.column_ dimensions.width = collen*1.2+4 # that is, the column width is the maximum length * 1.2 you can adjust the wb.save (filename) reset_col ('test.xlsx') yourself.

Note that Openpyxl only supports the latest .xlsx format, if the execution reports an error like this:

File "C:\ Users\ Administrator\ AppData\ Local\ Programs\ Python\ Python38\ lib\ site-packages\ openpyxl\ reader\ excel.py", line 94, in _ validate_archive raise InvalidFileException (msg) openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert

The file format is old. Please open the Excel file and choose to save as .xlsx format.

Some methods of Python writing Excel column width and row height use the third-party library xlsxwriter, and the result file format is xlsx.

The code is as follows:

Import xlsxwriter# creates a new workbook and adds a worksheet. Workbook = xlsxwriter.Workbook (r'\ Mac\ Home\ Desktop\ test11.xlsx') worksheet = workbook.add_worksheet ('test') # format the first column worksheet.set_column (30) # set column A width 30worksheet.set_column (' BGV B' 20) # set the width of column B, 2cm cell_format = workbook.add_format ({'bold': True}) worksheet.set_row (3,20) # set the height of the fourth row to 2cm worksheet.set_row (3,20)

The screenshot of the implementation result is as follows:

Use the third-party library xlwt, and the result file format is xls

The code is as follows:

Import xlwtbook = xlwt.Workbook (r'\ Mac\ Home\ Desktop\ test22.xls') sheet = book.add_sheet ('sheet1') first_col=sheet.col (0) sec_col=sheet.col (1) first_col.width=256*20 # width tall_style = xlwt.easyxf (' font:height 720) ') # 36pt, font size first_row = sheet.row (0) first_row.set_style (tall_style) book.save (r'\\ Mac\ Home\ Desktop\ test22.xls')

Parameter explanation:

The value of the column width in the xlwt is represented by the default font 0, which is measured in a unit of 1, 256. Xlwt is created with a default width of 2960, which is 11 characters 0, so we can use the following method when setting column width: width = 256x 20256 as the unit of measure, 20 represents 20 character width''

The screenshot of the implementation result is as follows:

This is the end of the content about "how to achieve the most appropriate column width of Excel by Python". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.