none
C#でビボット表示 Excel.Range.GroupとExcel.NamedRange.Group RRS feed

  • 質問

  • C#でビボットでグループ表示は Excel.Range.Groupでは以下のプログラムでできますがNamedRange の変数は設定できますが                                     Controls.AddNamedRange                                                      Excel.NamedRange.Group                                                                         が設定できません。必須コンポーネントにある Microsoft Visual Studio 2010 Tools for Office runtime が以下の環境でセットアップされてない可能性もあります。いずれの理由か教えてください。

    使用環境                                                Windows Visual Home Premium                                                             Visual Studio 2010 professional 乗換                               Office Personal 2007

     

    以下実行可能プログラム

    using System;
    using System.Windows.Forms;
    //プロジェクトから参照追加(COM)(Microsoft Excel 12.0 Object Library)
    using Excel = Microsoft.Office.Interop.Excel;
    //プロジェクトから参照追加(.NET)(Microsoft.Office.Tools.Excel)
    using Microsoft.Office.Tools.Excel;
    namespace test
    {
        public partial class Form1 : Form
        {
            private Excel.Application
                excel_App;
            private Excel.Workbooks
                excel_Books;
            private Excel.Workbook
                excel_Book;
            private Excel.Worksheet
                excel_Sheet;
            private Excel.Range
                excel_Range,
                excel_RangeOut,
                excel_RangeOutgrup;
            private Excel.PivotTable
                excel_PivotTable;
            private Excel.PivotField
                excel_PivotFieldRow,
                excel_PivotFieldColum;
            String
                s顧客 = "顧客",
                s日付 = "日付",
                s金額 = "金額";


            public Form1()
            {
                InitializeComponent();
                //初期実行イベント
                Load += new EventHandler(Form1_Load);
            }


            //初期実行イベント
            void Form1_Load(object sender, EventArgs e)
            {
                //エクセル新規作成
                excel_App = new Excel.Application();
                excel_Books = excel_App.Workbooks;
                //ワークブック新規作成
                excel_Book = excel_Books.Add(string.Empty);
                //ワークブックの最初のシート
                excel_Sheet = (Excel.Worksheet)excel_Book.Sheets[1];

                //エクセル表示
                excel_App.Visible = true;
                //確認メッセージ非表示
                //excel_App.DisplayAlerts = false;


                //てすと用データ
                //A列に顧客
                excel_Sheet.Cells[1, 1] = s顧客;
                excel_Sheet.Cells[2, 1] = "田中";
                excel_Sheet.Cells[3, 1] = "鈴木";
                excel_Sheet.Cells[4, 1] = "佐藤";
                excel_Sheet.Cells[5, 1] = "中田";

                //B列に日付
                excel_Sheet.Cells[1, 2] = s日付;
                excel_Sheet.Cells[2, 2] = new DateTime(2010, 1, 5);
                excel_Sheet.Cells[3, 2] = new DateTime(2010, 2, 6);
                excel_Sheet.Cells[4, 2] = new DateTime(2010, 4, 7);
                excel_Sheet.Cells[5, 2] = new DateTime(2010, 7, 15);

                //C列に金額
                excel_Sheet.Cells[1, 3] = s金額;
                excel_Sheet.Cells[2, 3] = 200;
                excel_Sheet.Cells[3, 3] = 300;
                excel_Sheet.Cells[4, 3] = 400;
                excel_Sheet.Cells[5, 3] = 500;
                //てすと用データ終了


                //2秒表示
                System.Threading.Thread.Sleep(2000);


                //データ範囲設定 excel_Sheet.get_Range("A1", "C5")
                excel_Range = excel_Sheet.UsedRange;
                //ビボット表示位置
                excel_RangeOut = excel_Sheet.get_Range("A12", Type.Missing);
                //ビボットグループ設定位置(日付)
                excel_RangeOutgrup = excel_Sheet.get_Range("B12", Type.Missing);

                //ビボット新規作成
                excel_PivotTable = excel_Sheet.PivotTableWizard(
                    Excel.XlPivotTableSourceType.xlDatabase,
                    excel_Range,   //データ範囲
                    excel_RangeOut,//配置場所
                    "ビボット",    //新しいレポートの名前
                    true,          //横行の顧客の合計表示
                    false,         //縦列の期間の合計表示
                    true,
                    true,          //書式自動設定
                    Type.Missing,
                    Type.Missing,
                    false,
                    false,
                    Excel.XlOrder.xlDownThenOver,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing
                    );
                //ビボットA列に顧客
                excel_PivotFieldRow = (Excel.PivotField)excel_PivotTable.PivotFields(s顧客);
                excel_PivotFieldRow.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                excel_PivotFieldRow.Position = 1;
                //ビボットB列に日付
                excel_PivotFieldColum = (Excel.PivotField)excel_PivotTable.PivotFields(s日付);
                excel_PivotFieldColum.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
                excel_PivotFieldColum.Position = 1;
                //ビボットデータに金額
                excel_PivotTable.AddDataField(
                    excel_PivotTable.PivotFields(s金額),
                    "ビボット表題",
                    Excel.XlConsolidationFunction.xlSum);


                //ビボットを月別にグループ処理
                excel_RangeOutgrup.Group(
                    true,
                    Type.Missing,
                    Type.Missing,
                    Type.Missing);

                //2秒表示
                System.Threading.Thread.Sleep(2000);

     

                //ビボットを四半期別にグループ処理
                bool[] Periods = new bool[] { false, false, false, false, false, true, false };
                excel_RangeOutgrup.Group(
                    true,
                    Type.Missing,
                    Type.Missing,
                    Periods);
                //2秒表示
                System.Threading.Thread.Sleep(2000);


                //ビボットを月別にグループ処理
                //四半期削除
                Periods[5] = false;
                //月別設定
                Periods[4] = true;
                excel_RangeOutgrup.Group(
                    true,
                    Type.Missing,
                    Type.Missing,
                    Periods);

                //2秒表示
                System.Threading.Thread.Sleep(2000);

     

                //以下の3行の使用方法を教えてください
                //Microsoft.Office.Tools.Excel.NamedRange NamedRangeVer;
                //NamedRangeVer = excel_Sheet.Controls.AddNamedRange(excel_RangeOut, "NamedRangeVer");
                //NamedRangeVer.Group(true, missing, missing, Periods);

     

                //リソース解放処理:
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_PivotFieldRow);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_PivotFieldColum);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_PivotTable);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_RangeOutgrup);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_RangeOut);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_Range);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_Sheet);
                //名前なしでセーブなし
                excel_Book.Close(false, "", false);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_Book);
                excel_Books.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_Books);
                excel_App.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel_App);
                GC.Collect();
            }
        }
    }

    2010年6月20日 17:05