5

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?

Rob Kielty
  • 7,958
  • 8
  • 39
  • 51
ajm
  • 12,863
  • 58
  • 163
  • 234
  • Can I just check with you, Ashish, that doing CTRL+A and then double clicking the vertical line between any two column headers to resize the column according to the text data present in the worksheet is an unacceptable workaround for your users? – Rob Kielty Jun 01 '12 at 15:56
  • @Rob No, In fact this is what we are doing currently. But the users dont want to do this anymore. :( – ajm Jun 02 '12 at 03:13
  • Ashish, that's useful to know as that workaround describes perfectly what needs to be done during/prior to export. I had a look at Display Tag, compiled and deployed the samples to Tomcat 6.0.14 On the Export page of the sample not reproduce the problem. The cells in Excel were sized correctly. Can you provide some sample data that would replicate this problem? – Rob Kielty Jun 02 '12 at 03:34

3 Answers3

3

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

Vlad
  • 1,077
  • 8
  • 8
2

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"?

Jonathan W
  • 3,759
  • 19
  • 20
1

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

kostas
  • 1,959
  • 1
  • 24
  • 43