{"id":397,"date":"2018-03-14T15:14:52","date_gmt":"2018-03-14T07:14:52","guid":{"rendered":"http:\/\/www.max-shu.com\/blog\/?p=397"},"modified":"2018-03-14T15:14:52","modified_gmt":"2018-03-14T07:14:52","slug":"python%e4%bd%bf%e7%94%a8openpyxl%e8%af%bb%e5%8f%96%e3%80%81%e4%bf%ae%e6%94%b9excel%e6%96%87%e4%bb%b6%e5%8f%8a%e7%bb%98chart%e5%9b%be%ef%bc%88%e6%94%af%e6%8c%81xlsx%ef%bc%89","status":"publish","type":"post","link":"http:\/\/www.max-shu.com\/blog\/?p=397","title":{"rendered":"Python\u4f7f\u7528openpyxl\u8bfb\u53d6\u3001\u4fee\u6539excel\u6587\u4ef6\u53ca\u7ed8chart\u56fe\uff08\u652f\u6301xlsx\uff09"},"content":{"rendered":"
\u5b89\u88c5\uff1a<\/div>\n
\n
\n
C:\\Python27\\Scripts>pip install openpyxl<\/div>\n
<\/div>\n
\u4f7f\u7528\u4f8b\u5b50\uff0c\u4e0d\u591a\u89e3\u91ca\uff1a<\/b><\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 border=Border(left=Side(border_style=’thin’, color=’FF000000′),
\nright=Side(border_style=’thin’, color=’FF000000′),
\ntop=Side(border_style=’thin’, color=’FF000000′),
\nbottom=Side(border_style=’thin’, color=’FF000000′))
\nworkBook = openpyxl.load_workbook(os.path.join(fileDir, u”\u6d4b\u8bd5.xlsx”))<\/p>\n

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

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

# chart operation
\nsheetChart = workBook.get_sheet_by_name(u”Chart”)
\nchartTopPos = 5
\nchartHeight = 200
\nlabels = Reference(sheet, (3, column_index_from_string(‘A’)), (sheet.max_row, column_index_from_string(‘A’)))
\nvalues = Reference(sheet, (3, column_index_from_string(‘B’)), (sheet.max_row, column_index_from_string(‘B’)))
\nseries1 = Series(values, title=u”\u54c8aaa”, labels=labels)
\nseries2 = Series(values, title=u”bbb”, labels=labels)
\nself.Execl_Insert_Chart(sheetChart, u”\u54c8CPU”, 10, chartTopPos, 1000, chartHeight, series1, series2)
\nchartTopPos += (chartHeight + 5)
\nself.Execl_Insert_Chart(sheetChart, u”Memory(GB)”, 10, chartTopPos, 1000, chartHeight, series1, series2)<\/p>\n

# save file
\nworkBook.save(os.path.join(fileDir, u”\u6d4b\u8bd5_new.xlsx”))<\/p><\/div>\n

<\/div>\n
\u00a0 \u00a0 def Execl_Insert_Chart(self, sheet, title, left, top, width, height, *series):
\nchart = LineChart()
\nchart.title = title
\nfor serial in series:
\nchart.append(serial)
\nchart.drawing.left = left
\nchart.drawing.top = top
\nchart.drawing.width = width
\nchart.drawing.height = height
\nchart.margin_left = width – 100
\nsheet.add_chart(chart)<\/div>\n
<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\u5b89\u88c5\uff1a C:\\Python27\\Scripts>pip install openpyxl \u4f7f\u7528\u4f8b\u5b50\uff0c\u4e0d\u591a …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[324,291,323,108,325],"class_list":["post-397","post","type-post","status-publish","format-standard","hentry","category-3","tag-chart","tag-excel","tag-openpyxl","tag-python","tag-325"],"views":2954,"_links":{"self":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/397","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=397"}],"version-history":[{"count":1,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/397\/revisions"}],"predecessor-version":[{"id":398,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/397\/revisions\/398"}],"wp:attachment":[{"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=397"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.max-shu.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}