ExcelVBA常用代码VSTO版

Excel VBA常用代码VSTO版(C#) 1-1使用Range属性 this.Range["A3:F6, B1:C5"].Select(); 1-2使用Cells属性 for(int icell=1;icell<=100;icell++) { this.Application.Worksheets[2].cells[icell, 1].value = icell; } 1-3使用快捷记号 #N/A 1-4使用Offset属性 this.Range["A1:A3"].Offset[3, 3].Select(); 1-5使用Resize属性 this.Range["A1"].Resize[3, 3].Select(); 1-6使用Union属性 this.Application.Union(this.Range["A1:D4"], this.Range["E5:H8"]).Select(); 1-7使用UsedRange属性 this.UsedRange.Select(); 1-8使用CurrentRegion属性 this.Range["A5"].CurrentRegion.Select(); 2-1 使用Select方法 this.Application.Worksheets[3].Activate(); this.Application.Worksheets[3].Range["A1:B10"].Select(); 2-2 使用Activate方法 this.Application.Worksheets[3].Activate(); this.Application.Worksheets[3].Range["A1:B10"].Activate(); 注:此处的代码,可以运行,但是只会选中A1这一个单元格 2-3 使用Goto方法 this.Application.Goto(this.Application.Worksheets[3].Range["A1:B10"], true); 3-1 获得指定行,列中的最后一个非空单元格 Excel.Range rng = this.Range["A65535"].End[Excel.XlDirection.xlUp]; MessageBox.Show("A列中最后一个非空单元格是" + rng.Address[0, 0] + ",行号" + rng.Row.ToString() + ",数值" + rng.Text); 4-1 定位单元格 Excel.Range rng = this.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas); rng.Select(); MessageBox.Show("工作表中有公式的单元格为:" + rng.Address); 5-1 查找单元格 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语句. 5-1 查找单元格重复数据 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); } } 5-2 使用Like运算符 C#中没有Like运算符,可以用正则表达式来处理. 6-1 替换单元格内字符串 this.Range["A1:A5"].Replace("通州", "南通"); 7-1 复制单元格区域 this.Application.DisplayAlerts = false; this.Range["A1"].CurrentRegion.Copy(this.Application.Worksheets[2].Range["A1"]); this.Application.DisplayAlerts = true; 7-2 复制单元格区域时带列宽大小 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; 8-1 使用选择性粘贴 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; 8-2 直接赋值的方法 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; 9-1 单元格自动进入编辑状态 先在”VSTO 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 器生成的代码”内加入 this.SelectionChange += new Excel.DocEvents_SelectionChangeEventHandler(工作表1_SelectionChange); 然后在事件代码中输入 if (Target.Column == 3 && Target.Count == 1) { if (Target.Text == string.Empty) { this.Application.SendKeys("{F2}"); } } 10-1 禁用单元格拖放功能 if (this.Application.Intersect(Target, this.Range["A1:A15"]) != null) { this.Application.CellDragAndDrop = false; } else { this.Application.CellDragAndDrop = true; } 11-1 单元格字体格式设置 Excel.Font rng = this.Range["A1"].Font; rng.Name = "宋体"; rng.FontStyle = "Bold"; rng.Size = 18; rng.ColorIndex = 3; rng.Underline = 2; 11-2 设置单元格内部格式 Excel.Interior rng = this.Range["A1"].Interior; rng.ColorIndex = 3; rng.Pattern = Excel.XlPattern.xlPatternCrissCross; rng.PatternColorIndex = 6; 11-3 为单元格区域添加边框 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); 11-3 为单元格区域应用多种边框格式 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); 11-4 靈活設置單元格的行高列寬 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); 12-1 單元格中建立數據有效性 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"); 12-2 判斷單元格是否存在數據有效性 try { if (this.Range["A12"].Validation.Type >= 0) { MessageBox.Show("單元格中有數據有效性!"); } } catch { MessageBox.Show("單元格中沒有數據有效性!"); } 12-3 動態的數據有效性 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, "主機,顯示器"); } } 12-4 自動展開數據有效性下拉列表 this.Application.SendKeys("%{down}"); 13-1 在單元格中寫入公式 this.Range["C1:C10"].Formula="=sum(A1,B1)"; 13-1 寫入單元格區域數組公式 this.Range["C1"].FormulaArray = "=A1:A2*B1:B2"; 13-2 檢查單元格是否含有公式 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貌似并不支持. 13-3 判斷單元格公式是否存在錯誤 未研究出來,如何調用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); } 13-4 取得單元格中公式的引用單元格 Excel.Range rng = this.Range["C1"].Precedents; MessageBox.Show("公式所引用的單元格有:" + rng.Address); 13-5 將單元格中的公式轉換為數值 Excel.Range rng = this.Range["C1:C10"]; rng.Formula = "=sum(A1:B1)"; rng.Value = rng.Value; 14-1 判斷單元格是否存在指注 if (this.Range["A1"].Comment == null) { MessageBox.Show("A1單元格中沒有批注"); } else { MessageBox.Show("A1單元格中批注內容為:" + "\n" + this.Range["A1"].Comment.Text()); } 14-2 為單元格添加批注 Excel.Range rng = this.Range["A1"]; if (rng.Comment == null) { rng.AddComment(rng.Text); rng.Comment.Visible = true; } 14-3 刪除單元格中的批注 Excel.Range rng = this.Range["A1"]; if (rng.Comment != null) { rng.Comment.Delete(); } 15-1 判斷單元格區域是否存在合并單元格 Excel.Range rng = this.Application.Selection; if (Convert.IsDBNull(rng.MergeCells)) { MessageBox.Show("區域中包含合并單元格!"); } else if (rng.MergeCells) { MessageBox.Show("區域中全部為合并單元格!"); } else { MessageBox.Show("區域中沒有合并單元格!"); } 15-2 合并單元格時連接每個單元格的文本 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; 15-3 合并內容相同的連續單元格 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(); } } 15-4 取消合并單元格時在每個單元格中保留內容 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; } } 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:D10"].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 = "数据"; 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; 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");