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));
}
}
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;
}
}
}
