请选择导出的Excel文件导入:
<INPUT id="FileImport" style="WIDTH: 280px; HEIGHT: 19px" type="file" size="27" name="file"
runat="server">
<asp:button id="btnExport" runat="server" Text="导出" CssClass="button" oOnClientClick="return confirm('确定要导出查询内容吗？')" >
'JS方法:
function CheckExcel()
{
var obj=document.getElementById('FileImport');
if(obj.value =="")
{
window.alert('请选择Excel文件。');
return false;
}
else
{
var arr=obj.value.split(".");
if(arr[arr.length-1].toLowerCase()!="xls")
{
window.alert('请选择Excel文件。');
return false;
}
else
{
return true;
}
}
}
'后台方法:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.btnNext.Attributes.Add("onclick", "return CheckExcel();")
End Sub
'响应导出按钮的点击事件
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
'Dim msg As String = ToExcel(dgResult)
Dim msg As String = DatagridToExcel()
If Not msg.Equals("") Then
Response.Write("<script>alert ('导出出现异常" & msg & "');history.go(-1)</script>")
ErrorMsg.Text = "导出出现异常,异常信息:" & msg
End If
End Sub
'修饰datable
Private Function DatagridToExcel() As String
Dim dg As DataTable = New DataTable
If Not ViewState("DTDatagrid") Is Nothing Then
'获得需要导入的时候的datatable,需要先将导入的数据存到viewstate中
dg = ViewState("DTDatagrid")
Else
Return "未获得导出数据"
End If
dg.Columns.Remove("object_class")
dg.Columns("INT_ID").ColumnName = "网元ID"
dg.Columns("ZH_NAME").ColumnName = "属性"
dg.Columns("USERLABEL").ColumnName = "网元名称"
dg.Columns("ATT_VALUE").ColumnName = "当前值"
dg.Columns("ATT_PRE_VALUE").ColumnName = "上次的值"
dg.Columns("ATT_TIME").ColumnName = "变更为当前值的时间"
dg.Columns("ATT_PRE_TIME").ColumnName = "上次变更的时间"
Dim filename As String = Server.MapPath("template_export.xls")
Dim exfilename As String = "NEChangeLogInfo_Export_" & DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss") & ".xls"
Dim nfilename As String = Server.MapPath("~/Temp/") & exfilename
'Dim nfilename As String = Server.MapPath("Excel\") & exfilename
If File.Exists(nfilename) Then
File.Delete(nfilename)
End If
File.Copy(filename, nfilename)
Dim msg As String = expDataToExcel(nfilename, "YourData", dg, True)
If msg = Nothing Then
Try
Response.Clear()
Response.Charset = "utf-8"
Response.Buffer = True
Me.EnableViewState = False
Response.ContentEncoding = System.Text.Encoding.UTF8
Response.AddHeader("Content-Disposition", "attachment;FileName=" & System.Web.HttpUtility.UrlEncode(exfilename, System.Text.Encoding.UTF8))
Response.WriteFile(nfilename)
Response.Flush()
Response.Close()
Response.End()
Catch ex As Exception
Return "管理员相关:" & ex.Message
End Try
Else
Return "导出失败" & msg
End If
End Function
'将函数导出到excel的主函数,fileName:导出的excel名称,sheetname:导出的sheet页名称,datatable:需要导出数据的datatable,是否导出列头.
Public Function expDataToExcel(ByVal fileName As String, ByVal sheetName As String, ByVal dt As DataTable, ByVal hasColsName As Boolean) As String
If fileName Is Nothing Then
Return "文件名为空"
End If
If sheetName Is Nothing Then
sheetName = "Sheet1"
End If
If dt Is Nothing Then
Return "内存数据表为空"
End If
Dim ret As String = initExcel(fileName, sheetName)
If Not ret Is Nothing Then
Return ret
End If
Dim colsName(dt.Columns.Count - 1) As String
If hasColsName Then
For i As Integer = 0 To dt.Columns.Count - 1 Step 1
colsName(i) = dt.Columns(i).ColumnName
Next
Else
colsName = Nothing
End If
Return writeDataToExcel(fileName, sheetName, dt, colsName)
End Function
'初始化Exel
Private Function initExcel(ByVal fileName As String, ByVal sheetName As String) As String
Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlsApp As New Microsoft.Office.Interop.Excel.Application
Dim sheet As Microsoft.Office.Interop.Excel.Worksheet
Try
If File.Exists(fileName) Then
xlsBook = xlsApp.Workbooks.Open(fileName)
For i As Integer = 1 To xlsBook.Worksheets.Count Step 1
If sheetName.Equals(xlsBook.Worksheets(i).Name) Then
sheet = xlsBook.Worksheets(i)
xlsBook.Close()
Exit For
End If
Next
If sheet Is Nothing Then
sheet = xlsBook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
sheet.Name = sheetName
xlsBook.Saved = True
xlsBook.SaveCopyAs(fileName)
xlsBook.Close()
End If
Else
xlsBook = xlsApp.Workbooks.Add(True)
sheet = xlsBook.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
sheet.Name = sheetName
xlsBook.Saved = True
xlsBook.SaveCopyAs(fileName)
xlsBook.Close()
End If
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
sheet = Nothing
xlsBook = Nothing
xlsApp = Nothing
GC.Collect()
Catch ex As Exception
If Not xlsBook Is Nothing Then
xlsBook.Close()
End If
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
sheet = Nothing
xlsBook = Nothing
xlsApp = Nothing
GC.Collect()
Return ex.Message
End Try
Return Nothing
End Function
'将数据导入到Excel
Private Function writeDataToExcel(ByVal fileName As String, ByVal sheetName As String, ByVal dt As DataTable, ByVal colsName() As String) As String
Dim xlsApp As New Microsoft.Office.Interop.Excel.Application
Dim xlsBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlsSheet As Microsoft.Office.Interop.Excel.Worksheet
Try
xlsBook = xlsApp.Workbooks.Open(fileName)
xlsSheet = xlsBook.Worksheets(sheetName)
Catch ex As Exception
If Not xlsBook Is Nothing Then
xlsBook.Close()
End If
xlsApp.Quit()
Return ex.Message
End Try
Dim rowIdx As Integer = 1
If Not colsName Is Nothing Then
If colsName.Length > 0 Then
For i As Integer = 0 To colsName.Length - 1 Step 1
Dim rg As Microsoft.Office.Interop.Excel.Range = xlsSheet.Cells(rowIdx, i + 1)
rg.Value = colsName(i)
Next
rowIdx = 2
End If
End If
For r As Integer = 0 To dt.Rows.Count - 1 Step 1
For n As Integer = 0 To dt.Columns.Count - 1 Step 1
dataArry(r, n) = Convert.ToString(dt.Rows(r)(n))
Next
rowIdx = rowIdx + 1
Next
Dim temprg As Microsoft.Office.Interop.Excel.Range
temprg = xlsSheet.Range(xlsSheet.Cells(2, 1), xlsSheet.Cells(dt.Rows.Count + 1, dt.Columns.Count))
temprg.Value = dataArry
Try
xlsBook.Save()xlsBook.Close()
xlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet)
xlsSheet = Nothing
xlsBook = Nothing
xlsApp = Nothing
GC.Collect()
Catch ex As Exception
Return ex.Message
End Try
Return Nothing
End Function