ColdFusion Sample - Create an Excel File

This post is more than 2 years old.

In this ColdFusion sample, I'll demonstrate a simple way to take form input and generate an Excel spreadsheet file. This is a simple example with a very basic form, but hopefully it will give you enough to go by to create your own similar application.

Let's begin by creating a simple form. This form is going to ask for 10 rows of data that mimics the Excel sheet I uploaded in my previous blog entry. It will ask for a name and the number of beers, vegetables, fruits, and meats consumed.

<form action="test2.cfm" method="post"> <table> <tr> <th>Name</th> <th>Beers</th> <th>Vegetables</th> <th>Fruits</th> <th>Meats</th> </tr> <cfloop index="x" from="1" to="10"> <cfoutput> <tr> <td><input type="text" name="name_#x#"></td> <td><input type="text" name="beers_#x#"></td> <td><input type="text" name="veggies_#x#"></td> <td><input type="text" name="fruits_#x#"></td> <td><input type="text" name="meats_#x#"></td> </tr> </cfoutput> </cfloop> </table> <input type="submit" name="doit" value="Create Excel File"> </form>

This form could be more dynamic of course. You could present 3 rows and use jQuery (or another less capable JavaScript framework) to easily add additional rows for input. But to keep things simple the form will just create 10 rows. Here's how it looks - and again - more work could be done to make this friendlier to the end user.

Ok - now to generate the spreadsheet. Let's look at an example.

<cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")> <cfloop index="x" from="1" to="10"> <cfset queryAddRow(q)> <cfset querySetCell(q, "Name", form["name_#x#"])> <cfset querySetCell(q, "Beers", form["beers_#x#"])> <cfset querySetCell(q, "Vegetables", form["veggies_#x#"])> <cfset querySetCell(q, "Fruits", form["fruits_#x#"])> <cfset querySetCell(q, "Meats", form["meats_#x#"])> </cfloop>

<cfset filename = expandPath("./myexcel.xls")> <cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">

We begin by turning the form data into a query. This is done by looping from 1 to 10 and grabbing the relevant values from the Form struct. You could do a trim and other checks here as well. The real magic of this code block is the last line. All it takes to create a spreadsheet in ColdFusion is one line. You pass in the query, filename, and... that's it! While this works, the output is a bit plain. Let's now look at a slight modification.

<!--- Make a spreadsheet object ---> <cfset s = spreadsheetNew()> <!--- Add header row ---> <cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")> <!--- format header ---> <cfset spreadsheetFormatRow(s, { bold=true, fgcolor="lemon_chiffon", fontsize=14 }, 1)>

<!--- Add query ---> <cfset spreadsheetAddRows(s, q)> <cfset spreadsheetWrite(s, filename, true)>

This version is a bit more complex. We begin creating a blank spreadsheet object. Next we add the header row. We can format both cells and rows, and in this example I've gone ahead and formatted the header. You have many options but I used just bold, fgcolor, and fontsize. (No one make any comments about lemon chiffon, it rocks.)

Next I add the data using spreadsheetAddRows and write out the data. Here's a quick example of the result.

Here's the entire code template.

<cfif not structKeyExists(form, "doit")>

&lt;form action="test2.cfm" method="post"&gt;
	&lt;table&gt;
		&lt;tr&gt;
			&lt;th&gt;Name&lt;/th&gt;
			&lt;th&gt;Beers&lt;/th&gt;
			&lt;th&gt;Vegetables&lt;/th&gt;
			&lt;th&gt;Fruits&lt;/th&gt;
			&lt;th&gt;Meats&lt;/th&gt;
		&lt;/tr&gt;
	&lt;cfloop index="x" from="1" to="10"&gt;
		&lt;cfoutput&gt;
		&lt;tr&gt;
			&lt;td&gt;&lt;input type="text" name="name_#x#"&gt;&lt;/td&gt;
			&lt;td&gt;&lt;input type="text" name="beers_#x#"&gt;&lt;/td&gt;
			&lt;td&gt;&lt;input type="text" name="veggies_#x#"&gt;&lt;/td&gt;	
			&lt;td&gt;&lt;input type="text" name="fruits_#x#"&gt;&lt;/td&gt;	
			&lt;td&gt;&lt;input type="text" name="meats_#x#"&gt;&lt;/td&gt;
		&lt;/tr&gt;
		&lt;/cfoutput&gt;
	&lt;/cfloop&gt;
	&lt;/table&gt;
	&lt;input type="submit" name="doit" value="Create Excel File"&gt;
&lt;/form&gt;

<cfelse>

&lt;cfset q = queryNew("Name,Beers,Vegetables,Fruits,Meats", "cf_sql_varchar,cf_sql_integer,cf_sql_integer,cf_sql_integer,cf_sql_integer")&gt; 
&lt;cfloop index="x" from="1" to="10"&gt;
	&lt;cfset queryAddRow(q)&gt;
	&lt;cfset querySetCell(q, "Name", form["name_#x#"])&gt;
	&lt;cfset querySetCell(q, "Beers", form["beers_#x#"])&gt;
	&lt;cfset querySetCell(q, "Vegetables", form["veggies_#x#"])&gt;
	&lt;cfset querySetCell(q, "Fruits", form["fruits_#x#"])&gt;
	&lt;cfset querySetCell(q, "Meats", form["meats_#x#"])&gt;
&lt;/cfloop&gt;

&lt;cfset filename = expandPath("./myexcel.xls")&gt;
&lt;!---
&lt;cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true"&gt;
---&gt;
&lt;!--- Make a spreadsheet object ---&gt;
&lt;cfset s = spreadsheetNew()&gt;
&lt;!--- Add header row ---&gt;
&lt;cfset spreadsheetAddRow(s, "Name,Beers,Vegetables,Fruits,Meats")&gt;
&lt;!--- format header ---&gt;	
&lt;cfset spreadsheetFormatRow(s,
		{
			bold=true,
			fgcolor="lemon_chiffon",
			fontsize=14
		}, 
		1)&gt;

&lt;!--- Add query ---&gt;
&lt;cfset spreadsheetAddRows(s, q)&gt;
&lt;cfset spreadsheetWrite(s, filename, true)&gt;
	
Your spreadsheet is ready. You may download it &lt;a href="myexcel.xls"&gt;here&lt;/a&gt;.

</cfif>

Your Homework!

In the code template above, I save the file and use HTML to link to it. Modify this template to instead serve the content to the user immediately. Also - use another ColdFusion function to specify a numeric formatting for the columns (all of the ones but name).

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Rob Barthle posted on 6/2/2011 at 6:40 AM

I like that, good for a basic spreadsheet. Much nicer than using CFHEADER and CFCONTENT to convert an HTML table into one ugly spreadsheet.

But these spreadsheet tags aren't going to be able to work with a real stylized spreadsheet, are they? We have to create the template in Excel then save it as Excel XML and then have our CFML output filled in XML that the user can open in Excel and have look all pretty. It becomes a real pain though if you have to tweak anything, you can real easily mess up the Excel XML.

Comment 2 by Joshua Miller posted on 6/2/2011 at 6:57 AM

I was working with the new spreadsheet functions for the first time today, so this article is quite timely for me! I really like the power and flexibility of CFSpreadsheet, but I found that there appear to be some limitations to the amount of data that it can handle. I generated a minimally styled Excel spreadsheet using a sample dataset of about 100 rows and everything worked fine. I then used the full dataset of 11,000+ rows and, despite the CFSetting RequestTimeout being set unbelievably high, the task simply never finished. I waited literally hours and nothing ever happened. The page spun and spun and no error was generated, but no Excel file was generated either. I tested a few times using smaller datasets and it always worked fine until I ran with the full set. Do you know if there is a physical limit to how much data the functions can handle? I was well below Excel's limit, but obviously above ColdFusion's.

I'm running a 64-bit install and have 12GB of memory allocated to CF. I don't think that memory is an issue - could there be something else?

Comment 3 by Raymond Camden posted on 6/2/2011 at 6:26 PM

@Rob: It depends on what you mean by "real stylized" ;) I'd check the docs to see what their formatting/etc options are. It seems pretty complex but I've just scratched the surface.

@Joshua: I'm going to try to replicate that test later today. Do you really want one sheet with 11K rows though? What about multiple sheets?

Comment 4 by Joshua Miller posted on 6/2/2011 at 6:42 PM

@Ray: Granted, it's not ideal, but it's the business requirement I was given.

As a follow-up to my post, I did receive an error message about 10 hours after kicking off the task:

An exception occurred while calling the function formatColumn.
An exception occurred while calling the function formatColumn. java.lang.ClassCastException: org.apache.poi.hssf.record.WindowOneRecord cannot be cast to org.apache.poi.hssf.record.ExtendedFormatRecord
The error occurred on line 248.

This is line 248: <cfset SpreadsheetFormatColumn(sheet,fCenter,5)>

fCenter is a structure:

fCenter = StructNew();
fCtrNum.dataformat = "@";
fCenter.alignment = "center";

Probably has something to do with the datatype represented and the "@" formatting, although the bigger issue is the 10 hour time lapse between execution and error reporting.

Comment 5 by Raymond Camden posted on 6/2/2011 at 6:44 PM

You know, 11k really doesn't seem like a lot. How many columns were you using?

Comment 6 by Scott Busche posted on 6/2/2011 at 6:55 PM

I've ran into similar issues as Josh, except the page would finish, but never output the excel file. It would then never write an excel file until I restarted CF. I haven't had the problem occur since they released 9.0.1 though.

@Rob you can read in a 'stylized' xlsx file into CF, but if you write to existing cells the formatting will be overwritten for that cell. In that case you're better off using the apache poi directly instead of the CF tags.

Comment 7 by Michael Oliver posted on 6/2/2011 at 7:18 PM

@Joshua: In regards to the speed, when you run the 11,000+ record attempt, have you watched the memory usage of jrun?

A coworker had a similar problem when working with the apache POI in Java. He explained that the issue was that the JVM has to store the entire document in memory while it's building the file. As the file takes up more of the JVM's memory, everything will slow to a crawl. The more you do with the cells in memory (formatting, functions, etc), the more resources it pulls from the JVM.

This behavior is different from when you write to a text file (to create a CSV), because the JVM writes the line to the file, but doesn't keep the file in memory.

For us, the end result was to switch to CSV files exclusively when working with potentially large recordsets, and only writing to XLS when working with small sets of data. The writing to XLS just doesn't seem to scale well. We never tried using the built in functions though, but since it also uses the POI, I would think it would have the same problems.

I have been wondering though if writing to XLSX would be any different since supposedly the file format is significantly different (I haven't tried though). Looking at the livedocs for spreadsheetNew(), it looks like you can tell it to do that using the "xmlFormat" parameter. If that's enabled, does the performance improve for you on the 11,000+ file?

Comment 8 by James Moberg posted on 6/2/2011 at 8:23 PM

Does "dataformat" work at all?
http://help.adobe.com/en_US...

I converted one of the columns to a date datatype, but it is incorrectly outputted as a text string in the Excel file and doesn't retain the specified formatting. (If I click in the cell, Excel automagically converts it to a date datatype.)

Here's the function I'm using:
<cfset SpreadsheetFormatColumns(s, {dataformat="m/d/yy h:mm"}, 2)>
I've done this before & after calling spreadsheetAddRows() and the results are the same. I've also removed the header row to no avail.

Is this a bug, am I doing something wrong or do I just not properly understand how this should work? What's the recommended way to output formatted date values to Excel using CFSpreadsheet? (This seems trivial, but I haven't been able to figure it out.) Thanks.

Comment 9 by jeff posted on 6/2/2011 at 9:02 PM

Is this for does 9.0.1? and does it handle large sheets better than 9.0. Anything over a few hundred lines would take forever to run for me so I switched to Ben's POI code. I haven't had any problems with several thousands of lines using it. Coldfusion 9.0 not so much.

Comment 10 by jeff posted on 6/2/2011 at 9:10 PM

@rob Doing the HTML trick for EXCEL supports style sheets and formulas. The only downside really is that it's not "really" an excel file and I never figured out a way to do multiple sheets. Microsoft EXCEL works great with it, but Open Office and Google Docs doesn't like it :).

Comment 11 by Joshua Miller posted on 6/2/2011 at 9:21 PM

@Ray: There are a total of 7 columns - so I wouldn't really consider it a significant amount of data for Excel to handle.

@Michael: Thanks for the note. My original version of this used no formatting whatsoever and in that case generating 11,000 rows took just a few seconds. It wasn't until I added the SpreadsheetX() functions to do custom formatting that things started slowing down. Since I need custom formatting the CSV option is a no-go.

@James: I'm starting to wonder the same thing myself. None of the formatting that I've tried appears to have any effect whatsoever. I was attempting to protect leading zeros in a column, but no combination of formatting statements - even with only 20 records in the output - resulted in any change in display.

Comment 12 by Ryan Harper posted on 6/3/2011 at 11:34 PM

I too have had mixed success with the spreadsheet tags/functions. For a simple spreadsheet it works great.

Dumping a query out to excel with thousands of rows also worked just fine (as long as you're ok with the column headers coming straight from the db).

BUT opening or trying to format an excel file that has thousands of lines caused problems.

Also trying to add 1000's of rows with any of the spreadsheet functions would also cause it to hang.

Comment 13 by Raymond Camden posted on 6/4/2011 at 11:02 PM

So I was going to do a demo of a huge spreadsheet, but it seems as if the agreement is that the issue is with custom formatting. That seems odd to me. If I were to apply a format to a column, I'd assume it wouldn't matter if I had 10 or a million rows.

Is this something you've reported Joshua?

Comment 14 by Raymond Camden posted on 6/4/2011 at 11:22 PM

"Dumping a query out to excel with thousands of rows also worked just fine (as long as you're ok with the column headers coming straight from the db). "

In my example, I did the headers manually. You don't have to use them from the query.

Comment 15 by Joshua Miller posted on 6/6/2011 at 4:52 PM

@Ray - I haven't reported as a bug yet. I've not worked with CFSpreadsheet much and before reporting this thought I would see if maybe I was doing something wrong.

As a side note to the column names, you can also give them custom names in your query and then get the benefit of "pretty names" without having to specify them in the CFSpreadsheet tag:

SELECT [foo] AS [My Pretty Name]
FROM [dbo].[tblFoo]

CFSpreadsheet seems to handle the column names properly when rendered. If all you want is a simple query and dump to Excel it works really well.

Comment 16 by Ryan Harper posted on 6/6/2011 at 6:58 PM

I should have clarified my comment in saying that the column headers come straight from the query. That's what I've been doing lately also.

The only other problem that I've had with dealing with cfspreadsheet is when working with date/time fields. Opening a csv file into excel works better for interpreting the cells correctly as dates.

Comment 17 by James Moberg posted on 6/6/2011 at 7:13 PM

Here's a quick test that I wrote that allows you to format dates and download the Excel spreadsheet.
http://pastebin.com/aQnembR3

It doesn't appear as though spreadsheetFormatColumn() works when combined with spreadsheetAddRows(). The only time I've been able to successfully format a date cell is when the cell is manually populated using spreadsheetSetCellValue().

Comment 18 by Joe posted on 6/11/2011 at 12:25 AM

I've found a few things relating to cfspreadsheet and ColdFusion 9.0 vs ColdFusion 9.0.1.
In CF9 formatting more than just a few rows and columns would result in an "...maximum number of fonts was exceeded" error. The solution was to install CF 9.0.1. If you are still getting the error try to change any loops which format by cell (spreadSheetFormatCell) to the SpreadsheetFormatColumns or SpreadsheetFormatRows (plural). From what I've read Excel stores formatting information for whole columns or for cells and nothing in between. Furthermore, try to use the 9.0.1 function SpreadsheetFormatCellRange which is also less memory intensive for Excel. See this thread for more detail: http://forums.adobe.com/mes...

Comment 19 by James Moberg posted on 6/11/2011 at 12:29 AM

Please test the following code on CF 9/9.01 and post regarding whether or not anyone is able to successfully format Excel dates using spreadsheetAddRows() and spreadsheetFormatColumn().
http://pastebin.com/aQnembR3

Comment 20 by Joe posted on 6/11/2011 at 12:54 AM

Just from taking a quick glance at the code, I'm not seeing a spreadsheetWrite command anywhere, perhaps that method might be able to format correctly?

Comment 21 by James Moberg posted on 6/11/2011 at 1:38 AM

I've used spreadsheetWrite() and the result is the same. I'm using spreadsheetReadBinary() so that I can return the spreadsheet directly in the browser without having to resort to a file write, read and/or redirect function.

Please change it to spreadsheetWrite() and let me know if the behavior is any different. I'm guessing that the results would be the same as that's what I was doing before I rewrote this to use an immediate download method.

Comment 22 by Rob Barthle posted on 6/13/2011 at 8:05 PM

In a small stroke of irony, the day after posting my comments on this I found a chance to use CFSPREADSHEET. A legacy application was timing out after 5 minutes creating a 1500 row HTML table and streaming it out as an Excel file using CFHEADER and CFCONTENT. It was a basic data dump so I gave CFSPREADSHEET a try. After a runtime of six seconds a native Excel file was created, something Excel doesn't have to parse and convert like it would an HTML table. Very happy with that.

Comment 23 by Les Konley posted on 6/17/2011 at 10:36 PM

I'm struggling with the issue that using CFSPREADSHEET to generate an Excel (.xls) file does not format date columns as DATES. I was hoping that using the new CFSPREADSHEET tag would eliminate our need to install MS Office on the production servers and retire the use of the CFX_EXCEL tag. Since we're only on CF 9.0, we can't use the AddSpreadsheetRow functions because it's too slow for pruduction environments. No matter how I format the date fields using QuerySetCell to reformat my query object before passing it to the CFSPREADSHEET tag, Excel just doesn't recognize dates AS DATES. What a bummer. I don't see any evidence that CF9.0.1 addresses this issue, except that I might then be able to create a spreadsheet object without performance issues and do formatting that way.

Comment 24 by Raymond Camden posted on 6/19/2011 at 12:49 AM

Dumb question - if your data is query based, can you ensure the column is a date type? And if not - maybe try a query object instead.

Comment 25 by James Moberg posted on 6/19/2011 at 6:53 AM

Ray... Been there, done that. Nothing works. Please research this yourself and see if you are successful. In CF9, you can't appear to format values (even date values) as dates in Excel unless you format each cell individually. Here's some proof on concept code where no SpreadsheetFormatColumn() functions seem to work.
http://pastebin.com/aQnembR3

If this hasn't been officially reported as a bug yet, please do so. It's kinda of a show stopper for me for using CFSpreadsheet to create database updates. (Ben Nadel's POI Utility CFC seems to work fine even though it's slower at times.)

I always hope that it's me that's doing something wrong... since I've upgraded to CF9, it hasn't been me as often. :(

Comment 26 by Raymond Camden posted on 6/19/2011 at 6:21 PM

I can confirm it's not working. Just to be sure, I added a color key to your dfStyle structure and it worked ok. Best I can recommend is to ensure you've got it logged as a bug.

Comment 27 by James Moberg posted on 6/20/2011 at 6:30 PM

What is the code that you used and what is a color "key"? Did passing a "color" fix the date format and add the time or did it just pass the correct color? (I can live with the column being a different color if it is passing/formatting the correct data.) Thanks.

Comment 28 by Raymond Camden posted on 6/20/2011 at 6:33 PM

Sorry I wasn't clear. What I meant was - I modified your struct to ALSO include a color change. I verified the color DID change, but the date formatting did NOT work. This implies you were doing the right thing, but it didnt work. Hence me saying you should file a bug report. Your CFM is perfect too so be sure to include the link or the code itself.

Comment 29 by James Moberg posted on 6/20/2011 at 7:49 PM

ok. I wanted to make sure that I was reporting the correct issue & didn't need to add that date formats were correctly formatted only when additional color attributes were added. I've reported it. Thanks.

Comment 30 by Les Konley posted on 6/20/2011 at 8:12 PM

Ray & James, I have additional information about the CFSPREADSHEET and Excel not recognizing resulting date fields. First of all, our code is both using a query object as well as setting explicit date formats [i.e, cfset temp = QuerySetCell(GetExcelReport, "FollowUpDate", "DateFormat(GetResult.FollowUpDate, "MM/DD/YYYY")) ]. When we open the spreadsheet in Excel (built using CFSPREADSHEET), the date column is formatted as regular text or general (left-justified). On this site (http://www.appnitro.com/for... a work-around is offered, so the business users can highlight the column (within Excel), then select Data > Text to Columns, then click FINISH on the Wizard (without needing to step through it)... and date fields are now recognized by Excel as a DATE. We may need to revert back to installing MS Office on the production servers and continue using the old CFX_Excel tag if the business users decide they don't want this added minor inconvenience, at least until that particular bug is corrected on a future release. Thanks for submitting a bug report to Adobe about this issue.

Comment 31 by Joshua Miller posted on 6/27/2011 at 11:13 PM

Update on this discussion. I've installed the CF 9.0.1 update, removed all references to SpreadsheetFormatX functions and I still can't get more than ~400 rows before CF dies. I'm using grouped output and using SpreadsheetAddRow() for each row of data because I need a custom formatted "header" row before each section in the file.

I know, I know, I could make each "section" it's own sheet or change the formatting of the Excel doc to make it work with CF, but the formatting is a business requirement and I'd rather CF just work that me have to work around it.

It seems unlikely that someone didn't run into this when testing the new features in CF9. How is it that I can't even get 500 rows of data out without the server dying? I've got 12GB of RAM, all but 2 of which are dedicated to CF (64 bit) and on two different servers I see the same behavior.

Using SpreadsheetAddRow() with 7 columns and 500 rows of data with no formatting would presumably not be outside of the testing parameters of the new functions.

Has anyone else used group output with SpreadsheetAddRow()? Did it work for you?

Comment 32 by James Moberg posted on 6/28/2011 at 5:08 AM

I wrote a quick test script and it worked with 500 rows and 7 columns (using Windows 2003 with only 4Gb total RAM.)
http://pastebin.com/VmF2r7Rz

I added getTickCount() as one of the column values and generation does appear to start to slow down after about 150 rows. Here are the incremental averages to generate 500 rows using SpreadsheetAddRow():
Rows 1-100 12.34ms per row. Total = 1,234ms
Rows 101-200 37.66ms per row. Total = 3,766ms
Rows 201-300 60ms per row. Total = 6,000ms
Rows 301-400 83.9ms per row. Total = 8,390ms
Rows 401-500 107.51ms per row. Total = 10,751ms

It looks like CF9 increases processing by 2.5 seconds for each increment of 100 rows. I attempted to export 2,000 rows and it took 477,890 milleseconds. The final rows took 485 milleseconds each to individually add. The overall average is 239ms per row. Ouch!

Maybe a better technique would be to format your rows and save them to a query object and then use SpreadsheetAddRows() (with the "s")... but you can then probably kiss your data format & styles good-bye. :(

Comment 33 by Joshua Miller posted on 6/28/2011 at 7:13 PM

FWIW I went back and rewrote the routine using Java POI and the entire 12,245 row file takes 742ms to generate with formatting and proper data types intact.

Comment 34 by Raymond Camden posted on 6/28/2011 at 7:14 PM

Bit behind on all this - but guys - don't forget to record/note issues with the public bug tracker. Adobe needs to be aware in order to reply.

Comment 35 by James Moberg posted on 6/29/2011 at 1:21 AM

In the process of reporting it, I discovered that it was reported back in December 2009 (bug #81154). I added a comment with the code I posted at PasteBin & voted it up.
http://cfbugs.adobe.com/cfb...

Any idea if Adobe looks at CFbugs.Adobe.com? There's no status on this bug other than "verified" and I believe that this may have been provided by the submitter.

Comment 36 by Raymond Camden posted on 6/29/2011 at 1:22 AM

Can't speak for the CF team but as far as I know, yes, it IS taken seriously.

Comment 37 by James Moberg posted on 6/29/2011 at 1:24 AM

Wait a minute... I just realized that Adobe has the status on bug #81154 as "closed". I don't want to submit a duplicate bug. Any idea on how to get them to re-open a bug that hasn't been fixed? (Any idea regarding how to identify who prematurely marked this as "closed"?)

Comment 38 by Raymond Camden posted on 6/29/2011 at 1:31 AM

Hmm. If I were in a bad mood, I'd post a comment AND post a new bug. Just to make it a bit more... vocal.

Comment 39 by Dan Murphy posted on 7/22/2011 at 7:44 PM

James, Ray, and Les - I know I'm a bit late to the game here, but I think I just found a workaround for the date formats in Excel that has been working out well for me. First, I'm creating my own query with querynew based on another query and using that to export to Excel. If I make the datatype in the querynew to cf_sql_varchar instead of date or cf_sql_timestamp and then output the query to the Excel spreadsheet, it will take whatever format you give it. If you stop there, Excel will still see the data as just text, not an actual date (although formatted correctly). Second, just set the dataformat using spreadsheetFormatColumns to match the format you specified in the query, and Excel now recognizes it all as a date. Ray, I'm using your DateTimeFormat function from CFLib, so here is how it all worked out for me. (this is just part of it all obviously, but you'll catch the idea).

Here's the query...
<cfset mys = QueryNew("OrderNumber,InScan,OutScan","cf_sql_varchar,cf_sql_varchar,cf_sql_varchar")>
<cfloop query="qryThroughputScans">
<cfset queryAddRow(mys)>
<cfset querySetCell(mys,"OrderNumber",NumberFormat(qryThroughputScans.OrderNumber,000000))>
<cfset querySetCell(mys,"InScan",DateTimeFormat(qryThroughputScans.InScan))>
<cfset querySetCell(mys,"OutScan",DateTimeFormat(qryThroughputScans.OutScan))>
</cfloop>

And then the column format that I use after creating the Excel spreadsheet...
<cfset spreadsheetFormatColumns(s,
{
alignment="center",
dataformat="m/d/yyyy h:mm AM/PM"
},
"2-3")>

I tested this out with all sorts of weird formats just to make sure it works, and I had great success with it. Maybe this is already well known but I couldn't find anything when I was looking for a fix. Hopefully this helps someone out when they go looking for an answer to the date issue.

Thanks,
Dan

Comment 40 by Raymond Camden posted on 7/24/2011 at 2:16 AM

Thanks for sharing this, Dan.

Comment 41 by James Moberg posted on 7/24/2011 at 6:41 PM

"m/d/yyyy h:mm AM/PM" isn't one of the allowable formats listed here:
http://help.adobe.com/en_US...

Are you able to generate any custom format you desire? If so, is there any documentation anywhere? (Adobe doesn't provide any direction regarding this on their website.)

Thanks.

Comment 42 by Raymond Camden posted on 7/24/2011 at 6:46 PM

Well, the docs _do_ list supported formats. And his looks real close to what's listed. The time portion is definitely there. So maybe it's just implied that you can mix the date/time ones together like that. Either way - one of you guys should mention this format in a comment to the Livedoc edition.

Comment 43 by Dan Murphy posted on 7/26/2011 at 6:38 PM

So I'm not sure if this is a matter of allowable formats, or just a way to create whatever custom format you want. I tried this with several formats that were not defined on the docs (ie - "m/yyyy h AM/PM") and they all came in as dates to the Excel spreadsheet if I followed the same process I outlined previously. I'm not aware of any documentation on it - this was just me goofing around to try to get the format I wanted.

I guess I'll just put something similar to my previous comment on this post in the comments of the docs as Ray suggested.

Comment 44 by Tony Weeg posted on 7/29/2011 at 9:05 AM

hey ray, have you had any luck adding a second sheet to an excel doc created by cfspreadsheet? im getting a null pointer exception on the update call?

thanks
tony

Comment 45 by Raymond Camden posted on 7/29/2011 at 3:26 PM

Haven't tried it. Create a simple CFM that replicates the issue and fire it off to me. I can try hacking it on it.

Comment 46 by Jonathan posted on 11/27/2011 at 10:38 PM

Anyone know how to make the spreadsheet recalculate?

Comment 47 by Raymond Camden posted on 11/28/2011 at 12:41 AM

What do you mean exactly?

Comment 48 by Josh Tischer posted on 2/18/2012 at 3:12 AM

in CF8; I need to export an excel document from a HUGE data set. The code is using a cfcontent variable to write the file to a folder.

I keep getting a timeout 500 jRun servlet 500 error. I am thinking about appending 500-1000 records on an automated page refresh. Is there a better way?

Comment 49 by Raymond Camden posted on 2/18/2012 at 10:05 PM

You may not be able to use CF for this (or CF's cfspreadsheet tag). You may want to look at using POI (the underlying Java library) directly instead. I believe Ben Nadel has a wrapper library for it. You may also want to consider NOT sending the file to user. Instead, try using cfspreadsheet to create the file, save it, and then provide a link to it. That removes the 'stream' to the user where things may time out.

Comment 50 by misty posted on 3/6/2012 at 2:29 AM

Hi ray, I tried using an ajax request to the page and using the cfsavecontent variable to create the excel, but i am unable to either store the file as excel on the disk, am i missing something

Comment 51 by Raymond Camden posted on 3/6/2012 at 9:59 AM

You can't use Ajax to render binary data like that.

Comment 52 by misty posted on 3/6/2012 at 10:39 AM

btw, the things are happening on the server page, i am just getting a response from the page in the form of an alert, should i try with iframes or something

Comment 53 by Raymond Camden posted on 3/6/2012 at 10:41 AM

Not quite sure what you are asking then. Maybe back up and explain again?

Comment 54 by Misty posted on 3/6/2012 at 5:43 PM

As your previous commnet, u are saying i cannot render the cfsavecontent tag to generate an excel for me, but is there any way like using an iframe in the server page i mean where i am writing my cfsavecontent to generate the excel for me and save it to the disk and show the message to the user. The thing is i am using a serialize() of jquery to send form values to the submission page and using cfsavecontent to create a table and write that values in the excel file

is this a possiblity

Comment 55 by Raymond Camden posted on 3/6/2012 at 6:37 PM

Ah, so you want to use an Ajax request that fires off a server side script to generate Excel, and then simply have that downloaded to the user, right?

Comment 56 by Misty posted on 3/6/2012 at 9:32 PM

Yes, it should just create an excel with cfsavecontent variable and store on the disk and show a message Thanks message to the user

Comment 57 by Raymond Camden posted on 3/6/2012 at 10:26 PM

If you use a simple document.location=X, where X is a CFM that uses cfcontent to serve up binary data, you get the same rule - a download.

Comment 58 by Misty posted on 3/7/2012 at 2:13 AM

Um, Getting little confused, if you tell me properly that will be gr8

Comment 59 by Raymond Camden posted on 3/7/2012 at 4:37 AM

Well, first off, are you aware of the technique of using cfcontent to serve up binary files to the user? (Don't think about Ajax, let's keep it simple first.)

Comment 60 by Misty posted on 3/7/2012 at 10:52 AM

yes, here is the code, i am using

<cfsavecontent variable="a">

</cfsavecontent>
<cfset theDir = GetDirectoryFromPath(GetCurrentTemplatePath())>
<cfcontent type="application/vnd.msexcel" variable="#ToBinary(ToBase64(a.Trim()))#" file="#theDir#/#Dateformat(now(),'dd_mm_yyyy')#_#TimeFormat(now(),'hh_mm_ss_tt')#.xls">

Comment 61 by Raymond Camden posted on 3/7/2012 at 11:48 PM

Ok - so you understand how that works, right? Like if that file was ray.cfm, if you link to it, when the user clicks the link, they get prompted to download. To them, it looks like they didn't leave the page.

So to do the same in JavaScript, you would use document.location.href = 'ray.cfm';

Comment 62 by Misty posted on 3/8/2012 at 12:21 PM

Ok, i think thigs are going over the head now, you said use document.location.href:

i am using like this,

var str = $("form").serialize();
$.ajax({
type:"POST",
url:"submit.cfm",
data:str
});

where i use the document.location.href

i do not want the user to get page reloaded and then show them a message

Comment 63 by Raymond Camden posted on 3/8/2012 at 6:04 PM

Hmm. If that isn't working - then I'll need to look at this later. I'm traveling today.

Comment 64 by Raymond Camden posted on 3/8/2012 at 8:04 PM

Ok, let's imagine test.cfm, a page that accepts dynamic input to generate a pdf.

http://pastebin.com/QZ1LB7im

Now let's look at front end code to hit this:

http://pastebin.com/yrzk2DFx

Notice I use document.location.href to "Push" the user to the CFM. What they experience though is simply a download.

Comment 65 by Misty posted on 3/9/2012 at 1:48 AM

Hi Ra, Made it work, No need to use the document.location: here is my version

http://pastebin.com/vvqVhsju

Comment 66 by Misty posted on 3/9/2012 at 1:49 AM

oops! sorry missed (Y) from ur name, Apologies

:)

Comment 67 by Raymond Camden posted on 3/9/2012 at 6:36 AM

So wait - that worked for you? Your success.cfm is _exactly_ like my CFM? (Well, not exactly, but same technique of generating binary data and sending via cfcontent.)

Comment 68 by Albert Aynbinder posted on 5/10/2012 at 7:32 PM

Hi, Ray,
Thanks for your example how to use <cfspreadsheet>
Finally I’ve build and save excel file.
But I have problem with headers, I have to use variables Like Current Year and Prior as a column name.
Do you have any idea how it solve.
Thanks for Help

Comment 69 by Raymond Camden posted on 5/10/2012 at 8:07 PM

If you wanted dynamic headers, you would change how you make the query. In mine, it is hard coded to Name, Beers, etc. Just make it dynamic.

Comment 70 by Albert Aynbinder posted on 5/10/2012 at 8:37 PM

Raymond,
Is it posible to say:
<cfset q = queryNew("RUN,DATE, TY_#dataQuery_R1.TAX_YR#,CURRENT_TY_POSTED,TY_2009_TOTAL,TY_2010_TOTAL,PR2009,PR2010,DIFFERENCE",
"cf_sql_varchar,
cf_sql_varchar,
cf_sql_varchar,
cf_sql_integer,
cf_sql_integer,
cf_sql_integer,
cf_sql_decimal,
cf_sql_decimal,
cf_sql_varchar" )>

Comment 71 by Raymond Camden posted on 5/11/2012 at 3:12 PM

Sure.

Comment 72 by Monique posted on 8/24/2012 at 12:10 AM

Very nice. Is it possible to have the file open up directly vs the user clicking on a link to open it?

Comment 73 by Raymond Camden posted on 8/24/2012 at 12:39 AM

Two ways. You could just cflocation to the XLS file you made, or, don't save to the file system at all. Just use cfcontent to serve up the binary data.

Comment 74 by Ron Randall posted on 9/5/2012 at 12:39 AM

How can I generate a spreadsheet with:

a. Colheads on Row 2?

b. Filtering already ON (when downloaded, opened)?

c. Print set to "Fit to Page"?

Thanks?

Comment 75 by Raymond Camden posted on 9/5/2012 at 1:29 AM

I don't think you can do the colheads on row 2. Not as far as I know.

Comment 76 by James Moberg posted on 9/5/2012 at 1:39 AM

You may have to start with pre-existing empty Excel template w/Row 2 headers, filtering already enabled and print already set to "fit to page" and then use SpreadsheetAddRows() to populate the rest of it.

Comment 77 by Branden deBuhr posted on 12/3/2012 at 9:26 PM

I see this post goes a bit way back but I've been working with a cfspreadsheet app for a classified company in which we're uploading allot of data via cfcfspreadsheet. My first iterations of the app dealt with loading the data and then looping through it as I've seen others talk about in this thread. I'm working here with a very smart guy who is literally a sql genious... He introduced me to a sql statement that is an "insert into tablename select statement". In this situation the coldfusion server makes one single call to the database to insert all the data without running separate loops. We've inserted thousands of rows in less than a second or two and then redirected to a review page all taking less than a few seconds... It's pretty amazing to see the performance gains!

<br><br>
Actually for more detail, we go to a global temporary table first with the data, and then move it to the actual destination table. While coming from the GTT so we can do the joins we need for the business right when we actually do the insert. To make all this happen, the SQL must be perfect and the load and destination field order has to be perfectly in sync to work. But when you do, the performance is off the charts! So I hope this tid bit helps someone else here...<br><br>

The problem I'm trying to figure out is that some of our users are trying to upload encrypted spreadsheets and I need to be able to either detect this or ask for a password or somehow intelligently account for all of this.<br><br>

Any suggestions?

Comment 78 by Raymond Camden posted on 12/4/2012 at 2:54 AM

As far as I can tell, we do not support opening password protected sheets. We support adding them to a sheet. I'd file an ER for it.

You could handle it at least by wrapping your read with a try/catch.

Comment 79 by Jay Rizzi posted on 4/11/2013 at 11:23 PM

For anyone looking for an answer to the SpreadsheetAddRows performance on medium to larger queries I thought I would add this:

dont use SpreadsheetAddRows or any of those related functions like SpreadsheetFormatRows, the performance hit on the JVM is staggering

My solution to this was to execute the query, create an xls file, use the tag cfspreadsheet to write to the newly created xls file, then serve to the browser, deleting after serving

Using SpreadsheetAddRows, Runtime went from crashing server on 1000+ rows, 5+mins on 700 rows

Using the method outlined above 1-1.5 secs

Comment 80 by yonaker posted on 5/31/2013 at 8:37 PM

Has any one completed the Homework - Modify this template to instead serve the content to the user immediately. I can't seem to get it to render without saving it to my server first. Thanks

Comment 81 by yonaker posted on 5/31/2013 at 9:05 PM

Nevermind... I found it under Quick followup to my CFSpreadSheet Samples
Just what I was looking for!!!!

Comment 82 by Russ posted on 6/6/2013 at 10:47 AM

Jay,

Does your solution allow formatting of individual cells? That's the reason I need to build the spreadsheet a cell at a time.

Maybe construct the spreadsheet via Query, then go back through it and do the formatting in a 2nd pass?

Thanks,
Russ

Comment 83 by WebYoda posted on 7/16/2013 at 6:18 PM

Thank you for your random act of kindness in posting this sample code. It worked the first try and made it possible to complete my task of converting a table we have to excel simply using your sample and the example.

Russ #, check the previous comments, there were suggestions of how to use other data types. If every cell in every row in your spreadsheet was to be of different types, then you would have to craft the code to be manual rows instead of a loop.

Comment 84 by Faisal posted on 7/23/2013 at 7:41 PM

Hi Ray, Thanks for the article. It was really helpful. Btw, at this moment I am stuck at one point. Actually was trying to get account numbers by a query and displaying those account numbers as column header after exporting into excel.

The following code is working fine but displaying wrongly:

<cfset variables.setHeader = "">
<cfoutput query="myLoop">
<cfset variables.setHeader = variables.setHeader & ", C_#myLoop.acc#>
</cfoutput>

The above code is displaying column header (just one sample data I am showing here) but it is showing as 'C_000391'. Which is wrong! For display, 'C_' should not be with account number, it is the account number which should create the column header alone. However, as soon as I am taking off 'C_' from infront of #myLoop.acc# it is giving the following error:

The column name 000391 is invalid. If possible, please let me know what will be the best solution(s) to resolve this toughest problem on the earth :)

Thanks always Sir

Comment 85 by Raymond Camden posted on 7/23/2013 at 10:31 PM

I'm afraid I do not have enough information here. What exactly is setHeader and how exactly are you using it?

Comment 86 by Russ LaValle posted on 7/23/2013 at 11:54 PM

I don't see a closing double-quote in the second cfset line in Faisal's code snippet...

Comment 87 by Garrett Neisler posted on 8/27/2013 at 7:12 PM

I know this is an old thread but wanted to weigh in a solution I have come across for the date format issue.

Ran into this issue and was hard to duplicate until I upgraded to 2013 excel and was able to duplicate. My first attempt was a success at upgrading the output for spreadsheetNew to output XLSX using the xmlFormat attribute. Bam, dates are properly formatted!

<cfset sheet = SpreadSheetNew('CustomerData', "TRUE")

<cfheader name="Content-Disposition" value="attachment; filename=CustomerData.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#spreadSheetReadBinary(sheet)#">

Any thoughts?

Comment 88 by James Moberg posted on 8/27/2013 at 7:35 PM

According to the ColdFusion 9 documentation, SpreadsheetNew() accepts a second optional boolean value (but the docs display it passed as a text string) and this doesn't seem to have any effect.

I've posted sample code here. Adding XLSX support for Microsoft Office Excel 2007 and changing the cfcontent type didn't generate the desired date formatting I requested:
http://pastebin.com/aQnembR3

What datetime format are you using? According to Adobe, "m/d/yy h:mm" is a valid date format, but it returns date-only values (without the time) when using ColdFusion 9 or 10.

Comment 89 by Garrett Neisler posted on 8/27/2013 at 9:14 PM

James, I am using the "m/d/yy" dataformat.

I now understand your issue as times do not display for me either, I had a similar issue where my dates would display at 5 random numbers in excel.

Using your test I still get a custom dataformat in Excel with the following in all rows: 8/27/2013 0:00

Comment 90 by Laurie posted on 2/26/2014 at 3:49 AM

Thanks for this code!

Comment 91 by ion posted on 4/1/2014 at 6:19 PM

i'm trying to avoid making an extra trip to the server just to get a spreadsheet. I mean, i already have the data and display it soon as the page loads. If user wants to save it as excel, i don't want to reload the page and rerun the query, but rather use the original data, saved in a form field. is there a way to translate that cfsavecontent mechanism into a javascript function? Generating the html table would be pretty straightforward, but how do i pop the last part
<cfheader name="Content-Disposition"
value="attachment; filename=machineList.xls" />

<cfcontent type="application/msexcel"
variable="#ToBinary( ToBase64( myVar) )#" />

thanks

Comment 92 by Raymond Camden posted on 4/1/2014 at 10:18 PM

Modern browsers can create files on the client side and even stream them to download, but you would need to handle creating XLS-compatible binary data client-side, which is not something I think is possible. In theory you could make a CSV file, and I think most computers w/ Excel installed will open a CSV in Excel.

But again - the "create a file you can download" is *very* much limited to modern browsers.

Comment 93 by Raymond Camden posted on 4/3/2014 at 2:44 PM

I created a demo of dynamically generating CSV and downloading it. I'll be blogging it today.

Comment 94 by Siva posted on 4/18/2014 at 6:55 PM

Hi ,

We have migrated from Microsoft Office to Open Office. My current logic of writing to an excel sheet in MS Office is not working in Open Office . When i try to open the .xls file in the word pad, i am getting lot of blank spaces before the html content. once i have removed the blank spaces, i could be able to open the .xls file using Open Office. May i know what can be down to fix this issue ?

Comment 95 by Raymond Camden posted on 4/18/2014 at 8:01 PM

I'm sorry, but I don't quite understand what you are saying. In theory, the binary generated by cfspreadsheet should work in OpenOffice as well.

Comment 96 by Siva posted on 4/18/2014 at 10:11 PM

<cfsavecontent variable="a">

</cfsavecontent>
<cfcontent type="application/vnd.ms-excel" reset="no" variable="#ToBinary(ToBase64(a.Trim()))#">

I have used the above logic from you blog discussion and the problem has been resolved now.

HTML content had lot of blank spaces which i have trimmed off using the above logic from your blog.

Thank You very much for you blog.

Comment 97 by Siva posted on 4/18/2014 at 10:13 PM

I did not use cfspreadsheet , but i have used the following logic

<cfcontent type="application/vnd.ms-excel">

<cfsavecontent variable="a">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
.......................
..........................
......................
</head>
.............
....................
</body></html>
</cfsavecontent>
<cfcontent type="application/vnd.ms-excel" reset="no" variable="#ToBinary(ToBase64(a.Trim()))#">

Comment 98 by Raymond Camden posted on 4/19/2014 at 1:24 AM

Um, well, this blog post is about cfspreadsheet. If your question is not related to the topic, I'd ask you please post it to Stack Overflow.

Comment 99 by Niva Le posted on 5/10/2014 at 4:42 AM

I used the following code to create multiple sheets, but I can't get the getPrintSetup() function to work on these sheet.
<cfset i=1>
<CFLOOP query="getproducttypeid">
<cfset sObj = "sObj"&"#i#">
<cfset sObj=SpreadsheetNew("#getproducttypeid.description#")>
<cfset poiSheet = sObj.getWorkBook().getSheet("#getproducttypeid.description#")>
<cfset ps = poiSheet.getPrintSetup()>
<cfset ps.setLandscape(true)>
<cfset poiSheet.setAutobreaks(true)>
<!--- fit to one page --->
<cfset ps.setFitWidth(1)>
<cfspreadsheet action="update" name="sObj" filename="#thefile#">
<cfset i = i+1>
</cfloop>
Do you know why? Am I missing something here?

Comment 100 by Jarlath Eoin Gallagher posted on 7/9/2014 at 1:53 PM

Hi Raymond,

Firstly, thank you for this tutorial on the use of spreadsheetNew(). We have been able to implement this to provide us with an excel export in very few lines of code, unlike some of what we experienced with poiutility approaches.

We have run into one issue with spreadsheetFormatRow. Is there a known issue in Coldfusion 10 for this? I have attempted to take an exact replica of what you have done in this tutorial above and run it upon Apache and ColdFusion 10,286680 with Tomcat 7.0.23.0.

We are running into an issue however where the process fails at spreadsheetFormatRow with an issue here 500 org.apache.poi.ss.usermodel.Font.getCharSet()...

It may be a long shot but hoping someone has experienced this and resolved it, i will keep investigating and reply when i know more.

Thanks,

Jarlath

Comment 101 by Raymond Camden posted on 7/9/2014 at 3:39 PM

I just tested and my code worked ok. Using build 10,0,13,287689 which I think is above yours. You may want to ensure you are 100% updated. If it still doesn't work you need to file a bug report.

Comment 102 by Jarlath Eoin Gallagher posted on 7/9/2014 at 7:45 PM

Thank you Raymond,

I have, as suggested, updated my CF10 and have discovered that there are 2 poi jar variants in my lib folder. The poi.jar distributed in update 12 and one named poi-3.8-20120326.jar. It turns out a fellow developer included this poi-3.8-20120326 for a specific purpose and it appears to be in conflict with the distributed jar.

Using poi.jar alone allowed the above code to work correctly so it is now down to the infrastructure team to ascertain what the risk is of having the 2 different jars in the one lib folder.

Thank you for your help and pointers in the right direction.

Regards,

Jarlath

Comment 103 by Bob Baader posted on 11/17/2014 at 10:09 PM

I am running cf10 on Linux. I am not using cfspreadsheet because it is not a straight output due to conditions. I am generating the excel file just fine and it prompts download. What I need to do is save the excel file to the server and have my email script pick it up and email as an attachment. If I manually place an excel file in the server my script emails it. Otherwise, the file is not written to the server. Is it a permissions issue?

Comment 104 by Raymond Camden posted on 11/18/2014 at 5:02 PM

I'd guess so. That should be easy to confirm though, right?

Comment 105 by Bob Baader posted on 11/18/2014 at 7:23 PM

Nice to meet you! Got it all sorted. Thanks. :)

Comment 106 by haysfluid posted on 7/1/2015 at 7:45 PM

Using the code above, how can you also repeat the header row on each printed page. This is needed if the number of rows exceeds the size of a page when printing.

Comment 107 (In reply to #106) by Raymond Camden posted on 7/1/2015 at 9:26 PM

Hmm, not sure. If you build a simple XLS sheet in Excel itself, does it do this automatically?

Comment 108 (In reply to #107) by haysfluid posted on 7/2/2015 at 7:18 PM

Thanks for getting back to me. I was hoping to build off of this example. I have seen other places where they used XML, then opened that XML in Excel, added the repeating header and then compared XML to see what XML code was added. They then added that code to their ColdFusion and I am guessing it worked. I prefer this example, I am not very familiar with XML. That's why I was hoping you or someone else had accomplished this using your example as a base.

I was abled to change the layout from portrait to landscape with some code I found. (POI??) It looks like using this same code will work, but I can't figure it out. Just can't seem to get it to work. Here is the Code for both the Landscape change and the Repeating code that I can't get to work.

__________________________________________________________

<cfset poisheet="s.getWorkBook().getSheet("Schedule")">
<cfset ps="poiSheet.getPrintSetup()">
<cfset ps.setlandscape(true)="">

<cfset ps.setfitheight(1)="">
<cfset ps.setfitwidth(1)="">

<cfset poisheet.setrepeatingrowsandcolumns(int="" sheetindex,="" int="" startcolumn,="" int="" endcolumn,="" int="" startrow,="" int="" endrow)="">

<cfset test="poiSheet.setRepeatingRowsAndColumns(int" sheetindex,="" int="" startcolumn,="" int="" endcolumn,="" int="" startrow,="" int="" endrow)="">

<cfset test.setrepeatingrowsandcolumns(0,="" 0,="" 7,="" 0,="" 0)="">

______________________________________________________

It doesn't like the setRepeatingRowsAndColumns(....) It either says it's not a defined function or tells me it's expecting (sheetIndex) which is there, but has the int in front of it. But if I remove the int from all of the items, it then tells me they are not defined. With just this code added to your code I get this error when trying to load the page.

Invalid CFML construct found on line 578 at column 48.ColdFusion was looking at the following text:

sheetIndex

The CFML compiler was processing:<ul><li>An expression beginning with poiSheet.setRepeatingRowsAndColumns, on line 578, column 8.This message is usually caused by a problem in the expressions structure.<li>A cfset tag beginning on line 578, column 2.<li>A cfset tag beginning on line 578, column 2.</ul>

Comment 109 (In reply to #107) by haysfluid posted on 7/2/2015 at 7:23 PM

Here is the information on the POI that I talked about.
Webpage:
)https://poi.apache.org/apid...

Below is the content that I think will do the trick, just can't figure out how to get it working in my ColdFusion code.
setRepeatingRowsAndColumns

void setRepeatingRowsAndColumns(int sheetIndex,
int startColumn,
int endColumn,
int startRow,
int endRow)

Deprecated. use Sheet.setRepeatingRows(CellRangeAddress) or Sheet.setRepeatingColumns(CellRangeAddress)

Sets the repeating rows and columns for a sheet (as found in File->PageSetup->Sheet). This is function is included in the workbook because it creates/modifies name records which are stored at the workbook level.

To set just repeating columns:

workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
To set just repeating rows:

workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
To remove all repeating rows and columns for a sheet.

workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);

Parameters:sheetIndex - 0 based index to sheet.startColumn - 0 based start of repeating columns.endColumn - 0 based end of repeating columns.startRow - 0 based start of repeating rows.endRow - 0 based end of repeating rows.

Comment 110 (In reply to #108) by Raymond Camden posted on 7/3/2015 at 2:24 PM

Yeah, sorry, I can't really help here. I haven't worked with Poi in years. Best I can do is wish you luck. :)

Comment 111 (In reply to #110) by haysfluid posted on 7/6/2015 at 4:23 PM

Thanks!

Comment 112 (In reply to #91) by dawesi posted on 7/27/2016 at 1:47 AM

For anyone who implemented the HTML table generated excel file, from July 2016 msoffiec will no longer open .xls files created as tables

http://www.infoworld.com/ar...

Comment 113 by Alec posted on 6/26/2017 at 2:09 PM

Hi Ray, your code example is great! thank you! but I can only generate an excel file when the records are below 20K. When I tested it with 20K records coldfusion 10 crashed. The application.log says something about java heap. I did not see any error message but the process just stopped!
Java heap space The specific sequence of files included or processed is: .. pointing to my cfm file.

<cfquery name="q" datasource="#application.Main_DSN#">
select *
from address
where rownum < 20001
</cfquery>
<cfset filename="expandPath("./myexcel.xls")">
<cfspreadsheet action="write" query="q" filename="#filename#" overwrite="true">

Comment 114 (In reply to #113) by Raymond Camden posted on 6/26/2017 at 2:33 PM

I'm guessing maybe it ran out of memory? You can try increasing the max available memory in the settings.

Comment 115 (In reply to #114) by Alec posted on 6/26/2017 at 2:53 PM

Current setting is : Minimum JVM Heap Size (in MB)

Maximum JVM Heap Size (in MB)

Comment 116 (In reply to #115) by Alec posted on 6/26/2017 at 2:54 PM

min is 256 and max is 512

Comment 117 (In reply to #114) by Alec posted on 6/26/2017 at 3:07 PM

Ray, from your example codes, which objects occupy the most portions of heap? I mean, I only have three lines, the query and two cfsets. Is cfspreadsheet writing to excel file the one occupying the largest postion of heap???

Comment 118 (In reply to #117) by Raymond Camden posted on 6/26/2017 at 3:21 PM

No idea. If it doesn't crash w/ just the query, than it's probably cfspreadsheet.