XlsxWriter 学习

python3.5自带了xlsxwriter模块
xlsxwriter官方文档
简单例子：

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')    #创建excle文件
worksheet = workbook.add_worksheet('s1')    #创建工作表s1

worksheet.write('A1', 'Hello')  #在表中A1单元格写入

workbook.close()    #关闭文件

一个使用循环写入数据，使用excel公式的例子

import xlsxwriter

workbook = xlsxwriter.Workbook('cs1.xlsx')
worksheet = workbook.add_worksheet('s1')

# 创建一个名称和数据的元祖
expenses = (
    ['房租', 1000],
    ['加油', 100],
    ['食物', 300],
    ['健身房', 50],
)

# 初始的行、列设置为0
rows = 0
col = 0

#循环将元祖中各字段写入表格中
for item, cost in expenses:
    worksheet.write(rows, col, item)
    worksheet.write(rows, col+1, cost)
    rows += 1

#用exlce公式求和
worksheet.write(rows, 0, 'Total')
worksheet.write(rows, 1, '=SUM(B1:B4)')

workbook.close()

再看一个带格式写入excel的例子。

workbook = xlsxwriter.Workbook('cs1.xlsx')
worksheet = workbook.add_worksheet('s1')

#用add_format方法添加格式
bold = workbook.add_format({'bold': True})
money = workbook.add_format({'num_format': '$#,##0'})

#A1、B1表头先写好
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', money)

expenses = (
    ['房租', 1000],
    ['加油', 100],
    ['食物', 300],
    ['健身房', 50],
)

rows = 1
col = 0

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

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

workbook.close()

一个写入时间格式的例子。

from datetime import datetime
import xlsxwriter

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

 bold = workbook.add_format({'bold': 1})
 money_format = 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)

 # 马上要写入excel的数据.
 expenses = (
     ['Rent', '2013-01-13', 1000],
     ['Gas',  '2013-01-14',  100],
     ['Food', '2013-01-16',  300],
     ['Gym',  '2013-01-20',   50],
 )

 row = 1
 col = 0

 for item, date_str, cost in (expenses):
     # 时间字符串格式转换.
     date = datetime.strptime(date_str, "%Y-%m-%d")

     worksheet.write_string  (row, col,     item              )
     worksheet.write_datetime(row, col + 1, date, date_format )
     worksheet.write_number  (row, col + 2, cost, money_format)
     row += 1

 worksheet.write(row, 0, 'Total', bold)
 worksheet.write(row, 2, '=SUM(C2:C5)', money_format)

 workbook.close()

write方法

往excel写不同数据需要用不同的write方法，如以下。

• write_string()
• write_number()
• write_formula()
• write_datetime()
• write_url()

---

The Workbook Class

Workbook(filename[, options])

workbook.add_worksheet()
name参数是可选的。 如果未指定，将遵循默认Excel约定，即Sheet1，Sheet2等。

worksheet1 = workbook.add_worksheet()           # Sheet1
worksheet2 = workbook.add_worksheet('Foglio2')  # Foglio2
worksheet3 = workbook.add_worksheet('Data')     # Data
worksheet4 = workbook.add_worksheet()           # Sheet4

workbook.add_format()
添加单元格格式
参数：字典形式

workbook.add_chart()
添加图表

workbook.close()
关闭并写入到excel

workbook.set_size(width, height)
设置工作簿窗口大小

workbook.set_properties()

workbook.set_properties({
    'title':    'This is an example spreadsheet',
    'subject':  'With document properties',
    'author':   'John McNamara',
    'manager':  'Dr. Heinz Doofenshmirtz',
    'company':  'of Wolves',
    'category': 'Example spreadsheets',
    'keywords': 'Sample, Example, Properties',
    'comments': 'Created with Python and XlsxWriter'})

workbook.define_name()
定义单元格变量

workbook.define_name('Exchange_rate', '=0.96')
worksheet.write('B3', '=B2*Exchange_rate')

可分全局变量（整个工作簿）和本地变量（工作表）

# Global workbook name
workbook.define_name('Sales', '=Sheet1!$G$1:$H$10')

# Local worksheet name 
workbook.define_name('Sheet2!Sales', '=Sheet2!$G$1:$G$10')

如果工作表名称有特殊字符，必须用单引号括出来

workbook.define_name("'New Data'!Sales", '=Sheet2!$G$1:$G$10')

workbook.add_vba_project()
添加vba二进制程序至工作簿

workbook.worksheets()
获得一个工作簿中所有工作表的列表。通常用来对每个工作表执行相同操作。

for worksheet in workbook.worksheets():
    worksheet.write('A1', 'Hello')

workbook.get_worksheet_by_name()
通过表的name，获得工作簿中一个工作表object，如果找不到返回None。