How to export to excel from ASP?

Hi, I need some assistance with exporting to excel from asp. I have seen your responses on the formating of columns when exporting, but I need an example.

The code I'm using:

Expand|Select|Wrap|Line Numbers

 
Function GenHtmlTable()

set conn=Server.CreateObject("ADODB.Connection")

conn.provider="Microsoft.Jet.OLEDB.4.0;"

conn.open server.mappath(MyDB.mdb")
 
set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Users", conn

On Error resume next

Dim sRet, VarFirstName, VarSurname, VarHomeTelephoneNumberCode, VarHomeTelephoneNumber

     sRet = ""

     sRet = <table border=1 class=mysmalltext><tr>"

     sRet = sRet & "<td>Name</td>" & _

     "<td>Surname</td>"& _

     "<td>HomeTelephoneNumberCode</td>" & _   

     "<td>HomeTelephoneNumber</td>" & _ 

     sRet = sRet & "</tr>"
 
while (not rs.EOF)
 
     VarFirstName = rs("FirstName")

     VarSurname = rs("Surname")

     VarHomeTelephoneNumberCode = rs("HomeTelephoneNumberCode")

     VarHomeTelephoneNumber = rs("HomeTelephoneNumber")
 
     sRet = sRet & "<tr>"

     sRet = sRet & "<td>"& VarFirstName & "</td><td>"

     & VarSurname & "</td>" & VarHomeTelephoneNumberCode

     & "</td><td>" & VarHomeTelephoneNumber & "</td>"

     sRet = sRet & "</tr>"
 
    rs.MoveNext()

    wend

    sRet = sRet & "</table>"

    rs.close()

    conn.close()

                GenHtmlTable = sRet

End Function
 
Response.Clear()

Response.Buffer = True

Response.AddHeader "Content-Disposition", "attachment;filename=export.xls"

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

Response.Write GenHtmlTable()

Response.End()

Now, the problem is the telephone area code starts with a 0, and I need it in text. Like I said, I have read and tested your comments, but apparently I'm to much of a novice to master it without assistance.

Please assist, Thanx. H

Oct 9 '10 #1

Follow Post Reply

3822

jhardman

3,406

Expert 2GB

try putting a single apostrophe before the zero.

Jared

Oct 10 '10 #2

Hennie JvN

I have tried that, seeing that one would usually get a hidden apostrophe in excel. But if I do that the number appears like this '0 instead of just 0. I was actually more tending to use the mso-number-format

Expand|Select|Wrap|Line Numbers

 {mso-number-format:General; text-align:general;white-space: nowrap; mso-spacerun: yes;}
 

Oct 10 '10 #3

jhardman

3,406

Expert 2GB

You absolutely need to make sure you save as text rather than a number. As far as I know there is no way to force an excel column to only accept 'varchars' so your alternatives are to start with an apostrophe (excel default) or some other character that can't be interpreted as numeric (so commas and periods are no good). If an apostrophe won't work for you, then consider a space or using a left() or right() function to remove the apostrophe before displaying it.

Jared

Oct 11 '10 #4

Hennie JvN

The problem is that I will need to convert the file to CSV and the database that I need to import it in to, will not allow any other characters. But Thanx for trying to help. I'll try to explore asp.net, I might find something that will help. H

Oct 11 '10 #5

jhardman

3,406

Expert 2GB

It is excel's limitation, as long as you use excel you will not be able to get around that. If you are really serious, I would suggest switching to a fuLl db instead of messing around with csvs and excel sheets.

Jared

Oct 11 '10 #6

jhardman

3,406

Expert 2GB

You can also go directly from asp to csv, that would circumvent the problem.

Jared

Oct 11 '10 #7

Hennie JvN

Hey, I found my answer and thought I'd post it. The idea is to create a spreadsheet from ASP and ADO information. If you open a spreadsheet saved as a web page (in notepad) you will see that it is basically an xml page combined with an html page. NOW what I did was, I created the columns that I needed and formatted it accordingly. I then saved the spreadsheet as a web page, and opened it with notepad. The styles created by excel explained everything. You can basically copy the document to VB and just insert your own variables, and styles.

Note the xl24 xl26 xl27 styles

Expand|Select|Wrap|Line Numbers

 
<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=Excel.Sheet>

<meta name=Generator content="Microsoft Excel 11">

<link rel=File-List href="Book1_files/filelist.xml">

<link rel=Edit-Time-Data href="Book1_files/editdata.mso">

<link rel=OLE-Object-Data href="Book1_files/oledata.mso">

<!--[if gte mso 9]><xml>

 <o:DocumentProperties>

  <o:Author>*****</o:Author>

  <o:LastAuthor>*****</o:LastAuthor>

  <o:Created>2010-10-14T16:38:41Z</o:Created>

  <o:LastSaved>2010-10-14T16:42:11Z</o:LastSaved>

  <o:Company>*******</o:Company>

  <o:Version>*****</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;

    font-family:Arial, sans-serif;

    mso-font-charset:0;

    background:silver;

    mso-pattern:auto none;}

.xl25

    {mso-style-parent:style0;

    mso-number-format:"Short Date";}

.xl26

    {mso-style-parent:style0;

    font-weight:700;

    font-family:Arial, sans-serif;

    mso-font-charset:0;

    mso-number-format:"\@";

    background:silver;

    mso-pattern:auto none;}

.xl27

    {mso-style-parent:style0;

    mso-number-format:"\@";}

-->

</style>

<!--[if gte mso 9]><xml>

 <x:ExcelWorkbook>

  <x:ExcelWorksheets>

   <x:ExcelWorksheet>

    <x:Name>Sheet1</x:Name>

    <x:WorksheetOptions>

     <x:Print>

      <x:ValidPrinterInfo/>

      <x:VerticalResolution>0</x:VerticalResolution>

     </x:Print>

     <x:Selected/>

     <x:Panes>

      <x:Pane>

       <x:Number>3</x:Number>

       <x:ActiveRow>4</x:ActiveRow>

       <x:ActiveCol>3</x:ActiveCol>

      </x:Pane>

     </x:Panes>

     <x:ProtectContents>False</x:ProtectContents>

     <x:ProtectObjects>False</x:ProtectObjects>

     <x:ProtectScenarios>False</x:ProtectScenarios>

    </x:WorksheetOptions>

   </x:ExcelWorksheet>

   <x:ExcelWorksheet>

    <x:Name>Sheet2</x:Name>

    <x:WorksheetOptions>

     <x:ProtectContents>False</x:ProtectContents>

     <x:ProtectObjects>False</x:ProtectObjects>

     <x:ProtectScenarios>False</x:ProtectScenarios>

    </x:WorksheetOptions>

   </x:ExcelWorksheet>

   <x:ExcelWorksheet>

    <x:Name>Sheet3</x:Name>

    <x:WorksheetOptions>

     <x:ProtectContents>False</x:ProtectContents>

     <x:ProtectObjects>False</x:ProtectObjects>

     <x:ProtectScenarios>False</x:ProtectScenarios>

    </x:WorksheetOptions>

   </x:ExcelWorksheet>

  </x:ExcelWorksheets>

  <x:WindowHeight>12525</x:WindowHeight>

  <x:WindowWidth>21915</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=343 style='border-collapse:

 collapse;table-layout:fixed;width:259pt'>

 <col width=90 span=2 style='mso-width-source:userset;mso-width-alt:3291;

 width:68pt'>

 <col width=82 style='mso-width-source:userset;mso-width-alt:2998;width:62pt'>

 <col width=81 style='mso-width-source:userset;mso-width-alt:2962;width:61pt'>

 <tr height=17 style='height:12.75pt'>

  <td height=17 class=xl24 width=90 style='height:12.75pt;width:68pt'>Column1</td>

  <td class=xl24 width=90 style='width:68pt'>Column2</td>

  <td class=xl24 width=82 style='width:62pt'>DateHeader</td>

  <td class=xl26 width=81 style='width:61pt'>TextHeader</td>

 </tr>

 <tr height=17 style='height:12.75pt'>

  <td height=17 style='height:12.75pt'>Row2Column1</td>

  <td>Row2Column2</td>

  <td class=xl25 align=right x:num="40179">2010/01/01</td>

  <td class=xl27>0001</td>

 </tr>

 <tr height=17 style='height:12.75pt'>

  <td height=17 style='height:12.75pt'>Row3Column1</td>

  <td>Row3Column2</td>

  <td class=xl25 align=right x:num="40179">2010/01/01</td>

  <td class=xl27>0002</td>

 </tr>

 <tr height=17 style='height:12.75pt'>

  <td height=17 style='height:12.75pt'>Row4Column1</td>

  <td>Row4Column2</td>

  <td class=xl25 align=right x:num="40179">2010/01/01</td>

  <td class=xl27>0003</td>

 </tr>

 <![if supportMisalignedColumns]>

 <tr height=0 style='display:none'>

  <td width=90 style='width:68pt'></td>

  <td width=90 style='width:68pt'></td>

  <td width=82 style='width:62pt'></td>

  <td width=81 style='width:61pt'></td>

 </tr>

 <![endif]>

</table>
 
</body>
 
</html>

Oct 14 '10 #8

Post your reply

Similar topics