0

/t5/coldfusion-discussions/create-excel-file-with-macro/td-p/234446
Jul 28, 2008
Jul 28, 2008
Copy link to clipboard
Copied
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!
Thanks!
TOPICS
Advanced techniques
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
1 Correct answer
Advocate
,
Jul 28, 2008
Jul 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.
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.
Advocate
,
/t5/coldfusion-discussions/create-excel-file-with-macro/m-p/234447#M20768
Jul 28, 2008
Jul 28, 2008
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

Guest
AUTHOR
/t5/coldfusion-discussions/create-excel-file-with-macro/m-p/234448#M20769
Jul 28, 2008
Jul 28, 2008
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Advocate
,
/t5/coldfusion-discussions/create-excel-file-with-macro/m-p/234449#M20770
Jul 28, 2008
Jul 28, 2008
Copy link to clipboard
Copied
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.
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.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

Guest
AUTHOR
/t5/coldfusion-discussions/create-excel-file-with-macro/m-p/234450#M20771
Jul 28, 2008
Jul 28, 2008
Copy link to clipboard
Copied
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!
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Beginner
,
LATEST
/t5/coldfusion-discussions/create-excel-file-with-macro/m-p/234451#M20772
Jul 28, 2008
Jul 28, 2008
Copy link to clipboard
Copied
Thanks to all for the nice answers.
For music visit http://mp3katalog.eu
For music visit http://mp3katalog.eu
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

