xlrd 和 xlsxwriter 操作excel表格

Introduction 前言

• 结合自己平时用到的例子
  • 读excel 用 xlrd 模块
  • 设置单元格样式 以及 写 excel文件用 xlsxwriter 就够了！

xlrd 用法

import xlrd
book = xlrd.open_workbook('data.xlsx')
sheet1 = book.sheets()[0]
nrows = sheet1.nrows
print('表格总行数',nrows)
ncols = sheet1.ncols
print('表格总列数',ncols)
row3_values = sheet1.row_values(2)
print('第3行值',row3_values)
col3_values = sheet1.col_values(2)
print('第3列值',col3_values)
cell_3_3 = sheet1.cell(2,2).value
print('第3行第3列的单元格的值：',cell_3_3)

xlsxwriter 用法

trick 1：使用python将excel单元格中指定文字加粗标红

import xlsxwriter
out_path = 'test.xlsx'
workbook = xlsxwriter.Workbook(out_path)
worksheet = workbook.add_worksheet('sheet1')
bold_red = workbook.add_format({'bold': True, 'color': 'red'})

worksheet.write_rich_string('A1',
                            '这是一个',
                            bold_red, '测试',
                            '，哈哈哈')

workbook.close()

• 效果
  
  Reference

写文件

逐行写

import xlsxwriter
res_file = 'labeled_data_consistency.xlsx'
workbook = xlsxwriter.Workbook(os.path.join(cur_dir, res_file))
booksheet = workbook.add_worksheet('Sheet1')

booksheet.write_row('A1', head)
bold_red = workbook.add_format({'bold': True, 'color': 'red'})
for i in range(2, len(data_list)+2):
    line = 'A' + str(i)  # 从第二行开始，第一行是表头
    booksheet.write_row(line, data_list[i-2])
workbook.close()

挨个单元格写

import xlsxwriter
res_file = 'labeled_data_consistency.xlsx'
workbook = xlsxwriter.Workbook(os.path.join(cur_dir, res_file))
booksheet = workbook.add_worksheet('Sheet1')

booksheet.write_row('A1', head)
bold_red = workbook.add_format({'bold': True, 'color': 'red'})
for i in range(2, len(data_list)+2):
    # line = 'A' + str(i)  # 从第二行开始，第一行是表头
    # booksheet.write_row(line, data_list[i-2])
    one_list = data_list[i-2]
    for j in range(12):
        if one_list[j]:  # 数据非空，才准备写入单元格
            booksheet.write_rich_string(i, j, one_list[j])
workbook.close()