python下xlsxwriter与pandas的办公自动化 v20191022

走过弯路，刚开始做的时候是用openpyxl，这几天才发现xlsxwriter功能比较强大。

写个脚本实现自动化办公，主要参看xlsxwriter文档

1.xlsxwriter

首先利用pandas整理数据(利用pandas进行数据分析第二版)，原始表格如下

我们需要添加表格标题和添加图片，为重复利用脚本，写为函数。

# -*- coding: utf-8 -*-
"""
Created on Mon Sep 30 11:07:03 2019

@author: hgh
"""
'''
data 数据
workbook 工作表
worksheetname 工作簿
Merged_Cells 表的title
title 图的title
y_axis y轴名称
x_axis x轴名称
'''

def bar(data, workbook, worksheetname, Merged_Cells=None, title=None, y_axis=None, x_axis=None):
    x, y = data.shape
#    获取数据的大小    
    end_col = chr(ord('A') + y -1)
#    获取最大的col边界
       
    worksheet1 = workbook.add_worksheet(worksheetname)
#    建立一个sheet 
    merge_range = 'A1:' + str(end_col) + '1'
#    标题的位置
    format = workbook.add_format({'align': 'center'})
#    格式
    worksheet1.merge_range(merge_range, Merged_Cells, format)
#    合并标题所在的单元格

    
    col_num = 0
    for col in data:  
        worksheet1.write(1, col_num, col)
        worksheet1.write_column(2, col_num, data[col])
        col_num += 1
#    读取DataFrame文件进入worksheet
        
    all_format = 'A:' + str(end_col)    
    worksheet1.set_column(all_format, None, format)
#    设定全文居中
    
    format1 = workbook.add_format({'num_format': '0.00%', 'align': 'center'})
#    pandas将14%转为小数，必须将数字格式化        
    worksheet1.set_column(str(end_col) + ':' + str(end_col), None, format1)
    
    worksheet1.set_row(x+1, None, format1)
            
    chart = workbook.add_chart({'type': 'column'}) 
         
    for col in range(1, y-2):
        point = chr(ord('A') + col)
        categories = '='+ worksheetname +'!' + '$A$3:$A$' + str(x)    
        values = '='+ worksheetname +'!' + '$' + point + '$3:$'+ point +'$' + str(x)
        name = '='+ worksheetname +'!$'+ point +'$2'
        chart.add_series({'categories': categories,
                          'values': values, 
                          'data_labels': {'value': True},
                          'name': name})
                  
    chart.set_size({'width': 1000, 'height': 500})#设置表的大小
    chart.set_title({'name': title})#设置表名称
    chart.set_y_axis({'name': y_axis})#设置y轴的小标题
    chart.set_x_axis({'name': x_axis})#设置x轴的小标题
        
    worksheet1.insert_chart('H20', chart)

写完分析函数，我们可以有

import pandas as pd
import xlsxwriter

data = pd.read_excel(r'\test.xlsx', sheet_name='Sheet1')

workbook = xlsxwriter.Workbook(r'test_.xlsx')

bar(data=data1, workbook=workbook, worksheetname='Sheet1', Merged_Cells = 'Merged_Cells', title=None, y_axis=None, x_axis=None)

workbook.close()

运行，就可以得到

和图表

2.GUI

这里我选择了PySimpleGUI ，因为看起来比较简单。
我就直接copy cookbook的例子进行进行修改，例子为

import PySimpleGUI as sg
import sys

if len(sys.argv) == 1:
    layout = [[sg.Text('Document to open')],
             [sg.In(), sg.FileBrowse()],
             [sg.Open(), sg.Cancel()]]

    window = sg.Window('My Script', layout)
    event, values = window.Read()
    window.Close()

    fname = values[0]
    print(event, values)
else:
    fname = sys.argv[1]

if not fname:
    sg.Popup("Cancel", "No filename supplied")
    raise SystemExit("Cancelling: no filename supplied")
else:
    sg.Popup('The filename you chose was', fname)

注意：sg.Popup('The filename you chose was', fname)要最后执行。

import PySimpleGUI as sg
import sys
import os
import pandas as pd
import xlsxwriter
#from untitled1 import bar, pie

if len(sys.argv) == 1:
    layout = [[sg.Text('Document to open')],
             [sg.In(), sg.FileBrowse()],
             [sg.Open(), sg.Cancel()]]

    window = sg.Window('My Script', layout)
    event, values = window.Read()
    window.Close()

    fname = values[0]
    print(event, values)
else:
    fname = sys.argv[1]

if not fname:
    sg.Popup("Cancel", "No filename supplied")
    raise SystemExit("Cancelling: no filename supplied")
else:
    
    path = fname 
      
    newfilepath = os.path.join(os.path.dirname(path), os.path.basename(path).split('.')[0]+'_.' + os.path.basename(path).split('.')[1])    
    
    data1 = pd.read_excel(fname, sheet_name='Sheet1')
    
    
    
    workbook = xlsxwriter.Workbook(newfilepath, {'strings_to_formulas': True})
    
    def bar(data, workbook, worksheetname, Merged_Cells=None, title=None, y_axis=None, x_axis=None):
        x, y = data.shape
    #    获取数据的大小    
        end_col = chr(ord('A') + y -1)
    #    获取最大的col边界
           
        worksheet1 = workbook.add_worksheet(worksheetname)
    #    建立一个sheet 
        merge_range = 'A1:' + str(end_col) + '1'
    #    标题的位置
        format = workbook.add_format({'align': 'center'})
        format.set_font_name('宋体')
    #    格式
        worksheet1.merge_range(merge_range, Merged_Cells, format)
    #    合并标题所在的单元格
    
        
        col_num = 0
        for col in data:  
            worksheet1.write(1, col_num, col)
            worksheet1.write_column(2, col_num, data[col])
            col_num += 1
    #    读取DataFrame文件进入worksheet
            
        all_format = 'A:' + str(end_col)    
        worksheet1.set_column(all_format, None, format)
    #    设定全文居中
        
        format1 = workbook.add_format({'num_format': '0.00%', 'align': 'center'})
        format1.set_font_name('宋体')
            
        worksheet1.set_column(str(end_col) + ':' + str(end_col), None, format1)
        
        worksheet1.set_row(x+1, None, format1)
                
        chart = workbook.add_chart({'type': 'column'}) 
             
        for col in range(1, y-2):
            point = chr(ord('A') + col)
            categories = '='+ worksheetname +'!' + '$A$3:$A$' + str(x)    
            values = '='+ worksheetname +'!' + '$' + point + '$3:$'+ point +'$' + str(x)
            name = '='+ worksheetname +'!$'+ point +'$2'
            chart.add_series({'categories': categories,
                              'values': values, 
                              'data_labels': {'value': True},
                              'name': name})
                      
        chart.set_size({'width': 1000, 'height': 500})#设置表的大小
        chart.set_title({'name': title})#设置表名称
        chart.set_y_axis({'name': y_axis})#设置y轴的小标题
        chart.set_x_axis({'name': x_axis})#设置x轴的小标题
        chart.set_table({'show_keys': True})
            
        worksheet1.insert_chart('H20', chart)
    
    
    
    
    bar(data=data1, workbook=workbook, worksheetname='Sheet1', Merged_Cells = 'Merged_Cells', title=None, y_axis=None, x_axis=None)
    
    workbook.close()
    sg.Popup('The filename you chose was', fname)

3.pyinstaller

安装最新版pyinstaller，管理员权限打开Anaconda Prompt，输入两次进入上层目录

cd ..

转到C盘根目录，再输入

D:

转到你的文件跟目录盘根目录，再输入

你的文件跟目录

输入

cd 你的文件路径相对路径

要是提示utf-8错误，在你打包的命令行中先输入chcp 65001，然后再输入打包命令。

pyinstaller -Fw xxx.py

慢慢等，会成功的。

4. 后续

运行速度较慢，文件较大，即使我已经新建了一个只有PySimpleGUI、pandas 和xlsxwriter的python新环境。