468,510 Members | 1,667 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,510 developers. It's quick & easy.

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
  1. Function GenHtmlTable()
  2. set conn=Server.CreateObject("ADODB.Connection")
  3. conn.provider="Microsoft.Jet.OLEDB.4.0;"
  4. conn.open server.mappath(MyDB.mdb")
  5.  
  6. set rs=Server.CreateObject("ADODB.recordset")
  7. rs.Open "Select * from Users", conn
  8. On Error resume next
  9. Dim sRet, VarFirstName, VarSurname, VarHomeTelephoneNumberCode, VarHomeTelephoneNumber
  10.      sRet = ""
  11.      sRet = <table border=1 class=mysmalltext><tr>"
  12.      sRet = sRet & "<td>Name</td>" & _
  13.      "<td>Surname</td>"& _
  14.      "<td>HomeTelephoneNumberCode</td>" & _   
  15.      "<td>HomeTelephoneNumber</td>" & _ 
  16.      sRet = sRet & "</tr>"
  17.  
  18. while (not rs.EOF)
  19.  
  20.      VarFirstName = rs("FirstName")
  21.      VarSurname = rs("Surname")
  22.      VarHomeTelephoneNumberCode = rs("HomeTelephoneNumberCode")
  23.      VarHomeTelephoneNumber = rs("HomeTelephoneNumber")
  24.  
  25.  
  26.      sRet = sRet & "<tr>"
  27.      sRet = sRet & "<td>"& VarFirstName & "</td><td>"
  28.      & VarSurname & "</td>" & VarHomeTelephoneNumberCode
  29.      & "</td><td>" & VarHomeTelephoneNumber & "</td>"
  30.      sRet = sRet & "</tr>"
  31.  
  32.     rs.MoveNext()
  33.     wend
  34.     sRet = sRet & "</table>"
  35.     rs.close()
  36.     conn.close()
  37.                 GenHtmlTable = sRet
  38. End Function
  39.  
  40. Response.Clear()
  41. Response.Buffer = True
  42. Response.AddHeader "Content-Disposition", "attachment;filename=export.xls"
  43. Response.ContentType = "application/vnd.ms-excel"
  44. Response.Write GenHtmlTable()
  45. Response.End()
  46.  
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
7 3690
jhardman
3,405 Expert 2GB
try putting a single apostrophe before the zero.

Jared
Oct 10 '10 #2
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
  1. {mso-number-format:General; text-align:general;white-space: nowrap; mso-spacerun: yes;}
Oct 10 '10 #3
jhardman
3,405 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
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,405 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,405 Expert 2GB
You can also go directly from asp to csv, that would circumvent the problem.

Jared
Oct 11 '10 #7
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
  1. <html xmlns:o="urn:schemas-microsoft-com:office:office"
  2. xmlns:x="urn:schemas-microsoft-com:office:excel"
  3. xmlns="http://www.w3.org/TR/REC-html40">
  4.  
  5. <head>
  6. <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
  7. <meta name=ProgId content=Excel.Sheet>
  8. <meta name=Generator content="Microsoft Excel 11">
  9. <link rel=File-List href="Book1_files/filelist.xml">
  10. <link rel=Edit-Time-Data href="Book1_files/editdata.mso">
  11. <link rel=OLE-Object-Data href="Book1_files/oledata.mso">
  12. <!--[if gte mso 9]><xml>
  13.  <o:DocumentProperties>
  14.   <o:Author>*****</o:Author>
  15.   <o:LastAuthor>*****</o:LastAuthor>
  16.   <o:Created>2010-10-14T16:38:41Z</o:Created>
  17.   <o:LastSaved>2010-10-14T16:42:11Z</o:LastSaved>
  18.   <o:Company>*******</o:Company>
  19.   <o:Version>*****</o:Version>
  20.  </o:DocumentProperties>
  21. </xml><![endif]-->
  22. <style>
  23. <!--table
  24.     {mso-displayed-decimal-separator:"\.";
  25.     mso-displayed-thousand-separator:"\,";}
  26. @page
  27.     {margin:1.0in .75in 1.0in .75in;
  28.     mso-header-margin:.5in;
  29.     mso-footer-margin:.5in;}
  30. tr
  31.     {mso-height-source:auto;}
  32. col
  33.     {mso-width-source:auto;}
  34. br
  35.     {mso-data-placement:same-cell;}
  36. .style0
  37.     {mso-number-format:General;
  38.     text-align:general;
  39.     vertical-align:bottom;
  40.     white-space:nowrap;
  41.     mso-rotate:0;
  42.     mso-background-source:auto;
  43.     mso-pattern:auto;
  44.     color:windowtext;
  45.     font-size:10.0pt;
  46.     font-weight:400;
  47.     font-style:normal;
  48.     text-decoration:none;
  49.     font-family:Arial;
  50.     mso-generic-font-family:auto;
  51.     mso-font-charset:0;
  52.     border:none;
  53.     mso-protection:locked visible;
  54.     mso-style-name:Normal;
  55.     mso-style-id:0;}
  56. td
  57.     {mso-style-parent:style0;
  58.     padding-top:1px;
  59.     padding-right:1px;
  60.     padding-left:1px;
  61.     mso-ignore:padding;
  62.     color:windowtext;
  63.     font-size:10.0pt;
  64.     font-weight:400;
  65.     font-style:normal;
  66.     text-decoration:none;
  67.     font-family:Arial;
  68.     mso-generic-font-family:auto;
  69.     mso-font-charset:0;
  70.     mso-number-format:General;
  71.     text-align:general;
  72.     vertical-align:bottom;
  73.     border:none;
  74.     mso-background-source:auto;
  75.     mso-pattern:auto;
  76.     mso-protection:locked visible;
  77.     white-space:nowrap;
  78.     mso-rotate:0;}
  79. .xl24
  80.     {mso-style-parent:style0;
  81.     font-weight:700;
  82.     font-family:Arial, sans-serif;
  83.     mso-font-charset:0;
  84.     background:silver;
  85.     mso-pattern:auto none;}
  86. .xl25
  87.     {mso-style-parent:style0;
  88.     mso-number-format:"Short Date";}
  89. .xl26
  90.     {mso-style-parent:style0;
  91.     font-weight:700;
  92.     font-family:Arial, sans-serif;
  93.     mso-font-charset:0;
  94.     mso-number-format:"\@";
  95.     background:silver;
  96.     mso-pattern:auto none;}
  97. .xl27
  98.     {mso-style-parent:style0;
  99.     mso-number-format:"\@";}
  100. -->
  101. </style>
  102. <!--[if gte mso 9]><xml>
  103.  <x:ExcelWorkbook>
  104.   <x:ExcelWorksheets>
  105.    <x:ExcelWorksheet>
  106.     <x:Name>Sheet1</x:Name>
  107.     <x:WorksheetOptions>
  108.      <x:Print>
  109.       <x:ValidPrinterInfo/>
  110.       <x:VerticalResolution>0</x:VerticalResolution>
  111.      </x:Print>
  112.      <x:Selected/>
  113.      <x:Panes>
  114.       <x:Pane>
  115.        <x:Number>3</x:Number>
  116.        <x:ActiveRow>4</x:ActiveRow>
  117.        <x:ActiveCol>3</x:ActiveCol>
  118.       </x:Pane>
  119.      </x:Panes>
  120.      <x:ProtectContents>False</x:ProtectContents>
  121.      <x:ProtectObjects>False</x:ProtectObjects>
  122.      <x:ProtectScenarios>False</x:ProtectScenarios>
  123.     </x:WorksheetOptions>
  124.    </x:ExcelWorksheet>
  125.    <x:ExcelWorksheet>
  126.     <x:Name>Sheet2</x:Name>
  127.     <x:WorksheetOptions>
  128.      <x:ProtectContents>False</x:ProtectContents>
  129.      <x:ProtectObjects>False</x:ProtectObjects>
  130.      <x:ProtectScenarios>False</x:ProtectScenarios>
  131.     </x:WorksheetOptions>
  132.    </x:ExcelWorksheet>
  133.    <x:ExcelWorksheet>
  134.     <x:Name>Sheet3</x:Name>
  135.     <x:WorksheetOptions>
  136.      <x:ProtectContents>False</x:ProtectContents>
  137.      <x:ProtectObjects>False</x:ProtectObjects>
  138.      <x:ProtectScenarios>False</x:ProtectScenarios>
  139.     </x:WorksheetOptions>
  140.    </x:ExcelWorksheet>
  141.   </x:ExcelWorksheets>
  142.   <x:WindowHeight>12525</x:WindowHeight>
  143.   <x:WindowWidth>21915</x:WindowWidth>
  144.   <x:WindowTopX>120</x:WindowTopX>
  145.   <x:WindowTopY>120</x:WindowTopY>
  146.   <x:ProtectStructure>False</x:ProtectStructure>
  147.   <x:ProtectWindows>False</x:ProtectWindows>
  148.  </x:ExcelWorkbook>
  149. </xml><![endif]-->
  150. </head>
  151.  
  152. <body link=blue vlink=purple>
  153.  
  154. <table x:str border=0 cellpadding=0 cellspacing=0 width=343 style='border-collapse:
  155.  collapse;table-layout:fixed;width:259pt'>
  156.  <col width=90 span=2 style='mso-width-source:userset;mso-width-alt:3291;
  157.  width:68pt'>
  158.  <col width=82 style='mso-width-source:userset;mso-width-alt:2998;width:62pt'>
  159.  <col width=81 style='mso-width-source:userset;mso-width-alt:2962;width:61pt'>
  160.  <tr height=17 style='height:12.75pt'>
  161.   <td height=17 class=xl24 width=90 style='height:12.75pt;width:68pt'>Column1</td>
  162.   <td class=xl24 width=90 style='width:68pt'>Column2</td>
  163.   <td class=xl24 width=82 style='width:62pt'>DateHeader</td>
  164.   <td class=xl26 width=81 style='width:61pt'>TextHeader</td>
  165.  </tr>
  166.  <tr height=17 style='height:12.75pt'>
  167.   <td height=17 style='height:12.75pt'>Row2Column1</td>
  168.   <td>Row2Column2</td>
  169.   <td class=xl25 align=right x:num="40179">2010/01/01</td>
  170.   <td class=xl27>0001</td>
  171.  </tr>
  172.  <tr height=17 style='height:12.75pt'>
  173.   <td height=17 style='height:12.75pt'>Row3Column1</td>
  174.   <td>Row3Column2</td>
  175.   <td class=xl25 align=right x:num="40179">2010/01/01</td>
  176.   <td class=xl27>0002</td>
  177.  </tr>
  178.  <tr height=17 style='height:12.75pt'>
  179.   <td height=17 style='height:12.75pt'>Row4Column1</td>
  180.   <td>Row4Column2</td>
  181.   <td class=xl25 align=right x:num="40179">2010/01/01</td>
  182.   <td class=xl27>0003</td>
  183.  </tr>
  184.  <![if supportMisalignedColumns]>
  185.  <tr height=0 style='display:none'>
  186.   <td width=90 style='width:68pt'></td>
  187.   <td width=90 style='width:68pt'></td>
  188.   <td width=82 style='width:62pt'></td>
  189.   <td width=81 style='width:61pt'></td>
  190.  </tr>
  191.  <![endif]>
  192. </table>
  193.  
  194. </body>
  195.  
  196. </html>
  197.  
  198.  
  199.  
Oct 14 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by jsudo | last post: by
1 post views Thread by s_a_ravi | last post: by
2 posts views Thread by amitshinde02 | last post: by
1 post views Thread by theheathergirl | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.