Python OPENPYXL instance of the package that operates the xlsx file

Below for you to share a Python operation xlsx file package OPENPYXL instance, has a good reference value, I hope to be helpful to everyone. Come and see it together.

Python extension library OPENPYXL, you can manipulate the xlsx files above version 07. You can create workbooks, select active worksheets, write cell data, set cell font color, border style, merge cells, set cell backgrounds, and more.

You need to add color to the package installation directory.

Your_pthon_path/site-packages/openpyxl/styles

Modify the colors.py file under the

color_index = (' 00000000 ', ' 00FFFFFF ', ' 00ff0000 ', ' 0000ff00 ', ' 000000FF ', #0-4 ' 00ffff00 ', ' 00ff00ff ', ' 0000FFFF ', ' 00000000 ', ' 00FFFFFF ', #5-9 ' 00ff0000 ', ' 0000ff00 ', ' 000000FF ', ' 00ffff00 ', ' 00ff00ff ', #10 -14 ' 0000FFFF ', ' 00800000 ', ' 00008000 ', ' 00000080 ', ' 00808000 ', #15 -19 ' 00800080 ', ' 00008080 ', ' 00c0c0c0 ', ' 00808080 ', ' 009999FF ', #20 -24 ' 00993366 ', ' 00FFFFCC ', ' 00CCFFFF ', ' 00660066 ', ' 00ff8080 ', #25 -29 ' 000066CC ', ' 00CCCCFF ' , ' 00000080 ', ' 00ff00ff ', ' 00ffff00 ', #30 -34 ' 0000FFFF ', ' 00800080 ', ' 00800000 ', ' 00008080 ', ' 000000FF ', #35 -39 ' 0000CCFF ', ' 00CCFFFF ', ' 00CCFFCC ', ' 00ffff99 ', ' 0099CCFF ', #40 -44 ' 00ff99cc ', ' 00cc99ff ', ' 00ffcc99 ', ' 003366FF ', ' 0033CCCC ', #45 -49 ' 0099cc00 ', ' 00ffcc00 ', ' 00ff9900 ', ' 00ff6600 ', ' 00666699 ', #50-54 ' 00969696 ', ' 00003366 ', ' 00339966 ', ' 00003300 ', ' 0 0333300 ', #55 -59 ' 00993300 ', ' 00993366 ', ' 00333399 ', ' 00333333 ', ' System Foreground ', ' System Background ' #60 -64 ' 00d2b48 C ', ' 0087CEFA ', ' 0000BFFF ' #自己添加的) 

In the form of 00+16 RGB color code, you can

Write your own code to generate an xlsx file:

#!/usr/bin/env python#-*-coding:utf-8-*-import openpyxlfrom OPENPYXL import workbookfrom openpyxl.styles import Colors , FONT,BORDER,SIDE,PATTERNFILL,ALIGNMENTWB = Workbook () #创建工作簿ws = Wb.active #激活工作簿ws. Merge_cells (' C3:e3 ') # Merge cell ws.merge_cells (' c4:e4 ') ws.merge_cells (' c20:i20 ') ws.merge_cells (' c21:i21 ') left, right, top, bottom = [Side ( Style= ' thin ', color= ' 000000 ')]*4 #设置单元格边框属性border = Border (Left=left, Right=right, Top=top, Bottom=bottom) # Format cell Border fill1 = Patternfill (start_color = ' FFFF00 ', End_color = ' FFFF00 ', Fill_type = ' solid ') #设置单元格背景色fill2 = Patternfi  ll (Start_color = ' d2b48c ', End_color = ' d2b48c ', Fill_type = ' solid ') Fill3 = Patternfill (start_color = ' 00BFFF ', End_color = ' 00BFFF ', Fill_type = ' solid ') Fill4 = Patternfill (start_color = ' FF0000 ', End_color = ' FF0000 ', Fill_type = ' solid ') Ali GN1 = Alignment (horizontal= ' center ', vertical= ' center ') #设置文本对齐align2 = Alignment (horizontal= ' left ', vertical= ' Center ') for I in Range (3,22): for col in ' Cdefghijk ': ws[col+str (i)].border= Border #给每个单元格设置相应的格式 #ws [Col+str (3)].fill = Fill1 #ws [Col+str (i)].alignment = alignfor col in ' Cdefghijk ': ws[col+str ( 3)].fill = Fill1 ws[col+str ()].fill = Fill3 ws[col+str (+)].fill = Fill4 for i in Range (4,20): Ws[col+str (i)].fill = fil L2for col in ' Cdefghijk ': Ws[col+str (3)].alignment = Align1for i in range (4,22): for col in ' CDE ': ws[col+str (i)].alignmen t = align2for col in ' Cdefghijk ': Ws[col+str (3)] = ' test1 ' #单元格赋值for I in Range (3,22): for col in ' CDE ': If I in range (5,2  0) and Col = = ' C ': Pass Else:ws[col+str (i)] = ' test2 ' for I in Range (4,20): for col in ' Efghijk ': Ws[col+str (i)] = 50for I in range (20,22): for col in ' JK ': Ws[col+str (i)] = 100wb.save (' test.xlsx ') #保存文件

Cell fonts can also be set using the corresponding module.