vb.net 将数据导出到多个 Excel 工作表

Exporting Data To Multiple Excel Sheets

提问by BRSinh

I'm Using following code to export my listview to Excelsheet but the problem is i have multiple listviews which i have to export in different sheets in same excel file . . . . .

我正在使用以下代码将我的列表视图导出到 Excelsheet，但问题是我有多个列表视图，我必须将它们导出到同一个 Excel 文件的不同工作表中。. . . .

Dim flnameSaveAs As String = System.IO.Path.GetFileName(Main.spath1)
'Save Files name
Dim extension As String
  extension = Path.GetExtension(Main.spath1)

Dim file As String = System.IO.Path.GetFileName(Main.spath1)
Dim FinenameA As String = System.IO.Path.GetDirectoryName(Main.spath1)

Dim savnames As String
savnames = file.Substring(0, Len(file) - Len(extension))

Dim ExportSheet As String
    ExportSheet = deskPath + "\Cel_ID_TimeLine.txt"

    Dim lvi As ListViewItem
    Dim sb As New System.Text.StringBuilder

    Dim sbhd As New System.Text.StringBuilder
    Dim columns As Integer = lvCidTimeLine.Columns.Count

    For ixhd As Integer = 0 To lvCidTimeLine.Columns.Count - 1
        sbhd.Append(lvCidTimeLine.Columns(ixhd).Text)
        sbhd.Append(vbTab)
    Next

    sb.Append(vbCrLf)

    For Each lvi In lvCidTimeLine.Items
        For ix As Integer = 0 To lvi.SubItems.Count - 1
            sb.Append(lvi.SubItems(ix).Text)
            If ix < lvi.SubItems.Count - 1 Then
                sb.Append(vbTab)
            Else
                sb.Append(vbCrLf)
            End If
        Next
    Next

    Dim sw As New StreamWriter(ExportSheet)
    sw.Write(sbhd.ToString)
    sw.Write(sb.ToString)
    sw.Close()


    Dim oExcel As Excel.Application

    ' Create the spreadsheet

    oExcel = CreateObject("Excel.Application")

  oExcel.Workbooks.OpenText(ExportSheet, , , , -4142, , True)
  oExcel.Cells.EntireColumn.AutoFit()

  oExcel.ActiveWorkbook.SaveAs(savpath + "\" + savnames + ".xls", -4143)
  oExcel.Quit()
  oExcel = Nothing

So do you have any idea how to add another sheet and export another listview to it ??

那么您知道如何添加另一个工作表并将另一个列表视图导出到其中吗？

采纳答案by Jadeja RJ

Try This Code Instead

试试这个代码

Try
        Dim objExcel As New Excel.Application
        Dim bkWorkBook As Excel.Workbook
        Dim shWorkSheet As Excel.Worksheet
        Dim shWorkSheet1 As Excel.Worksheet

        Dim i As Integer
        Dim j As Integer

        objExcel = New Excel.Application
        bkWorkBook = objExcel.Workbooks.Add
        shWorkSheet = CType(bkWorkBook.ActiveSheet, Excel.Worksheet)

        For i = 0 To lv1.Columns.Count - 1
            shWorkSheet.Cells(1, i + 1) = lv1.Columns(i).Text
        Next
        For i = 0 To lv1.Items.Count - 1
            For j = 0 To lv1.Items(i).SubItems.Count - 1
                shWorkSheet.Cells(i + 2, j + 1) = lv1.Items(i).SubItems(j).Text
            Next
        Next

        shWorkSheet1 = bkWorkBook.Worksheets.Add(, shWorkSheet, , )

        For i = 0 To lv2.Columns.Count - 1
            shWorkSheet1.Cells(1, i + 1) = lv2.Columns(i).Text
        Next
        For i = 0 To lv2.Items.Count - 1
            For j = 0 To lv2.Items(i).SubItems.Count - 1
                shWorkSheet1.Cells(i + 2, j + 1) = lv2.Items(i).SubItems(j).Text
            Next
        Next


        objExcel.Visible = False
        objExcel.Application.DisplayAlerts = False

        objExcel.ActiveWorkbook.SaveAs(savpath + "\" + savnames + "_1" + ".xls", -4143)
        objExcel.Quit()

        objExcel = Nothing

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

    Dim proc As System.Diagnostics.Process
    For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
        proc.Kill()
    Next

回答by Steve

You need to ADD to the sheets collection. Interop Excel Sheets

您需要添加到工作表集合。 互操作 Excel 表格