importCSV formatting numbers as string in Excel

Aspose.Cells Product Family
1

Hello,


I’m importing a CSV file into an Excel using Aspose.Cells. To do that, I’m using the following code:

public void putCSVDataIntoSheet(String csvFilePath, String XLSXFilePath,

String sheetName, int row, int column, String separator)

throws Exception {

Worksheet sheet;

if (sheets.get(sheetName) == null) {

sheet = sheets.add(sheetName);

} else {

sheet = sheets.get(sheetName);

}

Cells cells = sheet.getCells();

Style style = workbook.createStyle();

StyleFlag sf = new StyleFlag();

sf.setNumberFormat(true);

sheet.getCells().applyStyle(style, sf);

cells.importCSV(csvFilePath, separator, true, row, col);


The CSV file contains strings and numbers, and as such, the numbers should be formatted properly. The problem is that when I see the file in Excel after processing it with the previous code, I get a message that says: "The number in this cell is formatted as text or preceded by an apostrophe", breaking all the formulas in the Excel.


I though the 3rd argument in "importCSV" should've taken care of that.


Any help would be greatly appreciated. Thanks.

2

Another thing to keep in mind is that not all numbers are formatted wrongly, only the ones with decimal separator (".")

3

Hi Pablo,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for Java v8.0.1.3 and see if it fixes your issue.

If your problem persists, then please provide us your source csv file which you are importing and your actual and expected output Excel files. You can create your expected output Excel file manually using MS-Excel.

It will help us look into your issue more precisely and we will update you asap.

4

Hello,


I tried with the mentioned version of Aspose.Cells and the problem persists.

I’m attaching a demo version of the CSV and expected and current excel files.
5

Hi Pablo,

Thanks for your posting and using Aspose.Cells.

We have found that numbers are treated as strings when your data.csv file is converted to xlsx using Aspose.Cells. If we manually open your csv file into MS-Excel after changing the separator to comma, then numbers like 13.0 are treated as numbers like 13 and they display to the right side of the cells.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40812.


6

Thanks a lot for your response.


Is there a time estimate for a resolution to this?
7

Hi Pablo,

Thanks for using Aspose.Cells.

We have looked into this issue further. Please use the following code, it works fine now. It applies the formatting to range “K2:AB3” to make all numbers appear as simple numbers i.e 13.0 will display as 13.

I have attached the output xlsx file for your reference.

Java


String csvFilePath = “F:\Shak-Data-RW\Downloads\data.csv”;


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().get(0);


Cells cells = worksheet.getCells();


cells.importCSV(csvFilePath, “;”, true, 0, 0);


//Apply style to make number 13.0 appear as 13

Style style = workbook.createStyle();

style.setNumber(1);


StyleFlag flag = new StyleFlag();

flag.setNumberFormat(true);


Range range = cells.createRange(“K2:AB3”);

range.applyStyle(style, flag);


workbook.save(csvFilePath + “.out.xlsx”);