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

98 阅读 0 评论 65 点赞

我是靠谱客的博主安静摩托，这篇文章主要介绍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)
            Next

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

'    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

Return beginEndList
    End Function

End Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
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)

        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


    ''' <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

                    '    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