NPOI SET ACTIVE SHEET

If workbook contains more than one sheets and you want to set a active sheet. This sample show you how to set activate a specific sheet in your workbook.

''=====================================

Imports NPOI

Imports NPOI.SS

Imports NPOI.SS.UserModel

Imports NPOI.HSSF.Util

Imports NPOI.HSSF.UserModel

''=====================================

  Public Sub SetActiveSheet()

        Dim filePath As String = "C:\temp\Test_01.xls"

        Dim wb As HSSFWorkbook = Nothing

        Using fs As New FileStream(filePath, FileMode.Open, FileAccess.Read)

            wb = New HSSFWorkbook(fs)

        End Using

        ''アクティブなシートを設定

        wb.SetActiveSheet(1)

        wb.SetSelectedTab(1)

        Dim newSavePath As String = "C:\temp\Test_02.xls"

        Using fs As New FileStream(newSavePath, FileMode.Create, FileAccess.Write)

            wb.Write(fs)

        End Using

    End Sub

Excel Merge cells npoi vb.net

Imports NPOI.SS.UserModel

Imports NPOI.HSSF.Util  

''---------------------------------- 

Public Sub MergeCells()

        Dim srcWB As HSSFWorkbook = Nothing

        Dim filePath = "C:\test.xls"

        Using fs As New FileStream(filePath, FileMode.Open, FileAccess.Read)

            srcWB = New HSSFWorkbook(fs)

        End Using

        Dim sheet As HSSFSheet = srcWB.GetSheetAt(0)

      

        'Set row number

        Dim startRow = 2

        Dim endRow = 4

        'Set column number

        Dim startColumn = 2

        Dim endColumn = 2

        sheet.AddMergedRegion(New CellRangeAddress(startRow, endRow, startColumn, endColumn))

        Dim savePath As String = "C:\test1.xls"

        Using fs As New FileStream(savePath, FileMode.Create, FileAccess.Write)

            srcWB.Write(fs)

        End Using

        MsgBox("done")

    End Sub

NPOI set custom cell background color [RGB format]

    Public Sub SetCellBackColorCustomColorRGB()

        Dim srcWB As HSSFWorkbook = Nothing

        Dim filePath = "C:\test.xls"

        Using fs As New FileStream(filePath, FileMode.Open, FileAccess.Read)

            srcWB = New HSSFWorkbook(fs)

        End Using

        Dim sheet As HSSFSheet = srcWB.GetSheetAt(0)

        Dim cell As HSSFCell = sheet.GetRow(1).GetCell(1)

        Dim style As HSSFCellStyle = srcWB.CreateCellStyle

        'Get custom palette

        Dim palette = srcWB.GetCustomPalette

        'Set color index at palette

        palette.SetColorAtIndex(57, 184, 255, 113)

        'Set color from color palette

        style.FillForegroundColor = palette.GetColor(57).GetIndex

        style.FillPattern = FillPattern.SolidForeground

        cell.CellStyle = style

        Dim savePath As String = filePath

        Using fs As New FileStream(savePath, FileMode.Create, FileAccess.Write)

            srcWB.Write(fs)

        End Using

        MsgBox("done")

    End Sub

Output listview data in excel with npoi

Private Sub btnOutput_Click(sender As Object, e As EventArgs) Handles btnOutput.Click

        Try

            Using dlg As New SaveFileDialog

                With dlg

                    .Filter = "Excel Files (*.xls)|*.xls"

                    .FilterIndex = 1

                    .InitialDirectory = My.Application.Info.DirectoryPath

                    .FileName = Inputs.BaseLangName

                End With

                If dlg.ShowDialog = DialogResult.OK Then

                    Dim savePath As String = dlg.FileName

                    Dim openPath As String = Path.Combine(My.Application.Info.DirectoryPath, "ListViewData.xls")

                    Dim wb As HSSFWorkbook = Nothing

                    Using fs As New FileStream(openPath, FileMode.Open, FileAccess.Read)

                        wb = New HSSFWorkbook(fs, True)

                    End Using

                    Dim wSheet As HSSFSheet = wb.GetSheetAt(0)

                    Me.pbProgress.Value = 0

                    Me.pbProgress.Maximum = Me.lvBSData.Items.Count

                    Me.grpProgressInfo.Text = "Excelファイル作成中..."

                    Me.grpProgressInfo.Update()

                    'step through rows and columns and copy data to worksheet

                    Dim row As Integer = 1

                    Dim col As Integer = 0

                    For Each item As ListViewItem In Me.lvBSData.Items

                        Me.pbProgress.Value += 1

                        For i As Integer = 0 To item.SubItems.Count - 1

                            wSheet.SetCellStringValue(row, col, item.SubItems(i).Text)

                            col = col + 1

                        Next

                        row += 1

                        col = 0

                    Next

                    Using fs As New FileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write)

                        wb.Write(fs)

                    End Using

                    Me.pbProgress.Value = 0

                    Me.grpProgressInfo.Text = String.Empty

                    MessageBox.Show("Done!", "Result")

                End If

            End Using

        Catch ex As Exception

            MessageBox.Show(ex.Message, "Error!")

        End Try

    End Sub

CheckColumnOrRowIsHidden

Public Sub CheckColumnOrRowIsHidden()

        Dim wb As HSSFWorkbook = Nothing

        Using fs As New FileStream("C:\test.xls", FileMode.Open, FileAccess.Read)

            wb = New HSSFWorkbook(fs)

        End Using

        Dim wSheet As HSSFSheet = wb.GetSheet("sheetname")

        For rowNum As Integer = 0 To wSheet.LastRowNum

            Dim row As HSSFRow = wSheet.GetRow(rowNum)

            If row Is Nothing Then Continue For

            If row.IsHidden Then

                Debug.Print("Hiddn row number:" & rowNum + 1)

            End If

        Next

        Dim trgRowNum As Integer = 3

        Dim lastClmNum As Integer = wSheet.GetRow(trgRowNum).LastCellNum

        For clmNum As Integer = 0 To lastClmNum

            If wSheet.IsColumnHidden(clmNum) Then

                Debug.Print("Hiddn column number:" & clmNum + 1)

            End If

        Next

    End Sub