Get cell value type and format

Get a cell from a sheet

There are 2 methods to get a cell from a sheet; by address name e.g. "A2" or by col and row index.

// 1-By address name: get cell at B7
ExcelCell excelCell= proc.GetCellAt(excelSheet, "B7");

// 2-By col and row index: get cell at B7: row: B/2, col:7
ExcelCell excelCell= proc.GetCellAt(excelSheet, 2, 7);

If the cell doesn't exists, excelCell is null.

It's not an error the function, will return true.

The function will return an error only if an internal problem occurs to get the cell.

Create a cell in a sheet

This method below create a new cell in a sheet at the defined column and row.

// create a cell at B2 position 
ExcelCell excelCell = proc.CreateCell(excelSheet, "B2");

If the cell already exists, return it.

Get the type, the value and the data format from a cell

There is one important function to get in one call, the value, the type, and the data format from a cell.

There are 3 versions of this function, depending on the way to find the cell.

//1- get the type and the value, by cell reference 
ExcelCellValue excelCellValue= proc.GetCellValue(excelSheet, "A2);

//2- get the type, the format and the value, by column and row indexes
ExcelCellValue excelCellValue= proc.GetCellValue(excelSheet, 1, 2);

//3- get the type, the format and the value, by cell object
ExcelCell excelCell= proc.GetCellAt(excelSheet, 2, 7);
ExcelCellValue excelCellValue= proc.GetCellValue(excelSheet, excelCell);

Get the type, general cases

These functions returns the object ExcelCellValue which contains the type, the value and the data format.

if(excelCellValue.CellType == ExcelCellType.String) -> do something
if(excelCellValue.CellType == ExcelCellType.Integer) -> do something
if(excelCellValue.CellType == ExcelCellType.Double) -> do something
if(excelCellValue.CellType == ExcelCellType.DateOnly) -> do something
if(excelCellValue.CellType == ExcelCellType.DateTime) -> do something
if(excelCellValue.CellType == ExcelCellType.TimeOnly) -> do something

Error case: It was not possible to get the information from the cell.

if(excelCellValue.CellType == ExcelCellType.Error) -> do something

Cell is empty (or blank)

Cell can be set with a value or can be empty. An empty cell can have style like Fill (bg/fg color), Border, ...

In some cases, it's not possible to define the type of the cell so the type is Undefined.

if(excelCellValue.CellType == ExcelCellType.Undefined) -> do something

This flag indicates is the cell value is empty:

if(excelCellValue.IsEmpty) -> do something

Get the value

According to the type you can get the value.

if(excelCellValue.CellType == ExcelCellType.String) 
{ 
  Console.WriteLine("value is a string: "+ excelCellValue.StringValue);
}

if(excelCellValue.CellType == ExcelCellType.Integer) 
{ 
  Console.WriteLine("value is an integer: "+ excelCellValue.IntegerValue);
}

if(excelCellValue.CellType == ExcelCellType.Double) 
{ 
  Console.WriteLine("value is a double: "+ excelCellValue.DoubleValue);
}

if(excelCellValue.CellType == ExcelCellType.DateOnly) 
{ 
  Console.WriteLine("value is a DateOnly: "+ excelCellValue.DateOnlyValue);
}

if(excelCellValue.CellType == ExcelCellType.DateTime) 
{ 
  Console.WriteLine("value is a DateTime: "+ excelCellValue.DateTimeValue);
}

if(excelCellValue.CellType == ExcelCellType.TimeOnly) 
{ 
  Console.WriteLine("value is a TimeOnly: "+ excelCellValue.TimeOnlyValue);
}

Get the number format

For some kind of Number (integer and double), all date and also currency, a number format is set.

int format= excelCellValue.NumberFormatId;

string format= excelCellValue.NumberFormat;

If there is no format defined for the value of the cell, it will return null.