ExcelVBA常用代码VSTO版

ExcelVBA常用代码VSTO版(C#)1-1使用Range属性this.Range[A3:F6,B1:C5].Select();1-2使用Cells属性for(inticell=1;icell=100;icell++){this.Application.Worksheets[2].cells[icell,1].value=icell;}1-3使用快捷记号#N/A1-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.Rangerng=this.Range[A65535].End[Excel.XlDirection.xlUp];MessageBox.Show(A列中最后一个非空单元格是+rng.Address[0,0]+,行号+rng.Row.ToString()+,数值+rng.Text);4-1定位单元格Excel.Rangerng=this.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);rng.Select();MessageBox.Show(工作表中有公式的单元格为:+rng.Address);5-1查找单元格Excel.Rangerng,Rng;Rng=this.Range[A:A];stringstrFind=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……Endwith语句.5-1查找单元格重复数据Excel.Rangerng,Rng;stringFindAddress=string.Empty;Rng=this.Range[A:A];stringstrFind=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.Rangerng=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.Rangerng=this.Application.Worksheets[3].Range[A1];rng.PasteSpecial(Excel.XlPasteType.xlPasteValues);this.Application.CutCopyMode=Excel.XlCutCopyMode.xlCut;8-2直接赋值的方法Excel.Rangerng=this.Application.Worksheets[3].Range[A1];Excel.RangeRng=this.Range[A1].CurrentRegion;rng.Resize[Rng.Rows.Count,Rng.Columns.Count].Value=Rng.Value;9-1单元格自动进入编辑状态先在”VSTO设计器生成的代码”内加入this.SelectionChange+=newExcel.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.Fontrng=this.Range[A1].Font;rng.Name=宋体;rng.FontStyle=Bold;rng.Size=18;rng.ColorIndex=3;rng.Underline=2;11-2设置单元格内部格式Excel.Interiorrng=this.Range[A1].Interior;rng.ColorIndex=3;rng.Pattern=Excel.XlPattern.xlPatternCrissCross;rng.PatternColorIndex=6;11-3为单元格区域添加边框Excel.Bordersrng=this.Range[B4:G10].Borders;rng.LineStyle=Excel.XlLineStyle.xlContinuous;rng.Weight=Excel.XlBorderWeight.xlThin;rng.ColorIndex=5;Excel.XlColorIndexcol=(Excel.XlColorIndex)5;this.Range[B4:G10].BorderAround2(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlMedium,col);11-3为单元格区域应用多种边框格式Excel.XlColorIndexcol=(Excel.XlColorIndex)5;Excel.Borderrng=this.Range[B4:G10].Borders[Excel.XlBordersIndex.xlInsideHorizontal];Excel.BorderRng=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.Rangerng1=this.Range[A1];Excel.Rangerng2=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.Rangerng=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動態的數據有效性