建站
咨詢(xún)
售后
建站咨詢(xún)
新聞中心
其實(shí)在openpyxl中也是可以用來(lái)做折線圖的，是不是很有意思呢？今天就讓我們一起來(lái)試試吧。
太湖ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)公司的ssl證書(shū)銷(xiāo)售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：028-86922220（備注：SSL證書(shū)合作）期待與您的合作！
代碼如下：
import os import openpyxl from datetime import date from openpyxl import Workbook from openpyxl.chart import ( Series, LineChart, Reference, ) def add_line_chart(title, wss, min_col, min_row, max_col, max_row): c1 = LineChart() c1.title = title # 圖的標(biāo)題 c1.style = 12 # 線條的style c1.y_axis.title = 'percent' # y坐標(biāo)的標(biāo)題 if 'IDC' not in title: c1.x_axis.number_format = 'd-mmm' # 規(guī)定日期格式 這是月,年格式 c1.x_axis.majorTimeUnit = "Months" # 規(guī)定日期間隔 注意days；Months大寫(xiě) c1.x_axis.title = "Date" # x坐標(biāo)的標(biāo)題 data = Reference(wss, min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row) # 圖像的數(shù)據(jù) 起始行、起始列、終止行、終止列 c1.add_data(data, titles_from_data=True, from_rows=True) dates = Reference(wss, min_col=2, min_row=1, max_col=max_col) c1.set_categories(dates) wss.add_chart(c1, "A6") # 將圖表添加到 sheet中 def save_data_to_excel(file_name, target_sheet_name): select_cursor = connect.cursor() select_sql = "select phone_company, record_date, record_percent from phone_statistics where record_company = '%s'" % target_sheet_name if target_sheet_name == "IDC": select_sql = "select phone_company, record_q, record_percent from phone_statistics where record_company = '%s'" % target_sheet_name select_cursor.execute(select_sql, ()) data_dic = {} all_date = {} all_phone_company = {} for item in select_cursor: if target_sheet_name == "IDC": data_dic[item[0] + '_' + item[1]] = item[2] else: if type(item[1]) == str: data_dic[item[0] + '_' + item[1]] = item[2] else: data_dic[item[0] + '_' + item[1].strftime("%Y-%m-%d")] = item[2] all_date[item[1]] = 1 all_phone_company[item[0]] = 1 if os.path.exists(file_name): wb = openpyxl.load_workbook(file_name) else: wb = Workbook() try: wb.remove_sheet(wb['Sheet']) except Exception as e: pass try: wb.remove_sheet(wb[target_sheet_name]) except Exception as e: pass try: sheet = wb[target_sheet_name] except Exception as e: sheet = wb.create_sheet() start_date_index = 'B' for each_date in all_date.keys(): if target_sheet_name == "IDC": sheet['%s1' % start_date_index] = each_date else: if type(each_date) == str: sheet['%s1' % start_date_index] = each_date else: sheet['%s1' % start_date_index] = each_date.strftime("%Y-%m-%d") start_date_index = chr(ord(start_date_index) + 1) start_name_index = 2 for each_name in all_phone_company.keys(): sheet['A%d' % start_name_index] = each_name start_name_index += 1 start_date_index = 'B' start_name_index = 2 for each_date in all_date.keys(): for each_name in all_phone_company.keys(): if target_sheet_name == "IDC": key = each_name + '_' + each_date if key in data_dic: sheet['%s%d' % (start_date_index, start_name_index)] = data_dic[key] else: if type(each_date) == str: key = each_name + '_' + each_date else: key = each_name + '_' + each_date.strftime("%Y-%m-%d") if key in data_dic: sheet['%s%d' % (start_date_index, start_name_index)] = data_dic[key] start_name_index += 1 start_date_index = chr(ord(start_date_index) + 1) start_name_index = 2 sheet.title = target_sheet_name sheet.column_dimensions['A'].width = 20 start_date_index = 'B' for each_date in all_date.keys(): sheet.column_dimensions[start_date_index].width = 13 start_date_index = chr(ord(start_date_index) + 1) add_line_chart(target_sheet_name.upper() + "'s Phone Statistics", sheet, 1, 2, len(all_date.keys()) + 1, min(15, len(all_phone_company.keys()) + 1)) wb.save(file_name) pass
成果：
補(bǔ)充知識(shí)：python plotly line chart 折線圖
# 1 折線圖數(shù)據(jù) # trace1 - 基本格式 # trace2 - 更多參數(shù) trace1 = go.Scatter( x = x1, y = y2, ) trace2 = go.Scatter( x = x2, y = y2, mode = 'lines', # 模式：lines 線，markers 點(diǎn)?？捎谩?”相連 name = 'line2', # 折線名，顯示于圖例 connectgaps = True # 連接缺失點(diǎn)兩端 默認(rèn)False line = dict( color = ('rgb(205, 12, 24)'), # 顏色 width = 4, #線寬 dash = 'dash') # 虛線： dash 一一，dot ···，dashdot 一·一 ) ) # 2 打包數(shù)據(jù) data = [trace1,trace2] # 3 格式 layout = dict(title = '折線', xaxis = dict(title = '時(shí)間'), # 橫軸坐標(biāo) yaxis = dict(title = '數(shù)量'), # 總軸坐標(biāo) legend=dict(x=1.1,y=1) # 圖例位置 ) # 4 打包數(shù)據(jù)+格式 fig = dict(data=data, layout=layout) # 5 畫(huà)圖 py.iplot(fig, filename='styled-line')
以上這篇openpyxl 插入折線圖的方法已經(jīng)分享給大家了，更多Python學(xué)習(xí)推薦:PyThon學(xué)習(xí)網(wǎng)教學(xué)中心
名稱(chēng)欄目：創(chuàng)新互聯(lián)Python教程：Python之openpyxl插入折線圖方法
分享鏈接：http://m.fisionsoft.com.cn/article/ccieigh.html
