Posts tagged cfspreadsheet

CFSpreadsheet Custom Colors: Part Deux

Nov 3rd

Posted by existdissolve in ColdFusion

3 comments

In another post on using custom colors in CFSpreadsheet, I think I might have given the wrong impression that using custom colors REQUIRES that you abandon the CF-native spreadsheet functions. This is not true, of course–my intention was to show how adding custom colors IN ADDITION to the default palette will push you into the direction of POI and away from the CF methods.

However, if you really don’t care about using the default palette, you can have the best of both worlds: custom colors in conjunction with CF spreadsheet methods.

To not belabor the point, here’s the full code:

// create new workbook
excel = spreadsheetnew("My Worksheet",false);

// get reference to object containing color-related methods
palette = excel.getworkbook().getcustompalette();

// light blue color
lb = {
     r = javacast("int",240).bytevalue(),
     g = javacast("int",248).bytevalue(),
     b = javacast("int",255).bytevalue()};

// using index 48 to overwrite HSSFColor.LIGHT_BLUE
palette.setcoloratindex(48,lb.r,lb.g,lb.b);

// now that we have a custom color that has overwritten one of the POI constants (LIGHT_BLUE),
// we can use "LIGHT_BLUE" with CF methods and it will understand what we're talking about 

// specify format object for "odd" rows
format = {};
     format.fgcolor="light_blue";
     format.topborder="thin";
     format.topbordercolor="grey_40_percent";
     format.bottomborder="thin";
     format.bottombordercolor="grey_40_percent";
     format.leftborder="thin";
     format.leftbordercolor="grey_40_percent";
     format.rightborder="thin";
     format.rightbordercolor="grey_40_percent";

// apply style formatting to row
spreadsheetformatrows(excel,format,1);

As you can More >

, ,

CFSpreadsheet Custom Colors (and more)

Nov 1st

Posted by existdissolve in ColdFusion

1 comment

In my last post on CFSpreadsheet, I showed how you can easily create a custom row striping mechanism for your Excel data.  The one downside of the approach I outlined, however, is that it relied on the limited number of functions that CF exposes from POI, the Java magic that makes CFSpreadsheet work.

While the OOTB method is all well and good, there are a number of limitations.  One of the most severe is the limited number of colors that are available to use in spreadsheet styles.  There are a couple of dozen “named” colors that can be used, but that’s it: tough luck if you want 3 light shades of grey 😉

However, like most things in ColdFusion, you don’t necessarily have to limit yourself to what is available through CF’s tags and methods.  Rather, you can tap into the Java goodness underlying it, and modify, modify, modify to your heart’s content…including creating custom colors for CFSpreadsheet styles!

Creating a Color

First, let’s set up our workbook:

// create new workbook
excel = spreadsheetnew("My Worksheet",false);
// get underlying java methods goodness
workbook= excel.getworkbook();

As before, we create a new spreadsheet with spreadsheetnew().  However, immediately following this, we invoke the getworkbook() method.

Now I don’t want More >

,

CFSpreadsheet Row Striping

Oct 20th

Posted by existdissolve in ColdFusion

No comments

Today I was trying to get CF9’s new spreadsheet functionality to stripe the rows in the spreadsheet I was generating.  From what I could find, there’s nothing inherent to the tag (cfspreadsheet) or any of the related functions to do this.  I find this a bit curious, but stewing over should-have-been’s doesn’t get the job done.  So I decided to roll my own solution.

The first thing to check out is the documentation for the spreadsheetformatrows() function.  This takes 3 arguments:

  • spreadsheetObj: This is the spreadsheet in which the rows exist
  • format: An object of appropriate formatting rules to apply to the selected rows
  • row: The rows to which the formatting specification is applied

The “row” argument is the most important.  While you can’t specify something like “please stripe the rows with these two formatting objects” (wish list), you can either specify a range of rows (e.g., “3-12”) or a list of rows (e.g., “3,5,7,9”).

Ah, so if we can use a list of rows, the answer becomes apparent: all we have to do is roll through the query and create some lists of odd and even numbers!

To do this, I created a simple function:

function getnumlist(start,end) {
     // create structure to hold odd and even number lists
     numbers = structnew(); More >
,