C# Range.Merge方法代码示例

        /// <summary>
        /// Creates a header in the excel sheet
        /// </summary>
        /// <param name="row">the row of the header</param>
        /// <param name="col">the column of the header</param>
        /// <param name="htext">the text to be shown in the header</param>
        /// <param name="cell1">starting cell range</param>
        /// <param name="cell2">ending cell range</param>
        /// <param name="mergeColumns"></param>
        /// <param name="interiorColor"></param>
        /// <param name="isFontBold"></param>
        /// <param name="fontSize"></param>
        /// <param name="fontColor"></param>
        public void CreateHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string interiorColor, bool isFontBold, int fontSize, string fontColor)
        {
            _worksheet.Cells[row, col] = htext;
            _workSheetRange = _worksheet.Range[cell1, cell2];
            _workSheetRange.Merge(mergeColumns);

            switch (interiorColor)
            {
                case "YELLOW":
                    _workSheetRange.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
                    break;
                case "GRAY":
                    _workSheetRange.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                    break;
                case "GAINSBORO":
                    _workSheetRange.Interior.Color =
                        System.Drawing.Color.Gainsboro.ToArgb();
                    break;
                case "Turquoise":
                    _workSheetRange.Interior.Color =
                        System.Drawing.Color.Turquoise.ToArgb();
                    break;
                case "PeachPuff":
                    _workSheetRange.Interior.Color =
                        System.Drawing.Color.PeachPuff.ToArgb();
                    break;
                default:
                    //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                    break;
            }

            _workSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
            _workSheetRange.Font.Bold = isFontBold;
            _workSheetRange.ColumnWidth = fontSize;
            _workSheetRange.Font.Color = fontColor.Equals("")
                ? System.Drawing.Color.White.ToArgb()
                : System.Drawing.Color.Black.ToArgb();
        }

 private void MergeCells(Range range, string label)
 {
     range.Clear();
     range.Merge();
     range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
     range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
     range.Font.Bold = true;
     range.Value = label;
 }

        /// <summary>
        /// Write the column header for a sheet
        /// </summary>
        /// <param name="currentSheet">Current worksheet object</param>
        /// <param name="currentRange">Current range object</param>
        /// <param name="sequenceGroupName">Sequence group Name</param>
        /// <param name="sheetRowIndex">Sheet row index</param>
        /// <param name="sheetColumnIndex">Sheet column index</param>
        private static void WriteSequeceRangeHeader(
            Worksheet currentSheet,
            Range currentRange,
            string sequenceGroupName,
            ref int sheetRowIndex,
            ref int sheetColumnIndex,
            bool showBasePairCount)
        {
            sheetRowIndex++;
            currentRange = currentSheet.get_Range(GetColumnString(sheetColumnIndex) + sheetRowIndex, Type.Missing);
            currentRange = currentRange.get_Resize(1, showBasePairCount ? 2 : 1);
            currentRange.Merge();
            currentRange.Cells.Font.Bold = true;
            currentRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            WriteRangeValue(currentRange, sequenceGroupName);
            sheetRowIndex++;

            if (showBasePairCount)
            {
                currentRange = currentSheet.get_Range(GetColumnString(sheetColumnIndex) + sheetRowIndex, Type.Missing);
                currentRange.Cells.Font.Bold = true;
                currentRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                WriteRangeValue(currentRange, Resources.BASE_PAIR_COUNT);
                sheetColumnIndex++;
            }

            currentRange = currentSheet.get_Range(GetColumnString(sheetColumnIndex) + sheetRowIndex, Type.Missing);
            currentRange.Cells.Font.Bold = true;
            currentRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            WriteRangeValue(currentRange, Resources.NUMBER_OF_RANGES);
            sheetColumnIndex++;

            sheetRowIndex -= 2;
        }

//.........这里部分代码省略.........
            #region 設定變數

            ExcelXmlWorkbook exl_WorkSheet = new ExcelXmlWorkbook();
            Worksheet sheet = exl_WorkSheet[0];
            sheet.Name = "折讓入帳彙總表";

            #region 設定長和寬

            int i_Vertical = 0;//直的有幾個欄位(依據資料庫[利潤中心])
            int i_Horizontal = 0;//橫的有幾個欄位(依據資料庫[型式])

            int i_Vertical_Fix = 3;//直的固定欄位[型式、稅別、合計]
            int i_Horizontal_Fix = 4;//橫的固定欄位[標題抬頭、利潤中心抬頭、合計上方要空一行、合計]

            i_Vertical = ds_Return.Tables["PROFIT"].Rows.Count + i_Vertical_Fix;
            i_Horizontal = (ds_Return.Tables["DISC_TYPE"].Rows.Count * 3) + i_Horizontal_Fix;

            #endregion

            #endregion

            #region 設定格式

            #region 設定欄位寬度

            for (int i_Style = 0; i_Style < i_Vertical; i_Style++)
            { sheet.Columns(i_Style).Width = 100; }

            #endregion

            #region 設定合併儲存格

            Range rang_1 = new Range(sheet[0, 0], sheet[i_Vertical - 1, 0]);
            rang_1.Merge();

            #endregion

            #region 設定Style

            XmlStyle style_1 = new XmlStyle();
            style_1.Alignment.Vertical = VerticalAlignment.Center;
            style_1.Border.LineStyle = Borderline.Continuous;
            style_1.Border.Color = System.Drawing.Color.Black;
            style_1.Border.Sides = BorderSides.All;
            style_1.Border.Weight = 1;
            style_1.Font.Size = 10;
            style_1.Font.Name = "新細明體";

            XmlStyle style_2 = new XmlStyle();
            style_2.Alignment.Vertical = VerticalAlignment.Center;
            style_2.Alignment.Horizontal = HorizontalAlignment.Center;
            style_2.Border.LineStyle = Borderline.Continuous;
            style_2.Border.Color = System.Drawing.Color.Black;
            style_2.Border.Sides = BorderSides.All;
            style_2.Border.Weight = 1;
            style_2.Font.Size = 10;
            style_2.Font.Name = "新細明體";

            for (int x = 0; x < i_Vertical; x++)
            {
                for (int y = 0; y < i_Horizontal; y++)
                {
                    if (y != 0)
                    { sheet[x, y].Style = style_1; }
                }
            }

    private void AddSheetData(ref Worksheet sheet, string strSheetName, DataTable dtTemp)
    {

        //設定Worksheet各column 寬度
        sheet.Font.Size = 12;
        sheet.Font.Name = "新細明體";

        //欄位寬度特別設定的
        //sheet.Columns(0).Width = 74;

        int iRow = 0;

        sheet.Name = strSheetName;
        //sheet.PrintOptions.Orientation = PageOrientation.Landscape;
        sheet.PrintOptions.Orientation = PageOrientation.Portrait;
        sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);

        iRow = 0;

        sheet[0, iRow].Value = "";
        sheet[1, iRow].Value = "圖書";
        sheet[2, iRow].Value = "";
        sheet[3, iRow].Value = "漫畫";
        sheet[4, iRow].Value = "";
        sheet[5, iRow].Value = "加總";
        sheet[6, iRow].Value = "";
        sheet[7, iRow].Value = "";
        sheet[8, iRow].Value = "";

        Range rng = new Range(sheet[1, iRow], sheet[2, iRow]);
        rng.Merge();

        rng = new Range(sheet[3, iRow], sheet[4, iRow]);
        rng.Merge();

        rng = new Range(sheet[5, iRow], sheet[8, iRow]);
        rng.Merge();

        sheet[0, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[1, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[2, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[3, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[4, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[5, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[6, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[7, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[8, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;

        sheet[0, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[1, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[2, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[3, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[4, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[5, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[6, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[7, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[8, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;

        iRow++;

        sheet[0, iRow].Value = "路線";
        sheet[1, iRow].Value = "紙箱數";
        sheet[2, iRow].Value = "氣泡袋數";
        sheet[3, iRow].Value = "紙箱數";
        sheet[4, iRow].Value = "氣泡袋數";
        sheet[5, iRow].Value = "紙箱數";
        sheet[6, iRow].Value = "氣泡袋數";
        sheet[7, iRow].Value = "總箱數";
        sheet[8, iRow].Value = "物流箱數";

        sheet[0, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[1, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[2, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[3, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[4, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[5, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[6, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[7, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        sheet[8, iRow].Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
        
        sheet[0, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[1, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[2, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[3, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[4, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[5, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[6, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[7, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;
        sheet[8, iRow].Border.Sides = Yogesh.ExcelXml.BorderSides.All;

        iRow++;

        for (int i = 0; i < dtTemp.Rows.Count; i++)
        {
            sheet[0, iRow].Value = dtTemp.Rows[i]["ROUTE"].ToString();
            sheet[1, iRow].Value = dtTemp.Rows[i]["QTY_00"].ToString();
            sheet[2, iRow].Value = dtTemp.Rows[i]["QTY_01"].ToString();
            sheet[3, iRow].Value = dtTemp.Rows[i]["QTY_10"].ToString();
            sheet[4, iRow].Value = dtTemp.Rows[i]["QTY_11"].ToString();
            sheet[5, iRow].Value = dtTemp.Rows[i]["SUM_QTY_0"].ToString();
//.........这里部分代码省略.........