In Excel, on row 1, from columns A to K I have text representing column header labels.
On row 2, from columns A to K I have the following:
- [a.auditHour;block=tbs:row]
- [a.instance]
- [a.dataFileUsedMB]
- [a.dataFileSizeMB]
- [a.logFileUsedMB]
- [a.logFileSizeMB]
- [a.dataFileUtilisationPercent]
- [a.logFileUtilisationPercent]
- [a.dataFileMinSizeMB]
- [a.dataFileMaxSizeMB]
- [a.logFileMinSizeMB]
- [a.logFileMaxSizeMB]
I'm wanting the first column to be a timestamp/datetime, the second to be text/string and the remainder to be decimal numbers.
I have explicitly set those data types for the numeric columns in my Excel template (by right-clicking the column headers > Format Cells... > Number tab > Number > 5 decimal places and use the 1000 separator).
In PHP I have the following:
$TBS->LoadTemplate($template, OPENTBS_ALREADY_UTF8);
$TBS->PlugIn(OPENTBS_SELECT_SHEET, $sheetName);
$TBS->MergeBlock('a', $rows);
try {
// Save the result file
$TBS->Show(OPENTBS_FILE, $savePath);
}
The code executes successfully and the resultant workbook contains data.
However, when the resultant file is opened, all those decimal columns show little green triangles which, when I hover over them, brings up the yellow triangle with an exclamation mark, and the error message -
"Number Stored as Text"
I tried working around this issue by building another worksheet that attempts to convert those text values into numbers using Excel's VALUE function; viz, cell C2 on my "calculations" sheet contains:
=VALUE(Data!C2)
The result there is the error:
#VALUE!
If I use the button "Calculate Now" in the "Calculation" group of buttons in the Formulas menu, the formulas are not recalculated .. but if I click into that cell and press Enter, the cell calculates and shows the value from the Data! tab.
I'd much rather not have to produce a calculation worksheet to resolve this.
It looks like TinyButStrong is wiping out the data type settings I had defined for the cells into which it is pasting.
What can I do to ensure the pasted data retains the data types defined within the Excel template?
Excel version:
Microsoft® Excel® for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
