Export data to Excel though ASP

ivychan used Ask the Experts™
I have a ASP file, select data from SQL Server and display it in excel. But it has two problems.

1. Some data is missing (I want to display a table in the following:)

Group    Subtotal
Textbook  1
book      1
Total:    2

2. Chinese data can't display


How to solve, please help me. Thanks!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
1st, I wrote this in Notepad since i am witout my usual developement PC - so this might have some bugs (hell it might not even work!)
2nd, there is minimal error checking - but if it works,  you can add that!

Since not a lot of detail was supplied, i assumed a few things to give you an example.

> DB is set up with a DSN called My_DSN
> Table that holds data is called Table1
> the Group field in Table1 is called 'Group'
> the SubTotal field in Table1 is called 'SubTotal'

Look out for items marked *** - these should be edited to suit

Here goes...

Const sDSN="My_DSN" '***Set this to required DSN***

''Dim variables
Dim objCn
Dim objRst
Dim sSql
Dim sErr

'Set up SQL to retrieve records *** Set this as required ***
sSql = "SELECT Group, Subtotal FROM Table1"

'Create connection
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN" & sDSN & ";"

GetRS objCon, objRst, sErr

'******************* Functions **********************
Function GetRS(byref objCon, byref rs, Byval sQuery, ByRef ErrString, Byval iItemsPerPage)
on error resume next
'Create RS and Get the data Values***************************************
     'Create recordset object
Set rs = Server.CreateObject("ADODB.Recordset")
With rs
     .ActiveConnection = objCon
     .CursorLocation = adUseClient
     .CursorType = adOpenForwardOnly
     .LockType = adLockReadOnly
     .Source = sQuery
     .PageSize = iItemsPerPage
     Set .ActiveConnection = Nothing     'Disconnect the recordset
End With
if Err <> 0 then ErrString = "Error retrieving data for: " & sQuery & "<BR>" & Err.Description & " (" & Err.Number & ")"
End Function

Function CleanUp
If IsObject(objRst) Then
    If Not objRst Is Nothing Then
         If objRst.State = 1 Then objRst.Close
         Set objRst = Nothing
    End If
End If

If IsObject(objConn) Then
    If Not objConn Is Nothing Then
         If objConn.State = 1 Then objConn.Close
         Set objConn = Nothing
    End If
End If
End Function

<Title>Data To Excel</Title>
<!--The Line below indicates content is for Excel-->
<%Response.ContentType = "application/vnd.ms-excel"%>
        Dim iTotal, iTemp, sItem, iLoop
        If sErr = "" then
          While Not objRst.EOF
            sItem=objRst("Group")'*** set Field name as required
            iTemp=objRst("Subtotal")'*** set Field name as required
            iTotal=iTotal + iTemp
            Response.Write vbcrlf 'start a new line
            Response.Write "<TR><TD width=""70%"">"
            Response.Write sItem
            Response.Write "</TD><TD width=""30%"">"
            Response.Write iTemp
            Response.Write "</TD><TR>"
          Response.Write "<TR><TD>Total:</TD><TD>" & iTotal & "</TD></TR>"
          Response.Write "<TR><TD align=Center COLSPAN=2>Error: " & sErr & "</TD></TR>"
        End If

<% CleanUp %>
theres the 1st bug, spotted it when i read it back!!!

... i called the connection objCn then i try to use objCon then i attempt to clean up objConn - whats going on!

These will need to be made all the same name!

A good argument for option explicit!!!

ivychan, add this at the very top of the asp file

<% Option Explicit %>

to catch those inevitable bugs!  (well i did do it in note pad (not realy an excuse i suppose!))

I'll keep taking the happy pils ;)

let me talk about the chinese data not displaying...
first, are you data in unicode format? if they are then u should use the charset "utf-8".
if youre not using unicode format then you should find the appopriate charset for the language you are using...
as i understand there are few types of chinese language...
tradtional and simplified...

heres an example of setting a charset...

=simplified chinese=
<META HTTP-EQUIV="Content-type" CONTENT="text/html; charset=gb2312">

use "big5" for traditional
use "utf-8" for unicode encoding...

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.


To stevie_mac

Thanks! But I try your method to display data from SQL server. It also is missing some data.


To jmevasco

Yes, it display Unicode format. I have already used charset that is big-5. But it also can't work.


To jmevasco

Yes, it display Unicode format. I have already used charset that is big-5. But it also can't work.

can u check the table in ur db where your data is coming from is its storing the unicode format data properly...
it should look like "&#3649;&#3621;&#3604;&#3610;&#3619;&#3641;&#3658;&#3588;"

if they are "????" then ur data is not stored correctly...

are your fields in nvarchar data type?

check out in your code if ur setting the codepage property... that overwrites the charset when it is set.

or better yet post a sample of ur code...


1. The field Type is nvarchar
2. It can store chinese e.g.&#35430;&#39511;
3. my program set code page and charset

<%@codepage 950 %>
<META HTTP-EQUIV="Content-type" CONTENT="text/html; charset=big-5">

But export to excel that display " Ç¼½»PªÀ·|µo®i¡v¾Ç³N¬ã°Q·|½×¤ "

Please help!

if u display it in a web page... are the chinese character correctly displayed?


In Office 97, you need to run the following first...
Traditional Chinese       Chtsupp.exe
Simplified Chinese       Chssupp.exe

These are on the CD (see the ValuPack)

ive tried exporting my chinese unicode data to excel using ASP and the characters are displayed properly...
im not sure whats wrong with your situation...
im using windows 2000 prof, and office 2000 prof ed. if that helps...

ivychan, did you get this sorted?
Did the Chtsupp.exe/Chssupp.exe off the CD do the job?

No comment has been added lately and it seems that this question have been abandoned. So it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question or invite a Moderator to close this question if there's no reply from you after seven days.

In the absence of responses, I will recommend the following:

To accept the comment and points awarded to  stevie_mac


Just trying to help for the cleanup...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial