Handling Microsoft Excel file format: Difference between revisions

From Qt Wiki
Jump to navigation Jump to search
(Added xlnt library)
 
(18 intermediate revisions by 8 users not shown)
Line 1: Line 1:
{{Cleanup | reason=Auto-imported from ExpressionEngine.}}
{{Ambox|text=TODO: If you know more about the container format, and whether it really needs a specialized library for processing, please expand this section.}}


[[Category:Developing_with_Qt]]
{{Ambox|text=TODO: Tips for displaying Excel documents which were manually parsed using one of the methods described.}}


{{Ambox|text=TODO: If you know whether Excel provides a "viewer" ActiveX control that can be embedded in a Qt application through ActiveQt, please fill out this section (including links to relevant resources).}}


= Handling Microsoft Excel (file format) =
{{LangSwitch}}
[[Category:Developing_with_Qt]]


This page discusses various available options for working with [http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats Microsoft Excel] documents in your Qt application. Please also read the general considerations outlined on the [[Handling_Document_Formats | Handling Document Formats]] page.
This page discusses various available options for working with [http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats Microsoft Excel] documents in your Qt application. Please also read the general considerations outlined on the [[Handling_Document_Formats | Handling Document Formats]] page.
'' <small> <pre style="background-color: #E6E6FA">Note that this information is collaboratively collected by the community, with no promise
of completeness or correctness. In particular, use your own research and judgment
when evaluating third-party libraries or tools! </pre> </small> ''


One needs to distinguish between two different formats (this page deals with both of them):
One needs to distinguish between two different formats (this page deals with both of them):
Line 16: Line 14:
{| class="wikitable"
{| class="wikitable"
|
|
! style="text-align:left;"| Legacy "Excel Spreadsheet" format
! style="text-align:left;" | Legacy "Excel Spreadsheet" format
! style="text-align:left;"| "Office Open XML Workbook" format
! style="text-align:left;" | "Office Open XML Workbook" format
|-
|-
| classification:
| classification:
Line 44: Line 42:
{| class="wikitable"
{| class="wikitable"
|
|
! style="text-align:left;"| DLL file name
! style="text-align:left;" | DLL file name
! style="text-align:left;"| COM object name
! style="text-align:left;" | COM object name
! style="text-align:left;"| platforms
! style="text-align:left;" | platforms
! style="text-align:left;"| license
! style="text-align:left;" | license
|-
|-
| [http://office.microsoft.com/excel/ '''Microsoft Excel''']
| [http://office.microsoft.com/excel/ '''Microsoft Excel''']
Line 58: Line 56:
=== Using ODBC ===
=== Using ODBC ===


<small> <pre style="background-color: Moccasin"> TODO: Info on using ODBC drivers (via QSqlDatabase) for accessing Excel spreadsheets - please fill out this section if you know more. (What
To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1 and Windows 10 with QT 5.7) :
is the ODBC driver called? Where does it come from? Windows only or also Mac/Linux? Link to sample code snippet?) </pre> </small>
 
To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1) :


<code>
<code>
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
db.setDatabaseName("DRIVER={Microsoft Excel Driver ('''.xls)};DBQ=" + QString("c:file.xlsx"));
db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\path\\to\\your\\file\\file.xlsx"));
if(db.open())
if(db.open())
{
{
  QSqlQuery query("select''' from [" + QString("Sheet1") + "$]"); // Select range, place A1:B5 after $
  QSqlQuery query("select * from [" + QString("Sheet1") + "$]",db); // Select range, place A1:B5 after $
  while (query.next())
  while (query.next())
  {
  {
Line 74: Line 69:
  qDebug() << column1;
  qDebug() << column1;
  }
  }
db.close();
QSqlDatabase::removeDatabase("xlsx_connection");
}
}
</code>
</code>


This sample print in the console all column1's values. It works for <span style="color:SeaGreen">.xls</span> and <span style="color:SeaGreen">xlsx</span>
The above code print all of column1's values to the debug output. It works for <span style="color:SeaGreen">*.xls</span> and <span style="color:SeaGreen">*.xlsx</span> and the other excel file formats.


By default OBDC uses the first row as names for the columns, you can change this whith the 'FirstRowHasNames' option in the connection settings. Keep in mind that you are using a database and that each column has his own datatype. So if your second row contains text and your third row contains numbers, sql wil pick one of these datatypes. If a few rows contain text and the rest of them contains floating numbers, sql wil make the text appear and will make the numbers disappear.
By default OBDC uses the first row as names for the columns, you are supposed to be able to change this with the 'FirstRowHasNames' option in the connection settings, however there is a bug (see [https://support.microsoft.com/en-us/kb/288343 KB288343]). Keep in mind that you are using a database and that each column has his own datatype, so if your second row contains text and your third row contains numbers, sql will pick one of these datatypes. If a few rows contain text and the rest of them contain floating numbers, sql will make the text appear and will make the numbers disappear.
 
'''NOTE:''' To use ODBC on Windows, the MS Access Database Engine has to be installed. You can find it here: [https://www.microsoft.com/en-us/download/details.aspx?id=13255 Microsoft Access Database Engine 2010]. The Engine is maybe distributed with a MS Office Access installation, but on this should not be relied on. In Addition, you should regard that a 64 bit application can only use the 64 bit Engine and so for 32 bit accordingly. That’s why you maybe install both versions to avoid problems. Furthermore, the Engine should not be confused with the MS Access Runtime which contains the Engine.


=== Using independent parser/writer libraries ===
=== Using independent parser/writer libraries ===
Line 87: Line 86:
{| class="wikitable"
{| class="wikitable"
|
|
! style="text-align:left;"| API
! style="text-align:left;" | API
! style="text-align:left;"| .xls
! style="text-align:left;" | .xls
! style="text-align:left;"| .xlsx
! style="text-align:left;" | .xlsx
! style="text-align:left;"| reading
! style="text-align:left;" | reading
! style="text-align:left;"| writing
! style="text-align:left;" | writing
! style="text-align:left;"| platforms
! style="text-align:left;" | platforms
! style="text-align:left;"| license
! style="text-align:left;" | license
|-
|-
| [https://github.com/dbzhang800/QtXlsxWriter '''Qt Xlsx''']
| [https://github.com/QtExcel/QXlsx '''QXlsx''']
| C++
| C++
Qt
Qt
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| Win, Mac, Linux, …
| MIT <span style="color:Navy">[weak copyleft]</span>
|-
| [https://github.com/tfussell/xlnt '''xlnt''']
| C++
| <span style="color:DarkRed">no</span>
| <span style="color:Green">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Green">yes</span>
| Win, Mac, Linux, …
| Win, Mac, Linux, …
| MIT <span style="color:Navy">[weak copyleft]</span>
| MIT <span style="color:Navy">[weak copyleft]</span>
Line 107: Line 115:
| [http://xlslib.sourceforge.net/ '''xlsLib''']
| [http://xlslib.sourceforge.net/ '''xlsLib''']
| C++
| C++
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| Win, Mac, Linux, …
| Win, Mac, Linux, …
| LGPL v3 <span style="color:Navy">[weak copyleft]</span>
| LGPL v3 <span style="color:Navy">[weak copyleft]</span>
Line 116: Line 124:
| [http://libxls.sourceforge.net '''libxls''']
| [http://libxls.sourceforge.net '''libxls''']
| C
| C
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| Win, Mac, Linux, …
| Win, Mac, Linux, …
| LGPL <span style="color:Navy">[weak copyleft]</span>
| LGPL <span style="color:Navy">[weak copyleft]</span>
Line 125: Line 133:
| [http://www.libxl.com/ '''LibXL''']
| [http://www.libxl.com/ '''LibXL''']
| C++
| C++
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| Win, Mac, Linux, …
| Win, Mac, Linux, …
| <span style="color:Navy">commercial</span>
| <span style="color:Navy">commercial</span>
Line 134: Line 142:
| [http://www.qtsoftware.de/vertrieb/db/qtxls_e.htm '''qtXLS''']
| [http://www.qtsoftware.de/vertrieb/db/qtxls_e.htm '''qtXLS''']
| C
| C
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| Win, ?
| Win, ?
| <span style="color:Navy">commercial</span>
| <span style="color:Navy">commercial</span>
Line 143: Line 151:
| [https://www.gaia-gis.it/fossil/freexl '''FreeXL''']
| [https://www.gaia-gis.it/fossil/freexl '''FreeXL''']
| C
| C
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| Linux, ?
| Linux, ?
| LGPL / MPL <span style="color:Navy">[weak copyleft]</span>
| LGPL / MPL <span style="color:Navy">[weak copyleft]</span>
Line 152: Line 160:
| [http://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft '''BasicExcel''']
| [http://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft '''BasicExcel''']
| C++
| C++
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| ?
| ?
| ?  
| ?  
Line 161: Line 169:
| [https://numberduck.com/ '''Number Duck''']
| [https://numberduck.com/ '''Number Duck''']
| C++
| C++
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Crimson">no</span>
| <span style="color:DarkRed">no</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:SeaGreen">yes</span>
| <span style="color:Green">yes</span>
| Win, Linux
| Win, Linux
| <span style="color:Navy">commercial</span>
| <span style="color:Navy">commercial</span>
|-
| [https://github.com/VSRonin/QtXlsxWriter '''Qt Xlsx'''] (Unmaintained)
| C++
Qt
| <span style="color:DarkRed">no</span>
| <span style="color:Green">yes</span>
| <span style="color:Green">yes</span>
| <span style="color:Green">yes</span>
| Win, Mac, Linux, …
| MIT <span style="color:Navy">[weak copyleft]</span>
|}
|}


Line 177: Line 195:
{| class="wikitable"
{| class="wikitable"
|
|
! style="text-align:left;"| API
! style="text-align:left;" | API
! style="text-align:left;"| supported platforms
! style="text-align:left;" | supported platforms
! style="text-align:left;"| license
! style="text-align:left;" | license
|-
|-
| [http://libopc.codeplex.com '''libopc''']
| [http://libopc.codeplex.com '''libopc''']
Line 186: Line 204:
| <span style="color:Navy">permissive</span>
| <span style="color:Navy">permissive</span>
|}
|}
<small> <pre style="background-color: Moccasin"> TODO: If you know more about the container format, and whether it really needs a specialized library for processing, please expand this section. </pre> </small>


=== Using batch conversion tools ===
=== Using batch conversion tools ===
Line 195: Line 211:
{| class="wikitable"
{| class="wikitable"
|
|
! style="text-align:left;"| .xls to *  
! style="text-align:left;" | .xls to *  
! style="text-align:left;"| .xlsx to *
! style="text-align:left;" | .xlsx to *
! style="text-align:left;"| *to .xls
! style="text-align:left;" | *to .xls
! style="text-align:left;"| *to .xlsx  
! style="text-align:left;" | *to .xlsx  
! style="text-align:left;"| platforms
! style="text-align:left;" | platforms
! style="text-align:left;"| license
! style="text-align:left;" | license
|-
|-
| [http://www.libreoffice.org/ '''LibreOffice''']
| [http://www.libreoffice.org/ '''LibreOffice''']
Line 221: Line 237:
''Notes:''
''Notes:''
LibreOffice can be used like this for batch conversion (it's slow, though): <code>soffice —invisible -convert-to xls test.ods</code>
LibreOffice can be used like this for batch conversion (it's slow, though): <code>soffice —invisible -convert-to xls test.ods</code>
== Displaying / User Interaction ==
=== Using Excel itself ===
<small> <pre style="background-color: Moccasin"> TODO: If you know whether Excel provides a "viewer" ActiveX control that can be embedded in a Qt application through ActiveQT, please fill out
this section (including links to relevant resources). </pre> </small>
=== Manual solution ===
<small> <pre style="background-color: Moccasin">TODO: Tips for displaying Excel documents which were manually parsed using one of the methods described above. </pre> </small>


----
----
Line 238: Line 242:
== See Also ==
== See Also ==


* [[Handling_Document_Formats | Handling Document Formats]]
* [[Handling Document Formats]]
** ''other Microsoft Office formats:''
* [[Handling Microsoft Word file format]]
*** [[Handling_Microsoft_Word_(file_format) | Microsoft Word]]
* [[Handling Microsoft PowerPoint file format]]
*** [[Handling_Microsoft_Powerpoint_(file_format) | Microsoft Powerpoint]]
* [[Handling HTML]]
** ''other “spreadsheet” formats:''
* [[Handling PDF]]

Latest revision as of 09:15, 11 June 2021

TODO: If you know more about the container format, and whether it really needs a specialized library for processing, please expand this section.
TODO: Tips for displaying Excel documents which were manually parsed using one of the methods described.
TODO: If you know whether Excel provides a "viewer" ActiveX control that can be embedded in a Qt application through ActiveQt, please fill out this section (including links to relevant resources).

En Ar Bg De El Es Fa Fi Fr Hi Hu It Ja Kn Ko Ms Nl Pl Pt Ru Sq Th Tr Uk Zh

This page discusses various available options for working with Microsoft Excel documents in your Qt application. Please also read the general considerations outlined on the Handling Document Formats page.

One needs to distinguish between two different formats (this page deals with both of them):

Legacy "Excel Spreadsheet" format "Office Open XML Workbook" format
classification: binary BIFF-based XML-based
main filename extension: .xls .xlsx
main internet media type: application/vnd.ms-excel application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
default format of Excel: until Excel 2003 since Excel 2007

Reading / Writing

Using Excel itself

If you are exclusively targeting the Windows platform and Microsoft Excel will be installed on all target machines, then you can use Qt's ActiveX framework to access Excel's spreadsheet processing functionality through OLE automation. For an introductory code example (and a way to list the API provided by the Excel COM object), consult this how-to.

DLL file name COM object name platforms license
Microsoft Excel ? Excel.Application Windows commercial

Using ODBC

To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1 and Windows 10 with QT 5.7) :

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\path\\to\\your\\file\\file.xlsx"));
if(db.open())
{
 QSqlQuery query("select * from [" + QString("Sheet1") + "$]",db); // Select range, place A1:B5 after $
 while (query.next())
 {
 QString column1= query.value(0).toString();
 qDebug() << column1;
 }
db.close();
QSqlDatabase::removeDatabase("xlsx_connection");
}

The above code print all of column1's values to the debug output. It works for *.xls and *.xlsx and the other excel file formats.

By default OBDC uses the first row as names for the columns, you are supposed to be able to change this with the 'FirstRowHasNames' option in the connection settings, however there is a bug (see KB288343). Keep in mind that you are using a database and that each column has his own datatype, so if your second row contains text and your third row contains numbers, sql will pick one of these datatypes. If a few rows contain text and the rest of them contain floating numbers, sql will make the text appear and will make the numbers disappear.

NOTE: To use ODBC on Windows, the MS Access Database Engine has to be installed. You can find it here: Microsoft Access Database Engine 2010. The Engine is maybe distributed with a MS Office Access installation, but on this should not be relied on. In Addition, you should regard that a 64 bit application can only use the 64 bit Engine and so for 32 bit accordingly. That’s why you maybe install both versions to avoid problems. Furthermore, the Engine should not be confused with the MS Access Runtime which contains the Engine.

Using independent parser/writer libraries

For a more portable solution, you could take a look at some of the available third-party C/C++ libraries for parsing/writing Excel files:

API .xls .xlsx reading writing platforms license
QXlsx C++

Qt

no yes yes yes Win, Mac, Linux, … MIT [weak copyleft]
xlnt C++ no yes yes yes Win, Mac, Linux, … MIT [weak copyleft]
xlsLib C++ yes no no yes Win, Mac, Linux, … LGPL v3 [weak copyleft]
libxls C yes no yes no Win, Mac, Linux, … LGPL [weak copyleft]
LibXL C++ yes yes yes yes Win, Mac, Linux, … commercial
qtXLS C yes no yes yes Win, ? commercial
FreeXL C yes no yes no Linux, ? LGPL / MPL [weak copyleft]
BasicExcel C++ yes no yes yes ? ?
Number Duck C++ yes no yes yes Win, Linux commercial
Qt Xlsx (Unmaintained) C++

Qt

no yes yes yes Win, Mac, Linux, … MIT [weak copyleft]

Note that these libraries differ in their scope and general approach to the problem.

Using manual XML processing

Files using the XML-based (.xlsx) format could be processed using Qt's XML handling classes (see Handling Document Formats). Third-party libraries can help you in dealing with the container format that wraps the actual XML files:

API supported platforms license
libopc C Win, Mac, Linux, … permissive

Using batch conversion tools

If all else fails, there is always the option of using an existing tool to automatically convert between Excel files and a more manageable format, and let your Qt application deal with that format instead. The conversion tool could be bundled with your application or specified as a prerequisite, and controlled via Doc:QProcess. Some possibilities are:

.xls to * .xlsx to * *to .xls *to .xlsx platforms license
LibreOffice .ods .csv … .ods .csv … .ods .csv … .ods .csv … Win, Mac, Linux, … GPL v3 [strong copyleft]

Notes:

LibreOffice can be used like this for batch conversion (it's slow, though):

soffice invisible -convert-to xls test.ods

See Also