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;
}
protected void ddlSummary_SelectedIndexChanged(object sender, System.EventArgs e)
{
//Create pivot table object against selected dropdownlist value
GridWorksheet sheet = GridWeb1.WorkSheets["PivotTable Report"];
GridPivotTable pivotTable = sheet.PivotTables[0];
//Bind pivot table
pivotTable.Fields(GridPivotFieldType.Data)[0].Function =
(GridPivotFieldFunction)TypeDescriptor.GetConverter
(typeof(GridPivotFieldFunction)).ConvertFrom
(ddlSummary.SelectedItem.Value);
//Paints PivotTable report
pivotTable.CalculateData();
}
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;
}
//Sets row or column field position
protected void btnSubmit3_Click(object sender, EventArgs e)
{
//initialization
InitData();
GridWorksheet sheet = GridWeb1.WorkSheets["PivotTable Report"];
GridPivotTable pivotTable = sheet.PivotTables[0];
//Sets feild Position property determining the location of the field
pivotTable.Fields("Employee").Position = int.Parse(TextBox3.Text.Trim());
pivotTable.Fields("Country").Position = int.Parse(TextBox4.Text.Trim());
//Paints PivotTable report
pivotTable.CalculateData();
}
// Renames field label to show
protected void btnSubmit4_Click(object sender, EventArgs e)
{
// Initialize Data
InitData();
GridWorksheet sheet = GridWeb1.WorkSheets["PivotTable Report"];
GridPivotTable pivotTable = sheet.PivotTables[0];
// Sets field Caption property to be show in the report
pivotTable.Fields("Employee").DisplayName = txtEmployee.Text.Trim();
pivotTable.Fields("Product").DisplayName = txtCountry.Text.Trim();
// Paints PivotTable report
pivotTable.CalculateData();
}
// Sets row or column order
protected void btnSubmit2_Click(object sender, EventArgs e)
{
// Initialize Data
InitData();
GridWorksheet sheet = GridWeb1.WorkSheets["PivotTable Report"];
GridPivotTable pivotTable = sheet.PivotTables[0];
// Sets field SortOrder property determining the order of the field item
pivotTable.Fields("Employee").IsAscendSort = (ddlEmployee.SelectedItem.Value.Equals("Asc"));
pivotTable.Fields("Country").IsAscendSort = (ddlCountry.SelectedItem.Value.Equals("Asc"));
// Paints PivotTable report
pivotTable.CalculateData();
}
//Hides some row or column
protected void btnSubmit_Click(object sender, EventArgs e)
{
//initialization
InitData();
GridWorksheet sheet = GridWeb1.WorkSheets["PivotTable Report"];
GridPivotTable pivotTable = sheet.PivotTables[0];
//Sets field item visible property indicating whether the item is visible
pivotTable.Fields("Employee").HideItemByName("David", !CheckBox1.Checked);
pivotTable.Fields("Product").HideItemByName("Maxilaku", !CheckBox2.Checked);
pivotTable.Fields("Continent").HideItemByName("Europe", !CheckBox3.Checked);
pivotTable.Fields("Country").HideItemByName("China", !CheckBox4.Checked);
//Paints PivotTable report
pivotTable.CalculateData();
}
// 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;
}
