Article directory
- Install
- Used with openpyxl
- For other basic operations, see [openpyxl](https://blog.csdn.net/CaviaRain/article/details/134144189?spm=1001.2014.3001.5501)
- Insert image/internal link
- Insert chart
Installation
xlsxwriter is a non-standard library, so you need to install it yourself. The installation process is not difficult. Windows/Mac users can use pip to install it in the command line (CMD)/terminal (Terminal)
pip install xlsxwriter
Use with openpyxl
Since xlsxwriter is designed to create new files, it does not directly provide a method to open an existing worksheet. We can use the openpyxl module to open an existing worksheet and combine it with the functions of XlsxWriter to edit and write data.
Example: xlwt opens the existing document yxy.xlsx and creates a temporary document
import xlsxwriter import openpyxl workbook = openpyxl.load_workbook(filename='yxy.xlsx') #Open the file with openpyxl worksheetname = workbook.sheetnames #Read all worksheets tempbook = xlsxwriter.Workbook('tempbook.xlsx') #Open one with xlwt for sheetname in worksheetname: worksheet = workbook[sheetname] tempsheet = tempbook.add_worksheet(sheetname) for row in range(1,worksheet.max_row + 1): for col in range(1,worksheet.max_column + 1): cell_value = worksheet.cell(row=row,column=col).value tempsheet.write(row-1,col-1,cell_value) tempbook.close()
For other basic operations, see openpyxl
Insert image/internal link
import xlsxwriter import openpyxl workbook = openpyxl.load_workbook(filename='yxy.xlsx') worksheetname = workbook.sheetnames tempbook = xlsxwriter.Workbook('tempbook.xlsx') for sheetname in worksheetname: worksheet = workbook[sheetname] tempsheet = tempbook.add_worksheet(sheetname) for row in range(1,worksheet.max_row + 1): for col in range(1,worksheet.max_column + 1): cell_value = worksheet.cell(row=row,column=col).value tempsheet.write(row-1,col-1,cell_value) #The above is to open existing files tempsheet = tempbook.sheetnames ['Test Project'] tempsheet.insert_image('E1',r'C:\Users\Administrator\Desktop\Demo picture.png') #The second parameter is the absolute link of the image. Since the string contains multiple backslashes\, use the r prefix to represent the path string. tempsheet = tempbook.sheetnames ['Test Project'] tempsheet.write_url(20,0, "internal:%s!B3" % ("Test 2"), string="Click to jump to cell B3 of the Test 2 worksheet") #"20" and "0" respectively Refers to inserting content in row 21 and column 1, "%s!B3" % ("Test 2")" means that this link points to cell B3 of test 2 worksheet, "Click to jump to cell B3 of test 2 worksheet " is the text displayed tempbook.close()
After clicking the link
Insert chart
- Insert chart
chart = workbook.add_chart({<!-- -->'type': 'column','subtype': 'stacked'})
‘type’ refers to the chart type, ‘subtype’ refers to the subtype of some chart types
- area: Create an Area (solid line) style sheet.
- bar: Creates a bar style (transposed histogram) chart.
- column: Create column style (histogram) charts.
- line: Create a line chart.
- pie: Create a pie style chart.
- donut: Create a donut style sheet.
- scatter: Creates a scatter plot style graph.
- stock: Create a stock style chart.
- radar: Create a radar style sheet.
- Insert data
chart.add_series(options)
The optios format is {‘values’: ‘=worksheet name!
Column corresponding letters
Column corresponding letters
Columns correspond to letters and rows correspond to numbers:
Column corresponding letters
Column corresponding letters
The columns correspond to letters and the rows correspond to numbers, name’: series name’}
- insertion position
worksheet.insert_chart('A7', chart)
- Set X-axis Y-axis properties
chart.set_x_axis({<!-- --> 'name': 'x-axis name', 'name_font': {<!-- -->'size': 12, 'bold': True}, 'num_font': {<!-- -->'italic': True }, })
This is to set the attributes of the X-axis: the first parameter name refers to the name of the axis, name_font sets the font attribute of the The font attribute num_font refers to the axis number (that is, 1234 below the x-axis in the figure) is set in italics. Y-axis is similar
- Combine different types of charts
chart.combine(line_chart)
chart is the original table, line_chart is the table to be superimposed
- Set chart size
chart.set_size({<!-- -->'width': 720, 'height': 576}) chart.set_size({<!-- -->'x_scale': 1.5, 'y_scale': 2}) worksheet.insert_chart('E2', chart, {<!-- -->'x_offset': -10, 'y_offset': 5})
There are six parameters in the function: width, height, x_offset, y_offset
The first two represent width and height in pixels. The default chart width * height is 480 x 288 pixels.
The last two parameters are to set the type of image to move throughout the chart area. The meaning is explained in a table
- Set chart title
chart.set_title({<!-- -->'name': 'Common sequence'}) chart.set_title({<!-- --> 'name': 'Common sequence', 'overlay': True, 'layout': {<!-- --> 'x': 0.62, 'y': 0.24, } }) chart.set_title({<!-- -->'none': True}) #Turn off this default title and turn off all other set_title() options.
name refers to the title; overlay represents allowing the title to be overlaid on the chart and is usually used together with layout. layout sets the title’s position (x, y) in chart-relative units.
- Set legend
chart.set_legend({<!-- -->'none': True}) chart.set_legend({<!-- -->'position': 'none'}) chart.set_legend({<!-- -->'position': 'bottom'}) chart.set_legend({<!-- -->'font': {<!-- -->'size': 4, 'bold': True}}) chart.set_legend({<!-- -->'delete_series': [0, 3]})
none: Turn off the legend; the default is on. position: the position of the legend. font: The font attribute of the legend. delete_series: Delete the specified legend, presented in a list.
-
Set default chart style
Use the set_style(num) function to set the style of the chart to one of the 48 built-in styles available on the “Design” tab in Excel. The parameter num is one of the 48 built-in styles. -
Set chart area
chart.set_chartarea({<!-- --> 'fill': {<!-- -->'color': 'black'} })
fill: Set the solid fill properties of the chart area, such as color.
- Add a data table below the chart
Use the set_table() function to add a data table below the horizontal axis.
Examples
import xlsxwriter import openpyxl workbook = openpyxl.load_workbook(filename='yxy.xlsx') worksheetname = workbook.sheetnames tempbook = xlsxwriter.Workbook('tempbook.xlsx') for sheetname in worksheetname: worksheet = workbook[sheetname] tempsheet = tempbook.add_worksheet(sheetname) for row in range(1,worksheet.max_row + 1): for col in range(1,worksheet.max_column + 1): cell_value = worksheet.cell(row=row,column=col).value tempsheet.write(row-1,col-1,cell_value) #The above is to open existing files tempsheet = tempbook.sheetnames['Test 3'] chart = tempbook.add_chart({<!-- -->'type':'column'}) #Create histogram cols = ['A','B','C','D'] rows = [2,8] max_row = max(rows) min_row = min(rows) for col in cols: worksheet1 = workbook['Test 3'] colname = worksheet1['%s1' % col] #Since the xlwt module cannot read the file content, you need to use the workbook in openpyxl to process the title. Tempbook and tempsheet cannot be used here. chart.add_series({<!-- -->'values':'=Test 3!$%s$%s:$%s$%s' % (col,min_row,col,max_row), 'name':colname.value}) #Insert histogram data chart.set_title({<!-- -->'name': 'Common sequence'}) #Set table title chart.set_table() #Insert data table below the chart tempsheet.insert_chart('A9', chart) #Chart insertion position A9 tempbook.close()