Qt Operations Excel

QT to Excel data read/write operation there is no existing class, need to use Qaxobject, the following is downloaded from the Internet, a package of good class, feel can, generally enough, to give you to share.

Header file:

#ifndef Excelengine_h
#define Excelengine_h

#include <QObject>
#include <QFile>
#include <QString>
#include <QStringList>
#include <QVariant>
#include <QAxBase>
#include <QAxObject>
#include <QTableWidget>
#include <QTableView>
#include <QTableWidgetItem>
#include <QDebug>

 typedef unsigned int UINT; 
  /** 
  * @brief This is a class that allows QT to read and write Excel encapsulation, while at the same time, it is easy to display the data in Excel 
 * to the interface,  or write the data on the interface into Excel and interact with the GUI as follows: 
 *QT tablewidget <--> excelengine <--> xls file. 
 * 
 * @note Excelengine class is only responsible for reading/writing data, do not parse, do middle layer 
 * @author Yaoboyuan [email protected] 
 * @date 2012-4-12 
 */ 

 class excelengine:protected Qobject 
 {
 Public: 
 Excelengine (); 
 Excelengine (QString xlsfile); 
 ~excelengine ();          
 public: 
  bool Open (UINT nsheet = 1,bool Visible = False);    Open the XLS file 
 bool Open (QString xlsfile,uint nsheet = 1,bool Visible = false); 
                                              void Save ();                                             Save XLS report 
 void Close ();          Close the XLS report 
  bool Savedatafrtable (qtablewidget* tablewidget);          Save data to XLS 
  bool readdatatotable (qtablewidget* tablewidget);               Read data from XLS to UI 
  Qvariant Getcelldata (UINT row,uint column); Gets the specified cell data 
  bool Setcelldata (UINT row,uint column,qvariant data); Modifies the specified cell data 

    UINT GetRowCount () const;
    UINT getColumnCount () const;

    BOOL IsOpen ();
    BOOL IsValid ();

Protected
    void Clear ();

Private
    Qaxobject *pexcel;       Point to the entire Excel application
    Qaxobject *pworkbooks;   Point to Workbook Set, Excel has many workbooks
    Qaxobject *pworkbook;    Workbooks that point to Sxlsfile
    Qaxobject *pworksheet;   Point to a sheet form in the workbook

    QString   Sxlsfile;      XLS file path
    UINT      Ncurrsheet;    The first few sheet that are currently open
    BOOL      bisvisible;    Whether Excel is visible
    int       nRowCount;     Number of rows
    int       nColumnCount;  Number of columns

    int       Nstartrow;     Start row subscript value with data
    int       nstartcolumn;  Start column with data subscript value

    BOOL      Bisopen;       is already open
    BOOL      bisvalid;      is valid

    BOOL      bisanewfile;   is a new XLS file used to differentiate whether an open Excel file exists or a new
    BOOL      bissavealready;//prevents duplicate saves
};

  #endif//excelengine_h 
 
 Source file: 
  #include "excelengine.h" 
 # Include "Qt_windows.h" 
  
 Excelengine::excelengine () 
  {
 Pexcel = NULL; 
 Pworkbooks = NULL; 
 Pworkbook = NULL; 
 Pworksheet = NULL;    
  Sxlsfile = ""; 
  nRowCount = 0; 
 nColumnCount = 0; 
 Nstartrow = 0; 
 Nstartcolumn = 0;    
  Bisopen = false; 
  Bisvalid = false; 
 Bisanewfile = false; 
 Bissavealready = false; 
  HRESULT r = oleinitialize (0);  

    if (r! = S_OK && r! = S_FALSE)
    {
        Qdebug ("Qt:could not initialize OLE (error%x)", (unsigned int) r);
    }
}

Excelengine::excelengine (QString xlsfile)
{
    Pexcel     = NULL;
    Pworkbooks = NULL;
    Pworkbook  = NULL;
    Pworksheet = NULL;
    Sxlsfile     = xlsfile;
    nRowCount    = 0;
    nColumnCount = 0;
    Nstartrow    = 0;
    Nstartcolumn = 0;
    Bisopen     = false;
    Bisvalid    = false;
    Bisanewfile = false;
    Bissavealready = false;

    HRESULT r = oleinitialize (0);
    if (r! = S_OK && r! = S_FALSE)
    {
        Qdebug ("Qt:could not initialize OLE (error%x)", (unsigned int) r);
    }
}

Excelengine::~excelengine ()
{
    if (Bisopen)
    {
        Before you refactor, save the data and then close the workbook
        Close ();
    }
    OleUninitialize ();
}

/**
  * @brief Open the Excel report specified sxlsfile
  * @return true: Open success
  *        false: Open failed
  */

BOOL Excelengine::open (UINT nsheet, bool visible)
{
    if (Bisopen)
    {
        Close ();
    }
    Ncurrsheet = Nsheet;
    bisvisible = visible;

    if (NULL = = Pexcel)
    {
        Pexcel = new Qaxobject ("Excel.Application");
        if (Pexcel)
        {
            Bisvalid = true;
        }
        Else
        {
            Bisvalid = false;
            Bisopen  = false;
            return bisopen;
        }
        Pexcel->dynamiccall ("setvisible (BOOL)", bisvisible);
    }

    if (!bisvalid)
    {
        Bisopen = false;
        return bisopen;
    }

    if (Sxlsfile.isempty ())
    {
        Bisopen = false;
        return bisopen;
    }

/* If the pointing file does not exist, you need to create a new */
 QFile F (sxlsfile); 
    if (!f.exists ()) 
 {
 Bisanewfile = true; 
    
 Else 
 {
 Bisanewfile = false; 
 } 
  
 if (!bisanewfile) 
  {
 Pworkbooks = Pexcel->querysubo Bject ("WorkBooks"); Get workbook 
 Pworkbook = Pworkbooks->querysubobject ("Open (QString, qvariant)", Sxlsfile,qvariant (0));//Open the XLS corresponding work      Book 
} 
 Else 
 {
 Pworkbooks = Pexcel->querysubobject ("WorkBooks");                        Gets the workbook 
 Pworkbooks->dynamiccall ("Add"); Add a new workbook 
 Pworkbook = Pexcel->querysubobject ("ActiveWorkbook");//Create a new XLS 
} 
  Pworksheet = Pworkbook->querysubobject ("Worksheets (int)", ncurrsheet);//Open first sheet  

    Now open to get the corresponding property
    Qaxobject *usedrange = Pworksheet->querysubobject ("UsedRange");       Gets the usage scope object for the sheet
    Qaxobject *rows      = Usedrange->querysubobject ("Rows");
    Qaxobject *columns   = Usedrange->querysubobject ("columns");

    Because Excel can fill in the data from any row, not necessarily from 0, 0, so to get the first row subscript
    Nstartrow    = Usedrange->property ("Row"). ToInt ();    Start position of first line
    Nstartcolumn = Usedrange->property ("Column"). ToInt (); Start position of first column
    nRowCount    = Rows->property ("Count"). ToInt ();       Get row Count
    nColumnCount = Columns->property ("Count"). ToInt ();    Get Number of columns

    Bisopen  = true;
    return bisopen;
}

/**
  * @brief the overloaded function of Open ()
  */
BOOL Excelengine::open (QString xlsfile, UINT nsheet, bool visible)
{
    Sxlsfile   = xlsfile;
    Ncurrsheet = Nsheet;
    bisvisible = visible;
    Return Open (ncurrsheet,bisvisible);
}

/**
  * @brief Save the table data and write the data to the file
  */

void Excelengine::save ()
{
    if (Pworkbook)
    {
        if (Bissavealready)
        {
            return;
        }

        if (!bisanewfile)
        {
            Pworkbook->dynamiccall ("Save ()");
        }
        else/* If the document is newly created, use the Save as COM interface */
        {
            Pworkbook->dynamiccall ("SaveAs (const qstring&,int,const qstring&,const qstring&,bool,bool)",
                                   Sxlsfile,56,qstring (""), QString (""), False,false);
        }
        Bissavealready = true;
    }
}

/**
  * Save the data before closing @brief, then close the current Excel COM object and free up memory
  */

void Excelengine::close ()
{
    Save ();//store data before closing
    if (Pexcel && pworkbook)
    {
        Pworkbook->dynamiccall ("Close (BOOL)", true);
        Pexcel->dynamiccall ("Quit ()");
        Delete Pexcel;
        Pexcel = NULL;

        Bisopen        = false;
        Bisvalid       = false;
        Bisanewfile    = false;
        Bissavealready = true;
    }
}

/**
  * @brief Save the data in Tablewidget to Excel
  * @param tablewidget: Pointer to Tablewidget in GUI
  * @return Save Success true: Success
  *                   false: Failed
  */
BOOL Excelengine::savedatafrtable (Qtablewidget *tablewidget)
{
    if (NULL = = Tablewidget)
    {
        return false;
    }

    if (!bisopen)
    {
        return false;
    }

    int tabler = Tablewidget->rowcount ();
    int tableC = Tablewidget->columncount ();

    Get header Write first line
    for (int i=0;i<tablec;i++)
    {
        if (Tablewidget->horizontalheaderitem (i)! = NULL)
        {
            This->setcelldata (1,i+1,tablewidget->horizontalheaderitem (i)->text ());
        }
    }

    Write Data
    for (int i=0;i<tabler;i++)
    {
        for (int j=0;j<tablec;j++)
        {
            if (Tablewidget->item (i,j) = NULL)
            {
                This->setcelldata (I+2,j+1,tablewidget->item (i,j)->text ());
            }
        }
    }

    Save
    Save ();
    return true;
}

/**
  * @brief import data into Tablewidget from the specified XLS file
  * @param tablewidget: Perform the tablewidget pointer you want to import to
  * @return Import success or not: success
  *                   false: Failed
  */
BOOL Excelengine::readdatatotable (Qtablewidget *tablewidget)
{
    if (NULL = = Tablewidget)
    {
        return false;
    }

    Clear the contents of the table first
    int tablecolumn = Tablewidget->columncount ();
    Tablewidget->clear ();
    for (int n=0;n<tablecolumn;n++)
    {
        Tablewidget->removecolumn (0);
    }

int rowcnt = Nstartrow + nrowcount;
int columncnt = Nstartcolumn + nColumnCount;

Get the first row of data in Excel as a header
Qstringlist headerlist;
for (int n = nstartcolumn;n<columncnt;n++)
{
qaxobject* cell = Pworksheet->querysubobject ("Cells (Int,int)", Nstartrow, N);
if (cell)
{
Headerlist<<cell->dynamiccall ("Value2 ()"). ToString ();
}
}

Re-create the table header
Tablewidget->setcolumncount (nColumnCount);
Tablewidget->sethorizontalheaderlabels (headerlist);

Inserting new data
for (int i=nstartrow+1,r=0;i<rowcnt;i++,r++)//Line
{
Tablewidget->insertrow (R); Insert New row
for (int j=nstartcolumn,c=0;j<columncnt;j++,c++)//Column
{
Qaxobject * cell = Pworksheet->querysubobject ("Cells (int,int)", i,j);//Get cell
            
Add child data to a new row of R
if (cell)
{
Tablewidget->setitem (R,c,new Qtablewidgetitem (Cell->dynamiccall ("Value2 ()"). ToString ());
}
}
}

return true;
}

/**
  * @brief get the data for the specified cell
  * @param row: The line number of the cell
  * @param column: Number of cells
  * @return data for [row,column] cells
  */

Qvariant Excelengine::getcelldata (UINT row, uint column)
{
    Qvariant data;
    Qaxobject *cell = Pworksheet->querysubobject ("Cells (int,int)", row,column);//Get Cell object

    if (cell)
    {
        data = Cell->dynamiccall ("Value2 ()");
    }
    return data;
}

/**
  * @brief Modify the data for the specified cell
  * @param row: The line number of the cell
  * @param column: The number specified by the cell
  * @param data: The new information to be modified by the cell
  * @return The modification is successful true: Success
  *                   false: Failed
  */

BOOL Excelengine::setcelldata (UINT row,uint column,qvariant data)
{
    BOOL op = false;
    Qaxobject *cell = Pworksheet->querysubobject ("Cells (int,int)", row,column);//Get Cell object

    if (cell)
    {
        QString strdata = data.tostring (); Excel can only insert strings and integers, floating-point type cannot be inserted
        Cell->dynamiccall ("SetValue (const qvariant&)", strdata); Modify the data of a cell
        OP = true;
    }
    Else
    {
        op = false;
    }

    return op;
}

/**
  * @brief empty the data except the report
  */

void Excelengine::clear ()
{
    Sxlsfile     = "";
    nRowCount    = 0;
    nColumnCount = 0;
    Nstartrow    = 0;
    Nstartcolumn = 0;
}

/**
  * @brief determine if Excel has been opened
  * @return true: Opened
  *        false: Not open
  */
BOOL Excelengine::isopen ()
{
    return bisopen;
}

/**
  * @brief determine if Excel COM object is invoked successfully, Excel is available
  * @return true: Available
  *        false: Not available
  */
BOOL Excelengine::isvalid ()
{
    return bisvalid;
}

/**
  * @brief get the number of lines in Excel
  */
UINT Excelengine::getrowcount () const
{
    return nrowcount;
}

/**
  * @brief Get the number of columns in Excel
  */

UINT Excelengine::getcolumncount () const
{
    return ncolumncount;
}

Simple to use:

/*
Excelengine Excel (Qobject::tr ("C:\\test.xls")); Create
Excel. Open (); Open it

int num = 0;
for (int i=1; i<=10; i++)
{
for (int j=1; j<=10; j + +)
{
Excel. Setcelldata (I,j,++num); Modify the specified cell data
}
}

Qvarient data = Excel. Getcelldata (a); Accessing the specified cell data
Excel. Getcelldata (2,2);
Excel. Getcelldata (3,3);
Excel. Save (); Save
Excel. Close ();
*/

Import data into Tablewidget
/*
Excelengine Excel (Qobject::tr ("C:\\import.xls"));
Excel. Open ();
Excel. Readdatatotable (Ui->tablewidget); Import into Widget
Excel. Close ();
*/

Export data from Tablewidget to Excel
/*
Excelengine Excel (Qobject::tr ("C:\\export.xls"));
Excel. Open ();
Excel. Savedatafrtable (Ui->tablewidget); Export a report
Excel. Close ();
*/

Qt Operations Excel