Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to use ASP to format excel spreadsheet columns

Posted on 2010-01-12
18
Medium Priority
?
832 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
  • 9
  • 6
16 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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