如何将列表框项目导出到Excel？ [英] How do I export Listbox items to Excel?

问题描述

嘿伙计们！



我是VB.NET的初学者，所以请耐心等待我:)

所以，事实陈述如下：

- 我有一个带有列表框的表格。

- 我通过文本框手动将行插入该列表框。

- 列表框的最大项目是46行。

- 我想将列表框中的行导出到Excel工作表到指定范围。

- 如果列表框项目数大于23，我必须将工作表上的两列中的行分开。因此，工作表有两列23行。



问题是，我还没弄明白，如何编码。

更确切地说：我想在编译导出时请求一些帮助，并在代码中指定事实，当Listbox项大于23时，代码步进到下一列，并继续填写那里的数据。



提前谢谢你，干杯！

Adam

解决方案

< blockquote>你好，

首先你必须导入一些库

  Imports  Microsoft.Office.Interop 
  Imports  Excel = Microsoft.Office.Interop.Excel 

并使用此功能

 公共  Sub  ExportListToExcel（ ByVal  lv  As  ListBox）
 
 尝试 
  Dim  NewSaveFile  As  新 SaveFileDialog 
  Dim  iCol  As  整数 =  0  
  Dim  iRow 作为 整数 =  0  
  Dim  iPos  As  整数 =  0  
  Dim  RowCount  As  整数 =  0  
  Dim  tmpLine  As   String  =   
 
 < span class =code-keyword>使用 NewSaveFile 
 .FileName =   Excel_From_Listbox 
 .Filter =   Microsoft Excel 2007（* .xlsx）| * .xlsx | Microsoft Excel 2003（* .xls）| * .xls 
 .FilterIndex =  1  
 .RestoreDirectory =  True  
 结束 使用 
 
 如果 NewSaveFile.ShowDialog（）= DialogResult.OK 那么 
  Dim  ExApp 作为 Excel.Application 
  Dim  ExWorkbook  As  Excel.Workbook 
  Dim  ExSheet  As  Excel.Worksheet 
  Dim  tmpPath 正如 对象 = System.Reflection.Missing.Value 
 
 ExApp = 新 Excel.Application 
 ExWorkbook = ExApp.Workbooks.Add（tmpPath）
 ExSheet = ExWorkbook.Sheets（  Sheet1）
 使用 ExSheet 
 .Cells.Font.Name =   Calibri 
 .Cells.Font.Size =  12  
 使用 .PageSetup 
 .LeftFooter =  & 8Exported from VB.NET 
 .CenterFooter =  & 8Page& P Of& N 
 .RightFooter =  & 8& Today.ToLongDateString 
 结束 使用 
 结束 使用 
 
 RowCount = lv.Items.Count 
 对于 iRow =  0  要 RowCount  -   1  
 如果 iRow =  23  然后 
 iCol =  1  
 iPos =  1  
 其他 
 iPos + =  1  
 结束 如果 
 ExSheet.Cells（iPos，iCol +  1 ）= lv.Items（iRow）.ToString 
  Next  
 
 ExSheet.SaveAs（NewSaveFile.FileName）
 ExWorkbook.Close（）
 ExApp.Quit（）
 
 ReleaseObject（ExApp）
 ReleaseObject（ExWorkbook）
 ReleaseObject（ExSheet）
 
 MsgBox（  Export成功。）
 
 结束 如果 
 
  Catch  ex  As 异常
 MsgBox（Err.Number.ToString& ;  ：& ex.ToString）
 结束 尝试 
 
 结束  Sub  
 
 公开 < span class =code-keyword> Sub  ReleaseObject（ ByVal  obj  As  对象）
 
 尝试 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（ obj）
 
 obj = 没什么 
  Catch  ex  As 异常
 obj = 没什么 
 最后 
 GC.Collect（）
 结束 尝试 
 
 结束  Sub  

Hey Guys!

I'm beginner with VB.NET, so pls be patient with me :)
So, the statement of fact are the following:
- I have a form with a Listbox on it.
- I manually insert rows to that Listbox, via a Textbox.
- The max items of the listbox is 46 rows.
- I would like to export the rows in the Listbox to an Excel-sheet, to a specified range.
- If the Listbox items count is greater than 23, I must separate the rows in two columns on the sheet. So, the sheet has two columns with 23 rows.

The problem is, that I haven't figured out, how to code this.
More precisely: I'd like to ask some help in coding the exporting, and to specify the fact in the code, when the Listbox items are greater than 23, the code "steps" to the next column, and continue filling the data there.

Thank you in advance, cheers!
Adam

解决方案

Hello,
Firstly you have to import some libraries

Imports Microsoft.Office.Interop
Imports Excel = Microsoft.Office.Interop.Excel

And use this function

Public Sub ExportListToExcel(ByVal lv As ListBox)

        Try
            Dim NewSaveFile As New SaveFileDialog
            Dim iCol As Integer = 0
            Dim iRow As Integer = 0
            Dim iPos As Integer = 0
            Dim RowCount As Integer = 0
            Dim tmpLine As String = ""

            With NewSaveFile
                .FileName = "Excel_From_Listbox"
                .Filter = "Microsoft Excel 2007 (*.xlsx)|*.xlsx|Microsoft Excel 2003 (*.xls)|*.xls"
                .FilterIndex = 1
                .RestoreDirectory = True
            End With

            If NewSaveFile.ShowDialog() = DialogResult.OK Then
                Dim ExApp As Excel.Application
                Dim ExWorkbook As Excel.Workbook
                Dim ExSheet As Excel.Worksheet
                Dim tmpPath As Object = System.Reflection.Missing.Value

                ExApp = New Excel.Application
                ExWorkbook = ExApp.Workbooks.Add(tmpPath)
                ExSheet = ExWorkbook.Sheets("Sheet1")
                With ExSheet
                    .Cells.Font.Name = "Calibri"
                    .Cells.Font.Size = 12
                    With .PageSetup
                        .LeftFooter = "&8Exported From VB.NET"
                        .CenterFooter = "&8Page &P Of &N"
                        .RightFooter = "&8" & Today.ToLongDateString
                    End With
                End With

                RowCount = lv.Items.Count
                For iRow = 0 To RowCount - 1
                    If iRow = 23 Then
                        iCol = 1
                        iPos = 1
                    Else
                        iPos += 1
                    End If
                    ExSheet.Cells(iPos, iCol + 1) = lv.Items(iRow).ToString
                Next

                ExSheet.SaveAs(NewSaveFile.FileName)
                ExWorkbook.Close()
                ExApp.Quit()

                ReleaseObject(ExApp)
                ReleaseObject(ExWorkbook)
                ReleaseObject(ExSheet)

                MsgBox("Export to Excel successfully.")

            End If

        Catch ex As Exception
            MsgBox(Err.Number.ToString & " : " & ex.ToString)
        End Try

    End Sub

    Public Sub ReleaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

这篇关于如何将列表框项目导出到Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！