最新要处理excel文件,于是整理了下python3中常用操作excel的包的用法
pip 用法
# 查看已安装 pip list # 安装包 pip install xlrd # 安装指定版本 pip install xlrd==1.2.0 # 卸载 pip uninstall xlrd
xlrd
pip install xlrd
xlrd 读取 xls 文件,读取 xlsx 文件出错
import xlrd workbook = xlrd.open_workbook('./source-files/info.xls', formatting_info=True) print(workbook.sheet_names()) # sheet = workbook.sheets()[0] #通过索引顺序获取sheet # sheet = workbook.sheet_by_index(0) #通过索引顺序获取sheet sheet = workbook.sheet_by_name('Sheet1') #通过名称获取sheet print(sheet.name) #sheet名称 print(sheet.nrows, sheet.ncols) #行数、列数 print(sheet.row(rowx=0)) #整行 print(sheet.row_len(rowx=0)) #返回该行的有效单元格长度 print(sheet.col(colx=0)) #整列 print(sheet.cell(rowx=0, colx=0)) #单元格 print(sheet.row_values(1)) #获取整行的值 print(sheet.row_values(1, start_colx=0, end_colx=None)) #获取某行的某列到另一列的值 print(sheet.col_values(1)) #获取整列的值 print(sheet.col_values(0, start_rowx=1, end_rowx=2))#获取某列的某行到另一行的值 print(sheet.cell_value(rowx=0, colx=0)) #单元格的值 list_a = [] for i in range(0, sheet.nrows, 1): tmp = [] for j in range(0, sheet.ncols, 1): cell_type = sheet.cell_type(i, j) cell_value = sheet.cell_value(i, j) #处理日期格式 if cell_type == 3: tmp.append(xlrd.xldate_as_datetime( cell_value, workbook.datemode).strftime('%Y-%m-%d %H:%M:%S')) else: tmp.append(cell_value) list_a.append(tmp) print(list_a) #读取合并单元格 print(sheet.merged_cells) for (row_start, row_end, col_start, col_end) in sheet.merged_cells: print(sheet.cell_value(rowx=row_start, colx=col_start))
xlwt
pip install xlwt
xlwt 生成 xls 文件,生成的 xls 文件最多能支持 65536 行、256列数据
import xlwt import datetime import os workbook = xlwt.Workbook() sheet = workbook.add_sheet('test') style = xlwt.XFStyle() # 字体 font = xlwt.Font() font.name = 'Microsoft Yahei' font.height = 20*12 font.bold = True font.underline = True font.italic = True style.font = font # 背景颜色 pattern_obj = xlwt.Pattern() pattern_obj.pattern = xlwt.Pattern.SOLID_PATTERN pattern_obj.pattern_fore_colour = 13 style.pattern = pattern_obj # 边框 borders = xlwt.Borders() borders.left = xlwt.Borders.DASHED # 虚线 borders.right = 1 # 实线 borders.left_colour = 0x000 borders.right_colour = 3 style.borders = borders # 对齐 alignment_obj = xlwt.Alignment() alignment_obj.horz = xlwt.Alignment.HORZ_CENTER alignment_obj.vert = xlwt.Alignment.VERT_CENTER style.alignment = alignment_obj sheet.col(0).width = 2000 #日期格式 style1 = xlwt.XFStyle() style1.num_format_str = "YYYY-M-D H:M:S" for i in range(5): for j in range(4): sheet.write(i, j, i+j, style) #公式 sheet.write(i, 4, xlwt.Formula("sum(A"+str(i+1)+":D"+str(i+1)+")")) #日期格式 sheet.write(i, 5, datetime.datetime.now(), style1) #超链接 sheet.write(i, 6, xlwt.Formula('HYPERLINK("https://www.baidu.com/";"Baidu")')) #合并单元格 sheet.write_merge(i, i, 7, 8, 'value') if os.path.exists('./gen-files/test.xls'): os.remove('./gen-files/test.xls') workbook.save('./gen-files/test.xls')
标签:xlwt,sheet,excel,cell,workbook,print,xlrd From: https://www.cnblogs.com/caroline2016/p/16944744.html