说来VB.Net这个也是之前的一个项目中用到的。今天拿来总结下用途,项目需求,不让在服务器安装Office办公软件。这个也是煞费了一顿.主要的思路就是 在导出的时候,利用DataTable做中间变量导出
下边这个是使用时候的代码 getData()方法是我获取的DataTable "defaultPickUpLocation" 是表的名字
'導出Excel Protected Sub btnExport_Click(ByVal sender As Object,ByVal e As EventArgs) Handles btnExport.Click Dim dt As DataTable = getData() GridViewHelper.ExportToExcel(dt,"defaultPickUpLocation_") End Sub
Shared Sub ExportToExcel(ByVal dataTable As Data.DataTable,ByVal fileName As String) For Each dataRow As DataRow In dataTable.Rows For i As Integer = 0 To dataRow.ItemArray.Count - 1 If Not IsDBNull(dataRow.Item(i)) Then dataRow.Item(i) = removeHTMLTagat(CType(dataRow.Item(i),String)) End If Next Next Export2Excel(dataTable,fileName)
因为数据库中总有一些是Table的字节 现在要删除
Private Shared Function removeHTMLTagat(ByVal HTML As String) As String Dim Codehtml() As KeyValuePair(Of String,String) = New KeyValuePair(Of String,String)() { New KeyValuePair(Of String,String)("<","<"),New KeyValuePair(Of String,String)(">",">"),String)(""," ") } For Each keyValuePair As KeyValuePair(Of String,String) In Codehtml Dim regex As New Regex(keyValuePair.Key) HTML = regex.Replace(HTML,keyValuePair.Value) Next Dim aryReg() As String = New String() { "<[^>]*>"} For Each pattern As String In aryReg Dim regex As New Regex(pattern) HTML = regex.Replace(HTML,"") Next Return HTML End Function
还有Export2Excel的具体导出Excel的方法
Private Shared Sub Export2Excel(ByVal _dataTable As DataTable,ByVal _filename As String) Dim fileName = String.Format("{0}{1}.xls",_filename,DateTime.Now.ToString("yyMMddHHmmssffff")) Dim sNewFullFile = String.Format("{0}Downfiles/{1}",HttpContext.Current.Request.PhysicalApplicationPath,fileName) File.Copy(String.Format("{0}format_files/{1}format.xls",_filename),sNewFullFile) Dim strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'" Dim conn = New OleDbConnection(strConn) Dim cmd As OleDbCommand = Nothing Dim bRet = False conn.Open() Dim insertDataStr = "INSERT INTO [Sheet1$] (" Dim insertValueStr = "values(" For i As Integer = 0 To _dataTable.Columns.Count - 1 insertDataStr = String.Format("{0} [{1}],",insertDataStr,_dataTable.Columns(i).ColumnName) insertValueStr = String.Format("{0}{1},insertValueStr,"?") Next insertValueStr = String.Format("{0})",insertValueStr.Trim(","c)) insertDataStr = String.Format("{0}) {1}",insertDataStr.Trim(","c),insertValueStr) cmd = New OleDbCommand(insertDataStr,conn) For i As Integer = 0 To _dataTable.Columns.Count - 1 cmd.Parameters.Add(i.ToString(),OleDbType.LongVarChar) Next For Each row As DataRow In _dataTable.Rows For i As Integer = 0 To _dataTable.Columns.Count - 1 Dim value = row(_dataTable.Columns(i).ColumnName).ToString() If value.Length > 100 Then value = String.Format("{0}……",value.Substring(0,100)) End If cmd.Parameters(i).Value = value Next cmd.ExecuteNonQuery() Next bRet = True cmd.Dispose() conn.Dispose() If bRet Then HttpContext.Current.Response.Redirect(String.Format("Downfiles/{0}",fileName)) End If End Sub现在就写了这个文件,等把导入再写。如有问题 请回复 祝君成功。