7

I'm looking for packages available in Ubuntu main/universe repositories able to create Excel 2007+ xlsx files from a script (python or perl)

My requirements:

  • 100% compatible Excel XLSX files.
  • Solid and comprehensive documentation and a wide variety of examples/tutorials
  • Stable API
  • Write text, numbers, formulas and hyperlinks
  • Multiple worksheets
  • Full formatting
  • Merged cells
  • Charts
  • Autofilters
  • Worksheet PNG/JPEG images
  • Cell comments
  • Group and Outlines
  • 4
    Note to close voters: Let's not close this as too broad. The OP has not only given a very specific list of requirements, he has also answered it himself. This is not just a blind request for help from someone too lazy to try, it is the exact opposite, a useful resource provided by a user who presumably had to learn it the hard way. – terdon Apr 29 '14 at 22:25
  • 3
    I've been using XLS and XLSX modules to create all sort of reports in perl and python for more than a decade now and I wanted to share my knowledge since packages providing such features are not so many. I'm the package maintainer of XlsxWriter in Debian/Ubuntu. I packaged it simply because I missed some features only provided by this module. Give them a try. – Sylvain Pineau Apr 29 '14 at 23:27
13

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, jmcnamara@cpan.org
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()

enter image description here

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, jmcnamara@cpan.org
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.

protected by Sylvain Pineau Sep 16 '14 at 12:22

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.