How to use ASP to format excel spreadsheet columns

Hi all,

I have this code that I need to know how to modify an excel spreadsheet before its sent out to the web browser client. The code below will be my example, in this issue I need to be able to turn on auto-format & freeze-panes on and format E and H as money i.e. 26.5 to $26.50.

Thanks again

Code:
<%
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.ConnectionTimeout = 0
Connection.CommandTimeout = 0  
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
Response.ContentType = "application/vnd.ms-excel"

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof  
Response.write "<tr>" & "</TD> "
Response.write "<TD ALIGN=left><B>" & showCell(Recordset("docgamingdate")) & "</B></TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("player_id")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("firstname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("lastname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("ForexAmount")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("units")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("Rate")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("USDamount")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("username")) & "</TD> "
Response.write "</tr>"
Recordset.MoveNext    
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Session.Timeout=1
%>
LVL 1
smyers051972Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rdivilbissCommented:
I would suggest you create the Excel spreadsheet manually using dummy values.

Save the resulting file as HTML.  It will contain all the necessary formatting in MS Office's bloated CSS styles, such as:

[snip]
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
x\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:LastAuthor>Anonymous</o:LastAuthor>
  <o:LastPrinted>2007-04-16T00:00:01Z</o:LastPrinted>
  <o:Created>2007-04-16T00:00:01Z</o:Created>
  <o:LastSaved>2007-04-16T00:00:01Z</o:LastSaved>
  <o:Company>Some Company</o:Company>
  <o:Version>11.0000</o:Version>
 </o:DocumentProperties>
 <o:OfficeDocumentSettings>
[snip]

Copy the file to your server, rename it with an ASP extension.  Add the code necessary to get your data from the database, the insert the values into the template as needed.  When someone browses to the page, the ASP will execute and then the page will show a Excel file download.  The spreadsheet will open in Excel or Excel viewer, with your data in the correct location and the formatting set in the original template file.

Works well. Downside, the Excel HTML template is bloated in size and it can be hard to find the spots where you need to insert your values from the database (simply because of the abundance of inline mso style info.)

Regards,
Rod
0
R_HarrisonCommented:
Hopefully the below will work straight out of the box - however there where a couple of points I was unsure of so I have added some comments to the code to help you correct it if necessary.
<%
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.ConnectionTimeout = 0
Connection.CommandTimeout = 0  
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
Response.ContentType = "application/vnd.ms-excel"

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
' we need to set some class styles for the Excel sheet
' the <x:SplitHorizontal>1</x:SplitHorizontal> - determines the horizontal freeze point
' the <x:SplitVertical>5</x:SplitVertical> - determines the veritical freeze point
' so adjust these as necessary for the correct freeze point
%>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=FrontPage.Editor.Document>
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:Author>Your Name</o:Author>
  <o:Version>10.2625</o:Version>
 </o:DocumentProperties>
</xml><![endif]-->

<style>
<!--table
	{mso-displayed-decimal-separator:"\.";
	mso-displayed-thousand-separator:"\,";}
.xl24
	{mso-style-parent:style0;
	mso-number-format:"\[$$-409\]\#\,\#\#0\.00";}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:Selected/>
     <x:FreezePanes/>
     <x:FrozenNoSplit/>
     <x:SplitHorizontal>1</x:SplitHorizontal>
     <x:TopRowBottomPane>1</x:TopRowBottomPane>
     <x:SplitVertical>5</x:SplitVertical>
     <x:LeftColumnRightPane>4</x:LeftColumnRightPane>
     <x:ActivePane>0</x:ActivePane>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>


'if there are records then loop through the fields
response.write("<table x:str>")
Do While NOT Recordset.Eof   
Response.write "<tr>" & "</TD> "
Response.write "<TD ALIGN=left><B>" & showCell(Recordset("docgamingdate")) & "</B></TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("player_id")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("firstname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("lastname")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("ForexAmount")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("units")) & "</TD> "
Response.write "<TD ALIGN=left>" & showCell(Recordset("Rate")) & "</TD> "
Response.write "<td class=xl24 align=left x:num=""" & Recordset("USDamount") & """>$" & Recordset("USDamount") & "</td>"
'  as I don't know you database the above is a guess but it
'  should generate html below - i have assumed 
'  RecordSet("USDamount") will generate a number such as 25.6
'  <td class=xl24 align=left x:num="25.6">$25.60</td>"
Response.write "<TD ALIGN=left>" & showCell(Recordset("username")) & "</TD> "
Response.write "</tr>"
Recordset.MoveNext    
Loop
response.write("</table>")
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Session.Timeout=1
%>

Open in new window

0
R_HarrisonCommented:
Sorry, I also forgot to convert column E, hopefully you can figure it out, it should be something like the below for line 82

 
Response.write "<td class=xl24 align=left x:num=""" & Recordset("ForexAmount") & """>$" & Recordset("ForexAmount") & "</td>"

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

smyers051972Author Commented:
Ok here is what I have now... the code revised is quite long and here is my new problem...

I am trying to display whole numbers with out the cents to it i.e. 45.17 = 45, so in doing so I noticed something, I dropped the sum of column E because it could never be accurate as there are many possible exchange rates and its all mixed so I am sticking to summing the total of all USD taken and only on column H. The code I have worked except the column he shows the whole dollar but when you select an entry on one of the rows you will see the 45.17 for example on the top, however in excel its showing the whole dollar, to sum this up, the bottom sum's it but sums it for every thing cents and all, when you highlight the column the sum at the bottom is more than what the formula got. Sounds weird but here is my attempt to make it work, also I am getting an adjecent cell warning but I dont see where its coming from its summing H2:H& Counter i.e. H2:H108 no other columns... I also included a couple screen shots...

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN"
"http://www.w3.org/TR/html4/frameset.dtd">
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<TITLE>ForEx Report</TITLE>

<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="Forex_Jade_01122010%202%20_files/filelist.xml">
<link rel=Edit-Time-Data href="Forex_Jade_01122010%202%20_files/editdata.mso">
<link rel=OLE-Object-Data href="Forex_Jade_01122010%202%20_files/oledata.mso">
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:LastAuthor>User</o:LastAuthor>
  <o:Created>2010-01-13T14:58:59Z</o:Created>
  <o:LastSaved>2010-01-13T14:58:59Z</o:LastSaved>
  <o:Version>11.9999</o:Version>
 </o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
	{mso-displayed-decimal-separator:"\.";
	mso-displayed-thousand-separator:"\,";}
@page
	{margin:1.0in .75in 1.0in .75in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;}
tr
	{mso-height-source:auto;}
col
	{mso-width-source:auto;}
br
	{mso-data-placement:same-cell;}
.style0
	{mso-number-format:General;
	text-align:general;
	vertical-align:bottom;
	white-space:nowrap;
	mso-rotate:0;
	mso-background-source:auto;
	mso-pattern:auto;
	color:windowtext;
	font-size:10.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:Arial;
	mso-generic-font-family:auto;
	mso-font-charset:0;
	border:none;
	mso-protection:locked visible;
	mso-style-name:Normal;
	mso-style-id:0;}
td
	{mso-style-parent:style0;
	padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:windowtext;
	font-size:10.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:Arial;
	mso-generic-font-family:auto;
	mso-font-charset:0;
	mso-number-format:General;
	text-align:general;
	vertical-align:bottom;
	border:none;
	mso-background-source:auto;
	mso-pattern:auto;
	mso-protection:locked visible;
	white-space:nowrap;
	mso-rotate:0;}
.xl24
	{mso-style-parent:style0;
	font-weight:700;
	text-align:left;
	vertical-align:middle;
	border:.5pt solid black;
	white-space:normal;}
.xl25
	{mso-style-parent:style0;
	font-weight:700;
	mso-number-format:"Short Date";
	text-align:left;
	border:.5pt solid black;
	white-space:normal;}
.xl26
	{mso-style-parent:style0;
	text-align:left;
	border:.5pt solid black;
	white-space:normal;}
.xl27
	{mso-style-parent:style0;
	text-align:right;
	border:.5pt solid black;
	white-space:normal;}
.xl28
	{mso-style-parent:style0;
	font-weight:700;
	mso-number-format:"\0022$\0022\#\,\#\#0";
	text-align:left;
	vertical-align:middle;
	border:.5pt solid black;
	white-space:normal;}
.xl29
	{mso-style-parent:style0;
	mso-number-format:"\0022$\0022\#\,\#\#0";
	text-align:right;
	border:.5pt solid black;
	white-space:normal;}
.xl30
	{mso-style-parent:style0;
	mso-number-format:"\0022$\0022\#\,\#\#0";}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Forex_Jade_01122010 2 </x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>300</x:DefaultRowHeight>
     <x:Unsynced/>
     <x:Selected/>
     <x:DoNotDisplayGridlines/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>1</x:ActiveRow>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>12660</x:WindowHeight>
  <x:WindowWidth>19020</x:WindowWidth>
  <x:WindowTopX>120</x:WindowTopX>
  <x:WindowTopY>120</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 width=849 style='border-collapse:
 collapse;table-layout:fixed;width:639pt'>
 <col width=90 style='mso-width-source:userset;mso-width-alt:3291;width:68pt'>
 <col width=65 style='mso-width-source:userset;mso-width-alt:2377;width:49pt'>
 <col width=165 style='mso-width-source:userset;mso-width-alt:6034;width:124pt'>
 <col width=146 style='mso-width-source:userset;mso-width-alt:5339;width:110pt'>
 <col class=xl30 width=98 style='mso-width-source:userset;mso-width-alt:3584;
 width:74pt'>
 <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'>
 <col width=98 style='mso-width-source:userset;mso-width-alt:3584;width:74pt'>
 <col class=xl30 width=87 style='mso-width-source:userset;mso-width-alt:3181;
 width:65pt'>
 <col width=0 style='display:none;mso-width-source:userset;mso-width-alt:0'>
 <tr height=20 style='mso-height-source:userset;height:15.0pt'>
 <td height=20 class=xl24 width=90 style='height:15.0pt;width:68pt'>Gaming
  Date</td>
  <td class=xl24 width=65 style='border-left:none;width:49pt'>Player ID</td>
  <td class=xl24 width=165 style='border-left:none;width:124pt'>Player
  FirstName</td>
  <td class=xl24 width=146 style='border-left:none;width:110pt'>Player LastName</td>
  <td class=xl28 width=98 style='border-left:none;width:74pt'>ForEx Amount</td>
  <td class=xl24 width=100 style='border-left:none;width:75pt'>Currency Units</td>
  <td class=xl24 width=98 style='border-left:none;width:74pt'>Currency Rate</td>
  <td class=xl28 width=87 style='border-left:none;width:65pt'>USD Amount</td>
  <td class=xl24 width=0 style='border-left:none'>User ID</td>
 </tr>
 
<%
mm = DatePart("m", Date()) 
If Len(mm) = 1 THEN mm = "0" & mm
filename = "Forex_" & ship & "_" & (mm) & DatePart("d", Date()) & DatePart("yyyy", Date()) '& "_" & DatePart("h", Date()) & DatePart("n", Date())
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.ConnectionTimeout = 0
Connection.CommandTimeout = 0  
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=" & filename & ".xls"

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
'if there are records then loop through the fields
Counter=0
Do While NOT Recordset.Eof  
Counter=Counter+1
Response.write "<tr height=20 style='mso-height-source:userset;height:15.0pt'>" & "</TD> "
Response.write "<td height=20 class=xl25 width=90 style='height:15.0pt;border-top:none;  width:68pt' x:num="""">"& showCell(Recordset("docgamingdate")) &"</B></td>"
Response.write "<td class=xl26 width=65 style='border-top:none;border-left:none;width:49pt' x:num>" & showCell(Recordset("player_id")) & "</td>"
Response.write "<td class=xl26 width=165 style='border-top:none;border-left:none;width:124pt'>" & showCell(Recordset("firstname")) & "</td>"
Response.write "<td class=xl26 width=146 style='border-top:none;border-left:none;width:110pt'>" & showCell(Recordset("lastname")) & "</td>"
Response.write "<td class=xl29 width=98 style='border-top:none;border-left:none;width:74pt' x:num=" & showCell(Recordset("ForexAmount")) & "></td>"
Response.write "<td class=xl27 width=100 style='border-top:none;border-left:none;width:75pt'>" & showCell(Recordset("units")) & "</td>"
Response.write "<td class=xl27 width=98 style='border-top:none;border-left:none;width:74pt' x:num=" & showCell(Recordset("Rate")) & "></td>"
Response.write "<td class=xl29 width=87 style='border-top:none;border-left:none;width:65pt' x:num=" & showCell(Recordset("USDamount")) & "></td>"
Response.write "<td class=xl27 width=0 style='border-top:none;border-left:none'>" & showCell(Recordset("username")) & "</td>"
Response.write "</tr>"
Recordset.MoveNext    
Loop
response.write "<tr height=17 style='height:12.75pt'>"
response.write "<td height=17 style='height:12.75pt'>Totals: " & Counter & "</td>"
response.write "<td colspan=6 style='mso-ignore:colspan'></td>"
response.write "<td class=xl32 width=87 style='border-top:none;width:65pt' x:num=""21885.42"" x:fmla=""=SUM(H2:H"& Counter & ")"">$21,885</td>"
response.write "<td></td>"
response.write "</tr>"



'response.write "<TR>"
'response.write "<TD ALIGN=left>Totals: " & Counter & "</td>"
'response.write "  <td colspan=6 style='mso-ignore:colspan'></td>"
'response.write "<td class=xl32 width=87 style='border-top:none;width:65pt' x:num=" & Counter & " x:fmla=""=SUM(H2:H"& Counter & ")""></td>"
'response.write "<td align=right class=xl24 x:num="""" x:fmla=""=SUM(H2:H"& Counter & ")""></td>"
'response.write "<td class=xl24 align=right x:num="" x:fmla="">"=SUM(H2:H"& Counter &")"</td>"
'response.write "<TD width=0 ALIGN=left></td>"
'response.write "</tr></table>"
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Session.Timeout=1
%>

Open in new window

image001.jpg
image002.jpg
0
smyers051972Author Commented:
I had problems with the code in the beginning so you can ignored the commented out portions in the code.. I should of deleted that before posting actually.
0
smyers051972Author Commented:
I also noticed the username is cut off the report on the last right column...
0
R_HarrisonCommented:
OK, the below should do it.  The Username is a hidden column in excel, if you no longer want it to be a hidden column replace line 219 with

Response.write "<td class=xl27 style='border-top:none;border-left:none'>" & showCell(Recordset("username")) & "</td>"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN"
"http://www.w3.org/TR/html4/frameset.dtd">
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<TITLE>ForEx Report</TITLE>

<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="Forex_Jade_01122010%202%20_files/filelist.xml">
<link rel=Edit-Time-Data href="Forex_Jade_01122010%202%20_files/editdata.mso">
<link rel=OLE-Object-Data href="Forex_Jade_01122010%202%20_files/oledata.mso">
<!--[if gte mso 9]><xml>
 <o:DocumentProperties>
  <o:LastAuthor>User</o:LastAuthor>
  <o:Created>2010-01-13T14:58:59Z</o:Created>
  <o:LastSaved>2010-01-13T14:58:59Z</o:LastSaved>
  <o:Version>11.9999</o:Version>
 </o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
	{mso-displayed-decimal-separator:"\.";
	mso-displayed-thousand-separator:"\,";}
@page
	{margin:1.0in .75in 1.0in .75in;
	mso-header-margin:.5in;
	mso-footer-margin:.5in;}
tr
	{mso-height-source:auto;}
col
	{mso-width-source:auto;}
br
	{mso-data-placement:same-cell;}
.style0
	{mso-number-format:General;
	text-align:general;
	vertical-align:bottom;
	white-space:nowrap;
	mso-rotate:0;
	mso-background-source:auto;
	mso-pattern:auto;
	color:windowtext;
	font-size:10.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:Arial;
	mso-generic-font-family:auto;
	mso-font-charset:0;
	border:none;
	mso-protection:locked visible;
	mso-style-name:Normal;
	mso-style-id:0;}
td
	{mso-style-parent:style0;
	padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:windowtext;
	font-size:10.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:Arial;
	mso-generic-font-family:auto;
	mso-font-charset:0;
	mso-number-format:General;
	text-align:general;
	vertical-align:bottom;
	border:none;
	mso-background-source:auto;
	mso-pattern:auto;
	mso-protection:locked visible;
	white-space:nowrap;
	mso-rotate:0;}
.xl24
	{mso-style-parent:style0;
	font-weight:700;
	text-align:left;
	vertical-align:middle;
	border:.5pt solid black;
	white-space:normal;}
.xl25
	{mso-style-parent:style0;
	font-weight:700;
	mso-number-format:"Short Date";
	text-align:left;
	border:.5pt solid black;
	white-space:normal;}
.xl26
	{mso-style-parent:style0;
	text-align:left;
	border:.5pt solid black;
	white-space:normal;}
.xl27
	{mso-style-parent:style0;
	text-align:right;
	border:.5pt solid black;
	white-space:normal;}
.xl28
	{mso-style-parent:style0;
	font-weight:700;
	mso-number-format:"\0022$\0022\#\,\#\#0";
	text-align:left;
	vertical-align:middle;
	border:.5pt solid black;
	white-space:normal;}
.xl29
	{mso-style-parent:style0;
	mso-number-format:"\0022$\0022\#\,\#\#0";
	text-align:right;
	border:.5pt solid black;
	white-space:normal;}
.xl30
	{mso-style-parent:style0;
	mso-number-format:"\0022$\0022\#\,\#\#0";}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Forex_Jade_01122010 2 </x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>300</x:DefaultRowHeight>
     <x:Unsynced/>
     <x:Selected/>
     <x:DoNotDisplayGridlines/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>1</x:ActiveRow>
      </x:Pane>
     </x:Panes>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
  <x:WindowHeight>12660</x:WindowHeight>
  <x:WindowWidth>19020</x:WindowWidth>
  <x:WindowTopX>120</x:WindowTopX>
  <x:WindowTopY>120</x:WindowTopY>
  <x:ProtectStructure>False</x:ProtectStructure>
  <x:ProtectWindows>False</x:ProtectWindows>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 width=849 style='border-collapse:
 collapse;table-layout:fixed;width:639pt'>
 <col width=90 style='mso-width-source:userset;mso-width-alt:3291;width:68pt'>
 <col width=65 style='mso-width-source:userset;mso-width-alt:2377;width:49pt'>
 <col width=165 style='mso-width-source:userset;mso-width-alt:6034;width:124pt'>
 <col width=146 style='mso-width-source:userset;mso-width-alt:5339;width:110pt'>
 <col class=xl30 width=98 style='mso-width-source:userset;mso-width-alt:3584;
 width:74pt'>
 <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'>
 <col width=98 style='mso-width-source:userset;mso-width-alt:3584;width:74pt'>
 <col class=xl30 width=87 style='mso-width-source:userset;mso-width-alt:3181;
 width:65pt'>
 <col width=0 style='display:none;mso-width-source:userset;mso-width-alt:0'>
 <tr height=20 style='mso-height-source:userset;height:15.0pt'>
 <td height=20 class=xl24 width=90 style='height:15.0pt;width:68pt'>Gaming
  Date</td>
  <td class=xl24 width=65 style='border-left:none;width:49pt'>Player ID</td>
  <td class=xl24 width=165 style='border-left:none;width:124pt'>Player
  FirstName</td>
  <td class=xl24 width=146 style='border-left:none;width:110pt'>Player LastName</td>
  <td class=xl28 width=98 style='border-left:none;width:74pt'>ForEx Amount</td>
  <td class=xl24 width=100 style='border-left:none;width:75pt'>Currency Units</td>
  <td class=xl24 width=98 style='border-left:none;width:74pt'>Currency Rate</td>
  <td class=xl28 width=87 style='border-left:none;width:65pt'>USD Amount</td>
  <td class=xl24 width=0 style='border-left:none'>User ID</td>
 </tr>
 
<%
mm = DatePart("m", Date()) 
If Len(mm) = 1 THEN mm = "0" & mm
filename = "Forex_" & ship & "_" & (mm) & DatePart("d", Date()) & DatePart("yyyy", Date()) '& "_" & DatePart("h", Date()) & DatePart("n", Date())
'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
Connection.ConnectionTimeout = 0
Connection.CommandTimeout = 0  
Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=" & filename & ".xls"

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
'if there are records then loop through the fields
Counter=0
Do While NOT Recordset.Eof  
Counter=Counter+1
Response.write "<tr height=20 style='mso-height-source:userset;height:15.0pt'>" & "</TD> "
Response.write "<td height=20 class=xl25 width=90 style='height:15.0pt;border-top:none;  width:68pt' x:num="""">"& showCell(Recordset("docgamingdate")) &"</B></td>"
Response.write "<td class=xl26 width=65 style='border-top:none;border-left:none;width:49pt' x:num>" & showCell(Recordset("player_id")) & "</td>"
Response.write "<td class=xl26 width=165 style='border-top:none;border-left:none;width:124pt'>" & showCell(Recordset("firstname")) & "</td>"
Response.write "<td class=xl26 width=146 style='border-top:none;border-left:none;width:110pt'>" & showCell(Recordset("lastname")) & "</td>"
Response.write "<td class=xl29 width=98 style='border-top:none;border-left:none;width:74pt' x:num=" & showCell(Recordset("ForexAmount")) & "></td>"
Response.write "<td class=xl27 width=100 style='border-top:none;border-left:none;width:75pt'>" & showCell(Recordset("units")) & "</td>"
Response.write "<td class=xl27 width=98 style='border-top:none;border-left:none;width:74pt' x:num=" & showCell(Recordset("Rate")) & "></td>"
Response.write "<td class=xl29 width=87 style='border-top:none;border-left:none;width:65pt' x:num=" & showCell(int(Recordset("USDamount"))) & "></td>"
Response.write "<td class=xl27 width=0 style='border-top:none;border-left:none'>" & showCell(Recordset("username")) & "</td>"
Response.write "</tr>"
Recordset.MoveNext    
Loop
response.write "<tr height=17 style='height:12.75pt'>"
response.write "<td height=17 style='height:12.75pt'>Totals: " & Counter & "</td>"
response.write "<td colspan=6 style='mso-ignore:colspan'></td>"
response.write "<td class=xl32 width=87 style='border-top:none;width:65pt' x:num=""21885.42"" x:fmla=""=SUM(H2:H"& Counter & ")"">$21,885</td>"
response.write "<td></td>"
response.write "</tr>"



'response.write "<TR>"
'response.write "<TD ALIGN=left>Totals: " & Counter & "</td>"
'response.write "  <td colspan=6 style='mso-ignore:colspan'></td>"
'response.write "<td class=xl32 width=87 style='border-top:none;width:65pt' x:num=" & Counter & " x:fmla=""=SUM(H2:H"& Counter & ")""></td>"
'response.write "<td align=right class=xl24 x:num="""" x:fmla=""=SUM(H2:H"& Counter & ")""></td>"
'response.write "<td class=xl24 align=right x:num="" x:fmla="">"=SUM(H2:H"& Counter &")"</td>"
'response.write "<TD width=0 ALIGN=left></td>"
'response.write "</tr></table>"
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Session.Timeout=1
%>

Open in new window

0
smyers051972Author Commented:
that didnt work out, I had to revert to the code I posted above and am with out the user name shown on the spread sheet, which I cant seem to figure out. The above you posted gave me errors that the file could not be located and had to click ok about 5 times. Could we visit the code I posted yesterday? Its close, the bottom # isnt summed correctly though and the user name is missing.

Thanks!
0
R_HarrisonCommented:
Errr, that was the code you posted above.... I only modified line 218 so that it added the dollar amount as an Int.   Is there always a numeric value for USDamount?   If not then that will be the problem and we will need to check if USDamount is numeric before trying to get the Int value.

Do you mean username is hidden or not actually there?   As I mentioned line 219 set the TD width to 0 - Excel interpretes this as "Hidden" - if you want to unhide simply remove the width=0.

We will get there soon :-)
0
smyers051972Author Commented:
Actually I figured out something, I made an entry in my sql to sum the total result there and did a select distinct on that sum and used a variable in asp, the displayed the variable in the report. Worked good. But I need to figure out why the username is missing on the report?
0
R_HarrisonCommented:
On the screen shots it show that column I (which contains the username) is hidden - i.e you can see columns A,B,C,D,E,F,G,H and then J - is this what you mean???  Or when you "unhide" the column in Excel do the usernames not show up?
0
smyers051972Author Commented:
ok i see that, it is hiding the columns... I thought it was simply omitted some how.
0
smyers051972Author Commented:
Alright, so I found width=o on both lines 179 & 219 changed them but column I is still hidden for some reason, can you see anything else I should change?
0
R_HarrisonCommented:
Did you change both width settings???

<td class=xl24 width=87 style='border-left:none;width:65pt'>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smyers051972Author Commented:
I have this in the headers:

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=1240 style='border-collapse:
 collapse;table-layout:fixed;width:932pt'>
 <col width=98 style='mso-width-source:userset;mso-width-alt:3584;width:74pt'>
 <col width=82 style='mso-width-source:userset;mso-width-alt:2998;width:62pt'>
 <col width=287 style='mso-width-source:userset;mso-width-alt:10496;width:215pt'>
 <col width=154 style='mso-width-source:userset;mso-width-alt:5632;width:116pt'>
 <col width=121 style='mso-width-source:userset;mso-width-alt:4425;width:91pt'>
 <col width=81 style='mso-width-source:userset;mso-width-alt:2962;width:61pt'>
 <col width=106 style='mso-width-source:userset;mso-width-alt:3876;width:80pt'>
 <col width=152 style='mso-width-source:userset;mso-width-alt:5558;width:114pt'>
 <col width=95 style='mso-width-source:userset;mso-width-alt:3474;width:71pt'>
 <col width=64 style='width:48pt'>
 <tr class=xl25 height=20 style='mso-height-source:userset;height:15.0pt'>
  <td height=20 class=xl25 width=98 style='height:15.0pt;width:74pt'>SourceCode_ID</td>
  <td class=xl25 width=82 style='width:62pt'>Source Code</td>
  <td class=xl25 width=287 style='width:215pt'>Company Name</td>
  <td class=xl25 width=154 style='width:116pt'>Contact First Name</td>
  <td class=xl25 width=121 style='width:91pt'>Contact Last Name</td>
  <td class=xl25 width=81 style='width:61pt'>NCL Contact</td>
  <td class=xl25 width=106 style='width:80pt'>NCL Cost Center</td>
  <td class=xl25 width=152 style='width:114pt'>Date Created</td>
  <td class=xl25 colspan=20 width=159 style='mso-ignore:colspan;width:119pt'>Active Account</td>
 </tr>
<tr height=20 style='mso-height-source:userset;height:15.0pt'>

and in the sql body I have this:

If Recordset.EOF Then
Response.Write("No records located.<br><br>")
Response.Write(Time)
Response.Write("<br><br>")
Else
'if there are records then loop through the fields
Counter=0
Do While NOT Recordset.Eof  
Counter=Counter+1
TTLUSDAmount=Recordset("TTLUSDAmount")
Response.write "<tr height=20 style='mso-height-source:userset;height:15.0pt'>" & "</TD> "
Response.write "<td height=20 class=xl25 width=90 style='height:15.0pt;border-top:none;  width:68pt' x:num="""">"& showCell(Recordset("Source_ID")) &"</B></td>"
Response.write "<td class=xl26 width=65 style='border-top:none;border-left:none;width:49pt' x:num>" & showCell(Recordset("SourceCode")) & "</td>"
Response.write "<td class=xl26 width=165 style='border-top:none;border-left:none;width:124pt'>" & showCell(Recordset("CompanyName")) & "</td>"
Response.write "<td class=xl26 width=146 style='border-top:none;border-left:none;width:110pt'>" & showCell(Recordset("ContactFirstname")) & "</td>"
Response.write "<td class=xl29 width=98 style='border-top:none;border-left:none;width:74pt' x:num=" & showCell(Recordset("ContactLastname")) & "></td>"
Response.write "<td class=xl27 width=100 style='border-top:none;border-left:none;width:75pt'>" & showCell(Recordset("NCL_Contact")) & "</td>"
Response.write "<td class=xl27 width=98 style='border-top:none;border-left:none;width:74pt' x:num=" & showCell(Recordset("NCL_CostCenter")) & "></td>"
Response.write "<td class=xl29 width=87 style='border-top:none;border-left:none;width:65pt' x:num=" & showCell(Recordset("DateAdded")) & "></td>"
Response.write "<td class=xl29 width=87 style='border-top:none;border-left:none;width:65pt' x:num=" & showCell(Recordset("Active")) & "></td>"
'Response.write "</tr>"
Recordset.MoveNext    
Loop
response.write "<tr height=17 style='height:12.75pt'>"
response.write "<td height=17 style='height:12.75pt'>Total Accounts: " & Counter & "</td>"
'response.write "<td colspan=6 style='mso-ignore:colspan'></td>"
response.write "<td></td>"
response.write "</tr>"
End If

I dont see where any of it has width of 0.
0
smyers051972Author Commented:
Thank you its all figured out now..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.