Formate error in excel


#1

hi

we are using query to generate excel.

in that query one varchar column storing numbers.

in excel file format for this column set to text.

but it shows some warning messages.in excel.

please find the attached file.

how to remove the warning in excel file.

Thanks & Regards

Ranjith



#2

Hi,


Well, I am afraid, this is the behavior of MS Excel, so if you have (so called) numeric data in strings on the back end, this type of error checking would be applied when the data is pasted into the cells. Therefore, you need to have pure numeric data to be formatted properly.

In general, the error “Numbers stored as text” will be always popped up in the Cells, if the numeric data is inserted as string/text.

One way to negotiate with this kind of issue is to off the related Excel’s error checking option, see the following code:

Worksheet sheet = workbook.getWorksheets().getSheet(0);
ErrorCheckOptions opts = new ErrorCheckOptions();
opts.setErrorCheck(ErrorCheckType.TEXT_NUMBER, false);
//disable the check for numerics saved as text
opts.addRange(new CellArea(0, 0, 65535, 256));
//make the error check option apply on the whole sheet
sheet.addErrorCheckOptions(opts);


Thank you.



#3

hi

Thanks for your reply.

we are using lightcelldataprovider for generating

the above solutions works only for 2003.

how can i ignore the error incase 2007.

find the both 2003 and 2007 attachement.

Thanks & regards
Ranjith


#4

Hi,


Yes, I am afraid, currently we do not support to read/save ErrorCheckOptions for other file formats except XLS files. I have logged your feature request into our database with an id: CELLSJAVA-29538. We will support it later on.

thank you.