Visual Studio 2015 - 操作Excel？

Question

Visual Studio 2015 - 操作Excel？

vb.netexcelvisual-studio-2015

3

我有750个Excel文件，需要进行以下操作:

删除带星号标题的数据列，对这些文件进行清理。
将其中一部分数据放入新工作簿的工作表中，将另一部分数据放入同一工作簿的另一个工作表中，将其他数据放入第二个新工作簿中。

我在Visual Studio 2015中创建了一个WPF项目，其中包含一个小对话框和两个单选按钮：

清洗数据
生成新文件

下面是我的VB代码：

    Class MainWindow
    Dim wb As Microsoft.Office.Interop.Excel._Workbook
    Dim ws As Microsoft.Office.Interop.Excel._Worksheet
    Dim iCol As Integer
    Dim strName As String
    Dim iIndex As Integer
    Dim strPath As String
    Dim strFile As String

    Private Sub button_Click(sender As Object, e As RoutedEventArgs) Handles button.Click
        If cleanRadioButton.IsChecked = True Then
            strPath = "c:\test\old\"
            strFile = Dir(strPath & "*.csv")
            Do While strFile <> ""

                wb = wb.Open(Filename:=strPath & strFile)

                'Loop through the sheets.
                For iIndex = 1 To Application.Worksheets.Count
                    ws = Application.Worksheets(iIndex)

                    'Loop through the columns.
                    For iCol = 1 To ws.UsedRange.Columns.Count
                        'Check row 1 of this column for the char of *
                        If InStr(ws.Cells(10, iCol).Value, "*") > 0 Then
                            'We have found a column with the char of *
                            ws.Columns(iCol).EntireColumn.Delete
                            ws.Columns(iCol + 1).EntireColumn.Delete
                            ws.Columns(iCol + 2).EntireColumn.Delete
                        End If
                    Next iCol

                Next iIndex
                wb.SaveAs(Filename:="C:\test\new\" & wb.Name, FileFormat:=xlOpenXMLWorkbook)
                wb.Close(SaveChanges:=False)
                strFile = Dir()
            Loop
            MessageBox.Show("The csv files have now been cleaned.  Congrats.")
        Else inputRadioButton.IsChecked = True
            MessageBox.Show("The data has now been split into Trajectory and ForcePlate input files.  High 5.")
        End If
    End Sub
End Class

我遇到了三个错误，但不知道如何解决

a) Worksheets 不是 Application 的成员 [第19行]

b) Worksheets 不是 Application 的成员 [第20行]

c) 'xlOpenXMLWorkbook'未声明，可能由于其保护级别而无法访问。

- Tom Chambers

1

我同时使用VB.Net和VBA。VB.Net具有丰富的功能，包括访问保存在任何Excel版本下的工作簿，并且生成的可执行文件比VBA宏快100倍。我认为VB.Net是更好的语言，我比VBA更常使用它。然而，从VB.Net访问工作簿的速度较慢。如果程序的唯一目的是操作工作簿，我会使用VBA。 - Tony Dallimore

2个回答

1

要引用一个工作表，可以使用以下任一方法：ws = wb.Worksheets(1) 或 ws = wb.Worksheets("Sheet1") 或 ws = excelApp.ActiveWorkbook.Worksheets(1)，如果要使用 xlOpenXMLWorkbook，还需要使用相应枚举类型 XlFileFormat 的名称： XlFileFormat.xlOpenXMLWorkbook。

这个简化的示例打开名为 Test.xlsx 的工作簿，在单元格 A1 中写入文本，并将其保存到新文件夹中。

Imports System.IO
Imports Microsoft.Office.Interop.Excel

Public Class MainWindow

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim excelApp As Application
        Dim wb As _Workbook
        Dim ws As _Worksheet
        Dim rng As Range
        Dim strPathOld = "c:\temp\old"
        Dim strPathNew = "c:\temp\new"

        ' get excel application reference
        excelApp = New Application
        excelApp.Visible = True
        excelApp.ScreenUpdating = True

        ' open the workbook
        wb = excelApp.Workbooks.Open(Path.Combine(strPathOld, "Test.xlsx"))

        ' set reference to the sheet with index 1
        ws = wb.Worksheets(1)

        ' or use sheet name
        ' ws = wb.Worksheets("Sheet1")

        ' or use ActiveWorkbook if it exists
        ' ws = excelApp.ActiveWorkbook.Worksheets(1)

        ' write text in cell A1
        rng = ws.Range("A1")
        rng.Formula = "Test123"

        ' save the workbook in new location
        wb.SaveAs(Filename:=Path.Combine(strPathNew, wb.Name), _
              FileFormat:=XlFileFormat.xlOpenXMLWorkbook)

        excelApp.Quit()

    End Sub
End Class

注意：请为您的Excel版本添加MS Office Interop参考（这里以Excel 2007为例）。

- Daniel Dušek

嗨，Dee，感谢你抽出时间回复。我尝试了你的代码，但是一直出现错误，说某些东西未定义或不包含在应用程序中...有什么想法吗？ - Tom Chambers

@Tom 我不知道问题是什么。但请看编辑后的答案，我在示例项目中添加了一个图片，显示Excel-Interop dll引用的样子。希望对你有帮助。 - Daniel Dušek

你的代码中可能应用程序类型不是Excel-Application？尝试使用完全限定名称，写成Microsoft.Office.Interop.Excel.Application。 - Daniel Dušek

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- R3uK · Accepted Answer

对于 a) 和 b)，其模式为：

。

Application.Workbooks.Worksheets

对于c)最简单的方法是：

从Excel进入VBE（Alt + F11）

按F2显示对象浏览器

查找xlOpenXMLWorkbook

结果：Const xlOpenXMLWorkbook = 51 (&H33) 因此，只需将其替换为值51！

这是您修改过的代码：

  Class MainWindow
    Dim wb As Microsoft.Office.Interop.Excel._Workbook
    Dim ws As Microsoft.Office.Interop.Excel._Worksheet
    Dim iCol As Integer
    Dim strName As String
    Dim iIndex As Integer
    Dim wbIndex As Integer
    Dim strPath As String
    Dim strFile As String

    Private Sub button_Click(sender As Object, e As RoutedEventArgs) Handles button.Click
        If cleanRadioButton.IsChecked = True Then
            strPath = "c:\test\old\"
            strFile = Dir(strPath & "*.csv")
            Do While strFile <> ""

                wb = wb.Open(Filename:=strPath & strFile)

                'Loop through the sheets.
                For wbIndex = 1 To Application.Workbooks.Count
                    For iIndex = 1 To Application.Workbooks(wbIndex).Worksheets.Count
                        Ws = Application.Workbooks(wbIndex).Worksheets(iIndex)
    
                        'Loop through the columns.
                        For iCol = 1 To Ws.UsedRange.Columns.Count
                            'Check row 1 of this column for the char of *
                            If InStr(Ws.Cells(10, iCol).Value, "*") > 0 Then
                                'We have found a column with the char of *
                                Ws.Columns(iCol).EntireColumn.Delete
                                Ws.Columns(iCol + 1).EntireColumn.Delete
                                Ws.Columns(iCol + 2).EntireColumn.Delete
                            End If
                        Next iCol
    
                    Next iIndex
                Next wbIndex
                'Const xlOpenXMLWorkbook = 51 (&H33)
                wb.SaveAs(Filename:="C:\test\new\" & wb.Name, FileFormat:=51)
                wb.Close(SaveChanges:=False)
                strFile = Dir()
            Loop
            MessageBox.Show ("The csv files have now been cleaned.  Congrats.")
        Else: inputRadioButton.IsChecked = True
            MessageBox.Show ("The data has now been split into Trajectory and ForcePlate input files.  High 5.")
        End If
    End Sub
End Class