Python使用openpyxl读取、修改excel文件及绘chart图（支持xlsx）

C:\Python27\Scripts>pip install openpyxl

        border=Border(left=Side(border_style=’thin’, color=’FF000000′),
right=Side(border_style=’thin’, color=’FF000000′),
top=Side(border_style=’thin’, color=’FF000000′),
bottom=Side(border_style=’thin’, color=’FF000000′))
workBook = openpyxl.load_workbook(os.path.join(fileDir, u”测试.xlsx”))

sheet = workBook.get_sheet_by_name(u”Statistics”)
sheet.cell(row=4, column=2).value = 235
sheet.cell(row=4, column=2).style = Style(border=border)
sheet.cell(‘A4’).value = datetime.date.today() – datetime.timedelta(days=2)
sheet.cell(‘A4’).number_format = “yyyy/mm/dd”
sheet.cell(‘A4′).style = Style(border=border, number_format=’yyyy/mm/dd’)

sheet.cell(“B5”).value = 235
sheet.cell(“B5”).style = Style(border=border)
sheet.cell(‘A5’).value = datetime.date.today() – datetime.timedelta(days=1)
sheet.cell(‘A5’).number_format = “yyyy/mm/dd”
sheet.cell(‘A5′).style = Style(border=border, number_format=’yyyy/mm/dd’)
sheet.cell(“B6”).value = 235
sheet.cell(“B6”).style = Style(border=border)
sheet.cell(‘A6’).value = datetime.date.today()
sheet.cell(‘A6’).number_format = “yyyy/mm/dd”
sheet.cell(‘A6′).style = Style(border=border, number_format=’yyyy/mm/dd’)
sheet.cell(“B7”).value = 235
sheet.cell(“B7”).style = Style(border=border)
sheet.cell(‘A7’).value = datetime.date.today() + datetime.timedelta(days=1)
sheet.cell(‘A7’).number_format = “yyyy/mm/dd”
sheet.cell(‘A7′).style = Style(border=border, number_format=’yyyy/mm/dd’)

# chart operation
sheetChart = workBook.get_sheet_by_name(u”Chart”)
chartTopPos = 5
chartHeight = 200
labels = Reference(sheet, (3, column_index_from_string(‘A’)), (sheet.max_row, column_index_from_string(‘A’)))
values = Reference(sheet, (3, column_index_from_string(‘B’)), (sheet.max_row, column_index_from_string(‘B’)))
series1 = Series(values, title=u”哈aaa”, labels=labels)
series2 = Series(values, title=u”bbb”, labels=labels)
self.Execl_Insert_Chart(sheetChart, u”哈CPU”, 10, chartTopPos, 1000, chartHeight, series1, series2)
chartTopPos += (chartHeight + 5)
self.Execl_Insert_Chart(sheetChart, u”Memory(GB)”, 10, chartTopPos, 1000, chartHeight, series1, series2)

# save file
workBook.save(os.path.join(fileDir, u”测试_new.xlsx”))

    def Execl_Insert_Chart(self, sheet, title, left, top, width, height, *series):
chart = LineChart()
chart.title = title
for serial in series:
chart.append(serial)
chart.drawing.left = left
chart.drawing.top = top
chart.drawing.width = width
chart.drawing.height = height
chart.margin_left = width – 100
sheet.add_chart(chart)