python-excel driver xlsxwriter

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

  1. 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.
  1. 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’}

  1. insertion position
worksheet.insert_chart('A7', chart)
  1. 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

  1. Combine different types of charts
chart.combine(line_chart)

chart is the original table, line_chart is the table to be superimposed

  1. 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

  1. 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.

  1. 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.

  1. 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.

  2. Set chart area

chart.set_chartarea({<!-- -->
    'fill': {<!-- -->'color': 'black'}
})

fill: Set the solid fill properties of the chart area, such as color.

  1. 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()