Python如何實現Excel的最合適列寬(openpyxl)

2022-02-12 19:00:38

Excel的最合適列寬(openpyxl)

Python的Pandas模組是處理Excel的利器，尤其是加工儲存Excel非常方便，但是唯獨想讓匯出的Excel自動調整列寬或者行高，確實做不到啊，尤其是加工後還需要使用者自己調整列寬，非常不便。

所以必須openpyxl模組助力。

這是最簡單的實現

from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
ws = wb[wb.sheetnames[0]]                  # 開啟第一個sheet
ws.column_dimensions['A'].width = 20.0     # 調整列A寬
ws.row_dimensions[1].height = 40           # 調整行1高
ws.save('test.xlsx')

但是如果隨便讓一個Excel每個sheet都實現調整列寬怎麼辦呢，我寫了這麼一個函數

獲取每一個sheet的每一列的最長值，將其設定為列寬

from openpyxl import load_workbook
from 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.loc[len(df)]=list(df.columns)						#把標題行附加到最後一行
		for col in df.columns:				
			index=list(df.columns).index(col)					#列序號
			letter=get_column_letter(index+1)					#列字母
			collen=df[col].apply(lambda x:len(str(x).encode())).max()	#獲取這一列長度的最大值 當然也可以用min獲取最小值 mean獲取平均值
			ws.column_dimensions[letter].width=collen*1.2+4		#也就是列寬為最大長度*1.2 可以自己調整
	wb.save(filename)
	
reset_col('test.xlsx')

注意Openpyxl僅僅支援最新的.xlsx格式，如果執行有這樣的報錯：

File “C:UsersAdministratorAppDataLocalProgramsPythonPython38libsite-packagesopenpyxlreaderexcel.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

說明檔案格式老舊，請開啟Excel檔案，選擇另存為.xlsx格式

Python寫Excel列寬，行高的一些方法

使用第三方庫 xlsxwriter，結果檔案格式為xlsx

程式碼如下：

 import xlsxwriter
# 建立一個新工作簿並新增一個工作表。
workbook = xlsxwriter.Workbook(r'\MacHomeDesktoptest11.xlsx')
worksheet = workbook.add_worksheet('test')
# 為第一列設定格式
worksheet.set_column('A:A', 30)                                 # 設定A列寬度30
worksheet.set_column('B:B', 20)                                 # 設定B列寬度20
# cell_format = workbook.add_format({'bold': True})
worksheet.set_row(3, 20)                                           # 設定第4行高度為20
# worksheet.set_row(3, 20,cell_format)

實現結果截圖如下：

使用第三方庫 xlwt，結果檔案格式為xls