​ Excel.Worksheet wks = m_excell_app.ActiveSheet as Excel.Worksheet; foreach (Excel.AllowEditRange aer in wks.Protection.AllowEditRanges) aer.Delete(); wks.Protection.AllowEditRanges.Add("s", (Excel.Range)wks.Columns["C:H", Type.Missing], Type.Missing); wks.Protection.AllowEditRanges.Add("s1", (Excel.Range)wks.Columns["X:AR", Type.Missing], Type.Missing); int i = 2; foreach (Excel.Range e in wks.UsedRange.Rows) Excel.Range cell = (Excel.Range)wks.Cells[e.Row, 17]; if (cell.Value2 == null) wks.Protection.AllowEditRanges.Add("s" + i.ToString(), cell, Type.Missing); Convert.ToDouble(cell.FormulaR1C1.ToString()); wks.Protection.AllowEditRanges.Add("s" + i.ToString(), cell, Type.Missing); catch (Exception ex) wks.Protect(Type.Missing, true, true, false, false, false, false, false, false, false, false, false, false, false, false, false);

当前工作簿：

workBook = Globals.ThisAddIn.Application.ActiveWorkbook;

5.排序：

​ Range myRange = (Excel.Range)workSheet.Cells[1, 1]; workSheet.Sort.SortFields.Add(myRange, Excel.XlSortOn.xlSortOnValues, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal); myRange = workSheet.get_Range("A1", "A10"); workSheet.Sort.SetRange(myRange); workSheet.Sort.Header = Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo; workSheet.Sort.MatchCase = false; workSheet.Sort.Orientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns; workSheet.Sort.SortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin; workSheet.Sort.Apply();

6.设置单元格格式：

​ //显示格式

​ setRange.NumberFormatLocal = "$#,##0_); 红色 ";
//背景色 setRange.Interior.ColorIndex = 3; setRange.Cells.Borders.ColorIndex = 1; //设置单元格中不同字符为不同颜色，这个功能只有到07后才有的
Range rangeStyle = (Range)mgrSummary.UsedRange[changeStart, 1]; if (rangeStyle != null) object styleValue = rangeStyle.Value2; if (styleValue != null) Characters changeStyle = rangeStyle.get_Characters(0, rangeStyle.Value2.ToString().Length); changeStyle.Font.Color = Color.Red.ToArgb();

7.查找单元格内容：

​ usedRange.Find(string, miss, XlFindLookIn.xlFormulas, XlLookAt.xlWhole, XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false, false, miss); 8.单元格区域拷贝： worksheetRange = worksheet.get_Range(columnName, miss); newWorksheetRange = newWorkSheet.get_Range(columnName, miss); worksheetRange.Copy(newWorksheetRange); 9.产生数据透视表 private Worksheet GeneratetPivot(Worksheet worksheet, PivotCaches pivotCaches) Worksheet worksheetPivot = (Worksheet)worksheets.Add(miss, worksheet, 1, miss); worksheetPivot.Name = PivotName; PivotCache pivotCache = pivotCaches.Add(XlPivotTableSourceType.xlDatabase, worksheetLoan.UsedRange); Range range = worksheetPivot.get_Range("A1", miss);

​ //创建数据透视表 PivotTable pivotTable = pivotCache.CreatePivotTable(range, tPivotName, true, XlPivotTableVersionList.xlPivotTableVersionCurrent); //Adds row, column, and page fields to a PivotTable report or PivotChart report pivotTable.AddFields("Name", miss, miss, true); pivotTable.AddFields("Amount", miss, miss, true); PivotField pivotField = (PivotField)pivotTable.PivotFields("Name"); pivotField.Orientation = XlPivotFieldOrientation.xlRowField; pivotField = (PivotField)pivotTable.PivotFields("Amount"); pivotField.Orientation = XlPivotFieldOrientation.xlDataField; pivotField.Function = XlConsolidationFunction.xlSum; return worksheetPivot;