python-excel驱动之xlsxwriter

安装

xlsxwriter是一个非标准库，因此需要自行安装，安装过程并不困难，Windows/Mac用户均可以在命令行(CMD)/终端(Terminal)中使用pip安装

pip install xlsxwriter

配合openpyxl使用

由于xlsxwriter设计初衷时创建新文件，所以没有直接提供打开已存在工作表的方法。我们可以利用 openpyxl 模块来打开已存在的工作表，并且结合 XlsxWriter 的功能来进行编辑和写入数据。

示例：xlwt打开已有文档yxy.xlsx，创建一个临时文档

import xlsxwriter
import openpyxl
workbook = openpyxl.load_workbook(filename='yxy.xlsx')		#用openpyxl打开文件
worksheetname = workbook.sheetnames		#读取所有工作表
tempbook = xlsxwriter.Workbook('tempbook.xlsx')		#用xlwt打开一个
for sheetname in worksheetname:
    worksheet = workbook[sheetname] 
    tempsheet = tempbook.add_worksheet(sheetname)
    for row in range(1,worksheet.max_row+1):
        for col in range(1,worksheet.max_column+1):
            cell_value = worksheet.cell(row=row,column=col).value
            tempsheet.write(row-1,col-1,cell_value)

tempbook.close()

其他基本操作，详见openpyxl

插入图片/内部链接

import xlsxwriter
import openpyxl
workbook = openpyxl.load_workbook(filename='yxy.xlsx')
worksheetname = workbook.sheetnames
tempbook = xlsxwriter.Workbook('tempbook.xlsx')
for sheetname in worksheetname:
    worksheet = workbook[sheetname] 
    tempsheet = tempbook.add_worksheet(sheetname)
    for row in range(1,worksheet.max_row+1):
        for col in range(1,worksheet.max_column+1):
            cell_value = worksheet.cell(row=row,column=col).value
            tempsheet.write(row-1,col-1,cell_value)
#以上为打开已有文件

tempsheet = tempbook.sheetnames ['测试项目']
tempsheet.insert_image('E1',r'C:\Users\Administrator\Desktop\演示图片.png')
#第二个参数为图片的绝对链接，由于字符串中含多个反斜杠\，使用r前缀来表示路径字符串

tempsheet = tempbook.sheetnames ['测试项目']
tempsheet.write_url(20,0, "internal:%s!B3" % ("测试2"), string="点击跳转到测试2工作表的B3单元格")		#“20”和“0”分别指在21行和1列插入内容，“%s!B3" % ("测试2")”表示这个链接指向测试2工作表的B3单元格，"点击跳转到测试2工作表的B3单元格"为显示的文字

tempbook.close()

点击链接后

插入图表

1. 插入图表

chart = workbook.add_chart({'type': 'column','subtype': 'stacked'})

'type’指图表类型，'subtype’指某些图表类型中的子类型

• area：创建一个Area（实线）样式表。
• bar：创建条形样式（转置直方图）图表。
• column：创建列样式（直方图）图表。
• line：创建线型图表。
• pie：创建一个饼图样式图表。
• doughnut：创建一个甜甜圈样式表。
• scatter：创建散点图样式图。
• stock：创建一个股票样式图。
• radar：创建雷达样式表。

2. 插入数据

chart.add_series(options)

optios格式为{‘values’: '=工作表名!$列对应字母$行对应数字:$列对应字母$行对应数字,‘name’:‘系列名称’}

3. 插入位置

worksheet.insert_chart('A7', chart)

4. 设置X轴Y轴属性

chart.set_x_axis({
    'name': 'x轴名称',
    'name_font': {'size': 12, 'bold': True},
    'num_font':  {'italic': True },
})

这是设置X轴的属性：第一个参数name是指轴的名称，name_font设置x轴的字体属性，这里设置了粗体和大小。num_font指轴编号(也即如图中x轴下方的1234)的字体属性这里设置了斜体。Y轴类似

5. 合并不同类型的图表

chart.combine(line_chart)

chart是原表格，line_chart是要叠加的表格

6. 设置图表尺寸

chart.set_size({'width': 720, 'height': 576})
chart.set_size({'x_scale': 1.5, 'y_scale': 2})
worksheet.insert_chart('E2', chart, {'x_offset': -10, 'y_offset': 5})

函数里面有六个参数：width、height，x_offset,y_offset
前两个代表宽度与高度是以像素为单位，默认的图表的宽度*高度为480 x 288像素。
后两个参数是设置类型图片在整个chart图表区域中移动。含义用一张表格来说明

7. 设置图表标题

chart.set_title({'name': '常见数列'})
chart.set_title({
    'name': '常见数列',
    'overlay': True,
    'layout': {
        'x': 0.62,
        'y': 0.24,
    }
})
chart.set_title({'none': True})  #关闭此默认标题同时关闭所有其他set_title()选项。

name指标题；overlay代表允许标题覆盖到图表上通常与layout一起使用。layout以图表相对单位设置标题的位置(x, y)。

8. 设置图例

chart.set_legend({'none': True})
chart.set_legend({'position': 'none'})
chart.set_legend({'position': 'bottom'})
chart.set_legend({'font': {'size': 4, 'bold': True}})
chart.set_legend({'delete_series': [0, 3]})

none：关闭图例；默认是开启的。position：图例的位置。font：图例的字体属性。delete_series：删除指定图例，以列表呈现。

9. 设置默认图表样式
   用set_style(num)函数，用于将图表的样式设置为Excel中“设计”选项卡上可用的48种内置样式之一。参数num就是48种内置样式之一。

10. 设置图表区域

chart.set_chartarea({
    'fill':   {'color': 'black'}
})

fill：设置图表区域的实心填充属性，例如颜色。

1. 图表下方添加数据表
   用set_table()函数在水平轴下方添加一个数据表。

实例

import xlsxwriter
import openpyxl
workbook = openpyxl.load_workbook(filename='yxy.xlsx')
worksheetname = workbook.sheetnames
tempbook = xlsxwriter.Workbook('tempbook.xlsx')
for sheetname in worksheetname:
    worksheet = workbook[sheetname] 
    tempsheet = tempbook.add_worksheet(sheetname)
    for row in range(1,worksheet.max_row+1):
        for col in range(1,worksheet.max_column+1):
            cell_value = worksheet.cell(row=row,column=col).value
            tempsheet.write(row-1,col-1,cell_value)
#以上为打开已有文件
tempsheet = tempbook.sheetnames['测试3']

chart = tempbook.add_chart({'type':'column'})
#创建直方图


cols = ['A','B','C','D']
rows = [2,8]
max_row = max(rows)
min_row = min(rows)
for col in cols:
    worksheet1 = workbook['测试3']
    colname = worksheet1['%s1' % col]		#由于xlwt模块无法读取文件内容，因此需要用openpyxl里面的workbook来处理标题，此处不能用tempbook和tempsheet
    chart.add_series({'values':'=测试3!$%s$%s:$%s$%s' % (col,min_row,col,max_row),
    'name':colname.value})
#插入直方图数据


chart.set_title({'name': '常见数列'})
#设置表格标题

chart.set_table()
#在图表下方插入数据表

tempsheet.insert_chart('A9', chart)
#图表插入位置A9


tempbook.close()