python接入excel_python之Excel可視化操作

近段時間由于工作中需要對一些資料的整理和可視化顯示，故記下使用python操作Excel可視化操作，友善後續查閱。

一、資料導入Excel

Demo：#!/usr/bin/env python

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

# @File Name: python_excel.py

# @Time : 2019/7/25

# @Author : zhong

# @Blog : https://www.jianshu.com/search?q=keitwo&page=1&type=note

# @QQ交流 : 3227456102

import xlsxwriter # 導入子產品

workbook = xlsxwriter.Workbook('7月生活消費.xlsx') # 建立excel表

worksheet = workbook.add_worksheet('消費表') # 建立sheet（sheet的名稱為"sheet1"）

headings = ['Number', '交通費', '夥食費','通訊費'] # 設定表頭

data = [

['2019-7-1', '2019-7-2', '2019-7-3', '2019-7-4', '2019-7-5', '2019-7-6'],

[10, 40, 50, 20, 10, 50],

[30, 60, 70, 50, 40, 30],

[100,50,150,50,100,50],

] # 自己造的資料

worksheet.write_row('A1', headings)

worksheet.write_column('A2', data[0])

worksheet.write_column('B2', data[1])

worksheet.write_column('C2', data[2]) # 将資料插入到表格中

worksheet.write_column('C2', data[3])

workbook.close() # 将excel檔案儲存關閉，如果沒有這一行運作代碼會報錯

運作結果：

二、給資料添加格式和彙總

Demo:#!/usr/bin/env python

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

# @File Name: python_excel.py

# @Time : 2019/7/25

# @Author : zhong

# @Blog : https://www.jianshu.com/search?q=keitwo&page=1&type=note

# @QQ交流 : 3227456102

# 導入子產品

import xlsxwriter

# 建立excel表

workbook = xlsxwriter.Workbook('7月生活消費.xlsx')

# 建立sheet（sheet的名稱為"消費表"）

worksheet = workbook.add_worksheet('消費表')

# 設定表頭

headings = ['Number', '交通費', '夥食費','通訊費']

# 自己造的資料

data = [

('2019-7-1', '2019-7-2', '2019-7-3', '2019-7-4', '2019-7-5', '2019-7-6'),

(10, 40, 50, 20, 10, 50),

(30, 60, 70, 50, 40, 30),

(100,50,150,50,100,50),

]

worksheet.write_row('A1', headings)

# 添加全局格式

bold =workbook.add_format({'bold':True})

money = workbook.add_format({'num_format':'$#,##0'})

# 添加表頭附帶格式

# worksheet.write('B2','交通費',bold)

# worksheet.write('C2','夥食費',bold)

# worksheet.write('D2','通訊費',bold)

# 将資料插入到表格中

worksheet.write_column('A2', data[0])

worksheet.write_column('B2', data[1])

worksheet.write_column('C2', data[2])

worksheet.write_column('D2', data[3])

col = 0

# 添加一列求和計數

worksheet.write(col,4,'總消費',bold)

worksheet.write(col+1,4,'=SUM(B2+C2+D2)',money)

worksheet.write(col+2,4,'=SUM(B3+C3+D3)',money)

worksheet.write(col+3,4,'=SUM(B4+C4+D4)',money)

worksheet.write(col+4,4,'=SUM(B5+C5+D5)',money)

worksheet.write(col+5,4,'=SUM(B6+C6+D6)',money)

worksheet.write(col+6,4,'=SUM(B7+C7+D7)',money)

# 将excel檔案儲存關閉，如果沒有這一行運作代碼會報錯

workbook.close()

運作結果：

三、可視化圖表

線性圖示Demo:#!/usr/bin/env python

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

# @File Name: python_excel.py

# @Time : 2019/7/25

# @Author : zhong

# @Blog : https://www.jianshu.com/search?q=keitwo&page=1&type=note

# @QQ交流 : 3227456102

# 導入子產品

import xlsxwriter

# 建立excel表

workbook = xlsxwriter.Workbook('7月生活消費.xlsx')

# 建立sheet（sheet的名稱為"消費表"）

worksheet = workbook.add_worksheet('消費表')

# 設定表頭

headings = ['Number', '交通費', '夥食費','通訊費']

# 自己造的資料

data = [

('7月', '8月', '9月', '10月', '11月', '12月'),

(10, 40, 50, 20, 10, 50),

(30, 60, 70, 50, 40, 30),

(100,50,150,50,100,50),

]

worksheet.write_row('A1', headings)

# 添加全局格式

bold =workbook.add_format({'bold':True})

money = workbook.add_format({'num_format':'$#,##0'})

worksheet.write_column('A2', data[0]) # 将資料插入到表格中

worksheet.write_column('B2', data[1])

worksheet.write_column('C2', data[2])

worksheet.write_column('D2', data[3])

# --------2、生成圖表并插入到excel---------------

# 建立一個柱狀圖(line chart)

chart_col = workbook.add_chart({'type': 'line'})

# 配置第一個系列資料

chart_col.add_series({

# 我們建立sheet時設定了sheet名，這裡就要設定成相應的值

'name': '=消費表!$B$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$B$2:$B$7',

'line': {'color': 'red'},

})

# 配置第二個系列資料

chart_col.add_series({

'name': '=消費表!$C$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$C$2:$C$7',

'line': {'color': 'yellow'},

})

# 配置第二個系列資料(用了另一種文法)

# chart_col.add_series({

# 'name': ['消費表', 0, 2],

# 'categories': ['消費表', 1, 0, 6, 0],

# 'values': ['消費表', 1, 2, 6, 2],

# 'line': {'color': 'yellow'},

# })

# 配置第三個系列資料

chart_col.add_series({

'name': '=消費表!$D$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$D$2:$D$7',

'line': {'color': 'blue'},

})

# 設定圖表的title 和 x，y軸資訊

chart_col.set_title({'name': '下半年消費情況表'})

chart_col.set_x_axis({'name': '月份'})

chart_col.set_y_axis({'name': '價格(元)'})

# 設定圖表的風格

chart_col.set_style(1)

# 把圖表插入到worksheet并設定偏移

worksheet.insert_chart('A10', chart_col, {'x_offset': 25, 'y_offset': 10})

# col = 0

# # 添加一列求和計數

# worksheet.write(col,4,'總消費',bold)

# worksheet.write(col+1,4,'=SUM(B2+C2+D2)',money)

# worksheet.write(col+2,4,'=SUM(B3+C3+D3)',money)

# worksheet.write(col+3,4,'=SUM(B4+C4+D4)',money)

# worksheet.write(col+4,4,'=SUM(B5+C5+D5)',money)

# worksheet.write(col+5,4,'=SUM(B6+C6+D6)',money)

# worksheet.write(col+6,4,'=SUM(B7+C7+D7)',money)

# 将excel檔案儲存關閉，如果沒有這一行運作代碼會報錯

workbook.close()

運作結果：

柱狀圖Demo:

隻需要更改如下一行代碼即可：# 建立一個柱狀圖(line chart)

# chart_col = workbook.add_chart({'type': 'line'})

# 建立一個柱狀圖（column cahrt）

chart_col = workbook.add_chart({'type': 'column'})

運作結果：

柱狀圖+線性圖疊加顯示Demo：#!/usr/bin/env python

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

# @File Name: python_excel.py

# @Time : 2019/7/25

# @Author : zhong

# @Blog : https://www.jianshu.com/search?q=keitwo&page=1&type=note

# @QQ交流 : 3227456102

# 導入子產品

import xlsxwriter

# 建立excel表

workbook = xlsxwriter.Workbook('7月生活消費.xlsx')

# 建立sheet（sheet的名稱為"消費表"）

worksheet = workbook.add_worksheet('消費表')

# 設定表頭

headings = ['Number', '交通費', '夥食費','通訊費']

# 自己造的資料

data = [

('7月', '8月', '9月', '10月', '11月', '12月'),

(10, 40, 50, 20, 10, 50),

(30, 60, 70, 50, 40, 30),

(100,50,150,50,100,50),

]

worksheet.write_row('A1', headings)

# 添加全局格式

bold =workbook.add_format({'bold':True})

money = workbook.add_format({'num_format':'$#,##0'})

worksheet.write_column('A2', data[0]) # 将資料插入到表格中

worksheet.write_column('B2', data[1])

worksheet.write_column('C2', data[2])

worksheet.write_column('D2', data[3])

# --------2、生成圖表并插入到excel---------------

# 建立一個柱狀圖(line chart)

line_chart = workbook.add_chart({'type': 'line'})

# 建立一個柱狀圖（column cahrt）

#chart_col = workbook.add_chart({'type': 'column'})

# 配置第一個系列資料

line_chart.add_series({

# 我們建立sheet時設定了sheet名，這裡就要設定成相應的值

'name': '=消費表!$B$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$B$2:$B$7',

'line': {'color': 'red'},

})

# 配置第二個系列資料

line_chart.add_series({

'name': '=消費表!$C$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$C$2:$C$7',

'line': {'color': 'yellow'},

})

# 配置第二個系列資料(用了另一種文法)

# chart_col.add_series({

# 'name': ['消費表', 0, 2],

# 'categories': ['消費表', 1, 0, 6, 0],

# 'values': ['消費表', 1, 2, 6, 2],

# 'line': {'color': 'yellow'},

# })

# 配置第三個系列資料

line_chart.add_series({

'name': '=消費表!$D$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$D$2:$D$7',

'line': {'color': 'blue'},

})

# 設定圖表的title 和 x，y軸資訊

line_chart.set_title({'name': '下半年消費情況表'})

line_chart.set_x_axis({'name': '月份'})

line_chart.set_y_axis({'name': '價格(元)'})

# 設定圖表的風格

line_chart.set_style(1)

# 把圖表插入到worksheet并設定偏移

# worksheet.insert_chart('A10', line_chart, {'x_offset': 25, 'y_offset': 10})

"""------------------------------------------------------柱狀圖---------------------------------"""

# 建立一個柱狀圖（column chrt）a

column_chart = workbook.add_chart({'type': 'column'})

# 配置第一個系列資料

column_chart.add_series({

# 我們建立sheet時設定了sheet名，這裡就要設定成相應的值

'name': '=消費表!$B$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$B$2:$B$7',

'line': {'color': 'red'},

})

# 配置第二個系列資料

column_chart.add_series({

'name': '=消費表!$C$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$C$2:$C$7',

'line': {'color': 'yellow'},

})

# 配置第二個系列資料(用了另一種文法)

# column_chart.add_series({

# 'name': ['消費表', 0, 2],

# 'categories': ['消費表', 1, 0, 6, 0],

# 'values': ['消費表', 1, 2, 6, 2],

# 'line': {'color': 'yellow'},

# })

# 配置第三個系列資料

column_chart.add_series({

'name': '=消費表!$D$1',

'categories': '=消費表!$A$2:$A$7',

'values': '=消費表!$D$2:$D$7',

'line': {'color': 'blue'},

})

# 設定圖表的title 和 x，y軸資訊

column_chart.set_title({'name': '下半年消費情況表'})

column_chart.set_x_axis({'name': '月份'})

column_chart.set_y_axis({'name': '價格(元)'})

# 設定圖表的風格

column_chart.set_style(1)

# 把圖表插入到worksheet并設定偏移

worksheet.insert_chart('A10', column_chart, {'x_offset': 25, 'y_offset': 10})

# 柱狀圖和線性圖疊加一起顯示

column_chart.combine(line_chart)

# col = 0

# # 添加一列求和計數

# worksheet.write(col,4,'總消費',bold)

# worksheet.write(col+1,4,'=SUM(B2+C2+D2)',money)

# worksheet.write(col+2,4,'=SUM(B3+C3+D3)',money)

# worksheet.write(col+3,4,'=SUM(B4+C4+D4)',money)

# worksheet.write(col+4,4,'=SUM(B5+C5+D5)',money)

# worksheet.write(col+5,4,'=SUM(B6+C6+D6)',money)

# worksheet.write(col+6,4,'=SUM(B7+C7+D7)',money)

# 将excel檔案儲存關閉，如果沒有這一行運作代碼會報錯

workbook.close()

運作結果：

資料：xlsxwrite其他圖示制作示例見https://pandas-xlsxwriter-charts.readthedocs.io/chart_examples.html

https://www.cnblogs.com/lizm166/p/9449261.html