C# Microsoft.Office.Interop.Excel.get_Range方法代码示例

        public void Delete_Unused_Columns(Excel.Worksheet RawData_Sheet)
        {
            Excel.Range Unused_Range = RawData_Sheet.get_Range("E1", "N" + RawData_Sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row);

            Unused_Range.Delete();

            Excel.Range Unused_Range_2 = RawData_Sheet.get_Range("K1", "AI" + RawData_Sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row);

            Unused_Range_2.Delete();
        }

        //Method to get value; cellname is A1,A2, or B1,B2 etc...in excel.
        private string ExcelGetValue(string cellname, Excel._Worksheet sheet)
        {
            string value = string.Empty;
            try {
                value = sheet.get_Range(cellname).get_Value().ToString();
            } catch {
                value = "";
            }

            return value;
        }

        private void ColorCodeColumn(Excel.Worksheet ws, string range)
        {
            // Create a two-color ColorScale object for the created sample data
            // range.
            Excel.Top10 cfColorScale = (Excel.Top10)(ws.get_Range(range,
                Type.Missing).FormatConditions.AddTop10());

            cfColorScale.Interior.Color = 0x0000FF00;
            // Set the minimum threshold to red (0x000000FF) and maximum threshold
            // to green (0x0000FF00).
            //cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF;
            //cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x0000FF00;
        }

        public void Add_Metric_Calculations(Excel.Worksheet RawData_Sheet)
        {
            // Creates the range for average report write times
            Excel.Range Metric1 = RawData_Sheet.get_Range("K1", "K" + RawData_Sheet.UsedRange.Rows.Count);

            // Creates the range for the average check times
            Excel.Range Metric2 = RawData_Sheet.get_Range("L1", "L" + RawData_Sheet.UsedRange.Rows.Count);

            // Creates the range for the average invoice times
            Excel.Range Metric3 = RawData_Sheet.get_Range("M1", "M" + RawData_Sheet.UsedRange.Rows.Count);

            // Sets the formula for the average report write times (excludes those written by May or Shah)
            Metric1.Formula = "=IF(AND(B1=\"A\" , (G1-E1)>=0, F1 <> \"May\", F1 <> \"Shah\"), G1-E1, \" Invalid\" )";

            // Sets the formula for the average report check times (no exclusions needed as rows marked invalid from previous formula are deleted)
            Metric2.Formula = "=IF((H1-G1)>=0, H1-G1, \" Invalid\" )";

            Metric3.Formula = "=IF((J1-I1)>=0, J1-I1, \" Invalid\" )";

            Metric1.Cells[1] = "Time for Report";
            Metric2.Cells[1] = "Time for Check";
            Metric3.Cells[1] = "Time for Invoice";
        }

        private void CreateGraph(IScriptWorker CurrentScriptWorker, Excel.Worksheet xlWorkSheet, int RowCounter, int ColumnCounterForNamesOfTests)
        {
            Excel.Range chartRange;
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = xlCharts.Add(100, 200, 800, 350);
            Excel.Chart chartPage = myChart.Chart;

            chartRange = xlWorkSheet.get_Range((Excel.Range)xlWorkSheet.Cells[1, 1], (Excel.Range)xlWorkSheet.Cells[RowCounter - 1, ColumnCounterForNamesOfTests - 1]);
            chartPage.ChartWizard(Source: chartRange,
                Gallery: Excel.XlChartType.xlColumnClustered,
                Title: (string)CurrentScriptWorker.GetLabName(),
                CategoryTitle: (string)CurrentScriptWorker.GetValueSeparator(),
                ValueTitle: (string)CurrentScriptWorker.GetValueType());
        }

 public void autofitColumn(String columnName, Excel.Worksheet sheet)
 {
     // hackish - there has to be a better way to select the whole column....
     String colName = columnName + "1";
     String colEnd = columnName + "1000";
     try
     {
         // Get the column and tell it to autofit
         Excel.Range columns = sheet.get_Range(colName, colEnd);
         columns.Columns.AutoFit();
     }
     catch (Exception e)
     {
         System.Windows.Forms.MessageBox.Show(e.Message, "Column AutoFit Failed", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
     }
 }

        private void findNow(Excel.Worksheet worksheet, string textToFind)
        {
            Excel.Range range;
            string address;
            string first; // it will store the address of the cell where we find the word for the very first time<br/>
            // This is to ensure that we will not trap in a loop
            // As after finding the whole document, search returns to first cell
            // So if we find that the currently finded cell is same to the firstly founded address, then stop the processing

            range = worksheet.Cells.Find(textToFind, worksheet.get_Range("A1", "A1"),
            Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
            Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
            false, false);
            // This will find the 1st occurance of the word in our Excel file

            if (range != null) // Check whether it has found something in our file or not
            {
                first = range.get_Address(
                    true, true, Excel.XlReferenceStyle.xlA1, null, null); // Save the address of the first found item

                MessageBox.Show("1st match found at cell " + first);

                range = worksheet.Cells.FindNext(range);
                // Continue searching items using FindNext

                address = range.get_Address(
                    true, true, Excel.XlReferenceStyle.xlA1, null, null);
                // get the address of newly searched cell

                while (!address.Equals(first))
                {
                    // this loop will find all the occurances of your word
                    MessageBox.Show("match found at cell " + address);
                    range = worksheet.Cells.FindNext(range);
                    address = range.get_Address(
                      true, true, Excel.XlReferenceStyle.xlA1, null, null);
                }
            }
        }

        protected void WriteTitle(ExcelCOM.Worksheet worksheet)
        {
            BUSHoatDong bushoatdong = new BUSHoatDong();
            HOATDONG hoatdong = bushoatdong.TimKiem(int.Parse(Request.QueryString["id"]));

            ExcelCOM.Range sheetTitle = (ExcelCOM.Range)worksheet.get_Range("A1", "D1");
            sheetTitle.MergeCells = true;
            sheetTitle.Value2 = "Chi Tiết Đăng Ký Hoạt Động " + hoatdong.TenHoatDong;
            sheetTitle.Font.Bold = true;
            sheetTitle.Font.Size = 20;
            sheetTitle.HorizontalAlignment = ExcelCOM.XlHAlign.xlHAlignCenter;
            sheetTitle.Rows.AutoFit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheetTitle);

            for (int i = 0; i < GridViewDangKyHoatDong.Columns.Count; i++)
            {
                WriteIndexColumn(worksheet, 3, i + 1, GridViewDangKyHoatDong.Columns[i].ToString());
            }
        }

        //检查文字考点，参数为考点列表、继续检索的位置、学生工作表、标答工作表
        private int check_Words(List<OfficeElement> ls, int startPosition, Excel.Worksheet stuWs, Excel.Worksheet ansWs)
        {
            int thisPoint = 0;
            int i;
            if (stuWs == null)
                return 0;
            for (i = startPosition; i < ls.Count; i++)
            {
                OfficeElement oe = ls[i];
                if (oe.AttribName == "Name")
                {
                    if (stuWs.Name == ansWs.Name)
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                if (oe.AttribName == "Range")
                {
                    stuRange = stuWs.get_Range(oe.AttribValue, nullobj);
                    ansRange = ansWs.get_Range(oe.AttribValue, nullobj);
                    continue;
                }
                if (oe.AttribName == "Font")
                {
                    continue;
                }
                #region Fonts
                if (oe.AttribName == "FontName")
                {
                    if (stuRange.Font.Name.ToString() == ansRange.Font.Name.ToString())
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                if (oe.AttribName == "Size")
                {
                    if (stuRange.Font.Size.ToString() == ansRange.Font.Size.ToString())
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                if (oe.AttribName == "Bold")
                {
                    if (stuRange.Font.Bold.ToString() == ansRange.Font.Bold.ToString())
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                if (oe.AttribName == "Italic")
                {
                    if (stuRange.Font.Italic.ToString() == ansRange.Font.Italic.ToString())
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                if (oe.AttribName == "Underline")
                {
                    if (stuRange.Font.Underline.ToString() == ansRange.Font.Underline.ToString())
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                if (oe.AttribName == "Color")
                {
                    if (stuRange.Font.Color.ToString() == ansRange.Font.Color.ToString())
                        thisPoint += int.Parse(oe.AttribValue);
                    continue;
                }
                #endregion
                #region Manipulation
                if (oe.AttribName == "SubTotal" || oe.AttribName == "Sort")
                {
                    int row = ansRange.Rows.Count, col = ansRange.Columns.Count;
                    int check = 1;
                    Excel.Range stuCmp, ansCmp;
                    int p, q;

                    if (check == 0) { continue; }

                    for (p = 1; p <= row; p++)
                    {
                        for (q = 1; q <= col; q++)
                        {
                            stuCmp = (Excel.Range)stuRange.Cells[p, q];
                            ansCmp = (Excel.Range)ansRange.Cells[p, q];
                            if (stuCmp.Text.ToString() != ansCmp.Text.ToString())
                            {
                                check = 0;
                                break;
                            }
                        }
                        if (check == 0)
                            break;
                    }
                    if (check == 0)
                        continue;
                    if (oe.AttribName == "SubTotal")                                                //分类汇总还需要判断大纲层次是否一致
                    {
                        for (p = 1; p <= ansRange.Rows.Count; p++)
                        {
                            if (((Excel.Range)ansRange.Rows[p, nullobj]).ShowDetail.ToString() !=
                                ((Excel.Range)stuRange.Rows[p, nullobj]).ShowDetail.ToString())                     //判断大纲层次是否一致
                            {
                                check = 0;
                                break;
//.........这里部分代码省略.........

        private List<int> CompareLists(Excel.Worksheet sheet1, Excel.Worksheet sheet2, string[] sheet1Columns, string[] sheet2Columns, bool sheet1HeaderRow, bool sheet2HeaderRow, bool ignoreCaps, bool ignoreSpecialChars)
        {
            List<int> sheetIndices = new List<int>();

            //Gets the range and columns in the worksheet that are used. Range will be used to loop, and col to keep data intact
            var sheet1NumOfCols = sheet1.UsedRange.Columns.Count;
            var sheet1NumOfRows = sheet1.UsedRange.Rows.Count;

            var sheet2NumOfCols = sheet2.UsedRange.Columns.Count;
            var sheet2NumOfRows = sheet2.UsedRange.Rows.Count;

            // Compare each row in sheet1 to every row in sheet2. If a match is found, save the current row's index
            for (int i = (!sheet1HeaderRow) ? 1 : 2; i <= sheet1NumOfRows; i++)
            {
                StringBuilder sheet1RowString = new StringBuilder();
                bool matchFound = false;

                // Create the comparison string for the current row in sheet 1
                foreach (string column in sheet1Columns)
                {
                    // Add each cells' contents to the string
                    Range cell = sheet1.get_Range(column + i.ToString()); //sheet1.Cells[i, j] as Range;

                    if (cell.Value != null)
                    {
                        string value = Convert.ToString(cell.Value);

                        if (ignoreSpecialChars) // @"\s+"
                            value = Regex.Replace(value, "[^0-9a-zA-Z]", "");

                        sheet1RowString.Append(value + ",");
                    }
                }
                sheet1RowString.Replace(" ", "");

                // Compare the row in the base sheet with every row in the compare sheet
                for (int k = (!sheet2HeaderRow) ? 1 : 2; k <= sheet2NumOfRows; k++)
                {
                    StringBuilder sheet2RowString = new StringBuilder();

                    // Create the comparison string for the row in sheet 2
                    foreach (string column in sheet2Columns)
                    {
                        // Add each cells' contents to the string
                        Range cell = sheet2.get_Range(column + k.ToString());

                        if (cell.Value != null)
                        {
                            string value = Convert.ToString(cell.Value);

                            if (ignoreSpecialChars)
                                value = Regex.Replace(value, "[^0-9a-zA-Z]", "");

                            sheet2RowString.Append(value + ",");
                        }
                    }
                    sheet2RowString.Replace(" ", "");

                    string sheet1Row = sheet1RowString.ToString();
                    string sheet2Row = sheet2RowString.ToString();

                    bool romanize = true;

                    if (ignoreCaps)
                    {
                        sheet1Row = sheet1Row.ToLower();
                        sheet2Row = sheet2Row.ToLower();
                    }

                    if (romanize)
                    {
                        sheet1Row = Unidecoder.Unidecode(sheet1Row);
                        sheet2Row = Unidecoder.Unidecode(sheet2Row);
                    }

                    // Compare the two rows and see if they are the same
                    if (sheet1Row == sheet2Row)
                    {
                        matchFound = true;
                        break;
                    }
                }

                // If a similar entry was not found in the other list, add it to the list
                if (!matchFound)
                {
                    sheetIndices.Add(i);
                }
            }

            return sheetIndices;
        }

 private Excel.Range findRow(Excel.Worksheet portfolioWorkSheet, Excel.Range row, int rowNumber)
 {
     Excel.Range currentFind = null;
     Excel.Range firstFind = null;
     Excel.Range previousColumn1 = portfolioWorkSheet.get_Range("A1:A" + (rowNumber - 1));
     currentFind = previousColumn1.Find(row.Cells[1, 1]);
     while (currentFind != null)
     {
         Excel.Range currentRow = currentFind.EntireRow;
         if (equalRows(currentRow, row))
         {
             return currentRow;
         }
         if (firstFind == null)
         {
             firstFind = currentFind;
         }
         else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1)
               == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
         {
             break;
         }
         currentFind = previousColumn1.FindNext(currentFind);
     }
     return null;
     //for (int i = portfolioWorkSheet.UsedRange.Rows.Count - 1; i > 1; i--)
     //{
     //    Excel.Range currentRow = portfolioWorkSheet.Rows[i];
     //    //Excel.Range currentRow = (portfolioWorkSheet.get_Range(portfolioWorkSheet.Cells[i, 1], portfolioWorkSheet.Cells[i, baseEffectiveColumnNumber]) as Excel.Range);
     //    if (equalRows(currentRow, row))
     //    {
     //        return currentRow;
     //    }
     //}
     //return null;
 }

 /// <summary>
 /// Schreibt einen Wert in die Zelle des gegebenen Excel-Sheets
 /// </summary>
 /// <param name="sheet">Das Excel Sheet.</param>
 /// <param name="zelle">Die Zelle, z. B. A2.</param>
 /// <param name="value">Den Wert der Zelle als String.</param>
 public void WriteValueProtectedCell(Excel.Worksheet sheet, string zelle, string value)
 {
     sheet.Unprotect("1111");
       Excel.Range r = sheet.get_Range(zelle, missing);
       r.Value2 = value;
       sheet.Protect("1111", false, true);
 }

 /// <summary>
 /// Liest einen Wert aus einer Zelle des gegebenen ExcelSheets.
 /// </summary>
 /// <param name="sheet">Das Excel Sheet.</param>
 /// <param name="zelle">Die Zelle, z. B. A2.</param>
 /// <returns>Den Wert der Zelle als String.</returns>
 public string ReadValue(Excel.Worksheet sheet, string zelle)
 {
     Excel.Range r = sheet.get_Range(zelle, missing);
       return r.Value2 == null ? null : Convert.ToString(r.Value2).Trim();
 }

 void InitInstrumentFromRange(string a_rangeInput, Excel.Worksheet a_sheet)
 {
     if (a_rangeInput.Length <= 0)
     {
         return;
     }
     Excel.Range l_instrumentRange = a_sheet.get_Range(a_rangeInput);
     if (l_instrumentRange.Rows.Count == 1 && l_instrumentRange.Columns.Count == 1)
     {
         string l_valueArrTmp = l_instrumentRange.get_Value();
         m_instrumentList.Add(l_valueArrTmp);
     }
     else
     {
         System.Array l_valueArrTmp = (System.Array)l_instrumentRange.get_Value();
         foreach (string l_value in l_valueArrTmp)
         {
             m_instrumentList.Add(l_value);
         }
     }
 }

        //, object cell2, int fontSize, int InteriorColor, bool merge
        //單元格樣式
        private void CellStyle(Excel.Application excel, object cell1, object cell2, string rangeName, bool merge)
        {
            Excel.Range TempRange = excel.get_Range(cell1, cell2);
            TempRange.Merge(merge); //合并单元格
            TempRange.MergeCells = merge;
            //TempRange.WrapText = true;
            //TempRange.Font.Size = 10;//字体大小
            TempRange.WrapText = true;     //文本自动换行
            // TempRange.ShrinkToFit = true;//縮小字體填充
            //TempRange.Font.Name = "楷体";//設置字體 //標楷體
            TempRange.Value2 = rangeName;
            //TempRange.EntireColumn.AutoFit(); //自动列宽
            //TempRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop ].LineStyle = Excel.XlLineStyle.xlDot;
            //TempRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDot;

            //TempRange.Cells.Interior.Color = -4142;
            //TempRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
        }