Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Generate excel file with asp

Status
Not open for further replies.
Albano

Albano

Instructor
Joined
Dec 11, 2000
Messages
221
Location
PT
Hi,

I'm trying to change the header of execl file trow asp, and nothing appen, can someane take a look of this code to see what is wrong.

Thanks,

Albano

Code:

Set fso = CreateObject("Scripting.FileSystemObject")
Set NewFile = fso.CreateTextFile(nFich, True)

NewFile.WriteLine("<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">")
NewFile.WriteLine("<head>")
NewFile.WriteLine("<tr><td align='center'>" & imgCabecalho & "</td></tr>")
NewFile.WriteLine("<!--[if gte mso 9]><xml>")
NewFile.WriteLine("<x:ExcelWorkbook>")
NewFile.WriteLine("<x:ExcelWorksheets>")
NewFile.WriteLine("<x:ExcelWorksheet>")
NewFile.WriteLine("<x:Name>TDB Operacional semanal</x:Name>")
NewFile.WriteLine("<x:WorksheetOptions>")
NewFile.WriteLine("<x:PageSetup>")
NewFile.WriteLine("<x:Header>hello</x:Header>")
NewFile.WriteLine("</x:PageSetup>")
NewFile.WriteLine("<x:Print>")
NewFile.WriteLine("<x:ValidPrinterInfo/>")
NewFile.WriteLine("</x:Print>")
NewFile.WriteLine("</x:WorksheetOptions>")
NewFile.WriteLine("</x:ExcelWorksheet>")
NewFile.WriteLine("</x:ExcelWorksheets>")
NewFile.WriteLine("</x:ExcelWorkbook>")
NewFile.WriteLine("</xml>")
NewFile.WriteLine("<![endif]-->")
NewFile.WriteLine("</head>")
 
Sort by date Sort by votes
You want to generate an HTML file with office html tags as i see.
Or you want to generate and XLS file?
That html should be imported in excel or opened with.

________
George, M
Searches(faq333-4906),Carts(faq333-4911)
 
Upvote 0 Downvote
Hello Albano. I'm using the script below to open an excel file using ASP. The script includes a database connection along with sql that allows me to open my page in excel based on the criteria I select. Try it out.


Script

<%@ Language=VBScript %>
<%
Set Cnt = Server.CreateObject ("ADODB.Connection")
Set RecordSet = Server.CreateObject("ADODB.Recordset")

Cnt.Open "ASPVOL"
'-- Declare your variables
Dim Cnt, cmdDC, RSData, RecordSet, SecondMessage, reportType
'-- Create object and open database
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = Cnt

FirstDate = Request.Form("BeginDate")
LastDate = Request.Form("EndDate")
set RSData = Cnt.Execute("SELECT CustomerName, BoxNumber, Sum(ExternalAttempts) AS ExAttempts, Sum(InternalAttempts) AS InAttempts, Sum(Attempts) AS RAttempts, Sum(ExtractOnly) AS Extract, " _
& "Sum(ImageItems) AS Image, Sum(Correspondence) AS Corr, Sum(Unbankables) AS Unbank, Sum(ExternalHoldover) AS OutsideHoldover, Sum(InternalHoldover) AS InsideHoldover, " _
& "Sum(Holdover) AS HoldoverItems, Sum(Lookups) AS LookupItems, Sum(Returns) AS ReturnItems, Sum(ExternalImage) AS EXImage, Sum(ExternalCorr) AS ExCorr, Sum(ExternalUnbank) AS ExUnbank, " _
& "Sum(ExternalLookups) AS ExLookups, SUM(InternalImage) AS InImage, Sum(InternalCorr) AS InCorr, Sum(InternalUnbank) As InUnbank, Sum(InternalLookups) AS InLookups, Sum(PostalCards) AS PostalReturns " _
& "FROM qryMTDVolume " _
& "WHERE VolDate >= #" & Request.Form("BeginDate") & "# and " _
& "VolDate <= #" & Request.Form("EndDate") & "# " _

& "GROUP BY CustomerName, BoxNumber")
ReportTitle = "Customer Volume between " & Request.Form("BeginDate") _
& " and " & Request.Form("EndDate")




'Change HTML header to specify Excel's MIME content type.
Response.Buffer = TRUE

Response.ContentType = "application/vnd.ms-excel"
%>

<HTML>
<BODY>
<TABLE>
<tr>
<td width="150%" colspan="8">
<p align="center"><b><font face="Arial">
<img border="0" src="images/logo_Jason_v2.gif" width="110" height="49">DAILY STATUS REPORT from: <%=(FirstDate)%> through <%=(LastDate)%></font></b></td>
</tr>




<tr>
<td width="50%">&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>
<td width="50%" align=center>&nbsp;</td>

</tr>




<tr>
<td width="50%">Customer</td>
<td width="50%" align=center>Image</td>
<td width="50%" align=center>Extract</td>
<td width="50%" align=center>Correspondence</td>
<td width="50%" align=center>Unbankables</td>
<td width="50%" align=center>Postal Cards</td>
<td width="50%" align=center>Holdover</td>
<td width="50%" align=center>Lookups</td>
<td width="50%" align=center>Attempts</td>
<td width="50%" align=center>Returns</td>
<td width="50%" align=center>Totals</td>
</tr>




<%
Do Until RSData.EOF

'-- Display the fields
ImageTotal = ImageTotal + RSData("Image")
ExtractTotal = ExtractTotal + RSData("Extract")
CorrTotal = CorrTotal + RSData("Corr")
UnbankTotal = UnbankTotal + RSData("Unbank")
HoldoverTotal = HoldoverTotal + RSData("HoldoverItems")
LookupTotal = LookupTotal + RSData("LookupItems")
ReturnsTotal = ReturnsTotal + RSData("ReturnItems")
TotalVol = TotalVol + RSData("Image") + RSData("Extract") + RSData("Corr") + RSData("Unbank") + RSData("InLookups") + RSData("ReturnItems") + RSData("RAttempts")
ExternalTotal = ExternalTotal + RSData("ExImage") + RSData("ExCorr") + RSData("ExUnbank") + RSData("ReturnItems") + RSData("ExAttempts")
InternalTotal = InternalTotal + RSData("InImage") + RSData("Extract") + RSData("InCorr") + RSData("InUnbank") + RSData("InLookups") + RSData("InAttempts")
TotalAttempts = TotalAttempts + RSData("RAttempts")
PostalReturnedItems = PostalReturnedItems + RSData("PostalReturns")

ExternalImageItems = ExternalImageItems + RSData("ExImage")
ExternalCorrItems = ExternalCorrItems + RSData("ExCorr")
ExternalUnbankItems = ExternalUnbankItems + RSData("ExUnbank")
ExternalLookupItems = ExternalLookupItems + RSData("ExLookups")
ExAttempt = ExAttempt + RSData("ExAttempts")
ExternalHoldover = ExternalHoldover + RSData("OutsideHoldover")


InternalImageItems = InternalImageItems + RSData("InImage")
InternalCorrItems = InternalCorrItems + RSData("InCorr")
InternalUnbankItems = InternalUnbankItems + RSData("InUnbank")
InternalLookupItems = InternalLookupItems + RSData("InLookups")
InAttempt = InAttempt + RSData("InAttempts")
InternalHoldover = InternalHoldover + RSData("InsideHoldover")

Dim r, Customer, DDA, Box, Correspondence, Unbankables, Postals, Heldover, Lookedup, Attempted, Returned, Totaled
%>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%">

<% For r= 1 to 1 %>
<TR>

<% Customer = RSDATA("CustomerName") %>
<TD><%=Customer%>&nbsp;</TD>

<% DDA = FormatNumber(RSDATA("Image"),0) %>
<TD align=right><%=DDA%>&nbsp;</TD>

<% Box = FormatNumber(RSDATA("Extract"),0) %>
<TD align=right><%=Box%>&nbsp;</TD>

<% Correspondence = FormatNumber(RSDATA("Corr"),0) %>
<TD align=right><%=Correspondence%>&nbsp;</TD>

<% Unbankables = FormatNumber(RSDATA("Unbank"),0) %>
<TD align=right><%=Unbankables%>&nbsp;</TD>

<% Postals = FormatNumber(RSDATA("PostalReturns"),0) %>
<TD align=right><%=Postals%>&nbsp;</TD>

<% Heldover = FormatNumber(RSDATA("HoldoverItems"),0) %>
<TD align=right><%=Heldover%>&nbsp;</TD>

<% Lookedup = FormatNumber(RSDATA("LookupItems"),0) %>
<TD align=right><%=Lookedup%>&nbsp;</TD>

<% Lookedup = FormatNumber(RSDATA("RAttempts"),0) %>
<TD align=right><%=Lookedup%>&nbsp;</TD>

<% Returned = FormatNumber(RSDATA("ReturnItems"),0) %>
<TD align=right><%=Returned%>&nbsp;</TD>

<% Returned = FormatNumber(RSDATA("ReturnItems") + RSDATA("Image") + RSDATA("Extract") + RSDATA("Corr") + RSDATA("Unbank") + RSDATA("LookupItems") + RSDATA("RAttempts") + RSDATA("ReturnItems"),0) %>
<TD align=right><%=Returned%>&nbsp;</TD>


</TD>

</TR>
<% Next %>



<%
RSData.MoveNext
Loop
%>


<TR>

<TD><b>External Total</b></TD>
<TD align=right><b><%=FormatNumber((ExternalImageItems),0)%>&nbsp;</TD>
<TD align=right>---------&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExternalCorrItems),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExternalUnbankItems),0)%>&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExternalLookupItems),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExAttempt),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ReturnsTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExternalTotal),0)%>&nbsp;</TD>

</TR>



<TR>

<TD><b>Internal Total</b></TD>
<TD align=right><b><%=FormatNumber((InternalImageItems),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExtractTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((InternalCorrItems),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((InternalUnbankItems),0)%>&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right><b><%=FormatNumber((InternalLookupItems),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((InAttempt),0)%>&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right><b><%=FormatNumber((InternalTotal),0)%>&nbsp;</TD>
</TR>


<TR>

<TD><b>Grand Total</b></TD>
<TD align=right><b><%=FormatNumber((ImageTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ExtractTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((CorrTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((UnbankTotal),0)%>&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right>--------&nbsp;</TD>
<TD align=right><b><%=FormatNumber((LookupTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((TotalAttempts),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((ReturnsTotal),0)%>&nbsp;</TD>
<TD align=right><b><%=FormatNumber((TotalVol),0)%>&nbsp;</TD>
</TR>

</TABLE>
</BODY>
</HTML>
 
Upvote 0 Downvote
hi,

I already generate a XLS file, the problem is that i can´t change the header in excel page setup.

Thanks.

Albano
 
Upvote 0 Downvote
Farily new to all this so if I am misleading you please accept my apologies. Have you tried declaring an alias, in example

SELECT CustomerName as [Customer Name], etc. etc.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question Question
How to send HTML emails with ASP classic?
Replies
0
Views
1K
leifoet
leifoet
penguinspeaks
  • Locked
  • Question Question
write HTML code within ASP line
Replies
1
Views
1K
xwb
xwb
penguinspeaks
  • Locked
  • Question Question
Return to current location on page after asp processes.
Replies
2
Views
1K
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question Question
READ TEXT FILE BASED ON VARIABLE
Replies
2
Views
894
guitarzan
guitarzan
JScannell
  • Locked
  • Question Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
739
JScannell
JScannell

Part and Inventory Search

Sponsor

Back
Top