簡單的VB.NET寫Excel應用

摘要:簡單的VB.NET寫Excel範例

基本應用

Dim APP As New Microsoft.Office.Interop.Excel.Application   
Dim workbook As Excel.Workbook   
  
' 加入 workbook   
workbook = APP.Workbooks.Add()  
   
' Sheet、Range  
Dim Sheet As Excel.Worksheet = workbook.Sheets(1)   
Dim Range As Excel.Range   
  
' 填值  
Range = Sheet.Range("A1")   
Range.Value2 = "Test"   
  
' 關閉 Excel 的 Process (避免Process殘留)  
ReleaseComObject(APP)   
APP = Nothing   
GC.Collect()

如果要刪除正在執行中的Excel Workbook,可先將其設為唯讀

新增註解

尋找註解位置

For Each Comment As Excel.Comment In WorkSheet.Comments 
 …
Next
Next

Excel中Column的位址是英文字母 ,Row的位址是數字 ,例如

Range.Address => $C$3。

若要插入一列或一欄:

Range.Rows(("1").Insert()

複製一列或一欄:

Range.Rows(("2").Copy()

將Excel sheet 複製到另一個 Excel 中

Dim CopyBook As Excel.Workbook 
Dim CopySheet As Excel.Worksheet 
CopyBook = ExcelApp.Workbooks.Open(ExcelPath)
CopySheet = CopyBook.Worksheets(1)
CopySheet.Copy(Before:=OrgSheet) 

新增圖形

新增圖表

ExcelSheet.Shapes.AddChart()

若要讓開發的Office沒有版本的問題,例如Office2003與Office2007等可以使用延後連結的方式:

Dim ExApp As Object = CreateObject("Excel.Application")