python能够轻松实现自动化办公、数据分析，一些金融、证券、会计行业中会经常处理excel中的大量数据，工序单一，任务繁多，人工效率低下，想要实现自动化数据分析，excel也提供了方法，使用内部VBA进行二次开发，但对新手来说较为复杂，而python则应用范围更广、更为简单、代码更加简洁，因此python实现excel数据分析操作时首选；以下 项目实现汇总多个excel文件特定数据，生成图表呈现，也算是一个小的数据分析项目，读者可以作为一个模板进行改造升级应用：
文章目录
- 一、基础概念
- excel表格的一些概念
- python的强大来自于丰富的库
- 二、源代码（亲测可用）
- 三、使用
- 1、目录下放置需要分析的xlsx文件
- 2、excel 测试数据，处理需求是“按照月份，统计型号对应的金额，每个工作表都有相应数据，而工作表多达二十多个”
- 3、运行方式1：直接点击py文件即可
- 4、运行方式2：在文件夹空白位置下，shift+鼠标右击，打开cmd 输入命令行执行
- 5、输出结果展示
一、基础概念
excel表格的一些概念
python的强大来自于丰富的库
python处理excel文件主要是第三方模块库xlrd、xlwt、xlutils和pyExcelerator，除此之外，python处理excel还可以用win32com和openpyxl模块
二、源代码（亲测可用）
#!/usr/bin/env python
import os
import re
import sys
'''excel操作库'''
import xlrd
import xlwt
#复制、分割、刷选excel等功能
from xlutils.copy import *
import openpyxl
from openpyxl.chart import BarChart, Series, Reference, AreaChart3D
import pyExcelerator
import datetime
#复制库
from copy import deepcopy
from copy import copy
def openxl_unmeger(inputxl, outputxl):
'''
由于表格很多合并单元格，不方便处理，这里需要处理拆分单元格，拆分后每个单元格填充“原合并单元格”一样的数据
'''
huizhong = openpyxl.load_workbook(inputxl, data_only=True)#只读取值，忽略公式
# 工作表名字
worksheets_name = huizhong.sheetnames
# table = huizhong[worksheets[6]]
for sheet_name in worksheets_name:
table = huizhong[sheet_name]
print(sheet_name)
# 读取合并单元格位置
m_list = table.merged_cells
# 拆分合并的单元格 并填充内容
l = deepcopy(m_list)
for m_area in l:
# 这里的行和列的起始值（索引），和Excel的一样，从1开始，并不是从0开始（注意）
r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col
table.unmerge_cells(start_row=r1, end_row=r2, start_column=c1, end_column=c2)
print('区域:', m_area, ' 坐标:', r1, r2, c1, c2)
# 获取一个单元格的内容
first_value = table.cell(r1, c1).value
# 数据填充
for r in range(r1, r2 + 1): # 遍历行
if c2 - c1 > 0: # 多个列，遍历列
for c in range(c1, c2 + 1):
table.cell(r, c).value = first_value
else: # 一个列
table.cell(r, c1).value = first_value
table.cell(r, c1).number_format = copy(table.cell(r1, c1).number_format) #复制数字格式
huizhong.save(outputxl)
def del_row_include(inputxl, outputxl, del_list):
'''
删除含有del_list条目的行
'''
huizhong = openpyxl.load_workbook(inputxl)
# 工作表名字
worksheets_name = huizhong.sheetnames;
for sheet_name in worksheets_name:
table = huizhong[sheet_name]
row = 0
del_flag = False
while row < table.max_row:#遍历行
row = row + 1
col = 0
while col < table.max_column:#遍历列
col = col + 1
if del_flag == True:
del_flag = False
break
for str in del_list:
if table.cell(row, col).value == str:
table.delete_rows(row)
row = row - 1 #删除一行后，行号发生变化，需要手动进行减1调整
#print("deleting:sheet name:%s row:%d" % (sheet_name, row))
del_flag = True
break
huizhong.save(outputxl)
def del_row_exclude(inputxl, outputxl, not_del_list, title):
'''
删除不含有not_del_list条目的行
'''
huizhong = openpyxl.load_workbook(inputxl)
# 工作表名字
worksheets_name = huizhong.sheetnames;
for sheet_name in worksheets_name:
table = huizhong[sheet_name]
maxRow = deepcopy(table.max_row)
row = 0
del_flag = False
while row < maxRow:#遍历行
row = row + 1
col = 0
found_flag = False
while col < table.max_column:#遍历列
col = col + 1
for str in not_del_list:
if table.cell(row, col).value == str or table.cell(row, col).value == title:
found_flag = True
if found_flag == True:
break
if found_flag == False:
table.delete_rows(row)
row -= 1 #删除一行后，行号发生变化，需要手动进行减1调整
maxRow -= 1 #总行数也应该减1
huizhong.save(outputxl)
def init_summary(input_array, inputxl):
'''
初始化一个行列表，用来存储数据
'''
huizhong = openpyxl.load_workbook(inputxl)
# 工作表名字
worksheets_name = huizhong.sheetnames;
for sheet_name in worksheets_name:
input_array[0].append(sheet_name)
for i in range(1, len(input_array)):#相应其它行的列位置初始化为0
input_array[i].append(0)
input_array[0].append('sum')
for i in range(1, len(input_array)): #相应其它行的列位置初始化为0
input_array[i].append(0)
def total_result(inputxl, input_array, rmb_str, date_col_range):
huizhong = xlrd.open_workbook(inputxl)
# 工作表名字
worksheets_name = huizhong.sheet_names()
for sheet_name in worksheets_name:
table = huizhong.sheet_by_name(sheet_name)
#找到该sheet数据 将要记录列表的列数
array_col_index = 1 #从第二个位置开始填充
for sheet_index in input_array[0]:
if sheet_index == sheet_name:
break
array_col_index += 1
#print("sheet name:%s array_col_index:%d" % (sheet_name, array_col_index))
#找到金额的列数
rmb_col = 0
for row in range(0, table.nrows): # 遍历行
if rmb_col != 0:
break
for col in range(0, table.ncols): # 遍历列
if table.cell(row, col).value == rmb_str:
rmb_col = col #找到金额的列并记录下来
break
if rmb_col == 0:#没有找到提前进入下个循环
continue
#统计符合金额到统计列表
for row in range(0, table.nrows): # 遍历行
for col in range(0, date_col_range): # 遍历0-3列
if table.cell(row, col).ctype == 3:#if it is date
dtime = xlrd.xldate_as_tuple(table.cell_value(row, col), 0)#get month info
for m_index in range(1, len(input_array)):#遍历月份
if dtime[1] == input_array[m_index][0]:#月份相等，则记录到该位置上
if table.cell_value(row, rmb_col) != '':
input_array[m_index][array_col_index - 1] += table.cell_value(row, rmb_col) #累计到对应列表位置上
break
def add_sum(input_array):
for row_array in range(1, len(input_array)):#二维数组的行数
for add in range(1, len(input_array[0]) - 1):#求每一行的总和
input_array[row_array][len(input_array[0]) - 1] += input_array[row_array][add]
def wirte_to_xl(inputxl, outputxl, input_array, start_row, start_col):
huizhong = openpyxl.load_workbook(inputxl)
# 工作表名字
worksheets_name = huizhong.sheetnames
table = huizhong[worksheets_name[0]] #汇总到第一个工作表里
for row_array in range(0, len(input_array)):#二维数组的行数
for col_array in range(0, len(input_array[0])):#二维数组的列数
table.cell(row_array + 1 + start_row, start_col + col_array + 2).value = input_array[row_array][col_array]
huizhong.save(outputxl)
def data_move(src, dst):
for row in range(1, len(dst)):
dst[row].append(src[row][len(src[0]) - 1])
for row in range(1, len(dst)): #转换为浮点型，保留小数位2位
dst[row][1] = float(dst[row][1])
dst[row][1] = round(dst[row][1]/10000, 2)
def barchar(inputxl, outputxl, start_row_data, end_row_data, start_col_data, end_col_data, start_chart):
'''
生成直方图
'''
huizhong = openpyxl.load_workbook(inputxl)
# 工作表名字
worksheets_name = huizhong.sheetnames
table = huizhong[worksheets_name[0]] # 汇总到第一个工作表里
print(table.title)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'sum'
chart1.x_axis.title = 'months'
data = Reference(table, min_col=start_col_data+1, min_row=start_row_data, max_row=end_row_data, max_col=end_col_data)
cats = Reference(table, min_col=start_col_data, min_row=start_row_data+1, max_row=end_row_data)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
table.add_chart(chart1, start_chart)
huizhong.save(outputxl)
def Bar_Chart(start_row_data, end_row_data, start_col_data, end_col_data):
wb = openpyxl.Workbook()
ws = wb.active
rows = [
('Number', 'Batch 1', 'Batch 2'),
(2, 10, 30),
(3, 40, 60),
(4, 50, 70),
(5, 20, 10),
(6, 10, 40),
(7, 50, 30),
]
for row in rows:
ws.append(row)
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'
data = Reference(ws, min_col=start_col_data + 1, min_row=start_row_data, max_row=end_row_data, max_col=end_col_data)
cats = Reference(ws, min_col=start_col_data, min_row=start_row_data + 1, max_row=end_row_data)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
wb.save("barchar.xlsx")
#删除不计算的数据项
delete_not_cal = [
'人乳头状瘤病毒试剂',
'48测试/盒',
'96测试/盒',
'医学检验服务',
]
machine_name = [
'全自动免疫组化染色系统',
'化学发光免疫分析仪',
'液基薄层细胞制片机',
]
#统计的表格的首列数据
summary_array_template = [
['月份\\工作表名称', ],
[1, ],
[2, ],
[3, ],
[4, ],
[5, ],
[6, ],
[7, ],
[8, ],
[9, ],
[10, ],
[11, ],
[12, ],
]
#统计的表格的首列/首行数据
chart_reagent_data = [
['月份', '试剂销售额（万元）'],
['1月'],
['2月'],
['3月'],
['4月'],
['5月'],
['6月'],
['7月'],
['8月'],
['9月'],
['10月'],
['11月'],
['12月'],
]
def reagent_deal(oringinXL, addChart):
#输出以下中间处理的文件，目的是为了方便检查中间步骤结果是否处理正常
file_name = os.path.splitext(oringinXL)[0]
unmergeXL = file_name + '_unmerge_xl.xlsx'
del_ex_reagent = file_name + '_del_ex_reagent.xlsx'
del_machine = file_name + '_del_machine.xlsx'
write_summary_data = file_name + '_write_summary_data.xlsx'
write_reagent_data = file_name + '_write_reagent_data.xlsx'
summary_array = deepcopy(summary_array_template)
#初始化汇总表
init_summary(summary_array, oringinXL)
openxl_unmeger(oringinXL, unmergeXL)
#删除不计算的行
del_row_include(unmergeXL, del_ex_reagent, delete_not_cal)
#删除含机器的行
del_row_include(del_ex_reagent, del_machine, machine_name)
#开始统计
total_result(del_machine, summary_array, '金 额', 3)
#算每个月份的总和
add_sum(summary_array)
#移动数据到表格数据数组
data_move(summary_array, chart_reagent_data)
#写到汇总表格，这里写入的位置信息需要手动确认
wirte_to_xl(oringinXL, write_summary_data, summary_array, 0, 13)
wirte_to_xl(write_summary_data, write_reagent_data, chart_reagent_data, 15, 13)
#绘制直方图，这里写入的位置信息需要手动确认
barchar(write_reagent_data, addChart, 16, 28, 15, 16, "M31")
# main begin
if __name__=='__main__':
print("start deal with xls!")
#待处理源文件全名
file_full_name = '公司的真实销售.xlsx'
if not os.path.exists(file_full_name):
print("failed, file not exist!")
sys.exit(0)
os.system("pause") #等待用户手动确认结果
file_name = os.path.splitext(file_full_name)[0]
#最终处理完后输出的文件名
final_file = file_name + '_final.xlsx'
reagent_deal(file_full_name, final_file)
print("success!")
os.system("pause") #等待用户手动确认结果三、使用
1、目录下放置需要分析的xlsx文件
2、excel 测试数据，处理需求是“按照月份，统计型号对应的金额，每个工作表都有相应数据，而工作表多达二十多个”
3、运行方式1：直接点击py文件即可
4、运行方式2：在文件夹空白位置下，shift+鼠标右击，打开cmd 输入命令行执行
5、输出结果展示
表1：
表2：
表3：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
