Format Tables (Was RE: ColdFusion to excel)

Thanks for all the input. Now for the next question.  I am having a hard time formatting my html table correctly. I am using <TR><TH>#Skill[1]#</TH> <TH>#Skill[2]#</TH> in the heading (I have also tried making it just a regular <TR><TD></TD></TR>.  But unfortunately it is giving each student a heading. ie:  Reading  Math  Spelling JSmith  A  A  A   Reading  Math  Spelling SJones  B  B  B So I have 2 questions: 1. Is there a way to get the heading to be for the whole page when I am using a variable? 2.  Is there a way to make the #skill[1]# #Skill[2]#, etc. expand/constrict accordingly. (ie: 5th grade Math has 3 skills/ reading has 16 skills whereas 1st grade math has 21 skills and 19 reading skills....) I don't want to have to make a different page for each subject/grade.  If I just put #Skill# in the header it only gives me the 1st skill... Any ideas??? You can create an html table and output that is an excel file as follows: <CFSETTING ENABLECFOUTPUTONLY="YES"> <cfif parameterexists(EmpName)>   <cfinclude template="qry_employees.cfm">   <cfif qry_employees.recordcount gt 0>       <cfset excelstring="<table border=""1""><tr><td><b>First Name</b></td>">       <cfset excelstring=excelstring &"<td><b>Last Name</b></td>">       <cfset excelstring=excelstring &"<td><b>Office Phone</b></td>">       <cfset excelstring=excelstring &"<td><b>Mobile Phone</b></td>">       <cfset excelstring=excelstring &"<td><b>Pager</b></td></tr>">     <cfoutput query="qry_employees">     <cfset excelstring=excelstring &"<tr><td>#empfirstname#</td><td>#emplastname#</td><td>#officep#</td><td>#ce ll#</td><td>#pager#</td></tr>">     </cfoutput>   <cfset excelstring=excelstring &"</table>">   </cfif> </cfif> <CFHEADER NAME="Content-Disposition" VALUE="inline; filename=download.xls"> <cfcontent type="application/vnd.ms-excel"> <cfoutput>#excelstring#</cfoutput> <CFSETTING ENABLECFOUTPUTONLY="No"> This output string that you have built will come across as an excel file. The key is in the cfcontent tag and cfheader tag. John Anderson aloha-webdesign.com How flexible is the process of generating excel files? I need to have an output that is dynamic for the columns and rows. For example:     #student#   #student#   #student#   #student# #Skill#  #grade#  #grade#    #grade#  #grade# #Skill#  #grade#  #grade#  #grade#  #grade# #Skill#  #grade#  #grade#  #grade#  #grade# Translating into:     JohnSmith  JanSmith  SamJones  sandyJones Reading      C       A       A       B Math        B       A       B       A English      B        B       A       C My tables are normalized. There are many tables that make this info. 1.  One reference table that lists all the skills. 2.  One table that shows student information. 3.  One table that lists the studentID/skillID/Grade (hooks the student info with the skills reference table). 4.  One reference table that lists the quarter and dates of the quarter so only the current quarter prints out. Is this possible? What are the steps to making it so?  Do I need to make the information into an array(s) first? I've read the how to from Ben's CF5 book and the tutorial from cfcomet.com- but of course I want to make things more difficult. Thanks in advance. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com

Try this: <TABLE>   <TR>      <TH> </TH><TH>Skill1</TH><TH>Skill2</TH><TH>Skill3</TH>   </TR>   <CFLOOP QUERY = "#YourQuery#">   <TR> <TD>#StudentName#</TD><TD>#ReadingScore#</TD><TD>#MathScore#</TD><TD>Spellin gScore#</TD>   </TR>   </CFLOOP> </TABLE> This should get you started toward what you want.  I don;t have time to right the whole page layout right now, but I hope that this will help you in the right direction. Larry Juncker Senior Cold Fusion Developer Heartland Communications Group, Inc. larry@heartlandinternet.com Thanks for all the input. Now for the next question.  I am having a hard time formatting my html table correctly. I am using <TR><TH>#Skill[1]#</TH> <TH>#Skill[2]#</TH> in the heading (I have also tried making it just a regular <TR><TD></TD></TR>.  But unfortunately it is giving each student a heading. ie:  Reading  Math  Spelling JSmith  A  A  A   Reading  Math  Spelling SJones  B  B  B So I have 2 questions: 1. Is there a way to get the heading to be for the whole page when I am using a variable? 2.  Is there a way to make the #skill[1]# #Skill[2]#, etc. expand/constrict accordingly. (ie: 5th grade Math has 3 skills/ reading has 16 skills whereas 1st grade math has 21 skills and 19 reading skills....) I don't want to have to make a different page for each subject/grade.  If I just put #Skill# in the header it only gives me the 1st skill... Any ideas??? You can create an html table and output that is an excel file as follows: <CFSETTING ENABLECFOUTPUTONLY="YES"> <cfif parameterexists(EmpName)>   <cfinclude template="qry_employees.cfm">   <cfif qry_employees.recordcount gt 0>       <cfset excelstring="<table border=""1""><tr><td><b>First Name</b></td>">       <cfset excelstring=excelstring &"<td><b>Last Name</b></td>">       <cfset excelstring=excelstring &"<td><b>Office Phone</b></td>">       <cfset excelstring=excelstring &"<td><b>Mobile Phone</b></td>">       <cfset excelstring=excelstring &"<td><b>Pager</b></td></tr>">     <cfoutput query="qry_employees">     <cfset excelstring=excelstring &"<tr><td>#empfirstname#</td><td>#emplastname#</td><td>#officep#</td><td>#ce ll#</td><td>#pager#</td></tr>">     </cfoutput>   <cfset excelstring=excelstring &"</table>">   </cfif> </cfif> <CFHEADER NAME="Content-Disposition" VALUE="inline; filename=download.xls"> <cfcontent type="application/vnd.ms-excel"> <cfoutput>#excelstring#</cfoutput> <CFSETTING ENABLECFOUTPUTONLY="No"> This output string that you have built will come across as an excel file. The key is in the cfcontent tag and cfheader tag. John Anderson aloha-webdesign.com How flexible is the process of generating excel files? I need to have an output that is dynamic for the columns and rows. For example:     #student#   #student#   #student#   #student# #Skill#  #grade#  #grade#    #grade#  #grade# #Skill#  #grade#  #grade#  #grade#  #grade# #Skill#  #grade#  #grade#  #grade#  #grade# Translating into:     JohnSmith  JanSmith  SamJones  sandyJones Reading      C       A       A       B Math        B       A       B       A English      B        B       A       C My tables are normalized. There are many tables that make this info. 1.  One reference table that lists all the skills. 2.  One table that shows student information. 3.  One table that lists the studentID/skillID/Grade (hooks the student info with the skills reference table). 4.  One reference table that lists the quarter and dates of the quarter so only the current quarter prints out. Is this possible? What are the steps to making it so?  Do I need to make the information into an array(s) first? I've read the how to from Ben's CF5 book and the tutorial from cfcomet.com- but of course I want to make things more difficult. Thanks in advance. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com

Can you show us the looping code? It's probably an error in reusing too much of the sample code --- Janine Jakim <jjakim@albemarle.org> wrote: ----- Excess quoted text cut - see Original Post for more ----- &"<tr><td>#empfirstname#</td><td>#emplastname#</td><td>#officep#</td><td>#ce ----- Excess quoted text cut - see Original Post for more ----- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm

----- Excess quoted text cut - see Original Post for more ----- &"<tr><> td>#empfirstname#</td><td>#emplastname#</td><td>#officep#</td><td>#ce ----- Excess quoted text cut - see Original Post for more -----

Ok I answered question 2.  I found that using <CFOUTPUT><TH>#Skill#</TH></CFOUTPUT> will go through all the skills so that I don't need to do Skill1, Skill2, Skill3 Now I still have the issue of my headers showing up before each student. This is my code:   <Table>   <CFOUTPUT query="SGrade"group="PERMNUM">     <TR>      <TH> </TH><CFOUTPUT><TH>#Skill#</TH></CFOUTPUT>     </TR>         <TR>           <CFIF Val(SGrade.StudentID)>             <TD>#LASTNAME#, #FIRSTNAME#             </TD></CFIF>           <CFOUTPUT>  <TD>     <CFIF Val(SGrade.Grade)> #Grade#     </CFIF>             </TD></CFOUTPUT>         </TR></TABLE>            Can you show us the looping code? It's probably an error in reusing too much of the sample code --- Janine Jakim <jjakim@albemarle.org> wrote: ----- Excess quoted text cut - see Original Post for more ----- &"<tr><td>#empfirstname#</td><td>#emplastname#</td><td>#officep#</td><td>#ce ----- Excess quoted text cut - see Original Post for more ----- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com

Janine, There seems to be an error in your posted code, as you have 3 <cfoutput>s to 2 </cfoutput>. I'm surprised CF didn't give you an error message. It looks to me like you need to take the <CFOUTPUT><TH>#Skill#</TH></CFOUTPUT> outside so it isn't nested inside <CFOUTPUT query="SGrade"group="PERMNUM"> I'd do something like <Table> <CFOUTPUT query="SGrade"><TH>#Skill#</TH></CFOUTPUT> <CFOUTPUT query="SGrade" group="PERMNUM"> .... Also, there's a place where it reads     </TD></CFOUTPUT>     </TR></TABLE> where the </tr> should go inside of </cfoutput> --- Janine Jakim <jjakim@albemarle.org> wrote: ----- Excess quoted text cut - see Original Post for more ----- &"<tr><td>#empfirstname#</td><td>#emplastname#</td><td>#officep#</td><td>#ce ----- Excess quoted text cut - see Original Post for more ----- ===== I-Lin Kuo Macromedia CF5 Advanced Developer Sun Certified Java 2 Programmer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm