Render XLSX to CSV without custom formatting in Java

[sandyarora]

Hi ,

I am reading XSLX file and converting into CSV file. Some of my columns contains custom formating like $###0.00, I want CSV to have the value without this custom formatting. How can I do that. Below is the code:

Workbook workbook = new Workbook(“C:\Response.xlsx”);

workbook.save(“C:\Response.CSV”,SaveFormat.CSV);

[babar.raza]

Hi Sandeep,

Thank you for contacting Aspose support.

This is the default behaviour of Excel application (as well as Aspose.Cells APIs) to export the spreadsheet with its displayed format to CSV, that is; while exporting the spreadsheet to CSV, the resultant file will contain the values as they are displayed in Excel application. That said, you can workaround this situation by removing any formatting applied. Please check the following piece of code and attached samples for your reference.

Java

Workbook book = new Workbook(dir + “book1.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
Range range = cells.createRange(0, 0, cells.getMaxDataRow() + 1, cells.getMaxColumn() + 1);
Style style = book.createStyle();
style.setCustom("@");
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
range.applyStyle(style, flag);
book.save(dir + “output.csv”, new TxtSaveOptions());

[sandyarora]

Thanks for the quick turnaround. But this is more of workaround. I think there should be a property to set whether I want to read the cells with formatting or not .

Thanks

[babar.raza]

Hi Sandeep,

Yes, you have to workaround the situation because by default, the formatted strings are exported out as per Excel’s standards & specification. Even while using Excel, you have to manually remove the styling (or change the format to text) in order to export the actual string values to CSV.

That said, we are in discussion with the concerned member of the product team if we can expose some property that could handle such scenario in a more sophisticated manner. As soon as we conclude the discussion, we will let you know the verdict.

[babar.raza]

Hi again,

This is to update you that upon discussion with the concerned member of the product team, we have logged a feature request with Id CELLSJAVA-41694 in our database to expose some property that could allow to save the un-formatted contents of the spreadsheet in CSV format. Please spare us some time for proper feasibility analysis in this regard. As soon as we receive any updates, we will post here for your kind reference.