Working with Excel Sheet?

holeygaurav

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

Pavanesh

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

Pavanesh

Did u find solution for ur problem?

infoctopus

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

Skyrim

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

everything else was fine.

Skyrim

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.

Skyrim

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?

Skyrim

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

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

black_coder

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.

leninbooter

[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

Skyrim

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.

Skyrim

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

leninbooter

:-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

Skyrim

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

leninbooter

:P I'm sure it is :D

leninbooter

[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?

vsgobbi

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);
        }
    }

}
@