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