How to create Excel xlsx files from a script?

Solution 1:

I highly recommend John McNamara's modules. He uploaded a new Python module called XlsxWriter for creating XLSX files to PyPi that I packaged for Debian and Ubuntu.

It is a port of a Perl module that he wrote called Excel::Writer::XLSX which is a extension of another Perl module called Spreadsheet::WriteExcel.

python3-xlsxwriter (python-xlsxwriter for python 2.x)

XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format.

It can be used to write text, numbers, and formulas to multiple worksheets and it supports features such as formatting, images, charts, page setup, autofilters, conditional formatting and many others.

XlsxWriter has some advantages over the alternative Python modules for writing Excel files:

• It supports more Excel features than any of the alternative modules.
• It has a high degree of fidelity with files produced by Excel. In most cases the files produced are 100% equivalent to files produced by Excel.
• It has extensive documentation, example files and tests.
• It is fast and can be configured to use very little memory even for very large output files.
• Integration with Pandas (Python Data Analysis Library).

A simple example of some of the features of the XlsxWriter Python module:

#Copyright 2013-2014, John McNamara, [email protected]
import xlsxwriter

# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

# Insert an image.
worksheet.insert_image('B5', 'logo.png')

workbook.close()

Visit this page for a full list of XlsxWriter examples.

libexcel-writer-xlsx-perl

The Excel::Writer::XLSX module can be used to create an Excel file in the 2007+ XLSX format.

Multiple worksheets can be added to a workbook and formatting can be applied to cells. Text, numbers, and formulas can be written to the cells.

Excel::Writer::XLSX uses the same interface as the Spreadsheet::WriteExcel module which produces an Excel file in binary XLS format.

Excel::Writer::XLSX supports all of the features of Spreadsheet::WriteExcel and in some cases has more functionality. For more details see Compatibility with Spreadsheet::WriteExcel.

The main advantage of the XLSX format over the XLS format is that it allows a larger number of rows and columns in a worksheet.

The XLSX file format also produces much smaller files than the XLS file format.

To write a string, a formatted string, a number and a formula to the first worksheet in an Excel workbook called perl.xlsx:

# reverse ('(c)'), March 2001, John McNamara, [email protected]
use Excel::Writer::XLSX;

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );

# Add a worksheet
$worksheet = $workbook->add_worksheet();

#  Add and define a format
$format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$format->set_align( 'center' );

# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write( $row, $col, 'Hi Excel!', $format );
$worksheet->write( 1, $col, 'Hi Excel!' );

# Write a number and a formula using A1 notation
$worksheet->write( 'A3', 1.2345 );
$worksheet->write( 'A4', '=SIN(PI()/4)' );

Visit this page for a full list of Excel::Writer::XLSX examples.