ASP导出Excel数据的四种方法

ASP导出Excel数据的四种方法,ASP导出Excel数据的四种方法

一、使用OWC

什么是OWC？

OWC是Office Web Compent的缩写，即Microsoft的Office Web组件，它为在Web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一些功能强大的软件（如IE5和Office 2000），那么就有能力利用Office Web组件提供一个交互式图形开发环境。这种模式下，客户端工作站将在整个任务中分担很大的比重。

＜%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 Recordsets 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

设置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 居中

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 text="#000000"＞已保存为Excel文件.

＜a href="" & server.URLEncode(ExcelPath) & ""＞下载＜/a＞"

Else

Response.Write "在保存过程中有错误!"

End If

Set objExcel = Nothing

objRS.Close

Set objRS = Nothing

%＞

二、用Excel的Application组件在客户端导出到Excel或Word

注意：两个函数中的“data“是网页中要导出的table的 id

＜input type="hidden" name="out_word" value="导出到word"＞

＜input type="hidden" name="out_excel" value="导出到excel"＞

导出到Excel代码

＜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