Set numeric cell value to be formatted as #,##0.00

Question

In SpreadsheetLight, how can I set the numeric value into a #,##0.00 format? Tried using Format(value, "#,##0.00") but the output Excel file shows warnings that 'Number in this cell is formatted as text or preceded by an apostrophe'. Tried the following codes:

These two produce the above warning:

sld.SetCellValue(rowIndex, columnIndex, Format(row(column.ColumnName), "###0.00"))
sld.SetCellValue(rowIndex, columnIndex, Format(row(column.ColumnName), "#,##0.00"))

This doesn't have a warning but the output is not formatted:

sld.SetCellValue(rowIndex, columnIndex, row(column.ColumnName))

I want the output to be formatted but not stored as text in Excel.

TIA

I'd think you'd apply the formatting in Excel. [Strings.Format](https://learn.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.strings.format) returns a String which is what Excel is complaining about. — Étienne Laneville, Jun 02 '23 at 00:30

score 0 · Answer 1 · answered Jun 02 '23 at 02:15

Found the answer:

You have to declare a style then apply that style to your desired range

Dim style As SLStyle = sld.CreateStyle
style.FormatCode = "#,##0.00;(#,##0.00)"
sld.SetCellStyle(1,
                 1,
                 totalRows,
                 totalColumns,
                 style
                 )

Set numeric cell value to be formatted as #,##0.00

1 Answers1