Python calls Xlsxwriter method to create xlsx

This article mainly describes the Python call Xlsxwriter Create xlsx method, has a certain reference value, now share to everyone, the need for friends can refer to

Installing Xlsxwriter with PIP

Pip Install Xlsxwriter

Here's a basic Walkthrough:

1. First create a document for Excel

Workbook = Xlsxwriter. Workbook (dir)

2. Create a table in a document

table_name = ' Sheet1 ' worksheet = Workbook.add_worksheet (table_name) # Create a table with a table named ' Sheet1 ' and return this table object

3. After creating the table, you can write to the table above

Worksheet.write_column (' A1 ', 5) # write the number in the A1 cell 5

Sometimes, we want to modify the format of the input, such as setting the font color bold, italic, date format, etc., this time, you can use the format class provided by Xlsxwriter.

Specifically visible: http://xlsxwriter.readthedocs.io/format.html

The following is an example of writing a red date class in bold

Import datetime# need to format the string first to date Date_time = Datetime.datetime.strptime (' 2017-1-25 ', '%y-%m-%d ') # define a format class, bold red date Date_ Format = Workbook.add_format ({' bold ': True, ' font_color ': ' Red ', ' num_format ': ' Yyyy-mm-dd ') # Write the format class Worksheet.write_ Column (' A2 ', Date_time, Date_format)

4. Xlsxwriter supports the insertion of many chart formats

See also: http://xlsxwriter.readthedocs.io/chart.html

Here is a description of the icon type:

Excel defines a two-level category description, with the first level having nine major classes, as shown below

Area: Floor Plan
Bar: Transpose histogram
Column: Bar chart
Line: Straight chart
Pie: Pie chart
Doughnut: Ring diagram
Scatter: Scatter chart
Stock: Trend Chart
Radar: Radar chart

The second level is to describe whether there is a connection, whether there is a smooth curve and other details adjustment.

Area stacked Percent_stackedbar Stacked Percent_stackedcolumn stacked Percent_stackedscatter straight_with_markers Straight smooth_with_markers Smoothradar with_markers filled

The following example plots a scatter plot.

Chart1 = Workbook.add_chart ({' type ': ' Scatter ', ' subtype ': ' Straight '}) chart1.set_title ({' name ': ' Figure 1 '}) chart1.add_ Series ({' Name ': ' Family 1 ', # Collection range ' categories ': ' =sheet1! $A $: $A $89 ', # Value range ' values ': ' =sheet1! $B $: $B $89 '), # Whether line ' is wired ': {' None ': True}, # Default graph format ' marker ': {' type ': ' Automatic '}, ' # Set y-axis range Chart1.set_y_axis ({' Max ': 1.1, ' min ': 0}) chart1.set_ Size ({' X_scale ': 2, ' Y_scale ': 2}) # Insert the legend into the C2 position in the table and set the horizontal axis offset to 25, the vertical offset to 10worksheet.insert_chart (' C2 ', Chart1, {' X_ Offset ': +, ' Y_offset ': 10})

The above article

Detailed official documents are visible: http://xlsxwriter.readthedocs.io/

Installing Xlsxwriter with PIP

Pip Install Xlsxwriter

Here's a basic Walkthrough:

1. First create a document for Excel

Workbook = Xlsxwriter. Workbook (dir)

2. Create a table in a document

table_name = ' Sheet1 ' worksheet = Workbook.add_worksheet (table_name) # Create a table with a table named ' Sheet1 ' and return this table object

3. After creating the table, you can write to the table above

Worksheet.write_column (' A1 ', 5) # write the number in the A1 cell 5

Sometimes, we want to modify the format of the input, such as setting the font color bold, italic, date format, etc., this time, you can use the format class provided by Xlsxwriter.

Specifically visible: http://xlsxwriter.readthedocs.io/format.html

The following is an example of writing a red date class in bold

Import datetime# need to format the string first to date Date_time = Datetime.datetime.strptime (' 2017-1-25 ', '%y-%m-%d ') # define a format class, bold red date Date_ Format = Workbook.add_format ({' bold ': True, ' font_color ': ' Red ', ' num_format ': ' Yyyy-mm-dd ') # Write the format class Worksheet.write_ Column (' A2 ', Date_time, Date_format)

4. Xlsxwriter supports the insertion of many chart formats

See also: http://xlsxwriter.readthedocs.io/chart.html

Here is a description of the icon type:

Excel defines a two-level category description, with the first level having nine major classes, as shown below

Area: Floor Plan
Bar: Transpose histogram
Column: Bar chart
Line: Straight chart
Pie: Pie chart
Doughnut: Ring diagram
Scatter: Scatter chart
Stock: Trend Chart
Radar: Radar chart

The second level is to describe whether there is a connection, whether there is a smooth curve and other details adjustment.

Area stacked Percent_stackedbar Stacked Percent_stackedcolumn stacked Percent_stackedscatter straight_with_markers Straight smooth_with_markers Smoothradar with_markers filled

The following example plots a scatter plot.

Chart1 = Workbook.add_chart ({' type ': ' Scatter ', ' subtype ': ' Straight '}) chart1.set_title ({' name ': ' Figure 1 '}) chart1.add_ Series ({' Name ': ' Family 1 ', # Collection range ' categories ': ' =sheet1! $A $: $A $89 ', # Value range ' values ': ' =sheet1! $B $: $B $89 '), # Whether line ' is wired ': {' None ': True}, # Default graph format ' marker ': {' type ': ' Automatic '}, ' # Set y-axis range Chart1.set_y_axis ({' Max ': 1.1, ' min ': 0}) chart1.set_ Size ({' X_scale ': 2, ' Y_scale ': 2}) # Insert the legend into the C2 position in the table and set the horizontal axis offset to 25, the vertical offset to 10worksheet.insert_chart (' C2 ', Chart1, {' X_ Offset ': +, ' Y_offset ': 10})