How to validate birth dates in spreadsheet and mark incorrect birth dates with background color using Bytescout Spreadsheet for .NET

  • Home
  • /
  • Articles
  • /
  • How to validate birth dates in spreadsheet and mark incorrect birth dates with background color using Bytescout Spreadsheet for .NET

How to load and check if spreadsheet contains correct dates for persons (if person is a teenager and so his/here current age is from 10 to 19) and mark incorrect birth dates with red color using Bytescout Spreadsheet SDK for .NET

This source code sample “checking if person is teenager or not” demonstrates how to use Bytescout Spreadsheet SDK to open existing Excel (.XLS) document and verify birth dates of persons. If current age of a person is beetwen 10 and 19 so he/she is a teenager but if age is not beetween 10 and 19 then this person is not a teenager and the cell is marked with red background color

Download example source code: bytescoutxls_validate_datetime_in_cells.zip (8 KB)

Output spreadsheet document with validated birth dates where incorrect birth dates are marked with red color:

output spreadsheet document with incorrect birth dates marked with red color

using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using Bytescout.Spreadsheet;

namespace HelloWorld
{
class Program
{
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet(“Data.xls”);

// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.ByName(“Sample”);

// Years range
int RangeMin = 10;
int RangeMax = 19;

// Minimum age
DateTime YearMin = DateTime.Now;
// Maximum age
DateTime YearMax = DateTime.Now;

// Minimum year
YearMax = YearMax.AddYears(-RangeMin);
// Maximum year
YearMin = YearMin.AddYears(-RangeMax);

// Check dates
for (int i = 1; i < 8; i++)
{
// Set current cell
Cell currentCell = worksheet.Cell(i, 1);

// Create birth date
int daysBorn = System.Convert.ToInt32(currentCell.Value);

DateTime dateBirth = DateTime.FromOADate(daysBorn);

// Check current cell
if (dateBirth < YearMin || dateBirth > YearMax)
{
// Set fill pattern
currentCell.FillPattern = Bytescout Spreadsheet.Constants.PatternStyle.Solid;

// Markup wrong cell by red color
currentCell.FillPatternForeColor = System.Drawing.Color.Red;
}

// Set cell format
currentCell.NumberFormat = “dd.mm.yyyy”;
}

// Save document
document.SaveAs(“CheckedData.xls”);

// Close document
document.Close();

// open generated XLS document in default program
Process.Start(“CheckedData.xls”);

}
}
}

Download example source code: bytescoutxls_validate_datetime_in_cells.zip (8 KB)

prev
next