Gembox removes precision when saving as CSV

Question

I'm using Gembox spreadsheet to export some data to .CSV but aren't getting any decimals in the output file.

When exporting as XLSX everything looks as expected.

I've tried both Gembox 3.7 and 3.9 but the result is the same.

Use the following code to reproduce the issue.

        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var ef = new ExcelFile();

        var ws = ef.Worksheets.Add("NumberFormatTest");

        ws.Cells[0, 0].Value = "Expected";
        ws.Cells[1, 0].Value = "0";
        ws.Cells[2, 0].Value = "0.0";
        ws.Cells[3, 0].Value = "0.00";

        ws.Cells[0, 1].Value = "Actual";
        ws.Cells[1, 1].Value = 0m;
        ws.Cells[1, 1].Style.NumberFormat = "0";
        ws.Cells[2, 1].Value = 0m;
        ws.Cells[2, 1].Style.NumberFormat = "0.0";
        ws.Cells[3, 1].Value = 0m;
        ws.Cells[3, 1].Style.NumberFormat = "0.00";

        ef.Save("Numberformat test.csv");
        ef.Save("Numberformat test.xlsx");

How can I get the correct result without resorting to .ToString("0.00")?

score 1 · Accepted Answer · answered Aug 18 '15 at 06:08

If interesting for someone else. I was in contact with their support and they told me that the number format isn't currently exported into CSV file format and that the following workaround should be used.

foreach (var row in ws.Rows)
    foreach (var cell in row.AllocatedCells)
        if (cell.Value != null)
            cell.Value = cell.GetFormattedValue();


ef.Save("Numberformat test.csv");

score 0 · Answer 2 · answered Mar 20 '18 at 22:02

0

For Aussie VB users

    Dim enAU As CultureInfo = CultureInfo.CreateSpecificCulture("en-AU")
    Dim SaveOptions As New CsvSaveOptions(CsvType.CommaDelimited)
    With SaveOptions
        .FormatProvider = enAU
    End With

    For Each row In ws.Rows
        For Each cell In row.AllocatedCells
            If Not (String.IsNullOrEmpty(cell.Value)) Then
                cell.Value = cell.GetFormattedValue()
            End If
        Next
    Next

    ws.Save(sSaveAsFullFilename, New CsvSaveOptions(CsvType.CommaDelimited) With {.FormatProvider = enAU})

answered Mar 20 '18 at 22:02

Peter Wright

1

1

While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-‌code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – peacetype Mar 20 '18 at 22:22

score 0 · Answer 3 · answered Jul 12 '21 at 08:48

With the newer versions (GemBox.Spreadsheet 4.5 and above) this can be simplified by using the CsvSaveOptions.UseFormattedValues property.

For example, like this:

var options = new CsvSaveOptions(CsvType.CommaDelimited);
options.UseFormattedValues = true;
ef.Save("Numberformat test.csv", options);

Gembox removes precision when saving as CSV

3 Answers3