예제 #1
0
        private static void MakeExcel(
            string pivotStyle,
            string tableStyle,
            XlChartType charType,
            DataTable dataSource,
            Excel.Workbook eWorkbook,
            int counter,
            string sheetTitle,
            string pivotTableName,
            List <string> pivotRowFields,
            List <string> pivotValueFields,
            List <string> pivotColumnFields,
            List <string> pivotReportFields,
            List <string> sliderFields)
        {
            var startRow = 1;
            var endIndex = dataSource.Rows.Count + startRow;

            var sheets = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets;
            var sheet  = (Excel.Worksheet)sheets.Add();

            sheet.Name = sheetTitle + (++counter);

            for (var i = 1; i < dataSource.Columns.Count + 1; i++)
            {
                sheet.Cells[startRow, i] = dataSource.Columns[i - 1].ColumnName;
                if (dataSource.Columns[i - 1].DataType == Type.GetType("System.DateTime"))
                {
                    sheet.Range[sheet.Cells[startRow + 1, i], sheet.Cells[endIndex, i]].NumberFormat =
                        Cthread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern;
                }
                else if (dataSource.Columns[i - 1].DataType == Type.GetType("System.Decimal"))
                {
                    sheet.Range[sheet.Cells[startRow + 1, i], sheet.Cells[endIndex, i]].NumberFormat =
                        $"#{Cthread.CurrentThread.CurrentCulture.NumberFormat.NumberGroupSeparator}##0{Cthread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator}00";
                }
            }

            try
            {
                sheet.Range[sheet.Cells[startRow + 1, 1], sheet.Cells[endIndex, dataSource.Columns.Count]].Value2 =
                    General.Convert(dataSource);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }

            General.FormatAsTable(sheet.Range[sheet.Cells[startRow, 1], sheet.Cells[endIndex, dataSource.Columns.Count]],
                                  "SourceData" + counter, tableStyle, false);


            //eApplication.Visible = true;
            //sheet.Activate();
            //sheet.Application.ActiveWindow.SplitRow = 1;
            //sheet.Application.ActiveWindow.SplitColumn = 1;
            //sheet.Application.ActiveWindow.FreezePanes = true;
            //// Now apply autofilter
            //var firstRow = (Excel.Range)sheet.Rows[1];

            //firstRow.AutoFilter(1,
            //    Type.Missing,
            //    Excel.XlAutoFilterOperator.xlAnd,
            //    Type.Missing,
            //    true);

            sheet.Range[sheet.Cells[startRow, 1], sheet.Cells[endIndex, dataSource.Columns.Count]].Columns.AutoFit();
            //sheet.Columns.AutoFit();


            var pivotData = sheet.Range["A1",
                                        General.GetColumnName(dataSource.Columns.Count) + (dataSource.Rows.Count + 1)];



            var pivotWorkSheet = General.AddPivot(
                pivotStyle,
                pivotData,
                dataSource,
                counter,
                pivotTableName,
                pivotRowFields,
                pivotColumnFields,
                pivotValueFields,
                pivotReportFields);



            General.AddChart(pivotWorkSheet, sheetTitle, pivotData, charType);

            General.AddSlicers(eWorkbook, (Excel.PivotTable)pivotWorkSheet.PivotTables(pivotTableName),
                               pivotWorkSheet, sliderFields);

            pivotWorkSheet.Select();

            //Excel.Application oApp = Globals.ThisAddIn.Application;
            //oApp.Visible = true;
            //oApp.ScreenUpdating = true;
            //oApp.UserControl = true;
            //oApp.Interactive = true;
        }