主题：[分享]由后台直接导出为Excel文档

1.添加一个名为SQLExporte的内部函数，其代码如下：

'申明变量时可以用逗号分隔

Dim st As Date = Now(),et As Date,sp As New TimeSpan

Dim cmd As New SQLCommand,dt As DataTable

cmd.ConnectionName = Args(2)

cmd.CommandText = "Sel ect * From {" & Args(0) & "}"

dt = cmd.ExecuteReader() '通过SQL命令生成临时表(DataTable)

 

Dim app As New MSExcel.Application

Dim wb As MSExcel.WorkBook = app.WorkBooks.Add()

'获取工作簿中的第一张工作表

Dim ws As MSExcel.WorkSheet = wb.WorkSheets(1)

Dim j As Integer = 1

'将临时表的列名写入工作表的第一行

For Each dc As DataCol In dt.DataCols

    ws.Cells(1,j) = dc.Name:j += 1

Next

 

'--------------------通过双循环将表中的数据写入二维数组------------------------------

Dim values(dt.DataRows.Count-1,dt.DataCols.Count-1)

For i As Long = 0 To dt.DataRows.Count - 1

    j = 0

    For Each dc As DataCol In dt.DataCols

        values(i,j) = dt.DataRows(i)(dc.Name)

        j += 1

    Next

Next

'将二维数组中的值批量一次性写入Excel工作表中

ws.Range(ws.Cells(2,1),ws.Cells(dt.DataRows.Count+1,dt.DataCols.Count)).Value = values

'---------------批量读写较单次读写(以2.2万数据为例测试)快了竟270倍--------------------

 

'通过双循环将临时表的数据写入工作表中

'For i As Long = 0 To dt.DataRows.Count - 1:j = 1

    'For Each dc As DataCol In dt.DataCols

        'ws.Cells(i+2,j) = dt.DataRows(i)(dc.Name):j += 1

    'Next

'Next

 

'冒号可以分隔多条短语句

wb.SaveAs(Args(1)):app.Quit():et = Now():sp = et - st

MessageBox.Show("开销: " & sp.TotalSeconds & " 秒","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Information)

2.函数调用：

Functions.Execute("SQLExporte",TableName,ExcelName,ConnectionName)

TableName 字符型，要导出的表名

ExcelName 字符型，要保存的Excel文件(含完整的路径)

ConnectionName 字符型，数据源名称(若为内部数据源，则指定为空串)

mark，谢谢分享。