Handling microsoft excel file format: Difference between revisions

From Qt Wiki
Jump to navigation Jump to search
No edit summary
 
No edit summary
Line 1: Line 1:
[[Category:Developing_with_Qt]]<br />[toc align_right=&quot;yes&amp;quot; depth=&quot;2&amp;quot;]
[[Category:Developing_with_Qt]]
[toc align_right="yes" depth="2"]


= Handling Microsoft Excel (file format) =
= Handling Microsoft Excel (file format) =


This page discusses various available options for working with &quot;Microsoft Excel&amp;quot;:http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats 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 "Microsoft Excel":http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats documents in your Qt application. Please also read the general considerations outlined on the [[Handling_Document_Formats | Handling Document Formats]] page.


p{width:60%;border:solid 1px #99a;background:#eef;color:#335;padding:2pt 4pt;font-size:0.9em;line-height:150%;font-style:italic}. 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!
p{width:60%;border:solid 1px #99a;background:#eef;color:#335;padding:2pt 4pt;font-size:0.9em;line-height:150%;font-style:italic}. 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!
Line 9: Line 10:
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):


table{width:95%;margin-left:2.5%}.<br />| |''. Legacy &quot;Excel Spreadsheet&amp;quot; format |''. &quot;Office Open XML Workbook&amp;quot; format |<br />| ''classification:'' | binary BIFF-based | XML-based |<br />| ''main filename extension:'' | {font:1em monospace}.xls | {font-family:monospace}.xlsx |<br />| ''main internet media type:'' | {font:0.9em monospace}application/vnd.ms-excel | {font:0.9em monospace}application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |<br />| ''default format of Excel:'' | until Excel 2003 | since Excel 2007 |
table{width:95%;margin-left:2.5%}.
| |''. Legacy "Excel Spreadsheet" format |''. "Office Open XML Workbook" format |
| ''classification:'' | binary BIFF-based | XML-based |
| ''main filename extension:'' | {font:1em monospace}.xls | {font-family:monospace}.xlsx |
| ''main internet media type:'' | {font:0.9em monospace}application/vnd.ms-excel | {font:0.9em monospace}application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
| ''default format of Excel:'' | until Excel 2003 | since Excel 2007 |


== Reading / Writing ==
== Reading / Writing ==
Line 15: Line 21:
=== Using Excel itself ===
=== 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 &quot;Qt's ActiveX framework&amp;quot;:http://doc.qt.io/qt-4.8/activeqt.html 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 [[Using_ActiveX_Object_in_QT | this how-to]].
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":http://doc.qt.io/qt-4.8/activeqt.html 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 [[Using_ActiveX_Object_in_QT | this how-to]].


table{width:95%;margin-left:2.5%}.<br />| |''. DLL file name |''. COM object name |''. platforms |''. license |<br />| &quot;'''Microsoft Excel'''&quot;:http://office.microsoft.com/excel/ | ? | {font-family:monospace}Excel.Application | Windows | {color:#458}commercial |
table{width:95%;margin-left:2.5%}.
| |''. DLL file name |''. COM object name |''. platforms |''. license |
| "'''Microsoft Excel'''":http://office.microsoft.com/excel/ | ? | {font-family:monospace}Excel.Application | Windows | {color:#458}commercial |


=== Using ODBC ===
=== Using ODBC ===
Line 25: Line 33:
To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1) :
To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1) :


<code><br />QSqlDatabase db = QSqlDatabase::addDatabase(&quot;QODBC&amp;quot;);<br />db.setDatabaseName(&quot;DRIVER={Microsoft Excel Driver ('''.xls)};DBQ=&quot; + QString(&quot;c:file.xlsx&amp;quot;));<br />if(db.open())<br />{<br /> QSqlQuery query(&quot;select''' from [&quot; + QString(&quot;Sheet1&amp;quot;) + &quot;$]&quot;); // Select range, place A1:B5 after $<br /> while (query.next())<br /> {<br /> QString column1= query.value(0).toString();<br /> qDebug() &lt;&lt; column1;<br /> }<br />}<br /></code>
<code>
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("DRIVER={Microsoft Excel Driver ('''.xls)};DBQ=" + QString("c:file.xlsx"));
if(db.open())
{
QSqlQuery query("select''' from [" + QString("Sheet1") + "$]"); // Select range, place A1:B5 after $
while (query.next())
{
QString column1= query.value(0).toString();
qDebug() << column1;
}
}
</code>


This sample print in the console all column1's values. It works for {color:#580}.xls and {color:#580}.xlsx
This sample print in the console all column1's values. It works for {color:#580}.xls and {color:#580}.xlsx
Line 35: Line 55:
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:
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:


table{width:95%;margin-left:2.5%}.<br />| |''. API |''. {font-family:monospace}.xls |''. {font-family:monospace}.xlsx |''. reading |''. writing |''. platforms |''. license |<br />| &quot;'''Qt Xlsx'''&quot;:https://github.com/dbzhang800/QtXlsxWriter | C++ Qt| {color:#920}no | {color:#580}yes | {color:#580}yes| {color:#580}yes | Win, Mac, Linux, … | MIT {color:#458}[weak copyleft]|<br />| &quot;'''xlsLib'''&quot;:http://xlslib.sourceforge.net/ | C++ | {color:#580}yes | {color:#920}no | {color:#920}no | {color:#580}yes | Win, Mac, Linux, … | LGPL v3 {color:#458}[weak copyleft]|<br />| &quot;'''libxls'''&quot;:http://libxls.sourceforge.net | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#920}no | Win, Mac, Linux, … | LGPL {color:#458}[weak copyleft]|<br />| &quot;'''LibXL'''&quot;:http://www.libxl.com/ | C++ | {color:#580}yes | {color:#580}yes | {color:#580}yes | {color:#580}yes | Win, Mac, Linux, … | {color:#458}commercial |<br />| &quot;'''qtXLS'''&quot;:http://www.qtsoftware.de/vertrieb/db/qtxls_e.htm | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | Win, ? | {color:#458}commercial |<br />| &quot;'''FreeXL'''&quot;:https://www.gaia-gis.it/fossil/freexl | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#920}no | Linux, ? | LGPL / MPL {color:#458}[weak copyleft]|<br />| &quot;'''BasicExcel'''&quot;:http://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft | C++ | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | ? | ? |<br />| &quot;'''Number Duck'''&quot;:https://numberduck.com/ | C++ | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | Win, Linux | {color:#458}commercial |
table{width:95%;margin-left:2.5%}.
<br />Note that these libraries differ in their scope and general approach to the problem.
| |''. API |''. {font-family:monospace}.xls |''. {font-family:monospace}.xlsx |''. reading |''. writing |''. platforms |''. license |
<br />h3. Using manual XML processing
| "'''Qt Xlsx'''":https://github.com/dbzhang800/QtXlsxWriter | C++ Qt| {color:#920}no | {color:#580}yes | {color:#580}yes| {color:#580}yes | Win, Mac, Linux, … | MIT {color:#458}[weak copyleft]|
<br />Files using the XML-based (.xlsx) format could be processed using Qt's XML handling classes (see [[Handling_Document_Formats | Handling Document Formats]]). Third-party libraries can help you in dealing with the container format that wraps the actual XML files:
| "'''xlsLib'''":http://xlslib.sourceforge.net/ | C++ | {color:#580}yes | {color:#920}no | {color:#920}no | {color:#580}yes | Win, Mac, Linux, … | LGPL v3 {color:#458}[weak copyleft]|
<br />table{width:95%;margin-left:2.5%}.<br />| |''. API |''. supported platforms |''. license |<br />| &quot;'''libopc'''&quot;:http://libopc.codeplex.com | C | Win, Mac, Linux, … | {color:#458}permissive |
| "'''libxls'''":http://libxls.sourceforge.net | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#920}no | Win, Mac, Linux, … | LGPL {color:#458}[weak copyleft]|
| "'''LibXL'''":http://www.libxl.com/ | C++ | {color:#580}yes | {color:#580}yes | {color:#580}yes | {color:#580}yes | Win, Mac, Linux, … | {color:#458}commercial |
| "'''qtXLS'''":http://www.qtsoftware.de/vertrieb/db/qtxls_e.htm | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | Win, ? | {color:#458}commercial |
| "'''FreeXL'''":https://www.gaia-gis.it/fossil/freexl | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#920}no | Linux, ? | LGPL / MPL {color:#458}[weak copyleft]|
| "'''BasicExcel'''":http://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft | C++ | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | ? | ? |
| "'''Number Duck'''":https://numberduck.com/ | C++ | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | Win, Linux | {color:#458}commercial |
 
Note that these libraries differ in their scope and general approach to the problem.
 
h3. Using manual XML processing
 
Files using the XML-based (.xlsx) format could be processed using Qt's XML handling classes (see [[Handling_Document_Formats | Handling Document Formats]]). Third-party libraries can help you in dealing with the container format that wraps the actual XML files:
 
table{width:95%;margin-left:2.5%}.
| |''. API |''. supported platforms |''. license |
| "'''libopc'''":http://libopc.codeplex.com | C | Win, Mac, Linux, … | {color:#458}permissive |


p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. TODO: If you know more about the container format, and whether it really needs a specialized library for processing, please expand this section.
p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. TODO: If you know more about the container format, and whether it really needs a specialized library for processing, please expand this section.
Line 47: Line 82:
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:
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:


table{width:95%;margin-left:2.5%}.<br />|''. |''. {font-family:monospace}.xls to * |''. {font-family:monospace}.xlsx to * |''. &amp;#42; to {font-family:monospace}.xls |''. &amp;#42; to {font-family:monospace}.xlsx |''. platforms |_. license |<br />| &quot;'''LibreOffice'''&quot;:http://www.libreoffice.org/ | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | Win, Mac, Linux, … | GPL v3 {color:#458}[strong copyleft] |<br />| &quot;'''…'''&quot;:http://… | … | … | … | … | … | … |
table{width:95%;margin-left:2.5%}.
|''. |''. {font-family:monospace}.xls to * |''. {font-family:monospace}.xlsx to * |''. &amp;#42; to {font-family:monospace}.xls |''. &amp;#42; to {font-family:monospace}.xlsx |''. platforms |_. license |
| "'''LibreOffice'''":http://www.libreoffice.org/ | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | Win, Mac, Linux, … | GPL v3 {color:#458}[strong copyleft] |
| "'''…'''":http://… | … | … | … | … | … | … |


''Notes:''<br />LibreOffice can be used like this for batch conversion (it's slow, though): <code>soffice —invisible -convert-to xls test.ods<code>
''Notes:''
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 ==
== Displaying / User Interaction ==
Line 55: Line 94:
=== Using Excel itself ===
=== Using Excel itself ===


p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. TODO: If you know whether Excel provides a &quot;viewer&amp;quot; ActiveX control that can be embedded in a Qt application through ActiveQT, please fill out this section (including links to relevant resources).
p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. 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).


=== Manual solution ===
=== Manual solution ===

Revision as of 13:04, 25 February 2015

[toc align_right="yes" depth="2"]

Handling Microsoft Excel (file format)

This page discusses various available options for working with "Microsoft Excel":http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats documents in your Qt application. Please also read the general considerations outlined on the Handling Document Formats page.

p{width:60%;border:solid 1px #99a;background:#eef;color:#335;padding:2pt 4pt;font-size:0.9em;line-height:150%;font-style:italic}. 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!

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

table{width:95%;margin-left:2.5%}. | |. Legacy "Excel Spreadsheet" format |. "Office Open XML Workbook" format | | classification: | binary BIFF-based | XML-based | | main filename extension: | {font:1em monospace}.xls | {font-family:monospace}.xlsx | | main internet media type: | {font:0.9em monospace}application/vnd.ms-excel | {font:0.9em monospace}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":http://doc.qt.io/qt-4.8/activeqt.html 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.

table{width:95%;margin-left:2.5%}. | |. DLL file name |. COM object name |. platforms |. license | | "Microsoft Excel":http://office.microsoft.com/excel/ | ? | {font-family:monospace}Excel.Application | Windows | {color:#458}commercial |

Using ODBC

p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. TODO: Info on using ODBC drivers (via QSqlDatabase) for accessing Excel spreadsheets - please fill out this section if you know more. (What is the ODBC driver called? Where does it come from? Windows only or also Mac/Linux? Link to sample code snippet?)

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

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

This sample print in the console all column1's values. It works for {color:#580}.xls and {color:#580}.xlsx

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.

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:

table{width:95%;margin-left:2.5%}. | |. API |. {font-family:monospace}.xls |. {font-family:monospace}.xlsx |. reading |. writing |. platforms |. license | | "Qt Xlsx":https://github.com/dbzhang800/QtXlsxWriter | C++ Qt| {color:#920}no | {color:#580}yes | {color:#580}yes| {color:#580}yes | Win, Mac, Linux, … | MIT {color:#458}[weak copyleft]| | "xlsLib":http://xlslib.sourceforge.net/ | C++ | {color:#580}yes | {color:#920}no | {color:#920}no | {color:#580}yes | Win, Mac, Linux, … | LGPL v3 {color:#458}[weak copyleft]| | "libxls":http://libxls.sourceforge.net | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#920}no | Win, Mac, Linux, … | LGPL {color:#458}[weak copyleft]| | "LibXL":http://www.libxl.com/ | C++ | {color:#580}yes | {color:#580}yes | {color:#580}yes | {color:#580}yes | Win, Mac, Linux, … | {color:#458}commercial | | "qtXLS":http://www.qtsoftware.de/vertrieb/db/qtxls_e.htm | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | Win, ? | {color:#458}commercial | | "FreeXL":https://www.gaia-gis.it/fossil/freexl | C | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#920}no | Linux, ? | LGPL / MPL {color:#458}[weak copyleft]| | "BasicExcel":http://www.codeproject.com/Articles/13852/BasicExcel-A-Class-to-Read-and-Write-to-Microsoft | C++ | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | ? | ? | | "Number Duck":https://numberduck.com/ | C++ | {color:#580}yes | {color:#920}no | {color:#580}yes | {color:#580}yes | Win, Linux | {color:#458}commercial |

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

h3. 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:

table{width:95%;margin-left:2.5%}. | |. API |. supported platforms |. license | | "libopc":http://libopc.codeplex.com | C | Win, Mac, Linux, … | {color:#458}permissive |

p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. TODO: If you know more about the container format, and whether it really needs a specialized library for processing, please expand this section.

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:

table{width:95%;margin-left:2.5%}. |. |. {font-family:monospace}.xls to * |. {font-family:monospace}.xlsx to * |. &#42; to {font-family:monospace}.xls |. &#42; to {font-family:monospace}.xlsx |. platforms |_. license | | "LibreOffice":http://www.libreoffice.org/ | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | {font-family:monospace}.ods .csv … | Win, Mac, Linux, … | GPL v3 {color:#458}[strong copyleft] | | "":http://… | … | … | … | … | … | … |

Notes: LibreOffice can be used like this for batch conversion (it's slow, though): soffice —invisible -convert-to xls test.ods

Displaying / User Interaction

Using Excel itself

p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. 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).

Manual solution

p{border:dashed 1px #a94;background:#fbf3dd;color:#530;padding:2pt 4pt;margin-left:2pt;margin-right:2pt;font-size:0.9em;line-height:150%;font-style:italic}. TODO: Tips for displaying Excel documents which were manually parsed using one of the methods described above.

p{color:#fff;border-bottom:solid 1px #ccc}. .

See Also