Create Excel file with macro?

[JulioP_]

I'm working on a project where we have to create a lot of reports. Our accountant loves the fact that we can export to Excel so she can then use the data and do her own calculations, formatting, etc. Now she's asking if when we export to Excel, if certain columns can be hidden so people will just see the summary when they open the Excel file, but they can unhide the other columns if they want to get all the details. I've seen accounting software that export to Excel and hide columns, but they do it using macros. Is this even possible when exporting from ColdFusion or should we just create two sets of Excel files, some with the summaries and some with all the details?
Thanks!

[Kronin555 · Accepted solution]

You can do that using Apache POI.

Here's a code sample:
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(outputFilename));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet("Sheet Name");
sheet.setColumnHidden((short) 5, true);

I'm using the most recent version of POI. The one included with ColdFusion may or may not have this functionality, I don't know.

[JulioP_]

This is not the same as the POI Utility found at www.bennadel.com/projects/poi-utility.htm, right?
I just found that one after doing some searches here and was trying to see if they already had a way to hide columns.
Is this Apache POI just for Apache Server? We're using Windows 2003 Server with IIS.

[Kronin555]

Ben Nadel's POI utility is nothing more than a CFC/custom tag wrapper to the POI library included with ColdFusion. It's still all Apache POI code behind-the-scenes.

Apache POI is an open-source Apache-license Java library. It has nothing to do with the Apache Web Server, other than being licensed under that foundation (the Apache foundation).

http://poi.apache.org/

Like I said, a version of Apache POI is already included with ColdFusion. Go to your installation directory and look in lib. On CF8, there is:
poi-2.5.1-final-20040804.jar
poi-contrib-2.5.1-final-20040804.jar

The newest version of POI is 3.1, released on 6-29-2008. I haven't had any problems removing the POI included with CF and replacing it with the newer version, but your experience may vary.

[JulioP_]

Thanks! I looked on our production server and did see the poi 2.5.1 files. Thanks for the code, I'm going to see if I can get it to work here!

[musicmp3]

Thanks to all for the nice answers.
For music visit http://mp3katalog.eu