Some problems encountered by ASP exporting data to Excel

has been using the mobile platform of the ASP to do News publishing site, until now contact to export data to excel problems, the purpose of the company to count the amount of contributions to the Department, to do such things, realize it is quite simple, but the first time to do, or the cost of some kung fu, hereby record

The main code is as follows:

Write query string

Rsall as query string

Rsall.open sqlall,conn,1,3

Set xlapplication =server. CreateObject ("Excel.Application") ' Invoke Excel object
Xlapplication.visible = False ' no need to open Excel
Set xlworkbook=xlapplication.workbooks.add ' Add workbook
J=1

Define some table styles
Xlworkbook.worksheets (1). Columns (1). Columnwidth=30
Xlworkbook.worksheets (1). Columns (1). Horizontalalignment=3
Xlworkbook.worksheets (1). Columns (2). Columnwidth=30
Xlworkbook.worksheets (1). Columns (2). Horizontalalignment=3
Xlworkbook.worksheets (1). Columns (3). Columnwidth=30
Xlworkbook.worksheets (1). Columns (3). Horizontalalignment=3

Xlworkbook.worksheets (1). Range ("A2"). Font.bold=true ' Bold
Xlworkbook.worksheets (1). Range ("B2"). Font.bold=true ' Bold
Xlworkbook.worksheets (1). Range ("C2"). Font.bold=true ' Bold
Xlworkbook.worksheets (1). Range ("A1"). Horizontalalignment=3 ' Horizontal alignment
Xlworkbook.worksheets (1). Range ("A1"). Verticalalignment=3 ' Vertical alignment

Writing the Excel table header

Xlworkbook.worksheets (1). Cells (a). Value = "Group News Center each Bureau points statistics"
Xlworkbook.worksheets (1). Cells (2,1). Value = "Bureau name"
Xlworkbook.worksheets (1). Cells (2,2). Value = "Number of news articles"
Xlworkbook.worksheets (1). Cells (2,3). Value = "News article credit"
Xlworkbook.worksheets (1). Cells (2,4). Value = "Number of comprehensive news articles"
Xlworkbook.worksheets (1). Cells (2,5). Value = "Comprehensive news article credit"
Xlworkbook.worksheets (1). Cells (2,6). Value = "Number of articles in other columns"
Xlworkbook.worksheets (1). Cells (2,7). Value = "Number of articles in other columns"
Xlworkbook.worksheets (1). Cells (2,8). Value = "Number of public number"
Xlworkbook.worksheets (1). Cells (2,9). Value = "Public number points"
Xlworkbook.worksheets (1). Cells (2,10). Value = "Number of newspapers and magazines"
Xlworkbook.worksheets (1). Cells (2,11). Value = "Newspaper and magazine points"
Xlworkbook.worksheets (1). Cells (2,12). Value = "Number of network stations"
Xlworkbook.worksheets (1). Cells (2,13). Value = "Network TV points"
Xlworkbook.worksheets (1). Cells (2,14). Value = "Number of prizes won"
Xlworkbook.worksheets (1). Cells (2,15). Value = "Contest Award points"

Loop output The following data

Do and not rsall.eof
Xlworkbook.worksheets (1). Cells (2+j,1). Value = Rsall ("CopyFrom")
Xlworkbook.worksheets (1). Cells (2+j,2). Value = Rsall ("Ywno")
Xlworkbook.worksheets (1). Cells (2+j,3). Value = Rsall ("Ywfs")
Xlworkbook.worksheets (1). Cells (2+j,4). Value = Rsall ("Zhno")
Xlworkbook.worksheets (1). Cells (2+j,5). Value = Rsall ("ZHFS")
Xlworkbook.worksheets (1). Cells (2+j,6). Value = Rsall ("Qtno")
Xlworkbook.worksheets (1). Cells (2+j,7). Value = Rsall ("Qtfs")
Xlworkbook.worksheets (1). Cells (2+j,8). Value = Rsall ("Weixinno")
Xlworkbook.worksheets (1). Cells (2+j,9). Value = Rsall ("Weixinfs")
Xlworkbook.worksheets (1). Cells (2+j,10). Value = Rsall ("Baozishu")
Xlworkbook.worksheets (1). Cells (2+j,11). Value = Rsall ("Baozifenshu")
Xlworkbook.worksheets (1). Cells (2+j,12). Value = Rsall ("Shipinshu")
Xlworkbook.worksheets (1). Cells (2+j,13). Value = Rsall ("Shipinfs")
Xlworkbook.worksheets (1). Cells (2+j,14). Value = Rsall ("Zuopinshu")
Xlworkbook.worksheets (1). Cells (2+j,15). Value = Rsall ("Zuopinfenshu")

J=j+1
Rsall.movenext
LOOP
Xlworkbook.saveas tfile//Save File

Set xlworksheet = Nothing//release sheet
Xlapplication.quit//Releasing objects
Generate links for users to download
Response.Write ("<center><table width= ' align= ' center ' ><tr><td> export success! Click to download: <a href= ' jftj.xlsx ' >jftj.xlsx</a></td></tr></table></center> ')
Response. Write ("<script><alert>excel export is successful, please click to download below the list!") </alert></script> ")
END IF

The program is finished, then to run from the server, you also need to install Word components to the server, the installation of Word is no longer described, the installation of Word also to give IIS to use the Excel component in the Word component of the use of permissions, by Win+r, run the box input DCOMCNFG, Then select "Component Services"-"computer-" My Computer--"DCOM Config, find Microsoft Excel application, then right--" properties--"security--" Launch and Activation permissions--"To customize, and then select Edit, Then add everyone, then assign permissions to everyone, and then select the Identities tab to change to an interactive user.

The above installation is configured for Excel components because, 1, if you do not configure the "Start and activate" permission on the server. CreateObject ("Excel.Application"), the server will error, no permissions. 2, if not configured to identify this thing, in the execution of "Set xlworkbook=xlapplication.workbooks.add ' Add Workbook", will be reported insufficient memory please close related errors.

This record is complete.

Some problems encountered by ASP exporting data to Excel