13 - read and write Excel files in Python

In the past, the English word corresponding to Business Analysis was Business Analysis, and the analysis tool we used was excel. Later, the amount of data was large, and excel couldn't cope with it (the maximum number of rows supported by Excel was 1048576). People began to turn to analysis tools such as python and R

XlsxWriter xlrd&xlwt OpenPyXL Microsoft Excel API
introduce You can create XLSX files in Excel 2007 or later Namely python-excel , including xlrd,xlwt and xlutils The three modules provide reading, writing and other functions respectively You can read and write Excel 2007 XLSX and XLSM files Directly communicate with Microsoft Excel process through COM component, and call its various functions to realize the operation of Excel file
read
write
modify ⚠️
.xls
.xlsx ⚠️
Large file
function strong weak commonly Super strong
speed fast fast fast Super slow
system unlimited unlimited unlimited Windows + Excel
Usage scenario To create XLSX files < br / > you do not need to read existing files < br / > < br / > you need to implement complex functions < br / > the amount of data may be large < br / > you need to cross platform To read XLS or XLSX files < br / > to generate XLS files < br / > the functions required are not too complex < br / > cross platform is required To process XLSX files < br / > you need to modify existing files or constantly modify them during writing < br / > the required functions are complex < br / > the amount of data may be large < br / > you need to cross platform Need to deal with various file formats < br / > need to use particularly complex functions < br / > when modifying files, do not want to cause any accidental damage to the original information < br / > the amount of data is very small, or are willing to wait < br / > only used in Windows

XlsxWriter

Xlsx is a module used by python to construct xlsx files. You can write text, numbers, formulas and hyperlinks to Excel 2007 +.

It can complete the automatic construction of xlsx files, including: merging cells, making excel charts and other functions:

1. Advantages

1, Quite powerful

Relatively speaking, this is the most powerful tool besides Excel itself. For example, I use the settings it provides: font settings, foreground and background colors, border settings, view zoom, cell merging, autofilter, free panes, formulas, data validation, cell comments, row height and column width settings, etc.

2, Support large file writing

If the amount of data is very large, you can enable constant memory mode, which is a sequential write mode. When you get a row of data, you can write it to a row immediately, instead of keeping all the data in memory.

2. Disadvantages

1, Reading and modification are not supported

The author does not intend to make an XlsxReader to provide read operation. If you can't read it, you can't modify it. It can only be used to create new files. I use xlrd to read in the required information, and then use XlsxWriter to create a new file.

In addition, even if the Excel file is half created, the created content cannot be read (the information should be in, but there is no corresponding interface). Because its main method is write, not set. When you write data in a cell, there is still no way to read the written information unless you save the relevant content yourself. From this point of view, you can't read - > Modify - > write back. You can only write - > write - > write.

2, XLS files are not supported

Xls is the format used by Office 2013 or earlier. It is a binary format file. XLSX is a compressed package composed of a series of XML files (the last X represents XML). If you have to create a lower version XLS file, please move xlwt.

3, Pivot table is temporarily not supported

3. Basic operation method
  • Create excel file
import xlsxwriter

f = xlsxwriter.Workbook()    # Create excel file
  • Create worksheet
worksheet1 = f.add_worksheet('Operation log')  # The name of the worksheet is in parentheses
  • Add sheet style

Styles have many attributes. Please refer to for more specific styles Official documents https://xlsxwriter.readthedocs.io.

bold = f.add_format({
        'bold':  True,  # Bold font
        'border': 1,  # Cell border width
        'align': 'left',  # Horizontal alignment
        'valign': 'vcenter',  # Vertical alignment
        'fg_color': '#F4B084',  # Cell background color
        'text_wrap': True,  # Auto wrap
    })
  • Write single cell data
//Row: row, col: column, data: data to be written, bold: cell style
worksheet1.write(row, col, data, bold)
  • Write a whole row, a whole column
// A1: insert data from cell A1, insert by row, data: data to be written (format is a list), bold: cell style
worksheet1.write_row("A1",data,bold)

// A1: insert data from cell A1, insert by column, data: data to be written (format is a list), bold: cell style
worksheet1.write_column("A1",data,bold)
  • Insert picture
// The first parameter is the starting cell to insert, and the second parameter is the absolute path of the image file
worksheet1.insert_image('A1','f:\\1.jpg')
  • Write hyperlink
worksheet1.write_url(row, col, "internal:%s!A1" % ("Sheet name to associate"), string="Hyperlink display name")
  • Insert chart
workbook.add_chartsheet(type="")

In parameter type Refers to the chart type. An example of chart type is as follows:
[area: Area map,bar: Bar chart,column: histogram,doughnut: Ring diagram,line: Line chart,pie: Pie chart,scatter: Scatter diagram,radar: Radar chart,stock:Box diagram]
  • Get all worksheets of the current excel file
workbook.worksheets()

workbook.worksheets() is used to obtain all worksheets in the current workbook. The existence of this function facilitates the circular operation of worksheets. This command is useful if you want to enter a character 'Hello xlsxwriter' in the A1 cell of all worksheets in the current workbook.

  • Close excel file
workbook.close()

This command is the last command to use xlsxwriter to operate Excel. Be sure to close the file.

reference resources https://www.jianshu.com/nb/32...

case

import xlsxwriter

# New excel table
workbook = xlsxwriter.Workbook('D:/hello.xlsx')

# Create a new sheet (the name of the sheet is "sheet1")
worksheet = workbook.add_worksheet()

# Define header content
Title = ['example ID', 'Instance configuration', 'Instance name', 'region', 'Security group', 'EIP']

# Define Title Format
merge_format = workbook.add_format({
    'bold': True,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
    'fg_color': '#E0FFFF'
})

# Define header format
title_format = workbook.add_format({
    'bold': True,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
    'fg_color': '#87CEFF'
})

# Define content format
data_format = workbook.add_format({
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
    'fg_color': '#EED8AE'
})

# Quasi data
data = [['i-1', '1C1G', 'demo1', 'Shanghai', 'sg-1', '172.20.2.10'],
        ['i-2', '1C1G', 'demo2', 'Zhengzhou', 'sg-2', '172.20.2.11'],
        ['i-3', '1C1G', 'demo3', 'Beijing', 'sg-3', '172.20.2.12']]

# Merge A1 - F1 cells as the title. Pass in parameters: 1: merge cells, 2: title text, 3: Title format
worksheet.merge_range('A1:F1', 'ECS Information table', merge_format)

# Set the column width, and specify the column width of A - F as 25
worksheet.set_column('A:F', 25)

# Set the line number, specify the number of lines, height, A1 is 0, A2 is 1, and so on
worksheet.set_row(0, 60)

# write_row, write line, pass in parameters: 1: line, 2: content, 3: format
worksheet.write_row('A2', Title, title_format)
i = 3
for ECS in data:
    worksheet.write_row('A' + str(i), ECS, data_format)
    i += 1

# Save and close the excel file. If there is no such line, the code will report an error
workbook.close()

Online exercise: https://www.520mg.com/it

Tags: Python

Posted by thesaleboat on Thu, 12 May 2022 08:36:39 +0300