*SOLVED* Inserting and deleting rows in an excel file with QAxObject-CodePudding

I would like to insert a row to an excel file with QT and QAxObject functions like dynamicCall("insertRow(int)", rowindex). I tried to do it using the code I wrote below, but I am getting an error message. I've read the documentation on QAxobject and QAxBase but couldn't find any information about it. Other examples are for editing an existing cell or row, not inserting a blank row in between.

    QAxObject* excel     = new QAxObject("Excel.Application",0);
    QAxObject* workbooks = excel->querySubObject("Workbooks");
    QAxObject* workbook  = workbooks->querySubObject("Open(const QString&)","C:\\Users\\pc\\Desktop\\myFile.xlsx");
    QAxObject* sheets = workbook->querySubObject( "Worksheets" );
    QAxObject* first_sheet = sheets->querySubObject( "Item( int )", 1 );

    QAxObject* rows = first_sheet->querySubObject("Rows");
    rows->dynamicCall("Insert(int)", 10);

Here is the error message;

QAxBase: Error calling IDispatch member Insert: Exception thrown by server
             Code       : -2146827284
             Source     : Microsoft Excel
             Description: To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet.  Select another location in which to insert new cells, or delete data from the end of your worksheet.

If you do not have data in cells that can be shifted off of the worksheet, you can reset which cells Excel considers nonblank.  To do this, press CTRL End to locate the last nonblank cell on the worksheet.  Delete this cell and all cells between it and the last row and column of your data then save.
             Help       : xlmain11.chm
         Connect to the exception(int,QString,QString,QString) signal to catch this exception

CodePudding user response：

After some trials, I figured out how to do it :) I was trying to insert inside the "Rows" object, but what I had to do was to select a row from the "Rows" and use the "Insert()" function on it. To delete a row with the same method, the desired row can be selected and the "Delete()" function can be used. I've been struggling with how to solve this for days, hope it helps others too. Here are the codes;

QAxObject* excel     = new QAxObject("Excel.Application",0);
QAxObject* workbooks = excel->querySubObject("Workbooks");
QAxObject* workbook  = workbooks->querySubObject("Open(const QString&)","C:\\Users\\pc\\Desktop\\deneme2.xlsx");
QAxObject* sheets = workbook->querySubObject( "Worksheets" );
QAxObject* first_sheet = sheets->querySubObject( "Item( int )", 1 );

QAxObject* rows = first_sheet->querySubObject("Rows");
QAxObject* row = first_sheet->querySubObject("Rows(int)", 10); //Selects 10th row from rows
row->dynamicCall("Insert()"); //Inserts a new blank row at row 10
row->dynamicCall("Delete()"); //Deletes 10th row