private void InitData() { //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; //Add sheet and get index GridWorksheet sheet = GridWeb1.WorkSheets.Add("PivotTable Report"); GridCells cells = GridWeb1.WorkSheets[0].Cells; int id=sheet.PivotTables.Add(sourceSheet, sourceRange, "A1", "PivotTable Report"); //Apply formatting GridWeb1.WorkSheets.DefaultFontName = "Arial"; GridWeb1.WorkSheets.DefaultFontSize = new System.Web.UI.WebControls.FontUnit(10); //Clears all list box lbxFields.Items.Clear(); lbxRowFields.Items.Clear(); lbxColumnFields.Items.Clear(); lbxDataFields.Items.Clear(); // GridPivotTable pivotTable=sheet.PivotTables[id]; //Adds PivotFields to lbxFields box. // GridPivotFieldCollection fields = pivotTable.Fields(GridPivotFieldType.Column); for (int i = 0; i < 6; i++) { lbxFields.Items.Add(new ListItem(cells[0, i].StringValue, cells[0, i].StringValue)); } }
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; }
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; }
private void InitData() { //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; //Add sheet and get index GridWorksheet sheet = GridWeb1.WorkSheets.Add("PivotTable Report"); GridCells cells = GridWeb1.WorkSheets[0].Cells; int id = sheet.PivotTables.Add(sourceSheet, sourceRange, "A1", "PivotTable Report"); //Apply formatting GridWeb1.WorkSheets.DefaultFontName = "Arial"; GridWeb1.WorkSheets.DefaultFontSize = new System.Web.UI.WebControls.FontUnit(10); //Clears all list box lbxFields.Items.Clear(); lbxRowFields.Items.Clear(); lbxColumnFields.Items.Clear(); lbxDataFields.Items.Clear(); // GridPivotTable pivotTable=sheet.PivotTables[id]; //Adds PivotFields to lbxFields box. // GridPivotFieldCollection fields = pivotTable.Fields(GridPivotFieldType.Column); for (int i = 0; i < 6; i++) { lbxFields.Items.Add(new ListItem(cells[0, i].StringValue, cells[0, i].StringValue)); } }
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() { // Create path to xls file string path = (this.Master as Site).GetDataDir(); // Set filename string fileName = path + "\\Miscellaneous\\PivotTable.xls"; // Imports from a 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 sheet and get index 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"); // Apply formatting GridWeb1.WorkSheets.DefaultFontName = "Arial"; GridWeb1.WorkSheets.DefaultFontSize = new System.Web.UI.WebControls.FontUnit(10); //Clears all list box lbxFields.Items.Clear(); lbxRowFields.Items.Clear(); lbxColumnFields.Items.Clear(); lbxDataFields.Items.Clear(); //Adds PivotFields to lbxFields box. for (int i = 0; i < 6; i++) { lbxFields.Items.Add(new ListItem(cells[0, i].StringValue, cells[0, i].StringValue)); } }
//Handles creating report from WebWorksheet protected void btnWorksheet_Click(object sender, EventArgs e) { 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["Form WebWorksheet"]; if (sheet == null) { sheet = GridWeb1.WorkSheets.Add("Form WebWorksheet"); GridCells cells = GridWeb1.WorkSheets[0].Cells; 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; }
// 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; }
protected void GridWeb1_CustomCommand(object sender, string command) { // Groups Rows or Ungroup Rows. if (GridWeb1.ActiveSheetIndex == 0) { switch (command) { case "GROUP": if (GridWeb1.SelectCells != null && GridWeb1.SelectCells.Count > 0) { // Get Cell Selected CellArea GridCellArea SelectedCells = (GridCellArea)GridWeb1.SelectCells[0]; // Group rows from starting cell to ending cell //GridWeb1.WebWorksheets[GridWeb1.ActiveSheetIndex].GroupRows(SelectedCells.StartRow, SelectedCells.EndRow); // Accessing the reference of the worksheet that is currently active GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex]; // Group the rows sheet.Cells.GroupRows(1, 9); } break; case "UNGROUP": if (GridWeb1.SelectCells != null && GridWeb1.SelectCells.Count > 0) { // Get Cell Selected CellArea GridCellArea SelectedCells = (GridCellArea)GridWeb1.SelectCells[0]; // Group rows from starting cell to ending cell GridWeb1.WebWorksheets[GridWeb1.ActiveSheetIndex].UngroupRows(SelectedCells.StartRow, SelectedCells.EndRow);; } break; } } }