python操作xlsx檔案的套件openpyxl實例-Python教學-PHP中文網

python操作xlsx檔案的套件openpyxl實例

不言

發布： 2018-05-03 11:56:48

原創

2737 人瀏覽過

下面要為大家分享一篇python操作xlsx檔的套件openpyxl實例，具有很好的參考價值，希望對大家有幫助。一起來看看吧

Python擴充程式庫openpyxl，可以操作07版以上的xlsx檔。可建立工作簿、選擇活動工作表、寫入儲存格數據，設定儲存格字體顏色、邊框樣式，合併儲存格，設定儲存格背景等等。

需要增加可以顏色進入套件安裝目錄的

your_pthon_path/site-packages/openpyxl/styles

登入後複製

修改colors.py檔案下的

COLOR_INDEX = (
 &#39;00000000&#39;, &#39;00FFFFFF&#39;, &#39;00FF0000&#39;, &#39;0000FF00&#39;, &#39;000000FF&#39;, #0-4
 &#39;00FFFF00&#39;, &#39;00FF00FF&#39;, &#39;0000FFFF&#39;, &#39;00000000&#39;, &#39;00FFFFFF&#39;, #5-9
 &#39;00FF0000&#39;, &#39;0000FF00&#39;, &#39;000000FF&#39;, &#39;00FFFF00&#39;, &#39;00FF00FF&#39;, #10-14
 &#39;0000FFFF&#39;, &#39;00800000&#39;, &#39;00008000&#39;, &#39;00000080&#39;, &#39;00808000&#39;, #15-19
 &#39;00800080&#39;, &#39;00008080&#39;, &#39;00C0C0C0&#39;, &#39;00808080&#39;, &#39;009999FF&#39;, #20-24
 &#39;00993366&#39;, &#39;00FFFFCC&#39;, &#39;00CCFFFF&#39;, &#39;00660066&#39;, &#39;00FF8080&#39;, #25-29
 &#39;000066CC&#39;, &#39;00CCCCFF&#39;, &#39;00000080&#39;, &#39;00FF00FF&#39;, &#39;00FFFF00&#39;, #30-34
 &#39;0000FFFF&#39;, &#39;00800080&#39;, &#39;00800000&#39;, &#39;00008080&#39;, &#39;000000FF&#39;, #35-39
 &#39;0000CCFF&#39;, &#39;00CCFFFF&#39;, &#39;00CCFFCC&#39;, &#39;00FFFF99&#39;, &#39;0099CCFF&#39;, #40-44
 &#39;00FF99CC&#39;, &#39;00CC99FF&#39;, &#39;00FFCC99&#39;, &#39;003366FF&#39;, &#39;0033CCCC&#39;, #45-49
 &#39;0099CC00&#39;, &#39;00FFCC00&#39;, &#39;00FF9900&#39;, &#39;00FF6600&#39;, &#39;00666699&#39;, #50-54
 &#39;00969696&#39;, &#39;00003366&#39;, &#39;00339966&#39;, &#39;00003300&#39;, &#39;00333300&#39;, #55-59
 &#39;00993300&#39;, &#39;00993366&#39;, &#39;00333399&#39;, &#39;00333333&#39;, &#39;System Foreground&#39;, &#39;System Background&#39; #60-64
 &#39;00D2B48C&#39;, &#39;0087CEFA&#39;, &#39;0000BFFF&#39; #自己添加的
)

登入後複製

以00 16進位RGB顏色碼的形式即可

自己寫的一個產生xlsx檔的程式碼：

#!/usr/bin/env python
#-*- coding: utf-8 -*-
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import colors,Font,Border,Side,PatternFill,Alignment
wb = Workbook() #创建工作簿
ws = wb.active #激活工作簿
ws.merge_cells(&#39;C3:E3&#39;) #合并单元格
ws.merge_cells(&#39;C4:E4&#39;)
ws.merge_cells(&#39;C20:I20&#39;)
ws.merge_cells(&#39;C21:I21&#39;)
left, right, top, bottom = [Side(style=&#39;thin&#39;, color=&#39;000000&#39;)]*4 #设置单元格边框属性
border = Border(left=left, right=right, top=top, bottom=bottom) #设置单元格边框格式
fill1 = PatternFill(start_color = &#39;FFFF00&#39;, end_color = &#39;FFFF00&#39;, fill_type = &#39;solid&#39;) #设置单元格背景色
fill2 = PatternFill(start_color = &#39;D2B48C&#39;, end_color = &#39;D2B48C&#39;, fill_type = &#39;solid&#39;)
fill3 = PatternFill(start_color = &#39;00BFFF&#39;, end_color = &#39;00BFFF&#39;, fill_type = &#39;solid&#39;)
fill4 = PatternFill(start_color = &#39;FF0000&#39;, end_color = &#39;FF0000&#39;, fill_type = &#39;solid&#39;)
align1 = Alignment(horizontal=&#39;center&#39;, vertical=&#39;center&#39;) #设置文本对齐
align2 = Alignment(horizontal=&#39;left&#39;, vertical=&#39;center&#39;)
for i in range(3,22):
 for col in &#39;CDEFGHIJK&#39;:
 ws[col+str(i)].border = border #给每个单元格设置相应的格式 
 #ws[col+str(3)].fill = fill1
 #ws[col+str(i)].alignment = align
for col in &#39;CDEFGHIJK&#39;:
 ws[col+str(3)].fill = fill1
 ws[col+str(20)].fill = fill3
 ws[col+str(21)].fill = fill4
 for i in range(4,20):
 ws[col+str(i)].fill = fill2
for col in &#39;CDEFGHIJK&#39;:
 ws[col+str(3)].alignment = align1
for i in range(4,22):
 for col in &#39;CDE&#39;:
 ws[col+str(i)].alignment = align2
for col in &#39;CDEFGHIJK&#39;:
 ws[col+str(3)] = &#39;test1&#39; #单元格赋值
for i in range(3,22):
 for col in &#39;CDE&#39;:
 if i in range(5,20) and col == &#39;C&#39;:
  pass
 else:
  ws[col+str(i)] = &#39;test2&#39;
for i in range(4,20):
 for col in &#39;EFGHIJK&#39;:
 ws[col+str(i)] = 50
for i in range(20,22):
 for col in &#39;JK&#39;:
 ws[col+str(i)] = 100
wb.save(&#39;test.xlsx&#39;) #保存文件

登入後複製

#單元格字體等也可以使用對應的模組去設定。