Worksheet Copy

Aspose.Cells Product Family
1

Hi, i am copying one sheet from a CSV file to XLS file using the Worksheets copy method. The sheet gets copied but all the cells which are formatted as "Number" loose the formatting and appear as "General". Hoping for some solution to this!

Thanks,

Rohan

2

Hi,


Aspose.Cells for Java should work the same way as Ms Excel does, you may trying opening your CSV file into Ms Excel and then re-save it as XLS format manually to confirm it.

Sample code:

TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.CSV);
loadOptions.setSeparator( ‘,’);
loadOptions.setConvertNumericData(true);

Workbook workbook = new Workbook(“e:\test2\BkCSV.csv”, loadOptions);

workbook.Save(“e:\test2\output.xls”, SaveFormat.
EXCEL_97_TO_2003 )


3

Hi. With this the numeric values are displayed with a green arrow indicating "The number in this cell is formatted as text" error. I found an option in Aspose to work with "ErrorCheckOptions" of excel by which we can disable the error check. But I want some option which will correct this error. i.e. "Convert to Number" option which is displayed in the Excel file whenever such green arrow is shown.

OR Else coming back to the original issue, if there is some way by which this Numeric formats are maintained while copying the sheet itself then that would be great! Note: The Numeric formats gets copied when i copy the sheet from XLS to XLS. But when i copy the sheets from CSV to XLS. it looses the number formatting. Thanks!

Regards,

Rohan

4

Hi,


Well, as I said earlier Aspose.Cells for Java works the same way as MS Excel does as we follow MS Excel standards. So, in MS Excel if numbers are stored as text values for CSV, Aspose.Cells would do so. Actually CSV is text/string type of format in which values are stored as strings separated by comma.

Moreover, please check the article on how you can disable the error checking options:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/use-error-checking-options.html





5

Hi. I had gone through the article. But here provision is given to DISABLE the error checking options. There is one feature in MS Excel where if we scroll on the green arrow of such cells where the number is stored as text there is one option called "Convert to Number". So please let me know how do i implement this in aspose. Thanks for the support.

Regards,

Rohan.

6

Hi,


Could you try the following line of code before converting from CSV to save XLS file format:

workbook.getSettings().setConvertNumericData(true);

Thank you.


7

Hi. I am using Aspose.cells 7.0.4. In this the mentioned method workbook.getSettings().setConvertNumericData() is depricated.

Regards,

Rohan

8

Hi,


Could you attach your sample input CSV file and output (SavedAs) XLS file here, we will check it soon.

Thank you.
9

Hi, I am attaching the CSV file from which i am copying the sheet into a new xls file. In the attached csv file first column has numeric values. If you right click and go into Format Cells it will show category as “Number”. After copying the sheet using copy method, the category changes to “General”. Even if i use LoadOptions.setConvertNumericData(false); the category still changes to General. I want to retain the format.

10

Hi Amjad,

Any luck on this?

Regards,

Rohan

11

Hi,


Well, yes, I can see this behavior, I think when you concert CSV file to XLS, the number formatting is gone, it looks like MS Excel’s behavior. I think, I am afraid, you need to apply the style manually (by using Aspose.Cells APIs) to the cells. See the updated code segment for your needs that works fine.

Sample code:

TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.CSV);

loadOptions.setConvertNumericData(true);

Workbook workbook = new Workbook(“e:\test2\FileA.csv”, loadOptions);

Style stl = workbook.getStyles().get(workbook.getStyles().add());
stl.setCustom("#,##0.00");
StyleFlag flg = new StyleFlag();
flg.setNumberFormat(true);
workbook.getWorksheets().get(0).getCells().applyColumnStyle(0, stl,flg);

workbook.save(“e:\test2\outputFileA.xls”, SaveFormat. EXCEL_97_TO_2003 );

12

Hi, Thanks for the help. Any chance of getting this feature added in the upcoming releases? The above code actually wont work for me. Because in the above code column numbers needs to be hard coded. But i don't know which cell is going to be numeric. So is dere a way where i can detect the error "Number is formatted as text" in the sheet and then use convert to number feature of Ms Excel?

Thanks,

Rohan

13

Hi,

As a workaround, you can decide if the given cell text is number or text by using the following code.

If it is a number, then you can format it as “#,##0.00"

C#


string cellText = “52,200.00”;


//if the below line throws exception, then it is not a number, otherwise it is a number

int num = Int32.Parse(cellText.Replace(”,", “”).Replace(".",""));

Also, I have tested your file with the following code which converts the csv file into xlsx file and found there is no problem, number formatting is working properly.

I have attached the output xlsx file and the screenshot.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\FileA.csv”;

Workbook workbook = new Workbook(filePath);

workbook.Save(filePath + “.out.xlsx”);

Screenshot

14

Hi,

Please try the new fix: Aspose.Cells for Java v7.1.0.3

Now we have supported to maintain the number formats for such kind of values.

15

Hi,

Please use this link to download: Aspose.Cells for Java v7.1.0.3