Posted By

cozmotrouble on 04/27/13


Tagged


Versions (?)

Getsheets.cfm


 / Published in: ColdFusion
 

Getsheets.cfm

  1. <cfscript>
  2. intYearID = 5 ;
  3. myfilesuffix = timeformat(now(),"hmm.s.l") ;
  4. myfilesuffix = "" ;
  5. intTotalColumn = 2 ;
  6. intTotalColumnByMonth = 3 ;
  7.  
  8. if (!isdefined("application.ObjMsGearupData") ) {
  9. application.ObjMsGearupData = CreateObject("component", "GetData").init();
  10. } else {
  11. application.ObjMsGearupData = CreateObject("component", "GetData").init();
  12. }
  13.  
  14. da = application.ObjMsGearupData;
  15. GetCoachesNumbersByMonth = da.GetCoachesNumbersByMonth(intYearID);
  16. GetCoachesNumbers = da.GetCoachesNumbers(intYearID);
  17. GetParentsNumbers = da.GetParentsNumbers(intYearID);
  18. GetParentsNumbersByMonth = da.GetParentsNumbersByMonth(intYearID);
  19. GetStudentsNumbers = da.GetStudentsNumbers(intYearID);
  20. GetStudentsNumbersByMonth = da.GetStudentsNumbersByMonth(intYearID);
  21. GetTeachersNumbers = da.GetTeachersNumbers(intYearID);
  22. GetTeachersNumbersByMonth = da.GetTeachersNumbersByMonth(intYearID);
  23. //Use an absolute path for the files. --->
  24. theDir=GetDirectoryFromPath(GetCurrentTemplatePath());
  25. //theFile=theDir & "MSGEARUP_ActLog_1213_2013.03.16_Blank.xls";
  26. theFile=theDir & "MSGEARUP_ActLog2.xls";
  27. theNewFileName ="MSGEARUP_ActLog_#year(now())#_#year(now())#.#month(now())#.#day(now())##myfilesuffix#.xls";
  28.  
  29. theNewFile=theDir & theNewFileName ;
  30. //Create two empty ColdFusion spreadsheet objects. --->
  31.  
  32. </cfscript>
  33.  
  34.  
  35.  
  36. <!--- <cfdump var="#GetParentsNumbersByMonth#" ><cfabort >--->
  37. <cfspreadsheet action="read" src="#theFile#" name="theSpreadshet2" sheetname="ParentbyMonth" headerRow="1" />
  38.  
  39. <cfscript>
  40.  
  41. //Set the sheet as active.
  42. SpreadsheetSetActiveSheet(theSpreadshet2, "CoachesLog");
  43. //Add a new row to the sheet.
  44. SpreadsheetAddRows(theSpreadshet2, GetCoachesNumbers, 2);
  45. da.TotalColumn(GetCoachesNumbers,theSpreadshet2,3);
  46. TotalsRow = da.TotalColumn(GetCoachesNumbers,theSpreadshet2,4);
  47.  
  48. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumn);
  49.  
  50. //writeoutput(test);intTotalColumn intTotalColumnByMonth
  51. //Set the sheet as active.
  52. SpreadsheetSetActiveSheet(theSpreadshet2, "CoachByMonth");
  53.  
  54. //Add a new row to the sheet.
  55. SpreadsheetAddRows(theSpreadshet2, GetCoachesNumbersByMonth, 2);
  56. da.TotalColumn(GetCoachesNumbersByMonth,theSpreadshet2,4);
  57. TotalsRow = da.TotalColumn(GetCoachesNumbersByMonth,theSpreadshet2,5);
  58. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumnByMonth);
  59.  
  60. //Set the sheet as active.
  61. SpreadsheetSetActiveSheet(theSpreadshet2, "ParentLogs");
  62. //Add a new row to the sheet.
  63. SpreadsheetAddRows(theSpreadshet2, GetParentsNumbers, 2);
  64. da.TotalColumn(GetParentsNumbers,theSpreadshet2,3);
  65. TotalsRow = da.TotalColumn(GetParentsNumbers,theSpreadshet2,4);
  66. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumn);
  67.  
  68. //Set the sheet as active.
  69. SpreadsheetSetActiveSheet(theSpreadshet2, "ParentbyMonth");
  70. //Add a new row to the sheet.
  71. SpreadsheetAddRows(theSpreadshet2, GetParentsNumbersByMonth, 2);
  72. da.TotalColumn(GetParentsNumbersByMonth,theSpreadshet2,4);
  73. TotalsRow = da.TotalColumn(GetParentsNumbersByMonth,theSpreadshet2,5);
  74. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumnByMonth);
  75.  
  76. //Set the sheet as active.
  77. SpreadsheetSetActiveSheet(theSpreadshet2, "StudentLogs");
  78. //Add a new row to the sheet.
  79. SpreadsheetAddRows(theSpreadshet2, GetStudentsNumbers, 2);
  80. da.TotalColumn(GetStudentsNumbers,theSpreadshet2,3);
  81. TotalsRow = da.TotalColumn(GetStudentsNumbers,theSpreadshet2,4);
  82. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumn);
  83.  
  84. //Set the sheet as active.
  85. SpreadsheetSetActiveSheet(theSpreadshet2, "StudentByMonth");
  86. //Add a new row to the sheet.
  87. SpreadsheetAddRows(theSpreadshet2, GetStudentsNumbersByMonth, 2);
  88. da.TotalColumn(GetStudentsNumbersByMonth,theSpreadshet2,4);
  89. TotalsRow = da.TotalColumn(GetStudentsNumbersByMonth,theSpreadshet2,5);
  90. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumnByMonth);
  91.  
  92. //Set the sheet as active.
  93. SpreadsheetSetActiveSheet(theSpreadshet2, "TeacherLogs");
  94. //Add a new row to the sheet.
  95. SpreadsheetAddRows(theSpreadshet2, GetTeachersNumbers, 2);
  96. da.TotalColumn(GetTeachersNumbers,theSpreadshet2,3);
  97. TotalsRow = da.TotalColumn(GetTeachersNumbers,theSpreadshet2,4);
  98. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumn);
  99.  
  100. //Set the sheet as active.
  101. SpreadsheetSetActiveSheet(theSpreadshet2, "TeacherByMonth");
  102. //Add a new row to the sheet.
  103. SpreadsheetAddRows(theSpreadshet2, GetTeachersNumbersByMonth, 2);
  104. da.TotalColumn(GetTeachersNumbersByMonth,theSpreadshet2,4);
  105. TotalsRow = da.TotalColumn(GetTeachersNumbersByMonth,theSpreadshet2,5);
  106. da.FormatTotalRow(theSpreadshet2, TotalsRow, intTotalColumnByMonth);
  107.  
  108.  
  109.  
  110. </cfscript>
  111. <!--- <cfabort> --->
  112.  
  113. <!---
  114. SpreadsheetSetCellFormula(spreadsheetObj, formula, row, column)
  115.  
  116.  
  117. NumberOfRowsToTotal = Get_CCNumbers.RecordCount + 1;
  118. mytotalRow = NumberOfRowsToTotal + 1;
  119. myformula = "SUM(D2:D#NumberOfRowsToTotal#)";
  120. myformula2 = "SUM(E2:D#NumberOfRowsToTotal#)";
  121.  
  122. // add the totals row
  123. spreadsheetAddRow(theSheet, "Total, , ");
  124. // Apply the formulas
  125. SpreadsheetSetCellFormula(theSheet, myformula, mytotalRow, 4);
  126. SpreadsheetSetCellFormula(theSheet, myformula2, mytotalRow, 5);
  127. //Format totals row
  128. spreadsheetFormatRow(theSheet, {bold=true, fgcolor="GREY_25_PERCENT"}, mytotalRow); --->
  129. <b>
  130.  
  131.  
  132.  
  133.  
  134.  
  135.  
  136.  
  137.  
  138. </b>
  139.  
  140.  
  141.  
  142.  
  143. <cfspreadsheet action="write" filename="#theNewFile#" name="theSpreadshet2" overwrite=true>
  144. <cfheader name="Content-Disposition" value="inline; filename=#theNewFileName#">
  145. <!---<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=search_results.xls">--->
  146. <cfcontent type="application/msexcel" file="#theNewFile#" deletefile="false">
  147. <cfsetting showdebugoutput="false">

Report this snippet  

You need to login to post a comment.