python3读写excel之xlrd和xlwt

最新要处理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')