Time-Performance problems reading/writing excel files with ActiveQt

[Cyrano]

Hi everybody!

I'm developing with QT an application that will read excel file, make some processing on the data and then rewrite the results on the file itself... Conceptually very simple (the complex part should be the data-processing). I found a good example on the net (probably i found it in this forum, but I don' remember exactly... if it is in this way sorry for the duplication) where it is explained how to read/write excel with QT:

#include <QtGui>
#include <QAxObject>
#include <QAxWidget>
 
int main(int argc, char **argv)
{
   QApplication a(argc, argv);
 
   QAxWidget excel("Excel.Application");
   excel.setProperty("Visible", true);
 
   QAxObject * workbooks = excel.querySubObject("WorkBooks");
   workbooks->dynamicCall("Open (const QString&)", QString("e:/test/test.xls"));
   QAxObject * workbook = excel.querySubObject("ActiveWorkBook");
   QAxObject * worksheets = workbook->querySubObject("WorkSheets");
   int intCount = worksheets->property("Count").toInt();
   for (int i = 1; i <= intCount; i++)
   {
  	int intVal;
  	QAxObject * worksheet = workbook->querySubObject("Worksheets(int)", i);
  	qDebug() << i << worksheet->property("Name").toString();
  	QAxObject * range = worksheet->querySubObject("Cells(1,1)");
  	intVal = range->property("Value").toInt();
  	range->setProperty("Value", QVariant(intVal+1));
 
  	QAxObject * range2 = worksheet->querySubObject("Range(C1)");
  	intVal = range2->property("Value").toInt();
  	range2->setProperty("Value", QVariant(intVal+1));
   }
 
   QAxObject * worksheet = workbook->querySubObject("Worksheets(int)", 1);
   QAxObject * usedrange = worksheet->querySubObject("UsedRange");
   QAxObject * rows = usedrange->querySubObject("Rows");
   QAxObject * columns = usedrange->querySubObject("Columns");
   int intRowStart = usedrange->property("Row").toInt();
   int intColStart = usedrange->property("Column").toInt();
   int intCols = columns->property("Count").toInt();
   int intRows = rows->property("Count").toInt();
 
   for (int i = intRowStart; i < intRowStart + intRows; i++)
   {
  	for (int j = intColStart; j <= intColStart + intCols; j++)
  	{
     	QAxObject * range = worksheet->querySubObject("Cells(int,int)", i, j );
     	qDebug() << i << j << range->property("Value");
  	}
   }
   excel.setProperty("DisplayAlerts", 0);
   workbook->dynamicCall("SaveAs (const QString&)", QString("e:/test/xlsbyqt.xls"));
   excel.setProperty("DisplayAlerts", 1);
   workbook->dynamicCall("Close (Boolean)", false);
 
   excel.dynamicCall("Quit (void)");
   return 0;
}

#include <QtGui>
#include <QAxObject>
#include <QAxWidget>

int main(int argc, char **argv)
{
   QApplication a(argc, argv);

   QAxWidget excel("Excel.Application");
   excel.setProperty("Visible", true);

   QAxObject * workbooks = excel.querySubObject("WorkBooks");
   workbooks->dynamicCall("Open (const QString&)", QString("e:/test/test.xls"));
   QAxObject * workbook = excel.querySubObject("ActiveWorkBook");
   QAxObject * worksheets = workbook->querySubObject("WorkSheets");
   int intCount = worksheets->property("Count").toInt();
   for (int i = 1; i <= intCount; i++)
   {
  	int intVal;
  	QAxObject * worksheet = workbook->querySubObject("Worksheets(int)", i);
  	qDebug() << i << worksheet->property("Name").toString();
  	QAxObject * range = worksheet->querySubObject("Cells(1,1)");
  	intVal = range->property("Value").toInt();
  	range->setProperty("Value", QVariant(intVal+1));

  	QAxObject * range2 = worksheet->querySubObject("Range(C1)");
  	intVal = range2->property("Value").toInt();
  	range2->setProperty("Value", QVariant(intVal+1));
   }
   
   QAxObject * worksheet = workbook->querySubObject("Worksheets(int)", 1);
   QAxObject * usedrange = worksheet->querySubObject("UsedRange");
   QAxObject * rows = usedrange->querySubObject("Rows");
   QAxObject * columns = usedrange->querySubObject("Columns");
   int intRowStart = usedrange->property("Row").toInt();
   int intColStart = usedrange->property("Column").toInt();
   int intCols = columns->property("Count").toInt();
   int intRows = rows->property("Count").toInt();

   for (int i = intRowStart; i < intRowStart + intRows; i++)
   {
  	for (int j = intColStart; j <= intColStart + intCols; j++)
  	{
     	QAxObject * range = worksheet->querySubObject("Cells(int,int)", i, j );
     	qDebug() << i << j << range->property("Value");
  	}
   }
   excel.setProperty("DisplayAlerts", 0);
   workbook->dynamicCall("SaveAs (const QString&)", QString("e:/test/xlsbyqt.xls"));
   excel.setProperty("DisplayAlerts", 1);
   workbook->dynamicCall("Close (Boolean)", false);

   excel.dynamicCall("Quit (void)");
   return 0;
}

To copy to clipboard, switch view to plain text mode 

This example is working fine in my application (i.e. I'm able to read/write xls files using it) but it has big problems in time-performance.
I'm working with medium-size files (1MByte, more or less... 6000rows x 40cols, for example). Excel (or OpenOffice) can open the file in a few seconds,
my application (using the code above) needs about 30 minutes to execute the same operation!!! I have tested it on two different pc.
So, I "suppose" there is something is not working properly... but i can't find where is the problem
1. The problem is in the code? I don't think of it... I have looked for other examples and I have almost always found the same code instructions (using "Cells(i, j)").
2. The problem is in the "settings" of my pc? I'm using VisualStudio 2010, Qt 4.8.1 open source, Office 2010.
3. The problem is in QT itself? Maybe it isn't the right solution for this kind of program...

Does anybody know something more about it?

In the meantime I have found a work-around to read the data from excel file: I save the sheets of the xls in different csv files and then I parse them.
It is working but using csv is leading to other problems:
1. Different separators/delimiters. For example, my home pc uses the comma "," and the office-pc uses the semicolon ";".
2. How to load (programmatically, of course) the csv files (that i modified after the data-processing) in different sheets of one single xls file?
3. Even if I find a way to merge the csv files in on xls, how to apply on the new xls the same formatting of the original xls?.

Thanks in advance... helps and suggestions will be appreciated!
Cyrano

[mvuori]

I would do the following:
1) Remove the debug messages from the loops - there is quite a lot of those and they can be quite slow...
2) If that doesn't solve the problem, remove parts of the code to locate the bottleneck -- one of the loops first