学步园

最近在做一个关于c#打印excel报表的程序，个人总结的经验：

以下步骤，全部是整体=>局部=>单个

1.画布局，设置边框

2.设置字体，对齐方式

3.数据出力

这样的顺序能提升报表的打印速度，比单个cell赋值快很多。

       /// <summary>
        /// Worksheetを取得
        /// </summary>
        /// <returns>返却するWorksheet</returns>
        private Excel.Worksheet GetExcelBook()
        {
            //excel対象
            oXls = new Excel.Application();

            //テンプレートを開く
            oXls.Visible = true;

            //workbook対象
            Excel.Workbooks oBooks = oXls.Workbooks;
            Excel.Workbook xBook = oXls.Workbooks.Add(Type.Missing);

            //セートするworksheetの数量
            oXls.SheetsInNewWorkbook = 1;
            //罫線を表示なし
            oXls.ActiveWindow.DisplayGridlines = false;

            //worksheet対象
            Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];

            //改ページの線を表示する
            //xSheet.DisplayAutomaticPageBreaks = true;

            //worksheetの名前
            xSheet.Name = "技術系_勤務予定・実績表";

            //その他のworksheetを削除する
            //int maxSheet = xBook.Worksheets.Count;
            //for (int i = maxSheet; i >=2; i--)
            //{
            //    ((Excel.Worksheet)xBook.Sheets[i]).Delete();
            //}

            return xSheet;
        }

       /// <summary>
        /// セートするセールの内容
        /// </summary>
        /// <param name="xSheet">worksheet対象</param>
        /// <param name="rangeFrom">開始するセール</param>
        /// <param name="rangeTo">終了するセール</param>
        /// <param name="list">パラメータ</param>
        private void SetCellView(Excel.Worksheet xSheet, string rangeFrom, string rangeTo, params string[] list)
        {
            //listの内容
            //list[0]:セールの値
            //list[1]:表示形式
            //list[2]:公式
            //list[3]:字体
            //list[4]:字体のセズ
            //list[5]:水平居中
            //list[6]:垂直居中
            //list[7]:文字列を立てます

            Excel.Range valueRange = xSheet.get_Range(rangeFrom, Type.Missing);

            Excel.Range range = xSheet.get_Range(rangeFrom, rangeTo);
            range.Merge(false);

            if (list.Length >= 9)
            {
                return;
            }

            // セールの値
            if (list.Length > 0)
            {
                if (list[0] != string.Empty)
                {
                    valueRange.Value2 = list[0];
                }
            }

            // 表示形式を設置
            if (list.Length > 1)
            {
                if (list[1] != string.Empty)
                {
                    range.NumberFormatLocal = list[1];
                }
            }

            // 公式を設置
            if (list.Length > 2)
            {
                if (list[2] != string.Empty)
                {
                    range.Formula = list[2];
                }
            }

            //字体を設置
            if (list.Length > 3)
            {
                if (list[3] != string.Empty)
                {
                    range.Font.Name = list[3];
                }
            }

            // 字体のセズを設置
            if (list.Length > 4)
            {
                if (list[4] != string.Empty)
                {
                    range.Font.Size = int.Parse(list[4]);
                }
            }
            // 水平居中
            if (list.Length > 5)
            {
                if (list[5] != string.Empty)
                {
                    range.HorizontalAlignment = int.Parse(list[5]);
                }
            }

            // 垂直居中
            if (list.Length > 6)
            {
                if (list[6] != string.Empty)
                {
                    range.VerticalAlignment = int.Parse(list[6]);
                }
            }

            // 文字列を立てます
            if (list.Length > 7)
            {
                range.Orientation = int.Parse(list[7]);
            }
        }

  /// <summary>
        /// セートするセールの枠
        /// </summary>
        /// <param name="xSheet">worksheet対象</param>
        /// <param name="rangeFrom">開始するセール</param>
        /// <param name="rangeTo">終了するセール</param>
        /// <param name="list">パラメータ</param>
        private void SetLayout(Excel.Worksheet xSheet, string rangeFrom, string rangeTo, params int[] list)
        {
            //listの内容
            //list[0]:頂線の幅
            //list[1]:左線の幅
            //list[2]:右線の幅
            //list[3]:底線の幅
            //list[4]:横糸の幅
            //list[5]:縦糸の幅

            Excel.Range range = xSheet.get_Range(rangeFrom, rangeTo);

            if (list.Length >= 7)
            {
                return;
            }

            // 頂線の幅
            if (list.Length > 0)
            {
                if (list[0] != 0)
                {
                    range.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = list[0];
                }
            }

            // 左線の幅
            if (list.Length > 1)
            {
                if (list[1] != 0)
                {
                    range.Cells.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = list[1];
                }
            }

            // 底線の幅
            if (list.Length > 2)
            {
                if (list[2] != 0)
                {
                    range.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = list[2];
                }
            }

            // 右線の幅
            if (list.Length > 3)
            {
                if (list[3] != 0)
                {
                    range.Cells.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = list[3];
                }
            }
            // 横糸の幅
            if (list.Length > 4)
            {
                if (list[4] != 0)
                {
                    range.Cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = list[4];
                }
            }

            // 縦糸の幅
            if (list.Length > 5)
            {
                if (list[5] != 0)
                {
                    range.Cells.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = list[5];
                }
            }

        }

        /// <summary>
        /// セートする幅広い
        /// </summary>
        /// <param name="xSheet">worksheet対象</param>
        private void setColumnWidth(Excel.Worksheet xSheet)
        {
            xSheet.Cells.RowHeight = 18.00;
            ((Excel.Range)xSheet.Columns["A", Type.Missing]).ColumnWidth = 3.63;
            ((Excel.Range)xSheet.Columns["B", Type.Missing]).ColumnWidth = 2.38;
            ((Excel.Range)xSheet.Columns["C", Type.Missing]).ColumnWidth = 2.38;
            for (int i = 24; i < 51; i++)
            {
                ((Excel.Range)xSheet.Columns[i, Type.Missing]).ColumnWidth = 4.00;
            }
        }

        /// <summary>
        /// 画面を設置
        /// </summary>
        /// <param name="xSheet">worksheet対象</param>
        private void setPageSetup(Excel.Worksheet xSheet)
        {
            //上1.5
            xSheet.PageSetup.TopMargin = oXls.InchesToPoints(0.590551181102362);
            //下0
            xSheet.PageSetup.BottomMargin = oXls.InchesToPoints(0.0);
            //左0.5
            xSheet.PageSetup.LeftMargin = oXls.InchesToPoints(0.196850393700787);
            //右0.5
            xSheet.PageSetup.RightMargin = oXls.InchesToPoints(0.196850393700787);
            //ヘッダー1.5
            xSheet.PageSetup.HeaderMargin = oXls.InchesToPoints(0.590551181102362);
            //フッター0.5
            xSheet.PageSetup.FooterMargin = oXls.InchesToPoints(0.196850393700787);
            //レベル居中
            //xSheet.PageSetup.CenterHorizontally = true;
            //レベル垂直
            //xSheet.PageSetup.CenterVertically = true;
            //ページのセイズ
            xSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//.xlPaperB4;
            //紙の方向-横の方向
            xSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
            //拡大/縮小(拡大縮小比率)
            xSheet.PageSetup.Zoom = 85;
            //列のタイトル
            xSheet.PageSetup.PrintTitleColumns = "$A:$C";
            //行のタイトル
            xSheet.PageSetup.PrintTitleRows = "$1:$4";
        }