Python with Excel: xlrd, xlsxwriter, and xlutils

Libraries

xlsxwriter 的文件寫得比較好

ref:
http://www.python-excel.org/
https://xlsxwriter.readthedocs.org/en/latest/
http://openpyxl.readthedocs.org/en/latest/

Usage

row 是橫排
column 是直排

Default format

import xlsxwriter

workbook = xlsxwriter.Workbook('label_copy.xlsx')

# default cell format
workbook.formats[0].set_font_size(12)
workbook.formats[0].set_text_wrap() # 要加上這個才能正常顯示多行
workbook.formats[0].set_align('vcenter')

ref:
https://xlsxwriter.readthedocs.org/en/latest/format.html

Multiple lines

lines_format = workbook.add_format({
    'align': 'left',
    'font_size': 12,
    'text_wrap': True,
    'valign': 'vcenter',
})

# 或是用 """多行"""
content = 'first line\nsecond line'
worksheet.write(0, 0, content, lines_format)

重點是要加上 text_wrap

ref:
http://stackoverflow.com/questions/15370432/writing-multi-line-strings-into-cells-using-openpyxl

Write to existing excel files

from xlutils.copy import copy as xlutils_copy
import xlrd

rb = xlrd.open_workbook('your_file.xls', formatting_info=True)
wb = xlutils_copy(rb)
ws = wb.get_sheet(0)
ws.write(0, 0, 'Hello World')

ref:
https://stackoverflow.com/questions/2725852/writing-to-existing-workbook-using-xlwt

Examples

ref:
https://xlsxwriter.readthedocs.org/en/latest/examples.html