C# Worksheet.get_Range方法代码示例

        public static void CreateResultsWorksheet(DataSet Schedule)
        {
            // Frequenty-used variable for optional arguments.
            object m_objOpt = System.Reflection.Missing.Value;

            Application xlApp = new Application();
            Worksheet ws = new Worksheet();
            int outRows, outCols;

            //Rows and columns are transposed for printing
            outCols = Schedule.Tables[0].Columns.Count;
            outRows = Schedule.Tables[0].Rows.Count + 1; //leave roof for row and column headers

            object[,] OutputArray = new object[outRows, outCols];
            ////Note Excel Arrays are 1-based, not 0-based
            for (int i = 0; i < outCols; i++)
            {
                //Put the titles into the spreadsheet.
                OutputArray[0, i] = Schedule.Tables[0].Columns[i].ColumnName;
            }

            for (int i = 1; i < outRows; i++)
            {
                for (int j = 0;j < outCols; j++)
                {
                    DataRow dr = Schedule.Tables[0].Rows[i - 1];
                    OutputArray[i, j] = dr[j];
                }
            }

            //Create a workbook and add a worksheet named "Schedule Results"
            xlApp.Workbooks.Add(m_objOpt);
            ws = (Worksheet) xlApp.Workbooks[1].Worksheets[1];
            ws.Name = "Schedule Results";

            Range r = ws.get_Range(ws.Cells[1,1],ws.Cells[outRows, outCols]);
            //put the output array into Excel in one step.
            r.Value2=OutputArray;

            //select the title columns and make them bold
            r = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, outCols + 1]);
            r.Font.Bold = true;
            r.Font.Size = 14;

            //format any DateTime Columns
            for (int i = 0; i<outCols; i++)
            {
                if (Schedule.Tables[0].Columns[i].DataType.ToString() == "System.DateTime" )
                {
                    r = ws.get_Range(ws.Cells[1, i+1], ws.Cells[outRows + 1, i+1]);
                    r.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@";
                }
            }

            //Select the entire spreadsheet and autofit the contents
            r = ws.get_Range(ws.Cells[1, 1], ws.Cells[outRows + 1, outCols + 1]);
            r.Columns.AutoFit();
            xlApp.Visible = true;
            xlApp = null;
        }

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

    //function to create charts, can be called for multiple charts
    static void chart(Worksheet thisWorkSheet,string x, string y, string name,string holder)
    {
        ChartObjects newCharts = (ChartObjects)thisWorkSheet.ChartObjects(Type.Missing);//creates a chart object
        ChartObject myChart = (ChartObject)newCharts.Add(10, 80, 1350, 550);//position of chart(pos X, pos y, width, height)
        Chart chartPage = myChart.Chart;
        Range chartRange = thisWorkSheet.get_Range(x,y);
        chartPage.Rotation = 0;
        chartPage.ChartType = XlChartType.xl3DColumnClustered;
        Axis xAxis = (Axis)chartPage.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
        xAxis.TickLabelPosition =XlTickLabelPosition.xlTickLabelPositionNone;
        int startCol = 3;
        int startRow = 1;
        int maxRow = 2;
        int maxCol = 14; //With this value the loop below will iterate until column 9 (inclusive)
        for (int i = startCol; i <= maxCol; i++)
        {
            Range holder1 = thisWorkSheet.Cells[1, i];
            Range holder2 = thisWorkSheet.Cells[2, i];
            Range holder3 = thisWorkSheet.get_Range(holder1, holder2);
            bool check = false;
            for (int j = startRow; j <= maxRow; j++) {

                Range currentRange = (Range)thisWorkSheet.Cells[j, i];//iterates through the data in the excel document
                if (currentRange.Value2 != null)
                {
                    check = true;
                    string curVal = currentRange.Value2.ToString();
                    //Console.WriteLine("\n :loading value "+i+" part "+j);
                }
            }
            //simple check statement to add more colums.
            if (check)
            {
                //Series newSeries;
                SeriesCollection seriesCollect = chartPage.SeriesCollection();
                //chartPage.Axes().text += holder1;
                Series seriesI= seriesCollect.NewSeries();
                seriesI.XValues = holder1;
                seriesI.Values = holder2;
                seriesI.HasDataLabels = true;
                seriesI.HasLeaderLines = true;
                //Console.WriteLine (xAxis.TickLabels.Name);
                seriesI.Name = holder1.Value;

            }
        }
        //chartPage.SetSourceData(chartRange, Type.Missing);

        chartPage.HasTitle = true;//makes the chart have a title
        chartPage.ChartTitle.Text = "Sales Data, Week of "+thisWorkSheet.get_Range("a2","a2").Value;//title of the chart
        chartPage.ChartTitle.Font.Size = 45;//sets the font size of the title
        chartPage.ChartTitle.Font.Color = XlRgbColor.rgbGoldenrod;//color of title
        chartPage.ChartStyle = 42;//style of chart

        //EVERYTHING THAT HAS AN EFFECT ON THE CHART SHOULD HAPPEN BEFORE THIS!
        chartPage.Export(loc + name +holder+ ".jpg", "JPG", false);//takes the newly generated chart and saves it as a jpg
        //THIS IS BASICALLY THE RETURN STATEMENT  /\  DO NOT DELETE!
        //      (not really but it save the immage)
    }

        public static void SaveExcelFile(string fileName, string tempFileName,string[,] contents, int startColumn, int startRow)
        {
            try
            {
                excelApp = new ApplicationClass();
                Workbooks myWorkBooks = excelApp.Workbooks;
                myWorkBooks.Open(tempFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                Sheets sheets = excelApp.Sheets;
                mySheet1 = (Worksheet)sheets[1];
                mySheet1.Activate();

                //写入测试信息
                Range range1 = mySheet1.get_Range(mySheet1.Cells[startRow, startColumn], mySheet1.Cells[contents.GetLength(0)+startRow-1,contents.GetLength(1)+startColumn-1]);
                range1.Value2 = contents;
                mySheet1.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                myWorkBooks.Close();
                excelApp.Quit();
                excelApp = null;
            }
            catch (Exception ee)
            {

                throw ee;
            }
        }

 public void setBorders(Worksheet sheet,string col)
 {
     Range range = sheet.get_Range(col, col);
     //range.ColumnWidth = 1000;
     range.Font.Size = 14;
     range.Font.Bold = true;
     Borders borders = range.Borders;
     borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
     borders.Weight = 1d;
 }

 private void generateHeader(Worksheet s)
 {
     s.get_Range("A1").Value = "ID";
     s.get_Range("B1").Value = "Correct_Output";
     s.get_Range("C1").Value = "Fam1_Shifts";
     s.get_Range("D1").Value = "Fam2_Shifts";
     s.get_Range("E1").Value = "Fam3_Shifts";
     s.get_Range("F1").Value = "Fam4_Shifts";
     s.get_Range("G1").Value = "Fam5_Shifts";
 }

        public List<string[]> ConvertExcelToListLuxuryAuto(Worksheet workSheet)
        {
            List<string[]> rows = new List<string[]>();

            for (int i = 4; i <= 99; i++)
            {
                Range range = workSheet.get_Range("A" + i.ToString(), "N" + i.ToString());
                System.Array myvalues = (System.Array)range.Cells.Value;
                string[] strArray = ConvertToStringArray(myvalues);
                rows.Add(strArray);
            }
            return rows;
        }

        public List<string[]> ConvertExcelToListITC(Worksheet workSheet)
        {
            List<string[]> rows = new List<string[]>();

            for (int i = 2; i <= 23; i++)
            {
                //Fixed
                Range range = workSheet.get_Range("B" + i.ToString(), "AD" + i.ToString());
                System.Array myvalues = (System.Array)range.Cells.Value;
                string[] strArray = ConvertToStringArray(myvalues);
                rows.Add(strArray);
            }
            return rows;
        }

        private void InitializeButtons(Worksheet sheet)
        {
            var range = sheet.get_Range("G2:G3", Type.Missing);

            // Fake Button
            CreateButton(sheet, "Column1 + 1", range, (o1, e1) =>
            {
                sheet.Rows.ClearFormats();
                var totalRows = sheet.UsedRange.Rows.Count;

                for (int i = 2; i <= totalRows; i++)
                {
                    sheet.Cells[i, 1] = sheet.Cells[i, 1].Value + "1";
                }

            });

            // Add book button
            CreateButton(sheet, "Add Book", sheet.get_Range("G5:G6"), (o1, e1) =>
            {
                sheet.Rows.ClearFormats();
                var totalRows = sheet.UsedRange.Rows.Count;

                using (var form = new Search())
                {
                    form.ShowDialog();
                    if (form.HasResult)
                    {
                        sheet.Cells[totalRows + 1, titleColumn] = form.ResultTitle;
                        sheet.Cells[totalRows + 1, authorColumn] = form.ResultAuthor;
                        sheet.Cells[totalRows + 1, ratingColumn] = form.ResultRating.ToString();
                        sheet.Columns.AutoFit();
                    }
                }
                //var httpRequest = (HttpWebRequest)WebRequest.Create(
            });
        }

    /// <summary>
    /// Set Export Data
    /// </summary>
    /// <param name="cells">Cells</param>
    /// <param name="gv">GridView</param>
    /// <param name="xfTitle">Title Style</param>
    /// <param name="xfHeader">Header Style</param>
    /// <param name="xfCell">Cell Style</param>
    /// <param name="rowIndex">Row Index</param>
    /// <param name="mergeNum">Merge Number</param>
    /// <param name="nullCellFlag">Empty Cell Flag</param>
    private void setExpData(Worksheet sheet, GridView gv, ref int rowIndex, int mergeNum, int nullCellFlag)
    {
        int colIndex = 1;
        int indexCStart = colIndex;
        int indexRStart = rowIndex;
        Range range = null;

        #region Set Title
        if (!string.IsNullOrEmpty(gv.Caption))
        {
            sheet.Cells[rowIndex, colIndex] = gv.Caption;
            range = sheet.get_Range(sheet.Cells[rowIndex, colIndex], sheet.Cells[rowIndex, colIndex]);
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Bold = true;
            rowIndex++;
        }
        #endregion

        #region Set Header
        int allColIndex = 0;
        for (int i = 0; i < gv.Columns.Count; i++)
        {
            if (gv.Columns[i].Visible)
            {
                sheet.Cells[rowIndex, colIndex] = gv.Columns[i].HeaderText;
                if (mergeNum != 0 && colIndex > 5)
                {
                    if (nullCellFlag == 1)
                    {
                        colIndex++;
                        sheet.Cells[rowIndex, colIndex] = null;
                    }
                    else if (nullCellFlag == 2 && allColIndex < gv.Columns.Count - 1)
                    {
                        colIndex++;
                        sheet.Cells[rowIndex, colIndex] = null;
                    }
                }
                if (mergeNum == 0)
                {
                    colIndex++;
                }
                else
                {
                    if (colIndex == indexCStart)
                    {
                        range = sheet.get_Range(sheet.Cells[rowIndex, indexCStart], sheet.Cells[rowIndex, indexCStart + mergeNum]);
                        range.Merge(0);
                        colIndex += mergeNum + 1;
                    }
                    else
                    {
                        colIndex++;
                    }
                }
            }
            allColIndex++;
        }
        rowIndex++;

        if (gv.Columns.Count != 0)
        {
            if (mergeNum == 0)
            {
                range = sheet.get_Range(sheet.Cells[indexRStart + 1, indexCStart], sheet.Cells[indexRStart + 1, colIndex - 1]);
            }
            else
            {
                range = sheet.get_Range(sheet.Cells[indexRStart, indexCStart], sheet.Cells[indexRStart, colIndex - 1]);
            }
            range.Interior.ColorIndex = 41;
            range.Font.Bold = true;
            range.Font.ColorIndex = 2;
        }
        #endregion

        #region Merge Title
        if (!string.IsNullOrEmpty(gv.Caption))
        {
            range = sheet.get_Range(sheet.Cells[indexRStart, indexCStart], sheet.Cells[indexRStart, colIndex - 1]);
            range.Merge(0);
        }
        #endregion

        #region Set Data
        string text = null;
        string aStart = "<a href='#'>";
        string aEnd = "</a>";
//.........这里部分代码省略.........

        TryGetNonEmptyRange
        (
            Worksheet worksheet,
            out Range usedRange
        )
        {
            Debug.Assert(worksheet != null);

            usedRange = null;

            Range oEntireWorksheet = (Range)worksheet.get_Range(

                (Range)worksheet.Cells[1, 1],

                (Range)worksheet.Cells[
                    worksheet.Rows.Count, worksheet.Columns.Count]
                );

            return (TryGetNonEmptyRange(oEntireWorksheet, out usedRange));
        }

 private void Exportweek(int year, int month, Worksheet sheet)
 {
     DateTime dtBegin = new DateTime(year, month, 1);
     for (int i = 0; i < 31; i++)
     {
         DateTime dt = dtBegin.AddDays(i);
         string week = string.Empty;
         if (dt.DayOfWeek == DayOfWeek.Sunday) week = "日";
         else if (dt.DayOfWeek == DayOfWeek.Monday) week = "一";
         else if (dt.DayOfWeek == DayOfWeek.Tuesday) week = "二";
         else if (dt.DayOfWeek == DayOfWeek.Wednesday) week = "三";
         else if (dt.DayOfWeek == DayOfWeek.Thursday) week = "四";
         else if (dt.DayOfWeek == DayOfWeek.Friday) week = "五";
         else if (dt.DayOfWeek == DayOfWeek.Saturday) week = "六";
         Range r = sheet.get_Range(GetColName(10 + i) + 2, Type.Missing);
         r.Value = week;
         if (week == "日")
         {
             //sheet.get_Range (GetColName(10 + i)+65535)
         }
     }
 }

 private void AddStaff(Staff staff, List<AttendanceResult> items, List<Department> depts, Worksheet sheet, int row)
 {
     int myRow = 3 + (row - 1) * 3; //从第三行开始输入数据,每个人占三行
     Range r = sheet.get_Range(GetColName(1) + myRow, Type.Missing); //序号
     r.Value = row;
     Department dept = depts.SingleOrDefault(it => it.ID == staff.DepartmentID);
     Department root = null;
     if (dept != null) root = GetRoot(depts, dept);
     r = sheet.get_Range(GetColName(2) + myRow, Type.Missing); //中心
     r.Value = root != null ? root.Name : string.Empty;
     r = sheet.get_Range(GetColName(3) + myRow, Type.Missing); //部门
     r.Value = dept != null ? dept.Name : string.Empty;
     r = sheet.get_Range(GetColName(5) + myRow, Type.Missing);  //职位
     r.Value = staff.UserPosition;
     r = sheet.get_Range(GetColName(6) + myRow, Type.Missing);  //姓名
     r.Value = staff.Name;
     r = sheet.get_Range(GetColName(9) + myRow, Type.Missing);  //
     r.Value = "上午";
     r = sheet.get_Range(GetColName(9) + (myRow + 1), Type.Missing);  //
     r.Value = "下午";
     r = sheet.get_Range(GetColName(9) + (myRow + 2), Type.Missing);  //
     r.Value = "加班";
     for (int i = 1; i <= 8; i++)
     {
         sheet.get_Range(GetColName(i) + myRow, GetColName(i) + (myRow + 2)).Merge(Type.Missing);
     }
     for (int i = 1; i <= 31; i++)
     {
         List<AttendanceResult> rets = (from item in items where item.ShiftDate.Day == i && !string.IsNullOrEmpty(item.ShiftID) orderby item.StartTime ascending select item).ToList();
         if (rets != null && rets.Count > 0)
         {
             if (rets.Count >= 1)
             {
                 r = sheet.get_Range(GetColName(9 + i) + (myRow + 0), Type.Missing);
                 r.Value = (rets[0].Present / 60).Trim();
             }
             if (rets.Count >= 2)
             {
                 r = sheet.get_Range(GetColName(9 + i) + (myRow + 1), Type.Missing);
                 r.Value = (rets[1].Present / 60).Trim(); ;
             }
         }
         decimal ot = (from item in items where item.ShiftDate.Day == i && string.IsNullOrEmpty(item.ShiftID) && !string.IsNullOrEmpty(item.Category) select item).Sum(item => item.Present);
         if (ot > 0)
         {
             r = sheet.get_Range(GetColName(9 + i) + (myRow + 2), Type.Missing);
             r.Value = (ot / 60).Trim();
         }
     }
     for (int i = 41; i <= 49; i++)
     {
         sheet.get_Range(GetColName(i) + myRow, GetColName(i) + (myRow + 2)).Merge(Type.Missing);
     }
     sheet.get_Range(GetColName(41) + myRow).Value = string.Format("=SUM(J{0}:AN{1})", myRow + 2, myRow + 2);
     sheet.get_Range(GetColName(42) + myRow).Value = string.Format("=COUNTIF(J{0}:AN{1},\"△\")*4", myRow, myRow + 1);
     sheet.get_Range(GetColName(45) + myRow).Value = string.Format("=COUNTIF(J{0}:AN{1},\"休\")*4", myRow, myRow + 1);
     sheet.get_Range(GetColName(46) + myRow).Value = string.Format("=SUM(J{0}:AN{1})", myRow, myRow + 1);
     sheet.get_Range(GetColName(48) + myRow).Value = string.Format("=AS{0}+AT{1}", myRow, myRow); //总小时数
 }

 //
 //**********************************************************************************************
 //
 public void ReadPlotDescription(List<TPlot> aPlot)
 {
     try
       {
     MyWorkSheet = (Worksheet)MyWorkBook.Worksheets.get_Item("Control");
     Range MyRange = MyWorkSheet.get_Range("$C$5:$C$8", Type.Missing);
     System.Array MyValues = (System.Array)MyRange.Cells.get_Value(XlRangeValueDataType.xlRangeValueDefault);
     for (Int32 i = 1; i <= aPlot.Count; i++)
     {
       TPlot MyPlot = aPlot[i - 1];
       MyPlot.Description = MyValues.GetValue(i, 1).ToString();
     }
       }
       catch (Exception e)
       {
     MessageBox.Show("??? Error reading plot description: " + e.Message);
       }
 }

    /// <summary>
    /// Set Export Data
    /// </summary>
    /// <param name="cells">Cells</param>
    /// <param name="gv">GridView</param>
    /// <param name="xfTitle">Title Style</param>
    /// <param name="xfHeader">Header Style</param>
    /// <param name="xfCell">Cell Style</param>
    /// <param name="rowIndex">Row Index</param>
    /// <param name="colIndex">Column Index</param>
    /// <param name="mergeNum">Merge Number</param>
    /// <param name="hasTitleFlag">Has Title Or Not</param>
    private void setExpData(Worksheet sheet, GridView gv, ref int rowIndex, ref int colIndex, int mergeNum, bool hasTitleFlag)
    {
        int indexCStart = colIndex;
        int indexRStart = rowIndex;
        Range range = null;

        #region Set Title
        if (!string.IsNullOrEmpty(gv.Caption))
        {
            sheet.Cells[rowIndex, colIndex] = gv.Caption;
            range = sheet.get_Range(sheet.Cells[rowIndex, colIndex], sheet.Cells[rowIndex, colIndex]);
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Bold = true;
            rowIndex++;
        }
        #endregion

        #region Set Header
        for (int i = 0; i < gv.Columns.Count; i++)
        {
            if (gv.Columns[i].Visible)
            {
                sheet.Cells[rowIndex, colIndex] = gv.Columns[i].HeaderText;
                if (mergeNum == 0)
                {
                    colIndex++;
                }
                else
                {
                    if (colIndex == indexCStart)
                    {
                        range = sheet.get_Range(sheet.Cells[rowIndex, indexCStart], sheet.Cells[rowIndex, indexCStart + mergeNum]);
                        range.Merge(0);
                        colIndex += mergeNum + 1;
                    }
                    else
                    {
                        colIndex++;
                    }
                }
            }
        }
        rowIndex++;

        if (gv.Columns.Count != 0)
        {
            if (hasTitleFlag)
            {
                range = sheet.get_Range(sheet.Cells[indexRStart + 1, indexCStart], sheet.Cells[indexRStart + 1, colIndex - 1]);
            }
            else
            {
                range = sheet.get_Range(sheet.Cells[indexRStart, indexCStart], sheet.Cells[indexRStart, colIndex - 1]);
            }
            range.Interior.ColorIndex = 41;
            range.Font.Bold = true;
            range.Font.ColorIndex = 2;
        }
        #endregion

        #region Merge Title
        if (!string.IsNullOrEmpty(gv.Caption))
        {
            range = sheet.get_Range(sheet.Cells[indexRStart, indexCStart], sheet.Cells[indexRStart, colIndex - 1]);
            range.Merge(0);
        }
        #endregion

        #region Set Data
        string text = null;
        for (int i = 0; i < gv.Rows.Count; i++)
        {
            colIndex = indexCStart;
            for (int j = 0; j < gv.Columns.Count; j++)
            {
                if (gv.Columns[j].Visible)
                {
                    text = HttpUtility.HtmlDecode(gv.Rows[i].Cells[j].Text);
                    if (ExcelHandler.IsFloat(text))
                    {
                        sheet.Cells[rowIndex, colIndex] = Convert.ToDecimal(text);
                    }
                    else
                    {
                        sheet.Cells[rowIndex, colIndex] = text;
                    }

//.........这里部分代码省略.........