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

Question

我正在使用以下代码将我的 ListView 导出到 Excelsheet，但问题是我有多个 ListView ，我必须将它们导出到同一个 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

那么您知道如何添加另一个工作表并将另一个 ListView 导出到它吗？

Answer 1

试试这个代码

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