Ask Ben: Getting Line Breaks To Work In A ColdFusion And POI Generated Excel Document
Posted February 1, 2007 at 4:45 PM by Ben Nadel
I am trying to use your export to excel code, and am having a small issue that I can't figure out. One of my columns needs to contain data having multiple lines in it, but I can't seem to get the output to break a newline where I need it to. I have tried "\n", <br>, #chr(10)#, and none give the desired output in the cell. Is it possible to get this type of output?
I did not know how to do this off hand and had to do a little bit of research, so sorry if this is not the most complete / best answer. In short, line breaks only work when "Text Wrapping" is turned on for a given cell. Without the text wrapping, the line break shows up (for me) as a weird ASCII box character.
So, how do you turn on the text wrapping? Through POI's mad-easy API (how awesome is POI?!? I mean really!). Assuming that the variable "LOCAL.Style" is a POI CellStyle object, the following line sends in either a boolean true or false to the API method SetWrapText():
- // Set the cell to wrap text. This will allow new
- // lines to show up properly in the text.
- LOCAL.Style.SetWrapText(
- JavaCast( "boolean", true )
- );
Now, this does come with some caveats. The biggest one that you will notice immediately is that when you turn this option on, Excel freaks and doesn't seem to be able auto-width any columns. Each column will be quite narrow and single words will break across multiple lines. To combat this, you can set the default column width on the sheet (or individual column widths) so that it doesn't make a habit of breaking individual words:
- // Create the sheet in the workbook.
- LOCAL.Sheet = ARGUMENTS.WorkBook.CreateSheet(
- JavaCast(
- "string",
- "Movies"
- )
- );
-
- // Set the sheet's default column width. This will
- // apply to all columns generated for this column
- // unless otherwise stated.
- LOCAL.Sheet.SetDefaultColumnWidth(
- JavaCast( "int", 23 )
- );
Here, we are setting the default column width to "23". This makes it so that all columns generated for that sheet will have (unless otherwise overridden) a width of 23. Now, what is 23? Not sure. Points? Certainly not pixels. Certainly not inches. I do know that the 23 here corresponds to the 23 in Excel when you right-click on the column and set the column width.
The problem with this, though, is that each column will have that width, even if that width is far too wide for the column. That's a new problem. You can try to calculate the column based on fractions of a font width (but I am not about to do that yet).
Here is a little demo of building an Excel using ColdFusion and POI so I can show you what it looks like with this compromise:
- <!--- Create a query from scratch for our list of movies. --->
- <cfset qMovie = QueryNew(
- "id, name, date_watched, rating, has_fighting, has_boobies",
- "CF_SQL_INTEGER, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_DECIMAL, CF_SQL_INTEGER, CF_SQL_INTEGER"
- ) />
-
- <!--- Add rows to query. --->
- <cfset QueryAddRow( qMovie, 5 ) />
-
- <!--- Set row data. --->
- <cfset qMovie[ "id" ][ 1 ] = JavaCast( "int", 1 ) />
- <cfset qMovie[ "name" ][ 1 ] = JavaCast( "string", "Terminator 2:#Chr( 10 )#Judgment Day" ) />
- <cfset qMovie[ "date_watched" ][ 1 ] = JavaCast( "string", "2006/05/25" ) />
- <cfset qMovie[ "rating" ][ 1 ] = JavaCast( "float", 10.0 ) />
- <cfset qMovie[ "has_fighting" ][ 1 ] = JavaCast( "boolean", true ) />
- <cfset qMovie[ "has_boobies" ][ 1 ] = JavaCast( "boolean", false ) />
-
- <cfset qMovie[ "id" ][ 2 ] = JavaCast( "int", 2 ) />
- <cfset qMovie[ "name" ][ 2 ] = JavaCast( "string", "American Pie" ) />
- <cfset qMovie[ "date_watched" ][ 2 ] = JavaCast( "string", "2005/08/02" ) />
- <cfset qMovie[ "rating" ][ 2 ] = JavaCast( "float", 9.0 ) />
- <cfset qMovie[ "has_fighting" ][ 2 ] = JavaCast( "boolean", false ) />
- <cfset qMovie[ "has_boobies" ][ 2 ] = JavaCast( "boolean", true ) />
-
- <cfset qMovie[ "id" ][ 3 ] = JavaCast( "int", 3 ) />
- <cfset qMovie[ "name" ][ 3 ] = JavaCast( "string", "Friends With Money" ) />
- <cfset qMovie[ "date_watched" ][ 3 ] = JavaCast( "string", "2006/06/21" ) />
- <cfset qMovie[ "rating" ][ 3 ] = JavaCast( "float", 8.0 ) />
- <cfset qMovie[ "has_fighting" ][ 3 ] = JavaCast( "boolean", false ) />
- <cfset qMovie[ "has_boobies" ][ 3 ] = JavaCast( "boolean", false ) />
-
- <cfset qMovie[ "id" ][ 4 ] = JavaCast( "int", 4 ) />
- <cfset qMovie[ "name" ][ 4 ] = JavaCast( "string", "Better Than Chocolate" ) />
- <cfset qMovie[ "date_watched" ][ 4 ] = JavaCast( "string", "2006/10/07" ) />
- <cfset qMovie[ "rating" ][ 4 ] = JavaCast( "float", 8.5 ) />
- <cfset qMovie[ "has_fighting" ][ 4 ] = JavaCast( "boolean", true ) />
- <cfset qMovie[ "has_boobies" ][ 4 ] = JavaCast( "boolean", true ) />
-
- <cfset qMovie[ "id" ][ 5 ] = JavaCast( "int", 5 ) />
- <cfset qMovie[ "name" ][ 5 ] = JavaCast( "string", "Real Genius" ) />
- <cfset qMovie[ "date_watched" ][ 5 ] = JavaCast( "string", "2006/12/12" ) />
- <cfset qMovie[ "rating" ][ 5 ] = JavaCast( "float", 9.0 ) />
- <cfset qMovie[ "has_fighting" ][ 5 ] = JavaCast( "boolean", false ) />
- <cfset qMovie[ "has_boobies" ][ 5 ] = JavaCast( "boolean", false ) />
-
-
- <!--- Create a new instance of the POI utility. --->
- <cfset objPOIUtility = CreateObject(
- "component",
- "POIUtility"
- ).Init()
- />
-
- <!--- Get the path to our Excel document. --->
- <cfset strFilePath = ExpandPath( "./multi_line.xls" ) />
-
-
- <!--- Create default sheet object. --->
- <cfset objSheet = objPOIUtility.GetNewSheetStruct() />
-
- <!--- Set sheet query data. --->
- <cfset objSheet.Query = qMovie />
-
- <!---
- This is the name that will show up in the first
- Excel sheet tab.
- --->
- <cfset objSheet.SheetName = "Movies" />
-
- <!---
- This is the list of columns that we want to use. This
- specifies the columns AND the order in which they
- should appear.
- --->
- <cfset objSheet.ColumnList = "id,name,rating,date_watched,rating,has_fighting,has_boobies" />
-
- <!--- This defines the header row values. --->
- <cfset objSheet.ColumnNames = "ID,Name,Rating,Date Watched,Rating,Has Fighting,Has Boobies" />
-
-
- <!---
- Write the excel from the query. We are passing in the
- CSS values for the header, row, and altrow.
- --->
- <cfset objSheet = objPOIUtility.WriteExcel(
- FilePath = strFilePath,
- Sheets = objSheet,
- HeaderCSS = "font: italic 16pt verdana ; background: lime ; color: white ; border-bottom: 3px solid green ;",
- RowCSS = "border-bottom: 1px solid gold ; font-size: 12pt ;",
- AltRowCSS = "background-color: lemon_chiffon ;"
- ) />
When we open the ColdFusion and POI generated "multi_line.xls" Excel document it looks like this:
| | | | ||
| |  | | ||
| | | |
As you can see, the movie title "Terminator 2: Judgment Day" did indeed wrap to the next line based on the Chr( 10 ) line break, but you can also see that all the columns, even the narrow ones have the same width. Of course, you can then go into the Excel document and re-width the columns to make them pretty:
| | | | ||
| |  | | ||
| | | |
This looks pretty nice to me with MINIMAL work. It becomes a judgement call; how well formatted do you need the Excel document? Is a report for delivery? Is it a report for data mining? Hopefully in future editions of the ColdFusion POI Utility component I will have an easy way to deal with highly formatted scenarios. I mean, technically, you can just go straight into the POI API, but that is way too technical for something you want to be able to accomplish generically.
I have added this "Text Wrapping" function to the latest POI Utility ColdFusion component code:
DOWNLOAD LATEST VERSION HERE (POIUtility.cfc.2007.02.01)!
Hope that helps a bit.
Reader Comments
Your POI entries have been extremely helpful. I am currently doing the initial research to implement Excel generation and there's not much CF POI info out there. Thanks for sharing your hard work and I look forward to learning from any other features/problems you conquer.
Daniel,
No problem. I am learning this stuff as I go and it is totally changing the way I can do things at work. I look forward to make this process even better and sharing. Let me know if you run into any problems that I might have the answers to.
I have been using the POI with your help for a few months, but I don't understand java. Where are you doing this:
// Create the sheet in the workbook.
LOCAL.Sheet = ARGUMENTS.WorkBook.CreateSheet(
JavaCast(
"string",
"Movies"
)
);
// Set the sheet's default column width. This will
// apply to all columns generated for this column
// unless otherwise stated.
LOCAL.Sheet.SetDefaultColumnWidth(
JavaCast( "int", 23 )
);
I am using using:
<poi:document name="REQUEST.ExcelData"
file="#ExpandPath( '/POI/CPT_Transaction_Doctor_POS_NoOffice.xls' )#" style="font-family: verdana ; font-size: 10pt ; color: black ; white-space: nowrap ;">
and cfheader/cfcontent to play the file.
@Greg,
I am not sure what your question is.
I just don't understand what to do with your code in a coldfusion script.
I am familiar with the poi tags, but the code you posted for creating the sheets and setting the column widths doesn't look like it goes in a CF tag or poi tags. Can you show a little more of the coe how this works in a .cfm?
@Greg,
Try downloading the latest POI Utils project. It should come with some samples files. I believe (I don't have it in front of me), that the column widths can be set via CSS on the column tag:
<poi:column style="width: 234px" />
But, I don't have it in front of me.
Ben, my goal was really to get linebreaks to work like this post said. I think the column widths was just part of that right?
@Greg,
Are you adding the ASCII character 10 (chr(10)) to the text that you want to have break?
I'll have to dig up the code again, but I'm pretty sure I tried chr(10), chr(13) neither seemed to work.
@Greg,
If you use the POI custom tags, there should be a "white-space" CSS property that you have access to. It should default to "normal" (which allows line breaking), but can be override to be "pre" or "nowrap". This creates an abstraction to the SetWrapText() and column width adjustment in this post.
Chr(10) works for me (in summary):
- <poi:class name="header" style="font-family: arial ; font-size: 8pt ; text-align: left ; "/>
- <poi:row class="header">
- <cfset local.value = "Impact scale:" & chr(10) & "-1=negative" & chr(10) & "0=neutral"> etc...
- <poi:cell value="#local.value#" />
- </poi:row>
