vb.net读取写入EXCEl的终极方法 |
您所在的位置：网站首页 › npoi导入excel到数据库 › vb.net读取写入EXCEl的终极方法 |
vb.net读取写入EXCEl的终极方法
|
据我浅薄的知识，之前只知道两种操作excel的方法： 一。ODBC操作excel看下图：
2.OLEDB与ODBC OLEDB：一个基于COM（Componet Object Model，DOS可执行命令文件）的数据存储对象，能提供对所有类型的数据的操作。 我们必须知道，ODBC 是为访问关系型数据库而专门开发的，OLE DB 则用于访问关系型和非关系型信息源，例如主机 ISAM/VSAM 和层次数据库，电子邮件和文件系统存储，文本、图形和地理数据以及自定义业务对象。 OLEDB其实和ODBC类似，都是负责数据库连接的低级接口。而OLEDB不仅支持结构化数据库如SQL server，Oracel Access等，而且还支持非结构化数据源的连接如EXCEL等。 OLEDB（对象连接和嵌入数据库），是一组对象的集合，一种读写数据的方法。在使用OLEDB时，使用步骤为：初始化OLE、连接到数据源、发出命令、处理结果、释放数据源对象并停止初始化OLE。他可以通过ODBC连接到数据库，也可以之间连接到数据库。当不通过ODBC时，这时在这其中并不涉及到驱动器的成分，因此，此时的数据库连接速度会更快。同时，我们可以知道，只要支持ODBC的数据源是一定能够支持OLEDB的。但是反过来则不一定。 也就是说OLEDB包含ODBC 3。ADO 是一个用于存取数据源的COM组件。它提供了编程语言和统一数据访问方式OLE DB的一个中间层。允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心到数据库的连接。访问数据库的时候，关于SQL的知识不是必要的，但是特定数据库支持的SQL命令仍可以通过ADO中的命令对象来执行。 https://www.cnblogs.com/dachuang/p/8615754.html https://blog.csdn.net/ithomer/article/details/6624684 二。OLEDB这种方式的有点速度非常快，但是excel数据量大的时候，会非常占用内存。当内存不够时会抛出内存溢出异常。 缺点是不能设置单元格格式。 1、操作Excel2007以前(不含2007)的连接字符串 string strConn = “Provider=Microsoft.Jet.OleDb.4.0;” + “data source=” + excelFilePath + “;Extended Properties=‘Excel 8.0; HDR=Yes; IMEX=1’”; //此连接只能操作Excel2007之前(.xls)文件 2、操作2007以后的Excel的连接字符串 string strConn = “Provider=Microsoft.Ace.OleDb.12.0;” + “data source=” + excelFilePath + “;Extended Properties=‘Excel 12.0; HDR=Yes; IMEX=1’”; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串) https://blog.csdn.net/qq_33459369/article/details/79308361 https://www.cnblogs.com/qixu/p/6140215.html 三。终极方法NOPI终于来到重点了。 NOPI 是开源的 用C#编写的读写excel、word等微软的OLE2组件文档的项目。 准备： NOPI nuget 包：https://www.nuget.org/packages/NPOI 或 我上传的https://download.csdn.net/download/weixin_43553234/10862591 注意：2.2.1亲测支持 .net 3.5，其余不确定，2.4.0不支持 .net 3.5，其余不确定。 用到再说。 步骤 1。导入NOPI Nuget包，具体见，https://blog.csdn.net/weixin_43553234/article/details/85108137。 2。调用用例，下面是我写的vb.net导出到EXCEL的类。 Imports System.Reflection Imports NPOI.SS.UserModel Imports NPOI.XSSF.UserModel Imports NPOI.HSSF.UserModel Imports System.IO Public Class NopiExcel Private workbook As IWorkbook '’工作簿 Private sheetList As List(Of ISheet) = New List(Of ISheet)() '’sheet列表 Private Shared suffixName As String = ".xls" Public Sub New(ByVal suffixName As String) If suffixName = ".xlsx" Then workbook = New XSSFWorkbook() ElseIf suffixName = ".xls" Then workbook = New HSSFWorkbook() End If suffixName = suffixName End Sub ''' ''' 共享方法，得到此计算机EXCEL表的后缀名 ''' ''' ''' Public Shared Function getSuffixName() Dim version As Double = checkExcelVer() If version = -1 Then suffixName = ".xls" ElseIf version >= 12 Then suffixName = ".xlsx" Else suffixName = ".xls" End If Return suffixName End Function ''' ''' 创建sheet表 ''' ''' sheet名 ''' ''' Public Function creatSheet(ByVal sheetName As String) As ISheet If workbook Is Nothing Then MsgBox("IWorkbook的实例为nothing", , "错误") Return Nothing End If Dim sheet As ISheet = workbook.CreateSheet(sheetName) sheetList.Add(sheet) Return sheet End Function ''' ''' 把dataTable的值写到excel ''' ''' Public Sub write(ByVal dataTable As DataTable, ByVal sheet As ISheet) If sheet Is Nothing Then MsgBox("ISheet的实例为nothing", , "错误") Return End If If dataTable Is Nothing Then MsgBox("DataTable的实例为nothing", , "错误") Return End If ''表头 Dim row As IRow = sheet.CreateRow(0) For j = 0 To dataTable.Columns.Count - 1 Dim cell As ICell = row.CreateCell(j) cell.SetCellValue(dataTable.Columns(j).ColumnName.ToString) Next For i = 0 To dataTable.Rows.Count - 1 row = sheet.CreateRow(i + 1) For j = 0 To dataTable.Columns.Count - 1 Dim cell As ICell = row.CreateCell(j) cell.SetCellValue(dataTable.Rows(i).Item(j).ToString) Next Next End Sub ''' ''' excel 工作簿保存 ''' ''' 保存路径 ''' Public Sub save(ByVal fileAddress As String) ''转为字节数组 Dim stream As MemoryStream = New MemoryStream() workbook.Write(stream) Dim buf = stream.ToArray() Dim fs As FileStream = New FileStream(fileAddress, FileMode.Create, FileAccess.Write) ''保存为Excel文件 Using (fs) fs.Write(buf, 0, buf.Length) fs.Flush() End Using End Sub ''' ''' 检测此计算机EXCEL的版本号 ''' ''' ''' Public Shared Function checkExcelVer() As Double Dim objExcelType As Type = Type.GetTypeFromProgID("Excel.Application") Dim objApp = Activator.CreateInstance(objExcelType) If objApp Is Nothing Then Return 0 End If Dim objVer = objApp.GetType().InvokeMember("Version", BindingFlags.GetProperty, Nothing, objApp, Nothing) If objVer Is Nothing Then Return -1 End If Dim iVer As Double = Convert.ToDouble(objVer) Return iVer End Function ''' ''' 得到EXCEL版本 ''' ''' ''' Public Function getExcelVerStr() As String Dim s1 As String Dim excelver As Double excelver = checkExcelVer() s1 = " Office " If excelver = Nothing Then MessageBox.Show("無法識別Excel的版本", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Information) s1 = "無法識別 office 版本" ElseIf (excelver >= 14) Then s1 += "2010或以上" ElseIf (excelver >= 12) Then s1 += "2007" ElseIf (excelver >= 11) Then s1 += "2003" ElseIf (excelver >= 10) Then s1 += "XP" ElseIf (excelver >= 9) Then s1 += "2000" ElseIf (excelver >= 8) Then s1 += "97" ElseIf (excelver >= 7) Then s1 += "95" End If MsgBox(excelver) Return s1 End Function ''' ''' 合并单元格 ''' ''' sheet名 ''' 要合并的列序号 ''' 开始的行序号 ''' 结束的行序号 ''' 开始和结束行序号的-维数组的量表 ''' Public Function mergerCell(ByVal sheet As ISheet, ByVal colIndex As Integer, ByVal beginRowsIndex As Integer, ByVal endRowsIndex As Integer) As List(Of Integer()) Dim preCellValue As String = sheet.GetRow(beginRowsIndex).Cells(colIndex).ToString Dim beginIndex As Integer = beginRowsIndex Dim beginEndArray As Integer(,) = Nothing Dim beginEndList As List(Of Integer()) = New List(Of Integer()) For i = beginRowsIndex To endRowsIndex Dim currentCellValue As String = sheet.GetRow(i).Cells(colIndex).ToString If Not currentCellValue = preCellValue Then If i > beginIndex + 1 Then sheet.AddMergedRegion(New NPOI.SS.Util.CellRangeAddress(beginIndex, i - 1, colIndex, colIndex)) ''***之前用数组实现的现在用List*** 'Dim len0 As Integer = 0 'If beginEndArray Is Nothing Then ' len0 = 0 'Else ' len0 = beginEndArray.GetLength(0) 'End If 'Dim tempArray As Integer(,) = beginEndArray 'ReDim beginEndArray(len0, 1) 'If Not tempArray Is Nothing Then ' For index = 0 To tempArray.GetLength(0) - 1 ' For j = 0 To tempArray.GetLength(1) - 1 ' beginEndArray(index, j) = tempArray(index, j) ' Next ' Next 'End If 'beginEndArray(len0, 0) = beginIndex 'beginEndArray(len0, 1) = i - 1 beginEndList.Add({beginIndex, i - 1}) End If beginIndex = i preCellValue = currentCellValue End If ''当遍历到表格最后一行时 If i = endRowsIndex And i > beginIndex Then sheet.AddMergedRegion(New NPOI.SS.Util.CellRangeAddress(beginIndex, i, colIndex, colIndex)) ''***之前用数组实现的现在用List*** 'Dim len0 As Integer = 0 'If beginEndArray Is Nothing Then ' len0 = 0 'Else ' len0 = beginEndArray.GetLength(0) 'End If 'Dim tempArray As Integer(,) = beginEndArray 'ReDim beginEndArray(len0, 1) 'If Not tempArray Is Nothing Then ' For index = 0 To tempArray.GetLength(0) - 1 ' For j = 0 To tempArray.GetLength(1) - 1 ' beginEndArray(index, j) = tempArray(index, j) ' Next ' Next 'End If 'beginEndArray(len0, 0) = beginIndex 'beginEndArray(len0, 1) = i beginEndList.Add({beginIndex, i}) End If Next Return beginEndList End Function End Class |
1.ODBC 由于初期连接不同的数据库通过各种不同的API，ODBC就是将各种不同的API封装成统一的接口。当sql语句进入接口时驱动器管理器会判断将他们送入对应的驱动器（driver），由对应的驱动将sql语句送入对应的数据库。 缺点：不同版本的excel，需要添加不同的ODBC数据源。【本文地址】
今日新闻 |
推荐新闻 |