VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > VB.net教程 >
  • 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)

        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

相关教程