Visual Basic中使用Excel自动化

Question

我一直在从Visual Basic应用程序中设置的Excel电子表格的行高和列宽不成功。

我有一个Visual Basic应用程序，其中我在剪贴板中有数据。我将该代码复制到一个excel实例，然后用excel保存生成的电子表格，然后excel关闭。我试图在保存电子表格之前以编程方式设置行高和单元格宽度，但一直未能这样做。这是我执行的代码：

If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then 
    Dim oXL As Excel.Application 
    Dim oWB As Excel.Workbook 
    Dim oSheet As Excel.Worksheet 
    oXL = CreateObject("Excel.Application") 
    oXL.Visible = True 
    oWB = oXL.Workbooks.Add 
    oSheet = oWB.ActiveSheet 

    oSheet.Paste() 
    oSheet.Cells.Select() 
    oSheet.Selection.RowHeight = 11.4 
    oSheet.Cells.EntireColumn.AutoFit() 
    oSheet = Nothing 
    oWB.Close(True, SaveFileDialog1.FileName) 
    oWB = Nothing 
    oXL.Quit() 
    oXL = Nothing 
    MsgBox("Finished!") 
End If 

该应用程序运行，而不oSheet.Cells.Select（），oSheet.Selection.RowHeight = 11.4，和oSheet.Cells.EntireColumn.AutoFit（） 线。有了这些行，我得到这个错误对话框消息：

公共部件类型的“工作表”“选择”未找到。

当我在Visual Studio中跟踪程序时，执行oSheet.Paste（）命令并执行oSheet.Cells.Select（）命令。当我尝试执行oSheet.Selection.RowHeight = 11.4命令时会生成异常。

任何援助将不胜感激。

乔纳森

Answer 1

下面是不是后期绑定，这是早期绑定，但模式应该有轻微的代码更改工作。

Option Strict On 
Imports Excel = Microsoft.Office.Interop.Excel 
Imports System.Runtime.InteropServices 
Imports System.IO 

Public Class Operations 
    Public HasError As Boolean 
    Public ErrorMessage As String 
    ''' <summary> 
    ''' 
    ''' </summary> 
    ''' <param name="FileName">Path and file name for Excel file</param> 
    ''' <param name="SheetName">Sheet name to work on</param> 
    ''' <param name="RowHeight">Used to set row height in SheetName</param> 
    ''' <returns></returns> 
    Public Function SetWidthHeight(
     ByVal FileName As String, 
     ByVal SheetName As String, 
     ByVal RowHeight As Integer) As Boolean 

     If File.Exists(FileName) Then 

      Dim Proceed As Boolean = False 

      Dim xlApp As Excel.Application = Nothing 
      Dim xlWorkBooks As Excel.Workbooks = Nothing 
      Dim xlWorkBook As Excel.Workbook = Nothing 
      Dim xlWorkSheet As Excel.Worksheet = Nothing 
      Dim xlWorkSheets As Excel.Sheets = Nothing 
      Dim xlCells As Excel.Range = Nothing 

      xlApp = New Excel.Application 
      xlApp.DisplayAlerts = False 


      xlWorkBooks = xlApp.Workbooks 
      xlWorkBook = xlWorkBooks.Open(FileName) 

      xlApp.Visible = False 

      xlWorkSheets = xlWorkBook.Sheets 

      For x As Integer = 1 To xlWorkSheets.Count 
       xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet) 

       If xlWorkSheet.Name = SheetName Then 
        Proceed = True 
        Exit For 
       End If 

       Marshal.FinalReleaseComObject(xlWorkSheet) 
       xlWorkSheet = Nothing 

      Next 
      If Proceed Then 

       xlCells = xlWorkSheet.Cells 
       xlCells.PasteSpecial() 

       Dim EntireRow As Excel.Range = xlCells.EntireRow 
       ' 
       ' Set row height 
       ' 
       EntireRow.RowHeight = RowHeight 
       Dim ColRange = xlCells.EntireColumn 
       ' 
       ' Auto fit all columns 
       ' 
       ColRange.AutoFit() 
       ReleaseComObject(ColRange) 

       ReleaseComObject(xlCells) 
       ReleaseComObject(EntireRow) 

      Else 
       HasError = True 
       ErrorMessage = SheetName & " not found." 
      End If 

      xlWorkSheet.SaveAs(FileName) 

      xlWorkBook.Close() 
      xlApp.UserControl = True 
      xlApp.Quit() 

      ReleaseComObject(xlWorkSheets) 
      ReleaseComObject(xlWorkSheet) 
      ReleaseComObject(xlWorkBook) 
      ReleaseComObject(xlWorkBooks) 
      ReleaseComObject(xlApp) 

     Else 
      HasError = True 
      ErrorMessage = "'" & FileName & 
       "' not located. Try one of the write examples first." 
     End If 

     Return HasError 
    End Function 
    Private Sub ReleaseComObject(ByVal excelObject As Object) 
     Try 
      If excelObject IsNot Nothing Then 
       Marshal.ReleaseComObject(excelObject) 
       excelObject = Nothing 
      End If 
     Catch ex As Exception 
      excelObject = Nothing 
     End Try 
    End Sub 
End Class