Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to prevent wrapping of data exported to excel using display tag?

We are using the Display tag library in our Java based web application.

Apart from just using display tag library to show lists, we are also using it to export data to excel sheets which is working fine.

The problem is that the data is wrapped up in the Excel sheet columns.

I need to click on the cell to expand the cell to see the complete data.

Is there any way to prevent this wrapping up of data? Can Excel sheet cells adjust themselves to the width of the data in it?

like image 805
ashishjmeshram Avatar asked May 21 '12 08:05

ashishjmeshram


3 Answers

Yes cells can be autoformated, if you are using POI with it HSSF to create you document. If not I'm suggesting you to use it just open an existing document and auto resize columns you need.

After you created all your cells but before saving document you just call this method that re-size column. for example i did it for all of my columns on a sheet like that:

//
// Create an instance of workbook and sheet
//
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
sheet.setAutobreaks(true);
 .
 .//code in which you create actual rows and cells HERE  
 .
//auto formating to fit the page
for(int i=0; i<repModel.getReportHeaders().length;i++){
sheet.autoSizeColumn((short) (i+1));     
}

And here is a best link to use to start up with poi: Busy Developers' Guide to HSSF and XSSF Features

like image 157
Vlad Avatar answered Oct 24 '22 04:10

Vlad


If you are using HSSF Poi to produce the excel document, then you should be able to adjust the column width:

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth%28int,%20int%29

like image 38
kostas Avatar answered Oct 24 '22 03:10

kostas


It looks like this blog entry discusses DisplayTag's excel export mechanism and shows you how to modify the Excel export to tie into a custom HSSF table writer.

To summarize:

  • Write an implementation org.displaytag.export.BinaryExportView (or extend DisplayTag's DefaultHssfExportView)
  • Said implementation needs to delegate to a new class that extends DisplayTag's HssfTableWriter (or copy / paste the original from DisplayTag into new source, since it sounds like a bunch of accessor methods on there are unnecessarily private)
  • override (or change) the particular method in HssfTableWriter (I think it's called writeBottomBanner())
  • update your displaytag.properties file with this line: export.excel.class=x.y.z.MyExcelExportView

That said, I'm looking at the source code in DisplayTag 1.2 and it appears to already be expanding the column for auto-sizing (http://displaytag.sourceforge.net/1.2/displaytag-export-poi/xref/org/displaytag/render/HssfTableWriter.html#347). Are you using DisplayTag 1.2, or are you using an earlier version? If you're using an earlier version, perhaps upgrading to 1.2 will solve your problem. Or am I misunderstanding what you meant by the data being "wrapped up"?

like image 40
Jonathan W Avatar answered Oct 24 '22 04:10

Jonathan W