Adding Basic CSS Support To My POI Utility ColdFusion Component For Excel Creation

By Ben Nadel

Published 2007-01-23 in ColdFusion — Comments (30)

I recently blogged about parsing CSS values at a very low level. I have taken that CSS parsing ability and integrated it with my POIUtility.cfc ColdFusion component to give my query-to-excel functionality a bit of style. The way it is set up is that when you pass in a query to the POIUtility.cfc, you can pass in an optional HeaderCSS, RowCSS, and AltRowCSS values. The HeaderCSS defines the CSS for the header row of the Excel document. This only gets used if Column Names are sent through. The RowCSS defines the CSS for all rows of the excel document (other than the header row). The AltRowCSS, in typical cascading action, defines the CSS for alternate rows (2,4,6,8,etc.) by overriding only parts of the RowCSS.

It's working quite nicely, but there are some limitations. Other than the fact that only a small subset of the CSS world is being taken into account, there are some other caveats. For starters, colors can only be sent over in English. I am not supporting custom HEX values at this time. Colors must be chosen from the following list of supported Excel colors:

AQUA, BLACK, BLUE, BLUE_GREY, BRIGHT_GREEN, BROWN, CORAL, CORNFLOWER_BLUE, DARK_BLUE, DARK_GREEN, DARK_RED, DARK_TEAL, DARK_YELLOW, GOLD, GREEN, GREY_25_PERCENT, GREY_40_PERCENT, GREY_50_PERCENT, GREY_80_PERCENT, INDIGO, LAVENDER, LEMON_CHIFFON, LIGHT_BLUE, LIGHT_CORNFLOWER_BLUE, LIGHT_GREEN, LIGHT_ORANGE, LIGHT_TURQUOISE, LIGHT_YELLOW, LIME, MAROON, OLIVE_GREEN, ORANGE, ORCHID, PALE_BLUE, PINK, PLUM, RED, ROSE, ROYAL_BLUE, SEA_GREEN, SKY_BLUE, TAN, TEAL, TURQUOISE, VIOLET, WHITE, YELLOW

I know that other people have supported custom HEX values by overriding the built in classes, and that might be available in time, but for right now, this list above should be satisfactory.

Other than color restrictions, units are quite "iffy." When it comes to borders, I do my best to translate the border width to the border size in Excel, but Excel does not have as many options. I really only have things like "hair line", "medium", and "thick" and I have take your units like "2px" and translate that based on the border style (solid,dotted, etc.).

For font-size units, I treat them all at points (pt) regardless of how they get passed in (12px becomes 12pt). So, keep that in mind. I was going to try and translate the pixels to points, but that became to much of a hassle for me (I needed to get this done faster than slower as I am using it in production as part of a beta phase).

I also introduce the CSS definition "background-style". This defines the pattern of the background fill. I guess I should have named it "background-pattern". Oh well, next time :). For now, the available values for that are "dots", "vertical", "horizontal", and "solid". By default, it chooses solid if a background color has been set.

The code is getting too big to post in the blog, so I am linking directly to the ColdFusion component now:

DOWNLOAD LATEST VERSION HERE (POIUtility.cfc.2007.01.23)!

When it comes to applying it, you can just pass in the CSS during the WriteExcel() (and related) method calls:

<!--- Create query to ouptut to excel. --->
<cfset qMovie = QueryNew(
	"id, name, rating",
	"CF_SQL_INTEGER, CF_SQL_VARCHAR, CF_SQL_DECIMAL"
	) />

<!--- 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" ) />
<cfset qMovie[ "rating" ][ 1 ] = JavaCast( "float", 10.0 ) />

<cfset qMovie[ "id" ][ 2 ] = JavaCast( "int", 2 ) />
<cfset qMovie[ "name" ][ 2 ] = JavaCast( "string", "American Pie" ) />
<cfset qMovie[ "rating" ][ 2 ] = JavaCast( "float", 9.0 ) />

<cfset qMovie[ "id" ][ 3 ] = JavaCast( "int", 3 ) />
<cfset qMovie[ "name" ][ 3 ] = JavaCast( "string", "Friends With Money" ) />
<cfset qMovie[ "rating" ][ 3 ] = JavaCast( "float", 8.0 ) />

<cfset qMovie[ "id" ][ 4 ] = JavaCast( "int", 4 ) />
<cfset qMovie[ "name" ][ 4 ] = JavaCast( "string", "Better Than Chocolate" ) />
<cfset qMovie[ "rating" ][ 4 ] = JavaCast( "float", 8.5 ) />

<cfset qMovie[ "id" ][ 5 ] = JavaCast( "int", 5 ) />
<cfset qMovie[ "name" ][ 5 ] = JavaCast( "string", "Real Genius" ) />
<cfset qMovie[ "rating" ][ 5 ] = JavaCast( "float", 9.0 ) />


<!--- Create a new instance of the POI utility. --->
<cfset objPOIUtility = CreateObject(
	"component",
	"POIUtility"
	).Init()
	/>

<!--- Get the path to our Excel document. --->
<cfset strFilePath = ExpandPath( "./movies.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" />

<!--- This defines the header row values. --->
<cfset objSheet.ColumnNames = "ID,Name,Rating" />


<!---
	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 ;"
	) />

This creates the Microsoft Excel file "movies.xls" that looks like:

The CSS was applied quite nicely. Notice that the alternate rows share the same basic style as the standard rows (border-color, font-size, and font-family), but overrides the background color. How sweet is that? These CSS arguments are available for the following functions:

WriteExcel()
WriteExcelSheet()
WriteSingleExcel()

Again, this was a first go at it. I hope to incorporate new CSS features in future versions. In particular I would like to create a mechanism for applying styles based on logic such as column type and cell value.

UPDATED: I forgot to tell you what styles were actually supported (oops). Here are all the single values that you can use:

background-color
background-style
border-bottom-color
border-bottom-style
border-bottom-width
border-left-color
border-left-style
border-left-width
border-right-color
border-right-style
border-right-width
border-top-color
border-top-style
border-top-width
color
font-family
font-size
font-style
font-weight
text-align
vertical-align

However, you do NOT need to use single values. You can create compound CSS rules like "font: 12px verdana ;" and "border: 1px dotted green ;".

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/484

Reader Comments

todd sharp Jan 23, 2007 at 8:58 AM

48 Comments

OK dude - excellent work. Now how about building a full featured non-excel-exclusive CSS package? :)

Seriously I've been thinking about something like this lately. It would be sweet to be able to read in a CSS file (or pass valid CSS) and return a struct of structs - one key each for elements, classes and IDs in the sheet - and in those keys each sub-element broken out into a separate struct with the keys being all valid attributes and the appropriate corresponding values (empty string if not applicable).

What do you think?

Ben Nadel Jan 23, 2007 at 9:02 AM

15,902 Comments

Todd,

I think that sounds like a really cool idea and would be awesome if it existed (as I would have totally used it). If we create something like this though, we have to be very clear on how things get defined. Maybe you can explain a bit more about what you envisioned.

For example, let's say you have a TD{} style definition. Then later on you have a TD.Header{} style definition. Then later on you have a TABLE.DataGrid TD.Header{} style definition.

How does the TABLE.DataGrid TD.Header{} get stored in the struct? Is it's key "TABLE.DataGrid TD.Header"? Or is there a struct for the table that has sub-structs for the header?

If you can flesh the idea out a bit more, I can certainly go and run with it.

Josh G Jan 23, 2007 at 6:46 PM

2 Comments

Ben,

First -- excellent job writing up this CFC. It's going to be a huge help ona project I'm working on.

I'm having a problem though, when I set HasHeaderRow to to true I get an error saying that SHEETDATA.QUERY.RECORDCOUNT is not defined. It dies on line 1204 which is where you check to see that ARGUMENTS.HasHeaderRow is true and that there is a RecordCount for LOCAL.SheetData.Query. It works fine with HasHeaderRow set to false.

I tried to narrow down the problem and it looks like the RecordCount property is no longer accessible once the loop that populates the query object has finished. The query object itself is there, and I can successfully pass it but I can't access it's properties or methods. For example, if I remove the check for RecordCount it will then fail when it tried to fire off the RemoveRows method of the query object saying that the method does not exist.

I googled around looking for a solution and the closest match was a blog posting by Sarge indicating that a recent hotfix caused similar problems when used with an Informix JDBC driver:

http://www.sargeway.com/blog/index.cfm/2006/10/9/CFMX-702-Query-Object-Missing-with-Empty-Informix-Record-Set

I'm running CF 7 (7,0,2,137072) but I'm not making any DB calls, so I'm not sure if it's related since you're creating the query object artificially versus calling a JDBC driver.

Any ideas?

Ben Nadel Jan 23, 2007 at 6:52 PM

15,902 Comments

Josh,

That is really strange. That link you posted might be what you are talking about indeed. If you mail me the page you are working on, perhaps I can take a look at it and see something you are not seeing (ben [ at ] bennadel.com).

Apeng Aug 9, 2007 at 12:11 AM

2 Comments

this is error i got "Object of type class java.lang.Class cannot be used as an array".

additional information
1455 : for (
1456 : LOCAL.MetaIndex = 1 ;
1457 : LOCAL.MetaIndex LTE ArrayLen( LOCAL.MetaData ) ;
1458 : LOCAL.MetaIndex = (LOCAL.MetaIndex + 1)
1459 : ){

i am using CFMX 6.1, is there any compatible issues?

Ben Nadel Aug 9, 2007 at 7:23 AM

15,902 Comments

@Apeng,

Yes. Unfortunately, ColdFusion did not add query GetMetaData() functionality until ColdFusion MX 7. Somewhere on this blog, someone posted a hack they added to make this work in MX6, but I cannot seem to find it at this moment. Sorry.

Apeng Aug 12, 2007 at 11:15 PM

2 Comments

Ben,

Too bad, Hope to find that hack soon. Thanks anyway.

Sid McVeigh Nov 2, 2007 at 12:55 PM

3 Comments

Hey Ben,

Was just skimming through this post as I have been hunting round for a "CSS to Struct" kind of function. I started building one this morning after not having much luck finding one to steal :-)

It is very basic, and I'd like to use RegEx's to get the class content as well, but due to time constraints and my limited knowledge of regex's, I opted for a string based grab of the bits between { and }.

Here is what I did anyway...
http://wastedbandwidth.co.uk/?p=16

I was thinking that the function could also start with a validation of CSS against W3C guidelines, and as you'll see - this function is designed to parse a doc and place it in a database, giving a GUID to each class name and storing attribute value pairs which are then easier to control via our CMS.

This is a very rushed job, so please feel free to make obvious corrections guys and gals :-)

Ben Nadel Nov 5, 2007 at 7:38 AM

15,902 Comments

@Sidvicious,

It's nice to see someone else trying to accomplish this task, which I think is a huge task to be had. I like your approach as it is very simple. The only problem with it is that it looks like CSS rules with different forms will not override each other. For instance,

p {
border: 1px solid red ;
border-width: 2px 2px 2px 2px ;
}

Technically, the second rule should override the first rule (in terms of border width), but if you store them as two separate attributes-value pairs then the override might not take place in the future usage. Of course, if going into it, you are fine with this, then the simple approach is very affective.

Sid McVeigh Nov 5, 2007 at 8:37 AM

3 Comments

yeah, that's true - although I have actually gone a little further since my post. Each of the attribute pairs are saved against a class name, and the order in which they are passed into the function is also saved (the order in which they are declared in the original CSS doc), so when I call my retrieval function to load the CSS into the page being served; "getCSSByProject(projectGuid,pageGuid)", it actually writes out the CSS in the same order it was put in the db.

It's still basic, but serves the simple requirement of being able to provide our non-technical users with a nice GUI to edit the CSS for a page, without moving away from using CSS under the hood.

In case you ever have a need for it, I'll put the finished version (well, most recently released version! ;-)) on the same link.

Thanks very much for your feedback :-)

Ben Nadel Nov 5, 2007 at 8:44 AM

15,902 Comments

@Sid,

No problem. Looking forward to seeing it. Oh, and sorry about the "Sidvicious" name. I knew a guy in highschool who went by that name and I always thought it was cool and have been waiting like 10 years to use it again (check and check).

Sid McVeigh Nov 5, 2007 at 8:50 AM

3 Comments

Haha! No problem at all... I usually get called Sid McVicious by some of my mates... so not far off ;-)

Thomas Apr 10, 2009 at 10:09 AM

3 Comments

For some reason I can not seem to get any css formatting to work, no errors either. I did a wirteoutput(LOCAL.HeaderStyle) etc to see if the value is being set. I also did a dump of LOCAL.HeaderCSS ETC to see if the values were set and it all looks good but the export does not have any formatting. <br />

LOCAL.HeaderStyle = org.apache.poi.hssf.usermodel.HSSFCellStyle@1065a9f<br />

LOCAL.RowStyle = org.apache.poi.hssf.usermodel.HSSFCellStyle@2796e4<br />

LOCAL.AltRowStyle = org.apache.poi.hssf.usermodel.HSSFCellStyle@1238721<br />

Thomas Apr 14, 2009 at 8:53 AM

3 Comments

I did not comment back on this, however, I did find my issue. After a while my PC started to slow down severely and i took a look at the CF instance running. JRUN was taking up a lot of memory. I tried to stop and restart CF and it could not stop. Reboot of the machine and then things started to work properly as expected. This is not the first time I have encountered issues like this with CF. I was also using JavaLoader to utilize poi 3.2. Something I did not mention in the previous post

tsurahman May 25, 2009 at 5:07 AM

1 Comments

hi Ben,

I try to create a cell with colspan, and I add style to it,

text-align: center; font-weight: bold; border: 2px solid black;

it centered and bold
but only the first column have the border

thx

Ben Nadel May 25, 2009 at 8:55 AM

15,902 Comments

@tsurahman,

Did you put the colspan attribute on the cell tag?

zeeshan May 27, 2009 at 2:21 AM

3 Comments

Ben,

Great Work Mate! You saved me tons of hours of research!

An awesome work with not just css, but all what you have posted.

One thing I'd like to know if I can lock the headers?

Thanks!

Zeeshan

Ben Nadel May 27, 2009 at 8:26 AM

15,902 Comments

@Zeeshan,

Try putting freeze="true" on the row that you want to freeze.

zeeshan May 27, 2009 at 9:03 AM

3 Comments

I am using the write function from the POIUtility,

where does Freeze="true" set in?

Ben Nadel May 27, 2009 at 9:06 AM

15,902 Comments

@Zeeshan,

You'd have to use the custom-tag version to use it currently.

zeeshan May 27, 2009 at 9:16 AM

3 Comments

k.. thanks, but I found this alot easier.

I will look in future if locking is required, its not a big deal.

Thanks anyways!

Zeeshan

Tony Patch May 31, 2009 at 7:12 AM

2 Comments

Ben,
Great work on POI. Quick question. How do you change the background color of a row depending on a certain value that comes up in a query?
Thanks,
Tony

Ben Nadel Jun 1, 2009 at 1:23 PM

15,902 Comments

@Tony,

To get more granular control, you have to use the POI custom tags. Check out the project page for more downloads:

www.bennadel.com/projects/poi-utility.htm

Tony Patch Jun 1, 2009 at 1:27 PM

2 Comments

I'm using POI....I'm just not sure how I can set the background-color depending on a certain value on the query. Any suggestions on how I can structure this logic with POI?
Thanks dooode,
Tony

Ben Nadel Jun 1, 2009 at 1:29 PM

15,902 Comments

@Tony,

You could conditionally add a class to the <poi:row> tag.

<poi:row class="#strClass#">

Cliff Pearson Aug 3, 2009 at 9:27 AM

14 Comments

@Ben,

I'm having the same issue as tsurahman above - adding a border to a cell with a colspan only applies the border to the first cell.

Do you have a clue for me, please? B->

Kind regards

</cliff>

Ben Nadel Aug 5, 2009 at 9:15 AM

15,902 Comments

@Cliff,

Sorry man, I'm not sure I ever solved that one. There may have been a bug in the colspan of the cell. That might be fixed; are you sure you have the latest project download?