Formula is being copied, not formula value? about qtxlsxwriter

[dbzhang800]

Hi, this is an intended behavior. As you can see,

When you call xlsx.write("A1", "=1+2"); to generate a xlsx file, it make sense to let xlsx.read("A1") return the formula itself.

Another problem to return the value instead of the formula is that, you must use sheet.writeFormula("A1", "=1+2", Format(), 3) to generate a xlsx file which contains proper formula result. In normal case, 0 is written to the xlsx file as the formula result, the proper value will be generated by Excel or OpenOffice when you open the xlsx file.

However, if your .xlsx file contains proper values, you can use xlsx.cellAt("A1").value() to extract the cell value contents, and xlsx.cellAt("A1").formula() to extract the formula contents.

[CCi-BClark]

Got it. Funny I was using that method prior.

Thank you

[CCi-BClark]

I remembered why I switched away from this call. If the cell is empty it returns a NULL value but if you run sheet->cellAt(x,y)->value().toString() you get a segFault. So in order to avoid this you have to query the sheet->cellAt(x,y) twice. As a feature request I would say it may be better for this to return a empty string in these cases, might you agree?

QString val;
if (sheet->cellAt(x,y) != NULL){ val.append(sheet->cellAt(x,y)->value().toString();) }
else { val.append(""); }

[dbzhang800]

Hi, you can write a helper function if it is used many times in your code.

QString getCellString(Document *xlsx, int row, int col)
{
    Cell *cell = xlsx.cellAt(row, col);
    if (cell)
        return cell->value().toString();
    else
        return QString();
}

What you expected is that, when you try to access a not-exist cell, QtXlsx create a Blank cell which contains a Null string, then returns the cell for you. But I don't like this behavior.

As you can see, non-exsts-cell != blank-cell, for example, if you have a xlsx file which created by

xlsx.write("B2", QVariant());

Then you can get a null string through

xlsx.cellAt("B2").value().toString();

But you will get a null cell if that cell doesn't exist

xlsx.cellAt("C3"); //zero here.

Maybe we can add an additional param for the read() function, such as

xlsx.read("C3", Cell::RealValue);

or

xlsx.stringValue("C3");

---

Also, please note that, null-string != empty-string. For example, you add an empty but not null string through

val.append("")

but following code create a null string.

val.append(QString())

[CCi-BClark]

I think your idea of adding stringValue() would be a great idea and probably solve my issue with date formats.

Thank you for the null value comment.