public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook from source Excel file
Workbook workbook = new Workbook(sourceDir + "sampleApplyingSubtotalChangeSummaryDirection.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Get the Cells collection in the first worksheet
Cells cells = worksheet.Cells;
// Create a cellarea i.e.., A2:B11
CellArea ca = CellArea.CreateCellArea("A2", "B11");
// Apply subtotal, the consolidation function is Sum and it will applied to Second column (B) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 }, true, false, true);
// Set the direction of outline summary
worksheet.Outline.SummaryRowBelow = true;
// Save the excel file
workbook.Save(outputDir + "outputApplyingSubtotalChangeSummaryDirection.xlsx");
Console.WriteLine("ApplyingSubtotalChangeSummaryDirection executed successfully.");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
//Load the source Excel file
Workbook wb = new Workbook(sourceDir + "sampleSortData_CustomSortList.xlsx");
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Specify cell area - sort from A1 to A40
CellArea ca = CellArea.CreateCellArea("A1", "A40");
//Create Custom Sort list
string[] customSortList = new string[] { "USA,US", "Brazil,BR", "China,CN", "Russia,RU", "Canada,CA" };
//Add Key for Column A, Sort it in Ascending Order with Custom Sort List
wb.DataSorter.AddKey(0, SortOrder.Ascending, customSortList);
wb.DataSorter.Sort(ws.Cells, ca);
//Save the output Excel file
wb.Save(outputDir + "outputSortData_CustomSortList.xlsx");
Console.WriteLine("SortDataInColumnWithCustomSortList executed successfully.\r\n");
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiate a new workbook
//Open the template file
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the Cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;
//Create a cellarea i.e.., B3:C19
CellArea ca = new CellArea();
ca.StartRow = 2;
ca.StartColumn = 1;
ca.EndRow = 18;
ca.EndColumn = 2;
//Apply subtotal, the consolidation function is Sum and it will applied to
//Second column (C) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 });
//Save the excel file
workbook.Save(dataDir + "output.out.xls");
//ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiate a new workbook
//Open the template file
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the Cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;
//Create a cellarea i.e.., B3:C19
CellArea ca = new CellArea();
ca.StartRow = 2;
ca.StartColumn = 1;
ca.EndRow = 18;
ca.EndColumn = 2;
//Apply subtotal, the consolidation function is Sum and it will applied to
//Second column (C) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 });
//Save the excel file
workbook.Save(dataDir + "output.xls");
}
private void btnReadExcel_Click(object sender, EventArgs e)
{
string fileName = "HDR库表集合_V1.0.5-201908.xlsx";//"hdr表集合-base-20190711.xlsx";
string filePathWithName = Path.Combine(basePath, fileName);
Workbook workbook = null;
using (FileStream fs = new FileStream(filePathWithName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
workbook = new Workbook(fs);
}
foreach (Worksheet sheet in workbook.Worksheets)
{
var mgdcells = sheet.Cells.MergedCells;
if (sheet.Shapes != null && sheet.Shapes.Count > 0)
{
var shape = sheet.Shapes[0];
}
foreach (var mgcell in mgdcells)
{
CellArea cell = (CellArea)mgcell;
this.richTextBox1.AppendText($"{cell}\r\n");
}
}
}
public static void Run()
{
// ExStart:UsingGlobalizationSettings
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(dataDir + "sample.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(dataDir + "output_out.xlsx");
// ExEnd:UsingGlobalizationSettings
}
public static void AddConditionalFormatting(ref Worksheet ws, int endRow, int TotalColumn)
{
int index = ws.ConditionalFormattings.Add();
FormatConditionCollection conds = ws.ConditionalFormattings[index];
int[] isCondForm = Program.isCondForm;
for (int i = 0; i < isCondForm.Length; i++)
{
if (isCondForm[i] == 1)
{
CellArea area = new CellArea();
area.StartRow = 3;
area.EndRow = endRow;
area.StartColumn = TotalColumn + 1 + i;
area.EndColumn = TotalColumn + 1 + i;
conds.AddArea(area);
}
}
int idx = conds.AddCondition(FormatConditionType.ContainsText);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.LightGreen;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "x";
}
public static void Run()
{
// ExStart:UsingGlobalizationSettings
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Load your source workbook
Workbook wb = new Workbook(dataDir + "sample.xlsx");
// Set the glorbalization setting to change subtotal and grand total names
GlobalizationSettings gsi = new GlobalizationSettingsImp();
wb.Settings.GlobalizationSettings = gsi;
// Access first worksheet
Worksheet ws = wb.Worksheets[0];
// Apply subtotal on A1:B10
CellArea ca = CellArea.CreateCellArea("A1", "B10");
ws.Cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 });
// Set the width of the first column
ws.Cells.SetColumnWidth(0, 40);
// Save the output excel file
wb.Save(dataDir + "output_out.xlsx");
// ExEnd:UsingGlobalizationSettings
}
public static void Run()
{
//ExStart:SpecifyingSortWarningWhileSortingData
//The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Create workbook.
Workbook workbook = new Workbook(dataDir + "sampleSortAsNumber.xlsx");
//Access first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
//Create your cell area.
CellArea ca = CellArea.CreateCellArea("A1", "A20");
//Create your sorter.
DataSorter sorter = workbook.DataSorter;
//Find the index, since we want to sort by column A, so we should know the index for sorter.
int idx = CellsHelper.ColumnNameToIndex("A");
//Add key in sorter, it will sort in Ascending order.
sorter.AddKey(idx, SortOrder.Ascending);
sorter.SortAsNumber = true;
//Perform sort.
sorter.Sort(worksheet.Cells, ca);
//Save the output workbook.
workbook.Save(dataDir + "outputSortAsNumber.xlsx");
//ExEnd:SpecifyingSortWarningWhileSortingData
}
public void CellsWorksheetValidationsPostWorkSheetValidationTest()
{
string name = "Book1.xlsx";
string sheetName = "SHEET1";
int? validationIndex = 0;
Validation validation = new Validation();
CellArea area = new CellArea();
area.StartRow = 0;
area.EndRow = 0;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList = new List <CellArea>();
validation.AreaList.Add(area);
validation.Formula1 = "=(OR(A1=\"Yes\",A1=\"No\"))";
validation.Type = "Custom";
validation.IgnoreBlank = true;
string folder = null;
new Config().UpdateDataFile(folder, name);
new Config().UpdateDataFile(null, "Book1.xlsx");
var response = instance.CellsWorksheetValidationsPostWorksheetValidation(name, sheetName, validationIndex, validation, folder);
Console.WriteLine(response);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbook object
// Open the Excel file
Workbook workbook = new Workbook(dataDir + "book1.xls");
Cells cells = workbook.Worksheets[0].Cells;
// Create Cell's area
CellArea ca = new CellArea();
ca.StartColumn = 0;
ca.EndColumn = 1;
ca.StartRow = 0;
ca.EndRow = 4;
// Move Range
cells.MoveRange(ca, 0, 2);
// Save the resultant file
workbook.Save(dataDir + "book2.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook from source Excel file
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Get the Cells collection in the first worksheet
Cells cells = worksheet.Cells;
// Create a cellarea i.e.., A2:B11
CellArea ca = CellArea.CreateCellArea("A2", "B11");
// Apply subtotal, the consolidation function is Sum and it will applied to Second column (B) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 }, true, false, true);
// Set the direction of outline summary
worksheet.Outline.SummaryRowBelow = true;
// Save the excel file
workbook.Save(dataDir + "output_out.xlsx");
// ExEnd:1
}
public Cell RandomCellByArea(CellArea area)
{
Func <Cell, bool> continueLooping;
switch (area)
{
case CellArea.Corner:
continueLooping = cell => !IsCorner(cell);
break;
case CellArea.Edge:
continueLooping = cell => !IsEdge(cell);
break;
case CellArea.EdgeNotCorner:
continueLooping = cell => IsCorner(cell) || !IsEdge(cell);
break;
case CellArea.Inner:
continueLooping = cell => IsCorner(cell) || IsEdge(cell);
break;
default:
continueLooping = cell => false;
break;
}
Cell returnCell;
while (continueLooping(returnCell = RandomCellOrDefault(CellType.All)))
{
}
return(returnCell);
}
private static void ProcessList(Cells cells, Cell cellToOperateOn, string propertyName, object listObject)
{
var templateRow = cellToOperateOn.Row + 1; //because it needs to insert between the user defined rows.
var column = cellToOperateOn.Column;
var list = (IList)listObject;
if (list.Count == 0)
{
cells.DeleteRange(templateRow - 1, column, templateRow - 1, column, ShiftType.Up);
cells.DeleteRange(templateRow - 1, column, templateRow - 1, column, ShiftType.Up);
if (cells[templateRow - 1, column].IsErrorValue)
{
cells[templateRow - 1, column].PutValue(0);
}
return;
}
cells.InsertRange(CellArea.CreateCellArea(templateRow, column, templateRow + list.Count, column), list.Count, ShiftType.Down, true);
//loop all the rows in the list we are binding too.
foreach (var item in list)
{
SetCellValue(cells[templateRow, column], propertyName, item);
templateRow++;
}
//now delete out the template cells.
cells.DeleteRange(cellToOperateOn.Row, column, cellToOperateOn.Row, column, ShiftType.Up);
cells.DeleteRange(templateRow - 1, column, templateRow - 1, column, ShiftType.Up);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
// Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.AddArea(ca);
// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
FormatCondition fc = fcs[conditionIndex];
fc.Style.Pattern = BackgroundType.ReverseDiagonalStripe;
fc.Style.ForegroundColor = Color.FromArgb(255, 255, 0);
fc.Style.BackgroundColor = Color.FromArgb(0, 255, 255);
workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
private static Hyperlink GetHyperlinkForCell(Cell cell, Worksheet sheet)
{
var row = cell.Row;
var column = cell.Column;
ArrayList list = sheet.Cells.MergedCells;
{
foreach (CellArea ca in list)
{
if (row >= ca.StartRow &&
row <= ca.EndRow &&
column >= ca.StartColumn &&
column <= ca.EndColumn)
{
row = ca.StartRow;
column = ca.EndColumn;
break;
}
}
}
HyperlinkCollection links = sheet.Hyperlinks;
for (int i = links.Count - 1; i >= 0; i--)
{
CellArea ca = links[i].Area;
if (row >= ca.StartRow &&
row <= ca.EndRow &&
column >= ca.StartColumn &&
column <= ca.EndColumn)
{
return(links[i]);
}
}
return(null);
}
public static void Main()
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Create a workbook and opening a template spreadsheet
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Instantiate the error checking options
ErrorCheckOptionCollection opts = sheet.ErrorCheckOptions;
int index = opts.Add();
ErrorCheckOption opt = opts[index];
//Disable the numbers stored as text option
opt.SetErrorCheck(ErrorCheckType.TextNumber, false);
//Set the range
opt.AddRange(CellArea.CreateCellArea(0, 0, 1000, 50));
//Save the Excel file
workbook.Save(dataDir + "out_test.out.xlsx");
//ExEnd:1
}
public static void Run()
{
// ExStart:1
// directories
string SourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();
Workbook workbook = new Workbook(SourceDir + "ValidationsSample.xlsx");
// Access first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the Validations collection of the worksheet
Validation validation = worksheet.Validations[0];
// Create your cell area.
CellArea cellArea = CellArea.CreateCellArea("D5", "E7");
// Adding the cell area to Validation
validation.AddArea(cellArea, false, false);
// Save the output workbook.
workbook.Save(outputDir + "ValidationsSample_out.xlsx");
// ExEnd:1
Console.WriteLine("AddValidationArea executed successfully.");
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
FormatCondition fc = fcs[conditionIndex];
fc.Style.Pattern = BackgroundType.ReverseDiagonalStripe;
fc.Style.ForegroundColor = Color.FromArgb(255, 255, 0);
fc.Style.BackgroundColor = Color.FromArgb(0, 255, 255);
workbook.Save(dataDir + "output.xlsx");
//ExEnd:1
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Load your source workbook
Workbook wb = new Workbook(sourceDir + "sampleTotalsInOtherLanguages.xlsx");
// Set the glorbalization setting to change subtotal and grand total names
GlobalizationSettings gsi = new GlobalizationSettingsImp();
wb.Settings.GlobalizationSettings = gsi;
// Access first worksheet
Worksheet ws = wb.Worksheets[0];
// Apply subtotal on A1:B10
CellArea ca = CellArea.CreateCellArea("A1", "B10");
ws.Cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 2, 3, 4 });
// Set the width of the first column
ws.Cells.SetColumnWidth(0, 40);
// Save the output excel file
wb.Save(outputDir + "outputTotalsInOtherLanguages.xlsx");
Console.WriteLine("TotalsInOtherLanguages executed successfully.");
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate a new workbook
//Open the template file
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the Cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;
//Create a cellarea i.e.., B3:C19
CellArea ca = new CellArea();
ca.StartRow = 2;
ca.StartColumn = 1;
ca.EndRow = 18;
ca.EndColumn = 2;
//Apply subtotal, the consolidation function is Sum and it will applied to
//Second column (C) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 });
//Save the excel file
workbook.Save(dataDir + "output.xls");
}
void TestCellWorld()
{
CellArea cellArea = new CellArea(70, 35);
bool repeat = true;
int iteration = 0;
while (repeat)
{
cellArea.NextStep();
double energySum = cellArea.Monsters.Sum(m => m.Energy);
if (energySum > 2000f)
{
break;
}
if (++iteration % 2000 == 0)
{
Console.SetCursorPosition(0, 0);
Console.WriteLine(iteration + " => " + energySum.ToString("f2") + " ");
Console.Out.Flush();
}
}
do
{
for (int i = 0; i < cellArea.Monsters.Count; i++)
{
cellArea.NextStep();
}
PrintCellArea(cellArea);
Console.WriteLine("Press spacebar to continue ");
}while (Console.ReadKey(false).Key == ConsoleKey.Spacebar);
}
public void CellsWorksheetValidationsPostWorkSheetValidationTest()
{
// TODO uncomment below to test the method and replace null with proper value
string name = BOOK1;
string sheetName = SHEET1;
int? validationIndex = 0;
Validation validation = new Validation();
CellArea area = new CellArea();
area.StartRow = 0;
area.EndRow = 0;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList = new List <CellArea>();
validation.AreaList.Add(area);
validation.Formula1 = "=(OR(A1=\"Yes\",A1=\"No\"))";
validation.Type = "Custom";
validation.IgnoreBlank = true;
string folder = TEMPFOLDER;
UpdateDataFile(folder, name);
UpdateDataFile(TEMPFOLDER, BOOK1);
var response = instance.CellsWorksheetValidationsPostWorksheetValidation(name, sheetName, validationIndex, validation, folder);
Assert.IsInstanceOf <ValidationResponse>(response, "response is ValidationResponse");
Assert.AreEqual(response.Code, 200);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbook object
// Open the Excel file
Workbook workbook = new Workbook(dataDir+ "book1.xls");
Cells cells = workbook.Worksheets[0].Cells;
// Create Cell's area
CellArea ca = new CellArea();
ca.StartColumn = 0;
ca.EndColumn = 1;
ca.StartRow = 0;
ca.EndRow = 4;
// Move Range
cells.MoveRange(ca, 0, 2);
// Save the resultant file
workbook.Save(dataDir+ "book2.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Create a workbook object.
Workbook workbook = new Workbook();
// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.Worksheets[0];
// Accessing the Validations collection of the worksheet
ValidationCollection validations = workbook.Worksheets[0].Validations;
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Creating a Validation object
Validation validation = validations[validations.Add(ca)];
// Setting the validation type to whole number
validation.Type = ValidationType.WholeNumber;
// Setting the operator for validation to Between
validation.Operator = OperatorType.Between;
// Setting the minimum value for the validation
validation.Formula1 = "10";
// Setting the maximum value for the validation
validation.Formula2 = "1000";
// Applying the validation to a range of cells from A1 to B2 using the
// CellArea structure
CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;
// Adding the cell area to Validation
validation.AreaList.Add(area);
// Save the workbook.
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Loads an existing spreadsheet containing some data
Workbook book = new Workbook(sourceDir + "sampleCustomLabelsSubtotals.xlsx");
// Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
book.Settings.GlobalizationSettings = new CustomSettings();
// Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
Worksheet sheet = book.Worksheets[0];
// Adds Subtotal of type Average to the worksheet
sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });
// Calculates Formulas
book.CalculateFormula();
// Auto fits all columns
sheet.AutoFitColumns();
// Saves the workbook on disc
book.Save(outputDir + "outputCustomLabelsSubtotals.xlsx");
Console.WriteLine("CustomLabelsSubtotals executed successfully.");
}
private static void findNow(Worksheet objSheet, string textToFind)
{
//Get Cells collection
Cells cells = objSheet.Cells;
//Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
//Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;
//Set cells area for find options
findOptions.SetRange(ca);
//Set searching properties
findOptions.SearchNext = true;
findOptions.SeachOrderByRows = true;
findOptions.LookInType = LookInType.Values;
//Find the cell with 0 value
Cell cell = cells.Find(textToFind, null, findOptions);
Console.WriteLine(cell.StringValue);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create a workbook and opening a template spreadsheet
Workbook workbook = new Workbook(sourceDir + "sampleErrorCheckingOptions.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Instantiate the error checking options
ErrorCheckOptionCollection opts = sheet.ErrorCheckOptions;
int index = opts.Add();
ErrorCheckOption opt = opts[index];
// Disable the numbers stored as text option
opt.SetErrorCheck(ErrorCheckType.TextNumber, false);
// Set the range
CellArea ca = CellArea.CreateCellArea("A1", "E20");
opt.AddRange(ca);
// Save the Excel file
workbook.Save(outputDir + "outputErrorCheckingOptions.xlsx");
Console.WriteLine("ErrorCheckingOptions executed successfully.\r\n");
}
void PrintCellArea(CellArea cellArea)
{
Console.SetCursorPosition(0, 0);
Console.Write(" ");
for (int w = 0; w < cellArea.Width; w++)
{
Console.Write(w % 10);
}
Console.WriteLine();
var top = cellArea.Monsters.OrderBy(m => - m.Energy).ToList();
for (int h = 0; h < cellArea.Height; h++)
{
Console.Write(h);
Console.Write(' ');
if (h < 10)
{
Console.Write(' ');
}
for (int w = 0; w < cellArea.Width; w++)
{
Cell cell = cellArea.GetCellAt(w, h);
switch (cell.ContentType)
{
case ContentType.Empty:
Console.Write('.');
break;
case ContentType.Food:
Console.Write('+');
break;
case ContentType.Monster:
int place = top.IndexOf(cell.Content as IMonster);
if (place >= 10)
{
Console.Write('M');
}
else
{
Console.Write(place);
}
break;
case ContentType.Wall:
Console.Write('#');
break;
}
}
Console.WriteLine();
}
foreach (IMonster monster in top.Take(32))
{
Console.WriteLine(monster.AreaInfo + ": " + monster.Energy.ToString("f1") + " ");
}
Console.Out.Flush();
}
private static void findNow(Worksheet objSheet, string textToFind)
{
//Get Cells collection
Cells cells = objSheet.Cells;
//Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
//Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;
//Set cells area for find options
findOptions.SetRange(ca);
//Set searching properties
findOptions.SearchNext = true;
findOptions.SeachOrderByRows = true;
findOptions.LookInType = LookInType.Values;
//Find the cell with 0 value
Cell cell = cells.Find(textToFind, null, findOptions);
Console.WriteLine(cell.StringValue);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Create a workbook object.
Workbook workbook = new Workbook();
// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.Worksheets[0];
// Obtain the existing Validations collection.
ValidationCollection validations = ExcelWorkSheet.Validations;
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Create a validation object adding to the collection list.
Validation validation = validations[validations.Add(ca)];
// Set the validation type.
validation.Type = ValidationType.Decimal;
// Specify the operator.
validation.Operator = OperatorType.Between;
// Set the lower and upper limits.
validation.Formula1 = Decimal.MinValue.ToString();
validation.Formula2 = Decimal.MaxValue.ToString();
// Set the error message.
validation.ErrorMessage = "Please enter a valid integer or decimal number";
// Specify the validation area of cells.
CellArea area;
area.StartRow = 0;
area.EndRow = 9;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the area.
validation.AreaList.Add(area);
// Save the workbook.
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
public void CreateStaticReport()
{
//Open template
string path = System.Web.HttpContext.Current.Server.MapPath("~");
path = path.Substring(0, path.LastIndexOf("\\"));
path += @"\designer\Workbooks\unsorted.xls";
//Instantiate a new Workbook object.
Workbook workbook = new Workbook(path);
//Get the workbook datasorter object.
DataSorter sorter = workbook.DataSorter;
//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Descending;
//Define the first key.
sorter.Key1 = 0;
//Set the second order for datasorter object.
sorter.Order2 = Aspose.Cells.SortOrder.Ascending;
//Define the second key.
sorter.Key2 = 1;
//Create a cells area (range).
CellArea ca = new CellArea();
//Specify the start row index.
ca.StartRow = 0;
//Specify the start column index.
ca.StartColumn = 0;
//Specify the last row index.
ca.EndRow = 13;
//Specify the last column index.
ca.EndColumn = 1;
//Sort data in the specified data range (A1:B14)
sorter.Sort(workbook.Worksheets[0].Cells, ca);
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "DataSorting.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "DataSorting.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
private static void RemoveSuperfluousErrorWarnings(Worksheet sheet)
{
ErrorCheckOptionCollection opts = sheet.ErrorCheckOptions;
int index = opts.Add();
ErrorCheckOption opt = opts[index];
opt.SetErrorCheck(ErrorCheckType.TextDate, false);
opt.SetErrorCheck(ErrorCheckType.TextNumber, false);
opt.AddRange(CellArea.CreateCellArea(0, 0, sheet.Cells.MaxRow + 1, sheet.Cells.MaxColumn + 1));
}
public static void Run()
{
// ExStart:AddColorScales
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add some data in cells
worksheet.Cells["A1"].PutValue("2-Color Scale");
worksheet.Cells["D1"].PutValue("3-Color Scale");
for (int i = 2; i <= 15; i++)
{
worksheet.Cells["A" + i].PutValue(i);
worksheet.Cells["D" + i].PutValue(i);
}
// Adding 2-Color Scale Conditional Formatting
CellArea ca = CellArea.CreateCellArea("A2", "A15");
int idx = worksheet.ConditionalFormattings.Add();
FormatConditionCollection fcc = worksheet.ConditionalFormattings[idx];
fcc.AddCondition(FormatConditionType.ColorScale);
fcc.AddArea(ca);
FormatCondition fc = worksheet.ConditionalFormattings[idx][0];
fc.ColorScale.Is3ColorScale = false;
fc.ColorScale.MaxColor = Color.LightBlue;
fc.ColorScale.MinColor = Color.LightGreen;
// Adding 3-Color Scale Conditional Formatting
ca = CellArea.CreateCellArea("D2", "D15");
idx = worksheet.ConditionalFormattings.Add();
fcc = worksheet.ConditionalFormattings[idx];
fcc.AddCondition(FormatConditionType.ColorScale);
fcc.AddArea(ca);
fc = worksheet.ConditionalFormattings[idx][0];
fc.ColorScale.Is3ColorScale = true;
fc.ColorScale.MaxColor = Color.LightBlue;
fc.ColorScale.MidColor = Color.Yellow;
fc.ColorScale.MinColor = Color.LightGreen;
// Save the workbook
workbook.Save(dataDir + "output_out_.xlsx");
// ExEnd:AddColorScales
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Sets the conditional format range.
CellArea ca = new CellArea();
ca = new CellArea();
ca.StartRow = 2;
ca.EndRow = 2;
ca.StartColumn = 1;
ca.EndColumn = 1;
fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.Expression);
//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Formula1 = "=IF(SUM(B1:B2)>100,TRUE,FALSE)";
fc.Style.BackgroundColor = Color.Red;
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";
sheet.Cells["C4"].PutValue("If Sum of B1:B2 is greater than 100, B3 will have RED background");
//Saving the Excel file
workbook.Save(dataDir + "output.out.xls", SaveFormat.Auto);
//ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
// Sets the conditional format range.
CellArea ca = new CellArea();
ca = new CellArea();
ca.StartRow = 2;
ca.EndRow = 2;
ca.StartColumn = 1;
ca.EndColumn = 1;
fcs.AddArea(ca);
// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.Expression);
// Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Formula1 = "=IF(SUM(B1:B2)>100,TRUE,FALSE)";
fc.Style.BackgroundColor = Color.Red;
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";
sheet.Cells["C4"].PutValue("If Sum of B1:B2 is greater than 100, B3 will have RED background");
// Saving the Excel file
workbook.Save(dataDir+ "output.out.xls", SaveFormat.Auto);
// ExEnd:1
}
public static Worksheet SetErrorCheck(this Worksheet ws, bool isCheck)
{
var opts = ws.ErrorCheckOptions;
var i = opts.Add();
ErrorCheckOption opt = opts[i];
opt.SetErrorCheck(ErrorCheckType.EmptyCellRef, isCheck);
opt.SetErrorCheck(ErrorCheckType.TextDate, isCheck);
opt.SetErrorCheck(ErrorCheckType.TextNumber, isCheck);
opt.AddRange(CellArea.CreateCellArea(0, 0, Settings.MaxRows, Settings.MaxColumns));
return(ws);
}
protected void CreateStaticReport()
{
//Intiaalize workbook with xlsx file format
Workbook workbook = new Workbook();
//Clear workbook's worksheets
workbook.Worksheets.Clear();
//Insert new Worksheet in workbook and name it "New"
Worksheet worksheet = workbook.Worksheets.Add("New");
//Insert dummy data in A8, A9 and A10 cells
worksheet.Cells["A8"].PutValue(34);
worksheet.Cells["A9"].PutValue(50);
worksheet.Cells["A10"].PutValue(34);
//Intialize Cell Area
CellArea cellArea = new CellArea();
//Assign Cell Area boundaries
cellArea.StartColumn = 0;
cellArea.EndColumn = 0;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
//Add new Sparklines in worksheet's sparlines collection and Assign the area for it
int index = worksheet.SparklineGroupCollection.Add(SparklineType.Column, worksheet.Name + "!A8:A10", true, cellArea);
//Initalize Sparklines Group
SparklineGroup group = worksheet.SparklineGroupCollection[index];
// change the color of the series if need
CellsColor cellColor = workbook.CreateCellsColor();
cellColor.Color = Color.Orange;
//Asign the group series color
group.SeriesColor = cellColor;
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "SparkLines.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "SparkLines.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
public static void Run()
{
// ExStart:1
// Instantiate the workbook object
Workbook workbook = new Workbook(sourceDir + "sampleFindDataOrFormulas.xlsx");
workbook.CalculateFormula();
// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
// Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
// Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;
// Set cells area for find options
findOptions.SetRange(ca);
// Set searching properties
findOptions.SearchBackward = false;
findOptions.SeachOrderByRows = true;
// Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;
// Set the lookattype, you may specify Match entire content, endswith, starwith etc.
findOptions.LookAtType = LookAtType.EntireContent;
// Find the cell with value
Cell cell = cells.Find(276, null, findOptions);
if (cell != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// ExEnd:1
Console.WriteLine("FindDataOrFormulas executed successfully.");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook object.
// Load a template file.
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Get the workbook datasorter object.
DataSorter sorter = workbook.DataSorter;
// Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Descending;
// Define the first key.
sorter.Key1 = 0;
// Set the second order for datasorter object.
sorter.Order2 = Aspose.Cells.SortOrder.Ascending;
// Define the second key.
sorter.Key2 = 1;
// Create a cells area (range).
CellArea ca = new CellArea();
// Specify the start row index.
ca.StartRow = 0;
// Specify the start column index.
ca.StartColumn = 0;
// Specify the last row index.
ca.EndRow = 13;
// Specify the last column index.
ca.EndColumn = 1;
// Sort data in the specified data range (A1:B14)
sorter.Sort(workbook.Worksheets[0].Cells, ca);
// Save the excel file.
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");
// Get Cells collection
Cells cells = workbook.Worksheets[0].Cells;
// Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
// Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 8;
ca.StartColumn = 2;
ca.EndRow = 17;
ca.EndColumn = 13;
// Set cells area for find options
findOptions.SetRange(ca);
// Set searching properties
findOptions.SearchNext = true;
findOptions.SeachOrderByRows = true;
// Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;
// Set the lookattype, you may specify Match entire content, endswith, starwith etc.
findOptions.LookAtType = LookAtType.EntireContent;
// Find the cell with value
Cell cell = cells.Find(205, null, findOptions);
if (cell != null)
{
Console.WriteLine("Name of the cell containing the value: " + cell.Name);
}
else
{
Console.WriteLine("Record not found ");
}
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a Workbook
// Open a template file
Workbook book = new Workbook(dataDir + "Book1.xlsx");
// Get the first worksheet
Worksheet sheet = book.Worksheets[0];
// Use the following lines if you need to read the Sparklines
// Read the Sparklines from the template file (if it has)
foreach (SparklineGroup g in sheet.SparklineGroupCollection)
{
// Display the Sparklines group information e.g type, number of sparklines items
Console.WriteLine("sparkline group: type:" + g.Type + ", sparkline items count:" + g.SparklineCollection.Count);
foreach (Sparkline s in g.SparklineCollection)
{
// Display the individual Sparkines and the data ranges
Console.WriteLine("sparkline: row:" + s.Row + ", col:" + s.Column + ", dataRange:" + s.DataRange);
}
}
// Add Sparklines
// Define the CellArea D2:D10
CellArea ca = new CellArea();
ca.StartColumn = 4;
ca.EndColumn = 4;
ca.StartRow = 1;
ca.EndRow = 7;
// Add new Sparklines for a data range to a cell area
int idx = sheet.SparklineGroupCollection.Add(SparklineType.Column, "Sheet1!B2:D8", false, ca);
SparklineGroup group = sheet.SparklineGroupCollection[idx];
// Create CellsColor
CellsColor clr = book.CreateCellsColor();
clr.Color = Color.Orange;
group.SeriesColor = clr;
// Save the excel file
book.Save(dataDir + "Book1.out.xlsx");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open an Excel file
Workbook workbook = new Workbook(dataDir+ "Book_SourceData.xlsx");
// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Get the cells collection in the sheet
Cells cells = worksheet.Cells;
// Obtain the DataSorter object in the workbook
DataSorter sorter = workbook.DataSorter;
// Set the first order
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;
// Define the first key.
sorter.Key1 = 0;
// Set the second order
sorter.Order2 = Aspose.Cells.SortOrder.Ascending;
// Define the second key
sorter.Key2 = 1;
// Create a cells area (range).
CellArea ca = new CellArea();
// Specify the start row index.
ca.StartRow = 1;
// Specify the start column index.
ca.StartColumn = 0;
// Specify the last row index.
ca.EndRow = 9;
// Specify the last column index.
ca.EndColumn = 2;
// Sort data in the specified data range (A2:C10)
sorter.Sort(workbook.Worksheets[0].Cells, ca);
// Saving the excel file in the default (that is Excel 2003) format
workbook.Save(dataDir+ "outBook_SortedData.out.xlsx");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
// Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
fcs.AddArea(ca);
// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
// Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Red;
// Saving the Excel file
workbook.Save(dataDir+ "output.out.xls", SaveFormat.Auto);
// ExEnd:1
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate a new Workbook object.
//Load a template file.
Workbook workbook = new Workbook(dataDir + "book1.xls");
//Get the workbook datasorter object.
DataSorter sorter = workbook.DataSorter;
//Set the first order for datasorter object.
sorter.Order1 = Aspose.Cells.SortOrder.Descending;
//Define the first key.
sorter.Key1 = 0;
//Set the second order for datasorter object.
sorter.Order2 = Aspose.Cells.SortOrder.Ascending;
//Define the second key.
sorter.Key2 = 1;
//Create a cells area (range).
CellArea ca = new CellArea();
//Specify the start row index.
ca.StartRow = 0;
//Specify the start column index.
ca.StartColumn = 0;
//Specify the last row index.
ca.EndRow = 13;
//Specify the last column index.
ca.EndColumn = 1;
//Sort data in the specified data range (A1:B14)
sorter.Sort(workbook.Worksheets[0].Cells, ca);
//Save the excel file.
workbook.Save(dataDir + "output.xls");
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiating a Workbook object
//Open an Excel file
Workbook workbook = new Workbook(dataDir+ "Book_SourceData.xlsx");
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Get the cells collection in the sheet
Cells cells = worksheet.Cells;
//Obtain the DataSorter object in the workbook
DataSorter sorter = workbook.DataSorter;
//Set the first order
sorter.Order1 = Aspose.Cells.SortOrder.Ascending;
//Define the first key.
sorter.Key1 = 0;
//Set the second order
sorter.Order2 = Aspose.Cells.SortOrder.Ascending;
//Define the second key
sorter.Key2 = 1;
//Create a cells area (range).
CellArea ca = new CellArea();
//Specify the start row index.
ca.StartRow = 1;
//Specify the start column index.
ca.StartColumn = 0;
//Specify the last row index.
ca.EndRow = 9;
//Specify the last column index.
ca.EndColumn = 2;
//Sort data in the specified data range (A2:C10)
sorter.Sort(workbook.Worksheets[0].Cells, ca);
//Saving the excel file in the default (that is Excel 2003) format
workbook.Save(dataDir+ "outBook_SortedData.xlsx");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new Workbook
// Open an existing excel file
Workbook wkBook = new Workbook(dataDir+ "SampleInput.xlsx");
// Get a worksheet in the workbook
Worksheet wkSheet = wkBook.Worksheets["Sheet2"];
// Clear its contents
wkSheet.Cells.Clear();
// Create an arraylist object
ArrayList al = new ArrayList();
// Get the merged cells list to put it into the arraylist object
al = wkSheet.Cells.MergedCells;
// Define cellarea
CellArea ca;
// Define some variables
int frow, fcol, erow, ecol, trows, tcols;
// Loop through the arraylist and get each cellarea
// To unmerge it
for (int i = 0; i < al.Count; i++)
{
ca = new CellArea();
ca = (CellArea)al[i];
frow = ca.StartRow;
fcol = ca.StartColumn;
erow = ca.EndRow;
ecol = ca.EndColumn;
trows = erow - frow + 1;
tcols = ecol - fcol + 1;
wkSheet.Cells.UnMerge(frow, fcol, trows, tcols);
}
dataDir = dataDir+ "MergeTrial.out.xlsx";
// Save the excel file
wkBook.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate a new Workbook
//Open an existing excel file
Workbook wkBook = new Workbook(dataDir+ "SampleInput.xlsx");
//Get a worksheet in the workbook
Worksheet wkSheet = wkBook.Worksheets["Sheet2"];
//Clear its contents
wkSheet.Cells.Clear();
//Create an arraylist object
ArrayList al = new ArrayList();
//Get the merged cells list to put it into the arraylist object
al = wkSheet.Cells.MergedCells;
//Define cellarea
CellArea ca;
//Define some variables
int frow, fcol, erow, ecol, trows, tcols;
//Loop through the arraylist and get each cellarea
//to unmerge it
for (int i = 0; i < al.Count; i++)
{
ca = new CellArea();
ca = (CellArea)al[i];
frow = ca.StartRow;
fcol = ca.StartColumn;
erow = ca.EndRow;
ecol = ca.EndColumn;
trows = erow - frow + 1;
tcols = ecol - fcol + 1;
wkSheet.Cells.UnMerge(frow, fcol, trows, tcols);
}
//Save the excel file
wkBook.Save(dataDir+ "MergeTrial.xlsx");
}
static void Main(string[] args)
{
//Instantiating a Workbook object
Workbook workbook = new Workbook("../../data/test.xlsx");
//Get the Cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;
//Create a cellarea i.e.., B3:C19
CellArea ca = new CellArea();
ca.StartRow = 2;
ca.StartColumn = 1;
ca.EndRow = 18;
ca.EndColumn = 2;
//Apply subtotal, the consolidation function is Sum and it will applied to
//Second column (C) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 });
//Save the excel file
workbook.Save("AsposeTotal.xls");
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Instantiate the workbook object
//Open the Excel file
Workbook workbook = new Workbook(dataDir+ "book1.xls");
Cells cells = workbook.Worksheets[0].Cells;
//Create Cell's area
CellArea ca = new CellArea();
ca.StartColumn = 0;
ca.EndColumn = 1;
ca.StartRow = 0;
ca.EndRow = 4;
//Move Range
cells.MoveRange(ca, 0, 2);
//Save the resultant file
workbook.Save(dataDir+ "book2.xls");
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
// Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.AddArea(ca);
// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
// Sets the background color.
FormatCondition fc = fcs[conditionIndex];
// fc.Style.BackgroundColor = Color.Red;
fc.Style.Font.IsItalic = true;
fc.Style.Font.IsBold = true;
fc.Style.Font.IsStrikeout = true;
fc.Style.Font.Underline = FontUnderlineType.Double;
fc.Style.Font.Color = Color.Black;
workbook.Save(dataDir + "output.xlsx");
// ExEnd:1
}
private void Factor(out Expr e) {
RARef r1, r2;
Sheet s1 = null;
double d;
bool sheetError = false;
e = null;
switch (la.kind) {
case 1: {
Application(out e);
break;
}
case 4:
case 5:
case 6:
case 7:
case 8:
case 9:
case 10:
case 11:
case 12:
case 13:
case 14: {
if (StartOf(2)) {}
else {
Get();
s1 = workbook[t.val.Substring(0, t.val.Length - 1)];
if (s1 == null) {
sheetError = true;
}
}
Raref(out r1);
if (StartOf(3)) {
if (sheetError) {
e = new Error(ErrorValue.refError);
}
else {
e = new CellRef(s1, r1);
}
}
else if (la.kind == 26) {
Get();
Raref(out r2);
if (sheetError) {
e = new Error(ErrorValue.refError);
}
else {
e = new CellArea(s1, r1, r2);
}
}
else {
SynErr(37);
}
break;
}
case 2: {
Number(out d);
e = new NumberConst(d);
break;
}
case 18: {
Get();
Factor(out e);
if (e is NumberConst) {
e = new NumberConst(-((NumberConst)e).value.value);
}
else {
e = FunCall.Make("NEG", new Expr[] {e});
}
break;
}
case 15: {
Get();
e = new TextConst(t.val.Substring(1, t.val.Length - 2));
break;
}
case 27: {
Get();
Expr(out e);
Expect(28);
break;
}
default:
SynErr(38);
break;
}
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Create a new workbook.
Workbook workbook = new Workbook();
// Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;
// Put a string value into A1 cell.
cells["A1"].PutValue("Please enter a string not more than 5 chars");
// Set row height and column width for the cell.
cells.SetRowHeight(0, 31);
cells.SetColumnWidth(0, 35);
// Get the validations collection.
ValidationCollection validations = workbook.Worksheets[0].Validations;
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Add a new validation.
Validation validation = validations[validations.Add(ca)];
// Set the data validation type.
validation.Type = ValidationType.TextLength;
// Set the operator for the data validation.
validation.Operator = OperatorType.LessOrEqual;
// Set the value or expression associated with the data validation.
validation.Formula1 = "5";
// Enable the error.
validation.ShowError = true;
// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Warning;
// Set the title of the data-validation error dialog box.
validation.ErrorTitle = "Text Length Error";
// Set the data validation error message.
validation.ErrorMessage = " Enter a Valid String";
// Set and enable the data validation input message.
validation.InputMessage = "TextLength Validation Type";
validation.IgnoreBlank = true;
validation.ShowInput = true;
// Set a collection of CellArea which contains the data validation settings.
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
// Add the validation area.
validation.AreaList.Add(cellArea);
// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Create a workbook object.
Workbook workbook = new Workbook();
// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
// Add a new worksheet and access it.
int i = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets[i];
// Create a range in the second worksheet.
Range range = worksheet2.Cells.CreateRange("E1", "E4");
// Name the range.
range.Name = "MyRange";
// Fill different cells with data in the range.
range[0, 0].PutValue("Blue");
range[1, 0].PutValue("Red");
range[2, 0].PutValue("Green");
range[3, 0].PutValue("Yellow");
// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Create a new validation to the validations list.
Validation validation = validations[validations.Add(ca)];
// Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List;
// Set the operator.
validation.Operator = OperatorType.None;
// Set the in cell drop down.
validation.InCellDropDown = true;
// Set the formula1.
validation.Formula1 = "=MyRange";
// Enable it to show error.
validation.ShowError = true;
// Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the error title.
validation.ErrorTitle = "Error";
// Set the error message.
validation.ErrorMessage = "Please select a color from the list";
// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the validation area.
validation.AreaList.Add(area);
// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");
// ExEnd:1
}
/// <summary>
/// Fetches data from database and exports report to excel
/// </summary>
private void ExportToExcel()
{
new License().SetLicense("Aspose.Cells.lic");
// Generate File
try
{
// Create Spreadsheet
Workbook book = new Workbook(FileFormatType.Xlsx);
book.Worksheets.Clear();
MemoryStream fileStream = new MemoryStream();
Aspose.Cells.Style dateStyle = book.Styles[book.Styles.Add()];
dateStyle.Number = 14;
Worksheet sheet = book.Worksheets.Add("Reports");
List<SqlParameter> parameters = new List<SqlParameter>();
List<InvalidParameterItem> results = this.ReportDao.GetInvalidParametersReport(parameters.ToArray()).ToList();
string[] fieldNames = new string[]
{
"ReportName",
"ParameterType",
"ParameterName",
"DeactivatedOrDeleted"
};
// Import data
sheet.Cells.ImportCustomObjects(
results,
fieldNames,
true,
0,
0,
results.Count(),
false,
"dd/mm/yyyy",
false);
// Update header fields text
Cells cells = sheet.Cells;
FindOptions findOptions = new FindOptions();
CellArea cellArea = new CellArea();
cellArea.StartRow = 0;
cellArea.StartColumn = 0;
cellArea.EndRow = 0;
cellArea.EndColumn = 20;
findOptions.SetRange(cellArea);
findOptions.SearchNext = true;
findOptions.SeachOrderByRows = true;
Cell cell;
cell = cells.Find("ReportName", null, findOptions);
if (cell != null)
{
cell.PutValue("Report Name");
}
cell = cells.Find("ParameterType", null, findOptions);
if (cell != null)
{
cell.PutValue("Parameter Type");
}
cell = cells.Find("ParameterName", null, findOptions);
if (cell != null)
{
cell.PutValue("Parameter Name");
}
cell = cells.Find("DeactivatedOrDeleted", null, findOptions);
if (cell != null)
{
cell.PutValue("Deactivated / Deleted");
}
// Set styling
ListObject sheetTable = sheet.ListObjects[sheet.ListObjects.Add(0, 0, Math.Max(sheet.Cells.MaxRow, 1), Math.Max(sheet.Cells.MaxColumn, 1), true)];
sheetTable.TableStyleType = TableStyleType.TableStyleLight1;
sheet.AutoFitColumns();
book.Save(fileStream, SaveFormat.Xlsx);
fileStream.Position = 0;
Response.Clear();
Response.Buffer = false;
Response.AppendHeader("Content-Disposition", "attachment;filename=InvalidParametersReport_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx");
Response.AppendHeader("ContentType", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Response.AppendHeader("Content-Length", fileStream.Length.ToString());
byte[] fileBytes = new byte[fileStream.Length];
Response.BinaryWrite(fileStream.ToArray());
Response.Flush();
Response.Close();
}
catch (ThreadAbortException)
{
// This should be first catch block i.e. before generic Exception
// This Catch block is to absorb exception thrown by Response.End
}
catch (Exception)
{
Response.Clear();
throw;
}
}
public void CallVisitor(CellArea cellArea) { result = new CGNormalCellArea(cellArea.MakeArrayView(thisFca)); }
public void CreateStaticReport()
{
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
//Initialize FormatConditionCollection from newly inserted Index
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
//Assign FormatConditionCollection the Area
fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
//Set BackgroundColor
fc.Style.BackgroundColor = Color.Red;
//Adds an empty conditional formatting
int index2 = sheet.ConditionalFormattings.Add();
//Initialize FormatConditionCollection for newly added index
FormatConditionCollection fcs2 = sheet.ConditionalFormattings[index2];
//Sets the conditional format range.
CellArea ca2 = new CellArea();
ca2.StartRow = 2;
ca2.EndRow = 2;
ca2.StartColumn = 1;
ca2.EndColumn = 1;
//Assign FormatConditionCollection the Area
fcs2.AddArea(ca2);
//Adds condition.
int conditionIndex2 = fcs2.AddCondition(FormatConditionType.Expression);
//Sets the background color.
FormatCondition fc2 = fcs2[conditionIndex2];
//Set FormatCondition Object formula
fc2.Formula1 = "=IF(SUM(B1:B2)>100,TRUE,FALSE)";
//Set FormatCondition Object Background Color
fc2.Style.BackgroundColor = Color.Red;
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";
//Put Value in Cell C4
sheet.Cells["C4"].PutValue("If Sum of B1:B2 is greater than 100, B3 will have RED background");
if (ddlFileVersion.SelectedItem.Value == "XLS")
{
////Save file and send to client browser using selected format
workbook.Save(HttpContext.Current.Response, "ConditionalFormatting.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
}
else
{
workbook.Save(HttpContext.Current.Response, "ConditionalFormatting.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
}
//end response to avoid unneeded html
HttpContext.Current.Response.End();
}
//This method specifies the CellArea range (start row, start col, end row, end col etc.)
//for the conditional formatting
internal static CellArea GetCellAreaByName(string s)
{
CellArea area = new CellArea();
string[] strCellRange = s.Replace("$", "").Split(':');
int column;
CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column);
area.StartColumn = column;
if (strCellRange.Length == 1)
{
area.EndRow = area.StartRow;
area.EndColumn = area.StartColumn;
}
else
{
CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column);
area.EndColumn = column;
}
return area;
}
public bool SeenBefore(CellArea cellArea) { return !cellAreasSeen.Add(cellArea); }
/// <summary>
/// Exports Reports to Excel spreadsheet
/// </summary>
/// <param name="reports">The report results to put into the excel spreadsheet</param>
public void ExportReports(List<ReportParametersDTO> reports)
{
new License().SetLicense("Aspose.Cells.lic");
// Generate File
try
{
// We use our own custom zip logic using System.IO.Packaging
ZipPackageManager packageManager = new ZipPackageManager();
// Create Spreadsheet
Workbook book = new Workbook(FileFormatType.Xlsx);
book.Worksheets.Clear();
MemoryStream fileStream = new MemoryStream();
Aspose.Cells.Style dateStyle = book.Styles[book.Styles.Add()];
dateStyle.Number = 14;
Worksheet sheet = book.Worksheets.Add("Reports");
string[] fieldNames = new string[1];
// Set which fields to import based on search filter report type
switch ((Enums.ReportType)this._SelectedReportTypeId)
{
case Enums.ReportType.BudgetOriginatorJobCodeDetailsReport:
fieldNames = this.GetJobCodeReportFields();
break;
case Enums.ReportType.BudgetOriginatorReport:
fieldNames = this.GetBudgetOriginatorReportFields();
break;
case Enums.ReportType.BudgetOwnerReport:
fieldNames = this.GetBudgetOwnerReportFields();
break;
case Enums.ReportType.ExpenseCzarReport:
fieldNames = this.GetExpenseCzarReportFields();
break;
case Enums.ReportType.ProfitabilityReport:
fieldNames = this.GetProfitabilityReportFields();
break;
default:
fieldNames = this.GetGenericReportFields();
break;
}
// Import data
sheet.Cells.ImportCustomObjects(
reports,
fieldNames,
true,
0,
0,
reports.Count(),
false,
"dd/mm/yyyy",
false);
// Update header fields text
Cells cells = sheet.Cells;
FindOptions findOptions = new FindOptions();
CellArea cellArea = new CellArea();
cellArea.StartRow = 0;
cellArea.StartColumn = 0;
cellArea.EndRow = 0;
cellArea.EndColumn = 20;
findOptions.SetRange(cellArea);
findOptions.SearchNext = true;
findOptions.SeachOrderByRows = true;
Cell cell;
cell = cells.Find("ReportId", null, findOptions);
if (cell != null)
{
cell.PutValue("Report ID");
}
cell = cells.Find("ReportName", null, findOptions);
if (cell != null)
{
cell.PutValue("Report Name");
}
cell = cells.Find("ReportType", null, findOptions);
if (cell != null)
{
cell.PutValue("Report Type");
}
cell = cells.Find("CurrencyCode", null, findOptions);
if (cell != null)
{
cell.PutValue("Currency");
}
cell = cells.Find("ReportReceivers", null, findOptions);
if (cell != null)
{
cell.PutValue("Report Receiver");
}
cell = cells.Find("EntityTypes", null, findOptions);
if (cell != null)
{
cell.PutValue("Reporting Entity Type");
}
cell = cells.Find("EntityList", null, findOptions);
if (cell != null)
{
cell.PutValue("Reporting Entity");
}
cell = cells.Find("SensitizeMRIPayrollData", null, findOptions);
if (cell != null)
{
cell.PutValue("Show Sensitized Payroll Information");
}
cell = cells.Find("CalculationMethod", null, findOptions);
if (cell != null)
{
cell.PutValue("Gross / Net Mode");
}
cell = cells.Find("GLCategorization", null, findOptions);
if (cell != null)
{
cell.PutValue("Categorization");
}
cell = cells.Find("FunctionalDepartments", null, findOptions);
if (cell != null)
{
cell.PutValue("Functional Department");
}
cell = cells.Find("AllocationSubRegions", null, findOptions);
if (cell != null)
{
cell.PutValue("Allocation Sub Region");
}
cell = cells.Find("OriginatingSubRegions", null, findOptions);
if (cell != null)
{
cell.PutValue("Originating Sub Region");
}
cell = cells.Find("EntityTypes", null, findOptions);
if (cell != null)
{
cell.PutValue("Reporting Entity Type");
}
cell = cells.Find("EntityList", null, findOptions);
if (cell != null)
{
cell.PutValue("Reporting Entity");
}
cell = cells.Find("SensitizeMRIPayrollData", null, findOptions);
if (cell != null)
{
cell.PutValue("Show Sensitized Payroll Information");
}
cell = cells.Find("FinancialCategories", null, findOptions);
if (cell != null)
{
cell.PutValue("Financial Category");
}
cell = cells.Find("MajorCategories", null, findOptions);
if (cell != null)
{
cell.PutValue("Major Category");
}
cell = cells.Find("MinorCategories", null, findOptions);
if (cell != null)
{
cell.PutValue("Minor Category");
}
cell = cells.Find("ActivityTypes", null, findOptions);
if (cell != null)
{
cell.PutValue("Activity Type");
}
cell = cells.Find("IncludeGrossNonPayrollExpenses", null, findOptions);
if (cell != null)
{
cell.PutValue("Include Gross Non-Payroll Expenses");
}
cell = cells.Find("IncludeFeeAdjustments", null, findOptions);
if (cell != null)
{
cell.PutValue("Include Fee Adjustment");
}
cell = cells.Find("DisplayOverheadBy", null, findOptions);
if (cell != null)
{
cell.PutValue("Display Overhead By");
}
cell = cells.Find("OverheadOriginatingSubRegion", null, findOptions);
if (cell != null)
{
cell.PutValue("Overhead Originating Sub Region");
}
cell = cells.Find("ConsolidationRegions", null, findOptions);
if (cell != null)
{
cell.PutValue("Consolidation Region");
}
// Set styling
ListObject sheetTable = sheet.ListObjects[sheet.ListObjects.Add(0, 0, Math.Max(sheet.Cells.MaxRow, 1), Math.Max(sheet.Cells.MaxColumn, 1), true)];
sheetTable.TableStyleType = TableStyleType.TableStyleLight1;
sheet.AutoFitColumns();
book.Save(fileStream, SaveFormat.Xlsx);
fileStream.Position = 0;
packageManager.Add("Reports.xlsx", fileStream);
MemoryStream zipFileStream = packageManager.Save();
Response.Clear();
Response.Buffer = false;
Response.AppendHeader("Content-Disposition", "attachment;filename=Report_Parameters_" + DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("hhmmss") + ".zip");
Response.AppendHeader("ContentType", "application/zip");
byte[] fileBytes = new byte[zipFileStream.Length];
Response.BinaryWrite(zipFileStream.ToArray());
Response.End();
}
catch
{
Response.Clear();
throw;
}
}
