Python modifies Excel tabular data methods using the OPENPYXL library

This article mainly introduces the use of Python using the OPENPYXL library to modify the Excel table data method, has a certain reference value, now share to everyone, the need for friends can refer to

1, OPENPYXL Library can read and write xlsx format files, for XLS old format files can only use xlrd read, XLWT write to complete.

Simple Package class:

From OPENPYXL import load_workbookfrom OPENPYXL import workbookfrom openpyxl.chart import Barchart, Series, Reference, Bar  Chart3dfrom openpyxl.styles Import Color, Font, alignmentfrom openpyxl.styles.colors import BLUE, RED, GREEN, Yellowclass    Write_excel (object): Def __init__ (self,filename): self.filename = filename SELF.WB = Load_workbook (self.filename) self.ws = self.wb.active def write (self, coord, value): # eg:coord:A1 Self.ws.cell (coord). Value = value Self . Wb.save (self.filename) def merge (self, rangstring): # eg:rangstring:A1:E1 Self.ws.merge_cells (rangstring) Self . Wb.save (Self.filename) def cellstyle (self, coord, font, align): cell = Self.ws.cell (coord) Cell.font = Font cel l.alignment = Align Def makechart (self, title, pos, Width, height, col1, Row1, col2, Row2, Col3, ROW3, row4): ":p Ara M Title: Chart name pos: Chart position width: Chart Width Height: chart altitude ' ' data = Reference (self.ws, Min_col=col1, Min_ Row=row1, Max_col=col2, MAX_ROW=ROW2) cat = Reference (self.ws, Min_col=col3, MIN_ROW=ROW3, max_row=row4) chart = Barchart3d () chart.title = Title Chart.width = Width chart.height = height chart.add_data (data=data, titles_from_data=true) chart.set_c Ategories (CAT) Self.ws.add_chart (chart, POS) self.wb.save (Self.filename)

Simple to use:

1. New Excel file Processing

WB = Workbook () #创建工作簿 ws = wb.active# active sheet ws1 = Wb.create_sheet ("Mysheet") #创建mysheet表 ws.title = "New title" #表明改为New titl E Ws.sheet_properties.tabColor = "1072BA" #颜色 ws[' A4 ') = 4# Assignment d = Ws.cell (row=4, column=2, value=10) #赋值 cell_range = ws[' A1 ': ' C2 '] #选择单元格区域 wb.save (' test.xlsx ') #保存

2, already has the Excel file processing

A. Modify Excel data

WR = Write_excel (' d:\demo.xlsx ') wr.write (' A2 ', ' hello ')

B, Merge cells

Wr.merge (' a1:b3 ')

C, cell join style, such as font, color and other properties

Cell B2 Set Arial, number 14th, red, auto wrap, horizontal center, vertical Center

Font = font (name=u ' Arial ', size=14, color=red, bold=true) align = Alignment (horizontal= ' center ', vertical= ' center ') Wr.cellstyle (' B2 ', font, align)

D. Create 3d histogram

rows = [   (None, $), ("   Apples", 5, 4), ("   oranges", 6, 2), (   "Pears", 8, 3)] for  row in rows:
  ws.append (Row)  Wr.makechart (u "3D Bar Chart", ' E5 ', 12.5, 7, 2, 1, 3, 4, 1, 2, 4)

Can create 3d histogram and line chart, very useful.