Assessment of Open XML Library

Wiki

Office Open XML (also informally known as OOXML or OpenXML) is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. Because Excel file is base on Open XML, so we can read and write Excel file directly.

Here some open source Open XML library with some different language implement.

PHP

  • PhpSpreadsheet

PhpSpreadsheet is a library written in pure PHP and providing a set of classes that allow you to read from and to write to different spreadsheet file formats, like Excel and LibreOffice Calc.

Official Website: phpspreadsheet.readthedocs.io

Project on GitHub: github.com/PHPOffice/PhpSpreadsheet

Python

  • jmcnamara/XlsxWriter

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

Official Website: xlsxwriter.readthedocs.io

Project on GitHub: github.com/jmcnamara/XlsxWriter

  • python-excel

Include xlrd, xlwt and xlutils module. xlrd can read file but xlwt can't write file.

Official Website: python-excel.org

  • OpenPyXL

A Python library to read/write Excel 2007 xlsx/xlsm files

Official Website: openpyxl.readthedocs.org

Go

  • 360EntSecGroup-Skylar/excelize

Excelize is a library written in pure Go providing a set of functions that allow you to write to and read from XLSX files. Supports reading and writing XLSX file generated by Microsoft Excel™ 2007 and later. Supports saving a file without losing original charts of XLSX. This library needs Go version 1.8 or later. The full API docs can be seen using go's built-in documentation tool, or online at godoc.org and docs reference.

Project on GitHub: github.com/360EntSecGroup-Skylar/excelize

  • tealeg/xlsx

Google Go library for reading and writing XLSX files.

Project on GitHub: github.com/tealeg/xlsx

  • psmithuk/xlsx

Create Office Open XML Spreadsheet files in Go.

Project on GitHub: github.com/psmithuk/xlsx

Microsoft Excel API

MSDN Link: msdn.microsoft.com/en-us/library/fp179694.aspx

+----------------+--------------------------+--------------------------------------------------------------------------+-----------------------------------------------+-----------------------+
|    Language    |           PHP            |                                  Python                                  |                    Go                         |          COM          |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|    Library     |         PhpSpreadsheet   |        XlsxWriter        |    python-excel    |         OpenPyXL         |       tealeg/xlsx        |   psmithuk/xlsx    |  Microsoft Excel API  |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|      Read      |            √             |            ×             |         √          |            √             |            √             |         ×          |           √           |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|     Write      |            √             |            √             |         √          |            √             |            √             |         √          |           √           |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|     Modify     |            √             |            ×             |         ×          |   ! Maybe lost charts    |      ! Lost charts       |         ×          |           √           |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|      .xls      |            ×             |            ×             |         √          |            ×             |            ×             |         √          |           √           |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|     .xlsx      |            √             |            √             | ! Function limited |            √             |            √             |         √          |           √           |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|   Large File   |            √             |            √             |         ×          |            √             |            √             |         √          |           ×           |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|    Function    |      4.0/5.0 Points      |      4.0/5.0 Points      |   2.0/5.0 Points   |      3.0/5.0 Points      |      4.0/5.0 Points      |   3.5/5.0 Points   |    5.5/5.0 Points     |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|     Speed      |           Fast           |           Fast           |        Fast        |           Fast           |           Fast           |        Fast        |         Slow          |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|Operating System|        No Limited        |        No Limited        |     No Limited     |        No Limited        |        No Limited        |     No Limited     |    Windows + Excel    |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+
|                |Create .xlsx file         |Create .xlsx file         |Create .xlsx file   |Modify .xlsx file         |Create .xlsx file         |Create .xlsx file   |Create .xlsx file      |
|                |Read .xlsx file           |Read .xlsx file           |Read .xlsx file     |Complex functions         |Read .xlsx file           |Complex functions   |Read .xlsx file        |
|Applicable Scene|Modify .xlsx file         |Modify .xlsx file         |No complex functions|Will be process large file|Will be process large file|No complex functions|Modify .xlsx file      |
|                |Complex functions         |Complex functions         |Cross platform      |Cross platform            |No complex functions      |Cross platform      |Complex functions      |
|                |Will be process large file|Will be process large file|                    |                          |Cross platform            |                    |Only process small file|
|                |Cross platform            |Cross platform            |                    |                          |                          |                    |Only works in Windows  |
+----------------+--------------------------+--------------------------+--------------------+--------------------------+--------------------------+--------------------+-----------------------+

In some case we want to generate Excel from exists template, I recommend to use PHPExcel with some manual hacking after assessment of these Open XML library. Some times PHPExcel will lost some colors or style of Excel, that means PHPExcel library lost archieve some file when process zip .xlsx files, so we need find out which file was lost via diff between template .xlsx file and generated .xlsx file.saved and using The ZipArchive class overwrite it after $objWriter->save with simple code:

$zip = new ZipArchive;
if ($zip->open('filename.xlsx') === TRUE) {
    $zip->addFile('mytheme1.xml', 'xl/theme/theme1.xml');
    // Some another lost file your find ...
    $zip->close();
    echo 'ok';
} else {
    echo 'err';
}

How to find out diff files? We need to understand Structure of Open XML of Excel.

Save UTF-8 characters to file with fputcsv in PHP:

$file = fopen($filepath, 'w');
fwrite($file, chr(0xEF).chr(0xBB).chr(0xBF));
fputcsv($file, $row);
fclose($file);

Related Resources

Tools for Working with Excel and Python

Excelize - Go Library for Reading and Writing XLSX Files

Assessment of Open XML Library
5 votes, 5.00 avg. rating (99% score)