Friday, May 29, 2015

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

No comments:

Post a Comment