Format excel using Apache POI

Request for help on DynamicReports

Moderator: ricardo

Format excel using Apache POI

Postby shikha.singhal » Tue Apr 07, 2015 6:20 am

Hi Ricardo,

I want to format generated excel file using apache poi (vesrion 3.7). I am facing basically two problems in formatting excel.
1. Numbers are considered as text and I am getting warning messages in excel (Number stored as text).
2. Left indentation is not coming in excel, although it is coming in PDF and HTML.

How can I achieve these two things using apache poi in excel report?

Thanks & Regards
Shikha Singhal
shikha.singhal
 
Posts: 78
Joined: Tue Aug 19, 2014 1:16 pm

Re: Format excel using Apache POI

Postby ricardo » Tue Apr 07, 2015 2:30 pm

Solution for the first issue is to set .setDetectCellType(true)
Code: Select all
JasperXlsExporterBuilder xlsExporter = export.xlsExporter(...)
  .setDetectCellType(true)

and the second problem, I'll check that...
ricardo
Site Admin
 
Posts: 1363
Joined: Sat Sep 18, 2010 4:47 pm
Name: Ricardo Mariaca

Re: Format excel using Apache POI

Postby shikha.singhal » Wed Apr 08, 2015 9:32 am

Hi Ricardo,

I tried .setDetectCellType(true) as you said. But still my problem is not resolved. I am attaching sample of problem.
textProblemExcel.png
number is considered as text
textProblemExcel.png (996 Bytes) Viewed 4649 times

I am getting warnings in excel "The number in this cell is formatted as text or preceded by an apostrophe". Here text is also not fitting properly and not visible too.

My code is as given below.
JasperXlsExporterBuilder xlsExporter = export.xlsExporter(fileOutStream);
xlsExporter.setRemoveEmptySpaceBetweenColumns(true).setOnePagePerSheet(true).setDetectCellType(true)
.sheetNames(ReportConstants.FILTERSHEET_NAME, reportConfigurationList.get(0).getTitle());


Do I need to put any other property too?? Or am I missing this property at any other place?? And what should I do to fit the text properly and to increase the cell's width and height dynamically ?

Thanks in advance!

Regards
Shikha Singhal
shikha.singhal
 
Posts: 78
Joined: Tue Aug 19, 2014 1:16 pm

Re: Format excel using Apache POI

Postby ricardo » Wed Apr 08, 2015 2:16 pm

Hi,
your code looks correct. I tested it on example http://www.dynamicreports.org/examples/excelreport1 and there weren't any warnings in number cells when I open it in excel. I'm not able to reproduce that problem, so could you modify code of the example so that it will generate an excel document with warnings?
To fit the text properly in excel you can set the property "net.sf.jasperreports.export.xls.auto.fit.column" to "true", then the width of a cell should fit the text width. An example
Code: Select all
col.column("Item",  "item", type.stringType()).addProperty("net.sf.jasperreports.export.xls.auto.fit.column", "true")
ricardo
Site Admin
 
Posts: 1363
Joined: Sat Sep 18, 2010 4:47 pm
Name: Ricardo Mariaca

Re: Format excel using Apache POI

Postby shikha.singhal » Thu Apr 09, 2015 12:01 pm

Hi Ricardo,

In the mentioned example you are setting column type for each column. But I am filling data in the columns by using crosstab. and my evaluator is returning values as string. That is why I am getting that warning. But if I am processing the sheet by using POI or any other way and making the cell type numeric. In this case if I have value like 1432.00, this is being converted into 1432. But I want to show decimal point as it is. How can I achieve this??

And how to add property for columns? I am using crosstab. I don't have control for each column. How to proceed to add the property??
My code is as given below :
JasperReportBuilder builder = report();
CrosstabBuilder crosstab = tabBuilder.rowGroups(rowGroups).columnGroups(columnGroups).measures(measures);
builder.setTemplate(template.getReportTemplate(style)).setDataSource(dataSource).summary(crosstab);


Thanks in advance!
Regards
Shikha Singhal
shikha.singhal
 
Posts: 78
Joined: Tue Aug 19, 2014 1:16 pm

Re: Format excel using Apache POI

Postby ricardo » Fri Apr 10, 2015 1:34 pm

Hi,

in crosstabs, there is method .addHeaderProperty in column group that allows to add properties to column header cells. If the property 'net.sf.jasperreports.export.xls.auto.fit.column' doesn't work for crosstabs, try property 'net.sf.jasperreports.export.xls.shrink.to.fit'. This property decreases font size.
ricardo
Site Admin
 
Posts: 1363
Joined: Sat Sep 18, 2010 4:47 pm
Name: Ricardo Mariaca

Re: Format excel using Apache POI

Postby shikha.singhal » Mon Apr 20, 2015 12:19 pm

Hi Ricardo,

Thanks for your reply.

I didn't try this property due to some constraints. But I solved this problem by using POI.

Code is as given below:
cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##0.00_ "));
clonedStyle.cloneStyleFrom(cellStyle);
cell.setCellStyle(clonedStyle);


Thanks & Regards
Shikha Singhal
shikha.singhal
 
Posts: 78
Joined: Tue Aug 19, 2014 1:16 pm

Re: Format excel using Apache POI

Postby RuchiKP » Tue Nov 14, 2017 3:52 am

Hi Shikha,

Can you please let me know that how you exactly resolve your problem because I m facing the same.

Thanks in Advance.

Regards,
Ruchi Pandya
RuchiKP
 
Posts: 4
Joined: Fri Oct 13, 2017 12:12 pm

Re: Format excel using Apache POI

Postby RuchiKP » Tue Nov 14, 2017 4:15 am

Hello,

I found the solution for the same. This is not the because of Dynamic report. This is the due to Excel feature.
Solution is :
In the Excel, go to File -> options -> formulas -> uncheck the number formatted as Text

Thanks & Regards,
Ruchi Pandya
RuchiKP
 
Posts: 4
Joined: Fri Oct 13, 2017 12:12 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 1 guest

cron