如何將Web上DataGrid內的數據庫導入Excel或Word…
這裡列舉將Web上DataGrid內的數據庫導入Excel或Word的兩種方法：
方式一：在Add References 中添加 Com --à Microsoft Office Web Components 9.0
在程序中添加Imports OWC
即運Office 的組件進行編程序來處理datagrid中的數據，整個過程，是循環從datagrid 中將數據取出放excel 中…,由於表頭處理不好，表頭得自已定重添加上去…
好處：據有靈活的自定議性
缺點：不適合批量共用..
Private Sub Out_Excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Out_Excel.Click
Dim objExcel As New OWC.Spreadsheet
Dim myRow As DataRow
Dim myColumn As New DataColumn
Dim RowCount As Object
Dim ColumnCount As Object
Dim FileName As String = "finished_analyse.xls"
Dim FileAndPath As String = "C:/" & FileName
Dim xlsheet As New SpreadsheetClass
Dim i As Integer = 0
Dim j As Integer = 0
Dim oItem As DataGridColumn
For Each oItem In dgd_result.Columns
xlsheet.ActiveSheet.Cells(1, i + 1) = oItem.HeaderText
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Color = "red"
i = i + 1
Next
Dim numbercols As Integer = dgd_result.Items.Item(0).Cells.Count
For j = 0 To dgd_result.Items.Count - 1
For i = 0 To numbercols - 1
xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
'xlsheet.Range(xlsheet.Cells(100, 100), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
xlsheet.Range(xlsheet.Cells(90, 90), xlsheet.Cells(j + 2, i + 1)).Cells.ColumnWidth = 90
'xlsheet.Range(xlsheet.Cells(100, 100), xlsheet.Cells(j + 2, i + 1)).Columns.ColumnWidth = 100
'xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
'xlsheet.Range(xlsheet.Cells(100, 100), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
xlsheet.ActiveSheet.Cells(j + 2, i + 1) = dgd_result.Items.Item(j).Cells(i).Text.Replace(" ", " ")
Next
Next
xlsheet.ActiveSheet.Columns.InsertColumns(0)
xlsheet.ActiveSheet.Rows.InsertRows(0)
xlsheet.ActiveSheet.Cells(1, 1) = "列1"
xlsheet.ActiveSheet.Cells(1, 2) = "列2"
xlsheet.ActiveSheet.Cells(1, 3) = "列3"
xlsheet.ActiveSheet.Cells(1, 4) = "列4"
xlsheet.ActiveSheet.Cells(1, 5) = "列5"
xlsheet.ActiveSheet.Cells(1, 6) = "列6"
xlsheet.ActiveSheet.Cells(1, 14) = ""
xlsheet.ActiveSheet.Rows(1).Font.Bold = True
Try
xlsheet.ActiveSheet.Export(FileAndPath, OWC.SheetExportActionEnum.ssExportActionNone)
' xlsheet.ActiveSheet.Export(Server.MapPath(".") + "/Images/" + Me.xlfile.Text, OWC.SheetExportActionEnum.ssExportActionNone)
' xlsheet.ActiveSheet.Export(Server.MapPath("xlsdata") + "//" + Me.xlfile.Text, OWC.SheetExportActionEnum.ssExportActionNone)
Catch ex As System.Runtime.InteropServices.COMException
Response.Write("xx：" + ex.Message)
End Try
Response.ContentType = "application/octet-stream"
Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName)
Response.Flush()
Response.WriteFile(FileAndPath)
End Sub
‘------------------------------------------------------------------------------------------
方式二：運用Asp.net 中讀取頁面上Control內的數據流的形式來導出到excel 或 Work…
好處：使用方便，簡單，代碼簡捷..
缺點：不適合自定義控制…
如下面的這個函數：
Public Function ExportDataToFile(ByVal ibool_excel_or_word As Boolean)
Response.Clear()
Response.Buffer = True
Response.Charset = "Big5"
If ibool_excel_or_word Then
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls")
Response.ContentType = "application/ms-excel"
Else
Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.word")
Response.ContentType = "application/ms-word"
End If
Me.EnableViewState = False
Dim CItrad As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("zh-TW", True)
Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter(CItrad)
Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)
Me.dgd_result.DataSource = Me.QueryResult
Me.DataBind()
Me.dgd_result.Visible = True
Me.dgd_result.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter.ToString())
Response.End()
Me.dgd_result.Visible = False
End Function
效果圖：
