Python module Xlsxwriter

Official website Tutorial:http://xlsxwriter.readthedocs.io/tutorial

XLSX is the module that Python uses to construct xlsx files, and you can write Text,numbers,formulas formulas and hyperlinks hyperlinks to excel2007+.

The automated construction of xlsx files can be completed, including:

Merge cells, make Excel charts and more:

1,introduction:

Xlsxwriter supports a wide range of excle functions, is perfectly compatible with Excel, writes large files quickly and takes up only a small amount of memory

Do not support reading or changing existing Excel files

2, Installing:

sudo pip install xlsxwriter;

sudo easy_install xlsxwriter;

or source installation: http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz

3, using:

Import= xlsxwriter. Workbook (' hello.xlsx '= workbook.add_worksheet () # Build sheet, you can Work.add_worksheet (' employee ') to specify the sheet name, But the Chinese name will report Unicodedecodeerro error worksheet.write (' A1 ', ' Hello World ') # write workbook.close to A1 ()

Excel formula Calculation

# Some data we want to write to the worksheet.expenses = ([' Rent ', ' + ', [' Gas    ', ' +   ],    [' food ',  300] ,    [' Gym ', [    ],] # Start from the first cell. Rows and columns are zero indexed. Write by label is starting from 0, press absolute position ' A1 ' write is starting from 1 row = 0col = 0# iterate over the data and write it out row by Row.for item, cost in (expenses ):    worksheet.write (Row, col,     Item)    worksheet.write (Row, col + 1, cost)    row + = # Write a total using a fo Rmula.worksheet.write (row, 0, ' total ') worksheet.write (row, 1, ' =sum (B1:B4) ')    # call formula expression for Excel Workbook.close ()

Excel Custom Format:

ImportXlsxwriter#documents and sheet.Workbook = Xlsxwriter. Workbook ('expenses02.xlsx') Worksheet=Workbook.add_worksheet ()#ADD a bold format to highlight cells. Set bold, default is FalseBold = Workbook.add_format ({'Bold': True}) #ADD A number format for cells with money. Define number formatsMoney = Workbook.add_format ({'Num_format':'$#,# #0'}) #Write Some data headers with custom bold Blod format headerWorksheet.write ('A1','Item', Bold) Worksheet.write ('B1',' Cost', Bold)#Some data we want to write to the worksheet.Expenses = (     ['Rent', 1000],     [' Gas', 100],     [' Food', 300],     ['Gym', 50], ) #Start from the first cell below the headers.row = 1Col=0#iterate over the data and write it out row by row.  forItem, Costinch(expenses): Worksheet.write (Row, col, item) # Write Worksheet.write with default format (row, col+ 1, cost, Money) # write to row with custom money format+ = 1#Write A total using a formula.Worksheet.write (row, 0,' Total', Bold) worksheet.write (row,1,'=sum (B2:B5)', Money) workbook.close ()

Excel Write Time format

From datetime import datetime import Xlsxwriter # Create a workbook and add a worksheet. Workbook = Xlsxwriter. Workbook (' expenses03.xlsx ') worksheet = Workbook.add_worksheet () # Add a bold format to use to highlight cells. Bold = Workbook.add_format ({' Bold ': 1}) # Add a number format for cells with money. Money_format = Workbook.add_format ({' Num_format ': ' $#,# #0 '}) # Add an Excel date format. Date_format = Workbook.add_format ({' Num_format ': ' Mmmm d yyyy '}) # Adjust the column width. Worksheet.set_column (1, 1,) # Write some data headers.  Worksheet.write (' A1 ', ' Item ', bold) worksheet.write (' B1 ', ' Date ', bold) worksheet.write (' C1 ', ' cost ', bold) # Some data we Want to write to the worksheet. Expenses = ([' Rent ', ' 2013-01-13 ', +], [' Gas ', ' 2013-01-14 ', ' +], [' food ', ' 2013-01-16 ', +], [' G Ym ', ' 2013-01-20 ', [],] # Start from the first cell below the headers. row = 1 col = 0 for item, DATE_STR, cost in (expenses): # Convert The date string into a datetime Object. Date = Datetime.strptime (Date_str, "%y-%m-%d") worksheet.write_string (row, col, item) Workshee T.write_datetime (Row, col + 1, date, date_format) worksheet.write_number (row, col + 2, cost, Money_format) row + = 1 # Write A total using a formula. Worksheet.write (row, 0, ' total ', bold) worksheet.write (row, 2, ' =sum (C2:C5) ', Money_format) workbook.close ()

The @@@ write method provides a python type-to-Excel conversion, Xlsxwriter supports an Excel worksheet with a maximum of 1048576 rows of records, 16,384 column records, and more than one can choose to build a new sheet

Worksheet.write (0, 0, ' Hello ')          # write_string () worksheet.write (1, 0, ' world ')          # write_string () Worksheet.write (2, 0, 2)                # Write_number () worksheet.write (3, 0, 3.00001)          # Write_number () worksheet.write (4, 0, ' =sin (PI ()/4) ')   # Write_formula () worksheet.write (5, 0, ')               # Write_blank () worksheet.write (6, 0, None)             # Write_blank ()

More about Excel formats such as strings, numbers, colors, and locations: http://xlsxwriter.readthedocs.io/format.html

4, Icon

This is my concern. Using Excel Tools for Icon statistics

Compared to Python's matplotlib drawing module, Excel's icons are more beautiful and flexible.

Chart:area, Bar, Column, doughnut, line, Pie, scatter, Stock, Radar

Workbook = Xlswriter. Workbook (' Chart.xls ')

Worksheet = Workbook.add_sheet (' first_example ') # Normal worksheet

Build Chart Object: Chart = Workbook.add_chart ({type, ' column '})

Insert diagram into sheet: worksheet.insert_chart (' A7 ', chart)

Or you can create a chart sheet ChartSheet

ChartSheet = Workbook.add_charsheet ()

Chartsheet.set_char (Chart)

Bar chart:

Import Xlsxwriterworkbook = 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, ten],    [3, 6, 9, 15],]worksheet.write_column (' A1 ', data[0])  # Insert Worksheet.write_column by 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 $: $A $ '}) chart.add_series ({' Values ': ' =sheet1! $B $ $B $ ') chart.add_series ({' Values ': ' =sheet1! $C $ $C $ '}) # Insert the chart into the Worksheet.worksheet.insert_chart (' A7 ', chart) Workbook.close ()

Workbook.add_chart ({' type ': ' column '}) # Default format

Workbook.add_chart ({' type ': ' column ', ' substyle ': ' percent_stacked ') # Show by Percent

Workbook.add_chart ({' type ': ' column ', ' substyle ': ' Stacked '})

Other types of chart are the same:

Python module Xlsxwriter