When adding formulas to an xls file and saving to csv, formula cells contain "0"

[Ou-sama Thompson]

I have a simple .NET application that takes an existing .xls file, inserts some columns and formulas to concatenate some fields together for a data merge, but I need an csv file to do the data merge.

When I save to an xlsx the expected info is there, but when I save to csv, the calculated fields are all "0"

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    application.EnableIncrementalFormula = true;

    IWorkbook wb = application.Workbooks.Open(new FileStream(xlsFile, FileMode.Open, FileAccess.ReadWrite));
    IWorksheet ws = wb.Worksheets[0];
    ws.EnableSheetCalculations();

    ws.InsertColumn(4, 1);
    if (weeks == "4" || weeks == "5") { ws.InsertColumn(10, 2); }
    else { ws.InsertColumn(10, 1); }

    string rowCount = ws.UsedRange.Rows.Length.ToString();
    int columnCount = ws.Range.Columns.Length;

    for (int i = 1; i <= columnCount; i++) { ws[1, i].Text = columnTitles[i - 1]; }

    ws.Range[$"D2:D{rowCount}"].Formula = "=TRIM(B2)&\" \"&TRIM(C2)";
    ws.Range[$"J2:J{rowCount}"].Formula = "=TRIM(G2)&\", \"&TRIM(H2)&\" \"&TEXT(TRIM(I2),\"00000\")";

    if (weeks == "4" || weeks == "5")
    {
        ws.Range[$"K2:K{rowCount}"].Formula = $"=TEXT(L2*{weeks},\"#,##0\")";
    }

    ws.DisableSheetCalculations();
    wb.SaveAs(new FileStream(outFile, FileMode.CreateNew, FileAccess.Write), ",");
    wb.SaveAs(new FileStream(xlsFile + "x", FileMode.CreateNew, FileAccess.Write));
    wb.Close();
}

I assume there's something simple I'm missing, but any help is appreciated.

[Atchaya Sekar]

Hi Ou-sama Thompson,

We have reproduced the reported issue on our end. Please refer to the attached sample below and confirm if this is the issue reported.

Regards,

Atchaya S.

Attachment: BaseSample_(2)_aaef8ff1.zip

[Ou-sama Thompson]

1. Your output file was xls, so I changed it to csv in the code.
2. The Book2.xls columns weren't ordered as expected to get the expected First Name + Last Name concatenation as well as missing a city and state to make a nice City, ST ZIP concatenation that I was doing, so I tweaked the Book2.xls file.

With these two minor fixes, it does do the same thing for me.

I am sending back the updated zip file.

Attachment: BaseImageConversion_db337aac.zip

[Atchaya Sekar]

Hi Ou-sama Thompson,

 

Using DisableSheetCalculations removes the calculation process, causing formulas not to return the correct values in the output CSV file. We kindly suggest avoiding the use of DisableSheetCalculations in your code.

 

Regards,

Atchaya S.

[Ou-sama Thompson]

I thought that was so you could access values programmatically, but it makes sense when you want to save to a purely text file.

This works now for me, thanks!

[Atchaya Sekar]

Hi Ou-sama Thompson,

 

We're glad to know it works for you.

 

Regards,

Atchaya S.