【python】【xlsxwriter】将数据存入excle并绘制chart

最近在工作中遇到一个问题，就是需要将UT测试获取到的数据以图表的形式显示

如果只是处理单文件，可以直接使用matplotlib模块，直接就可以绘制；但是需要同时绘制多个文件时就无法满足需求了。

此时我用了xlsxwriter模块来进行存储和绘制，其中主要的数据结构为OrderedDict，也就是有序的字典

OrderedDict{

file1：{key1:value1, key2:value2......} ,

file2：{key1:value1, key2:value2......} ,

file3：{key1:value1, key2:value2......} ,

}

创建一个这样的数据结构，但是问题应该是数据量多的时候可能会出问题。

if __name__ == '__main__':
	file_dir = os.path.abspath(os.getcwd())//获取当前路劲
	ls = os.listdir(file_dir)//或者当前路劲下的所有文件
	dic_power = OrderedDict ()
	result_path = file_dir + "\\result.xlsx"
	result_book = xlsxwriter.Workbook ( result_path )//创建一个workbook
	deal_data_to_chart_obj = deal_data_to_chart ( result_book )
	i=0
	for file in ls://遍历文件找到里面以.txt结尾的文件
		if(file[-4:] == ".txt"):
			index = 1
			dic_power[file]={}
			i = i + 1
			for line in open ( file )://打开文件,读取数据
				dic_power[file][index] = float(line)*1000
				index= index+1
			dic_power_cap_chart = {"sheet_name": "sheet"+str(i), "x_axis": "Time(s)",
								   "y_axis": "Power(mA)"}
			deal_data_to_chart_obj.make_chart ( dic_power[file],file ,dic_power_cap_chart )
	result_book.close ()

睡了一觉，起来觉得上面应该改一下title

dic_power_cap_chart = {
 "sheet_name": "sheet"+str(i), "x_axis": "Time(s)",
                  "y_axis": "Power(mA)","title":"UT_Power"}

下面是主要绘制的一个类

class deal_data_to_chart(object):
	def __init__(self,result_book):
		self.result_book = result_book

	def make_chart(self,dic_data,file_name,dic_chart):
		sheet1 = self.result_book.add_worksheet ( dic_chart["sheet_name"])//添加一个sheet
		chart_col = self.result_book.add_chart ({'type':'scatter','subtype': 'straight'})//设置风格为散点且以线连接
		list_colums_key = []
		list_colums_value = []
		sheet1.write ( "A1", dic_chart["x_axis"] )
		sheet1.write ( "B1", dic_chart["y_axis"] )
		sheet1.write ("D1", file_name )
		for x in dic_data.keys():
			list_colums_key.append (x/84)//这里需要将X数据稍作处理
			list_colums_value.append (dic_data[x])
		sheet1.write_column ( "A2", list_colums_key )
		sheet1.write_column ( "B2", list_colums_value )//以上为将数据写入表格，下面的就是开始绘图了
		chart_col.add_series ( {
			"name": dic_chart["sheet_name"],//划线的图标
			'categories': '='+dic_chart["sheet_name"]+'!$A$2:$A$'+str(len(list_colums_key)),//x的值
			'values': '='+dic_chart["sheet_name"]+'!$B$2:$'  + "B$" + str (len ( list_colums_value )),//y的值
			'line': {'color': "#1874CD",'width': 1.4},//设置线条的颜色和粗细
		} )
		chart_col.set_x_axis ( {'name':dic_chart["x_axis"]} )//设置坐标轴
		chart_col.set_y_axis ( {'name': dic_chart["y_axis"]} )
		sheet1.insert_chart ( "D3", chart_col,{'x_offset':0, 'y_offset': 0,'x_scale': 1.5, 'y_scale': 1.5})//拉伸1.5倍

在insert_chart前面一句上，设置title

chart_col.set_title ( {
 'name': dic_chart["title"]} )

结果如下所示：

主要还是使用xlsxwriter的api接口：http://xlsxwriter.readthedocs.io/chart.html

XlsxWriter

• Contents 
• Page 
• « The Format Class
• The Chartsheet Class »

The Chart Class

The Chart module is a base class for modules that implement charts in XlsxWriter. The information in this section is applicable to all of the available chart subclasses, such as Area, Bar, Column, Doughnut, Line, Pie, Scatter, Stock and Radar.

A chart object is created via the Workbook add_chart() method where the chart type is specified:

chart = workbook.add_chart({
        'type': 'column'})

It is then inserted into a worksheet as an embedded chart using the insert_chart() Worksheet method:

worksheet.insert_chart('A7', chart)

Or it can be set in a chartsheet using the set_chart() Chartsheet method:

chartsheet = workbook.add_chartsheet()
# ...
chartsheet.set_chart(chart)

The following is a small working example or adding an embedded chart:

import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

# Create a new Chart object.
chart = workbook.add_chart({
        'type': 'column'})

# Write some data to add to plot on the chart.
data = [
    [1, 2, 3, 4, 5],
    [2, 4, 6, 8, 10],
    [3, 6, 9, 12, 15],
]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
worksheet.write_column('C1', data[2])

# Configure the chart. In simplest case we add one or more data series.
chart.add_series({
        'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({
        'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({
        'values': '=Sheet1!$C$1:$C$5'})

# Insert the chart into the worksheet.
worksheet.insert_chart('A7', chart)

workbook.close()

The supported chart types are:

• area: Creates an Area (filled line) style chart.
• bar: Creates a Bar style (transposed histogram) chart.
• column: Creates a column style (histogram) chart.
• line: Creates a Line style chart.
• pie: Creates a Pie style chart.
• doughnut: Creates a Doughnut style chart.
• scatter: Creates a Scatter style chart.
• stock: Creates a Stock style chart.
• radar: Creates a Radar style chart.

Chart subtypes are also supported for some chart types:

workbook.add_chart({
        'type': 'bar', 'subtype': 'stacked'})

The available subtypes are:

area
    stacked
    percent_stacked

bar
    stacked
    percent_stacked

column
    stacked
    percent_stacked

scatter
    straight_with_markers
    straight
    smooth_with_markers
    smooth

radar
    with_markers
    filled

Methods that are common to all chart types are documented below. See Working with Charts for chart specific information.

chart.add_series()

add_series ( options )

Add a data series to a chart.

Parameters:	options (dict) – A dictionary of chart series options.

In Excel a chart series is a collection of information that defines which data is plotted such as values, axis labels and formatting.

For an XlsxWriter chart object the add_series() method is used to set the properties for a series:

chart.add_series({
         
    'categories': '=Sheet1!$A$1:$A$5',
    'values':     '=Sheet1!$B$1:$B$5',
    'line':       {
         'color': 'red'},
})

# Or using a list of values instead of category/value formulas:
#     [sheetname, first_row, first_col, last_row, last_col]
chart.add_series({
         
    'categories': ['Sheet1', 0, 0, 4, 0],
    'values':     ['Sheet1', 0, 1, 4, 1],
    'line':       {
         'color': 'red'},
})

As shown above the categories and values can take either a range formula such as =Sheet1!$A$2:$A$7 or, more usefully when generating the range programmatically, a list with zero indexed row/column values.

The series options that can be set are:

• values: This is the most important property of a series and is the only mandatory option for every chart object. This option links the chart with the worksheet data that it displays. The data range can be set using a formula as shown in the first example above or using a list of values as shown in the second example.

• categories: This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the categoriesproperty is optional and the chart will just assume a sequential series from 1..n.

• name: Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn’t supplied it will default to Series 1..n. The name can also be a formula such as =Sheet1!$A$1 or a list with a sheetname, row and column such as ['Sheet1', 0, 0].

• line: Set the properties of the series line type such as color and width. See Chart formatting: Line.

• border: Set the border properties of the series such as color and style. See Chart formatting: Border.

• fill: Set the solid fill properties of the series such as color. See Chart formatting: Solid Fill.

• pattern: Set the pattern fill properties of the series. See Chart formatting: Pattern Fill.

• gradient: Set the gradient fill properties of the series. See Chart formatting: Gradient Fill.

• marker: Set the properties of the series marker such as style and color. See Chart series option: Marker.

• trendline: Set the properties of the series trendline such as linear, polynomial and moving average types. See Chart series option: Trendline.

• smooth: Set the smooth property of a line series.

• y_error_bars: Set vertical error bounds for a chart series. See Chart series option: Error Bars.

• x_error_bars: Set horizontal error bounds for a chart series. See Chart series option: Error Bars.

• data_labels: Set data labels for the series. See Chart series option: Data Labels.

• points: Set properties for individual points in a series. See