C# Excel.Range類代碼示例

 public static void openXL(string xlp)
 {
     xla = new Excel.Application();
     xlw = xla.Workbooks.Open(xlp);
     xls = xlw.Worksheets.get_Item(1);
     xlr = xls.UsedRange;
 }

        public void readExcel()
        {
            string valueString = string.Empty;
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
            //Open the workbook containing the address data.
            objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
            //Get a reference to the first sheet of the workbook.
            objSheets = objBook.Worksheets;
            objSheet = (Excel._Worksheet)objSheets.get_Item(1);

            //Select the range of data containing the addresses and get the outer boundaries.
            rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
            long lLastRow = rngLast.Row;
            long lLastCol = rngLast.Column;

            // Iterate through the data and concatenate the values into a comma-delimited string.
            for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
            {
                for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
                {
                    //Write the next value into the string.
                    Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
                    string cellvalue = cell.Value.ToString();
                    //TODO: add your business logic for retrieve cell value
                }
            }
        }

    /// <summary>
    /// Class constructor does all the work
    /// </summary>
    /// <param name="excelApp"></param>
    /// <param name="fileName"></param>
    public StudentWorkbook( Excel.Application excelApp, string fileName )
    {
      try  // to open the student's spreadsheet
      {
        excelWorkbook = excelApp.Workbooks.Open( fileName, 0,
            true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,  // read only
            false, 0, true, false, false );
      }
      catch ( Exception e )
      {
        Console.WriteLine( "error: " + e.Message );
        Console.WriteLine( "Could not open spreadsheet " + fileName );
      }

      Excel.Sheets excelSheets = excelWorkbook.Worksheets;  // get the Worksheets collection
      Excel.Worksheet excelWorksheet = excelSheets[ 1 ];    // get the first one

      // get the Team Number cell
      Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" );

      // try to convert this cell to an integer
      if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 )
      {
        Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" );
      }

      // get the scores cells
      scores = excelWorksheet.get_Range( "B7", "B15" );

      // get the Additional Comments cell
      comments = excelWorksheet.get_Range( "B18", "B18" );

    }  // end of StudentWorkbook()

 public Insert(Excel.Application application, CultureInfo culture)
 {
     try {
         _excelapp = application;
         _culture = culture;
         _range = application.ActiveWindow.RangeSelection;
     } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
 }

 public void addData(int row, int col, string data,
     string cell1, string cell2, string format)
 {
     worksheet.Cells[row, col] = data;
     workSheet_range = worksheet.get_Range(cell1, cell2);
     workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
     workSheet_range.NumberFormat = format;
 }

 public Date(Excel.Application application, CultureInfo culture)
 {
     try {
         _excelapp = application;
         _culture = culture;
         _range = application.ActiveWindow.RangeSelection;
         _minDateTime = new DateTime(1900, 1, 1);
     } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
 }

 public Gen(Excel.Application application, CultureInfo culture)
 {
     try {
         _excelapp = application;
         _culture = culture;
         _range = application.ActiveWindow.RangeSelection;
         _singlesel = _range.Columns.Count == 1 && _range.Rows.Count == 1;
         _fnb = _singlesel ? new FieldNumber("Number of rows", "", 100, 1, true) : null;
     } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
 }

 //Constructor that takes an Excel range as an argument
 //The data is stored in the cells of the range
 public NormalDistribution(Excel.Range r)
 {
     _cells = r;
     _size = r.Count;
     _mean = __mean();
     _variance = __variance();
     _standard_deviation = __standard_deviation();
     _error = __error();
     _ranked_errors = __rank_errors();
 }

        public void generatExcelNoSubmit(string f)
        {
            string file_Name = f;
            m_oExcelApp = new Excel.Application();
            m_oExcelApp.Visible = false;

            m_oExcelApp.UserControl = false;

            m_oSheet = null;
            excelRange = null;

            try
            {
                m_oBook = m_oExcelApp.Workbooks.Add(missing);
                Excel.Sheets sheets = m_oBook.Worksheets;

                //Add new 4 Sheet
                //sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
                //Product Sheet [Sheet1]
                m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
                m_oSheet.Activate();
                m_oSheet.Name = "รายการเสนอซื้อ";
                SetData_to_SheetNoSubmit();

                string template = Application.StartupPath;
                string strRunReport = file_Name;
                //string strPass = "ktc123"; password

                m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);

                m_oExcelApp.Visible = true;
            }
            catch (interop.COMException ex)
            {
                MessageBox.Show("Error accessing Excel: " + ex.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }
            finally
            {
                if (m_oExcelApp == null)
                {
                    m_oExcelApp.Quit();
                    m_oExcelApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                }

            }
        }

 private void Sheet1_Startup(object sender, System.EventArgs e)
 {
     rngUp = this.Range["B2", missing];
     rngDown = this.Range["B3", missing];
     rngInterest = this.Range["B4", missing];
     rngInitial = this.Range["B5", missing];
     rngPeriods = this.Range["B6", missing];
     rngExercise = this.Range["B7", missing];
     rngRuns = this.Range["B8", missing];
     rngRemote = this.Range["B9", missing];
 }

 public ReportBuilder()
 {
     app = new Excel.Application();
     appBooks = app.Workbooks;
     currentBook = appBooks.Add(Missing.Value);
     sheets = currentBook.Worksheets;
     currentSheet = (Excel._Worksheet)sheets.get_Item(1);
     range = currentSheet.get_Range("A1", Missing.Value);
     charts = currentSheet.ChartObjects(Type.Missing);
     chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
 }

        public static void ReplaceExcelRange(Range com, InputSample input)
        {
            bool done = false;
            while (!done)
            {
                try
                {
                    com.Value2 = input.GetInputArray();
                    done = true;
                }
                catch (Exception)
                {

                }
            }
        }

        public void addData(int row, int col, string content, bool bold, bool italic, bool underline, int? size, string fontName)
        {
            //Data
            worksheet.Cells[row, col] = content;

            //Cell format
            workSheet_range = worksheet.get_Range(string.Empty + new string((char)(col + 64), 1) + row.ToString());
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.Font.Bold = bold;
            workSheet_range.Font.Italic = italic;
            workSheet_range.Font.Underline = underline;
            if (size != null)
                workSheet_range.Font.Size = size;
            if(fontName != null)
                workSheet_range.Font.Name = fontName;
        }

 public static Excel1.Range ReadExcel(string filePath, int workSheetNumber)
 {
     try
     {
         string startupPath = System.IO.Directory.GetCurrentDirectory();
         string outPutDirectory = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
         string xmlfilepath = Path.Combine(outPutDirectory + filePath);
         string xmlfile_path = new Uri(xmlfilepath).LocalPath;
         xlApp = new Excel1.Application();
         xlWorkBook = xlApp.Workbooks.Open(xmlfile_path, 0, false, 5, "", "", true, Excel1.XlPlatform.xlWindows, "\t", true, false, 0, true, true, false);
         xlWorkSheet = (Excel1.Worksheet)xlWorkBook.Worksheets.get_Item(workSheetNumber);
         return range = xlWorkSheet.UsedRange;
     }
     catch (Exception)
     {
         return null;
     }
 }

        public void createHeaders(int row, int col, string htext, string cell1,
        string cell2, int mergeColumns, string b, bool font, int size, string
        fcolor)
        {
            worksheet.Cells[row, col] = htext;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            //workSheet_range.Merge(mergeColumns);
            switch (b)
            {
                case "YELLOW":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                    break;
                case "GRAY":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                    break;
                case "GAINSBORO":
                    workSheet_range.Interior.Color =
            System.Drawing.Color.Gainsboro.ToArgb();
                    break;
                case "Turquoise":
                    workSheet_range.Interior.Color =
            System.Drawing.Color.Turquoise.ToArgb();
                    break;
                case "PeachPuff":
                    workSheet_range.Interior.Color =
            System.Drawing.Color.PeachPuff.ToArgb();
                    break;
                default:
                    //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                    break;
            }

            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.Font.Bold = font;
            workSheet_range.ColumnWidth = size;
            if (fcolor.Equals(""))
            {
                workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
            }
            else
            {
                workSheet_range.Font.Color = System.Drawing.Color.Red;
            }
        }