1. Use owc
What is owc?
Owc is the abbreviation of Office Web compent, Microsoft's Office Web component. It provides
And is also the most basic mechanism. In an Intranet environment, assume that a specific browser and
Some powerful software (such as ie5 and Office 2000), you can use the Office Web component to provide an Interactive Graphic
Sending environment. In this mode, the client workstation will share a large proportion of the entire task.
You can also find the details on this site.
<% Option explicit
Class excelgen
Private objspreadsheet
Private icoloffset
Private irowoffset
Sub class_initialize ()
Set objspreadsheet = server. Createobject ("owc. spreadsheet ")
Irowoffset = 2
Icoloffset = 2
End sub
Sub class_terminate ()
Set objspreadsheet = nothing 'clean up
End sub
Public property let columnoffset (icoloff)
If icoloff> 0 then
Icoloffset = icoloff
Else
Icoloffset = 2
End if
End Property
Public property let rowoffset (irowoff)
If irowoff> 0 then
Irowoffset = irowoff
Else
Irowoffset = 2
End if
End property sub generateworksheet (objrs)
'Populates the Excel worksheet Based on A recordset's contents
'Start by displaying the titles
If objrs. EOF then exit sub
Dim objfield, icol, irow
Icol = icoloffset
Irow = irowoffset
For each objfield in objrs. Fields
Objspreadsheet. cells (irow, icol). value = objfield. Name
Objspreadsheet. Columns (icol). autofitcolumns
'Set the font in the Excel table
Objspreadsheet. cells (irow, icol). Font. Bold = true
Objspreadsheet. cells (irow, icol). Font. italic = false
Objspreadsheet. cells (irow, icol). Font. size = 10
Objspreadsheet. cells (irow, icol). halignment = 2' Center
Icol = icol + 1
Next 'objfield
'Display all of the data
Do while not objrs. EOF
Irow = irow + 1
Icol = icoloffset
For each objfield in objrs. Fields
If isnull (objfield. Value) then
Objspreadsheet. cells (irow, icol). value = ""
Else
Objspreadsheet. cells (irow, icol). value = objfield. Value
Objspreadsheet. Columns (icol). autofitcolumns
Objspreadsheet. cells (irow, icol). Font. Bold = false
Objspreadsheet. cells (irow, icol). Font. italic = false
Objspreadsheet. cells (irow, icol). Font. size = 10
End if
Icol = icol + 1
Next 'objfield
Objrs. movenext
Loop
End sub function saveworksheet (strfilename)
'Save the worksheet to a specified filename
On Error resume next
Call objspreadsheet. activesheet. Export (strfilename, 0)
Saveworksheet = (ERR. Number = 0)
End Function
End Class
Dim objrs
Set objrs = server. Createobject ("ADODB. recordset ")
Objrs. Open "select * from XXXX", "provider = sqloledb.1; persist Security
Info = true; user id = xxxx; Password = xxxx; initial catalog = xxxx; Data Source = xxxx ;"
Dim savename
Savename = request. Cookies ("savename") ("name ")
Dim objexcel
Dim excelpath
Excelpath = "Excel/" & savename & ". xls"
Set objexcel = new excelgen
Objexcel. rowoffset = 1
Objexcel. columnoffset = 1
Objexcel. generateworksheet (objrs)
If objexcel. saveworksheet (server. mappath (excelpath) then
'Response. Write "<HTML> <body bgcolor = 'gainsboro 'text = '#000000'> the file is saved as an Excel file.
<A href = '"& server. urlencode (excelpath) &"'> download </a>"
Else
Response. Write "An error occurred while saving! "
End if
Set objexcel = nothing
Objrs. Close
Set objrs = nothing
%>
2. Use the Excel application component to export data to excel or word on the client
Note: "data" in the two functions is the ID of the table to be exported from the webpage.
<Input type = "hidden" name = "out_word" onclick = "VBScript: builddoc" value = "export to word" class = "notprint">
<Input type = "hidden" name = "out_excel" onclick = "automateexcel ();" value = "export to excel" class = "notprint">
Export to Excel Code
<Script language = "JavaScript">
<! --
Function automateexcel ()
{
// Start Excel and get application object.
VaR oxl = new activexobject ("Excel. application ");
// Get a new workbook.
VaR owb = oxl. workbooks. Add ();
VaR osheet = owb. activesheet;
VaR table = Document. All. Data;
VaR hang = table. Rows. length;
VaR lie = table. Rows (0). cells. length;
// Add Table headers going cell by cell.
For (I = 0; I {
For (j = 0; j <lie; j ++)
{
Osheet. cells (I + 1, J + 1). value = table. Rows (I). cells (j). innertext;
}
}
Oxl. Visible = true;
Oxl. usercontrol = true;
}
// -->
</SCRIPT>
Export to Word Code
<Script language = "VBScript">
Sub builddoc
Set table = Document. All. Data
Row = table. Rows. Length
Column = table. Rows (1). cells. Length
Set objworddoc = Createobject ("word. Document ")
Objworddoc. application. Documents. Add thetemplate, false
Objworddoc. application. Visible = true
Dim thearray (20,10000)
For I = 0 to row-1
For J = 0 to column-1
Thearray (J + 1, I + 1) = table. Rows (I). cells (j). innertext
Next
Next
Objworddoc. application. activedocument. Paragraphs. Add. range. insertbefore ("comprehensive query result set") // display the table title
Objworddoc. application. activedocument. Paragraphs. Add. range. insertbefore ("")
Set rngpara = objworddoc. application. activedocument. Paragraphs (1). Range
With rngpara
. Bold = true // set the title to bold
. Paragraphformat. Alignment = 1 // center the title
. Font. Name = "" // set the title Font
. Font. size = 18 // set the title Font Size
End
Set rngcurrent = objworddoc. application. activedocument. Paragraphs (3). Range
Set tabcurrent = objworddoc. application. activedocument. Tables. Add (rngcurrent, row, column)
For I = 1 to Column
Objworddoc. application. activedocument. Tables (1). Rows (1). cells (I). range. insertafter thearray (I, 1)
Objworddoc. application. activedocument. Tables (1). Rows (1). cells (I). range. paragraphformat. Alignment = 1
Next
For I = 1 to Column
For J = 2 to row
Objworddoc. application. activedocument. Tables (1). Rows (j). cells (I). range. insertafter thearray (I, j)
Objworddoc. application. activedocument. Tables (1). Rows (j). cells (I). range. paragraphformat. Alignment = 1
Next
Next
End sub
</SCRIPT>
3. Open it directly in IE and save it as an Excel file.
Display the read data in <Table> format on the webpage. Add the following statement to display the Excel table on the client.
<% Response. contenttype = "application/vnd. MS-excel" %>
Note: Only <Table> is output on the displayed page. It is better not to output information other than the table.
4. Export CSV files separated by commas
Use the FSO method to generate a text file and generate a CSV file with the extension. This file contains one row of the data table. Generate data
Table fields are separated by commas.
The FSO method for generating text files is not described here. Relevant documents can be found on this site.
CSV file introduction (comma-separated files)
Selecting this option will create a CSV file for download. CSV is the most common file format and can be easily imported into various PC tables and databases.
Note that the CSV file can be downloaded even if you select a table as the output format. The "CSV file" option is displayed at the bottom of the table output screen. Click it to download the file.
If you configure your browser to associate your workbook software with a text (txt)/comma-separated file (CSV), the file is automatically opened when you download the file. After downloading the file, if Excel is installed locally, click this file to open it automatically using Excel software.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
