I am using CF9. I've created a multi-sheet workbook. I am attempting to format the columns for each sheet. The formatting is only taking on the first sheet. How can I get it to apply to all sheets? Also I cannot figure out how to get the column width to work for any sheet.
Here's the code that I've got at the moment:
<cfscript>
qExecSummary = queryNew("");
queryAddColumn(qExecSummary, "responsible", [1,12,13]);
queryAddColumn(qExecSummary, "bud_sum", [100,500,1000]);
queryAddColumn(qExecSummary, "Spent_YTD", [10,50,100]);
queryAddColumn(qExecSummary, "Name", ["A","B","C"]);
queryAddColumn(qExecSummary, "Description", ["Descrip1","Descrip2","Descrip3"]);
queryAddColumn(qExecSummary, "Committed", ["Committed1","Committed2","Committed3"]);
//Create new workbook with one sheet
//by default that sheet is the active sheet
Workbook = SpreadsheetNew("ExecSummary");
//Add Data to the sheet
//Formatting
format1.bold="true";
format1.fontsize=12;
format1.font="Calibri";
format2.bold="true";
format2.fontsize=18;
format2.font="Calibri";
formatNum.dataformat="0.0%";
//adding the formating to the cells
//adding the Headers
SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
Spreadsheetformatcell(Workbook,format2,1,1);
SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
Spreadsheetformatcell(Workbook,format1,3,1);
SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
Spreadsheetformatcell(Workbook,format1,5,1);
SpreadSheetSetColumnWidth(Workbook,1,10);
SpreadSheetSetCellValue(Workbook,"Level",5,2);
Spreadsheetformatcell(Workbook,format1,5,2);
SpreadSheetSetColumnWidth(Workbook,2,10);
SpreadSheetSetCellValue(Workbook,"Name",5,3);
Spreadsheetformatcell(Workbook,format1,5,3);
SpreadSheetSetColumnWidth(Workbook,3,17);
SpreadSheetSetCellValue(Workbook,"Description",5,4);
Spreadsheetformatcell(Workbook,format1,5,4);
SpreadSheetSetColumnWidth(Workbook,4,20);
SpreadSheetSetCellValue(Workbook,"Budget",5,5);
Spreadsheetformatcell(Workbook,format1,5,5);
SpreadSheetSetColumnWidth(Workbook,5,15);
SpreadSheetSetCellValue(Workbook,"Commited",5,6);
Spreadsheetformatcell(Workbook,format1,5,6);
SpreadSheetSetColumnWidth(Workbook,6,15);
SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
Spreadsheetformatcell(Workbook,format1,5,7);
SpreadSheetSetColumnWidth(Workbook,7,15);
SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
Spreadsheetformatcell(Workbook,format1,5,8);
SpreadSheetSetColumnWidth(Workbook,8,15);
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 6;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 6 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
SpreadsheetCreateSheet(Workbook,"ExecSummary331-333");
SpreadsheetSetActiveSheet(Workbook,"ExecSummary331-333");
//adding the Headers
SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
Spreadsheetformatcell(Workbook,{bold="true"},1,1);
Spreadsheetformatcell(Workbook,{fontsize=18},1,1);
Spreadsheetformatcell(Workbook,{font="Calibri"},1,1);
SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
Spreadsheetformatcell(Workbook,format1,3,1);
SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
Spreadsheetformatcell(Workbook,format1,5,1);
SpreadSheetSetColumnWidth(Workbook,1,10);
SpreadSheetSetCellValue(Workbook,"Level",5,2);
Spreadsheetformatcell(Workbook,format1,5,2);
SpreadSheetSetColumnWidth(Workbook,2,10);
SpreadSheetSetCellValue(Workbook,"Name",5,3);
Spreadsheetformatcell(Workbook,format1,5,3);
SpreadSheetSetColumnWidth(Workbook,3,17);
SpreadSheetSetCellValue(Workbook,"Description",5,4);
Spreadsheetformatcell(Workbook,format1,5,4);
SpreadSheetSetColumnWidth(Workbook,4,20);
SpreadSheetSetCellValue(Workbook,"Budget",5,5);
Spreadsheetformatcell(Workbook,format1,5,5);
SpreadSheetSetColumnWidth(Workbook,5,15);
SpreadSheetSetCellValue(Workbook,"Commited",5,6);
Spreadsheetformatcell(Workbook,format1,5,6);
SpreadSheetSetColumnWidth(Workbook,6,15);
SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
Spreadsheetformatcell(Workbook,format1,5,7);
SpreadSheetSetColumnWidth(Workbook,7,15);
SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
Spreadsheetformatcell(Workbook,format1,5,8);
SpreadSheetSetColumnWidth(Workbook,8,15);
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 6;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 6 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 18;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-17],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-17],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-17],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-17],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-17],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-17],rowNum,7);
if (qExecSummary.bud_sum[rowNum-17] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-17]/qExecSummary.bud_sum[rowNum-17]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 17 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 29;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-28],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-28],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-28],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-28],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-28],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-28],rowNum,7);
if (qExecSummary.bud_sum[rowNum-28] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-28]/qExecSummary.bud_sum[rowNum-28]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 28 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
SpreadsheetCreateSheet(Workbook,"ExecSummary334-336");
SpreadsheetSetActiveSheet(Workbook,"ExecSummary334-336");
//adding the Headers
SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
Spreadsheetformatcell(Workbook,format2,1,1);
SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
Spreadsheetformatcell(Workbook,format1,3,1);
SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
Spreadsheetformatcell(Workbook,format1,5,1);
SpreadSheetSetColumnWidth(Workbook,1,10);
SpreadSheetSetCellValue(Workbook,"Level",5,2);
Spreadsheetformatcell(Workbook,format1,5,2);
SpreadSheetSetColumnWidth(Workbook,2,10);
SpreadSheetSetCellValue(Workbook,"Name",5,3);
Spreadsheetformatcell(Workbook,format1,5,3);
SpreadSheetSetColumnWidth(Workbook,3,17);
SpreadSheetSetCellValue(Workbook,"Description",5,4);
Spreadsheetformatcell(Workbook,format1,5,4);
SpreadSheetSetColumnWidth(Workbook,4,20);
SpreadSheetSetCellValue(Workbook,"Budget",5,5);
Spreadsheetformatcell(Workbook,format1,5,5);
SpreadSheetSetColumnWidth(Workbook,5,15);
SpreadSheetSetCellValue(Workbook,"Commited",5,6);
Spreadsheetformatcell(Workbook,format1,5,6);
SpreadSheetSetColumnWidth(Workbook,6,15);
SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
Spreadsheetformatcell(Workbook,format1,5,7);
SpreadSheetSetColumnWidth(Workbook,7,15);
SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
Spreadsheetformatcell(Workbook,format1,5,8);
SpreadSheetSetColumnWidth(Workbook,8,15);
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 6;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 6 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 18;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-17],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-17],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-17],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-17],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-17],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-17],rowNum,7);
if (qExecSummary.bud_sum[rowNum-17] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-17]/qExecSummary.bud_sum[rowNum-17]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 17 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
//check to make sure that data was pulled back by the query
if (qExecSummary.recordCount) {
rowNum = 29;
do {//if data is pulled back loop through it and add it to the correct cell
SpreadSheetSetCellValue(Workbook,dateFormat(now(),'mm/dd/yyy'),rowNum,1);
SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-28],rowNum,2);
SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-28],rowNum,3);
SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-28],rowNum,4);
SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-28],rowNum,5);
SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-28],rowNum,6);
SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-28],rowNum,7);
if (qExecSummary.bud_sum[rowNum-28] NEQ 0){
Spreadsheetformatcell(Workbook,formatNum,rowNum,8);//if there is a percentage used then format as a percent
SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-28]/qExecSummary.bud_sum[rowNum-28]),rowNum,8);
}
else {
SpreadSheetSetCellValue(Workbook,0,rowNum,8);
}
rowNum++;
} while (rowNum - 28 LT qExecSummary.recordCount);
} else {
SpreadSheetAddRows(Workbook,"No results for your criteria.");
}
SpreadsheetSetActiveSheet(Workbook,"ExecSummary");
</cfscript>
<cfheader name="Content-Disposition" value='attachment; filename="execSummaryNew.xls"'>
<cfcontent type="application/msexcel" variable="#SpreadsheetReadBinary(Workbook)#" reset="true">
I know that it's really long, but that's how I had to do it because of the DB that I have to work with, I had to use several queries to get the data how I wanted it.
I have tried having the SpreadSheetSetColumnWidth inside the <cfscript> to format the columns, but that didn't work either. Right now the formatting only works on the first sheet except for the width which doesn't work on any sheet.
EDIT
I almost have it all working. Now it's just the formatting. It works for 2.5 sheets. On the 3rd sheet it stops working half way through. There are 8 columns and the last 4 are not taking the formatting. I've tried everything that I can think of to get it to take. I've added it as an example here, I know that it's long, but I cannot reproduce the problem anywhere. I only get it in production. I've copied what I've got in prod both to my computer and to that example I linked above. Both on my local computer and in the example it works fine. But I have CF 2016 and the prod server is CF 9.
I know it's a lot of code to look at, but if someone could help that'd be great. I'm banging my head against the wall trying to see where I messed it up, but for each sheet I copied the sheet before it and then changed the query numbers, so it should be working.
I can update the example posted here as well, but like I said the example that I'm using is long.
FINAL EDIT
Here is the completed code as an example. It generates 3 sheets and the second two sheets run the query 3 times to fill out the page. each sheet has the same headings and formatting.
SpreadSheetSetColumnWidth only operates on the active sheet. So invoking it once, at the very end, is not going to work. That function must be invoked on each sheet, for each column you wish to modify.
As you noticed, the column width can only be changed after you add some data to that column. The reason for this is that the columns (or cells) are not actually created until you apply a value or formula. So if you try and modify their properties, before they even exist, nothing happens. The same rules apply to "formats": the cells must exist before you can apply a format.
Optimizations:
A few tips that will greatly simplify the original code and improve readability:
Since the report will use the same query columns, on every sheet, this is a perfect job for a UDF. Instead of copying over the same code for every sheet, just create a single function that populates an arbitrary sheet name using the supplied query.
function populateSummarySheet( any workbook
, string sheetName
, date reportDate
, query qryData
, boolean createNewSheet ) {
Then invoke the function as many times as you need:
Workbook = SpreadsheetNew("FirstSheet");
populateSummarySheet(Workbook, "FirstSheet", reportDate, query1, false);
populateSummarySheet(Workbook, "SecondSheet", reportDate, query2, true);
populateSummarySheet(Workbook, "ThirdSheet", reportDate, query3, true);
// ....
If you are unfamiliar with functions in CF, be sure to read up on how to properly scope function local variables. A common gotcha is forgetting to scope all of your function local variables, which can often create weird and difficult to reproduce problems down the line.
CF9+ supports shortcuts for structure and array creation. ie
headerFormat = { bold="true", fontsize=18, font="Calibri" };
If you need to format all cells in a particular row or column, it is more efficient to format the row or column, instead of each individual cell. See the documentation on: SpreadSheetFormatRow, SpreadSheetFormatColumn SpreadSheetFormatColumns
Also, Excel limits how many styles you can apply. Formatting individual cells uses up more styles, increasing the likelihood you will exceed the limits: SpreadsheetFormatRow abruptly stops working The limits for the newer .xlsx format are higher than for .xls format. So when possible, it is better to use .xlsx workbooks, instead of .xls.
I have CF 2016 and the prod server is CF 9
Like I mentioned in another thread, using different versions in Dev and Prod is a really bad idea. As it will make it impossible to test your code. If you do a search, you can still find downloads for older versions. For example: Direct download link for ColdFusion 9 Installer (64-bit Windows)