Skip to main content
A
Anonymous
July 28, 2008
Answered

Create Excel file with macro?

  • July 28, 2008
  • 4 replies
  • 1961 views
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!
This topic has been closed for replies.
Correct answer Kronin555
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.

4 replies

M
musicmp3
Participating Frequently
July 29, 2008
Thanks to all for the nice answers.
For music visit http://mp3katalog.eu
A
Anonymous
July 28, 2008
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!
K
Kronin555
Participating Frequently
July 28, 2008
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.
K
Kronin555Correct answer
Participating Frequently
July 28, 2008
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.
A
Anonymous
July 28, 2008
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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices