How to check whether the given range is valid or not?

8 Dec 20233 minutes to read

In the given range, if the first row and first column are less than or equal to last row and last column respectively, then the range is valid. XlsIO do not have direct support to check whether the given range is valid or not. But this can be achieved with a simple workaround. Please find the code snippet below.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  string[] ranges = { "D1:A14", "A14:D1", "E1:F3", "G5", "AA10", "A1:A1" };
  foreach (string range in ranges)
  {
    try
    {
      IRange temp_range = worksheet.Range[range];
      Debug.WriteLine(range + " - is valid worksheet range");
    }
    catch (Exception ex)
    {
      Debug.WriteLine(range + " - is invalid worksheet range");
    }
  }
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  string[] ranges = { "D1:A14", "A14:D1", "E1:F3", "G5", "AA10", "A1:A1" };
  foreach(string range in ranges)
  {
    try
    {
      IRange temp_range = worksheet.Range[range];
      Console.WriteLine(range + " - is valid worksheet range");
    }
    catch(Exception ex)
    {
      Console.WriteLine(range + " - is invalid worksheet range");
    }
  }
  Console.ReadLine();
}
Using excelEngine As ExcelEngine = New ExcelEngine
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  Dim ranges() As String = {"D1:A14", "A14:D1", "E1:F3", "G5", "AA10", "A1:A1"}
  For Each range As String In ranges
    Try
      Dim temp_range As IRange = worksheet.Range(range)
      Console.WriteLine((range + " - is valid worksheet range"))
    Catch ex As Exception
      Console.WriteLine((range + " - is invalid worksheet range"))
    End Try
  Next
  Console.ReadLine()
End Using