How to export excel in Qt?

[mathi77in]

thanks qxoz

[qxoz]

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

[mathi77in]

Hi qxoz,

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

Regards,
Mathi

[qxoz]

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

[jdavet]

see http://qt-project.org/forums/viewthread/2144/
and http://qt-project.org/wiki/Handling_Microsoft_Excel_(file_format)

[hammerfreedom]

Is this work with Qt 5.3.0?

[sharayu]

//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);

[drweb86]

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

[alex_malyu]

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.

[VRonin]

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

Edit: post is outdated, refer to https://wiki.qt.io/Handling_Microsoft_Excel_file_format

[Qt-member]

As drweb86, it just run on window OS, because office must installed on Window. It is simple that Open Excel App, visible and prepair data, then write. And save as name. If your OS have not Excel app then can't use this way. I tryed. Thank you