VSTO C# 开发Excel工作表对象

//工作表名称
MessageBox.Show(Excelapp.Application.ActiveSheet.Name);

// 隐藏工作表
Excel.Worksheet wst = (Excel.Worksheet)Excelapp.ActiveSheet;
wst.Visible = Excel.XlSheetVisibility.xlSheetHidden;//隐藏当前工作表
MessageBox.Show("隐藏了当前工作表");

// 深度隐藏
Excelapp.Application.Sheets[2].Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

// 使用For遍历工作表
int wkCount = Excelapp.Application.Worksheets.Count;
string s = string.Empty;
for (int i = 1; i <= wkCount; i++)
    s = s + Excelapp.Application.Worksheets[i].Name + "\n";
MessageBox.Show("工作簿中含有以下工作表:" + "\n" + s);

// 使用ForEach语句
string s = string.Empty;
foreach (Excel.Worksheet wk in Excelapp.Application.Worksheets)
    s = s + wk.Name + "\n";
MessageBox.Show("工作簿中含有以下工作表:" + "\n" + s);

// 建立工作表目录
int i = Excelapp.Application.Worksheets.Count;
Excelapp.Range["A1"].Value = "目录";
for (int n = 1; n <= i; n++)
    Excel.Worksheet wst = (Excel.Worksheet)Excelapp.ActiveSheet;
    string sheetName = Excelapp.Application.Worksheets[n].Name;
    wst.Hyperlinks.Add(Excelapp.Cells[n+1, 1],"" ,sheetName + "!A1","",sheetName);

// 选择工作表
Excelapp.Application.Worksheets[2].Select();
Excelapp.Application.Worksheets[2].Activate();

// 新增工作表
Excel.Worksheet wst = Excelapp.ActiveSheet;
wst = Excelapp.ActiveWorkbook.Worksheets.Add();//增加工作表

// 删除工作表
Excel.Worksheet wst = Excelapp.ActiveSheet;
wst.Delete();//删除工作表

工作表
Excel.Sheets wksThis = Excelapp.Application.Worksheets;
Excel.Worksheet wksNew = null;
if (wksThis.Count <= 3)
    for (int i = 1; i <= 10; i++)
        wksNew = wksThis.Add(System.Type.Missing, wksThis[wksThis.Count]);
        wksNew.Name = "第" + i.ToString() + "个工作表";

// 一次插入多行
Excel.Range rng = Excelapp.Rows[3];
rng.Resize[3].Insert();

// 保护与解除保护
Excel.Worksheet wst = Excelapp.ActiveSheet;
wst.Unprotect("12345");
Excelapp.Cells[1, 1].Value = 100;
wst.Protect("12345");

// 判断是否选中整行
int i = Excelapp.Columns.Count;
Excel.Range rng = Excelapp.Application.Selection;
if (rng.Columns.Count == i)
    MessageBox.Show("你选中了一整行");
    MessageBox.Show("你没有选中了一整行");

// 定位删除特定内容所在的行
// 注:需引用using System.Text.RegularExpressions;
Excelapp.Application.DisplayAlerts = false;
int rngEnd = Excelapp.Range["A65535"].End[Excel.XlDirection.xlUp].Row;
string strFind = Excelapp.Application.InputBox("请输入查找的值", "查找单元格", "A", Type: 0);
strFind = strFind.Replace("=", "").Trim('"').Trim();
for (int i = rngEnd; i >= 1; i--)
    Excel.Range rng = Excelapp.Cells[i, 1];
    if (Regex.IsMatch(rng.Text, strFind))
        Excelapp.Rows[i].Delete();

// 复制自动筛选后的数据区域
Excelapp.Application.Worksheets[2].Cells.Clear();
if (Excelapp.ActiveSheet.FilterMode)
    Excelapp.ActiveSheet.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy(
    Excelapp.Application.Worksheets[2].Cells[1, 1]);

Excel.Range rngSheet2 = this.Application.Worksheets[2].Cells;
rngSheet2.Clear();
this.Range["A1"].CurrentRegion.AdvancedFilter(
    Excel.XlFilterAction.xlFilterCopy,
    System.Type.Missing,
    this.Application.Worksheets[2].Cells[1, 1],
    true);

// 在工作表中添加图形
// 注:需引用using Microsoft.Office.Core
Excel.Shape myShape;
    Excelapp.ActiveSheet.Shapes.Item("myShape").Delete();
catch
myShape = Excelapp.ActiveSheet.Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, 40, 120, 280, 30);
myShape.Name = "myShape";
Excel.Characters myCharacters = myShape.TextFrame.Characters();
myCharacters.Text = "单击将选择工作表2!";
myCharacters.Font.Name = "新细明体";
myCharacters.Font.Size = 22;
myCharacters.Font.ColorIndex = 7;
myShape.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
myShape.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
myShape.Placement = Excel.XlPlacement.xlFreeFloating;
myShape.Select();
Excel.ShapeRange myShapeRng = Excelapp.Application.Selection.ShapeRange;
myShapeRng.Line.Weight = 1;
myShapeRng.Line.DashStyle = MsoLineDashStyle.msoLineSolid;
myShapeRng.Line.Style = MsoLineStyle.msoLineSingle;
myShapeRng.Line.Transparency = 0;
myShapeRng.Line.Visible = MsoTriState.msoTrue;
myShapeRng.Line.ForeColor.SchemeColor = 40;
myShapeRng.Line.BackColor.RGB = 65536 * 255 + 256 * 255 + 255; //RGB=65536*B + 256*G + R
myShapeRng.Fill.Transparency = 0;
myShapeRng.Fill.Visible = MsoTriState.msoTrue;
myShapeRng.Fill.ForeColor.SchemeColor = 41;
myShapeRng.Fill.OneColorGradient(MsoGradientStyle.msoGradientHorizontal, 4, 0.23F);    //OneColorGradient第三个参数为Float,C#中默认0.23为double,所以需要在0.23后面加上F,指定为Float
Excelapp.Range["A1"].Select();
Excelapp.ActiveSheet.Hyperlinks.Add(myShape, "", "工作表2!A1", "选择工作表2!");

// 在工作表中添加艺术字
// 注:需引用using Microsoft.Office.Core
Excel.Shape myShape;
    Excelapp.ActiveSheet.Shapes.Item("myShape").Delete();
catch
myShape = Excelapp.ActiveSheet.Shapes.AddTextEffect(
    MsoPresetTextEffect.msoTextEffect15,
    "我爱Excel Home",
    "新细明体",
    MsoTriState.msoFalse,
    MsoTriState.msoFalse,
    100, 100);
myShape.Name = "myShape";
myShape.Fill.Solid();
myShape.Fill.ForeColor.SchemeColor = 55;
myShape.Fill.Transparency = 0;
myShape.Line.Weight = 1.5F;
myShape.Line.DashStyle = MsoLineDashStyle.msoLineSolid;
myShape.Line.Style = MsoLineStyle.msoLineSingle;
myShape.Line.Transparency = 0;
myShape.Line.ForeColor.SchemeColor = 12;
myShape.Line.BackColor.RGB = 65536 * 255 + 256 * 255 + 255;

// 遍历工作表中的图形