python 读写 excel

参考

• 读写案例
• 快速开始

安装excel读写包

pip install xlrd
pip install xlwt

xlrd 语法

import xlrd
# 打开文档
book = xlrd.open_workbook("file_name.xls")

print("sheet 的个数", book.nsheets) 
print("sheet的名称", book.sheet_names()) 

# 打开工作表(三种方法)
sh = book.sheet_by_index(0)
sh = book.sheets()[0]
sh = book.sheet_by_name('sheet1')

# 操作行列和单元格
print(sh.name, sh.nrows, sh.ncols)
print("cell 值", sh.cell_value(rowx=29, colx=3))
print("cell 值", sh.cell(29,3).value)

xlwt语法

import xlwt
from datetime import datetime

style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
    num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))

案例 - 合并不同表格的某一列

import xlrd
import xlwt

file_name = "excel_file_name.xlsx"  // 要合并的excel文件的名称
col_name = "excel_col_name"  // 要合并的文件的每个sheet中的某一列的 名称
target_name = "合成表"  // 合成后的excel文件的名称


def read_file(file_path):
    book = xlrd.open_workbook(file_path)

    targetBook = xlwt.Workbook(encoding = "utf-8")
    targetSheet = targetBook.add_sheet(target_name)

    // 遍历 sheet ，取出每一列的数据，写入到目标文件中。
    for sheetIndex in range(book.nsheets):
        sheet = book.sheet_by_index(sheetIndex)

        selectedColIndex = sheet.row_values(0).index(col_name)

        sheetName = sheet.name

        targetSheet.write(0, sheetIndex, sheetName)

        currentCol = sheet.col_values(selectedColIndex)
        for index in range(len(currentCol)):
            if index > 0:
                targetSheet.write(index, sheetIndex, str(currentCol[index]))

    targetBook.save(target_name + '-' + file_path)


if __name__ ==  '__main__':
    read_file(file_name)