protected void btnAddFormula_Click(object sender, EventArgs e)
{
// Clear GridWeb and add a new worksheet
GridWeb1.WorkSheets.Clear();
GridWeb1.WorkSheets.Add();
// ExStart:AddFormulas
// Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Putting some values to cells
sheet.Cells["A1"].PutValue("1st Value");
sheet.Cells["A2"].PutValue("2nd Value");
sheet.Cells["A3"].PutValue("Sum");
sheet.Cells["B1"].PutValue(125.56);
sheet.Cells["B2"].PutValue(23.93);
// Adding a simple formula to "B3" cell
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";
// ExEnd:AddFormulas
// ExStart:CalculateFormulas
// Calculating all formulas added in worksheets
GridWeb1.WorkSheets.CalculateFormula();
// ExEnd:CalculateFormulas
}
// Initialize new bind row.
private void GridWeb1_InitializeNewBindRow(GridWorksheet sender, object bindObject)
{
// Handles the initialize new bind row event.
MyCustomRecord rec = (MyCustomRecord)bindObject;
rec.DateField1 = DateTime.Now;
}
protected void btnAddListValidation_Click(object sender, EventArgs e)
{
// ExStart:AddListValidation
// Accessing the cells collection of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Access "B1" cell and add some text
GridCell cell = sheet.Cells[0, 1];
cell.PutValue("Select Course:");
// Accessing "C1" cell
cell = sheet.Cells[0, 2];
// Creating List validation for the "C1" cell
var validation = cell.CreateValidation(GridValidationType.List, true);
// Adding values to List validation
var values = new System.Collections.Specialized.StringCollection();
values.Add("Fortran");
values.Add("Pascal");
values.Add("C++");
values.Add("Visual Basic");
values.Add("Java");
values.Add("C#");
validation.ValueList = values;
// ExEnd:AddListValidation
}
protected void btnAddComplexFormula_Click(object sender, EventArgs e)
{
// Clear GridWeb
GridWeb1.WorkSheets.Clear();
// Add worksheets
GridWorksheet sheet1 = GridWeb1.WorkSheets.Add("Sheet1");
GridWorksheet sheet2 = GridWeb1.WorkSheets.Add("Sheet2");
// Putting some values to cells
sheet1.Cells["A1"].PutValue(10);
sheet1.Cells["A2"].PutValue(20);
sheet1.Cells["A3"].PutValue(30);
sheet1.Cells["A4"].PutValue(40);
sheet1.Cells["A5"].PutValue(50);
sheet1.Cells["B1"].PutValue(1);
sheet1.Cells["B2"].PutValue(2);
sheet1.Cells["B3"].PutValue(3);
sheet1.Cells["B4"].PutValue(4);
sheet1.Cells["B5"].PutValue(5);
sheet2.Cells["A1"].PutValue("Value for reference : ");
sheet2.Cells["B1"].PutValue(100);
sheet1.Cells["A6"].PutValue("Result");
// ExStart:AddComplexFormulas
// Adding a bit complex formula to "A1" cell
sheet1.Cells["B6"].Formula = "=(SUM(A1:A5)/AVERAGE(B1:B5))-Sheet2!B1";
// ExEnd:AddComplexFormulas
// Calculating all formulas added in worksheets
GridWeb1.WorkSheets.CalculateFormula();
}
protected void btnAddDropDownListValidation_Click(object sender, EventArgs e)
{
// ExStart:AddDropDownListValidation
// Accessing the cells collection of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Access "B1" cell and add some text
GridCell cell = sheet.Cells[0, 1];
cell.PutValue("Select Degree:");
// Accessing "C1" cell
cell = sheet.Cells[0, 2];
// Creating DropDownList validation for the "C1" cell
var validation = cell.CreateValidation(GridValidationType.DropDownList, true);
// Adding values to DropDownList validation
var values = new System.Collections.Specialized.StringCollection();
values.Add("Bachelor");
values.Add("Master");
values.Add("Doctor");
validation.ValueList = values;
// ExEnd:AddDropDownListValidation
}
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack == false && this.GridWeb1.IsPostBack == false)
{
//Access the first worksheet
GridWorksheet gridSheet = GridWeb1.WorkSheets[0];
//Input data into the cells of the first worksheet.
gridSheet.Cells["A1"].PutValue("Product1");
gridSheet.Cells["A2"].PutValue("Product2");
gridSheet.Cells["A3"].PutValue("Product3");
gridSheet.Cells["A4"].PutValue("Product4");
gridSheet.Cells["B1"].PutValue(100);
gridSheet.Cells["B2"].PutValue(200);
gridSheet.Cells["B3"].PutValue(300);
gridSheet.Cells["B4"].PutValue(400);
//Set the caption of the first two columns.
gridSheet.SetColumnCaption(0, "Product Name");
gridSheet.SetColumnCaption(1, "Price");
//Set the column width of the first column.
gridSheet.Cells.SetColumnWidth(0, 20);
//Set the second column header's tip.
gridSheet.SetColumnHeaderToolTip(1, "Unit Price of Products");
//Set the last cell so that scroll bars appear
gridSheet.Cells["J30"].PutValue("");
}
}
private void AddTextHyperlinks()
{
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Adds a text hyperlink that gos to Aspose site and opens in new window
int linkIndex = sheet.Hyperlinks.Add("B1", "http://www.aspose.com");
GridHyperlink link1 = sheet.Hyperlinks[linkIndex];
link1.Target = "_blank";
// Setting text of the hyperlink
link1.TextToDisplay = "Aspose";
// Setting tool tip of the hyperlink
link1.ScreenTip = "Open Aspose Web Site in new window";
// Adding hyperlink to the worksheet to open in parent window
linkIndex = sheet.Hyperlinks.Add("B2", "http://www.aspose.com/docs/display/cellsnet/Aspose.Cells.GridWeb");
GridHyperlink link2 = sheet.Hyperlinks[linkIndex];
link2.Target = "_parent";
// Setting text of the hyperlink
link2.TextToDisplay = "Aspose.Grid Docs";
// Setting tool tip of the hyperlink
link2.ScreenTip = "Open Aspose.Grid Docs in parent window";
}
protected void chkSelectedCells_CheckedChanged(object sender, EventArgs e)
{
chkAllCells.Checked = false;
if (chkSelectedCells.Checked)
{
// ExStart:MakeSelectedCellsEditable
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Setting all cells of the worksheet to Readonly first
sheet.SetAllCellsReadonly();
// Finally, Setting selected cells of the worksheet to Editable
sheet.SetEditableRange(3, 2, 4, 1);
// ExEnd:MakeSelectedCellsEditable
Label1.Text = "4 rows and 1 column are editable starting from row 4 and column 3";
}
else
{
// ExStart:MakeSelectedCellsReadOnly
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Setting all cells of the worksheet to Editable first
sheet.SetAllCellsEditable();
// Finally, Setting selected cells of the worksheet to Readonly
sheet.SetReadonlyRange(3, 2, 4, 1);
// ExEnd:MakeSelectedCellsReadOnly
Label1.Text = "4 rows and 1 column are readonly starting from row 4 and column 3";
}
}
private void AddImageHyPerlinks()
{
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Adding hyperlink to the worksheet
int linkIndex = sheet.Hyperlinks.Add("B5", "http://www.aspose.com");
GridHyperlink link1 = sheet.Hyperlinks[linkIndex];
link1.Target = "_blank";
// Setting URL of the image that will be displayed as hyperlink
link1.ImageURL = "../Images/Aspose.Banner.gif";
// Setting tool tip of the hyperlink
link1.ScreenTip = "Open Aspose Web Site in new window";
// Resize the row to display image nicely
sheet.Cells.SetRowHeight(4, 40);
// Adding hyperlink to the worksheet
linkIndex = sheet.Hyperlinks.Add("B6", "http://www.aspose.com/docs/display/cellsnet/Aspose.Cells.GridWeb");
GridHyperlink link2 = sheet.Hyperlinks[linkIndex];
link2.Target = "_blank";
// Setting URL of the image that will be displayed as hyperlink
link2.ImageURL = "../Images/Aspose.Grid.gif";
// Setting tool tip of the hyperlink
link2.ScreenTip = "Open Aspose.Grid Docs in new window";
}
protected void chkAllCells_CheckedChanged(object sender, EventArgs e)
{
chkSelectedCells.Checked = false;
if (chkAllCells.Checked)
{
// ExStart:MakeAllCellsEditable
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Setting all cells of the worksheet to Editable
sheet.SetAllCellsEditable();
// ExEnd:MakeAllCellsEditable
Label1.Text = "All cells are editable now.";
}
else
{
// ExStart:MakeAllCellsReadOnly
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Setting all cells of the worksheet to Readonly
sheet.SetAllCellsReadonly();
// ExEnd:MakeAllCellsReadOnly
Label1.Text = "All cells are readonly now.";
}
}
protected void chkMergeCells_CheckedChanged(object sender, EventArgs e)
{
if (chkMergeCells.Checked)
{
// ExStart:MergeCells
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Merging cells starting from the cell with 3rd row and 3rd column.
// 2 rows and 2 columns will be merged from the starting cell
sheet.Cells.Merge(2, 2, 2, 2);
// ExEnd:MergeCells
Label1.Text = "2 rows and 2 columns are merged starting from row 3 and column 3";
}
else
{
// ExStart:UnmergeCells
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Unmerging cells starting from the cell with 3rd row and 3rd column.
// 2 rows and 2 columns will be unmerged from the starting cell
sheet.Cells.UnMerge(2, 2, 2, 2);
// ExEnd:UnmergeCells
Label1.Text = "2 rows and 2 columns are unmerged starting from row 3 and column 3";
}
}
protected void btnAddCustomValidation_Click(object sender, EventArgs e)
{
// Accessing the cells collection of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Access cell B1 and add input message text
GridCell cell = sheet.Cells["B1"];
cell.PutValue("Please enter Date in cell C3 e.g. 2018-02-18");
//Access cell B3 and add the Date Pattern
cell = sheet.Cells["B3"];
cell.PutValue("Date (yyyy-mm-dd):");
// Access cell C3 and add to it custom expression validation to accept dates in yyyy-mm-dd format
cell = sheet.Cells["C3"];
var validation = cell.CreateValidation(GridValidationType.CustomExpression, true);
validation.RegEx = @"\d{4}-\d{2}-\d{2}";
//Set the column widths
sheet.Cells.SetColumnWidth(1, 40);
sheet.Cells.SetColumnWidth(2, 30);
// Assigning the name of JavaScript function to OnCellErrorClientFunction property of GridWeb
GridWeb1.OnCellErrorClientFunction = "ValidationErrorFunction";
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !GridWeb1.IsPostBack)
{
// Creates the collection.
MyCollection list = new MyCollection();
System.Random rand = new System.Random();
for (int i = 0; i < 5; i++)
{
// Create custom record object and set properties
MyCustomRecord rec = new MyCustomRecord();
rec.DateField1 = DateTime.Now;
rec.DoubleField1 = rand.NextDouble();
rec.IntField1 = rand.Next();
rec.StringField1 = "ABC_" + i;
((IList)list).Add(rec);
}
// Create web worksheet object
GridWorksheet sheet = GridWeb1.WorkSheets[0];
// Uses the collection as datasource.
sheet.DataSource = list;
// Creates bind columns.
sheet.CreateAutoGenratedColumns();
// Sets the DateFiled1's validation to DateTime.
sheet.BindColumns["DateField1"].Validation.ValidationType = ValidationType.DateTime;
// Binding.
sheet.DataBind();
}
}
protected void btnCreate_Click(object sender, System.EventArgs e)
{
// Fill web worksheet object
GridWorksheet sheet = GridWeb1.WorkSheets[0];
// Removes the created subtotal first.
//sheet.RemoveSubtotal();
// Creates the subtotal.
int groupByIndex;
if (ddlSort.SelectedItem.Value == "CategoryName")
groupByIndex = 1;
else
groupByIndex = 2;
// Creates GrandTotal and Subtotal style.
GridTableItemStyle grandStyle = new GridTableItemStyle();
grandStyle.BackColor = Color.Gray;
grandStyle.ForeColor = Color.Black;
GridTableItemStyle subtotalStyle = new GridTableItemStyle();
subtotalStyle.BackColor = Color.SkyBlue;
subtotalStyle.ForeColor = Color.Black;
// ExStart:CreateSubTotal
sheet.CreateSubtotal(0, sheet.Cells.MaxRow, groupByIndex, (SubtotalFunction)System.Enum.Parse(typeof(SubtotalFunction), ddlFunction.SelectedItem.Value), new int[] { 1, 2, 3, 4, 5 }
, ddlFunction.SelectedItem.Text, grandStyle, subtotalStyle, NumberType.General, null);
// ExEnd:CreateSubTotal
}
protected void btnApplyFontStyles_Click(object sender, EventArgs e)
{
// ExStart:ApplyFontStyles
// Accessing the reference of the worksheet that is currently active and resize first row and column
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
sheet.Cells.Clear();
sheet.Cells.SetColumnWidth(0, 50);
sheet.Cells.SetRowHeight(0, 40);
// Accessing a specific cell of the worksheet
GridCell cell = sheet.Cells["A1"];
// Inserting a value in cell A1
cell.PutValue("Aspose.Cells.GridWeb");
var style = cell.Style;
// Setting font, color and alignment of cell
style.Font.Size = new FontUnit("12pt");
style.Font.Bold = true;
style.ForeColor = Color.Blue;
style.BackColor = Color.Aqua;
style.HorizontalAlign = HorizontalAlign.Center;
// Set the cell style
cell.CopyStyle(style);
sheet.AutoFitColumn(0);
// ExEnd:ApplyFontStyles
}
protected void btnMakeCellReadOnly_Click(object sender, EventArgs e)
{
// ExStart:MakeCellReadOnly
// Accessing the first worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Set the 1st cell (A1) read only
sheet.SetIsReadonly(sheet.Cells["A1"], true);
// ExEnd:MakeCellReadOnly
}
protected void btnUnfreeze_Click(object sender, EventArgs e)
{
// ExStart:UnfreezePanes
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Unfreezing rows and columns
sheet.UnFreezePanes();
// ExEnd:UnfreezePanes
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack && !GridWeb1.IsPostBack)
{
// Create dataset object
this.dataSet11 = new DataSet1();
// Create web worksheet object
GridWorksheet sheet = GridWeb1.WorkSheets[0];
// Specifies the datasource for the sheet.
sheet.DataSource = dataSet11;
sheet.DataMember = "Products";
// Creates in-sheet column headers.
sheet.EnableCreateBindColumnHeader = true;
// Data cells begin at row 1;
sheet.BindStartRow = 1;
// Creates the data field column automatically.
sheet.CreateAutoGenratedColumns();
// Modifies a column's number type.
sheet.BindColumns["UnitPrice"].NumberType = NumberType.Currency3;
// Modifies column headers' background color.
for (int i = 0; i < sheet.BindColumns.Count; i++)
{
sheet.BindColumns[i].ColumnHeaderStyle.BackColor = Color.SkyBlue;
}
// Create demo database object
ExampleDatabase db = new ExampleDatabase();
// Create path to database file
string path = (this.Master as Site).GetDataDir();
// Create connection string
db.oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\Miscellaneous\\Database\\demos.mdb";
try
{
// Loads data from database.
db.oleDbDataAdapter1.Fill(dataSet11);
}
finally
{
// Close connection
db.oleDbConnection1.Close();
}
// Binding.
sheet.DataBind();
}
}
private void CreateRowCaptions()
{
// ExStart:CustomizeRowHeader
// Accessing the worksheet that is currently active
GridWorksheet workSheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Create custom row header caption.
workSheet.SetRowCaption(1, "Row1");
workSheet.SetRowCaption(2, "Row2");
// ExEnd:CustomizeRowHeader
}
protected void Button1_Click(object sender, EventArgs e)
{
int row = Convert.ToInt16(TextBox1.Text.Trim());
int column = Convert.ToInt16(TextBox2.Text.Trim());
int rowNumber = Convert.ToInt16(TextBox3.Text.Trim());
int columnNumber = Convert.ToInt16(TextBox4.Text.Trim());
GridWorksheet sheet = GridWeb1.WorkSheets[0];
sheet.FreezePanes(row, column, rowNumber, columnNumber);
}
public string Validate(GridWorksheet sheet, int row, int col, string value)
{
if ((row == 1) || (row == 2))
{
return("Value Not Passed!");
}
else
{
return(string.Empty);
}
}
private void CreateColumnCaptions()
{
// ExStart:CustomizeColumnHeader
// Accessing the worksheet that is currently active
GridWorksheet workSheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Creates custom column header caption.
workSheet.SetColumnCaption(0, "Product");
workSheet.SetColumnCaption(1, "Category");
workSheet.SetColumnCaption(2, "Price");
// ExEnd:CustomizeColumnHeader
}
private void AccessCellByRowColumnIndex()
{
// ExStart:AccessCellByRowColumnIndex
// Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Accessing "B1" cell of the worksheet using its row and column indices
GridCell cell = sheet.Cells[0, 1];
// Display cell name and value
Label1.Text += "Cell Value of " + cell.Name + " is " + cell.StringValue + "<br/>";
// ExEnd:AccessCellByRowColumnIndex
}
protected void btnRemoveComments_Click(object sender, EventArgs e)
{
// ExStart:RemoveComments
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Accessing a specific cell that contains comment
GridCell cell = sheet.Cells["A1"];
// Removing comment from the specific cell
sheet.Comments.RemoveAt(cell.Name);
// ExEnd:RemoveComments
}
private void AccessCellByName()
{
// ExStart:AccessCellByName
// Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Accessing "A1" cell of the worksheet
GridCell cell = sheet.Cells["A1"];
// Display cell name and value
Label1.Text += "Cell Value of " + cell.Name + " is " + cell.StringValue + "<br/>";
// ExEnd:AccessCellByName
}
private void AddDoubleValue()
{
// ExStart:AddCellDoubleValue
// Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Accessing "B5" cell of the worksheet
GridCell cell = sheet.Cells["B5"];
// Putting a numeric value as string in "B5" cell that will be converted to a suitable data type automatically
cell.PutValue("19.4", true);
// ExEnd:AddCellDoubleValue
}
private void AddIntValue()
{
// ExStart:AddCellIntValue
// Accessing the worksheet of the Grid that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Accessing "B3" cell of the worksheet
GridCell cell = sheet.Cells["B3"];
// Putting a value in "B3" cell
cell.PutValue(30);
// ExEnd:AddCellIntValue
}
protected void btnRemoveHyperlinks_Click(object sender, EventArgs e)
{
// ExStart:RemoveHyperlink
// Accessing the reference of the worksheet that is currently active
GridWorksheet sheet = GridWeb1.WorkSheets[GridWeb1.ActiveSheetIndex];
// Accessing a specific cell that contains hyperlink
GridCell cell = sheet.Cells["B1"];
// Removing hyperlink from the specific cell
GridWeb1.WebWorksheets[GridWeb1.ActiveSheetIndex].Hyperlinks.RemoveHyperlink(cell);
// ExEnd:RemoveHyperlink
}
private void BindWithInSheetHeaders()
{
// Create dataset object
this.dataSet11 = new DataSet1();
// Create database object
ExampleDatabase db = new ExampleDatabase();
// Create path to database file
string path = (this.Master as Site).GetDataDir();
// Create connection string
db.oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\Miscellaneous\\Database\\demos.mdb";
try
{
// Connects to database and fetches data.
db.oleDbDataAdapter1.Fill(dataSet11);
// Create webworksheet object
GridWorksheet sheet = GridWeb1.WorkSheets[0];
// Clears the sheet.
sheet.Cells.Clear();
// Enables creating in-sheet headers.
sheet.EnableCreateBindColumnHeader = true;
// Data cells begin from row 2.
sheet.BindStartRow = 2;
// Creates some title cells.
sheet.Cells["A1"].PutValue("The Product Table");
sheet.Cells["A1"].Style.Font.Size = new FontUnit("20pt");
sheet.Cells["A1"].Style.HorizontalAlign = HorizontalAlign.Center;
sheet.Cells["A1"].Style.VerticalAlign = VerticalAlign.Middle;
sheet.Cells["A1"].Style.BackColor = Color.SkyBlue;
sheet.Cells["A1"].Style.ForeColor = Color.Blue;
sheet.Cells.Merge(0, 0, 2, 11);
// Freezes the header rows.
sheet.FreezePanes(3, 0, 3, 0);
// Bind the sheet to the dataset.
sheet.DataBind();
}
finally
{
//Close connection
db.oleDbConnection1.Close();
}
}
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();
}
