8721 0
2016-03-16 Dodu 业界资讯

前言

Asp.net操作Excel已经是老生长谈的事情了,可下面我说的这个NPOI操作Excel,应该是最好的方案了,没有之一,使用NPOI能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件,支持的文件格式包括xls, doc, ppt等。NPOI是构建在POI 3.x版本之上的,它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

方法

先去官网:http://npoi.codeplex.com/下载需要引入dll(可以选择.net2.0或者.net4.0的dll),然后在网站中添加引用。

导出代码:

Dim book As New NPOI.HSSF.UserModel.HSSFWorkbook()
Dim sheet As NPOI.SS.UserModel.ISheet = book.CreateSheet("test_01")

' 第一列
Dim row As NPOI.SS.UserModel.IRow = sheet.CreateRow(0)
row.CreateCell(0).SetCellValue("第一列第一行")

' 第二列
Dim row2 As NPOI.SS.UserModel.IRow = sheet.CreateRow(1)
row2.CreateCell(0).SetCellValue("第二列第一行")

' ...

' 写入到客户端  
Dim ms As New System.IO.MemoryStream()
book.Write(ms)
Response.AddHeader("Content-Disposition", String.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")))
Response.BinaryWrite(ms.ToArray())
book = Nothing
ms.Close()
ms.Dispose()
导入代码:
Private hssfworkbook As HSSFWorkbook
#Region ""
Public Function ImportExcelFile(filePath As String) As DataTable
	'#Region ""
	Try
		Using file As New FileStream(filePath, FileMode.Open, FileAccess.Read)
			hssfworkbook = New HSSFWorkbook(file)
		End Using
	Catch e As Exception
		Throw e
	End Try
	'#End Region

	Dim sheet As NPOI.SS.UserModel.Sheet = hssfworkbook.GetSheetAt(0)
	Dim rows As System.Collections.IEnumerator = sheet.GetRowEnumerator()
	Dim dt As New DataTable()
	For j As Integer = 0 To (sheet.GetRow(0).LastCellNum) - 1
		dt.Columns.Add(Convert.ToChar((AscW("A"C)) + j).ToString())
	Next
	While rows.MoveNext()
		Dim row As HSSFRow = DirectCast(rows.Current, HSSFRow)
		Dim dr As DataRow = dt.NewRow()
		For i As Integer = 0 To row.LastCellNum - 1
			Dim cell As NPOI.SS.UserModel.Cell = row.GetCell(i)
			If cell Is Nothing Then
				dr(i) = Nothing
			Else
				dr(i) = cell.ToString()
			End If
		Next
		dt.Rows.Add(dr)
	End While
	Return dt
End Function
#End Region

发表评论: