vb.net读取写入EXCEl的终极方法-NOPI。

当前位置:

首页 > 编程开发 > vb >

vb.net读取写入EXCEl的终极方法-NOPI。

据我浅薄的知识，之前只知道两种操作excel的方法：

一。ODBC操作excel

看下图：

1.ODBC

由于初期连接不同的数据库通过各种不同的API，ODBC就是将各种不同的API封装成统一的接口。当sql语句进入接口时驱动器管理器会判断将他们送入对应的驱动器（driver），由对应的驱动将sql语句送入对应的数据库。

缺点：不同版本的excel，需要添加不同的ODBC数据源。

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

''' <summary>

''' 共享方法，得到此计算机EXCEL表的后缀名

''' </summary>

''' <returns></returns>

''' <remarks></remarks>

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

''' <summary>

''' 创建sheet表

''' </summary>

''' <param name="sheetName">sheet名</param>

''' <returns></returns>

''' <remarks></remarks>

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

''' <summary>

''' 把dataTable的值写到excel

''' </summary>

''' <remarks></remarks>

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)

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)

End Sub

''' <summary>

''' excel 工作簿保存

''' </summary>

''' <param name="fileAddress">保存路径</param>

''' <remarks></remarks>

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

''' <summary>

''' 检测此计算机EXCEL的版本号

''' </summary>

''' <returns></returns>

''' <remarks></remarks>

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

''' <summary>

''' 得到EXCEL版本

''' </summary>

''' <returns></returns>

''' <remarks></remarks>

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

''' <summary>

''' 合并单元格

''' </summary>

''' <param name="sheet">sheet名</param>

''' <param name="colIndex">要合并的列序号</param>

''' <param name="beginRowsIndex">开始的行序号</param>

''' <param name="endRowsIndex">结束的行序号</param>

''' <returns>开始和结束行序号的-维数组的量表</returns>

''' <remarks></remarks>

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

'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

'End If

'beginEndArray(len0, 0) = beginIndex

'beginEndArray(len0, 1) = i

beginEndList.Add({beginIndex, i})

End If

Return beginEndList

End Function

End Class

————————————————

原文链接：https://blog.csdn.net/weixin_43553234/article/details/85019177

栏目列表