Office中国论坛/Access中国论坛

标题: Excel VBA常用代码VSTO版(C#)之三-Winner [打印本页]

作者: tmtony 时间: 2015-8-17 15:45
标题: Excel VBA常用代码VSTO版(C#)之三-Winner
16-1 高亮顯示單元格區域
Excel.Range rng = this.Application.Selection;
Cells.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
rng.Interior.ColorIndex = 8;

17-1 雙擊被保護單元格時不顯示提示消息框
if (Target.Locked)
{
MessageBox.Show("此單元格已保護,不能編輯");
Cancel = true;
}

18-1 重新計算工作表指定區域
Excel.XlCalculation oldCalcultion = this.Application.Calculation;
this.Application.Calculation = Excel.XlCalculation.xlCalculationManual;
this.Range["A1

10"].Calculate();
this.Application.Calculation = oldCalcultion;

19-1 錄入數據后單元格自動保護
if (this.ProtectContents)
{
this.Unprotect("123456");
}
if (Target.Text != string.Empty)
  {
Target.Locked = true;
this.Protect("123456");
  }

20-1 使用單元格的Address屬性
if (Target.Address[0,0]=="A1")
{
MessageBox.Show("你選擇了A1單元格");
}

20-2 使用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;
}

20-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] + "單元格");
}

21-1 使用工作表的名称
         this.Application.Worksheets["工作表2"].Activate();

21-2 使用工作的索引号
         this.Application.Worksheets[2].Activate();

21-3 使用工作表的代码名称
         MessageBox.Show(this.Application.ActiveSheet.CodeName);

21-4 用ActiveSheet属性引用活动工作表
this.Application.Worksheets[2].Select();
MessageBox.Show( this.Application.ActiveSheet.Name);

22-1 选择工作表的方法
         this.Application.Worksheets[2].Select();
         this.Application.Worksheets[2].Activate();

23-1 使用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);

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

24-1 在工作表中向下翻页
  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();
}

25-1 工作表的添加与删除
         Excel.Sheets wksThis = this.Application.Worksheets;
Excel.Worksheet wsAdd = this.Application.Worksheets.Add(System.Type.Missing, wksThis[wksThis.Count]);
wsAdd.Name = "数据";

25-1 批量添加工作表
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() + "个工作表";
         }
   }

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

27-1 自动建立工作表目录
int i = this.Application.Worksheets.Count;
for (int n = 1; n <= i; n++)
{
   this.Cells[n+1, 1].Value = this.Application.Worksheets[n].Name;
}

27-1 建立工作表链接
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();
         }
      }
   }

28-1 工作表的深度隐藏
this.Application.Sheets[2].Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

29-1 防止更改工作表的名称
      void ThisWorkbook_BeforeClose(ref bool Cancel)
      {
         if (this.Sheets[1].Name != "Excel Home")
         {
            this.Sheets[1].Name = "Excel Home";
         }
         this.Save();
      }

30-1 工作表中一次插入多行
Excel.Range rng = this.Rows[3];
rng.Resize[3].Insert();

31-1 删除工作表中的空行
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();
   }
}

32-1 删除工作表的重复行
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();
      }
   }

33-1 定位删除特定内容所在的行(删除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;

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

35-1 限制工作表的滚动区域
this.ScrollArea = "B4:H12";

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

37-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);

38-1 工作表的保护与解除保护
  this.Unprotect("12345");
  this.Cells[1,1].Value=100;
  this.Protect("12345");

39-1 奇偶页打印
int pg = this.PageSetup.Pages.Count;
for (int i = 1; i <= pg; i=i+2)
  {
this.PrintOutEx(1, i);
}

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

40-3 使用ThisWorkbook
         this.Application.ThisWorkbook.Close(false);

40-4 使用ActiveWorkbook
         MessageBox.Show(this.Application.ActiveWorkbook.Name);

41-1 新建工作簿
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);

42-1 打开指定的工作簿
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");

作者: 风中漫步 时间: 2015-8-17 16:38
谢谢分享.站长转向c#了？

作者: tmtony 时间: 2015-8-18 10:16
也要学点新东西哦

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)