xlsxwriter

hello

• 将文本写入单元格

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write('A1', 'Hello world')

workbook.close()

simple xlsx

• 生成简单的excel文件

import xlsxwriter

workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()

expenses = (
    ['Rent', 1000],
    ['Gas', 100],
    ['Food', 300],
    ['Gym', 50]
    )

row = 0
col = 0

for item, cost in expenses:
    worksheet.write(row, col, item)
    worksheet.write(row, col + 1, cost)
    row += 1

worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

formatting

• 生成带格式的excel文件

import xlsxwriter

workbook = xlsxwriter.Workbook('Expenses02.xlsx')
worksheet = workbook.add_worksheet()

bold = workbook.add_format({'bold' : True})

money = workbook.add_format({'num_format': '$#,##0'})

worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)

expenses = (
    ['Rent', 1000],
    ['Gas', 100],
    ['Food', 300],
    ['Gym', 50]
)

row = 1
col = 0

for item, cost in expenses:
    worksheet.write(row, col, item)
    worksheet.write(row, col + 1, cost, money)
    row += 1;

worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 1, '=SUM(B2:B5)', money)

workbook.close()

types of data

• 其它数据类型

import xlsxwriter
from datetime import datetime

workbook = xlsxwriter.Workbook('Expenses03.xlsx')
worksheet = workbook.add_worksheet()

bold = workbook.add_format({'bold' : True})

money = workbook.add_format({'num_format': '$#,##0'})

date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})

worksheet.set_column(1, 1, 15)

worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Date', bold)
worksheet.write('C1', 'Cost', bold)

expenses = (
    ['Rent','2013-01-13', 1000],
    ['Gas','2013-01-13', 100],
    ['Food','2013-01-13', 300],
    ['Gym','2013-01-13', 50]
)

row = 1
col = 0

for item, date_str, cost in expenses:
    date = datetime.strptime(date_str, "%Y-%m-%d")
    worksheet.write(row, col, item)
    worksheet.write(row, col + 1, date, date_format)
    worksheet.write(row, col + 2, cost, money)
    row += 1;

worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 2, '=SUM(B2:B5)', money)

workbook.close()