Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In ColdFusion 8, it was fairly trivial to create a very simple Excel spreadsheet. You could generate a plain old HTML table. And then tell cf content to tell the browser it was an Excel spreadsheet. Excel would open it just fine and it would be on your way. But doing anything more advanced than that was difficult and often required third-party components or software installed on the server, and let's forget about trying to read Excel spreadsheets. Cold fusion 9 changes that by giving us deep access to spreadsheets, allowing us to add new worksheets, create, edit and read the results or formulas, format rows and columns, and on and on.
There are over 30 new functions for working with spreadsheets in ColdFusion 9. In this video, we are going to cover reading and writing spreadsheets and just give you a basic introduction to using Excel with ColdFusion. So to get started in Chapter7, let's open excel.cfm. Now, the only thing I have in here is our tried and true query from Chapter 3. I am just pulling out all of the artists and their individual art pieces. And let's go ahead and preview this and see how it looks. I am just querying the database and then dumping everything out.
If I want to send this data to a third party, it would be far easier to provide it in some common format that everybody has. So I am going to dump this out to an Excel spreadsheet. Let's go back to our source code. And I am going to do, cfspreadsheet action = "write", filename = "Artists.xls" and query = "rsArtists". Now let's preview that in Safari.
I don't see any feedback that anything has happened, but if I go back over to my Chapter 7 folder and refresh, I now see that I have an Artist.xls file. I will double click that. It opens up in Excel and I have everything in here with the columns in my query as the headers in each column here, and every individual row has all of my data from my query. And it is just that easy. So that's great for writing Excel spreadsheets, but if I want two way communication in between Excel and Cold fusion, I have a little more work to do.
So let's take our Excel spread sheet and change some data in here. For example, let's assume that Jeff Baclawaski had a fantastic day at the art show and we sold all of his artwork. So, I am going to set everything in here to true. So I will save my Excel spreadsheet. Let's go and close Excel. Go back to our source code here, and now I am going to read my Artist.xls file. The file name to src and the query attribute to name.
And we will put this into NewArtistsData variable. So let's delete our query up at the top, because we don't need that anymore. And then we will dump out NewArtistsData. If I look at that in the browser, we now see that I have a number of rows and my sheet names that are in there. I have the sheet numbers and all of the summery info from the Excel file. So if I had who last edited the file, who the manager was, if there were more than one sheet, if there was the title for the particular Excel spreadsheet.
All that information will be available here in this spreadsheet info that I have dumped out. That's fine if I just want to know about the spreadsheet, but I need to know what data is in there. So if we go back to our source code and change "name" to "query", then the results from my Excel file will actually give me all of the rows as a query. So go back to Safari, and here we have all of our rows, and if we take a look, I have one problem. It's put my headers in Row 1, so if I go back to my source code I can specify header row = 1 and then we will preview that file again.
So now I have the header up here as my column names but it's still returning them all as one of my rows. So to get rid of that, we go back to our source code again and I can specify which rows I wanted to bring in. So I can say rows = "2-1000". So it's going to start at row number 2 and give me everything after row 1. So we will go back to Safari again and there we have all of our records, all of Jeff Baclawaski's artwork shows as being sold, and we have now read and written an Excel spreadsheet.
So now that you know how to read and write spreadsheets, you are ready to start doing some serious exploration about the wonderful new functions for working with spreadsheets. As I said earlier there are over 30 new functions for formatting, performing calculations and slicing and dicing your spreadsheets. There's really no easier way to get data into and out of Excel than using the new ColdFusion 9 spreadsheet tools.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104937 Viewers
56 Video lessons · 116780 Viewers
71 Video lessons · 85989 Viewers
131 Video lessons · 41127 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.