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
// 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()
{
//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()
{
//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.");
}
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");
}
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 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 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 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 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 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: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
}
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 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);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "sampleSearchReplaceDataInRange.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
// Specify the range where you want to search
// Here the range is E3:H6
CellArea area = CellArea.CreateCellArea("E9", "H15");
// Specify Find options
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
opts.SetRange(area);
Cell cell = null;
do
{
// Search the cell with value search within range
cell = worksheet.Cells.Find("search", cell, opts);
// If no such cell found, then break the loop
if (cell == null)
{
break;
}
// Replace the cell with value replace
cell.PutValue("replace");
} while (true);
// Save the workbook
workbook.Save(outputDir + "outputSearchReplaceDataInRange.xlsx");
Console.WriteLine("SearchReplaceDataInRange executed successfully.");
}
/// <summary>
/// Thực hiện binding
/// </summary>
public override int Bind(Workbook workbook, object data)
{
#region Khắc phục lỗi numbers stored as text option
var opts = workbook.Worksheets[0].ErrorCheckOptions;
var index = opts.Add();
var opt = opts[index];
//Disable the numbers stored as text option
opt.SetErrorCheck(ErrorCheckType.TextNumber, false);
//Set the range
opt.AddRange(CellArea.CreateCellArea(0, 0, 65535, 256));
#endregion
// Tạo Header
var rowStart = BindHeader(workbook, RowBegin);
// Bind nội dung
return(BindContent(workbook, data, rowStart));
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create an instance of Workbook or load existing
var book = new Workbook();
// Access the Worksheet on which desired rule has to be applied
var sheet = book.Worksheets[0];
// Add FormatConditions to the instance of Worksheet
int idx = sheet.ConditionalFormattings.Add();
// Access the newly added FormatConditions via its index
var conditionCollection = sheet.ConditionalFormattings[idx];
// Define a CellsArea on which conditional formatting will be applicable
// The code creates a CellArea ranging from A1 to I20
var area = CellArea.CreateCellArea("A1", "I20");
//Add area to the instance of FormatConditions
conditionCollection.AddArea(area);
// Add a condition to the instance of FormatConditions
// For this case, the condition type is expression, which is based on some formula
idx = conditionCollection.AddCondition(FormatConditionType.Expression);
// Access the newly added FormatCondition via its index
FormatCondition formatCondirion = conditionCollection[idx];
// Set the formula for the FormatCondition
// Formula uses the Excel's built-in functions as discussed earlier in this article
formatCondirion.Formula1 = @"=MOD(ROW(),2)=0";
// Set the background color and patter for the FormatCondition's Style
formatCondirion.Style.BackgroundColor = Color.Blue;
formatCondirion.Style.Pattern = BackgroundType.Solid;
// Save the result on disk
book.Save(dataDir + "output_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);
string filePath = dataDir + "input.xlsx";
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
// Specify the range where you want to search
// Here the range is E3:H6
CellArea area = CellArea.CreateCellArea("E9", "H15");
// Specify Find options
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
opts.SetRange(area);
Cell cell = null;
do
{
// Search the cell with value search within range
cell = worksheet.Cells.Find("search", cell, opts);
// If no such cell found, then break the loop
if (cell == null)
{
break;
}
// Replace the cell with value replace
cell.PutValue("replace");
} while (true);
// Save the workbook
workbook.Save(dataDir + "output.out.xlsx");
// ExEnd:1
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Create workbook object from source excel file
Workbook workbook = new Workbook(sourceDir + "sampleGenerateDatabarImage.xlsx");
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Access the cell which contains conditional formatting databar
Cell cell = worksheet.Cells["C1"];
// Create and get the conditional formatting of the worksheet
int idx = worksheet.ConditionalFormattings.Add();
FormatConditionCollection fcc = worksheet.ConditionalFormattings[idx];
fcc.AddCondition(FormatConditionType.DataBar);
fcc.AddArea(CellArea.CreateCellArea("C1", "C4"));
// Access the conditional formatting databar
DataBar dbar = fcc[0].DataBar;
// Create image or print options
ImageOrPrintOptions opts = new ImageOrPrintOptions();
opts.ImageFormat = ImageFormat.Png;
// Get the image bytes of the databar
byte[] imgBytes = dbar.ToImage(cell, opts);
// Write image bytes on the disk
File.WriteAllBytes(outputDir + "outputGenerateDatabarImage.png", imgBytes);
Console.WriteLine("GenerateDatabarImage executed successfully.");
}
public static void Main()
{
// ExStart:1
// Create a workbook object and load template file
Workbook workbook = new Workbook(sourceDir + "sampleBackGroundFile.xlsx");
// Instantiate data sorter object
DataSorter sorter = workbook.DataSorter;
// Add key for second column for red color
sorter.AddKey(1, SortOnType.CellColor, SortOrder.Descending, Color.Red);
// Sort the data based on the key
sorter.Sort(workbook.Worksheets[0].Cells, CellArea.CreateCellArea("A2", "C6"));
// Save the output file
workbook.Save(outputDir + "outputsampleBackGroundFile.xlsx");
// ExEnd:1
//Display the message
Console.WriteLine("SortDataInColumnWithBackgroundColor executed successfully.\r\n");
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Open the Excel file
Workbook workbook = new Workbook(sourceDir + "sampleMoveRangeOfCells.xlsx");
Cells cells = workbook.Worksheets[0].Cells;
// Create Cell's area
CellArea ca = CellArea.CreateCellArea("A1", "C5");
// Move Range
cells.MoveRange(ca, 7, 5);
// Save the resultant file
workbook.Save(outputDir + "outputMoveRangeOfCells.xlsx");
Console.WriteLine("MoveRangeOfCells executed successfully.");
}
public static void Main()
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//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.xlsx");
}
