How to export excel in Qt?
-
-
As i said i never use it :) , but i found following topic:
"http://qt-project.org/forums/viewthread/1871":http://qt-project.org/forums/viewthread/1871 -
-
Is this work with Qt 5.3.0?
-
//initialize excel
QAxWidget excel("Excel.Application");
excel.setProperty("Visible", false);//workbooks pointer QAxObject* workbooks=excel.querySubObject("WorkBooks"); workbooks->dynamicCall("Add"); // Add new workbook QAxObject* workbook=excel.querySubObject("ActiveWorkBook"); QAxObject* sheets=workbook->querySubObject("WorkSheets"); QAxObject * cell; int count= sheets->dynamicCall("Count()").toInt(); bool isEmpty = true; for(int k=1;k<=count;k++) { //sheet pointer QAxObject* sheet = sheets->querySubObject( "Item( int )", k ); for(int i=1; i<row ; i++) { for(int j=1; j<column ;j++) { QString text= qrandom(); // value you want to export //get cell QAxObject* cell = sheet->querySubObject( "Cells( int, int )", i,j); //set your value to the cell of ith row n jth column cell->dynamicCall("SetValue(QString)",text); // if you wish check your value set correctly or not by retrieving and printing it QVariant value = cell->dynamicCall( "Value()" ); } } } QString fileName=QFileDialog::getSaveFileName(0,"save file","export_table", "XML Spreadhseet(*.xlsx);;eXceL Spreadsheet(*.xls);;Comma Seperated Value(*.csv)"); fileName.replace("/","\\"); workbook->dynamicCall("SaveAs(QString&)",fileName); workbook->dynamicCall("Close (Boolean)",false); -
Hello
I spent 2 hours to get export to excel work, so i'm pasting here my class just for those guys who needs a good sample.
- Add to .pro file:
QT += axcontainer - Application must be of UI type for ActiveX work.
File: ExcelExportHelper.h
#ifndef EXCELHELPER_H #define EXCELHELPER_H #include <ActiveQt/qaxobject.h> #include <ActiveQt/qaxbase.h> #include <QString> //Expected in .pro file: QT += axcontainer //Application must be of UI type for ActiveX work. class ExcelExportHelper { public: ExcelExportHelper(const ExcelExportHelper& other) = delete; ExcelExportHelper& operator=(const ExcelExportHelper& other) = delete; ExcelExportHelper(bool closeExcelOnExit = false); void SetCellValue(int lineIndex, int columnIndex, const QString& value); void SaveAs(const QString& fileName); ~ExcelExportHelper(); private: QAxObject* m_excelApplication; QAxObject* m_workbooks; QAxObject* m_workbook; QAxObject* m_sheets; QAxObject* m_sheet; bool m_closeExcelOnExit; }; #endif // EXCELHELPER_HFile: ExcelExportHelper.cpp
#include <ActiveQt/qaxobject.h> #include <ActiveQt/qaxbase.h> #include <QString> #include <QFile> #include <stdexcept> using namespace std; #include "ExcelExportHelper.h" ExcelExportHelper::ExcelExportHelper(bool closeExcelOnExit) { m_closeExcelOnExit = closeExcelOnExit; m_excelApplication = nullptr; m_sheet = nullptr; m_sheets = nullptr; m_workbook = nullptr; m_workbooks = nullptr; m_excelApplication = nullptr; m_excelApplication = new QAxObject( "Excel.Application", 0 );//{00024500-0000-0000-C000-000000000046} if (m_excelApplication == nullptr) throw invalid_argument("Failed to initialize interop with Excel (probably Excel is not installed)"); m_excelApplication->dynamicCall( "SetVisible(bool)", false ); // hide excel m_excelApplication->setProperty( "DisplayAlerts", 0); // disable alerts m_workbooks = m_excelApplication->querySubObject( "Workbooks" ); m_workbook = m_workbooks->querySubObject( "Add" ); m_sheets = m_workbook->querySubObject( "Worksheets" ); m_sheet = m_sheets->querySubObject( "Add" ); } void ExcelExportHelper::SetCellValue(int lineIndex, int columnIndex, const QString& value) { QAxObject *cell = m_sheet->querySubObject("Cells(int,int)", lineIndex, columnIndex); cell->setProperty("Value",value); delete cell; } void ExcelExportHelper::SaveAs(const QString& fileName) { if (fileName == "") throw invalid_argument("'fileName' is empty!"); if (fileName.contains("/")) throw invalid_argument("'/' character in 'fileName' is not supported by excel!"); if (QFile::exists(fileName)) { if (!QFile::remove(fileName)) { throw new exception(QString("Failed to remove file '%1'").arg(fileName).toStdString().c_str()); } } m_workbook->dynamicCall("SaveAs (const QString&)", fileName); } ExcelExportHelper::~ExcelExportHelper() { if (m_excelApplication != nullptr) { if (!m_closeExcelOnExit) { m_excelApplication->setProperty("DisplayAlerts", 1); m_excelApplication->dynamicCall("SetVisible(bool)", true ); } if (m_workbook != nullptr && m_closeExcelOnExit) { m_workbook->dynamicCall("Close (Boolean)", true); m_excelApplication->dynamicCall("Quit (void)"); } } delete m_sheet; delete m_sheets; delete m_workbook; delete m_workbooks; delete m_excelApplication; }Usage sample:
try { const QString fileName = "g:\\temp\\kaka2.xlsx"; ExcelExportHelper helper; helper.SetCellValue(1,1,"Test-11"); helper.SetCellValue(1,2,"Test-12"); helper.SaveAs(fileName); } catch (const exception& e) { QMessageBox::critical(this, "Error - Demo", e.what()); } - Add to .pro file:
-
Just 2 hours and it works?
Amazing! Somebody did a good job on COM support in Qt.I remember it took me a week to get it work using MFC.
And first thing I had to do is study COM. -
Old post but worth sharing: QtXlsxWriter is probably the easiest way to handle excel files and does not require excel installed. The author of the library disappeared from the face of earth but still a decent community support.
A Qt 5.6 compatible version is available in this fork: https://github.com/OlivierDelbeke/QtXlsxWriterEdit: post is outdated, refer to https://wiki.qt.io/Handling_Microsoft_Excel_file_format
-
