How to set excel column format though Qt?

Question

I want to write some tel numbers to excel file, some of them start with 0(like 02167820096). I try to set the NumberFormatLocal property of that column to string type:

        QAxObject* col=worksheet->querySubObject("Columns(int)",1);
        if (!col)
        {
            qDebug()<<"col is NULL";
        }
        qDebug()<<"col 1 NumberFormatLocal:"<<col->property("NumberFormatLocal").toString();
        col->setProperty("NumberFormatLocal","@");
        qDebug()<<"col 1 NumberFormatLocal:"<<col->property("NumberFormatLocal").toString();

The output is

col 1 NumberFormatLocal: "G/通用格式" 
col 1 NumberFormatLocal: "@"

and I can see the cell in 1st column is indeed set to string type("@").

            QAxObject * range = worksheet->querySubObject("Cells(int,int)", 1, 1);
            if (!range)
            {
                qDebug()<<"range does not exist";
            }
            QVariant tel=QString("%1").arg(record["tel"].toString()); //tel is 02167820096
            //qDebug()<<tel;
            //range->dynamicCall("SetValue(const QVariant&)", tel);
            qDebug()<<"NumberFormatLocal:"<<range->property("NumberFormatLocal").toString();
            qDebug()<<"NumberFormat:"<<range->property("NumberFormat").toString();
            range->setProperty("Value", tel.toString());
            range->clear();

and the output is

NumberFormatLocal: "@" 
NumberFormat: "@"

but when I open the saved excel file, all cells in it are marked as general type, the code didn't work at all!

Help! Thanks...

I also tried this:range->dynamicCall("SetNumberFormatLocal(const QVariant&)", QVariant("@"));but it doesn't work either... — areslp, Jun 21 '12 at 16:35
I want to release the resource. And I also tried without this expressing, there is no difference... — areslp, Jun 22 '12 at 00:58

score 0 · Accepted Answer · answered Jun 22 '12 at 04:15

OK,I solved this problem. Because I install Office2007, the "Save" function will save the file in 2007 format, but I save it to "*.xls", some format can not be recognized correctly.

So the solution is, use "SaveAs" to save excel to 2003 format.

        QList<QVariant> lstParam;  
        qDebug()<<QDir::toNativeSeparators(file_path);
        lstParam.append(QDir::toNativeSeparators(file_path));  
        lstParam.append(-4143);   
        lstParam.append("");   
        lstParam.append("");   
        lstParam.append(false);   
        lstParam.append(false);   
        lstParam.append(1);  
        lstParam.append(2);  
        lstParam.append(false);   
        lstParam.append(false);   
        lstParam.append(false);   
        lstParam.append(false);   
        QVariant res = workbook->dynamicCall("SaveAs(QVariant, QVariant, QVariant, QVariant, QVariant, QVariant, QVariant, QVariant, QVariant, QVariant, QVariant, QVariant)", lstParam);  
        if(res.toBool())  
        {   
            qDebug()<<"SaveAs successful";
        }

How to set excel column format though Qt?

1 Answers1