Help! .NET Save to CSV doesn't save formula results--outputs "#NAME?" instead

excel.zip (1.9 MB)
Hi, I’m building a spreadsheet in aspose with formulas. It saves it correctly as .xlsx file, but the .csv file doesn’t output the formula results but outputs excel-like error codes. Please see attached.

I’m using the following code:

poXL.Workbook.Save(psSaveAsFileName & “.xlsx”, New XlsSaveOptions(Aspose.Cells.SaveFormat.Xlsx))

poXL.Workbook.Save(psSaveAsFileName & “.csv”, New XlsSaveOptions(Aspose.Cells.SaveFormat.CSV))

??? That’s weird. I don’t know if I’ve ever needed to save to CSV one with formulas, so I don’t know if it’s always done it or???

Is this impossible to fix?

Thanks in advance. P

@PittsburghSteelersFan,
Many cells are same in CSV and XLSX file so could you please name which rows, columns or cells are you referring to. Also share screenshots and input file for our analysis.

FG003-output - troubleshooting screenshot.zip (2.0 MB)

Thanks for your help. See attached spreadsheet AND screenshot of previously sent CSV showing entire columns of error messages

Also, I’m using the latest downloadable version of aspose cells

@PittsburghSteelersFan,
I have tried the scenario by loading the XLSX and then saving it back as CSV but no issue is observed as all the values are properly displayed. You may share a simple console application which can be used to reproduce the scenario here. This application should compile and run without error and should save the XLSX and CSV file. Once the issue is reproduced here, we will provide assistance accordingly.

excel.zip (1.9 MB)

thanks again for your help. I simply copied the single function, XL2CSV, to aspose-help.vb, along with the .xlsx file that is being called, and text that makes the call…for what it’s worth, i found an earlier .csv file that used cells v 8.x and it worked ok, i only downloaded the newest version because there was a bug that had been fixed (identified in your support forum). Maybe something in my code has been deprecated in the new version, but I couldn’t find it…

Also, if I save the .xlsx file as a .csv in excel, the resulting .csv file works like a champ

thanks again

@PittsburghSteelersFan,
I have converted FG003-output.xlsx file to CSV with MS Excel. The attached output file is same as the one you have shared in FG003-output - troubleshooting screenshot.xlsx file.
ExcelCSV.zip (2.0 KB)

Could you please compare the output and let us know if you notice difference in this file and the one created by Aspose.Cells API.

FG003-saved_using_aspose_see_trial_notice_row53.zip (134.9 KB)

Thanks for your ongoing help. This has to be frustrating for you.

your file is different from what i’m saving via excel (attached is" saved w/excel, and save w/aspose - you can tell the difference by row 52, the aspose trial version notice).

something doesn’t make sense. As long as the formula is correct, excel will save the numbers (versus “#name?”) or that would be a massive bug in excel that would be easy to google info on. I looked and there is no google chatter.

And, if you look at my .xlsx file that was created by aspose (perfectly displayed in excel with included screenshot in the upload), is that .csv what aspose intends its “save” output to be? I never saw any example on aspose site where that was the desired outcome. Why does the aspose save as csv not save the cells based on their formula results?

is it impossible for aspose cells to output common excel formula results into a .csv file?

thanks a ton!

@PittsburghSteelersFan,
I have tried to save the source Excel file as CSV using MS Excel 365 and observed that it saves CSV properly i.e. values are shown instead of #Name?. It seems that Excel 2016 and Excel 365 are behaving differently.

Now I executed your code which you have sent above and observed that it creates proper CSV and no issue is observed with the latest version Aspose.Cells for .NET 20.6.

Here is the output created by the latest version.
output (2).zip (6.6 KB)
FG003-outputByExcel365.zip (6.7 KB)

I see no difference in the output of Aspose.Cells and MS Excel.

Regarding the evaluation issue, it will be removed when code is executed with license. You may try the scenario with the latest version of Aspose.Cells and share the feedback.

output_using_aspose_cells_20.6.zip (24.3 KB)

i’ve screen captured showing i have been using 20.6…
reran and attached .csv with same “#NAME” (or #VALUE) error output for formula-calculated fields.

I’m using Win10, Visual Studio 2019. I knew the “evaluation issue” would go away, but I mentioned it because that’s how I could confirm it was saved by aspose versus saved by excel365. would it be helpful for you if I screen share so you can confirm or test?

What next? are you out of options?

thanks again! P

using_vbs_even_works.zip (7.1 KB)
FYI, I even used this vbscript and it output the csv correctly (without the “#NAME” errors saved). I mention this because using aspose is the only way I have found that it saves to csv incorrectly on my machine. I don’t have an older copy of excel to test, but I wouldn’t think that would matter since it’s not on my machine and the file is only being handled by aspose.

@PittsburghSteelersFan,
I guess when I open your Excel file here to remove the evaluation sheet and then save it again, it makes changes to the file which results in different outputs. (This step is required because Aspose.Cells and MS Excel converts active sheet to CSV.) Please remove the evaluation sheet from your source template file here in excel.zip and share the file again. I want to convert your file as it is without opening and saving it here to CSV for further investigation.

@PittsburghSteelersFan,
We have investigated it bit more, in your template file FG003-output.xlsx, those values saved in the file for those cells are also "#NAME? ". So the file should have been saved by lower versions of ms excel, or other tools/third party APIs. Commonly for such kind of template file, with Aspose.Cells you should call workbook.CalculateFormula() before saving/rendering.

Unfortunately, currently we do not support to calculate the specific function “FORECAST.ETS” used in this file. So even with workbook.CalculateFormula(), the calculated result for it will be "#NAME? ". We will investigate whether we can support to calculate “FORECAST.ETS” function in later versions, however, because its complexity, maybe we need quite long time and cannot support it in near future.

As workaround, you may re-save the template file with versions that support this function, such as excel 365, and then use the re-saved file to generate csv file with Aspose.Cells.

You’ve been very patient and helpful, even if there’s no automated “fix” currently available.

Given the troubleshooting we’d done, I was afraid that was going to be your answer unless you could have a quick fix.

Thanks. Stay safe through this covid mess.

@PittsburghSteelersFan,
Thank you for your understanding and feel free to write us back if you have any other query related to Aspose.Cells.