Working with Excel Sheet?



  • Dear All,

    I am trying to use excel sheet.I am able to open the excel sheet and write the data into the one cell at a time, but not able to create the excel sheet and various other functionalities of excel sheet through "dynamicCall". Please guide me on this.

    Please refer the code and suggest the suitable changes.

    I have referred to "Using ActiveX Object in Qt" tutorial available on this forum.
    @

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

    QAxObject * workbooks = excel.querySubObject("WorkBooks");

    workbooks->dynamicCall("Add");
    //workbooks->dynamicCall("Open (const QString&)", QString("C:/test7.xls"));
    QAxObject * workbook = excel.querySubObject("ActiveWorkBook");
    QAxObject * worksheets = workbook->querySubObject("WorkSheets");

    workbook->dynamicCall("SaveAs (const QString&)", QString("C:\test7.xls"));
    workbook->dynamicCall("Close (Boolean)", false);
    workbooks->dynamicCall("Open (const QString&)", QString("C:/test7.xls"));

    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("Range(F6)");//ABLE TO WRITE DATA IN CELL F6
    intVal = range->property("Value").toInt();
    range->setProperty("Value", QVariant(7/intVal+1/));//DATA TO BE WRITTEN=7

        QAxObject * range2 = worksheet->querySubObject("Range(C4)"); //ABLE TO WRITE DATA IN CELL C4
        intVal = range2->property("Value").toInt();
        range2->setProperty("Value", QVariant(4/*intVal+1*/));//DATA TO BE WRITTEN=4
    

    }

    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("C:\test5.xls"));
    excel.setProperty("DisplayAlerts", 1);
    // workbook->dynamicCall("Close (Boolean)", false);

    //excel.dynamicCall("Quit (void)");@

    Any help/Tutorial on working of the dynamicCall, QAxObject/QFile would be greatly appreciated.

    Thanks and Regards,
    Gaurav Holey



  • Hi,
    Can u pls tell me what r the funnctionalities you want try?



  • Did u find solution for ur problem?



  • could you provide us with code that doesn't work? (e.g. "not able to create the excel sheet")

    Everything must be fine, as long as you use Excel object model properly



  • Hi all
    Error in row 9.
    Should be:
    @
    QAxObject *worksheets = workbook->querySubObject("Sheets");
    worksheets->dynamicCall("Add()"); //insert new Sheet
    @

    everything else was fine.



  • Interested in this question - anyone got access to a Cells?

    my code does not work.

    @
    QAxObject *excel;
    QAxObject *wbooks;
    QAxObject *book;
    QAxObject *sheets;
    QAxObject *cell;
    QTableWidgetItem *wit;

    excel = new QAxObject("Excel.Application", this);
    excel->setProperty("Visible", 1);
    excel->setProperty("DisplayAlerts", 0);
    wbooks = excel->querySubObject("Workbooks");
    book = wbooks->querySubObject("Open (const QString&)", ui.lineEdit->text());
    sheets = book->querySubObject("Sheets");
    ui.spinBox->setValue(sheets->dynamicCall("Count()").toInt());

    for (int i = 0; i<ui.tableWidget->rowCount(); i++){
    for (int j =0; j<ui.tableWidget->columnCount(); j++){
    cell = excel->querySubObject("Cells(QVariant&, QVariant&)", i+1, j+1);
    wit = new QTableWidgetItem(cell->property("Value()").toString());
    //wit = new QTableWidgetItem(QString("%1,%2").arg(i).arg(j));
    ui.tableWidget->setItem(i, j, wit);
    }
    @
    As a result, the table is filled with zeroes.
    The file that I open only one sheet and the cells are filled with arbitrary integers.
    What am I doing wrong? Please help.



  • So, my efforts were crowned with success.
    Thus it is possible to transfer data from .xls file into a QTableWidget.

    @
    for (int i = 1; i <= 5; i++) {
    for (int j = 1; j <= 3; j++) {
    cell = excel->querySubObject("Cells(Int, Int)", i, j);
    QString valC = cell->dynamicCall("value").toString();
    // ui.textEdit->append(valC);//-- simple check
    QTableWidgetItem *it = new QTableWidgetItem;
    it->setText(valC);
    //it->setText(cell->dynamicCall("Value").toString());// ----- ??? it's doesn't work ???
    ui.tableWidget->setItem(i-1, j-1, it);
    }
    }
    @
    Let's move on.
    Has anyone run macros?



  • As it turned out, run Excel macro knowing his name - very simple.

    @
    QString nameMacro = "mac"; //name macro
    excel->dynamicCall("Run(QVariant)", nameMacro);
    @



  • Anyone knows how to extract an attached images inside a cell using the excel activex ? I can read and write normal text but I can't manage to extract images attached inside a cell. Any help would be appreciated.



  • [quote author="Skyrim" date="1307478863"]As it turned out, run Excel macro knowing his name - very simple.

    @
    QString nameMacro = "mac"; //name macro
    excel->dynamicCall("Run(QVariant)", nameMacro);
    @

    [/quote]

    Hello, I've been trying to make this work and just doesn't
    I can't find the mistake

    seleccion->dynamicCall("GoTo(const QString&, const QString&, const QString&)","wdGoToPage","wdGoToAbsolute", pag);
    

    I really would thank you very much if you can help me



  • Sorry but this does not apply to work with Excel Sheets, its work with Word Documents
    It would be nice to look at your code, because I do not understand what you are trying to do.



  • ok, let it be Word
    for example, "go to" the 3 line.

    @
    word = new QAxObject("Word.Application", this);
    word->setProperty("DisplayAlerts", false);
    doc = word->querySubObject("Documents");
    doc->dynamicCall("Open(QVariant)", "d:\a15.doc");
    word->querySubObject("Selection")->querySubObject("GoTo(Int, Int, Int)", 3, 1, 3);
    @
    Selection.GoTo(What, Which, Count, Name)
    What:=wdGoToLine - emun = 3;
    Which:=wdGoToAbsolute - enum = 1;
    Count:= 3 - destination line
    -Name - forget about Name



  • :-O OMG , dude, thank you very much... So, I have to use the correspoding number of the enumeration instead of the name of the enumeration (wdGoToLine) it works!!! thank you veryyyyyyyy
    Sorry for misplacing this but I thought this was the most related thread :P
    Thank you againnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn



  • Come on,
    ... and welcome to the best qt forum



  • :P I'm sure it is :D



  • [quote author="Skyrim" date="1305238978"]Hi all
    Error in row 9.
    Should be:
    @
    QAxObject *worksheets = workbook->querySubObject("Sheets");
    worksheets->dynamicCall("Add()"); //insert new Sheet
    @

    everything else was fine.[/quote]

    Hi, was this running inside a qt dialog or window? or outside ur qt application?



  • Skyrim! Thanks for the tip. Works great!
    I have to do the same thread, but comparing one row, which has a product description of my .xls file (as database) with bar code input by code bar reader of the software user.
    I'm just adding this to my *.pro *Project File:

    @CONFIG += qaxcontainer
    DEF_FILE = qaxserver.def
    RC_FILE = qaxserver.rc@

    This on my .cpp file working with a new "QTableWidget":

    @{
    QAxObject *excel;
    QAxObject *wbooks;
    QAxObject *book;
    QAxObject *sheets;
    QAxObject *cell;
    QTableWidgetItem *wit;

    excel = new QAxObject("Excel.Application", this);
    excel->setProperty("Visible", 1);
    excel->setProperty("DisplayAlerts", 0);
    wbooks = excel->querySubObject("Workbooks");
    book = wbooks->querySubObject("Open (const QString&)", "C:\\1.xls" );;
    sheets = book->querySubObject("Sheets");
    ui->spinBox->setValue(sheets->dynamicCall("Count()").toInt());
    
    
    for (int i = 0; i<ui->tableWidget->rowCount(); i++){
        for (int j =0; j<ui->tableWidget->columnCount(); j++){
            cell = excel->querySubObject("Cells(QVariant&, QVariant&)", i+1, j+1);
            wit = new QTableWidgetItem(cell->property("Value()").toString());
            wit = new QTableWidgetItem(QString("%1,%2").arg(i).arg(j));
            ui->tableWidget->setRowCount(10);
            ui->tableWidget->setColumnCount(5);
            ui->tableWidget->setItem(i, j, wit);
        }
    }
    
    for (int i = 1; i <= 5; i++) {
            for (int j = 1; j <= 3; j++) {
                cell = excel->querySubObject("Cells(Int, Int)", i, j);
                QString valC = cell->dynamicCall("value").toString();
                ui->textEdit->append(valC);//-- simple check
                QTableWidgetItem *it = new QTableWidgetItem;
                it->setText(valC);
                it->setText(cell->dynamicCall("Value").toString());
    
                ui->tableWidget->setItem(i-1, j-1, it);
                ui->tableWidget->setRowCount(10);
                ui->tableWidget->setColumnCount(5);
            }
        }
    

    }
    @


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.