Excel Interop的使用（Excel.Application）

白驹……过隙

已于 2024-11-29 16:34:41 修改

阅读量1k

点赞数 9

分类专栏： Excel专题文章标签： excel

于 2024-11-01 09:03:10 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_71742083/article/details/143422691

版权

Excel专题专栏收录该内容

2 篇文章

订阅专栏

.NET4.7.2-用VSTO开发Excel插件的开发

应用程序的实例：excelApp
工作簿：excelWB
工作表：excelSht

引用：

Imports Microsoft.Office.Interop.Excel

一、基本

（一）应用程序实例

1、创建 Excel 应用程序的实例

Dim excelApp As New Application()

2、获取当前 Excel 应用程序的实例(开发插件用的多)

Dim excelApp As Excel.Application = Globals.ThisAddIn.Application

（二）打开工作簿

1、打开目标工作簿

Dim excelWB As Workbook = excelApp.Workbooks.Open("你的工作簿路径")

2、打开新的工作簿

Dim excelWB As Workbook = excelApp.Workbooks.Add()

3、获取当前活动的工作薄（多配合上面（一）内2情况下使用）

Dim excelWB As Excel.Workbook = excelApp.ActiveWorkbook

（三）打开工作表

Dim excelSht As Worksheet = excelWB.Sheets("工作表名称")

（四）关闭工作簿(保存：true，不保存：false)+退出Excel程序

excelWB.Close(False) '关闭工作簿，不保存更改。
excelApp.Quit() '退出 Excel 应用程序。

（五）清理（释放 COM 对象，以避免内存泄漏）

'System.Runtime.InteropServices.Marshal.ReleaseComObject
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSht)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWB)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

（六）清空<ClearContents、Clear、ClearFormats、ClearComments>

1、ClearContents清除指定单元格区域中所有数据和公式，但保留单元格的格式、批注、超链接等

excelSht.Range(excelSht.Cells(开始行, 开始列), excelSht.Cells(结束行, 结束列)).ClearContents()
或
Dim ranges As Excel.Range = excelSht.Range(excelSht.Cells(开始行, 开始列), excelSht.Cells(结束行, 结束列))
ranges.ClearContents()
或
excelSht.Range("A1:A10").ClearContents()

2、Clear 清除单元格区域的所有内容，包括数据、格式、批注、超链接等

excelSht.Range("A1:A10").Clear()

3、ClearFormats 清除单元格区域的格式，但保留数据

excelSht.Range("A1:A10").ClearFormats()

4、ClearComments 清除单元格区域的批注，但保留数据和格式

excelSht.Range("A1:A10").ClearComments()

（七）超链接

Hyperlinks.Add()用于在指定的单元格中添加超链接

Hyperlinks.Add(Anchor As Range, Address As String, SubAddress As String, ScreenTip As String, TextToDisplay As String)

参数

Anchor：要添加超链接的单元格或单元格区域。
Address：超链接的目标地址，可以是 URL 或文件路径。
SubAddress（可选）：工作表中的子地址，例如单元格地址或命名范围。
ScreenTip（可选）：当鼠标悬停在超链接上时显示的屏幕提示文本。
TextToDisplay（可选）：在单元格中显示的文本。如果不指定，将显示 Address。

例子：在第15行第3列的单元格添加一个超链接，并显示为百度一下

With excelSht
  .Hyperlinks.Add(.Cells(15, 5), "https://www.BaiDu.com", TextToDisplay:="百度一下")
End With

（八）添加图表

1、柱状图

With excelSht
  '准备图表数据
  .Cells(11, 1).Value = "Month"
  .Cells(11, 2).Value = "Sales"
  .Cells(12, 1).Value = "Jan"
  .Cells(12, 2).Value = 100
  .Cells(13, 1).Value = "Feb"
  .Cells(13, 2).Value = 150
  .Cells(14, 1).Value = "Mar"
  .Cells(14, 2).Value = 200
  .Cells(15, 1).Value = "Apr"
  .Cells(15, 2).Value = 250
  '添加图表对象
  Dim chartObj As Excel.ChartObject = .ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=200)
  '获取图表对象的图表
  Dim chart As Microsoft.Office.Interop.Excel.Chart = chartObj.Chart
  '设置图表的数据源
  chart.SetSourceData(Source:= .Range("A11:B15"))
  '设置图表类型
  chart.ChartType = Excel.XlChartType.xlColumnClustered  '柱状图
  '设置图表标题
  chart.HasTitle = True
  chart.ChartTitle.Text = "Monthly Sales Report"
  '设置 X 轴标题
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Month"
  '设置 Y 轴标题
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Sales"
  '设置图例
  chart.HasLegend = True
  chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
End With

2、折线图

With excelSht
  '准备图表数据
  .Cells(11, 1).Value = "Month"
  .Cells(11, 2).Value = "Sales"
  .Cells(12, 1).Value = "Jan"
  .Cells(12, 2).Value = 100
  .Cells(13, 1).Value = "Feb"
  .Cells(13, 2).Value = 150
  .Cells(14, 1).Value = "Mar"
  .Cells(14, 2).Value = 200
  .Cells(15, 1).Value = "Apr"
  .Cells(15, 2).Value = 250
  '添加图表对象
  Dim chartObj As Excel.ChartObject = .ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=200)
  '获取图表对象的图表
  Dim chart As Microsoft.Office.Interop.Excel.Chart = chartObj.Chart
  '设置图表的数据源
  chart.SetSourceData(Source:= .Range("A11:B15"))
  '设置图表类型
  chart.ChartType = Excel.XlChartType.xlLine  '折线图
  '设置图表标题
  chart.HasTitle = True
  chart.ChartTitle.Text = "Monthly Sales Report"
  '设置 X 轴标题
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Month"
  '设置 Y 轴标题
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Sales"
  '设置图例
  chart.HasLegend = True
  chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
End With

3、饼图

With excelSht
  '准备图表数据
  .Cells(11, 1).Value = "Month"
  .Cells(11, 2).Value = "Sales"
  .Cells(12, 1).Value = "Jan"
  .Cells(12, 2).Value = 100
  .Cells(13, 1).Value = "Feb"
  .Cells(13, 2).Value = 150
  .Cells(14, 1).Value = "Mar"
  .Cells(14, 2).Value = 200
  .Cells(15, 1).Value = "Apr"
  .Cells(15, 2).Value = 250
  '添加图表对象
  Dim chartObj As Excel.ChartObject = .ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=200)
  '获取图表对象的图表
  Dim chart As Microsoft.Office.Interop.Excel.Chart = chartObj.Chart
  '设置图表的数据源
  chart.SetSourceData(Source:= .Range("A11:B15"))
  '设置图表类型
  chart.ChartType = Excel.XlChartType.xlPie  '饼图
  '设置图表标题
  chart.HasTitle = True
  chart.ChartTitle.Text = "Monthly Sales Report"
  '设置图例
  chart.HasLegend = True
  chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
End With

4、散点图

With excelSht
  '准备图表数据
  .Cells(11, 1).Value = "Month"
  .Cells(11, 2).Value = "Sales"
  .Cells(12, 1).Value = "Jan"
  .Cells(12, 2).Value = 100
  .Cells(13, 1).Value = "Feb"
  .Cells(13, 2).Value = 150
  .Cells(14, 1).Value = "Mar"
  .Cells(14, 2).Value = 200
  .Cells(15, 1).Value = "Apr"
  .Cells(15, 2).Value = 250
  '添加图表对象
  Dim chartObj As Excel.ChartObject = .ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=200)
  '获取图表对象的图表
  Dim chart As Microsoft.Office.Interop.Excel.Chart = chartObj.Chart
  '设置图表的数据源
  chart.SetSourceData(Source:= .Range("A11:B15"))
  '设置图表类型
  chart.ChartType = Excel.XlChartType.xlXYScatter  '散点图
  '设置图表标题
  chart.HasTitle = True
  chart.ChartTitle.Text = "Monthly Sales Report"
  '设置 X 轴标题
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Month"
  '设置 Y 轴标题
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Sales"
  '设置图例
  chart.HasLegend = True
  chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
End With

5、面积图

With excelSht
  '准备图表数据
  .Cells(11, 1).Value = "Month"
  .Cells(11, 2).Value = "Sales"
  .Cells(12, 1).Value = "Jan"
  .Cells(12, 2).Value = 100
  .Cells(13, 1).Value = "Feb"
  .Cells(13, 2).Value = 150
  .Cells(14, 1).Value = "Mar"
  .Cells(14, 2).Value = 200
  .Cells(15, 1).Value = "Apr"
  .Cells(15, 2).Value = 250
  '添加图表对象
  Dim chartObj As Excel.ChartObject = .ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=200)
  '获取图表对象的图表
  Dim chart As Microsoft.Office.Interop.Excel.Chart = chartObj.Chart
  '设置图表的数据源
  chart.SetSourceData(Source:= .Range("A11:B15"))
  '设置图表类型
  chart.ChartType = Excel.XlChartType.xlArea  '面积图
  '设置图表标题
  chart.HasTitle = True
  chart.ChartTitle.Text = "Monthly Sales Report"
  '设置 X 轴标题
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Month"
  '设置 Y 轴标题
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
  chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = "Sales"
  '设置图例
  chart.HasLegend = True
  chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
End With

6、雷达图

With excelSht
  '准备图表数据
  .Cells(11, 1).Value = "Month"
  .Cells(11, 2).Value = "Sales"
  .Cells(12, 1).Value = "Jan"
  .Cells(12, 2).Value = 100
  .Cells(13, 1).Value = "Feb"
  .Cells(13, 2).Value = 150
  .Cells(14, 1).Value = "Mar"
  .Cells(14, 2).Value = 200
  .Cells(15, 1).Value = "Apr"
  .Cells(15, 2).Value = 250
  '添加图表对象
  Dim chartObj As Excel.ChartObject = .ChartObjects.Add(Left:=100, Top:=50, Width:=300, Height:=200)
  '获取图表对象的图表
  Dim chart As Microsoft.Office.Interop.Excel.Chart = chartObj.Chart
  '设置图表的数据源
  chart.SetSourceData(Source:= .Range("A11:B15"))
  '设置图表类型
  chart.ChartType = Excel.XlChartType.xlRadar  '雷达图
  '设置图表标题
  chart.HasTitle = True
  chart.ChartTitle.Text = "Monthly Sales Report"
  '设置图例
  chart.HasLegend = True
  chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom
End With

（九）单元格-格式

文本格式：.Range("A:A").NumberFormat = "@"
数字格式：.Range("A:A").NumberFormat = "0"
带千位分隔符的数字格式：.Range("A:A").NumberFormat = "#,##0"
小数格式：.Range("A:A").NumberFormat = "0.00"
百分比格式：.Range("A:A").NumberFormat = "0.00%"
货币格式：.Range("A:A").NumberFormat = "$#,##0.00"
日期格式：.Range("A:A").NumberFormat = "yyyy-mm-dd"
时间格式：.Range("A:A").NumberFormat = "hh:mm:ss"
自定义格式：.Range("A:A").NumberFormat = "[Red]-0.00;[Green]0.00"
科学计数法格式：.Range("A:A").NumberFormat = "0.00E+00"

（十）复制

1、Copy

'源.copy(目标)-将 源 复制到 目标
With excelSht
.Range(.Cells(1, 2), .Cells(5, 5)).Copy(.Range(.Cells(6, 2), .Cells(10, 5)))
End With

二、操作-功能

（一）增删行

插行时，新插入行的样式会同于开始行的上一行，且g3必须>=2。
如图的话，插行应在18行为开始行。

'g1（填数开始行）、g2（填数结束行）、g3=1+g2-g1（有效需填数行数）、s3（需填入的总行数）
Dim addRows As Integer = 0 '增删行后，下侧保留的空行数
If g3 > s3 + addRows Then
    excelSht.Rows($"{g1}:{g1 + g3 - s3 - addRows - 1}").Delete
ElseIf g3 < s3 + addRows Then
    excelSht.Rows($"{g1 + 1}:{g1 + s3 + addRows - g3}").Insert(-4121, 0)
End If
g2 = g1 + s3 + addRows - 1
g3 = g2 - g1 + 1

（二）保护和取消保护工作表

1、Unprotect 方法
用于取消保护工作表。如果工作表受到密码保护，你需要提供正确的密码才能取消保护。

excelSht.Unprotect(Password) 'Password为自定义的string类型-密码

参数

Password（可选）：用于保护工作表的密码。如果工作表没有密码保护，可以省略此参数。

2、Protect 方法
用于保护工作表，防止未经授权的编辑。你可以设置密码和其他保护选项

excelSht.Protect(Password As String, DrawingObjects As Boolean, Contents As Boolean, Scenarios As Boolean, UserInterfaceOnly As Boolean, AllowFormattingCells As Boolean, AllowFormattingColumns As Boolean, AllowFormattingRows As Boolean, AllowInsertingColumns As Boolean, AllowInsertingRows As Boolean, AllowInsertingHyperlinks As Boolean, AllowDeletingColumns As Boolean, AllowDeletingRows As Boolean, AllowSorting As Boolean, AllowFiltering As Boolean, AllowUsingPivotTables As Boolean)

参数

Password（可选）：用于保护工作表的密码。如果不需要密码保护，可以省略此参数。
DrawingObjects（可选）：是否保护绘图对象。默认值为 True。
Contents（可选）：是否保护工作表内容。默认值为 True。
Scenarios（可选）：是否保护方案。默认值为 False。
UserInterfaceOnly（可选）：是否仅保护用户界面，允许宏操作。默认值为 False。
AllowFormattingCells（可选）：是否允许格式化单元格。默认值为 False。
AllowFormattingColumns（可选）：是否允许格式化列。默认值为 False。
AllowFormattingRows（可选）：是否允许格式化行。默认值为 False。
AllowInsertingColumns（可选）：是否允许插入列。默认值为 False。
AllowInsertingRows（可选）：是否允许插入行。默认值为 False。
AllowInsertingHyperlinks（可选）：是否允许插入超链接。默认值为 False。
AllowDeletingColumns（可选）：是否允许删除列。默认值为 False。
AllowDeletingRows（可选）：是否允许删除行。默认值为 False。
AllowSorting（可选）：是否允许排序。默认值为 False。
AllowFiltering（可选）：是否允许筛选。默认值为 False。
AllowUsingPivotTables（可选）：是否允许使用数据透视表。默认值为 False。

（三）使用 AutoFill 方法填充单元格区域

假设你有一个工作表 excelSht，你希望从单元格 A1 开始填充到 A10，使用默认的填充方式

'设置起始单元格和结束单元格
Dim 开始行 As Integer = 1
Dim 列 As Integer = 1
Dim 结束行 As Integer = 10
With excelSht
.Cells(开始行, 列).Value = 1  '设置起始单元格的值
.Cells(开始行, 列).AutoFill(Destination:= .Range(.Cells(开始行, 列), .Cells(结束行, 列)), Type:=0)
End With

参数

Destination：（Range）要填充的目标区域。这是一个范围对象，指定了从起始单元格到结束单元格的区域。
Type（可选）：（XlAutoFillType）填充的类型。XlAutoFillType 是一个枚举类型，包含多种填充方式。如下：

xlFillDefault（0）：默认填充方式，通常会复制单元格的内容和格式。
xlFillCopy：复制单元格的内容和格式。
xlFillSeries：填充序列，例如日期或数字序列。
xlFillFormats：仅复制格式。
xlFillJustify：将文本分散到目标区域的所有单元格中。
xlFillDays：填充日期序列。
xlFillWeekdays：填充工作日序列。
xlFillMonths：填充月份序列。
xlFillYears：填充年份序列。
xlGrowthTrend：填充增长趋势。
xlLinearTrend：填充线性趋势。

三、辅助

（一）判断已打开工作簿是否存在相应工作表,找到返回true

'''<param name="wb">wb代表要查询工作簿</param>
'''<param name="s1">s1代表要查询工作表名称</param>
'''<param name="b1">true包含、false相等</param>
Public Function exist_sht_WB(wb As Excel.Workbook, s1 As String,Optional b1 As Boolean=False) As Boolean
    Dim sht_Exist As Boolean = False
    If b1 Then
	  For Each sht In wb.Worksheets
          If sht.Name.Contains(s1) Then
              sht_Exist = True
              Exit For
          End If
      Next
	Else
      For Each sht In wb.Worksheets
          If sht.Name = s1 Then
              sht_Exist = True
              Exit For
          End If
      Next
    End If
    Return sht_Exist
End Function

（二）判断已打开工作簿是否存在相应工作表,找到返回工作表

'''<param name="wb">wb代表要查询工作簿</param>
'''<param name="s1">s1代表要查询工作表名称</param>
Public Function get_sht_WB(wb As Excel.Workbook, s1 As String) As Excel.Worksheet
    For Each sht In wb.Worksheets
        If sht.Name = s1 Then
           Return sht
        End If
    Next
    Return Nothing
End Function

（三）获取字母所代表的 Excel列数字

Public Shared Function GetColNum(str1 As String) As Integer
    Dim res As Integer = 0
    For i = 0 To str1.Length - 1
        res += (Asc(str1(i)) - 64) * CType((26 ^ (str1.Length - i - 1)), Integer)
    Next
    Return res
End Function

四、插行+拉公式、删行

''' <summary>
''' 插行
''' </summary>
''' <param name="sht">工作表</param>
''' <param name="sr">开始行（*插入区域 与开始行上一行格式相同）</param>
''' <param name="er">结束行</param>
''' <param name="destRows">目标行数</param>
''' <param name="foumulaCols">要拉公式列</param>
''' <returns></returns>
Public Shared Function InsertRows(sht As Excel.Worksheet, sr As Integer, er As Integer, destRows As Integer, Optional foumulaCols As Integer() = Nothing) As Boolean
    Dim shtRows As Integer = er - sr + 1
    If shtRows > destRows Then
        sht.Rows(sr + destRows & ":" & er).Delete()
    ElseIf shtRows < destRows Then
        sht.Rows(sr + 1 & ":" & sr + destRows - shtRows).Insert()
        If foumulaCols IsNot Nothing Then
            FormulaFill_COL(sht, foumulaCols, sr, sr + destRows - 1)
        End If
    End If
    Return True
End Function
''' <summary>
''' 填充多列 公式|竖着拉
''' </summary>
''' <param name="sht">工作表</param>
''' <param name="formulaCols">要下拉填充公式的列们</param>
''' <param name="sr">开始行</param>
''' <param name="er">结束行</param>
''' <returns></returns>
Public Shared Function FormulaFill_COL(sht As Excel.Worksheet, formulaCols As Integer(), sr As Integer, er As Integer) As Boolean
    With sht
        For i = 0 To formulaCols.Length - 1
            .Cells(sr, formulaCols(i)).AutoFill(.Range(.Cells(sr, formulaCols(i)), .Cells(er, formulaCols(i))))
        Next
    End With
    Return True
End Function

五、插列+拉公式、删列

''' <summary>
''' 插列
''' </summary>
''' <param name="sht">工作表</param>
''' <param name="sc">开始列（*插入区域 与开始列前一列格式相同）</param>
''' <param name="ec">结束列</param>
''' <param name="destColumns">目标列数</param>
''' <param name="foumulaRows">要拉公式行</param>
''' <returns></returns>
Public Shared Function InsertColumns(sht As Excel.Worksheet, sc As Integer, ec As Integer, destColumns As Integer, Optional foumulaRows As Integer() = Nothing) As Boolean
    Dim shtColumns As Integer = ec - sc + 1
    If shtColumns > destColumns Then
        sht.Columns(sc + destColumns & ":" & ec).Delete()
    ElseIf shtColumns < destColumns Then
        sht.Columns(sc + 1 & ":" & sc + destColumns - shtColumns).Insert()
        If foumulaRows IsNot Nothing Then
            FormulaFill_Row(sht, foumulaRows, sc, sc + destColumns - 1)
        End If
    End If
    Return True
End Function
''' <summary>
''' 填充多行 公式|横着拉
''' </summary>
''' <param name="sht"></param>
''' <param name="formulaRows">要右拉填充公式的行们</param>
''' <param name="sc">开始列</param>
''' <param name="ec">结束列</param>
''' <returns></returns>
Public Shared Function FormulaFill_Row(sht As Excel.Worksheet, formulaRows As Integer(), sc As Integer, ec As Integer) As Boolean
    With sht
        For i = 0 To formulaRows.Length - 1
            .Cells(formulaRows(i), sc).AutoFill(.Range(.Cells(formulaRows(i), sc), .Cells(formulaRows(i), ec)))
        Next
    End With
    Return True
End Function

四、可能用到的工具

WPS无法运行宏：
https://pan.baidu.com/s/1JaQRYU0gDb-jxqM-mzWreQ 提取码: ……
没装过office,WPS内不显示插件：
https://pan.baidu.com/s/1PpjWP4jHF1eZ2cSscdx0Og 提取码: ……

……持续更新中