首页 >后端开发 >Python教程 >python操作xlsx文件的包openpyxl实例

python操作xlsx文件的包openpyxl实例

不言原创: 2018-05-03 11:56:482734浏览

下面为大家分享一篇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;) #保存文件

单元格字体等也可以使用相应的模块去设置。

python操作xlsx文件的包openpyxl实例

相关文章