How to export excel in Qt?



  • Hi,
    How to export excel in qt? Any sample code or url pls. Which qt api use export in excel?

    Regards,
    Mathi





  • thanks qxoz



  • By the way, I do not use these libraries. Can you share the experience, after you will use them?



  • Hi qxoz,

    i have qt lib 4.7.4. how to use QAxObject api base export excel?

    Regards,
    Mathi



  • 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.

    1. Add to .pro file:
      QT += axcontainer
    2. 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_H
    
    

    File: 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());
        }
    


  • 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/QtXlsxWriter


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.