Extract Excel integer as String to avoid .0

Aspose.Cells Product Family
1

Hello,

I’m having a little issue when I’m extracting my datas. When a user enter some integer value in Excel for instance, 55, Excel interpret it as a number and when I extract it, I get the String 55.0 which is a problem when the number is a zip code for example.

When I extract the data, I don’t know anything about the Excel content, so I can’t just “cast” targeted numbers as String.
I use the method:
cells.exportArray(1, 0, maxDataRow, maxDataColumn);

I tried to play with the LoadOptions.setConvertNumericData without any success. My guess is that 55.0 is the “real” stored data, and that I have to somehow get rid of the .0 by myself, but if you have any suggestion…

Thank you!
Guillaume

EDIT: I tried to set the cell format as zip code in excel. Excel is displaying 00055 but value extracted is still 55.0 …

2

Hi,


I could not find the issue. I used the following sample code with a simple template file (attached), I first exported data to fill multi-dimensional array from the first worksheet data and then import the multi-dimensional array to paste the values into the second worksheet, the data in the first and second worksheet is same in the output file (attached). I am using latest version v8.2.1.x of the product.
e.g
Sample code:

FileInputStream fstream = new FileInputStream(“Bk_exparray1.xlsx”);

//Instantiating a Workbook object
Workbook workbook = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

//Exporting the contents of 7 rows and 2 columns starting from 1st cell to Array.
Object dataTable [][] = worksheet.getCells().exportArray(0,0,4,3);

//Closing the file stream to free all resources
fstream.close();

Cells cells = workbook.getWorksheets().get(“Sheet2”).getCells();
cells.importTwoDimensionArray(dataTable, 0, 0, true);

workbook.save(“outexparray1.xlsx”);


If you still find the issue, kindly provide us simple JAVA program (runnable) with your template file (if any) to reproduce the issue on our end, we will check it soon.

Thank you.

3

You actually reproduced it :slight_smile:

If you check the content of dataTable after the exportArray with :

System.out.println(Arrays.deepToString(dataTable));

You’ll find:

[[1.0, 55.0, 7.0], [2.0, 56.0, 6.0], [3.0, 57.0, 5.0], [4.0, 58.0, 4.0]]

EDIT: If you enter 55.0 in a cell of an excel file, excel will display 55.
4

Hi,


Sorry but I still could not find the issue, I added your line of code to my code:
e.g
Sample code:

FileInputStream fstream = new FileInputStream(“Bk_exparray1.xlsx”);

//Instantiating a Workbook object
Workbook workbook = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

//Exporting the contents of 7 rows and 2 columns starting from 1st cell to Array.
Object dataTable [][] = worksheet.getCells().exportArray(0,0,4,3);

//Closing the file stream to free all resources
fstream.close();

System.out.println(Arrays.deepToString(dataTable));

Cells cells = workbook.getWorksheets().get(“Sheet2”).getCells();
cells.importTwoDimensionArray(dataTable, 0, 0, true);

workbook.save(“outexparray1.xlsx”);

Output:

[[1, 55, 7], [2, 56, 6], [3, 57, 5], [4, 58, 4]]

(Note: I am using v8.2.1.x (latest version/fix)).

Thank you.
5

It seems thaty it comes from the excel version I’m using.

Please try the same code with this file.
6

Hi,


Thanks for providing us template XLS file.

After an initial test, I observed the issue with XLS file format, XLSX file format works just
fine though. I used the template file provided by you with my sample code
below. Each numeric values (which is retrieved) is always set with 1 decimal
places:

e.g


Sample code:


FileInputStream fstream = new FileInputStream("updateTest.xls");

//Instantiating a Workbook object
Workbook workbook = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);

//Exporting the contents of 7 rows and 1 column starting from 1st cell to Array.
Object dataTable [][] = worksheet.getCells().exportArray(0,0,6,1);

//Closing the file stream to free all resources
fstream.close();

System.out.println(Arrays.deepToString(dataTable));

Output:



[[Age], [56.0], [56.0], [47.0], [47.0], [56.0]]


I have logged a ticket with an id “CELLSJAVA-41040” for your issue. We need to evaluate and investigate your issue though, it might be the behavior of XLS file format. Anyways, we will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
7

Thanks a lot!

8

Hi Guillaume,

Thanks for using Aspose.Cells.

We are pleased
to inform you that we have fixed this issue and we will provide you a
fix in couple of days after some extensive testing. Please spare us some
time. Once, it is available for you, we will let you know asap by
posting the fix in this thread.

9

So fast! :slight_smile:

Thanks a lot for the awesome work guys.
10

Hi Guillaume,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.2.1.4 and let us know your feedback.

11

It’s working great thanks!

12

Hi Guillaume,

Thanks for your feedback and using Aspose.Cells.

It is good to know that your issue is resolved with the latest fix. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

13

The issues you have found earlier (filed as CELLSJAVA-41040) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.