By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,868 Members | 1,991 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,868 IT Pros & Developers. It's quick & easy.

How to export to excel from ASP?

P: 5
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
Share this Question
Share on Google+
7 Replies


jhardman
Expert 2.5K+
P: 3,405
try putting a single apostrophe before the zero.

Jared
Oct 10 '10 #2

P: 5
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
Expert 2.5K+
P: 3,405
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

P: 5
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
Expert 2.5K+
P: 3,405
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
Expert 2.5K+
P: 3,405
You can also go directly from asp to csv, that would circumvent the problem.

Jared
Oct 11 '10 #7

P: 5
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.