?
Solved

How to use ASP to format excel spreadsheet columns

Posted on 2010-01-12
18
Medium Priority
?
811 Views
Last Modified: 2012-05-08
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
%>
0
Comment
Question by:smyers051972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
18 Comments
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 26298961
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
 
LVL 12

Expert Comment

by:R_Harrison
ID: 26302656
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
 
LVL 12

Expert Comment

by:R_Harrison
ID: 26302675
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:smyers051972
ID: 26306226
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
 
LVL 1

Author Comment

by:smyers051972
ID: 26306267
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
 
LVL 1

Author Comment

by:smyers051972
ID: 26308791
I also noticed the username is cut off the report on the last right column...
0
 
LVL 12

Expert Comment

by:R_Harrison
ID: 26313718
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
 
LVL 1

Author Comment

by:smyers051972
ID: 26314718
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
 
LVL 12

Expert Comment

by:R_Harrison
ID: 26314941
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
 
LVL 1

Author Comment

by:smyers051972
ID: 26315162
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
 
LVL 12

Expert Comment

by:R_Harrison
ID: 26316007
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
 
LVL 1

Author Comment

by:smyers051972
ID: 26316430
ok i see that, it is hiding the columns... I thought it was simply omitted some how.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 26316511
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
 
LVL 12

Accepted Solution

by:
R_Harrison earned 2000 total points
ID: 26381710
Did you change both width settings???

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

Author Comment

by:smyers051972
ID: 26399563
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
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31676304
Thank you its all figured out now..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question