python操作Excel方法汇总（二）

本篇主要是对于xlwings模块的应用，xlwings是功能较为全面的一个模块，可以进行读、写、修改格式等操作，而且对于（.xls）与（.xlsx）都适用，还与matplotlib等模块可以交互。
本节我们只记录基本操作，更详细的内容可参阅官方文档：
xlwings官方文档

结构示意图

使用xlwings时，按照以上结构依次进行操作，即app（程序）——book（工作簿）——sheet（工作表）——range（某些单元格）

创建新文档，写入内容，保存退出

文件夹初始状态：

执行以下代码：

import xlwings

#打开程序（只打开不新建）：
app = xlwings.App(visible=True,add_book=False)

#创建工作簿：
wb = app.books.add()

#创建工作表：
sheet = wb.sheets.add('mysheet')

#在A1单元格输入：1；在B1单元格输入：hello；在C1单元格输入：哈哈
sheet.range('A1').value = 1
sheet.range('B1').value = 'hello'
sheet.range('C1').value = '哈哈'

#在第十一行输入：1-10
list1 = [i for i in range(1,11)]
sheet.range('A11').value = list1

#在第四列输入：11-20
list2 = [i for i in range(11,21)]
sheet.range('D1').options(transpose=True).value = list2

#在A2:C3区域输入
list3 = [[1,2,3],[4,5,6]]
sheet.range('A2').value = list3


#保存：
wb.save(r'D:\programming\Python learn\excel有关\xlwings创建.xls')

#关闭工作簿：
wb.close()

#退出程序：
app.quit()

可见文件夹已存入新文件：

打开后如图：

打开已有表格文件，读取、修改数据

import xlwings

#打开程序（只打开不新建）：
app = xlwings.App(visible=True,add_book=False)

#打开已存在的文件：
wb = app.books.open(r'D:\programming\Python learn\excel有关\xlwings创建.xls')

#选取工作表：
sheet = wb.sheets['mysheet']#或通过索引选取

#获取第十一行内容：
rng1 = sheet.range('A11').expand('right')
n1 = rng1.columns.count#有效列数
print(rng1.value,'\n',n1)

#获取第四列内容：
rng2 = sheet.range('D1').expand('down')
n2 = rng2.rows.count
print(rng2.value,'\n',n2)

#获取A2：C3区域内容：
rng3 = sheet.range('A2:C3')
print(rng3.value)

#在A5、B6、C7单元格写入：21,22,23
for i in range(3):
    sheet[i+4,i].value = i+21#单元格另一种表示方法

#保存：
wb.save()

#关闭工作簿：
wb.close()

#退出程序：
app.quit()

输出结果：

打开之前的文件：

可以看到新写入了21-23

修改单元格格式

rng = sheet.range('A1')
rng.color = (0,0,255)#背景色(RGB)
rng.column_width = 15#列宽
rng.row_height = 20#行高

sheet.range('D12').formula = '=SUM(D1:D11)'#利用公式计算

sheet.range('B6').clear_contents()#清除内容，清除格式和内容为clear（）

'''
一些其他操作：
rng.columns.autofit()# 所有列宽度自适应
rng.rows.autofit()# 所有行宽度自适应
rng.offset(row_offset=0,column_offset=0)# range平移
rng.resize(row_size=None,column_size=None)#range进行resize改变range的大小
'''

在之前的表格基础上进行以上操作之后：

将matplotlib图像插入表格

import xlwings
import numpy as np
import matplotlib.pyplot as plt


app = xlwings.App(visible=True,add_book=False)
wb = app.books.add()
sheet = wb.sheets.add('picture')

x = np.arange(0,5,0.1)
y = 2*x+np.random.random(x.shape[0])

pic = plt.figure()
plt.scatter(x,y,c='r')
plt.title('xlwings and matplotlib')

sheet.pictures.add(pic,left=100,top=100)

wb.save(r'D:\programming\Python learn\excel有关\matplotlib交互.xls')
wb.close()
app.quit()

xlwings生成图表

import xlwings

app = xlwings.App(visible=True,add_book=False)
wb = app.books.add()
sheet = wb.sheets.add('picture')

sheet.range('A1').value = [['月份','数量'],
                           ['1月',12],
                           ['2月',19],
                           ['3月',26],
                           ['4月',20],
                           ['5月',33],
                           ['6月',42],
                           ['7月',56],
                           ['8月',44],
                           ['9月',34],
                           ['10月',30],
                           ['11月',24],
                           ['12月',11]]


chart1 = sheet.charts.add(100,100)#添加图表，输入图表位置
chart1.set_source_data(sheet.range('A1').expand())#数据区域
chart1.chart_type = 'column_clustered'#图表类型
chart1.api[1].SetElement(2)#显示标题
chart1.api[1].ChartTitle.Text = 'column_clustered'#标题内容

wb.save(r'D:\programming\Python learn\excel有关\生成图表.xls')
wb.close()
app.quit()

图表类型从官方文档中查看有以下若干种:
3d_area, 3d_area_stacked, 3d_area_stacked_100, 3d_bar_clustered, 3d_bar_stacked, 3d_bar_stacked_100, 3d_column, 3d_column_clustered, 3d_column_stacked, 3d_column_stacked_100, 3d_line, 3d_pie, 3d_pie_exploded, area, area_stacked, area_stacked_100, bar_clustered, bar_of_pie, bar_stacked, bar_stacked_100, bubble, bubble_3d_effect, column_clustered, column_stacked, column_stacked_100, combination, cone_bar_clustered, cone_bar_stacked, cone_bar_stacked_100, cone_col, cone_col_clustered, cone_col_stacked, cone_col_stacked_100, cylinder_bar_clustered, cylinder_bar_stacked, cylinder_bar_stacked_100, cylinder_col, cylinder_col_clustered, cylinder_col_stacked, cylinder_col_stacked_100, doughnut, doughnut_exploded, line, line_markers, line_markers_stacked, line_markers_stacked_100, line_stacked, line_stacked_100, pie, pie_exploded, pie_of_pie, pyramid_bar_clustered, pyramid_bar_stacked, pyramid_bar_stacked_100, pyramid_col, pyramid_col_clustered, pyramid_col_stacked, pyramid_col_stacked_100, radar, radar_filled, radar_markers, stock_hlc, stock_ohlc, stock_vhlc, stock_vohlc, surface, surface_top_view, surface_top_view_wireframe, surface_wireframe, xy_scatter, xy_scatter_lines, xy_scatter_lines_no_markers, xy_scatter_smooth, xy_scatter_smooth_no_markers
比较常用的即折线图、散点图、条形图、饼形图等等。