protected void btnCreateReport_Click(object sender, EventArgs e) { // Validates if (lbxRowFields.Items.Count < 1) { Util.ShowMessage(this, "Please Set row field"); return; } if (lbxColumnFields.Items.Count < 1) { Util.ShowMessage(this, "Please Set column field"); return; } if (lbxDataFields.Items.Count < 1) { Util.ShowMessage(this, "Please Set data field"); return; } // Sets PivotTable report row fields . GridPivotTable pivotTable = GridWeb1.WorkSheets[1].PivotTables[0]; pivotTable.ClearAllFields(); for (int i = 0; i < lbxRowFields.Items.Count; i++) { pivotTable.AddFieldToArea(GridPivotFieldType.Row, lbxRowFields.Items[i].Text); } // Set PivotTable report column fields for (int i = 0; i < lbxColumnFields.Items.Count; i++) { pivotTable.AddFieldToArea(GridPivotFieldType.Column, lbxColumnFields.Items[i].Text); } // Set PivotTable data fields for (int i = 0; i < lbxDataFields.Items.Count; i++) { int j = pivotTable.AddFieldToArea(GridPivotFieldType.Data, lbxDataFields.Items[i].Text); if (lbxDataFields.Items[i].Text == "Sale") { pivotTable.Fields(GridPivotFieldType.Data)[j].Function = GridPivotFieldFunction.Sum; } } // Paints PivotTable report pivotTable.CalculateData(); // Set Activesheet GridWeb1.ActiveSheetIndex = GridWeb1.WorkSheets["PivotTable Report"].Index; }
private void InitData() { if (!(!IsPostBack && !GridWeb1.IsPostBack)) { return; } // Create path to xls file string path = (this.Master as Site).GetDataDir(); // Set filename string fileName = path + "\\Miscellaneous\\PivotTable.xls"; // Imports from an excel file. GridWeb1.ImportExcelFile(fileName); // Extract cells from source sheet GridWorksheet sourceSheet = GridWeb1.WorkSheets[0]; Aspose.Cells.GridWeb.Data.GridCellArea sourceRange = new GridCellArea(); sourceRange.StartRow = 0; sourceRange.StartColumn = 0; sourceRange.EndRow = 29; sourceRange.EndColumn = 5; // Add new sheet GridWorksheet sheet = GridWeb1.WorkSheets.Add("PivotTable Report"); GridCells cells = GridWeb1.WorkSheets[0].Cells; // Add Pivot Table int id = sheet.PivotTables.Add(sourceSheet, sourceRange, "A1", "PivotTable Report"); GridPivotTable pivotTable = sheet.PivotTables[id]; // Apply formatting GridWeb1.DefaultFontName = "Arial"; GridWeb1.DefaultFontSize = new System.Web.UI.WebControls.FontUnit(10); pivotTable.AddFieldToArea(GridPivotFieldType.Row, 0); pivotTable.AddFieldToArea(GridPivotFieldType.Row, 2); pivotTable.AddFieldToArea(GridPivotFieldType.Column, 3); pivotTable.AddFieldToArea(GridPivotFieldType.Column, 4); pivotTable.AddFieldToArea(GridPivotFieldType.Data, 5); pivotTable.Fields(GridPivotFieldType.Data)[0].Function = GridPivotFieldFunction.Sum; // Paints PivotTable report pivotTable.CalculateData(); GridWeb1.ActiveSheetIndex = sheet.Index; }
private void InitData() { if (!(!IsPostBack && !GridWeb1.IsPostBack)) { return; } //Create path to xls file string path = Server.MapPath("~"); path = path.Substring(0, path.LastIndexOf("\\")); string fileName = path + "\\File\\PivotTable.xls"; // Imports from a excel file. GridWeb1.ImportExcelFile(fileName); GridWorksheet sourceSheet = GridWeb1.WorkSheets[0]; Aspose.Cells.GridWeb.Data.GridCellArea sourceRange = new GridCellArea(); sourceRange.StartRow = 0; sourceRange.StartColumn = 0; sourceRange.EndRow = 29; sourceRange.EndColumn = 5; GridWorksheet sheet = GridWeb1.WorkSheets.Add("PivotTable Report"); GridCells cells = GridWeb1.WorkSheets[0].Cells; int id = sheet.PivotTables.Add(sourceSheet, sourceRange, "A1", "PivotTable Report"); GridPivotTable pivotTable = sheet.PivotTables[id]; //Apply formatting GridWeb1.DefaultFontName = "Arial"; GridWeb1.DefaultFontSize = new System.Web.UI.WebControls.FontUnit(10); pivotTable.AddFieldToArea(GridPivotFieldType.Row, 0); pivotTable.AddFieldToArea(GridPivotFieldType.Row, 2); pivotTable.AddFieldToArea(GridPivotFieldType.Column, 3); pivotTable.AddFieldToArea(GridPivotFieldType.Column, 4); pivotTable.AddFieldToArea(GridPivotFieldType.Data, 5); pivotTable.Fields(GridPivotFieldType.Data)[0].Function = GridPivotFieldFunction.Sum; //Paints PivotTable report pivotTable.CalculateData(); GridWeb1.ActiveSheetIndex = sheet.Index; }
// Handles creating report from WebWorksheet protected void btnWorksheet_Click(object sender, EventArgs e) { // Extract cells from source sheet GridWorksheet sourceSheet = GridWeb1.WorkSheets[0]; Aspose.Cells.GridWeb.Data.GridCellArea sourceRange = new GridCellArea(); sourceRange.StartRow = 0; sourceRange.StartColumn = 0; sourceRange.EndRow = 29; sourceRange.EndColumn = 5; GridWorksheet sheet = GridWeb1.WorkSheets["From WebWorksheet"]; if (sheet == null) { // Add worksheet sheet = GridWeb1.WorkSheets.Add("From WebWorksheet"); GridCells cells = GridWeb1.WorkSheets[0].Cells; // Add pivot table int id = sheet.PivotTables.Add(sourceSheet, sourceRange, "A1", "Form WebWorksheet"); GridPivotTable pivotTable = sheet.PivotTables[id]; // Apply formatting GridWeb1.DefaultFontName = "Arial"; GridWeb1.DefaultFontSize = new System.Web.UI.WebControls.FontUnit(10); pivotTable.AddFieldToArea(GridPivotFieldType.Row, 0); pivotTable.AddFieldToArea(GridPivotFieldType.Row, 2); pivotTable.AddFieldToArea(GridPivotFieldType.Column, 3); pivotTable.AddFieldToArea(GridPivotFieldType.Column, 4); pivotTable.AddFieldToArea(GridPivotFieldType.Data, 5); pivotTable.Fields(GridPivotFieldType.Data)[0].Function = GridPivotFieldFunction.Sum; // Paints PivotTable report pivotTable.CalculateData(); } GridWeb1.ActiveSheetIndex = sheet.Index; }