0

We have an application in Classic ASP(Active Server Pages)

We have following source code to Export a data to Excel

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

    sqlString = Trim(Request("hiddensqlstring"))

    If sqlString <> "" then
        set result = cn.execute(sqlString)
%>
    <table border="1" style="width:300px;" align="center">
    <tr>
        <td align="left">ContractNo</b></td>
        <td align="left">TotalNo</td>
    </tr>
            <% do while not result.EOF %>
            <tr>                             
                <td align="left"><%=result("ContractNo")%></td>
                <td align="right"><%=result("TotalNo")%></td>
            </tr>
         <% result.MoveNext
         loop 
         %>
    </table>
<%Else
    Response.Write("<BR> <BR> <P ALIGN=CENTER CLASS=PAGETITLE> Error </P>")
End If%>

After exporting the values the value of ContractNo which has to be "12345.678901.0100" is getting exported as "123,456,789,010,100" or sometimes "12,34,56,78,90,10,100". especially on systems where Excel is configured with Portuguese language.

Is there any settings or format changes that I have to make?

captainsac
  • 2,484
  • 3
  • 27
  • 48
  • This has nothing to do with export, I bet it you tested it by switching `Response.ContentType="application/vnd.ms-excel"` to `Response.ContentType="text/html"` the values will be shown correctly, in which case the problem is with the local regional settings on the machine opening the export. You might be able to override the defaults by passing `mso-number-format` or something similar though. – user692942 Mar 15 '16 at 12:35
  • Try using NumberFormat Text in Excel: `<%=result("ContractNo")%>` – Axel Richter Mar 15 '16 at 12:35
  • Possible duplicate of [Export to Excel using ASP](http://stackoverflow.com/questions/584863/export-to-excel-using-asp) – user692942 Mar 15 '16 at 12:38
  • Also [Date format in asp to excel form](http://stackoverflow.com/a/31667157/692942) shows how to use `mso-number-format`. – user692942 Mar 15 '16 at 13:03

1 Answers1

0

Change

<td align="left"><%=result("ContractNo")%></td>

To

<td align="left">=<%=result("ContractNo")%></td>

The = tells excel not to try and change the format and treat it as a set value

Barry Dowd
  • 168
  • 5