Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how python uses xlsxwriter module to deal with excel files". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Module installation:

Pip install xlsxwriter

After installation, import the module directly in the file, and test the existence of BUG.

Import xlsxwriter

This module, which is used to write data to Excel, is known by its name, where the Excel version is limited to 2007 +.

Basic use of module

Next, complete a basic process of creating a file and writing data:

Import xlsxwriter# create workbook workbook = xlsxwriter.Workbook ('test .xlsx') # add worksheet worksheet = workbook.add_worksheet () # write hello world to A1 cell worksheet.write ('A1, 'Hello World') # close the Excel file workbook.close ()

The process of the code is to create a workbook, create a worksheet, and write data to the specified cell.

Write more style data

The above case is the simplest application, and we can extend it to add more styles.

The relevant comments have been written into the code and can be learned with the comments.

Import xlsxwriter# create workbook workbook = xlsxwriter.Workbook ('test .xlsx') # create worksheet worksheet = workbook.add_worksheet ('test Sheet') # set single column width worksheet.set_column (' Apura width, 20) # set bold style bold = workbook.add_format ({'bold': True}) # write data A1worksheet.write (' A1, 'Hello') # write data A2worksheet.write (' A2, 'world' Bold) # write data B1worksheet.write ('B1Hello, u' Hello in the world') workbook.close ()

The function set_column () that sets the column width in the above table, the functions that can be extended are:

Worksheet.set_column ("avanza B", 15): sets the width of the area column based on the column name

Worksheet.set_column ("0:1", 15): update column widths based on index

Worksheet.set_row (1,30): sets the height of a single row.

Other style extension

Only one bold style has been set above, and you can configure other styles

# set column header style head_format = workbook.add_format ({'bold': True, # bold' border': 1, # cell border width 'align':' left', # alignment 'valign':' vcenter' # font alignment 'fg_color':' # 03A9F4colors, # cell background color 'font_size': 12 # font size})

In addition to the overall configuration, you can also set it in the form of an object method, which is used to carry this parameter in the worksheet.write () method.

Workfomat = workbook.add_format () workfomat.set_bold (1) # set bold workfomat.set_border (1) # set border workfomat.set_num_format ('0.00') # format data to two decimal places workfomat.set_align (' center') # set horizontal alignment workfomat.set_bg_color ('# ff0000') # set cell foreground color # set add Coarse style bold = workbook.add_format ({'bold': True}) # write data A1worksheet.write (' A1' The write method in 'Hello', workfomat) xlsxwriter

In addition to the write () method, there are also some extension methods for this module, as follows:

Write_string (): write string

Write_number (): write a number

Write_blank (): writes formatted white space characters to the worksheet cell, leaving only the cell style.

Write_formula (): write formula

Write_datetime (): write time

Write_boolean (): writing Boolean values

Write_url (): write the link.

There are some unlisted, you can refer to the following figure:

If you want to insert a picture, refer to the following code:

# insert the picture. Parameter 1 is the cell, and parameter 2 is the image address worksheet.insert_image ('B2'. PNG2001.png') xlsxwriter closes the file

After working with the Excel file, remember to close the file as follows:

Workbook.close () other methods that need to be understood

There are also some functions in the xlsxwriter module, which can assist us in coding most of the time.

Write_row (row, col, data, cell_format=None): fixed columns, write data

Write_column (row, col, data, cell_format=None): row fixed, write data

Insert_textbox (row, col, text, options=None): cell write text box

Insert_chart (row, col, chart, options=None): insert a chart

Activate (): set active worksheet

Merge_range (first_row, first_col, last_row,last_col,data, cell_format=None): merge cells

Conditional_format (first_row, first_col, last_row, last_col,options=None): conditional format

Advantages and disadvantages of xlsxwriter module

Write function is very complete, in addition to basic settings, font settings, foreground color, background color, border settings, cell merging, formulas, cell comments, row height settings, column width settings.

Xlsxwriter supports large file writes in cases where the amount of data is particularly large, you can enable constant memory mode to start line-by-line instant writes, thus avoiding one-time reading of data into memory.

Shortcoming

It is not supported to read and modify Excel files by name. Xlsxwriter is a module for writing data, so it cannot read Excel files.

Does not support XLS files xlsxwriter does not support the creation of excel files ending with the xls suffix.

This is the end of the content of "how python uses the xlsxwriter module to deal with excel files". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.