VSTO（Excel相关）的C#代码

关注

转载最新推荐文章于 2025-05-15 21:19:34 发布 5.2k 阅读 AI 写同款· GEO 优化 ›

如何简单部署用VSTO SE 2005开发的Excel，Word插件如何简单部署用VSTO SE 2005开发的Excel，Word插件本文标题之所以要加上简单二字，是为了区别于msdn上的两篇讲述VSTO部署的经典长文。http://msdn2.microsoft.com/en-us/library/bb332051.aspx http://msdn2.microsoft.com/en-us/library/bb332052.aspx 其实阅读详情

使用Range属性

this.Range["A3:F6, B1:C5"].Select();

使用Cells属性

  for(int icell=1;icell<=100;icell++)
     {
  this.Application.Worksheets[2].cells[icell, 1].value = icell;
     }

使用快捷记号

#N/A

使用Offset属性

this.Range["A1:A3"].Offset[3, 3].Select();

使用Resize属性

this.Range["A1"].Resize[3, 3].Select();

使用Union属性

this.Application.Union(this.Range["A1:D4"], this.Range["E5:H8"]).Select();

使用UsedRange属性

this.UsedRange.Select();

使用CurrentRegion属性

this.Range["A5"].CurrentRegion.Select();

使用Select方法

this.Application.Worksheets[3].Activate();
this.Application.Worksheets[3].Range["A1:B10"].Select();

使用Activate方法

this.Application.Worksheets[3].Activate();
this.Application.Worksheets[3].Range["A1:B10"].Activate();
//此处的代码,可以运行,但是只会选中A1这一个单元格

使用Goto方法

this.Application.Goto(this.Application.Worksheets[3].Range["A1:B10"], true);

获得指定行,列中的最后一个非空单元格

Excel.Range rng = this.Range["A65535"].End[Excel.XlDirection.xlUp];
    MessageBox.Show("A列中最后一个非空单元格是" + rng.Address[0, 0] + ",行号" + rng.Row.ToString() + ",数值" + rng.Text);

定位单元格

 Excel.Range rng = this.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);
     rng.Select();
     MessageBox.Show("工作表中有公式的单元格为:" + rng.Address);

查找单元格

Excel.Range rng, Rng;
    Rng = this.Range["A:A"];
    string strFind = textBox1.Text;
    if (strFind.Trim() != string.Empty)
     {
  rng = Rng.Find(strFind, Rng.Cells[Rng.Cells.Count], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false);
  if (rng != null)
  {
      this.Application.Goto(rng, true);
  }
  else
  {
      MessageBox.Show("没有找到单元格!");
  }
      }	
	//C#中没有InputBox,这里用文本框代替,另,C#中没有with……End with语句.

查找单元格重复数据

  Excel.Range rng, Rng;
     string FindAddress = string.Empty;
     Rng = this.Range["A:A"];
     string strFind = textBox1.Text;
     if (strFind.Trim() != string.Empty)
     {
  rng = Rng.Find(strFind, Rng.Cells[Rng.Cells.Count], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false);
  if (rng != null)
  {
      FindAddress = rng.Address;
      do
      {
   rng.Interior.ColorIndex=6;
   rng=Rng.FindNext(rng);
      }while(rng != null && rng.Address != FindAddress);
     
  }

     }

使用Like运算符

 // C#中没有Like运算符,可以用正则表达式来处理.

替换单元格内字符串

this.Range["A1:A5"].Replace("通州", "南通");

复制单元格区域

this.Application.DisplayAlerts = false;
     this.Range["A1"].CurrentRegion.Copy(this.Application.Worksheets[2].Range["A1"]);
     this.Application.DisplayAlerts = true;

复制单元格区域时带列宽大小

this.Range["A1"].CurrentRegion.Copy();
     Excel.Range rng = this.Application.Worksheets[3].Range["A1"];
     rng.PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths);
     rng.PasteSpecial(Excel.XlPasteType.xlPasteAll);
     this.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;

使用选择性粘贴

this.Range["A1"].CurrentRegion.Copy();
     Excel.Range rng = this.Application.Worksheets[3].Range["A1"];
     rng.PasteSpecial(Excel.XlPasteType.xlPasteValues);
     this.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;

直接赋值的方法

Excel.Range rng = this.Application.Worksheets[3].Range["A1"];
    Excel.Range Rng = this.Range["A1"].CurrentRegion;
    rng.Resize[Rng.Rows.Count, Rng.Columns.Count].Value = Rng.Value;

单元格自动进入编辑状态

先在”VSTO 设计器生成的代码”内加入

this.SelectionChange += new Excel.DocEvents_SelectionChangeEventHandler(工作表1_SelectionChange);

然后在事件代码中输入

  if (Target.Column == 3 && Target.Count == 1)
      {
        if (Target.Text == string.Empty)
          {
            this.Application.SendKeys("{F2}");
           }
       }

禁用单元格拖放功能

if (this.Application.Intersect(Target, this.Range["A1:A15"]) != null)
            {
                this.Application.CellDragAndDrop = false;
            }
            else
            {
                this.Application.CellDragAndDrop = true;
            }

单元格字体格式设置

Excel.Font rng = this.Range["A1"].Font;
    rng.Name = "宋体";
    rng.FontStyle = "Bold";
    rng.Size = 18;
    rng.ColorIndex = 3;
    rng.Underline = 2;

设置单元格内部格式

Excel.Interior rng = this.Range["A1"].Interior;
rng.ColorIndex = 3;
rng.Pattern = Excel.XlPattern.xlPatternCrissCross;
rng.PatternColorIndex = 6;

为单元格区域添加边框

 Excel.Borders rng = this.Range["B4:G10"].Borders;
   rng.LineStyle = Excel.XlLineStyle.xlContinuous;
   rng.Weight = Excel.XlBorderWeight.xlThin;
   rng.ColorIndex = 5;
   Excel.XlColorIndex col = (Excel.XlColorIndex)5;
   this.Range["B4:G10"].BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium,col);

为单元格区域应用多种边框格式

  Excel.XlColorIndex col = (Excel.XlColorIndex)5;
    Excel.Border rng = this.Range["B4:G10"].Borders[Excel.XlBordersIndex.xlInsideHorizontal];
    Excel.Border Rng = this.Range["B4:G10"].Borders[Excel.XlBordersIndex.xlInsideVertical];
    rng.LineStyle = Excel.XlLineStyle.xlDot;
    rng.Weight = Excel.XlBorderWeight.xlThin;
    rng.ColorIndex = col;
    Rng.LineStyle = Excel.XlLineStyle.xlContinuous;
    Rng.Weight = Excel.XlBorderWeight.xlThin;
    Rng.ColorIndex = col;
    this.Range["B4:G10"].BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, col);

灵活设计单元格的行高列宽

  Excel.Range rng1 = this.Range["A1"];
    Excel.Range rng2 = this.Range["B1"];
    rng1.RowHeight = this.Application.CentimetersToPoints(2);
    rng1.ColumnWidth = this.Application.CentimetersToPoints(1.5);
    rng2.RowHeight = this.Application.CentimetersToPoints(1.2);
    rng2.ColumnWidth = this.Application.CentimetersToPoints(0.3);

单元格中建立数据有效性

  Excel.Range rng = this.Range["A1:A10"];
    rng.Validation.Delete();
    rng.Validation.Add(
Excel.XlDVType.xlValidateList,
Excel.XlDVAlertStyle.xlValidAlertStop,
Excel.XlFormatConditionOperator.xlBetween,
"1,2,3,4,5,6,7,8");

判断单元格是否存在数据有效性

   try
    {
if (this.Range["A12"].Validation.Type >= 0)
{
    		MessageBox.Show("单元格中有数据有效性!");
}
    }
    catch
    	{


MessageBox.Show("单元格中沒有数据有效性!");
    }

动态的数据有效性

void 工作表1_SelectionChange(Excel.Range Target)
{
    	if (Target.Column == 1 && Target.Count == 1 && Target.Row > 1)
    {
Target.Validation.Delete();
Target.Validation.Add(
    			Excel.XlDVType.xlValidateList,
    			Excel.XlDVAlertStyle.xlValidAlertStop,
    			Excel.XlFormatConditionOperator.xlBetween,
    			"主機,显示器");
    }
}

自动展开数据有效性下拉列表

this.Application.SendKeys("%{down}");

在单元格中写入公式

this.Range["C1:C10"].Formula="=sum(A1,B1)";

写入单元格区域数组公式

this.Range["C1"].FormulaArray = "=A1:A2*B1:B2";

检查单元格是否含有公式

   Excel.Range rng = this.Application.Selection;
   if (Convert.IsDBNull(rng.HasFormula))
     {
       MessageBox.Show("公式区域為:" + rng.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, 23).Address[0, 0]);
     }
     else if(rng.HasFormula)
     {
       MessageBox.Show("全部单元格為公式!");
     }
     else
     {
       MessageBox.Show("全部单元格不為公式!");
     }
   // 注:因為HasFormula返回的是一个dynamic類型的值,C#的swith貌似并不支持.

判断单元格公式是否存在错误

未研究出来,如何调用用VBA.IsError,用了Excel自带函数来处理的.

Excel.Range rng = this.Range["A1"].Offset[0, 1];
rng.Formula = "=iserror(A1)";
if (rng.Value)
  {
      MessageBox.Show("A1单元格錯誤類型為:" + this.Range["A1"].Text);
   }
   else
   {
      MessageBox.Show("A1单元格結果為:" + this.Range["A1"].Text);
   }

取得单元格中公式的引用单元格

Excel.Range rng = this.Range["C1"].Precedents;
 MessageBox.Show("公式所引用的单元格有:" + rng.Address);

將单元格中的公式转换为数值

   Excel.Range rng = this.Range["C1:C10"];
     rng.Formula = "=sum(A1:B1)";
     rng.Value = rng.Value;

判断单元格是否存在批注

if (this.Range["A1"].Comment == null)
   {
     MessageBox.Show("A1单元格中沒有批注");
   }
   else
   {
     MessageBox.Show("A1单元格中批注內容为:" + "\n" + this.Range["A1"].Comment.Text());
   }

为单元格添加批注

 Excel.Range rng = this.Range["A1"];
    if (rng.Comment == null)
     {
        rng.AddComment(rng.Text);
        rng.Comment.Visible = true;
     }

删除单元格中的批注

   Excel.Range rng = this.Range["A1"];
   if (rng.Comment != null)
    {
      rng.Comment.Delete();
    }

判断单元格区域是否存在合并单元格

  Excel.Range rng = this.Application.Selection;
    if (Convert.IsDBNull(rng.MergeCells))
     {
       MessageBox.Show("区域中包含合并单元格!");
     }
     else if (rng.MergeCells)
     {
       MessageBox.Show("区域中全部為合并单元格!");
     }
     else
     {
       MessageBox.Show("区域中沒有合并单元格!");
     }

合并单元格时连接每个单元格的文本

  Excel.Range rng = this.Application.Selection;
  string s = string.Empty;
  foreach(Excel.Range Rng in rng)
   {
     s = s + Rng.Text;
   }
  this.Application.DisplayAlerts = false;
  rng.Merge();
  rng.Value = s;
  this.Application.DisplayAlerts = true;

合并內容相同的连续单元格

 int rEnd = this.Range["A65535"].End[Excel.XlDirection.xlUp].Row;
  this.Application.DisplayAlerts = false;
  for (int i = rEnd; i >= 2; i--)
  {
     Excel.Range rng = this.Cells[i, 1];
     if (rng.Value == rng.Offset[-1, 0].Value)
       {
         this.Application.Union(rng, rng.Offset[-1, 0]).Merge();
       }
  }

取消合并单元格时在每个单元格中保留內容

 int rEnd = this.Range["A65535"].End[Excel.XlDirection.xlUp].Row;
 int m = this.Cells[rEnd, 1].MergeArea.Count-1;
 this.Range[Cells[1, 1], Cells[rEnd, 1]].UnMerge();
 this.Application.DisplayAlerts = false;
 for (int i = 1; i < rEnd+m; i++)
   {
     Excel.Range rng = this.Cells[i, 1];
     if (rng.Offset[1, 0].Text == string.Empty)
       {
         rng.Offset[1, 0].Value = rng.Value;
        }
   }

高亮显示单元格区域

Excel.Range rng = this.Application.Selection;
Cells.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
rng.Interior.ColorIndex = 8;

双击被保护单元格时不显示提示消息框

 if (Target.Locked)
   {
     MessageBox.Show("此单元格已保护,不能編輯");
     Cancel = true;
   }

重新计算工作表指定区域

Excel.XlCalculation oldCalcultion = this.Application.Calculation;
this.Application.Calculation = Excel.XlCalculation.xlCalculationManual;
this.Range["A1:D10"].Calculate();
this.Application.Calculation = oldCalcultion;

录入数据后单元格自动保护

if (this.ProtectContents)
 {
   this.Unprotect("123456");
 }
 if (Target.Text != string.Empty)
  {
    Target.Locked = true;
    this.Protect("123456");
  }

使用单元格的Address属性

if (Target.Address[0,0]=="A1")
{
 MessageBox.Show("你选择了A1单元格");
}

使用Column属性和Row属性

 int i=0;
 if (Target.Column == 1 && Target.Row < 11 && int.TryParse(Target.Text,out i))
 {
   Target.Offset[0, 1].Value = i * 3;
 }

使用Intersect方法

Excel.Range rng = this.Application.Intersect(Target, this.Application.Union(this.Range["A1:A10"], this.Range["C1:C10"]));
if (rng != null)
 {
   MessageBox.Show("你选择了" + Target.Address[0, 0] + "单元格");

使用工作表的名称

this.Application.Worksheets["工作表2"].Activate();

使用工作的索引号

this.Application.Worksheets[2].Activate();

使用工作表的代码名称

MessageBox.Show(this.Application.ActiveSheet.CodeName);

用ActiveSheet属性引用活动工作表

  this.Application.Worksheets[2].Select();
    MessageBox.Show( this.Application.ActiveSheet.Name);

选择工作表的方法

this.Application.Worksheets[2].Select();
this.Application.Worksheets[2].Activate();

使用For遍历工作表

  int wkCount = this.Application.Worksheets.Count;
  string s = string.Empty;
  for (int i = 1; i <= wkCount; i++)
   {
     s = s + this.Application.Worksheets[i].Name + "\n";
   }
  MessageBox.Show("工作簿中含有以下工作表:" + "\n" + s);

使用ForEach语句

 string s = string.Empty;
 foreach (Excel.Worksheet wk in this.Application.Worksheets)
  {
    s = s + wk.Name + "\n";
  }
 MessageBox.Show("工作簿中含有以下工作表:" + "\n" + s);

在工作表中向下翻页

 Excel.Sheets shs=Globals.ThisWorkbook.Worksheets;
  Excel.Worksheet wkThis = shs.Application.ActiveSheet;
  Excel.Worksheet wkNext;
  int wkIndex = wkThis.Index;
  int wkCount = shs.Count;
  if (wkIndex < wkCount)
    {
      wkNext = (Excel.Worksheet)wkThis.Next;
      wkNext.Select();
    }

工作表的添加与删除

Excel.Sheets wksThis = this.Application.Worksheets;
    Excel.Worksheet wsAdd = this.Application.Worksheets.Add(System.Type.Missing, wksThis[wksThis.Count]);
wsAdd.Name = "数据";

批量添加工作表

  Excel.Sheets wksThis = this.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() + "个工作表";
           }
       }

禁止删除指定工作表

  Office.CommandBarControl cmdCtl =this.Application.CommandBars[41].Controls[2];
	//可以找到删除按钮,但是无法禁止,也无法加载单击事件,非常奇怪.
    //而且在Office 2010里,也无法禁用某个按钮,但是整个菜单是可以的.

自动建立工作表目录

 int i = this.Application.Worksheets.Count;
   for (int n = 1; n <= i; n++)
   {
     this.Cells[n+1, 1].Value = this.Application.Worksheets[n].Name;
   }

建立工作表链接

int m = this.Application.Worksheets.Count;
if (Target.Count == 1)
 {
   if (Target.Column==1)
     {
       if (Target.Row>1 && Target.Row<=(m+1))
          {
           this.Application.Sheets[Target.Value].Select();
          }
      }
    }

工作表的深度隐藏

this.Application.Sheets[2].Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

防止更改工作表的名称

 void ThisWorkbook_BeforeClose(ref bool Cancel)
        {
            if (this.Sheets[1].Name != "Excel Home")
            {
                this.Sheets[1].Name = "Excel Home";
            }
            this.Save();
        }

工作表中一次插入多行

Excel.Range rng = this.Rows[3];
   rng.Resize[3].Insert();

删除工作表中的空行

 Excel.Range rng = this.UsedRange;
   int rngEnd = this.Cells[rng.Rows.Count,rng.Columns.Count].End[Excel.XlDirection.xlUp].Row;
   for (int i = rngEnd; i >=1; i++)
   {
      if (this.Application.WorksheetFunction.CountA(this.Rows[i]) == 0)
       {
         this.Rows[i].Delete();
       }
   }

删除工作表的重复行

 int rngEnd = this.Range["A65535"].End[Excel.XlDirection.xlUp].Row;
    for (int i = rngEnd; i>=1; i--)
     {
       if (this.Application.WorksheetFunction.CountIf(this.Columns[1], this.Cells[i, 1]) > 1)
        {
          this.Rows[i].Delete();
        }
     }

定位删除特定内容所在的行(删除A列中包含”Excel”字符的行

 this.Application.DisplayAlerts = false;
   int rngEnd = this.Range["A65535"].End[Excel.XlDirection.xlUp].Row;
   string str = "Excel.*";
   for (int i = rngEnd; i >= 1; i--)
    {
      Excel.Range rng = this.Cells[i, 1];
      if (Regex.IsMatch(rng.Text, str))
       {
         this.Rows[i].Delete();
       }
    }
    注:需引用using System.Text.RegularExpressions;

判断是否选中整行

int i = this.Columns.Count;
    Excel.Range rng = this.Application.Selection;
    if (rng.Columns.Count == i)
     {
       MessageBox.Show("你选中了一整行");
     }
     else
      {
        MessageBox.Show("你没有选中了一整行");
      }

限制工作表的滚动区域

this.ScrollArea = "B4:H12";

复制自动筛选后的数据区域

 this.Application.Worksheets[2].Cells.Clear();
 if (this.FilterMode)
  {
    this.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy(
    this.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);

工作表的保护与解除保护

this.Unprotect("12345");
  this.Cells[1,1].Value=100;
  this.Protect("12345");

奇偶页打印

 int pg = this.PageSetup.Pages.Count;
 for (int i = 1; i <= pg; i=i+2)
  {
    this.PrintOutEx(1, i);
}

使用工作簿的名称

string str = this.Application.Workbooks["工作簿的引用方法.xlsx"].Path;
 MessageBox.Show(str);

使用ThisWorkbook

this.Application.ThisWorkbook.Close(false);

使用ActiveWorkbook

MessageBox.Show(this.Application.ActiveWorkbook.Name);

新建工作簿

 Excel.Workbook Nowbook;
 string[] shName = new string[4] { "余额", "单价", "数量", "金额" };
 string[] arr = new string[12] { "01月", "02月", "03月", "04月", "05月", "06月", "07月", "08月", "09月", "10月", "11月", "12月" };
  this.Application.SheetsInNewWorkbook = 4;
  Nowbook = this.Application.Workbooks.Add();
  for (int i = 1; i <= 4; i++)
   {
     Nowbook.Sheets[i].Name = shName[i - 1];
     Nowbook.Sheets[i].Range["B1"].Resize[1, arr.Length] = arr;
     Nowbook.Sheets[i].Range["B2"] = "品名";
   }
  Nowbook.SaveAs("C:\\"  +"存货明细.xlsx");
  Nowbook.Close(true);

打开指定的工作簿

 int wkCount = this.Application.Workbooks.Count;
 for (int i = 1; i <= wkCount; i++)
  {
    if (this.Application.Workbooks[i].Name == "123.xlsx")
     {
       MessageBox.Show("123工作簿已经打开");
     }               
 }
this.Application.Workbooks.Open("C:\\" + "123.xlsx");

使用公式不打开工作簿取得其他工作簿数据

 if (File.Exists("C:\\11.xlsx"))
            {
                string Temp = string.Empty;
                Temp = "\'C:\\[11.xlsx]工作表1\'!";
                Excel.Range rng = this.Range["A1:F22"];
                rng.FormulaR1C1 = "=" + Temp + "RC";
            }

使用GetObject函数

//C#中貌似沒有GetObject函数

隱藏Application對象

  Excel.Application myApp = new Excel.Application();
            Excel.Worksheet sh;
            string Temp ="C:\\11.xlsx";
            myApp.Visible = false;
            sh = myApp.Workbooks.Open(Temp).Sheets[1];
            Excel.Range rng = sh.Range["A1"].CurrentRegion;
            this.Range["A1"].Resize[rng.Rows.Count, rng.Columns.Count].Value = rng.Value;

使用ExecuteExcel4Macro方法

 private void button1_Click(object sender, EventArgs e)
        {
            object Rcout, Ccout;      // ExecuteExcel4Macro返回值為object類型
            String temp, temp1, temp2, temp3;
            object[,] arr;
            temp = "\'C:\\[11.xlsx]工作表1\'!";
            temp1=temp + this.Rows[1].Address[
                System.Type.Missing,
                System.Type.Missing,
                Excel.XlReferenceStyle.xlR1C1];
            temp1 = "Counta(" + temp1 + ")";
            Ccout = this.Application.ExecuteExcel4Macro(temp1);
            temp2 = temp + this.Columns["A"].Address[
                System.Type.Missing,
                System.Type.Missing,
                Excel.XlReferenceStyle.xlR1C1];
            temp2 = "Counta(" + temp2 + ")";
            Rcout = this.Application.ExecuteExcel4Macro(temp2);
            int R = int.Parse(Rcout.ToString());    //將object轉為int
            int C = int.Parse(Ccout.ToString());
            arr = new object[R,C];
            for(int m=0;m<R;m++)        //C#数組下標為0
            {
                for (int n = 0; n <C; n++)
                {
                    temp3 = temp + this.Cells[m+1,n+1].Address[
                       System.Type.Missing,
                       System.Type.Missing,
                       Excel.XlReferenceStyle.xlR1C1];
                    arr[m, n] = this.Application.ExecuteExcel4Macro(temp3);  
                }
            }
            this.Range["A1"].Resize[R, C].Value = arr;
        }

使用SQL連接

private void button1_Click(object sender, EventArgs e)
        {
            DataSet DS = ExcelOpen("C:\\11.xlsx");
            DataTable DT = new DataTable();
            DT = DS.Tables[0];
            if (DT != null)
            {
                for (int m = 0; m < DT.Rows.Count; m++)
                {
                    for (int n = 0; n < DT.Columns.Count; n++)
                    {
                        this.Cells[m + 1, n + 1].Value = DT.Rows[m][n].ToString();
                    }
                }
            }
        }
        private DataSet ExcelOpen(string str)   //讀取一個Excel,并返回一個DataSet
        {
            string ConnStr;
            ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + str + " ;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"";
            OleDbConnection ExcelConn = new OleDbConnection(ConnStr);
            try
            {
                ExcelConn.Open();
                if (ExcelConn.State == ConnectionState.Open)
                {
                    OleDbDataAdapter ExcelDA = new OleDbDataAdapter("select * from [工作表1$]", ExcelConn);     //這里我只讀取了工作表1的数据,如果需要讀取其它工作表,可以將工作表名設置為ExcelOpen的一個參数
                    DataSet ExcelDS = new DataSet();
                    ExcelDA.Fill(ExcelDS);
                    ExcelConn.Close();
                    return ExcelDS;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("提示信息:" + ex.ToString());
                return null;
            }
        }

返回窗口的可視區域地址

string str;
            str = this.Application.ActiveWindow.VisibleRange.Address[0, 0];
            MessageBox.Show("窗品的可視區域為:" + str);

在工作表中添加图形

Excel.Shape myShape;
            try
            {
                this.Shapes.Item("myShape").Delete();
            }
            catch
            {
            }
            myShape = this.Shapes.AddShape(
                    Office.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 = this.Application.Selection.ShapeRange;
            myShapeRng.Line.Weight = 1;
            myShapeRng.Line.DashStyle = Office.MsoLineDashStyle.msoLineSolid;
            myShapeRng.Line.Style = Office.MsoLineStyle.msoLineSingle;
            myShapeRng.Line.Transparency = 0;
            myShapeRng.Line.Visible = Office.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 = Office.MsoTriState.msoTrue;
            myShapeRng.Fill.ForeColor.SchemeColor = 41;
            myShapeRng.Fill.OneColorGradient(
                Office.MsoGradientStyle.msoGradientHorizontal,
                4, 0.23F);       //OneColorGradient第三個參数為Float,C#中默認0.23為double,所以需要在0.23后面加上F,指定為Float
            this.Range["A1"].Select();
            this.Hyperlinks.Add(myShape, "", "工作表2!A1", "選擇工作表2!");

导出出工作表中的图片

 string FileName = string.Empty;
            Excel.ChartObjects chartObjs = (Excel.ChartObjects)this.ChartObjects(missing);
            
            foreach (Excel.Shape myShape in this.Shapes)
            {
                FileName = "C:\\" + myShape.Name + ".gif";
                myShape.Copy();
                Excel.Chart myChart = chartObjs.Add(0, 0, myShape.Width + 28, myShape.Height + 30).Chart;
                myChart.Paste();
                myChart.Export(FileName, "gif");
                myChart.Parent.Delete();
            }

在工作表中添加艺术字

 Excel.Shape myShape;
            try
            {
                this.Shapes.Item("myShape").Delete();
            }
            catch
            {
            }
            myShape = this.Shapes.AddTextEffect(
                Office.MsoPresetTextEffect.msoTextEffect15,
                "我愛Excel Home",
                "新細明體",
                36,
                Office.MsoTriState.msoFalse,
                Office.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 = Office.MsoLineDashStyle.msoLineSolid;
            myShape.Line.Style = Office.MsoLineStyle.msoLineSingle;
            myShape.Line.Transparency = 0;
            myShape.Line.ForeColor.SchemeColor = 12;
            myShape.Line.BackColor.RGB = 65536 * 255 + 256 * 255 + 255;
        }

遍历工作表中的图片

int i = this.Shapes.Count;
            for (int m = 1; m <= i; m++)
            {
                this.Cells[m, 1].Value = this.Shapes.Item(m).Name;
            }

移动、旋转图片

Excel.Shape myShape = this.Shapes.Item(2);
            for (int m = 1; m <= 300; m = m + 5)
            {
                myShape.Top = (float)System.Math.Sin(m * (3.1416 / 180)) * 100 + 100;
                myShape.Left = (float)System.Math.Cos(m * (3.1416 / 180)) * 100 + 100;
                myShape.Fill.ForeColor.RGB = m * 100;
                for (int n = 1; n <= 10; n++)
                {
                    myShape.IncrementRotation(-2);
                    
                }
            }
//沒找到資料如何調用VBA內置函数DoEvents

工作表中自动插入图片

 string FilePath;
            Excel.Range rng;
            string temp = string.Empty;
            for (int i = 3; i <= this.Range["A65535"].End[Excel.XlDirection.xlUp].Row; i++)
            {
                FilePath = "C:\\Users\\Public\\Pictures\\Sample Pictures\\" + this.Cells[i, 1].Text + ".jpg";
                if (File.Exists(FilePath))
                {
                    rng = this.Cells[i, 3];
                    this.Shapes.AddPicture(FilePath,
                        Office.MsoTriState.msoTrue,
                        Office.MsoTriState.msoTrue,
                        rng.Left + 1,
                        rng.Top + 1,
                        rng.Width + 1,
                        rng.Height + 1);
                }
                else
                {
                    temp = temp + "\n" + this.Cells[i, 1].Text ;
                }
            }
            if (temp != string.Empty)
            {
                MessageBox.Show(temp + "\n" + "沒有照片");
            }