python3 xlsxwriter_Python3.x 操作Excel（写）——XlsxWriter 模块

XlsxWriter 模块

XlsxWriter is a Python module for creating Excel XLSX files.

XlsxWriter 是一个人基于Pytohn用于创建Excel文件的库，只能创建新文件，不支持读取和修改，所以和xlrd配合使用。

eg1：创建名为‘hello.xlsx’文件，并在第一行，第一列写入数据‘Hello， world’。

代码：

# -*- coding: utf-8 -*-

# Python操作excel表格(写)

# 创建名为'hello.xlsx'文件，并在第一行，第一列写入数据'Hello， world'.

import xlsxwriter

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

worksheet = workbook.add_worksheet('test')

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

workbook.close()

运行结果：

eg2：新建一个表格‘test’， 新建sheet表‘test1’， 插入数据，中国移动流量包价格大小统计表。

代码：

# -*- coding: utf-8 -*-

# 新建一个表格‘test’， 新建sheet表‘test1’， 插入数据，中国移动流量包价格大小统计表。

import xlsxwriter

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

worksheet = workbook.add_worksheet('test1')

data = ( # 数据格式

['value', 'MB'],

['3', '10'],

['5', '30'],

['10', '70'],

['20', '150'],

['30', '500'],

['50', '1024'],

['70', '2048'],

['100', '3072'],

['130', '4096'],

['180', '6144'],

['280', '11264'],

)

row = 0

col = 0

for facevalue, size in (data): # 插入数据

worksheet.write(row, col, facevalue)

worksheet.write(row, col + 1, size)

row += 1

workbook.close()

运行结果：

eg3：创建带格式的Excel表格

代码：

# -*- coding: utf-8 -*-

# 添加一些格式

import xlsxwriter

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

worksheet = workbook.add_worksheet('test2')

bold = workbook.add_format({'bold': True}) # 设置粗体，默认False

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) # 设置带money格式写入

row += 1

worksheet.write(row, 0, 'Total', bold)

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

workbook.close()

运行结果：

eg4：创建带日期的Excel表格

代码：

# -*- coding: utf-8 -*-

import xlsxwriter

from datetime import datetime

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

worksheet = workbook.add_worksheet() # 不命名sheet名称，默认为'sheet1'

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)

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()

运行结果：