vb.net版NPOI读写Excel模版

【实例简介】

NPOI用的版本是2.5.6.0，使用最新版自动引用的文件太多不会搞，请有缘人指点。。。。。

自动判断读取的是xls、xlxs文件。

【实例截图】

【核心代码】

Imports NPOI.HSSF.Record.Crypto
Imports NPOI.HSSF.UserModel

Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel
Imports System.IO


Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        日期.Value = DateTime.Now
    End Sub

    Private FilePath As String


    Public Sub ExcelReadWrite()
        Dim newfile As FileStream
        Dim file As FileStream = New FileStream(FilePath, FileMode.Open, FileAccess.Read)
        Dim hzm As String = System.IO.Path.GetExtension(FilePath)
        Dim wook ' As Object
        Dim sheet1 As ISheet

        'excel文件后缀不一样，打开方式不一样
        If hzm.Equals(".xls") Then
            Dim Hwk As HSSFWorkbook = New HSSFWorkbook(file)
            wook = Hwk
        Else
            Dim Xwk As XSSFWorkbook = New XSSFWorkbook(file)
            wook = Xwk
        End If

        sheet1 = wook.GetSheet("Sheet1")
        sheet1.GetRow(2).GetCell(0).SetCellValue(姓名.Text)
        sheet1.GetRow(2).GetCell(1).SetCellValue(性别.Text)
        sheet1.GetRow(2).GetCell(2).SetCellValue(Int32.Parse(年龄.Text))
        sheet1.GetRow(2).GetCell(3).SetCellValue(部门.Text)
        sheet1.GetRow(2).GetCell(4).SetCellValue(日期.Text)
        Dim newpath As String = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        newfile = New FileStream(newpath & "\new.xlsx", FileMode.Create)
        wook.Write(newfile)
        newfile.Close()

    End Sub


    Private Sub 浏览按钮_Click(sender As Object, e As EventArgs) Handles 浏览按钮.Click
        Dim openFileDialog As OpenFileDialog = New OpenFileDialog()
        openFileDialog.Title = "打开表格模板"
        openFileDialog.Filter = "*.xls;*.xlsx|*.xls;*.xlsx"

        If openFileDialog.ShowDialog = DialogResult.OK Then
            FilePath = openFileDialog.FileName
            textBox1.Text = FilePath
        End If
    End Sub

    Private Sub 生成到桌面_Click(sender As Object, e As EventArgs) Handles 生成到桌面.Click
        ExcelReadWrite()
        MsgBox("完成")
    End Sub

    Private Sub 读取加密的_Click(sender As Object, e As EventArgs) Handles 读取加密的.Click
        Dim newfile As FileStream
        Dim file As FileStream = New FileStream(FilePath, FileMode.Open, FileAccess.Read)
        Dim hzm As String = System.IO.Path.GetExtension(FilePath)
        'Dim num As Integer = Int32.Parse(年龄.Text)
        Dim wook As Object
        Dim sheet1 As ISheet
        Dim password As String = "" '

        Biff8EncryptionKey.CurrentUserPassword = "123456"
        'excel文件后缀不一样，打开方式不一样
        If hzm.Equals(".xls") Then
            Dim Hwk As HSSFWorkbook = New HSSFWorkbook(file)
            '    Hwk.WriteProtectWorkbook("123456", "")
            wook = Hwk
        Else
            Dim Xwk As XSSFWorkbook = New XSSFWorkbook(file)
            wook = Xwk
        End If



        sheet1 = wook.GetSheet("Sheet1")
        sheet1.GetRow(2).GetCell(0).SetCellValue(姓名.Text)
        sheet1.GetRow(2).GetCell(1).SetCellValue(性别.Text)
        sheet1.GetRow(2).GetCell(2).SetCellValue(Int32.Parse(年龄.Text))
        sheet1.GetRow(2).GetCell(3).SetCellValue(部门.Text)
        sheet1.GetRow(2).GetCell(4).SetCellValue(日期.Text)
        Dim newpath As String = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        newfile = New FileStream(newpath & "\new.xlsx", FileMode.Create)
        wook.Write(newfile)
        newfile.Close()

    End Sub
End Class