Sample cfm file

Use the icon to open a screenshot of each step. Note: If these steps do not work, please be sure that you are not using your regular web browser Safari to attempt to download the files instead of the iSilo inbuilt browser. Please follow the above steps again carefully, ensuring that you are in iSilo when doing so.

Note: If these steps do not work, please be sure that you are not using your device's web browser. You need to use iSilo's built in browser. To transfer the sample file to your mobile device using your USB data cable please go to the iSilo FAQ page and under "Transferring Files", find the instructions for your device.

All rights reserved. Reproduction in whole or in part without permission is prohibited. Altair Feko is a comprehensive computational electromagnetics CEM code used widely in the telecommunications, automobile, space and defense industries. Naturally, other applications may also use the. Even harmful programs can create. Next I add the data using spreadsheetAddRows and write out the data.

Here's a quick example of the result. 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. If you like this content, please consider supporting me. You can become a Patron , visit my Amazon wishlist , or buy me a coffee! Any support helps! I like that, good for a basic spreadsheet.

But these spreadsheet tags aren't going to be able to work with a real stylized spreadsheet, are they? It becomes a real pain though if you have to tweak anything, you can real easily mess up the Excel XML.

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 rows and everything worked fine. 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 bit install and have 12GB of memory allocated to CF. I don't think that memory is an issue - could there be something else? 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? 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. ClassCastException: org. WindowOneRecord cannot be cast to org. ExtendedFormatRecord The error occurred on line 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.

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.

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. 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.

Does "dataformat" work at all? 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. 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. Is this for does 9. I haven't had any problems with several thousands of lines using it. Coldfusion 9. The only downside really is that it's not "really" an excel file and I never figured out a way to do multiple sheets. 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, 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.

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.

Also trying to add 's of rows with any of the spreadsheet functions would also cause it to hang. 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. 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:. 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. 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. Opening a csv file into excel works better for interpreting the cells correctly as dates. Here's a quick test that I wrote that allows you to format dates and download the Excel spreadsheet. 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.

I've found a few things relating to cfspreadsheet and ColdFusion 9. In CF9 formatting more than just a few rows and columns would result in an " The solution was to install CF 9. 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.

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? I've used spreadsheetWrite and the result is the same.

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.

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. Since we're only on CF 9. What a bummer. I don't see any evidence that CF9. Dumb question - if your data is query based, can you ensure the column is a date type? The commands may need minor modification depending on the compiler that you are using.

Again, make certain that the analyses are run in a different subdirectory than where the files were uncompressed so that you can check to be sure that the results match. It is important to keep the two analyses separate, as there are many duplicate file names in the directories all of the MTDF files are created in both analyses.