Qt write/import qlist<qvariantlist> data to excel with qAxObject
-
wrote on 11 Sept 2019, 10:54 last edited by
void MainWindow::dragEnterEvent(QDragEnterEvent *event)
{
if(event->mimeData()->hasUrls()){
event->acceptProposedAction();
}}
void MainWindow::dropEvent(QDropEvent *event)
{
if (event->mimeData()->hasUrls()) {
const QList<QUrl> files = event->mimeData()->urls();
if (files.isEmpty())
return;const QString path = files.first().toLocalFile(); const QString newPath = path+"updated"; QAxObject* excel = new QAxObject( "Excel.Application", this ); //excel->setProperty("Visible", true); //excel->setProperty("DisplayAlerts", false); QAxObject* workbooks = excel->querySubObject( "Workbooks" ); QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", path ); QAxObject* sheets = workbook->querySubObject( "Worksheets" ); QList<QVariantList> data; //Data list from excel, each QVariantList is worksheet row //worksheets count int count = sheets->dynamicCall("Count()").toInt(); count = sheets->property("Count").toInt(); for (int i=1; i<=count; i++) //cycle through sheets { //sheet pointer QAxObject* sheet = sheets->querySubObject( "Item( int )", i ); QAxObject* rows = sheet->querySubObject( "Rows" ); int rowCount = rows->dynamicCall( "Count()" ).toInt(); //unfortunately, always returns 255, so you have to check somehow validity of cell values QAxObject* columns = sheet->querySubObject( "Columns" ); int columnCount = columns->property("Count").toInt(); //QMessageBox::warning(this, "", QString("%1 - %2").arg(QString::number(columnCount), QString::number(rowCount))); for (int column=1; column <= qMin(columnCount, 20); column++) { QVariantList dataRow; QVariant value; QVariant temp; for (int row=1; row <= qMin(rowCount, 20); row++) { QAxObject* cell = sheet->querySubObject("Cells(int,int)",row,column); value = cell->dynamicCall("Value()"); QString temp = value.toString(); int indexOfRow = 0; while(temp.isEmpty()) { indexOfRow++; cell = sheet->querySubObject("Cells(int,int)",row-indexOfRow,column); value = cell->dynamicCall("Value()"); temp = value.toString(); QMessageBox::warning(this, "", temp); } dataRow.append(value); } data.append(dataRow); } } QAxObject *newExcel = new QAxObject( "Excel.Application", this ); QAxObject *newWorkbooks = newExcel->querySubObject("Workbooks"); newWorkbooks->dynamicCall("Open()const QString&",newPath); newWorkbooks->dynamicCall("Add"); //QVariant variantNewWorkbook = newWorkbooks->asVariant(); //QVariantList nWL = variantNewWorkbook. excel->setProperty("Visible", true); excel->setProperty("DisplayAlerts", false); workbook->dynamicCall("Close()"); excel->dynamicCall("Quit()"); newExcel->dynamicCall("Quit()"); }
}
first of all i m little bit bad in english so, sorry for my bad english.
i m trying to use "data" list and import/write it to another excel file but i'm stuck so bad. i searched so many forums but couldnt find anything usefull so far.Any help would appreciated thx a lot.
-
void MainWindow::dragEnterEvent(QDragEnterEvent *event)
{
if(event->mimeData()->hasUrls()){
event->acceptProposedAction();
}}
void MainWindow::dropEvent(QDropEvent *event)
{
if (event->mimeData()->hasUrls()) {
const QList<QUrl> files = event->mimeData()->urls();
if (files.isEmpty())
return;const QString path = files.first().toLocalFile(); const QString newPath = path+"updated"; QAxObject* excel = new QAxObject( "Excel.Application", this ); //excel->setProperty("Visible", true); //excel->setProperty("DisplayAlerts", false); QAxObject* workbooks = excel->querySubObject( "Workbooks" ); QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", path ); QAxObject* sheets = workbook->querySubObject( "Worksheets" ); QList<QVariantList> data; //Data list from excel, each QVariantList is worksheet row //worksheets count int count = sheets->dynamicCall("Count()").toInt(); count = sheets->property("Count").toInt(); for (int i=1; i<=count; i++) //cycle through sheets { //sheet pointer QAxObject* sheet = sheets->querySubObject( "Item( int )", i ); QAxObject* rows = sheet->querySubObject( "Rows" ); int rowCount = rows->dynamicCall( "Count()" ).toInt(); //unfortunately, always returns 255, so you have to check somehow validity of cell values QAxObject* columns = sheet->querySubObject( "Columns" ); int columnCount = columns->property("Count").toInt(); //QMessageBox::warning(this, "", QString("%1 - %2").arg(QString::number(columnCount), QString::number(rowCount))); for (int column=1; column <= qMin(columnCount, 20); column++) { QVariantList dataRow; QVariant value; QVariant temp; for (int row=1; row <= qMin(rowCount, 20); row++) { QAxObject* cell = sheet->querySubObject("Cells(int,int)",row,column); value = cell->dynamicCall("Value()"); QString temp = value.toString(); int indexOfRow = 0; while(temp.isEmpty()) { indexOfRow++; cell = sheet->querySubObject("Cells(int,int)",row-indexOfRow,column); value = cell->dynamicCall("Value()"); temp = value.toString(); QMessageBox::warning(this, "", temp); } dataRow.append(value); } data.append(dataRow); } } QAxObject *newExcel = new QAxObject( "Excel.Application", this ); QAxObject *newWorkbooks = newExcel->querySubObject("Workbooks"); newWorkbooks->dynamicCall("Open()const QString&",newPath); newWorkbooks->dynamicCall("Add"); //QVariant variantNewWorkbook = newWorkbooks->asVariant(); //QVariantList nWL = variantNewWorkbook. excel->setProperty("Visible", true); excel->setProperty("DisplayAlerts", false); workbook->dynamicCall("Close()"); excel->dynamicCall("Quit()"); newExcel->dynamicCall("Quit()"); }
}
first of all i m little bit bad in english so, sorry for my bad english.
i m trying to use "data" list and import/write it to another excel file but i'm stuck so bad. i searched so many forums but couldnt find anything usefull so far.Any help would appreciated thx a lot.
wrote on 11 Sept 2019, 15:09 last edited by@oldthrasher Hi,
What is not working exactly ? Browsing the source file ? saving in the other one ?
This line seems strange to me, why is there
const QString &
in the string ? :newWorkbooks->dynamicCall("Open()const QString&",newPath);
-
wrote on 11 Sept 2019, 15:15 last edited by
If you are working with Xlsx files only, you may be interested by QXlsx library which is probably easier to use IMHO.
-
@oldthrasher Hi,
What is not working exactly ? Browsing the source file ? saving in the other one ?
This line seems strange to me, why is there
const QString &
in the string ? :newWorkbooks->dynamicCall("Open()const QString&",newPath);
wrote on 12 Sept 2019, 07:36 last edited by@gojir4 i am browsing an excel file it's working good. but i'm trying to open another excel(second excel) file and import all the data that is in the first excel file(source file) to second excel file.
code exports all the data from first excel (source file)
code not imports the data to second excel. -
@gojir4 i am browsing an excel file it's working good. but i'm trying to open another excel(second excel) file and import all the data that is in the first excel file(source file) to second excel file.
code exports all the data from first excel (source file)
code not imports the data to second excel.wrote on 12 Sept 2019, 08:19 last edited by@oldthrasher Did you try to fix the string for calling
Open
?newWorkbooks->dynamicCall("Open(const QString&)", newPath);
But anyway I see nothing in your code to write the data in the new file created. You are calling
Add
without any argument.
You need to loop ondata
to write them on the new excel file. BTW It could be more efficient to open the new excel file before the loop and then filling it with data at the same time you are reading them from the source file.
1/5