0. Python Excel庫對(duì)比

1. Python xlrd 讀取 操作Excel

1.1 xlrd模塊介紹

• python操作excel主要用到xlrd和xlwt這兩個(gè)庫，即xlrd是讀excel，xlwt是寫excel的庫。

• 在UI自動(dòng)化或者接口自動(dòng)化中數(shù)據(jù)維護(hù)是一個(gè)核心，所以此模塊非常實(shí)用。

1.2 安裝xlrd模塊

• 到python官網(wǎng)下載http://pypi./pypi/xlrd模塊安裝，前提是已經(jīng)安裝了python 環(huán)境。

• 或者在cmd窗口 pip install xlrd

1.3 使用介紹

1. 常用單元格的數(shù)據(jù)類型

• empty（空的）
• string（text）
• number
• date
• boolean
• error
• blank（空白表格）

2. 導(dǎo)入模塊

import xlrd

3. 打開Excel文件讀取數(shù)據(jù)

4. 常用的函數(shù)

• excel中最重要的方法就是book和sheet的操作

table = data.sheets()[0]             #通過索引順序獲取
table = data.sheet_by_index(sheet_indx)  #通過索引順序獲取
table = data.sheet_by_name(sheet_name)  #通過名稱獲取

# 以上三個(gè)函數(shù)都會(huì)返回一個(gè)xlrd.sheet.Sheet()對(duì)象

names = data.sheet_names()        #返回book中所有工作表的名字
data.sheet_loaded(sheet_name or indx)    # 檢查某個(gè)sheet是否導(dǎo)入完畢

ncols = table.ncols
    # 獲取列表的有效列數(shù)

table.col(colx, start_rowx=0, end_rowx=None)
    # 返回由該列中所有的單元格對(duì)象組成的列表

table.col_slice(colx, start_rowx=0, end_rowx=None)
    # 返回由該列中所有的單元格對(duì)象組成的列表

table.col_types(colx, start_rowx=0, end_rowx=None)
    # 返回由該列中所有單元格的數(shù)據(jù)類型組成的列表

table.col_values(colx, start_rowx=0, end_rowx=None)
    # 返回由該列中所有單元格的數(shù)據(jù)組成的列表

1.4 實(shí)戰(zhàn)訓(xùn)練

import xlrd

xlsx = xlrd.open_workbook('./3_1 xlrd 讀取 操作練習(xí).xlsx')

# 通過sheet名查找：xlsx.sheet_by_name('sheet1')
# 通過索引查找：xlsx.sheet_by_index(3)
table = xlsx.sheet_by_index(0)

# 獲取單個(gè)表格值 (2,1)表示獲取第3行第2列單元格的值
value = table.cell_value(2, 1)
print('第3行2列值為',value)

# 獲取表格行數(shù)
nrows = table.nrows
print('表格一共有',nrows,'行')

# 獲取第4列所有值（列表生成式）
name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]
print('第4列所有的值：',name_list)

2. Python xlwt 寫入 操作Excel（僅限xls格式?。?/span>

2.1 pip安裝xlwt

2.2 使用xlwt創(chuàng)建新表格并寫入

# 3.2.2 使用xlwt創(chuàng)建新表格并寫入
def fun3_2_2():
    # 創(chuàng)建新的workbook（其實(shí)就是創(chuàng)建新的excel）
    workbook = xlwt.Workbook(encoding= 'ascii')

    # 創(chuàng)建新的sheet表
    worksheet = workbook.add_sheet('My new Sheet')

    # 往表格寫入內(nèi)容
    worksheet.write(0,0, '內(nèi)容1')
    worksheet.write(2,1, '內(nèi)容2')

    # 保存
    workbook.save('新創(chuàng)建的表格.xls')

2.3 xlwt 設(shè)置字體格式

2.4 xlwt 設(shè)置列寬

# 3.2.4 設(shè)置列寬
def fun3_2_4():
    # 創(chuàng)建新的workbook（其實(shí)就是創(chuàng)建新的excel）
    workbook = xlwt.Workbook(encoding= 'ascii')

    # 創(chuàng)建新的sheet表
    worksheet = workbook.add_sheet('My new Sheet')

    # 往表格寫入內(nèi)容
    worksheet.write(0,0, '內(nèi)容1')
    worksheet.write(2,1, '內(nèi)容2')

    # 設(shè)置列寬
    worksheet.col(0).width = 256*20

    # 保存
    workbook.save('新創(chuàng)建的表格.xls')

2.5 xlwt 設(shè)置行高

2.6 xlwt 合并列和行

# 3.2.6 合并列和行
def fun3_2_6():
    # 創(chuàng)建新的workbook（其實(shí)就是創(chuàng)建新的excel）
    workbook = xlwt.Workbook(encoding= 'ascii')

    # 創(chuàng)建新的sheet表
    worksheet = workbook.add_sheet('My new Sheet')

    # 往表格寫入內(nèi)容
    worksheet.write(0,0, '內(nèi)容1')
    
    # 合并 第1行到第2行 的 第0列到第3列
    worksheet.write_merge(1, 2, 0, 3, 'Merge Test')

    # 保存
    workbook.save('新創(chuàng)建的表格.xls')

2.7 xlwt 添加邊框

2.8 xlwt為單元格設(shè)置背景色

# 設(shè)置單元格背景色
def fun3_2_8():
    # 創(chuàng)建新的workbook（其實(shí)就是創(chuàng)建新的excel）
    workbook = xlwt.Workbook(encoding= 'ascii')

    # 創(chuàng)建新的sheet表
    worksheet = workbook.add_sheet('My new Sheet')

    # 往表格寫入內(nèi)容
    worksheet.write(0,0, '內(nèi)容1')

    # 創(chuàng)建樣式
    pattern = xlwt.Pattern()
    
    # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    
    # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,
    # 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow ,
    # almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
    pattern.pattern_fore_colour = 5
    style = xlwt.XFStyle()
    style.pattern = pattern

    # 使用樣式
    worksheet.write(2,1, '內(nèi)容2',style)

2.9 xlwt設(shè)置單元格對(duì)齊

3. Python xlutils 修改 操作Excel

3.1 pip安裝xlutils

pip install xlutils

3.2 xlutils拷貝源文件（需配合xlrd使用）

3.3 xlutils 讀取 寫入 （也就是修改）Excel 表格信息

# 3.3.3 xlutils讀取 寫入 Excel 表格信息
def fun3_3_3():
    # file_path：文件路徑，包含文件的全名稱
    # formatting_info=True：保留Excel的原格式（使用與xlsx文件）
    workbook = xlrd.open_workbook('3_3 xlutils 修改操作練習(xí).xlsx')
    
    new_workbook = copy(workbook)  # 將xlrd對(duì)象拷貝轉(zhuǎn)化為xlwt對(duì)象

    # 讀取表格信息
    sheet = workbook.sheet_by_index(0)
    col2 = sheet.col_values(1)  # 取出第二列
    cel_value = sheet.cell_value(1, 1)
    print(col2)
    print(cel_value)

    # 寫入表格信息
    write_save = new_workbook.get_sheet(0)
    write_save.write(0, 0, 'xlutils寫入！')

    new_workbook.save('new_test.xls')  # 保存工作簿

4. Python xlwings 讀取 寫入 修改 操作Excel

• xlwings能夠非常方便的讀寫Excel文件中的數(shù)據(jù)，并且能夠進(jìn)行單元格格式的修改

• 可以和matplotlib以及pandas無縫連接，支持讀寫numpy、pandas數(shù)據(jù)類型，將matplotlib可視化圖表導(dǎo)入到excel中。

• 可以調(diào)用Excel文件中VBA寫好的程序，也可以讓VBA調(diào)用用Python寫的程序。

• 開源免費(fèi)，一直在更新

4.1 pip安裝xlwings

4.2 基本操作

import xlwings as xw 

wb = app.books.open('example.xlsx')
#練習(xí)的時(shí)候建議直接用下面這條
#wb = xw.Book('example.xlsx')
#這樣的話就不會(huì)頻繁打開新的Excel

wb.close()

# 導(dǎo)入xlwings模塊
import xlwings as xw

# 打開Excel程序，默認(rèn)設(shè)置：程序可見，只打開不新建工作薄，屏幕更新關(guān)閉
app=xw.App(visible=True,add_book=False)
app.display_alerts=False
app.screen_updating=False

# 文件位置：filepath，打開test文檔，然后保存，關(guān)閉，結(jié)束程序
filepath=r'g:\Python Scripts\test.xlsx'
wb=app.books.open(filepath)
wb.save()
wb.close()
app.quit()

 import xlwings as xw
    
 app=xw.App(visible=True,add_book=False)
 wb=app.books.add()
    
 # wb就是新建的工作簿(workbook)，下面則對(duì)wb的sheet1的A1單元格賦值
 wb.sheets['sheet1'].range('A1').value='人生'
 wb.save(r'd:\test.xlsx')
 wb.close()
 app.quit()

4.3 引用工作薄、工作表和單元格

wb=xw.books['工作簿的名字']

sht=xw.books['工作簿的名字'].sheets['sheet的名字']
# 或者
wb=xw.books['工作簿的名字']
sht=wb.sheets[sheet的名字]

rng=xw.books['工作簿的名字'].sheets['sheet的名字']
# 或者
sht=xw.books['工作簿的名字'].sheets['sheet的名字']
rng=sht.range('A1')

rng = sht.range('a1')
#rng = sht['a1']
#rng = sht[0,0] 第一行的第一列即a1,相當(dāng)于pandas的切片

4.4 寫入&讀取數(shù)據(jù)

sht.range('a1').value = 'Hello'

• 默認(rèn)按行插入：A1:D1分別寫入1,2,3,4

sht.range('a1').value = [1,2,3,4]

• 按列插入：A2:A5分別寫入5,6,7,8

sht.range('a2:a5').value = [5,6,7,8]

sht.range('a2').options(transpose=True).value = [5,6,7,8]

• 多行輸入就要用二維列表了：

# 將A1的值，讀取到a變量中
a=sht.range('A1').value

• 選取一列的數(shù)據(jù)

rng = sht.range('a1').expand('table')
nrows = rng.rows.count

• 選取一行的數(shù)據(jù)

ncols = rng.columns.count
#用切片
fst_col = sht[0,:ncols].value

4.5 常用函數(shù)和方法

• wb.activate() 激活為當(dāng)前工作簿
• wb.fullname 返回工作簿的絕對(duì)路徑
• wb.name 返回工作簿的名稱
• wb.save(path=None) 保存工作簿，默認(rèn)路徑為工作簿原路徑，若未保存則為腳本所在的路徑
• wb. close() 關(guān)閉工作簿

# 引用Excel程序中，當(dāng)前的工作簿
wb=xw.books.acitve
# 返回工作簿的絕對(duì)路徑
x=wb.fullname
# 返回工作簿的名稱
x=wb.name
# 保存工作簿，默認(rèn)路徑為工作簿原路徑，若未保存則為腳本所在的路徑
x=wb.save(path=None)
# 關(guān)閉工作簿
x=wb.close()

# 引用當(dāng)前活動(dòng)工作表的單元格
rng=xw.Range('A1')
# 加入超鏈接
# rng.add_hyperlink(r'www.baidu.com','百度','提示：點(diǎn)擊即鏈接到百度')
# 取得當(dāng)前range的地址
rng.address
rng.get_address()
# 清除range的內(nèi)容
rng.clear_contents()
# 清除格式和內(nèi)容
rng.clear()
# 取得range的背景色,以元組形式返回RGB值
rng.color
# 設(shè)置range的顏色
rng.color=(255,255,255)
# 清除range的背景色
rng.color=None
# 獲得range的第一列列標(biāo)
rng.column
# 返回range中單元格的數(shù)據(jù)
rng.count
# 返回current_region
rng.current_region
# 返回ctrl + 方向
rng.end('down')
# 獲取公式或者輸入公式
rng.formula='=SUM(B1:B5)'
# 數(shù)組公式
rng.formula_array
# 獲得單元格的絕對(duì)地址
rng.get_address(row_absolute=True, column_absolute=True,include_sheetname=False, external=False)
# 獲得列寬
rng.column_width
# 返回range的總寬度
rng.width
# 獲得range的超鏈接
rng.hyperlink
# 獲得range中右下角最后一個(gè)單元格
rng.last_cell
# range平移
rng.offset(row_offset=0,column_offset=0)
#range進(jìn)行resize改變r(jià)ange的大小
rng.resize(row_size=None,column_size=None)
# range的第一行行標(biāo)
rng.row
# 行的高度，所有行一樣高返回行高，不一樣返回None
rng.row_height
# 返回range的總高度
rng.height
# 返回range的行數(shù)和列數(shù)
rng.shape
# 返回range所在的sheet
rng.sheet
#返回range的所有行
rng.rows
# range的第一行
rng.rows[0]
# range的總行數(shù)
rng.rows.count
# 返回range的所有列
rng.columns
# 返回range的第一列
rng.columns[0]
# 返回range的列數(shù)
rng.columns.count
# 所有range的大小自適應(yīng)
rng.autofit()
# 所有列寬度自適應(yīng)
rng.columns.autofit()
# 所有行寬度自適應(yīng)
rng.rows.autofit()

# 新建工作表
xw.sheets.add(name=None,before=None,after=None)
# 引用當(dāng)前活動(dòng)sheet
xw.sheets.active

4.6 數(shù)據(jù)結(jié)構(gòu)

# 將a1,a2,a3輸入第一列，b1,b2,b3輸入第二列
list1=[['a1’,'a2','a3'],['b1','b2','b3']]
sht.range('A1').value=list1

# 選取第一列
rng=sht. range('A1').expand('down')
rng.value=['a1','a2','a3']

# 選取表格
rng.sht.range('A1').expand('table')
rng.value=[['a1’,'a2','a3'],['b1','b2','b3']]

4.7 xlwings生成圖表

import xlwings as xw
app = xw.App()
wb = app.books.active
sht = wb.sheets.active
# 生成圖表的數(shù)據(jù)
sht.range('A1').value = [['時(shí)間', '數(shù)量'], ['1日', 2], ['2日', 1], ['3日', 3]
             , ['4日', 4], ['5日', 5], ['6日', 6]]
'''圖表類型參數(shù)，被注釋的那幾個(gè)，無法生成對(duì)應(yīng)的圖表'''
dic = {
  '3d_area': -4098,
  '3d_area_stacked': 78,
  '3d_area_stacked_100': 79,
  '3d_bar_clustered': 60,
  '3d_bar_stacked': 61,
  '3d_bar_stacked_100': 62,
  '3d_column': -4100,
  '3d_column_clustered': 54,
  '3d_column_stacked': 55,
  '3d_column_stacked_100': 56,
  '3d_line': -4101,
  '3d_pie': -4102,
  '3d_pie_exploded': 70,
  'area': 1,
  'area_stacked': 76,
  'area_stacked_100': 77,
  'bar_clustered': 57,
  'bar_of_pie': 71,
  'bar_stacked': 58,
  'bar_stacked_100': 59,
  'bubble': 15,
  'bubble_3d_effect': 87,
  'column_clustered': 51,
  'column_stacked': 52,
  'column_stacked_100': 53,
  'cone_bar_clustered': 102,
  'cone_bar_stacked': 103,
  'cone_bar_stacked_100': 104,
  'cone_col': 105,
  'cone_col_clustered': 99,
  'cone_col_stacked': 100,
  'cone_col_stacked_100': 101,
  'cylinder_bar_clustered': 95,
  'cylinder_bar_stacked': 96,
  'cylinder_bar_stacked_100': 97,
  'cylinder_col': 98,
  'cylinder_col_clustered': 92,
  'cylinder_col_stacked': 93,
  'cylinder_col_stacked_100': 94,
  'doughnut': -4120,
  'doughnut_exploded': 80,
  'line': 4,
  'line_markers': 65,
  'line_markers_stacked': 66,
  'line_markers_stacked_100': 67,
  'line_stacked': 63,
  'line_stacked_100': 64,
  'pie': 5,
  'pie_exploded': 69,
  'pie_of_pie': 68,
  'pyramid_bar_clustered': 109,
  'pyramid_bar_stacked': 110,
  'pyramid_bar_stacked_100': 111,
  'pyramid_col': 112,
  'pyramid_col_clustered': 106,
  'pyramid_col_stacked': 107,
  'pyramid_col_stacked_100': 108,
  'radar': -4151,
  'radar_filled': 82,
  'radar_markers': 81,
  # 'stock_hlc': 88,
  # 'stock_ohlc': 89,
  # 'stock_vhlc': 90,
  # 'stock_vohlc': 91,
  # 'surface': 83,
  # 'surface_top_view': 85,
  # 'surface_top_view_wireframe': 86,
  # 'surface_wireframe': 84,
  'xy_scatter': -4169,
  'xy_scatter_lines': 74,
  'xy_scatter_lines_no_markers': 75,
  'xy_scatter_smooth': 72,
  'xy_scatter_smooth_no_markers': 73
}
w = 385
h = 241
n = 0
x = 100
y = 10
for i in dic.keys():
  xx = x + n % 3*w  # 用來生成圖表放置的x坐標(biāo)。
  yy = y + n//3*h   # 用來生成圖表放置的y坐標(biāo)。
  chart = sht.charts.add(xx, yy)
  chart.set_source_data(sht.range('A1').expand())
  chart.chart_type = i
  chart.api[1].ChartTitle.Text = i
  n += 1
wb.save('chart_圖表')
wb.close()
app.quit()

4.8 實(shí)戰(zhàn)訓(xùn)練

1. xlwings 新建 Excel 文檔

# 3.4.3 xlwings 打開已存在的Excel文件
def fun3_4_3():
    # 新建Excle 默認(rèn)設(shè)置：程序可見，只打開不新建工作薄，屏幕更新關(guān)閉
    app = xw.App(visible=True, add_book=False)
    app.display_alerts = False
    app.screen_updating = False

    # 打開已存在的Excel文件
    wb=app.books.open('./3_4 xlwings 修改操作練習(xí).xlsx')

    # 保存工作簿
    wb.save('example_2.xlsx')

    # 退出工作簿
    wb.close()

    # 退出Excel
    app.quit()

相關(guān)文章閱讀：

6000字長(zhǎng)文，帶你用Python完成Excel的各種騷操作！

Python 操作 Excel 庫 xlwings 常用操作詳解！

詳解 Python 操作 Excel 的各種騷操作！

5. Python openpyxl 讀取 寫入 修改 操作Excel

• Workbook就是一個(gè)excel工作表；

• Sheet是工作表中的一張表頁；

• Cell就是簡(jiǎn)單的一個(gè)格。

from openpyxl import Workbook
wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()

# Save the file
wb.save('sample.xlsx')

5.1 openpyxl 基本操作

from  openpyxl import  Workbook 
# 實(shí)例化
wb = Workbook()
# 激活 worksheet
ws = wb.active

# 方式一：數(shù)據(jù)可以直接分配到單元格中(可以輸入公式)
ws['A1'] = 42
# 方式二：可以附加行，從第一列開始附加(從最下方空白處，最左開始)(可以輸入多行)
ws.append([1, 2, 3])
# 方式三：Python 類型會(huì)被自動(dòng)轉(zhuǎn)換
ws['A3'] = datetime.datetime.now().strftime('%Y-%m-%d')

# sheet 名稱可以作為 key 進(jìn)行索引
>>> ws3 = wb['New Title']
>>> ws4 = wb.get_sheet_by_name('New Title')
>>> ws is ws3 is ws4
True

# 方法一
>>> c = ws['A4']
# 方法二：row 行；column 列
>>> d = ws.cell(row=4, column=2, value=10)
# 方法三：只要訪問就創(chuàng)建
>>> for i in  range(1,101):
...         for j in range(1,101):
...            ws.cell(row=i, column=j)

wb.save('文件名稱.xlsx')

# 獲得最大列和最大行
print(sheet.max_row)
print(sheet.max_column)

• sheet.rows為生成器, 里面是每一行的數(shù)據(jù)，每一行又由一個(gè)tuple包裹。

• sheet.columns類似，不過里面是每個(gè)tuple是每一列的單元格。

from openpyxl.utils import get_column_letter, column_index_from_string

# 根據(jù)列的數(shù)字返回字母
print(get_column_letter(2))  # B
# 根據(jù)字母返回列的數(shù)字
print(column_index_from_string('D'))  # 4

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10]]

list(zip(*rows))

# out
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]

# 注意 方法會(huì)舍棄缺少數(shù)據(jù)的列(行)
rows = [
    ['Number', 'data1', 'data2'],
    [2, 40      ],    # 這里少一個(gè)數(shù)據(jù)
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]
# out
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]

• 下面的代碼指定了等線24號(hào)，加粗斜體，字體顏色紅色。直接使用cell的font屬性，將Font對(duì)象賦值給它。

bold_itatic_24_font = Font(name='等線', size=24, italic=True, color=colors.RED, bold=True)

sheet['A1'].font = bold_itatic_24_font

• 也是直接使用cell的屬性aligment，這里指定垂直居中和水平居中。除了center，還可以使用right、left等等參數(shù)

# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列寬
sheet.column_dimensions['C'].width = 30

• 所謂合并單元格，即以合并區(qū)域的左上角的那個(gè)單元格為基準(zhǔn)，覆蓋其他單元格使之稱為一個(gè)大的單元格。
• 相反，拆分單元格后將這個(gè)大單元格的值返回到原來的左上角位置。

• 合并后只可以往左上角寫入數(shù)據(jù)，也就是區(qū)間中:左邊的坐標(biāo)。
• 如果這些要合并的單元格都有數(shù)據(jù)，只會(huì)保留左上角的數(shù)據(jù)，其他則丟棄。換句話說若合并前不是在左上角寫入數(shù)據(jù)，合并后單元格中不會(huì)有數(shù)據(jù)。
• 以下是拆分單元格的代碼。拆分后，值回到A1位置

sheet.unmerge_cells('A1:C3')

5.2 openpyxl生成2D圖表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference

wb = Workbook(write_only=True)
ws = wb.create_sheet()

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=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, 'A10')

from copy import deepcopy

chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = 'bar'
chart2.title = 'Horizontal Bar Chart'
ws.add_chart(chart2, 'G10')

chart3 = deepcopy(chart1)
chart3.type = 'col'
chart3.style = 12
chart3.grouping = 'stacked'
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, 'A27')

chart4 = deepcopy(chart1)
chart4.type = 'bar'
chart4.style = 13
chart4.grouping = 'percentStacked'
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, 'G27')

wb.save('bar.xlsx')

5.3 openpyxl生成3D圖表

5.4 實(shí)戰(zhàn)訓(xùn)練

# 3.5.2 openpyxl 新建Excel
def fun3_5_2():
    wb = Workbook()

    # 注意：該函數(shù)調(diào)用工作表的索引(_active_sheet_index)，默認(rèn)是0。
    # 除非你修改了這個(gè)值，否則你使用該函數(shù)一直是在對(duì)第一張工作表進(jìn)行操作。
    ws = wb.active

    # 設(shè)置sheet名稱
    ws.title = 'New Title'

    # 設(shè)置sheet顏色
    ws.sheet_properties.tabColor = '1072BA'

    # 保存表格
    wb.save('保存一個(gè)新的excel.xlsx')

# 3.5.4 openpyxl 讀寫Excel
def fun3_5_4():
    wb = load_workbook('./3_5 openpyxl 修改操作練習(xí).xlsx')

    # 注意：該函數(shù)調(diào)用工作表的索引(_active_sheet_index)，默認(rèn)是0。
    # 除非你修改了這個(gè)值，否則你使用該函數(shù)一直是在對(duì)第一張工作表進(jìn)行操作。
    ws = wb.active

    # 讀取單元格信息
    cellB2_value = ws['B2'].value
    print('單元格B2內(nèi)容為：',cellB2_value)

    # 寫入單元格
    ws['A1'].value = 'OPENPYXL'

    # 保存表格
    wb.save('copy.xlsx')

6. Python xlswriter 寫入 操作Excel

• XlsxWriter是一個(gè)用來寫Excel2007和xlsx文件格式的python模塊。它可以用來寫文本、數(shù)字、公式并支持單元格格式化、圖片、圖表、文檔配置、自動(dòng)過濾等特性

• 優(yōu)點(diǎn)：功能更多、文檔高保真、擴(kuò)展格式類型、更快并可配置 缺點(diǎn)：不能用來讀取和修改excel文件

6.1 xlswriter基本操作

# 創(chuàng)建文件
workbook = xlsxwriter.Workbook('new_excel.xlsx') 

# 法一：
worksheet.write('A1', 'write something')
# 法二：
worksheet.write(1, 0, 'hello world')

worksheet.write(2, 1, '=sum(B1:B2)')

# 寫入日期
d = workbook.add_format({'num_format': 'yyyy-mm-dd'})
worksheet.write(0, 2, datetime.datetime.strptime('2017-09-13', '%Y-%m-%d'), d)

• 字體顏色：color
• 字體加粗：bold
• 字體大?。篺ont_site
• 日期格式：num_format
• 超鏈接：url
• 下劃線設(shè)置：underline
• 單元格顏色：bg_color
• 邊框：border
• 對(duì)齊方式：align

# 自定義格式
f = workbook.add_format({'border': 1, 'font_size': 13, 'bold': True, 'align': 'center','bg_color': 'cccccc'})
worksheet.write('A3', 'python excel', f)
worksheet.set_row(0, 40, f)
worksheet.set_column('A:E', 20, f)

# 合并單元格寫入
worksheet.merge_range(7,5, 11, 8, 'merge_range')

6.3 xlswriter 生成折線圖

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

import xlsxwriter

# 創(chuàng)建一個(gè)excel
workbook = xlsxwriter.Workbook('chart_line.xlsx')
# 創(chuàng)建一個(gè)sheet
worksheet = workbook.add_worksheet()
# worksheet = workbook.add_worksheet('bug_analysis')

# 自定義樣式，加粗
bold = workbook.add_format({'bold': 1})

# --------1、準(zhǔn)備數(shù)據(jù)并寫入excel---------------
# 向excel中寫入數(shù)據(jù)，建立圖標(biāo)時(shí)要用到
headings = ['Number', 'testA', 'testB']
data = [
    ['2017-9-1', '2017-9-2', '2017-9-3', '2017-9-4', '2017-9-5', '2017-9-6'],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],
]

# 寫入表頭
worksheet.write_row('A1', headings, bold)

# 寫入數(shù)據(jù)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

# --------2、生成圖表并插入到excel---------------
# 創(chuàng)建一個(gè)柱狀圖(line chart)
chart_col = workbook.add_chart({'type': 'line'})

# 配置第一個(gè)系列數(shù)據(jù)
chart_col.add_series({
    # 這里的sheet1是默認(rèn)的值，因?yàn)槲覀冊(cè)谛陆╯heet時(shí)沒有指定sheet名
    # 如果我們新建sheet時(shí)設(shè)置了sheet名，這里就要設(shè)置成相應(yīng)的值
    'name': '=Sheet1!$B$1',
    'categories': '=Sheet1!$A$2:$A$7',
    'values':   '=Sheet1!$B$2:$B$7',
    'line': {'color': 'red'},
})

# 配置第二個(gè)系列數(shù)據(jù)
chart_col.add_series({
    'name': '=Sheet1!$C$1',
    'categories':  '=Sheet1!$A$2:$A$7',
    'values':   '=Sheet1!$C$2:$C$7',
    'line': {'color': 'yellow'},
})

# 配置第二個(gè)系列數(shù)據(jù)(用了另一種語法)
# chart_col.add_series({
#     'name': ['Sheet1', 0, 2],
#     'categories': ['Sheet1', 1, 0, 6, 0],
#     'values': ['Sheet1', 1, 2, 6, 2],
#     'line': {'color': 'yellow'},
# })

# 設(shè)置圖表的title 和 x，y軸信息
chart_col.set_title({'name': 'The xxx site Bug Analysis'})
chart_col.set_x_axis({'name': 'Test number'})
chart_col.set_y_axis({'name':  'Sample length (mm)'})

# 設(shè)置圖表的風(fēng)格
chart_col.set_style(1)

# 把圖表插入到worksheet并設(shè)置偏移
worksheet.insert_chart('A10', chart_col, {'x_offset': 25, 'y_offset': 10})

workbook.close()

6.4 xlswriter 生成柱狀圖

6.5 xlswriter 生成餅圖

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

import xlsxwriter

# 創(chuàng)建一個(gè)excel
workbook = xlsxwriter.Workbook('chart_pie.xlsx')
# 創(chuàng)建一個(gè)sheet
worksheet = workbook.add_worksheet()

# 自定義樣式，加粗
bold = workbook.add_format({'bold': 1})

# --------1、準(zhǔn)備數(shù)據(jù)并寫入excel---------------
# 向excel中寫入數(shù)據(jù)，建立圖標(biāo)時(shí)要用到
data = [
    ['closed', 'active', 'reopen', 'NT'],
    [1012, 109, 123, 131],
]

# 寫入數(shù)據(jù)
worksheet.write_row('A1', data[0], bold)
worksheet.write_row('A2', data[1])

# --------2、生成圖表并插入到excel---------------
# 創(chuàng)建一個(gè)柱狀圖(pie chart)
chart_col = workbook.add_chart({'type': 'pie'})

# 配置第一個(gè)系列數(shù)據(jù)
chart_col.add_series({
    'name': 'Bug Analysis',
    'categories': '=Sheet1!$A$1:$D$1',
    'values': '=Sheet1!$A$2:$D$2',
    'points': [
        {'fill': {'color': '#00CD00'}},
        {'fill': {'color': 'red'}},
        {'fill': {'color': 'yellow'}},
        {'fill': {'color': 'gray'}},
    ],

})

# 設(shè)置圖表的title 和 x，y軸信息
chart_col.set_title({'name': 'Bug Analysis'})

# 設(shè)置圖表的風(fēng)格
chart_col.set_style(10)

# 把圖表插入到worksheet以及偏移
worksheet.insert_chart('B10', chart_col, {'x_offset': 25, 'y_offset': 10})
workbook.close()

6.6 實(shí)戰(zhàn)訓(xùn)練

7. Python win32com 讀取 寫入 修改 操作Excel

7.1 pip安裝win32com

pip install pypiwin32

7.2 Python使用win32com讀寫Excel

8. Python pandas 讀取 寫入 操作Excel

8.1 pip安裝pandas

pip install pandas

8.2 pandas 讀寫 Excel

作者：超級(jí)大洋蔥806

blog.csdn.net/u014779536/article/details/108182833

- EOF -