關於NPOI的一點補充和示例
2018-06-18 03:13:37来源:未知 阅读 ()
最近看到很多人分享NPOI的用法.
但是很多都不是完整示例或者並沒有實戰效果.
剛好最近有個VB.NET的項目有升級原有的oledb select sheet$的做法.
很明顯,NPOI有更好的穩定性和兼容性.
2進制的處理方式排除了server和client端有沒有office excel的諸多影響和限制.如office版本,系統編碼問題.以及server端是否需要安裝office excel.
2.0版本之後更是支持.xlsx格式等等..
那直接上代碼:
要先引用HSSF和XSSF(for .xlsx support).
下載Excel.沒有寫下載成xlsx的格式了.沒必要.如果一定要download成xlsx.使用對應的XSSF命名空間下的XSSFWorkbook等即可
Imports NPOI.HSSF.UserModel Imports NPOI.XSSF.UserModel Public Shared Function DownLoadToExcel(ByVal dt As DataTable) Dim book As HSSFWorkbook = New HSSFWorkbook() Dim sheet As HSSFSheet = book.CreateSheet("Your_Sheet_Name") Dim row As HSSFRow = sheet.CreateRow(0) For i = 0 To dt.Columns.Count - 1 row.CreateCell(i).SetCellValue(dt.Columns(i).ColumnName) Next For i = 0 To dt.Rows.Count - 1 Dim row2 As HSSFRow = sheet.CreateRow(i + 1) For j = 0 To dt.Columns.Count - 1 row2.CreateCell(j).SetCellValue(dt.Rows(i)(j).ToString) Next Next Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream() book.Write(ms) HttpContext.Current.Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}.xls", Now.ToString)) HttpContext.Current.Response.BinaryWrite(ms.ToArray()) book = Nothing ms.Close() ms.Dispose() End Function
上傳EXCEL.
通常我們實際做項目的時候,需要通過導入excel再進行處理而不是單純的重複insert DB.
那我們直接獲取datatable.
支持xls和.xlsx格式上傳. 這裏的lofile 就是HtmlInputFile控件.調用方法的時候直接將這個對象傳過來即可.
我這裏是web項目.如果是winform.lofile完全不用save之後再處理.直接傳path過來就可以.同樣請將對應的HttpContext改成Context.
Public Shared Function GetTabelFromExcel(ByVal lofile As HtmlInputFile) As DataTable Dim filepath As String = Path.Combine(HttpContext.Current.Server.MapPath("bin"), Path.GetFileName(lofile.PostedFile.FileName)) Dim fileExt As String = Path.GetExtension(lofile.PostedFile.FileName) Try lofile.PostedFile.SaveAs(filepath) Dim tempname As String = Guid.NewGuid().ToString Dim tempfilepath As String = Path.Combine(HttpContext.Current.Server.MapPath("bin"), tempname & fileExt) File.Move(filepath, tempfilepath) File.Delete(filepath) filepath = tempfilepath If fileExt = ".xlsx" Then '.xlsx 2007 Try Dim wb As XSSFWorkbook Dim file As System.IO.FileStream = New System.IO.FileStream(filepath, System.IO.FileMode.Open, System.IO.FileAccess.Read) Using file wb = New XSSFWorkbook(file) End Using Dim sheet As XSSFSheet = CType(wb.GetSheetAt(0), XSSFSheet) Dim rows As System.Collections.IEnumerator = sheet.GetRowEnumerator() Dim dt As DataTable = New DataTable() For i As Integer = 0 To CInt(sheet.GetRow(0).LastCellNum) - 1 dt.Columns.Add(System.Convert.ToChar(65 + i).ToString()) Next While rows.MoveNext() Dim row As XSSFRow = CType(rows.Current, XSSFRow) Dim dr As DataRow = dt.NewRow() For j As Integer = 0 To CInt(row.LastCellNum) - 1 Dim cell As XSSFCell = CType(row.GetCell(j), XSSFCell) If cell Is Nothing Then dr(j) = Nothing Else dr(j) = cell.ToString() End If Next dt.Rows.Add(dr) End While dt.Rows.RemoveAt(0) Return dt Catch e As System.Exception Throw New Exception("文件第一行不允許有空白欄位或文件正在使用,請先關閉正在打開的文件!" & e.Message) End Try ElseIf fileExt = ".xls" Then 'office 2003 Try Dim wb As HSSFWorkbook Dim file As System.IO.FileStream = New System.IO.FileStream(filepath, System.IO.FileMode.Open, System.IO.FileAccess.Read) Using file wb = New HSSFWorkbook(file) End Using Dim sheet As HSSFSheet = CType(wb.GetSheetAt(0), HSSFSheet) Dim rows As System.Collections.IEnumerator = sheet.GetRowEnumerator() Dim dt As DataTable = New DataTable() For i As Integer = 0 To CInt(sheet.GetRow(0).LastCellNum) - 1 dt.Columns.Add(System.Convert.ToChar(65 + i).ToString()) Next While rows.MoveNext() Dim row As HSSFRow = CType(rows.Current, HSSFRow) Dim dr As DataRow = dt.NewRow() For j As Integer = 0 To CInt(row.LastCellNum) - 1 Dim cell As HSSFCell = CType(row.GetCell(j), HSSFCell) If cell Is Nothing Then dr(j) = Nothing Else dr(j) = cell.ToString() End If Next dt.Rows.Add(dr) End While dt.Rows.RemoveAt(0) Return dt Catch e As System.Exception Throw New Exception("文件第一行不允許有空白欄位或文件正在使用,請先關閉正在打開的文件!" & e.Message) End Try Else Throw New Exception("請選擇office excel文件!") End If Catch ex As Exception Throw New Exception("請選擇office excel文件!" & ex.Message) Finally File.Delete(filepath) End Try End Function
得到datatable之後請自行處理.
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- c#.Net:Excel导入/导出之NPOI 2.0简介 2018-06-27
- C# WebForm 使用NPOI 2 生成简单的word文档(.docx) 2018-06-18
- NPOIHelper.cs (NPOI 2.1.1) 2018-06-18
- 总结一下工作中遇到的NPOI以及在ASP.NET MVC中的使用 2018-06-18
- 利用jQuery npoi插件 asxh一般处理文件实现excel的下载 2018-06-18
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash