ExcelVBA常用代码VSTO版

VSTO for excel入门基础知识

常用语法IF 语句private void button11_Click(object sender, EventArgs e) {int i;i = -25;//-2147483648 到 2147483647 ，有符号 32 位整数if (i > 0){MessageBox.Show(i + "是正数");}else if (i == 0){MessageBox.Show(i + "是零");}else{MessageBox.Show(i + "是负数");}//更多分之使用Switch结构，类似select case}While语句private void button12_Click(object sender, EventArgs e) {int i;i = 0;while (i < 10)//括号内条件成立，不断执行花括号内容{i = i + 1;}this.button14.Text = i.ToString();//}FOR语句private void button13_Click(object sender, EventArgs e) {int i;//;int sum = 0;for (i = 1; i <= 10; i++)//i++自加的意思if (i <= 5)//if (i <=5) {break;}{sum = sum + i;}else{break;//跳出FORB不再执行。

}}this.button13.Text = sum.ToString();}FOR语句private void button14_Click(object sender, EventArgs e){//int i;这句声明变量,作用于整个单机过程int sum = 0;for (int i = 1; i <= 10; i++)//也可在FOR循环内部声明直接加变量int，只做用内部{if (i==6)//i等于6{continue;}//当I=6跳过不加，到7的时候继续加，比55小于6//严格区分break 和continue语句{sum = sum + i;}}this.button13.Text = sum.ToString();}FOR Each 语句private void button15_Click(object sender, EventArgs e){int[] arr = { 2, 5, 7 };//声明数组。

excel vba常用代码

excel vba常用代码Excel VBA是一种用于编写宏的编程语言，它可以帮助我们在Excel 中进行自动化操作。

在VBA中，有一些常用的代码，可以帮助我们快速完成一些常见的任务。

本文将介绍一些常用的Excel VBA代码，帮助读者更好地理解并运用它们。

一、数据处理1.1 数据筛选在Excel中，我们经常需要根据某些条件筛选数据。

使用VBA可以实现自动筛选，代码如下：```ActiveSheet.Range("A1:D10").AutoFilter Field:=1, Criteria1:=">10"```以上代码将自动筛选出范围为A1:D10的数据，其中第一列大于10的数据。

1.2 数据排序有时候，我们需要对数据进行排序。

使用VBA可以实现自动排序，代码如下：```ActiveSheet.Range("A1:D10").Sort Key1:=Range("A1"), Order1:=xlAscending```以上代码将自动对范围为A1:D10的数据根据第一列进行升序排序。

1.3 数据透视表数据透视表可以帮助我们对数据进行汇总和分析。

使用VBA可以自动生成数据透视表，代码如下：```ActiveSheet.PivotTableWizard```以上代码将自动生成一个数据透视表。

二、单元格操作2.1 单元格赋值在VBA中，我们可以使用代码将某个值赋给指定的单元格，代码如下：```Range("A1").Value = "Hello World"```以上代码将把"Hello World"赋值给A1单元格。

2.2 单元格格式设置使用VBA可以设置单元格的格式，例如设置字体、颜色、边框等，代码如下：```Range("A1").Font.Bold = TrueRange("A1").Interior.Color = RGB(255, 0, 0)Range("A1").Borders.LineStyle = xlContinuous```以上代码将设置A1单元格的字体为粗体、背景色为红色、边框为实线。

Excel VBA常用代码VSTO版20150425

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( );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 + + "\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]); = "数据";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]); = "第" + i.ToString() + "个工作表";}}26-1 禁止删除指定工作表mandBarControl cmdCtl =mandBars[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 = edRange;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 使用ThisWorkbookthis.Application.ThisWorkbook.Close(false);40-4 使用ActiveWorkbookMessageBox.Show();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");。

Excel VBA常用代码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/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属性edRange.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 = edRange.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 设计器生成的代码”内加入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.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);。

visual studio2010 vb excel代码

visual studio2010 vb excel代码以下是一个使用Visual Studio 2010中的代码来操作Excel的示例：vbImports Microsoft.Office.InteropPublic Class Form1Private Sub Button1_Click(sender As Object, e As EventArgs)Handles Button1.Click'创建Excel应用程序对象Dim excelApp As New Excel.Application'打开Excel文件Dim workbook As Excel.Workbook =excelApp.Workbooks.Open("C:\example.xlsx")'选择工作表Dim worksheet As Excel.Worksheet = workbook.Worksheets("Sheet1") '设置单元格的值worksheet.Range("A1").Value = "Hello"worksheet.Range("B1").Value = "World"'保存Excel文件workbook.Save()'关闭Excel应用程序excelApp.Quit()End SubEnd Class在此示例中，我们使用了Microsoft Office Interop库来操作Excel。

首先，我们创建了一个Excel应用程序对象，然后打开一个Excel文件并选择要操作的工作表。

接下来，我们设置了单元格的值，然后保存Excel文件并关闭Excel应用程序。

excelvba常用代码总结

Excel VBA常用代码总结1改变背景色Range("A1"). = xlNoneColorIndex一览改变文字颜色Range("A1"). = 1获取单元格Cells(1, 2)Range("H7")获取范围Range(Cells(2, 3), Cells(4, 5))Range("a1:c3")'用快捷记号引用单元格Worksheets("Sheet1").[A1:B5]选中某sheetSet NewSheet = Sheets("sheet1")选中或激活某单元格'“Range”对象的的Select方法可以选择一个或多个单元格，而Activate 方法可以指定某一个单元格为活动单元格。

'下面的代码首先选择A1:E10区域，同时激活D4单元格：Range("a1:e10").SelectRange("d4:e5").Activate'而对于下面的代码：Range("a1:e10").SelectRange("f11:g15").Activate'由于区域A1:E10和F11:G15没有公共区域，将最终选择F11:G15，并激活F11单元格。

获得文档的路径和文件名'路徑'名稱'路徑＋名稱'或将ActiveWorkbook换成thisworkbook隐藏文档= False禁止屏幕更新= False禁止显示提示和警告消息= False文件夹做成strPath = "C:\temp\"MkDir strPath状态栏文字表示= "计算中"双击单元格内容变换Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)If >= 5And <= 8) ThenIf = "●"Then= ""Else= "●"End IfCancel = TrueEnd IfEnd Sub文件夹选择框方法1Set objShell = CreateObject("")Set objFolder = (0, "文件", 0, 0)If Not objFolder Is NothingThen path= & "\"end ifSet objFolder = NothingSet objShell = Nothing文件夹选择框方法2（推荐）Public Function ChooseFolder() As StringDim dlgOpen As FileDialogSet dlgOpen = (msoFileDialogFolderPicker)With dlgOpen.InitialFileName = & "\"If .Show = -1ThenChooseFolder = .SelectedItems(1)End IfEnd WithSet dlgOpen = NothingEnd Function'使用方法例：Dim path As Stringpath = ChooseFolder()If path <> ""ThenMsgBox"open folder"End If文件选择框方法Public Function ChooseOneFile(Optional TitleStr As String = "Please choose a file", Optional TypesDec As String = "*.*", Optional Exten As String = "*.*") As StringDim dlgOpen As FileDialogSet dlgOpen = (msoFileDialogFilePicker)With dlgOpen.Title = TitleStr.. TypesDec, Exten.AllowMultiSelect = False.InitialFileName =If .Show = -1Then' .AllowMultiSelect = True' For Each vrtSelectedItem In .SelectedItems' MsgBox "Path name: " & vrtSelectedItem' Next vrtSelectedItemChooseOneFile = .SelectedItems(1)End IfEnd WithSet dlgOpen = NothingEnd Function某列到关键字为止循环方法1(假设关键字是end)Set CurrentCell = Range("A1")Do While <> "end"……Set CurrentCell = (1, 0)Loop某列到关键字为止循环方法2(假设关键字是空字符串)i = StartRowDo While Cells(i, 1) <> ""……i = i + 1Loop"For Each...Next 循环(知道确切边界)For Each c In Worksheets("Sheet1").Range("A1:D10").Cells If Abs < Then = 0Next"For Each...Next 循环(不知道确切边界),在活动单元格周围的区域内循环For Each c In If Abs < Then = 0Next某列有数据的最末行的行数的取得(中间不能有空行)lonRow=1Do While Trim(Cells(lonRow, ２).Value) <> ""lonRow = lonRow + 1LooplonRow11 = lonRow11 - 1A列有数据的最末行的行数的取得另一种方法Range("Ａ65536").End(xlUp).Row将文字复制到剪贴板Dim MyData As DataObjectSet MyData = New DataObjectRange("H7").Value取得路径中的文件名Private Function GetFileName(ByVal s As String)Dim sname() As Stringsname = Split(s, "\")GetFileName = sname(UBound(sname))End Function取得路径中的路径名Private Function GetPathName(ByVal s As String)intFileNameStart = InStrRev(s, "\")GetPathName = Mid(s, 1, intFileNameStart)End Function由模板sheet拷贝做成一个新的sheet("template").Copy After:=Set doc_s == "newsheetname" & Format(Now, "yyyyMMddhhmmss")选中当列的最后一个有内容的单元格(中间不能有空行)'删除B3开始到B列最后一个有内容的单元格为止的所有内容Range("B3").SelectRange(Selection, (xlDown)).Select常量定义Private Const StartRow As Integer = 3判断sheet是否存在Private Function IsWorksheet(ByVal strSeetName As String) As Boolean On Error GoTo ErrHandleDim blnRet As BooleanblnRet = IsNull(Worksheets(strSeetName))IsWorksheet = TrueExit FunctionErrHandle:IsWorksheet = FalseEnd Function向单元格中写入公式Worksheets("Sheet1").Range("D6").Formula = "=SUM(D2:D5)"引用命名单元格区域Range("!MyRange")Range("[]Sheet1!Sales"选定命名的单元格区域Reference:="!MyRange"'或者worksheets("sheetname").range("rangename").select使用Dictionary'使用Dictionary需要添加参照Microsoft Scripting RuntimeDim dic As New Dictionary"Table", "Cards"'前面是 Key 后面是 Value"Serial", "serialno""Number", "surface"MsgBox ("Table") '由Key取得Value("Table") '判断某Key是否存在将EXCEL表格中的两列表格插入到一个Dictionary中'函数：在ws工作表中，从iStartRow行开始到没有数据为止，把iKeyCol 列和iKeyCol右一列插入到一个字典中，并返回字典。

VB对Excel文件的操作代码

VB对Excel文件的操作代码Dim A(65536) As StringDim i As IntegerDim xlsApp As ObjectDim xlsWorkbook As ObjectSet xlsApp = CreateObject("Excel.Application")Set xlsWorkbook = xlsApp.Workbooks.Open("D:\文件名.xls") xlsApp.Visible = FalsexlsApp.Range("A1").Selecti = -1Doi = i + 1A(i) = xlsApp.ActiveCell.Offset(0, i).ValueIf A(i) <> "" ThenPrint i + 1;Print " ";Print A(i)End IfLoop Until A(i) = ""If Dir("D:\文件名2.xls") <> "" Then Kill ("D:\文件名2.xls")xlsWorkbook.saveas ("D:\文件名2.xls")xlsApp.Workbooks.ClosexlsApp.QuitSet xlsSheet = NothingSet xlsWorkbook = NothingSet xlsApp = Nothing'以下为Excel操作的其他一些功能'Set xlsSheet = xlsWorkbook.Worksheets("表名") '设置活动工作表'xlsSheet.Cells(行,列) = "值" '赋值'xlsApp.WorkBooks.Add '添加工作薄'xlsApp.WorkSheets.Add '添加工作表'xlsWorkbook.saveas ("文件名") '保存文件'xlsApp.ActiveSheet.Columns(1).ColumnWidth = 20 '设置列宽'xlsApp.ActiveSheet.Rows(1).RowHeight = 30 '设置行高'xlsApp.ActiveSheet.rows(8).pagebreak = 1 '插入分页符（值为0时删除分页符）'xlsApp.ActiveSheet.PageSetup.CenterHeader = "标题" '页眉设置。

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/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属性edRange.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 = edRange.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 设计器生成的代码”内加入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.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 (ment == null){rng.AddComment(rng.Text);ment.Visible = true;}14-3 刪除單元格中的批注Excel.Range rng = this.Range["A1"];if (ment != null){ment.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( );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 + + "\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]); = "数据";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]); = "第" + i.ToString() + "个工作表";}}26-1 禁止删除指定工作表mandBarControl cmdCtl =mandBars[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 = edRange;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 使用ThisWorkbookthis.Application.ThisWorkbook.Close(false);40-4 使用ActiveWorkbookMessageBox.Show();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"){}}this.Application.Workbooks.Open("C:\\" + "123.xlsx");。