1. Using OWC
What is OWC?
OWC is the abbreviation of Office Web Compent, which is Microsoft's Office Web Component. It provides a flexible and basic mechanism for drawing graphics on the Web. In an intranet environment, if it can be assumed that a specific browser and some powerful software (such as IE5 and Office 2000) exist on the client computer, then it is possible to use Office Web Components to provide an interactive graphical development environment. In this mode, the client workstation will share a large proportion of the entire task.
<%Option Explicit
ClassExcelGen
Private objSpreadsheet
Private iColOffset
Private iRowOffset
SubClass_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 'Centered
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
DimExcelPath
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'＞Saved as Excel file.
＜a href='" & server.URLEncode(ExcelPath) & "'＞Download＜/a＞ "
Else
Response.Write "An error occurred during saving!"
End If
Set objExcel = Nothing
objRS.Close
Set objRS = Nothing
%>
2. Use the Excel Application component to export to Excel or Word on the client.
Note: "data" in the two functions is the id of the table to be exported in the web page
<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<hang;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 //Make the title bold
.ParagraphFormat.Alignment = 1 //Center the title
.Font.Name = "official script" //Set the title font
.Font.Size = 18 //Set the 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＞
3. Open it directly in IE, and then save it as an EXCEL file.
Use the <table> format to display the read data on the web page. At the same time, add the next sentence to display the EXCEL table on the client.
<%response.ContentType ="application/vnd.ms-excel"%>
Note: In the displayed page, only <table> is output. It is best not to output information other than other tables.
4. Export csv separated by half-width commas.
Use the fso method to generate a text file and generate a csv file with an extension. In this file, one line is one line of the data table. Generate data table fields separated by half-width commas. (The method of generating text files by fso will not be introduced here)
Introduction to CSV files (comma separated files)
Select this option and the system will create a CSV file for download; CSV is the most common file format. Can be imported into various PC forms and databases very easily.
Please note that even if you select Table as the output format, you can still download the results as a CSV file. At the bottom of the table output screen, there is a "CSV file" option, click on it to download the file.
If you configure your browser to associate your spreadsheet software with text (TXT)/comma delimited files (CSV), the file will automatically open when you download it. After downloading, if EXCEL is installed locally, click this file to automatically open it with EXCEL software.