Exemplo n.º 1
0
        public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
        {
            object[,] sourceData = new object[, ] {
                { "Order ID", "Product", "Category", "Amount", "Date", "Country" },
                { 1, "Carrots", "Vegetables", 4270, new DateTime(2018, 1, 6), "United States" },
                { 2, "Broccoli", "Vegetables", 8239, new DateTime(2018, 1, 7), "United Kingdom" },
                { 3, "Banana", "Fruit", 617, new DateTime(2018, 1, 8), "United States" },
                { 4, "Banana", "Fruit", 8384, new DateTime(2018, 1, 10), "Canada" },
                { 5, "Beans", "Vegetables", 2626, new DateTime(2018, 1, 10), "Germany" },
                { 6, "Orange", "Fruit", 3610, new DateTime(2018, 1, 11), "United States" },
                { 7, "Broccoli", "Vegetables", 9062, new DateTime(2018, 1, 11), "Australia" },
                { 8, "Banana", "Fruit", 6906, new DateTime(2018, 1, 16), "New Zealand" },
                { 9, "Apple", "Fruit", 2417, new DateTime(2018, 1, 16), "France" },
                { 10, "Apple", "Fruit", 7431, new DateTime(2018, 1, 16), "Canada" },
                { 11, "Banana", "Fruit", 8250, new DateTime(2018, 1, 16), "Germany" },
                { 12, "Broccoli", "Vegetables", 7012, new DateTime(2018, 1, 18), "United States" },
                { 13, "Carrots", "Vegetables", 1903, new DateTime(2018, 1, 20), "Germany" },
                { 14, "Broccoli", "Vegetables", 2824, new DateTime(2018, 1, 22), "Canada" },
                { 15, "Apple", "Fruit", 6946, new DateTime(2018, 1, 24), "France" },
            };

            IWorksheet worksheet = workbook.Worksheets[0];

            worksheet.Range["A1:F16"].Value    = sourceData;
            worksheet.Range["A:F"].ColumnWidth = 15;

            //Create pivot cache.
            IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
            //Create pivot tables.
            IPivotTable pivottable1 = worksheet.PivotTables.Add(pivotcache, worksheet.Range["K5"], "pivottable1");
            IPivotTable pivottable2 = worksheet.PivotTables.Add(pivotcache, worksheet.Range["N3"], "pivottable2");

            worksheet.Range["D2:D16"].NumberFormat = "$#,##0.00";

            //Config pivot fields
            IPivotField field_product1 = pivottable1.PivotFields[1];

            field_product1.Orientation = PivotFieldOrientation.RowField;

            IPivotField field_Amount1 = pivottable1.PivotFields[3];

            field_Amount1.Orientation = PivotFieldOrientation.DataField;

            IPivotField field_product2 = pivottable2.PivotFields[5];

            field_product2.Orientation = PivotFieldOrientation.RowField;

            IPivotField field_Amount2 = pivottable2.PivotFields[2];

            field_Amount2.Orientation = PivotFieldOrientation.DataField;
            field_Amount2.Function    = ConsolidationFunction.Count;

            //create slicer cache, the slicers base the slicer cache just control pivot table1.
            ISlicerCache cache   = workbook.SlicerCaches.Add(pivottable1, "Product");
            ISlicer      slicer1 = cache.Slicers.Add(workbook.Worksheets["Sheet1"], "p1", "Product", 30, 550, 100, 200);

            //add pivot table2 for slicer cache, the slicers base the slicer cache will control pivot tabl1 and pivot table2.
            cache.PivotTables.AddPivotTable(pivottable2);
        }
Exemplo n.º 2
0
        /// <summary>
        /// Creates Spreadsheet with Styles and Formatting
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCreate_Click(object sender, EventArgs e)
        {
            #region Worksheet Initialize
            //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;

            //Set the default version as Excel 2007;
            if (rdbExcel2007.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2007;
            }
            //Set the default version as Excel 2010;
            if (rdbExcel2010.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2010;
            }
            //Set the default version as Excel 2013;
            if (rdbExcel2013.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }

            //Get the Path of Input file
            string    inputPath = GetFullTemplatePath("PivotCodeDate.xlsx");
            IWorkbook workbook  = application.Workbooks.Open(inputPath);

            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet worksheet = workbook.Worksheets[0];
            #endregion

            #region Create Pivot Table
            //Access the worksheet to draw pivot table.
            IWorksheet pivotSheet = workbook.Worksheets[1];
            pivotSheet.Activate();
            //Select the data to add in cache
            IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[4].Axis = PivotAxisTypes.Page;

            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field = pivotSheet.PivotTables[0].Fields[5];
            pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
#if !SyncfusionFramework2_0
            #region Apply RowField Filter
            if (ckbRowFieldFilter.Checked)
            {
                if (rdbRCLabelFilter.Checked)
                {
                    pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.CaptionEqual, null, "East", null);
                }
                else if (rdbRCValueFilter.Checked)
                {
                    pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.ValueEqual, field, "1341", null);
                }
                else
                {
                    pivotTable.Fields[2].Items[0].Visible = false;
                    pivotTable.Fields[2].Items[1].Visible = false;
                }
            }
            #endregion

            #region Column Field Filter
            if (ckbColumnFieldFilter.Checked)
            {
                if (rdbRCLabelFilter.Checked)
                {
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Jones", null);
                }
                else if (rdbRCValueFilter.Checked)
                {
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueEqual, field, "398", null);
                }
                else
                {
                    pivotTable.Fields[3].Items[0].Visible = false;
                    pivotTable.Fields[3].Items[1].Visible = false;
                }
            }
            #endregion

            #region PageField Filter
            if (ckbPageFilter.Checked)
            {
                //'Create Pivot Filter object to apply filter to page Fields
                IPivotFilter filterValue = pivotTable.Fields[4].PivotFilters.Add();
                //Page Field would be filtered with value 'Binder'
                filterValue.Value1 = "Binder";
                //XlsIO layout the Pivot table like MS Excel
                if (!rdbRCValueFilter.Checked)
                {
                    pivotTable.Layout();
                }
            }
            else if (ckbMultiplePageFilter.Checked)
            {
                pivotTable.Fields[4].Items[1].Visible = false;
                pivotTable.Fields[4].Items[2].Visible = false;
                if (!rdbRCValueFilter.Checked)
                {
                    pivotTable.Layout();
                }
            }

            #endregion
#endif

            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;

            //Activate the pivot worksheet.
            pivotSheet.Activate();
            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            #endregion

            #region Save the Workbook
            //Saving the workbook to disk.
            workbook.SaveAs("PivotTable.xlsx");
            #endregion

            #region Workbook Close and Dispose
            //Close the workbook.
            workbook.Close();

            //No exception will be thrown if there are unsaved workbooks.
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
            #endregion

            #region View the Workbook
            //Message box confirmation to view the created spreadsheet.
            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
                                MessageBoxButtons.YesNo, MessageBoxIcon.Information)
                == DialogResult.Yes)
            {
                try
                {
                    //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                    System.Diagnostics.Process.Start("PivotTable.xlsx");
                    //Exit
                    //this.Close();
                }
                catch (Win32Exception)
                {
                    MessageBox.Show("Excel 2007 is not installed in this system");
                }
            }
            else
            {
                // Exit
                this.Close();
            }
            #endregion
        }
Exemplo n.º 3
0
        private void btnCustomize_Click(object sender, EventArgs e)
        {
            #region Workbook Initialize
            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            //Set the default version as Excel 2007;
            if (rdbExcel2007.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2007;
            }
            //Set the default version as Excel 2010;
            if (rdbExcel2010.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2010;
            }
            //Set the default version as Excel 2013;
            if (rdbExcel2013.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }
            //Get the Path of the Input File
            string    inputPath = GetFullTemplatePath("PivotTable.xlsx");
            IWorkbook workbook  = application.Workbooks.Open(inputPath);

            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet worksheet = workbook.Worksheets[1];
            worksheet.Activate();
            #endregion

            #region Customize the Pivot Table
            //Access the collection of Pivot Table in the worksheet.
            IPivotTables pivotTables = worksheet.PivotTables;

            //Access the Single pivot table from the collection.
            IPivotTable pivotTable = pivotTables[0];

            //Access collection of pivot fields from the pivot table.
            IPivotFields fields = pivotTable.Fields;

            //Access a Pivot field from the collection.
            IPivotField field = fields[2];
            //Add the field to page axis
            field.Axis = PivotAxisTypes.Page;

            fields[1].Axis = PivotAxisTypes.None;
            fields[0].Axis = PivotAxisTypes.None;
            fields[3].Axis = PivotAxisTypes.Row;
            fields[4].Axis = PivotAxisTypes.Column;

            //Accessing the Calculated fields from the pivot table .
            IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;
            IPivotField            dataField        = fields[5];
            //Adding Calculatd field to the pivot table.
            //  IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");
#if !SyncfusionFramework2_0
            if (ckbRowFieldFilter.Checked)
            {
                if (rdbRCLabelFilter.Checked)
                {
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Parent", null);
                }
                else if (rdbRCValueFilter.Checked)
                {
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
                }
                else
                {
                    pivotTable.Fields[3].Items[0].Visible = false;
                }
            }
            if (ckbColumnFieldFilter.Checked)
            {
                if (rdbRCLabelFilter.Checked)
                {
                    pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Binder", null);
                }
                else if (rdbRCValueFilter.Checked)
                {
                    pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
                }
                else
                {
                    pivotTable.Fields[4].Items[0].Visible = false;
                }
            }
            if (ckbPageFilter.Checked)
            {
                //'Create Pivot Filter object to apply filter to page Fields
                IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
                //Page Field would be filtered with value 'East'
                filterValue.Value1 = "East";
                //XlsIO layout the Pivot table like MS Excel
                if (!rdbRCValueFilter.Checked)
                {
                    pivotTable.Layout();
                }
            }
            else if (ckbMultiplePageFilter.Checked)
            {
                pivotTable.Fields[2].Items[0].Visible = false;
            }
#endif

            #endregion

            #region Workbook Save
            //Saving the workbook to disk.
            worksheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            worksheet.SetColumnWidth(1, 15.29);
            worksheet.SetColumnWidth(2, 15.29);
            workbook.SaveAs("CustomizedPivotTable.xlsx");
            #endregion

            #region Workbook Close and Dispose
            //Close the workbook.
            workbook.Close();

            //No exception will be thrown if there are unsaved workbooks.
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
            #endregion

            #region View the Workbook
            //Message box confirmation to view the created spreadsheet.
            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
                                MessageBoxButtons.YesNo, MessageBoxIcon.Information)
                == DialogResult.Yes)
            {
                try
                {
                    //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                    System.Diagnostics.Process.Start("CustomizedPivotTable.xlsx");
                    //Exit
                    this.Close();
                }
                catch (Win32Exception)
                {
                    MessageBox.Show("MS Excel is not installed in this system");
                }
            }
            else
            {
                // Exit
                this.Close();
            }
            #endregion
        }
Exemplo n.º 4
0
        private static void CreatePivotTable(IWorkbook workbook, string layoutOption)
        {
            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet sheet = workbook.Worksheets[0];
            //Access the sheet to draw pivot table.
            IWorksheet pivotSheet = workbook.Worksheets[1];

            pivotSheet.Activate();
            //Select the data to add in cache
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:G20"]);
            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);

            pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            IPivotField field1 = pivotSheet.PivotTables[0].Fields[5];

            pivotTable.DataFields.Add(field1, "Sum of Land Area", PivotSubtotalTypes.Sum);
            IPivotField field2 = pivotSheet.PivotTables[0].Fields[6];

            pivotTable.DataFields.Add(field2, "Sum of Water Area", PivotSubtotalTypes.Sum);

            if (layoutOption == "Outline")
            {
                pivotTable.Options.RowLayout = PivotTableRowLayout.Outline;

                pivotTable.Location = pivotSheet.Range[1, 1, 51, 5];

                //Apply Inline formatting to pivot table
                IPivotCellFormat cellFormat1 = pivotTable.GetCellFormat("B3:E4");
                cellFormat1.BackColorRGB = Color.FromArgb(255, 169, 208, 142);
                IPivotCellFormat cellFormat2 = pivotTable.GetCellFormat("B31:E32");
                cellFormat2.BackColorRGB = Color.FromArgb(255, 244, 176, 132);
            }
            else if (layoutOption == "Tabular")
            {
                pivotTable.Location = pivotSheet.Range[1, 1, 51, 5];

                pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;

                //Apply Inline formatting to pivot table
                IPivotCellFormat cellFormat1 = pivotTable.GetCellFormat("B2:E2");
                cellFormat1.BackColorRGB = Color.FromArgb(255, 169, 208, 142);
                IPivotCellFormat cellFormat2 = pivotTable.GetCellFormat("B30:E30");
                cellFormat2.BackColorRGB = Color.FromArgb(255, 244, 176, 132);
            }
            else
            {
                pivotTable.Location = pivotSheet.Range[1, 1, 51, 3];

                //Apply Inline formatting to pivot table
                IPivotCellFormat cellFormat1 = pivotTable.GetCellFormat("A3:C4");
                cellFormat1.BackColorRGB = Color.FromArgb(255, 169, 208, 142);
                IPivotCellFormat cellFormat2 = pivotTable.GetCellFormat("A31:C32");
                cellFormat2.BackColorRGB = Color.FromArgb(255, 244, 176, 132);
            }

            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;
            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);

            if (pivotTable.Options.RowLayout == PivotTableRowLayout.Compact)
            {
                pivotSheet.SetColumnWidth(4, 1.0);
                pivotSheet.SetColumnWidth(5, 2.0);
                pivotSheet.SetColumnWidth(6, 0.5);
                pivotSheet.Range[2, 5, 2, 5].CellStyle.Color = Color.FromArgb(255, 169, 208, 142);
                pivotSheet.Range[4, 5, 4, 5].CellStyle.Color = Color.FromArgb(255, 244, 176, 132);
                pivotSheet.Range[2, 7, 2, 7].Text            = "County with largest land area";
                pivotSheet.Range[4, 7, 4, 7].Text            = "County with smallest land area";
            }
            else
            {
                pivotSheet.SetColumnWidth(6, 1.0);
                pivotSheet.SetColumnWidth(7, 2.0);
                pivotSheet.SetColumnWidth(8, 0.5);
                pivotSheet.Range[2, 7, 2, 7].CellStyle.Color = Color.FromArgb(255, 169, 208, 142);
                pivotSheet.Range[4, 7, 4, 7].CellStyle.Color = Color.FromArgb(255, 244, 176, 132);
                pivotSheet.Range[2, 9, 2, 9].Text            = "County with largest land area";
                pivotSheet.Range[4, 9, 4, 9].Text            = "County with smallest land area";
            }

            //Activate the pivot sheet.
            pivotSheet.Activate();
        }
Exemplo n.º 5
0
        //
        // GET: /PivotTable/

        public ActionResult PivotTable(string button, string Filter, string RowFilter, string ColumnFilter, string MultiplePageFilter, string PageFilter)
        {
            if (button == null)
            {
                return(View());
            }

            string basePath = _hostingEnvironment.WebRootPath;

            //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;
            IWorkbook    workbook    = null;

            application.DefaultVersion = ExcelVersion.Excel2016;

            if (button == "Customize Pivot Table")
            {
                FileStream inputStream = new FileStream(basePath + @"/XlsIO/PivotTable.xlsx", FileMode.Open, FileAccess.Read);
                workbook = application.Workbooks.Open(inputStream);

                // The first worksheet object in the worksheets collection is accessed.
                IWorksheet sheet = workbook.Worksheets[1];
                sheet.Activate();
                //Access the collection of Pivot Table in the worksheet.
                IPivotTables pivotTables = sheet.PivotTables;

                //Access the Single pivot table from the collection.
                IPivotTable pivotTable = pivotTables[0];

                //Access collection of pivot fields from the pivot table.
                IPivotFields fields = pivotTable.Fields;

                //Access a Pivot field from the collection.
                IPivotField field = fields[2];
                //Add the field to page axis
                field.Axis     = PivotAxisTypes.Page;
                fields[1].Axis = PivotAxisTypes.None;
                fields[0].Axis = PivotAxisTypes.None;
                fields[3].Axis = PivotAxisTypes.Row;
                fields[4].Axis = PivotAxisTypes.Column;
                IPivotField dataField = fields[5];
                //Accessing the Calculated fields from the pivot table .
                IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;

                if (RowFilter == "RowFilter")
                {
                    if (Filter == "LabelFilter")
                    {
                        pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Parent", null);
                    }
                    else if (Filter == "ValueFilter")
                    {
                        pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
                    }
                    else
                    {
                        pivotTable.Fields[3].Items[0].Visible = false;
                    }
                }
                if (ColumnFilter == "ColumnFilter")
                {
                    if (Filter == "LabelFilter")
                    {
                        pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Binder", null);
                    }
                    else if (Filter == "ValueFilter")
                    {
                        pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
                    }
                    else
                    {
                        pivotTable.Fields[4].Items[0].Visible = false;
                    }
                }
                //Adding Calculatd field to the pivot table.
                //  IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");
                if (PageFilter == "PageFilter")
                {
                    //Create Pivot Filter object to apply filter to page Fields
                    IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
                    //Page Field would be filtered with value 'Binder'
                    filterValue.Value1 = "East";
                    //XlsIO layout the Pivot table like Microsoft Excel
                    if (Filter != "ValueFilter")
                    {
                        pivotTable.Layout();
                    }
                }
                else if (MultiplePageFilter == "MultiplePageFilter")
                {
                    pivotTable.Fields[2].Items[0].Visible = false;
                }
                sheet.Range[1, 1, 1, 14].ColumnWidth = 11;
                sheet.SetColumnWidth(1, 15.29);
                sheet.SetColumnWidth(2, 15.29);
                try
                {
                    MemoryStream ms = new MemoryStream();
                    workbook.SaveAs(ms);
                    ms.Position = 0;

                    return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "PivotTable.xlsx"));
                }
                catch (Exception)
                {
                }
            }
            else
            {
                FileStream inputStream = new FileStream(basePath + @"/XlsIO/PivotCodeDate.xlsx", FileMode.Open, FileAccess.Read);
                workbook = application.Workbooks.Open(inputStream);

                // The first worksheet object in the worksheets collection is accessed.
                IWorksheet sheet = workbook.Worksheets[0];

                //Access the sheet to draw pivot table.
                IWorksheet pivotSheet = workbook.Worksheets[1];
                pivotSheet.Activate();
                //Select the data to add in cache
                IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);

                //Insert the pivot table.
                IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
                pivotTable.Fields[4].Axis = PivotAxisTypes.Page;

                pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
                pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
                pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

                IPivotField field = pivotSheet.PivotTables[0].Fields[5];
                pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
                #region Apply RowField Filter
                if (RowFilter == "RowFilter")
                {
                    if (Filter == "LabelFilter")
                    {
                        pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.CaptionEqual, null, "East", null);
                    }
                    else if (Filter == "ValueFilter")
                    {
                        pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.ValueEqual, field, "1341", null);
                    }
                    else
                    {
                        pivotTable.Fields[2].Items[0].Visible = false;
                        pivotTable.Fields[2].Items[1].Visible = false;
                    }
                }
                #endregion

                #region Column Field Filter
                if (ColumnFilter == "ColumnFilter")
                {
                    if (Filter == "LabelFilter")
                    {
                        pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Jones", null);
                    }
                    else if (Filter == "ValueFilter")
                    {
                        pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueEqual, field, "398", null);
                    }
                    else
                    {
                        pivotTable.Fields[3].Items[0].Visible = false;
                        pivotTable.Fields[3].Items[1].Visible = false;
                    }
                }
                #endregion
                if (PageFilter == "PageFilter")
                {
                    //'Create Pivot Filter object to apply filter to page Fields
                    IPivotFilter filterValue = pivotTable.Fields[4].PivotFilters.Add();
                    //Page Field would be filtered with value 'Binder'
                    filterValue.Value1 = "Binder";
                    //XlsIO layout the Pivot table like Microsoft Excel
                    if (Filter != "ValueFilter")
                    {
                        pivotTable.Layout();
                    }
                }
                else
                {
                    pivotTable.Fields[4].Items[1].Visible = false;
                    pivotTable.Fields[4].Items[2].Visible = false;
                }
                //Apply built in style.
                pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
                pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
                pivotSheet.SetColumnWidth(1, 15.29);
                pivotSheet.SetColumnWidth(2, 15.29);
                //Activate the pivot sheet.
                pivotSheet.Activate();

                try
                {
                    workbook.Version = ExcelVersion.Excel2016;
                    MemoryStream ms = new MemoryStream();
                    workbook.SaveAs(ms);
                    ms.Position = 0;

                    return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "PivotTable.xlsx"));
                }
                catch (Exception)
                {
                }
            }
            //Close the workbook.
            workbook.Close();
            excelEngine.Dispose();
            return(View());
        }
Exemplo n.º 6
0
        private async void btnCreate_Click(object sender, RoutedEventArgs e)
        {
            #region Setting output location
            StorageFile storageFile;
            if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
            {
                FileSavePicker savePicker = new FileSavePicker();
                savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
                savePicker.SuggestedFileName      = "PivotTableCreateSample";
                savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
                {
                    ".xlsx",
                });
                storageFile = await savePicker.PickSaveFileAsync();
            }
            else
            {
                StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
                storageFile = await local.CreateFileAsync("PivotTableCreateSample.xlsx", CreationCollisionOption.ReplaceExisting);
            }

            if (storageFile == null)
            {
                return;
            }
            #endregion

            #region Initializing Workbook
            ExcelEngine  excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel2013;

            Assembly  assembly     = typeof(PivotTable).GetTypeInfo().Assembly;
            string    resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.PivotCodeData.xlsx";
            Stream    fileStream   = assembly.GetManifestResourceStream(resourcePath);
            IWorkbook workbook     = await application.Workbooks.OpenAsync(fileStream);

            IWorksheet dataSheet  = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            #endregion

            #region Creating Pivot Table
            IPivotCache cache = workbook.PivotCaches.Add(dataSheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field1 = pivotSheet.PivotTables[0].Fields[5];
            pivotTable.DataFields.Add(field1, "Sum of Units", PivotSubtotalTypes.Sum);

            pivotTable.ShowDrillIndicators  = true;
            pivotTable.RowGrand             = true;
            pivotTable.DisplayFieldCaptions = true;
            pivotTable.BuiltInStyle         = PivotBuiltInStyles.PivotStyleMedium2;
            pivotSheet.Activate();

            #region Dynamic source change

            #region Adding rows
            string accountingFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)";
            string dateFormat       = "[$-409]d-mmm-yy;@";

            dataSheet["A51"].NumberFormat = dateFormat;
            dataSheet["A51"].DateTime     = DateTime.Parse("5/12/2012", CultureInfo.InvariantCulture);
            dataSheet["B51"].Formula      = "=TEXT(A51,\"dddd\")";
            dataSheet["C51"].Value        = "Central";
            dataSheet["D51"].Value        = "Allan";
            dataSheet["E51"].Value        = "Binder";
            dataSheet["F51"].Number       = 87;
            dataSheet["G51"].Number       = 3.21;
            dataSheet["G51"].NumberFormat = accountingFormat;
            dataSheet["H51"].Formula      = "G51*F51";
            dataSheet["H51"].NumberFormat = accountingFormat;

            dataSheet["A52"].NumberFormat = dateFormat;
            dataSheet["A52"].DateTime     = DateTime.Parse("5/15/2012", CultureInfo.InvariantCulture);
            dataSheet["B52"].Formula      = "=TEXT(A52,\"dddd\")";
            dataSheet["C52"].Value        = "Central";
            dataSheet["D52"].Value        = "Andrew";
            dataSheet["E52"].Value        = "Binder";
            dataSheet["F52"].Number       = 95;
            dataSheet["G52"].Number       = 2.48;
            dataSheet["G52"].NumberFormat = accountingFormat;
            dataSheet["H52"].Formula      = "G52*F52";
            dataSheet["H52"].NumberFormat = accountingFormat;

            dataSheet["A53"].NumberFormat = dateFormat;
            dataSheet["A53"].DateTime     = DateTime.Parse("5/18/2012", CultureInfo.InvariantCulture);
            dataSheet["B53"].Formula      = "=TEXT(A53,\"dddd\")";
            dataSheet["C53"].Value        = "West";
            dataSheet["D53"].Value        = "Kevin";
            dataSheet["E53"].Value        = "Binder";
            dataSheet["F53"].Number       = 68;
            dataSheet["G53"].Number       = 1.75;
            dataSheet["G53"].NumberFormat = accountingFormat;
            dataSheet["H53"].Formula      = "G53*F53";
            dataSheet["H53"].NumberFormat = accountingFormat;

            dataSheet["A54"].NumberFormat = dateFormat;
            dataSheet["A54"].DateTime     = DateTime.Parse("5/21/2012", CultureInfo.InvariantCulture);
            dataSheet["B54"].Formula      = "=TEXT(A54,\"dddd\")";
            dataSheet["C54"].Value        = "West";
            dataSheet["D54"].Value        = "Jack";
            dataSheet["E54"].Value        = "Binder";
            dataSheet["F54"].Number       = 19;
            dataSheet["G54"].Number       = 1.01;
            dataSheet["G54"].NumberFormat = accountingFormat;
            dataSheet["H54"].Formula      = "G54*F54";
            dataSheet["H54"].NumberFormat = accountingFormat;

            dataSheet["A55"].NumberFormat = dateFormat;
            dataSheet["A55"].DateTime     = DateTime.Parse("5/24/2012", CultureInfo.InvariantCulture);
            dataSheet["B55"].Formula      = "=TEXT(A55,\"dddd\")";
            dataSheet["C55"].Value        = "East";
            dataSheet["D55"].Value        = "Allan";
            dataSheet["E55"].Value        = "File Folder";
            dataSheet["F55"].Number       = 20;
            dataSheet["G55"].Number       = 0.28;
            dataSheet["G55"].NumberFormat = accountingFormat;
            dataSheet["H55"].Formula      = "G55*F55";
            dataSheet["H55"].NumberFormat = accountingFormat;

            dataSheet["A56"].NumberFormat = dateFormat;
            dataSheet["A56"].DateTime     = DateTime.Parse("5/27/2012", CultureInfo.InvariantCulture);
            dataSheet["B56"].Formula      = "=TEXT(A56,\"dddd\")";
            dataSheet["C56"].Value        = "East";
            dataSheet["D56"].Value        = "Jack";
            dataSheet["E56"].Value        = "File Folder";
            dataSheet["F56"].Number       = 32;
            dataSheet["G56"].Number       = 0.45;
            dataSheet["G56"].NumberFormat = accountingFormat;
            dataSheet["H56"].Formula      = "G56*F56";
            dataSheet["H56"].NumberFormat = accountingFormat;

            dataSheet["A57"].NumberFormat = dateFormat;
            dataSheet["A57"].DateTime     = DateTime.Parse("5/30/2012", CultureInfo.InvariantCulture);
            dataSheet["B57"].Formula      = "=TEXT(A57,\"dddd\")";
            dataSheet["C57"].Value        = "West";
            dataSheet["D57"].Value        = "Kevin";
            dataSheet["E57"].Value        = "Binder";
            dataSheet["F57"].Number       = 23;
            dataSheet["G57"].Number       = 1.19;
            dataSheet["G57"].NumberFormat = accountingFormat;
            dataSheet["H57"].Formula      = "G57*F57";
            dataSheet["H57"].NumberFormat = accountingFormat;

            dataSheet["A58"].NumberFormat = dateFormat;
            dataSheet["A58"].DateTime     = DateTime.Parse("6/2/2012", CultureInfo.InvariantCulture);
            dataSheet["B58"].Formula      = "=TEXT(A58,\"dddd\")";
            dataSheet["C58"].Value        = "West";
            dataSheet["D58"].Value        = "Andrew";
            dataSheet["E58"].Value        = "Binder";
            dataSheet["F58"].Number       = 43;
            dataSheet["G58"].Number       = 1.92;
            dataSheet["G58"].NumberFormat = accountingFormat;
            dataSheet["H58"].Formula      = "G58*F58";
            dataSheet["H58"].NumberFormat = accountingFormat;
            #endregion

            //Adding new named range to the workbook
            IName name = workbook.Names.Add("DynamicRange", dataSheet.UsedRange);

            //Setting pivot table source range from named range
            workbook.PivotCaches[0].SourceRange = name.RefersToRange;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            pivotSheet.SetColumnWidth(14, 10.43);
            #endregion

            #endregion

            #region Saving workbook and disposing objects

            await workbook.SaveAsAsync(storageFile);

            workbook.Close();
            excelEngine.Dispose();

            #endregion

            #region Save accknowledgement and Launching of output file
            MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");

            UICommand yesCmd = new UICommand("Yes");
            msgDialog.Commands.Add(yesCmd);
            UICommand noCmd = new UICommand("No");
            msgDialog.Commands.Add(noCmd);
            IUICommand cmd = await msgDialog.ShowAsync();

            if (cmd == yesCmd)
            {
                // Launch the saved file
                bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
            }
            #endregion
        }
Exemplo n.º 7
0
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;

            //Set the default version as Excel 2007;
            if (this.rdButtonxlsx.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2007;
            }
            else if (this.rdButtonexcel2010.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2010;
            }
            else if (this.rdButtonexcel2013.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }

            IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");

            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet sheet = workbook.Worksheets[0];

            //Access the sheet to draw pivot table.
            IWorksheet pivotSheet = workbook.Worksheets[1];

            pivotSheet.Activate();
            //Select the data to add in cache
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);

            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[4].Axis = PivotAxisTypes.Page;
            pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field = pivotSheet.PivotTables[0].Fields[5];

            pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);

            if (chkRowFilter.IsChecked.Value)
            {
                if (rdbLabelFilter.IsChecked.Value)
                {
                    //Applying label based filter to row field
                    pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.CaptionEqual, null, "East", null);
                }
                else if (rdbValueFilter.IsChecked.Value)
                {
                    //Applying value based filter to row field
                    pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.ValueEqual, field, "1341", null);
                }
                else
                {
                    //Applying multiple item filter to row field
                    pivotTable.Fields[2].Items[0].Visible = false;
                    pivotTable.Fields[2].Items[1].Visible = false;
                }
            }
            if (chkColumnFilter.IsChecked.Value)
            {
                if (rdbLabelFilter.IsChecked.Value)
                {
                    //Applying label based filter to row field
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Jones", null);
                }
                else if (rdbValueFilter.IsChecked.Value)
                {
                    //Applying value based filter to column field
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueEqual, field, "398", null);
                }
                else
                {
                    //Applying multiple item filter to Column field
                    pivotTable.Fields[3].Items[0].Visible = false;
                    pivotTable.Fields[3].Items[1].Visible = false;
                }
            }
            if (chkPageFilter.IsChecked.Value)
            {
                //Create Pivot Filter object to apply filter to page Fields
                IPivotFilter filterValue = pivotTable.Fields[4].PivotFilters.Add();
                //Page Field would be filtered with value 'East'
                filterValue.Value1 = "Binder";
                if (!rdbValueFilter.IsChecked.Value)
                {
                    //XlsIO layout the Pivot table like MS Excel
                    pivotTable.Layout();
                }
            }
            else if (chkMultiplePageFilter.IsChecked.Value)
            {
                //Applying multiple item filter page field
                pivotTable.Fields[4].Items[1].Visible = false;
                pivotTable.Fields[4].Items[2].Visible = false;
                if (!rdbValueFilter.IsChecked.Value)
                {
                    pivotTable.Layout();
                }
            }
            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            //Activate the pivot sheet.
            pivotSheet.Activate();

            try
            {
                //Saving the workbook to disk.
                workbook.SaveAs("PivotTable.xlsx");

                //Close the workbook.
                workbook.Close();
                excelEngine.Dispose();

                //Message box confirmation to view the created spreadsheet.
                if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
                                    MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
                {
                    try
                    {
                        //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                        System.Diagnostics.Process.Start("PivotTable.xlsx");
                        //Exit
                        this.Close();
                    }
                    catch (Win32Exception ex)
                    {
                        MessageBox.Show("Excel 2007 is not installed in this system");
                        Console.WriteLine(ex.ToString());
                    }
                }
                else
                {
                    // Exit
                    this.Close();
                }
            }
            catch
            {
                MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK);
            }
        }
Exemplo n.º 8
0
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            if (this.rdButtonxlsx.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2007;
            }
            else if (this.rdButtonexcel2010.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2010;
            }
            else if (this.rdButtonexcel2013.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }

            IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotTable.xlsx");

            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet sheet = workbook.Worksheets[1];

            sheet.Activate();
            //Access the collection of Pivot Table in the worksheet.
            IPivotTables pivotTables = sheet.PivotTables;

            //Access the Single pivot table from the collection.
            IPivotTable pivotTable = pivotTables[0];

            //Access collection of pivot fields from the pivot table.
            IPivotFields fields = pivotTable.Fields;

            //Access a Pivot field from the collection.
            IPivotField field = fields[2];

            //Add the field to page axis
            field.Axis = PivotAxisTypes.Page;


            fields[1].Axis = PivotAxisTypes.None;
            fields[0].Axis = PivotAxisTypes.None;
            fields[3].Axis = PivotAxisTypes.Row;
            fields[4].Axis = PivotAxisTypes.Column;
            IPivotField dataField = fields[5];

            //Accessing the Calculated fields from the pivot table .
            IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;

            //Adding Calculatd field to the pivot table.
            //IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");

            if (chkRowFilter.IsChecked.Value)
            {
                if (rdbLabelFilter.IsChecked.Value)
                {
                    //Applying label based filter to row field
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Parent", null);
                }
                else if (rdbValueFilter.IsChecked.Value)
                {
                    //Applying value based filter to row field
                    pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
                }
                else
                {
                    //Applying multiple item filter to row field
                    pivotTable.Fields[3].Items[0].Visible = false;
                }
            }
            if (chkColumnFilter.IsChecked.Value)
            {
                if (rdbLabelFilter.IsChecked.Value)
                {
                    //Applying label based filter to column field
                    pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Binder", null);
                }
                else if (rdbValueFilter.IsChecked.Value)
                {
                    //Applying value based filter to column field
                    pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
                }
                else
                {
                    //Applying multiple item filter to column field
                    pivotTable.Fields[4].Items[0].Visible = false;
                }
            }
            if (chkPageFilter.IsChecked.Value)
            {
                //'Create Pivot Filter object to apply filter to page Fields
                IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
                //Page Field would be filtered with value 'East'
                filterValue.Value1 = "East";
                //XlsIO layout the Pivot table like MS Excel
                if (!rdbValueFilter.IsChecked.Value)
                {
                    pivotTable.Layout();
                }
            }
            else if (chkMultiplePageFilter.IsChecked.Value)
            {
                pivotTable.Fields[2].Items[0].Visible = false;
                if (!rdbValueFilter.IsChecked.Value)
                {
                    pivotTable.Layout();
                }
            }
            sheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            sheet.SetColumnWidth(1, 15.29);
            sheet.SetColumnWidth(2, 15.29);

            try
            {
                //Saving the workbook to disk.
                workbook.SaveAs("Sample.xlsx");

                //Close the workbook.
                workbook.Close();

                //No exception will be thrown if there are unsaved workbooks.
                excelEngine.ThrowNotSavedOnDestroy = false;
                excelEngine.Dispose();

                //Message box confirmation to view the created spreadsheet.
                if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
                                    MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
                {
                    try
                    {
                        //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                        System.Diagnostics.Process.Start("Sample.xlsx");
                        //Exit
                        this.Close();
                    }
                    catch (Win32Exception ex)
                    {
                        MessageBox.Show("Excel 2007 is not installed in this system");
                        Console.WriteLine(ex.ToString());
                    }
                }
                else
                {
                    // Exit
                    this.Close();
                }
            }
            catch
            {
                MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK);
            }
        }
        /// <summary>
        /// Creates Spreadsheet with Styles and Formatting
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCreate_Click(object sender, EventArgs e)
        {
            #region Workbook Initialize
            //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;

            //Set the default version as Excel 2007;
            if (rdbExcel2007.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2007;
            }
            //Set the default version as Excel 2010;
            else if (rdbExcel2010.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2010;
            }
            else if (rdbExcel2013.Checked)
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }

            //Get the path of the input file
            string    inputPath = GetFullTemplatePath("PivotCodeDate.xlsx");
            IWorkbook workbook  = application.Workbooks.Open(inputPath);

            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet worksheet = workbook.Worksheets[0];
            #endregion

            #region Add Pivot Table
            //Access the worksheet to draw pivot table.
            IWorksheet pivotSheet = workbook.Worksheets[1];

            //Select the data to add in cache
            IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field = pivotSheet.PivotTables[0].Fields[5];
            pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);

            //Show expand/collapse button.
            pivotTable.ShowDrillIndicators = true;

            //Shows row grand.
            pivotTable.RowGrand = true;

            //Shows filter and field caption.
            pivotTable.DisplayFieldCaptions = true;

            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            #endregion

            #region Add Pivot Chart
            //Add the Pivot chart worksheet to the workbook.
            IChart pivotChartSheet = workbook.Charts.Add();
            pivotChartSheet.Name = "PivotChart";

            //Set the Pivot source for the Pivot Chart.
            pivotChartSheet.PivotSource = pivotTable;

            //Set the Pivot Chart Type.
            pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;

            //Activate the pivot worksheet.
            pivotChartSheet.Activate();
            #endregion

            #region Save the Workbook
            //Saving the workbook to disk.
            workbook.SaveAs("PivotChart.xlsx");
            #endregion

            #region Workbook Close and Dispose
            //Close the workbook.
            workbook.Close();

            //No exception will be thrown if there are unsaved workbooks.
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
            #endregion

            #region View the Workbook
            //Message box confirmation to view the created spreadsheet.
            if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
                                MessageBoxButtons.YesNo, MessageBoxIcon.Information)
                == DialogResult.Yes)
            {
                try
                {
                    //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
                    System.Diagnostics.Process.Start("PivotChart.xlsx");
                    //Exit
                    this.Close();
                }
                catch (Win32Exception)
                {
                    MessageBox.Show("MS Excel is not installed in this system");
                }
            }
            else
            {
                // Exit
                this.Close();
            }
            #endregion
        }
Exemplo n.º 10
0
        //
        // GET: /PivotChart/

        public ActionResult PivotChart(string button)
        {
            if (button == null)
            {
                return(View());
            }

            //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;


            IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath("PivotCodeDate.xlsx"));

            workbook.Version = ExcelVersion.Excel2016;
            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet sheet = workbook.Worksheets[0];

            //Access the sheet to draw pivot table.
            IWorksheet pivotSheet = workbook.Worksheets[1];

            //Select the data to add in cache
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);

            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field = pivotSheet.PivotTables[0].Fields[5];

            pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);

            //Show expand/collapse button.
            pivotTable.ShowDrillIndicators = true;

            //Shows row grand.
            pivotTable.RowGrand = true;

            //Shows filter and field caption.
            pivotTable.DisplayFieldCaptions = true;

            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;

            //Add the Pivot chart sheet to the workbook.
            IChart pivotChartSheet = workbook.Charts.Add();

            pivotChartSheet.Name = "PivotChart";

            //Set the Pivot source for the Pivot Chart.
            pivotChartSheet.PivotSource = pivotTable;

            //Set the Pivot Chart Type.
            pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;

            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            //Activate the pivot sheet.
            pivotChartSheet.Activate();

            try
            {
                return(excelEngine.SaveAsActionResult(workbook, "PivotChart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
            }
            catch (Exception)
            {
            }

            //Close the workbook.
            workbook.Close();
            excelEngine.Dispose();
            return(View());
        }
Exemplo n.º 11
0
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;

            //Set the default version as Excel 2007;
            if (this.rdButtonxlsx.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2007;
            }
            else if (this.rdButtonexcel2010.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2010;
            }
            else if (this.rdButtonexcel2013.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }

#if NETCORE
            IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
#else
            IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
#endif

            // The first worksheet object in the worksheets collection is accessed.
            IWorksheet sheet = workbook.Worksheets[0];

            //Access the sheet to draw pivot table.
            IWorksheet pivotSheet = workbook.Worksheets[1];

            //Select the data to add in cache
            IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field = pivotSheet.PivotTables[0].Fields[5];
            pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);

            //Show expand/collapse button.
            pivotTable.ShowDrillIndicators = true;

            //Shows row grand.
            pivotTable.RowGrand = true;

            //Shows filter and field caption.
            pivotTable.DisplayFieldCaptions = true;

            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;

            //Add the Pivot chart sheet to the workbook.
            IChart pivotChartSheet = workbook.Charts.Add();
            pivotChartSheet.Name = "PivotChart";

            //Set the Pivot source for the Pivot Chart.
            pivotChartSheet.PivotSource = pivotTable;

            //Set the Pivot Chart Type.
            pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;

            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            //Activate the pivot sheet.
            pivotChartSheet.Activate();

            try
            {
                //Saving the workbook to disk.
                workbook.SaveAs("PivotChart.xlsx");

                //Close the workbook.
                workbook.Close();
                excelEngine.Dispose();

                //Message box confirmation to view the created spreadsheet.
                if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
                                    MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
                {
                    try
                    {
                        //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
                        System.Diagnostics.Process process = new System.Diagnostics.Process();
                        process.StartInfo = new System.Diagnostics.ProcessStartInfo("PivotChart.xlsx")
                        {
                            UseShellExecute = true
                        };
                        process.Start();
#else
                        Process.Start("PivotChart.xlsx");
#endif
                        //Exit
                        this.Close();
                    }
                    catch (Win32Exception ex)
                    {
                        MessageBox.Show("Excel 2007 is not installed in this system");
                        Console.WriteLine(ex.ToString());
                    }
                }
                else
                {
                    // Exit
                    this.Close();
                }
            }
            catch
            {
                MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK);
            }
        }
Exemplo n.º 12
0
        private async void btnCreate_Click(object sender, RoutedEventArgs e)
        {
            #region Setting output location
            StorageFile storageFile;
            if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
            {
                FileSavePicker savePicker = new FileSavePicker();
                savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
                savePicker.SuggestedFileName      = "PivotChartCreateSample";
                savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
                {
                    ".xlsx",
                });
                storageFile = await savePicker.PickSaveFileAsync();
            }
            else
            {
                StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
                storageFile = await local.CreateFileAsync("PivotChartCreateSample.xlsx", CreationCollisionOption.ReplaceExisting);
            }

            if (storageFile == null)
            {
                return;
            }
            #endregion

            #region Initializing Workbook
            ExcelEngine  excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel2013;

            Assembly  assembly     = typeof(PivotChart).GetTypeInfo().Assembly;
            string    resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.PivotCodeData.xlsx";
            Stream    fileStream   = assembly.GetManifestResourceStream(resourcePath);
            IWorkbook workbook     = await application.Workbooks.OpenAsync(fileStream);

            IWorksheet dataSheet  = workbook.Worksheets[0];
            IWorksheet pivotSheet = workbook.Worksheets[1];
            #endregion

            #region Creating Pivot chart
            IPivotCache cache = workbook.PivotCaches.Add(dataSheet["A1:H50"]);

            //Insert the pivot table.
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
            pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
            pivotTable.Fields[3].Axis = PivotAxisTypes.Column;

            IPivotField field = pivotSheet.PivotTables[0].Fields[5];
            pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);

            //Show expand/collapse button.
            pivotTable.ShowDrillIndicators = true;

            //Shows row grand.
            pivotTable.RowGrand = true;

            //Shows filter and field caption.
            pivotTable.DisplayFieldCaptions = true;

            //Apply built in style.
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;

            //Add the Pivot chart sheet to the workbook.
            IChart pivotChartSheet = workbook.Charts.Add();
            pivotChartSheet.Name = "PivotChart";

            //Set the Pivot source for the Pivot Chart.
            pivotChartSheet.PivotSource = pivotTable;

            //Set the Pivot Chart Type.
            pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;

            pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
            pivotSheet.SetColumnWidth(1, 15.29);
            pivotSheet.SetColumnWidth(2, 15.29);
            //Activate the pivot sheet.
            pivotChartSheet.Activate();

            #endregion

            #region Saving workbook and disposing objects

            await workbook.SaveAsAsync(storageFile);

            workbook.Close();
            excelEngine.Dispose();

            #endregion

            #region Save accknowledgement and Launching of output file
            MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");

            UICommand yesCmd = new UICommand("Yes");
            msgDialog.Commands.Add(yesCmd);
            UICommand noCmd = new UICommand("No");
            msgDialog.Commands.Add(noCmd);
            IUICommand cmd = await msgDialog.ShowAsync();

            if (cmd == yesCmd)
            {
                // Launch the saved file
                bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
            }
            #endregion
        }
Exemplo n.º 13
0
        public ActionResult DistInd([Bind(Include = "Region,Province,District,Round,Measure")] requestfilter req)
        {
            Int16  Region;
            Int16  Province;
            Int16  District;
            Int16  Round;
            string Measure;

            Region   = req.Region;
            Province = req.Province;
            District = req.District;
            Round    = req.Round;
            Measure  = req.Measure;

            var data = _context.IndDistircts.Select
                           (x => new
            {
                Region       = x.Region,
                Province     = x.Province,
                District     = x.District,
                Year         = x.Year,
                Month        = x.Month,
                Round        = x.Round,
                ShorName     = x.ShortName,
                Indicator    = x.Indicator,
                Measure      = x.Measure,
                Numerator    = x.Numerator,
                Denominator  = x.Denominator,
                RegionId     = x.RegionId,
                ProvinceId   = x.ProvinceId,
                DistrictId   = x.DistrictId,
                RoundId      = x.RoundId,
                CampaignType = x.CampaignType,
                Form         = x.Form
            }
                           ).ToList();

            if (!Region.Equals(0) & !Province.Equals(0) & !District.Equals(0))
            {
                data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province) & m.DistrictId.Equals(District)).ToList();
            }

            else if (!Region.Equals(0) & !Province.Equals(0))
            {
                data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province)).ToList();
            }

            else if (!Region.Equals(0))
            {
                data = data.Where(m => m.RegionId.Equals(Region)).ToList();
            }


            if (!Round.Equals(0))
            {
                data = data.Where(m => m.RoundId.Equals(Round)).ToList();
            }


            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel2013;

            IWorkbook workbook;

            workbook = application.Workbooks.Create(3);
            IWorksheet sheet = workbook.Worksheets[0];

            if (!data.Any())
            {
                return(HttpNotFound());
            }

            sheet.ImportData(data, 1, 1, true);

            sheet.Name = "Data";
            IWorksheet pivotSheet = workbook.Worksheets[1];

            pivotSheet.Name = "Percentage";

            IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
            //Adding calculated fields to cache.
            PivotCacheFieldImpl Result  = (cash_data as PivotCacheImpl).CacheFields.AddNewField("Result", "Numerator/Denominator");
            PivotCacheFieldImpl Result2 = (cash_data as PivotCacheImpl).CacheFields.AddNewField("Result2", "Numerator*1");

            //Percentage indicator - Pivot Table (Percentage)
            #region "Percentage Pivot Table"
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);

            IPivotField field = pivotTable.Fields["Result"];
            pivotTable.DataFields.Add(field, "%", PivotSubtotalTypes.Sum);

            pivotTable.Fields["Region"].Axis       = PivotAxisTypes.Page;
            pivotTable.Fields["Province"].Axis     = PivotAxisTypes.Page;
            pivotTable.Fields["District"].Axis     = PivotAxisTypes.Page;
            pivotTable.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
            pivotTable.Fields["Round"].Axis        = PivotAxisTypes.Page;

            pivotTable.Fields["Year"].Axis    = PivotAxisTypes.Page;
            pivotTable.Fields["Month"].Axis   = PivotAxisTypes.Page;
            pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
            pivotTable.Fields["Form"].Axis    = PivotAxisTypes.Page;

            pivotTable.Fields["Indicator"].Axis = PivotAxisTypes.Row;


            IPivotField num   = pivotTable.Fields["Numerator"];
            IPivotField denom = pivotTable.Fields["Denominator"];
            if (Measure.Equals("1"))
            {
                pivotTable.Fields["Region"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text            = "Indicators comparison by region";
            }
            else if (Measure.Equals("2"))
            {
                pivotTable.Fields["Province"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text = "Indicators comparison by province";
            }
            else if (Measure.Equals("3"))
            {
                pivotTable.Fields["District"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text = "Indicators comparison by district";
            }
            else if (Measure.Equals("4"))
            {
                pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text          = "Indicators comparison by year";
            }
            else if (Measure.Equals("5"))
            {
                pivotTable.Fields["Year"].Axis  = PivotAxisTypes.Column;
                pivotTable.Fields["Month"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text           = "Indicators comparison by month";

                IPivotField pivotField = pivotTable.Fields["Month"];
                pivotField.Sort(new string[12] {
                    "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
                });
            }

            pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
            pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
            pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
            pivotSheet.Range["A3"].CellStyle.Font.Size   = 10f;
            pivotSheet.Range["A3"].CellStyle.Font.Bold   = true;
            pivotSheet.Range["A3"].CellStyle.Font.Italic = true;


            pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
            IPivotField pageField = pivotTable.Fields["Measure"];
            pageField.Items[0].Visible = false;


            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
            pivotTable.ColumnGrand  = false;
            pivotTable.RowGrand     = false;
            PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
            pivotOption.ShowGridDropZone = true;
            IPivotTableOptions option = pivotTable.Options;
            option.ErrorString         = "NA";
            option.ColumnHeaderCaption = "CompareBy";
            option.RowHeaderCaption    = "Indicator";


            #endregion

            //Number indicator - Pivot Table (Numeric)
            #region "Numeric Pivot Table"

            IWorksheet pivotSheet2 = workbook.Worksheets[2];

            pivotSheet2.Name = "Numeric";
            IPivotTable pivotTable2 = pivotSheet2.PivotTables.Add("PivotTable2", pivotSheet["A5"], cash_data);


            IPivotField field2 = pivotTable2.Fields["Result2"];
            pivotTable2.DataFields.Add(field2, "#", PivotSubtotalTypes.Sum);

            pivotTable2.Fields["Region"].Axis       = PivotAxisTypes.Page;
            pivotTable2.Fields["Province"].Axis     = PivotAxisTypes.Page;
            pivotTable2.Fields["District"].Axis     = PivotAxisTypes.Page;
            pivotTable2.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
            pivotTable2.Fields["Round"].Axis        = PivotAxisTypes.Page;
            pivotTable2.Fields["Year"].Axis         = PivotAxisTypes.Page;
            pivotTable2.Fields["Month"].Axis        = PivotAxisTypes.Page;

            pivotTable2.Fields["Form"].Axis = PivotAxisTypes.Page;

            pivotTable2.Fields["Indicator"].Axis = PivotAxisTypes.Row;


            IPivotField num2   = pivotTable2.Fields["Numerator"];
            IPivotField denom2 = pivotTable2.Fields["Denominator"];
            if (Measure.Equals("1"))
            {
                pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Column;
                pivotSheet2["A2"].Text            = "Indicators comparison by region";
            }
            else if (Measure.Equals("2"))
            {
                pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Column;
                pivotSheet2["A2"].Text = "Indicators comparison by province";
            }
            else if (Measure.Equals("3"))
            {
                pivotTable2.Fields["District"].Axis = PivotAxisTypes.Column;
                pivotSheet2["A2"].Text = "Indicators comparison by district";
            }
            else if (Measure.Equals("4"))
            {
                pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Column;
                pivotSheet2["A2"].Text          = "Indicators comparison by year";
            }
            else if (Measure.Equals("5"))
            {
                pivotTable2.Fields["Year"].Axis  = PivotAxisTypes.Column;
                pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Column;
                pivotSheet2["A2"].Text           = "Indicators comparison by month";
                IPivotField pivotField = pivotTable2.Fields["Month"];
                pivotField.Sort(new string[12] {
                    "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
                });
            }

            pivotSheet2.Range["A2"].CellStyle.Font.Size = 14f;
            pivotSheet2.Range["A2"].CellStyle.Font.Bold = true;
            pivotSheet2.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
            pivotSheet2.Range["A3"].CellStyle.Font.Size   = 10f;
            pivotSheet2.Range["A3"].CellStyle.Font.Bold   = true;
            pivotSheet2.Range["A3"].CellStyle.Font.Italic = true;

            pivotTable2.Fields["Measure"].Axis = PivotAxisTypes.Page;
            IPivotField pageField2 = pivotTable2.Fields["Measure"];
            pageField2.Items[1].Visible = false;

            pivotTable2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
            pivotTable2.ColumnGrand  = false;
            pivotTable2.RowGrand     = false;
            PivotTableOptions pivotOption2 = pivotTable2.Options as PivotTableOptions;
            pivotOption2.ShowGridDropZone = true;
            IPivotTableOptions option2 = pivotTable2.Options;
            option2.ErrorString         = "NA";
            option2.ColumnHeaderCaption = "CompareBy";
            option2.RowHeaderCaption    = "Indicator";

            pivotTable.Fields["Result"].NumberFormat   = "#.0%";
            pivotTable2.Fields["Result2"].NumberFormat = "#";

            (pivotTable.Options as PivotTableOptions).ShowGridDropZone  = true;
            (pivotTable2.Options as PivotTableOptions).ShowGridDropZone = true;
            (pivotTable as PivotTableImpl).Cache.IsRefreshOnLoad        = true;
            (pivotTable2 as PivotTableImpl).Cache.IsRefreshOnLoad       = true;

            pivotSheet2.Range["A5"].Activate();
            pivotSheet2.Activate();
            #endregion

            string ContentType = "Application/msexcel";
            string filename    = "Report_" + "donor" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";

            MemoryStream ms = new MemoryStream();
            workbook.SaveAs(ms);
            ms.Position = 0;
            workbook.Close();
            excelEngine.Dispose();
            return(File(ms, ContentType, filename));
        }
Exemplo n.º 14
0
        public ActionResult pivotNewInpatients([Bind(Include = "ProvCode,FacilityId,Year")] CreateReq req)
        {
            string ProvinceCode;
            int    FacilityId, Year;

            ProvinceCode = req.ProvCode;
            FacilityId   = req.FacilityId;
            Year         = req.Year;

            var data = _context.PvtNewInpatientCases.AsNoTracking().ToList();

            if (ProvinceCode != "" && FacilityId != 0 && Year != 0)
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Year.Equals(Year) && m.Province.Equals(ProvinceCode) && m.FacilityId.Equals(FacilityId)).ToList();
            }
            else if (ProvinceCode != "" & FacilityId != 0)
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode) && m.FacilityId.Equals(FacilityId)).ToList();
            }
            else if (ProvinceCode != "" && Year != 0)
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode) && m.Year.Equals(Year)).ToList();
            }
            else if (ProvinceCode != "")
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode)).ToList();
            }
            else if (FacilityId != 0 && Year != 0)
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.FacilityId.Equals(FacilityId) && m.Year.Equals(Year)).ToList();
            }
            else if (FacilityId != 0)
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.FacilityId.Equals(FacilityId)).ToList();
            }
            else if (Year != 0)
            {
                data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Year.Equals(Year)).ToList();
            }
            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel2013;

            IWorkbook workbook = application.Workbooks.Create(2);

            IWorksheet sheet = workbook.Worksheets[0];

            sheet.ImportData(data, 1, 1, true);
            sheet.Name = "Data";
            IWorksheet pivotSheet = workbook.Worksheets[1];

            pivotSheet.Name = "PivotTable";

            pivotSheet["A2"].Text = "HMIR New Inpatient Cases";
            pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
            pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
            pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
            pivotSheet.Range["A3"].CellStyle.Font.Size   = 10f;
            pivotSheet.Range["A3"].CellStyle.Font.Bold   = true;
            pivotSheet.Range["A3"].CellStyle.Font.Italic = true;

            if (sheet == null)
            {
                ModelState.AddModelError("Error", "There was error in selection or there is no data for the selected criteria");
                return(RedirectToAction("Index"));
            }

            IPivotCache cash_data  = workbook.PivotCaches.Add(sheet.UsedRange);
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);

            IPivotTableOptions options = pivotTable.Options;

            options.ShowFieldList = false;

            pivotTable.Fields["District"].Axis     = PivotAxisTypes.Page;
            pivotTable.Fields["Province"].Axis     = PivotAxisTypes.Page;
            pivotTable.Fields["FacilityId"].Axis   = PivotAxisTypes.Page;
            pivotTable.Fields["FacilityName"].Axis = PivotAxisTypes.Page;
            pivotTable.Fields["FacilityType"].Axis = PivotAxisTypes.Page;
            pivotTable.Fields["Year"].Axis         = PivotAxisTypes.Page;
            pivotTable.Fields["Month"].Axis        = PivotAxisTypes.Page;
            pivotTable.Fields["ElementName"].Axis  = PivotAxisTypes.Row;

            IPivotField FemaleOver5  = pivotTable.Fields["FemaleOver5"];
            IPivotField FemaleUnder5 = pivotTable.Fields["FemaleUnder5"];
            IPivotField MaleOver5    = pivotTable.Fields["MaleOver5"];
            IPivotField MaleUnder5   = pivotTable.Fields["MaleUnder5"];
            IPivotField ReferreddIn  = pivotTable.Fields["ReferredIn"];
            IPivotField ReferredOut  = pivotTable.Fields["ReferredOut"];
            IPivotField Deaths       = pivotTable.Fields["Deaths"];
            IPivotField Total        = pivotTable.Fields["Total"];

            pivotTable.DataFields.Add(FemaleOver5, "FO5", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(FemaleUnder5, "FU5", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(MaleOver5, "MO5", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(MaleUnder5, "MU5", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(ReferreddIn, "ReferIns", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(ReferredOut, "ReferOuts", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(Deaths, "Deaths", PivotSubtotalTypes.Sum);
            pivotTable.DataFields.Add(Total, "Total", PivotSubtotalTypes.Sum);

            IPivotTableOptions option = pivotTable.Options;

            option.ErrorString      = "X";
            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark20;
            pivotSheet.Activate();

            string ContentType = "Application/msexcel";
            string filename    = "HMIR_" + "NewInpatientCases" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";

            MemoryStream ms = new MemoryStream();

            workbook.SaveAs(ms);
            ms.Position = 0;
            workbook.Close();
            excelEngine.Dispose();
            return(File(ms, ContentType, filename));
        }
Exemplo n.º 15
0
        public ActionResult CreateChart()
        {
            var data = _context.lqasLust.Select
                           (x => new
            {
                Region    = x.Region,
                Province  = x.Province,
                District  = x.District,
                Year      = x.Year,
                Month     = x.Month,
                Round     = x.Round,
                MonthName = x.MonthName,
                Comments  = x.Comments,
                LotNo     = x.LotNo
            }
                           ).Where(m => m.Month < 5).ToList();


            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel2013;

            IWorkbook templateWorkbook;

            templateWorkbook = application.Workbooks.Open(Server.MapPath("/App_Data/book1.xlsx"));

            IWorksheet sheet = templateWorkbook.Worksheets[0];

            IWorkbook workbook;

            workbook = application.Workbooks.Create(3);
            IWorksheet worksheet = workbook.Worksheets[0];

            if (!data.Any())
            {
                return(HttpNotFound());
            }

            sheet.ImportData(data, 1, 1, true);

            sheet.Name = "Data";
            IWorksheet pivotSheet = templateWorkbook.Worksheets[1];

            pivotSheet.Name = "Pivot";

            IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);

            //Percentage indicator - Pivot Table (Percentage)
            #region "Percentage Pivot Table"


            IWorksheet  pivotTableSheet = templateWorkbook.Worksheets[1];
            IPivotTable pivotTable      = pivotTableSheet.PivotTables[0];
            IPivotField pivotField      = pivotTable.Fields["MonthName"];
            pivotField.Sort(new string[12] {
                "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
            });


            IChartShape pivotChartSheet = templateWorkbook.Worksheets[2].Charts[0];
            //Set the Pivot source for the Pivot Chart.
            pivotChartSheet.PivotSource = pivotTable;

            //Set the Pivot Chart Type.
            pivotChartSheet.PivotChartType = ExcelChartType.Column_Stacked_100;


            pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.Overlap  = 100;
            pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.GapWidth = 10;


            pivotChartSheet.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
            pivotChartSheet.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
            pivotChartSheet.Series[2].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;


            pivotChartSheet.Series[0].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.LightGreen;
            pivotChartSheet.Series[1].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Green;
            pivotChartSheet.Series[2].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Red;


            pivotChartSheet.TopRow      = 2;
            pivotChartSheet.BottomRow   = 30;
            pivotChartSheet.LeftColumn  = 1;
            pivotChartSheet.RightColumn = 20;

            pivotChartSheet.ChartTitle          = "This is a sample chart";
            pivotChartSheet.ShowAllFieldButtons = false;

            (pivotTable as PivotTableImpl).Cache.SourceRange = sheet.UsedRange;

            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
            pivotTable.ColumnGrand  = false;
            pivotTable.RowGrand     = false;
            PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
            pivotOption.ShowGridDropZone = true;
            IPivotTableOptions option = pivotTable.Options;
            option.ErrorString = "NA";

            #endregion


            (pivotTable.Options as PivotTableOptions).ShowGridDropZone = true;
            (pivotTable as PivotTableImpl).Cache.IsRefreshOnLoad       = true;

            IWorksheet pivotChart = templateWorkbook.Worksheets[2];
            pivotChart.Name = "Chart";

            pivotChart.Activate();

            string ContentType = "Application/msexcel";
            string filename    = "Report_" + "my" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";

            MemoryStream ms = new MemoryStream();
            templateWorkbook.SaveAs(ms);
            ms.Position = 0;
            workbook.Close();
            excelEngine.Dispose();
            return(File(ms, ContentType, filename));
        }
Exemplo n.º 16
0
        public ActionResult DistInd([Bind(Include = "Region,Province,District,Round,Measure,Filter")] requestfilter req)
        {
            Int16  Region;
            Int16  Province;
            Int16  District;
            Int16  Round;
            string Measure;
            string Filter;

            Region   = req.Region;
            Province = req.Province;
            District = req.District;
            Round    = req.Round;
            Measure  = req.Measure;
            Filter   = req.Filter;

            var data = _context.IndDistircts.Select
                           (x => new
            {
                Region       = x.Region,
                Province     = x.Province,
                District     = x.District,
                Year         = x.Year,
                Month        = x.Month,
                Round        = x.Round,
                ShorName     = x.ShortName,
                Indicator    = x.Indicator,
                Measure      = x.Measure,
                Numerator    = x.Numerator,
                Denominator  = x.Denominator,
                RegionId     = x.RegionId,
                ProvinceId   = x.ProvinceId,
                DistrictId   = x.DistrictId,
                RoundId      = x.RoundId,
                CampaignType = x.CampaignType,
                Form         = x.Form
            }
                           ).ToList() /*.Where(m=>m.Measure.Equals(Measure) & m.Denominator>0)*/;

            if (!Region.Equals(0) & !Province.Equals(0) & !District.Equals(0))
            {
                data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province) & m.DistrictId.Equals(District)).ToList();
            }

            else if (!Region.Equals(0) & !Province.Equals(0))
            {
                data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province)).ToList();
            }

            else if (!Region.Equals(0))
            {
                data = data.Where(m => m.RegionId.Equals(Region)).ToList();
            }


            if (!Round.Equals(0))
            {
                data = data.Where(m => m.RoundId.Equals(Round)).ToList();
            }


            ExcelEngine excelEngine = new ExcelEngine();

            IApplication application = excelEngine.Excel;

            application.DefaultVersion = ExcelVersion.Excel2013;

            IWorkbook workbook;

            workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];

            if (!data.Any())
            {
                return(HttpNotFound());
            }

            sheet.ImportData(data, 1, 1, true);

            sheet.Name = "Data";
            IWorksheet pivotSheet = workbook.Worksheets[1];

            pivotSheet.Name = "Result";


            IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
            //Percentage indicator
            IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);

            //IPivotTableOptions options = pivotTable.Options;

            pivotTable.Fields["Region"].Axis       = PivotAxisTypes.Page;
            pivotTable.Fields["Province"].Axis     = PivotAxisTypes.Page;
            pivotTable.Fields["District"].Axis     = PivotAxisTypes.Page;
            pivotTable.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
            pivotTable.Fields["Round"].Axis        = PivotAxisTypes.Page;

            pivotTable.Fields["Year"].Axis  = PivotAxisTypes.Page;
            pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;

            pivotTable.Fields["Form"].Axis = PivotAxisTypes.Page;

            pivotTable.Fields["Indicator"].Axis = PivotAxisTypes.Row;


            IPivotField num   = pivotTable.Fields["Numerator"];
            IPivotField denom = pivotTable.Fields["Denominator"];

            if (Measure.Equals("1"))
            {
                pivotTable.Fields["Region"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text            = "Indicators comparison by region";
            }
            else if (Measure.Equals("2"))
            {
                pivotTable.Fields["Province"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text = "Indicators comparison by province";
            }
            else if (Measure.Equals("3"))
            {
                pivotTable.Fields["District"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text = "Indicators comparison by district";
            }
            else if (Measure.Equals("4"))
            {
                pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text          = "Indicators comparison by year";
            }
            else if (Measure.Equals("5"))
            {
                pivotTable.Fields["Year"].Axis  = PivotAxisTypes.Column;
                pivotTable.Fields["Month"].Axis = PivotAxisTypes.Column;
                pivotSheet["A2"].Text           = "Indicators comparison by month";

                IPivotField pivotField = pivotTable.Fields["Month"];
                pivotField.Sort(new string[1] {
                    pivotField.Items[1].Text
                });
            }

            pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
            pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
            pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
            pivotSheet.Range["A3"].CellStyle.Font.Size   = 10f;
            pivotSheet.Range["A3"].CellStyle.Font.Bold   = true;
            pivotSheet.Range["A3"].CellStyle.Font.Italic = true;


            pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
            IPivotField pageField = pivotTable.Fields["Measure"];

            pageField.Items[0].Visible = false;

            if (Filter == "%")
            {
                IPivotField Result = pivotTable.CalculatedFields.Add("Result", "Numerator/Denominator");
                Result.Name         = " Result";
                Result.NumberFormat = "#.0%";
            }
            else if (Filter == "#")
            {
                IPivotField Result = pivotTable.CalculatedFields.Add("Result", "Numerator*1");
                Result.Name         = " Result";
                Result.NumberFormat = "#";
            }

            //IPivotField pivotField = pivotTable.Fields[0];
            //pivotField.Sort(new string[1] { pivotField.Items[4].Text });

            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
            pivotTable.ColumnGrand  = false;
            pivotTable.RowGrand     = false;
            PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;

            pivotOption.ShowGridDropZone = true;
            IPivotTableOptions option = pivotTable.Options;

            option.ErrorString         = "NA";
            option.ColumnHeaderCaption = "CompareBy";
            option.RowHeaderCaption    = "Indicator";
            pivotSheet.Activate();

            //else if (Filter == "#")
            //{
            //    //Number indicator
            //    IWorksheet pivotSheet2 = workbook.Worksheets[1];

            //    pivotSheet2.Name = "Numeric";
            //    IPivotTable pivotTable2 = pivotSheet2.PivotTables.Add("PivotTable2", pivotSheet["A5"], cash_data);

            //    //IPivotTableOptions options2 = pivotTable2.Options;
            //    //options2.ShowFieldList = true;

            //    pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Page;
            //    pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
            //    pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
            //    pivotTable2.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
            //    pivotTable2.Fields["Round"].Axis = PivotAxisTypes.Page;

            //    pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
            //    pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;

            //    pivotTable2.Fields["Form"].Axis = PivotAxisTypes.Page;

            //    pivotTable2.Fields["Indicator"].Axis = PivotAxisTypes.Row;


            //    IPivotField num2 = pivotTable2.Fields["Numerator"];
            //    IPivotField denom2 = pivotTable2.Fields["Denominator"];
            //    if (Measure.Equals("1"))
            //    {
            //        pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Column;
            //        pivotSheet2["A2"].Text = "Indicators comparison by region";
            //    }
            //    else if (Measure.Equals("2"))
            //    {
            //        pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
            //        pivotSheet2["A2"].Text = "Indicators comparison by province";
            //    }
            //    else if (Measure.Equals("3"))
            //    {
            //        pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
            //        pivotSheet2["A2"].Text = "Indicators comparison by district";
            //    }
            //    else if (Measure.Equals("4"))
            //    {
            //        pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
            //        pivotSheet2["A2"].Text = "Indicators comparison by year";
            //    }
            //    else if (Measure.Equals("5"))
            //    {
            //        pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
            //        pivotSheet2["A2"].Text = "Indicators comparison by month";
            //    }

            //    pivotSheet2.Range["A2"].CellStyle.Font.Size = 14f;
            //    pivotSheet2.Range["A2"].CellStyle.Font.Bold = true;
            //    pivotSheet2.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString(); ;
            //    pivotSheet2.Range["A3"].CellStyle.Font.Size = 10f;
            //    pivotSheet2.Range["A3"].CellStyle.Font.Bold = true;
            //    pivotSheet2.Range["A3"].CellStyle.Font.Italic = true;

            //    IPivotField result2 = pivotTable2.CalculatedFields.Add("Result2", "Numerator*1");
            //    result2.Name = " #";
            //    result2.NumberFormat = "#";

            //    pivotTable2.Fields["Measure"].Axis = PivotAxisTypes.Page;
            //    IPivotField pageField2 = pivotTable2.Fields["Measure"];
            //    pageField2.Items[1].Visible = false;

            //    pivotTable2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
            //    pivotTable2.ColumnGrand = false;
            //    pivotTable2.RowGrand = false;
            //    PivotTableOptions pivotOption2 = pivotTable2.Options as PivotTableOptions;
            //    pivotOption2.ShowGridDropZone = true;
            //    IPivotTableOptions option2 = pivotTable2.Options;
            //    option2.ErrorString = "NA";
            //    option2.ColumnHeaderCaption = "CompareBy";
            //    option2.RowHeaderCaption = "Indicator";

            //    pivotSheet2.Range["A5"].Activate();
            //    pivotSheet2.Activate();
            //}
            string ContentType = "Application/msexcel";
            string filename    = "NEOC_" + "Indicators" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";

            MemoryStream ms = new MemoryStream();

            workbook.SaveAs(ms);
            ms.Position = 0;
            workbook.Close();
            excelEngine.Dispose();
            return(File(ms, ContentType, filename));
        }