Reg: Commas in format in XLS

[eraianbanc]

Hi,

I have using the following data to generate the report.

In the Query i gave as 'Test_Commas_rtr 1',

And im changing the Pattern for this field(num ) as ¤ #,##0.00,

 

Rgds,

Eraianban C

After viewing the report in Excel, i cannot able to do sum of the values which are having commas in Excel. When I double click the comma separated values, then only i can able to do the sum in Excel. But for the numbers with normal pattern, i can able to do the sum.

I use the below code for xls generation (JExcelApiExporter)

Please help me out, how do get the sum formula for comma separated numbers in excel. Do i need to set any parameters other that what i have used to overcome this issue.

Code:

create table Test_Commas(num NUMERIC(10,3))INSERT INTO Test_Commas values (1)INSERT INTO Test_Commas values (23456)INSERT INTO Test_Commas values (7895)create proc Test_Commas_rtr(@a_i_lang_id int)asbeginselect num from Test_Commasend---- Java codeJExcelApiExporter jExcelApiExporter = new JExcelApiExporter();            jExcelApiExporter.setParameter(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);            jExcelApiExporter.setParameter(JExcelApiExporterParameter.IS_AUTO_DETECT_CELL_TYPE,Boolean.TRUE);                         //jExcelApiExporter.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);            jExcelApiExporter.setParameter(JRExporterParameter.JASPER_PRINT, print);            jExcelApiExporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "D:\NetBeanWS\JRXLS\bin\2986_1_0_1.xls");

[eraianbanc]

This was the requirement from the client side that they need the comma separated formats, which can evaluate the sum also in MS Excel. Can anyone please help me out on this issue.

[eraianbanc]

Hi,

Anyone please suggest me how do i do the sum for a comma separated values for which i have specified a pattern for that field when genearting the report to an Excel file. I can able to do it for that numbers which dont have commas in them. i.e . The fields which have the default format works fine for sum formlas in Excel.

Can i tell the end customer not to use any commas pattern for those fields, so that he can be able to do the sum for the reports generated in excel format.

Regards,

Erai.

[jrodri]

I have the same  issue

 

Please let me know if somebody have a fix for it case

 

 

Thanks

[eraianbanc]

Hi,

Set the propertfyfor JExcelApiExporter with true for IS_AUTO_DETECT_CELL_TYPE as shown in the code. Try it it will work.

Code:

JExcelApiExporter jExcelApiExporter = new JExcelApiExporter();jExcelApiExporter.setParameter(JExcelApiExporterParameter.IS_AUTO_DETECT_CELL_TYPE,Boolean.TRUE);