excel| Export excel| Data
first, the use of OWC
What is OWC?
OWC is the abbreviation for Office Web compent, Microsoft's Office Web Component, which provides a flexible and basic mechanism for drawing graphics on the web. In an intranet environment, if you can assume that there are specific browsers and powerful software (such as IE5 and Office 2000) on the client, you have the ability to use the Office Web Component to provide an interactive graphical development environment. In this mode, the client workstation will share a significant proportion of the entire task.
<%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 Excel
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 ' centered
Icol = Icol + 1
Next ' Objfield
' Display all ' of the data
Do as 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 '
<a Href= ' "& Server. UrlEncode (Excelpath) & "' > download </a>"
Else
Response.Write "There are errors in the save process!"
End If
Set Objexcel = Nothing
Objrs.close
Set objRS = Nothing
%>
exporting to Excel or Word in the client with Excel application components
Note: "Data" in two functions is the ID of the table to be exported in the Web page
<input type= "hidden" name= "Out_word" value= "exported to Word" class= "notprint"
<input type= "hidden" name= "Out_excel" 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 ("Consolidated query result set")//Show table title
ObjWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore ("")
Set Rngpara = objWordDoc.Application.ActiveDocument.Paragraphs (1). Range
With Rngpara
. Bold = True//set title to Bold
. ParagraphFormat.Alignment = 1//Center the title
. Font.Name = "Official script"//Set Title font
. Font.Size = 18//Set Title font size
End With
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>
open in IE directly, then Save as Excel file
read out the data in a <table> format, displayed in the Web page, at the same time, add the next sentence can be displayed in the Guest client Excel table.
<%response. ContentType = "Application/vnd.ms-excel"%>
Note: In the displayed page, only the <table> output, preferably do not output other forms of information.
Iv. exporting CSV separated by half-width commas
Method of generating a text file with the FSO method, generating an extension of CSV file. This file, a row is a row of the datasheet. Generate a data table field separated by a half-width comma. (for the FSO to generate a text file, this does not introduce the method)
CSV file Introduction (comma-delimited file)
Selecting the system will create a downloadable CSV file; CSV is the most common file format that can be easily imported into a variety of PC tables and databases.
Note that even if you select a table as the output format, you can still download the results to the CSV file. At the bottom of the table output screen, the CSV file option appears, and you can download the file by clicking it.
If you configure your browser to associate your spreadsheet software with a text (TXT)/comma-separated file (CSV), the file will automatically open when you download the file. After downloading, if you have Excel installed locally, click this file to automatically open the file with Excel software.
