When creating a website that generates reports, having the ability to generate Excel spreadsheets is a great feature to offer users. Allowing people to provide an Excel file of data rather than forcing them to manually enter everything into your system is also a great feature for users. ColdFusion allows for both the creation and reading of spreadsheets.
ColdFusion offers the ability to use tags and functions to manipulate spreadsheets. The cfspreadsheet tag is best used when reading in a spreadsheet; the spreadsheet functions are best used when manipulating the spreadsheet. There is the SpreadsheetRead function available to you, but it does not return the data contained in the spreadsheet. For this section, we will use the cfspreadsheet tag to read in data, but will use the spreadsheet functions for creation and manipulation.
Reading in a spreadsheet is very simple. The first thing is make sure the file is on your server. If this is a user supplied spreadsheet, this can be done by using the file upload techniques discussed in the previous section.
To read in a spreadsheet and assign it to a variable, use the cfspreadsheet tag. The action to use with the tag is 'Read'. You then must specify the location of the XLS or XLSX file by using the src attribute; then, specify a value to store the result in via the query attribute. With those three attributes set, you are able to read in the spreadsheet and access it from the variable name specified in the query attribute. The variable that you will have right now is a query object. If you did not want the data from the spreadsheet, but wanted to manipulate the spreadsheet, you could supply the tag with a name attribute. A variable will be created with the name you provided in the name attribute, which will contain the spreadsheet object that the spreadsheet functions require. For now, let's focus on the query.
Even though the cfspreadsheet tag will run with those three required attributes, the data that you have will probably not be in the best format for use. A prime example of this is that most spreadsheets have a header row that says what is in the column. Without specifying that the file has a header column and that the header column should be used for naming the query columns, you will end up with a query that has the headers as a row and the query columns named COL_1, COL_2 and so on. Specifying a headerrow attribute and the row number that contains the header information in your file will allow the cfspreadsheet tag to name the columns in the query according to the header values of your document. Also, specifying the attribute excludeheaderrow and setting it to true will stop your header row being included in the query data.
Once the cfspreadsheet tag has run and you have the data of the spreadsheet in your query variable, you can then add additional logic to add the data to your website.
Creating a spreadsheet is a simple process. The first thing that needs to be done is to create a spreadsheet object. Just like with the file and image functionality, an object rather than a file path is needed when calling the functionality. To create a spreadsheet object, call the SpreadsheetNew function. Passing in no arguments will create a simple spreadsheet object; optionally, you can pass in a sheet name which will be the name of the first sheet in the spreadsheet. You can also specify if you want the spreadsheet to be in the XML format or not. If the file is in the XML format, you can save it as a .xlsx file, thus making it readable by Excel 2007 or later.
Once you have the spreadsheet object, the first thing you most likely will want to do is add a header row to the file. Adding a header row uses the same functionality you would use if you were just adding a single row to the file. The function you will call is SpreadsheetAddRow. This function accepts the spreadsheet object and a comma delimited list of data. This row of data will be added to the last row of data in the file.
If you are adding many rows of data to your spreadsheet and they are in a query or array object, you can use the function SpreadsheetAddRows. Using this function rather than looping over your data and calling SpreadSheetAddRow is much more desirable; it is less code and faster. The SpreadsheetAddRows function accepts the spreadsheet object and the query or array object. All rows will be added after the last row of data in the spreadsheet. It is possible to change where the rows are inserted and if they overwrite the data or not by supplying some additional attributes. For further information on the subject, you can go to: http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6790.html.
Once the data is in your spreadsheet object, you may want to format the look and feel of the data a bit. The most common formatting done with a spreadsheet is to bold the header row so that it stands out. Formatting data is an easy task when using the SpreadsheetFormatRow function. This function accepts the spreadsheet object, a struct containing the formatting data, and the row that the formatting should be applied to.
There are a number of different styles that can be specified in the formatting struct that is passed to the function. This is not a complete list of the formats available, but it should give you an idea of the amount of control you have when formatting your spreadsheet.
- font
- fontsize
- italic
- bold
- alignment
- textwrap
- bgcolor
- bottomborder
- bottombordercolor
- topborder
- topbordercolor
- leftborder
- leftbordercolor
- rightborder
- rightbordercolor
When exporting data, sometimes the data you are outputting needs to be separated. When dealing with spreadsheets, this is done by creating separate sheets in the spreadsheet file. To create a new sheet in your spreadsheet, you would use the SpreadsheetCreateSheet function. This function allows you to pass in the spreadsheet object and, optionally, the name to use for the sheet. Once this sheet is created, you will need to switch to the sheet before entering data. To do this, call the SpreadsheetSetActiveSheet function. This function accepts the spreadsheet object and the sheet name of the sheet you wish to set as the active sheet. Once this call is made, all updates will now occur on that sheet. As this function requires you provide the name of the sheet, and not the sheet position, it is recommended that you always name your sheets.
Once you have added all the data to your spreadsheet and made all the necessary style and formatting updates, it will be necessary to write the spreadsheet to the file system. To do this, call the SpreadsheetWrite function.The spreadsheetWrite function accepts the spreadsheet object as well as the location of where the spreadsheet should be written. When specifying the path, remember to include the file name.
SpreadsheetWrite also has some optional arguments that may be useful. In addition to being able to specify if the function should overwrite a pre-existing file if there already is one, you can also specify a password. This is a very useful feature when dealing with sensitive information.
Below is an example of all the spreadsheet functionality discussed in this section. In this example, we create a spreadsheet, add some data to it, format the data, and then repeat this for a second sheet. Once completed, we then write the file to the file system.
//Create Spreadsheet
spreadsheetObj = SpreadsheetNew('Names');
//Add Header Row
SpreadSheetAddRow(spreadsheetObj,'ID,Name');
//Add Data
SpreadSheetAddRow(spreadsheetObj,'1,Simon');
SpreadSheetAddRow(spreadsheetObj,'2,Carl');
//Format Header
SpreadsheetformatRow(spreadsheetobj,{bold=true,alignment='center'},1);
//Add Sheet
SpreadSheetcreateSheet(spreadsheetobj,'Towns');
//Switch to Names Sheet
SpreadsheetSetActiveSheet(spreadsheetobj,'Towns');
//Add Header Row
SpreadSheetAddRow(spreadsheetObj,'ID,Town');
//Add Data
SpreadSheetAddRow(spreadsheetObj,'1,Detroit');
SpreadSheetAddRow(spreadsheetObj,'2,Sheffield');
//Format Header
SpreadsheetformatRow(spreadsheetobj,{bold=true,alignment='center'},1);
//Write File
Spreadsheetwrite(spreadsheetobj,expandpath('myData.xls'),true);
//Create Spreadsheet
spreadsheetObj = SpreadsheetNew('Names');
//Add Header Row
SpreadSheetAddRow(spreadsheetObj,'ID,Name');
//Add Data
SpreadSheetAddRow(spreadsheetObj,'1,Simon');
SpreadSheetAddRow(spreadsheetObj,'2,Carl');
//Format Header
SpreadsheetformatRow(spreadsheetobj,{bold=true,alignment='center'},1);
//Add Sheet
SpreadSheetcreateSheet(spreadsheetobj,'Towns');
//Switch to Names Sheet
SpreadsheetSetActiveSheet(spreadsheetobj,'Towns');
//Add Header Row
SpreadSheetAddRow(spreadsheetObj,'ID,Town');
//Add Data
SpreadSheetAddRow(spreadsheetObj,'1,Detroit');
SpreadSheetAddRow(spreadsheetObj,'2,Sheffield');
//Format Header
SpreadsheetformatRow(spreadsheetobj,{bold=true,alignment='center'},1);
//Write File
Spreadsheetwrite(spreadsheetobj,expandpath('myData.xls'),true);