/// <summary>
/// DeSerialise the date held in the sheet into a COBieWorkbook
/// </summary>
/// <returns>COBieWorkbook with date imported from XLS file</returns>
public COBieWorkbook Deserialise()
{
try
{
GetXLSFileData(); //Read XLS file into the HSSFWorkbook object
foreach (string sheetname in SheetNames)
{
ISheet excelSheet = XlsWorkbook.GetSheet(sheetname); //get sheet name in XLS file
if (excelSheet != null)
{
ICOBieSheet <COBieRow> thisSheet = GetSheetType(sheetname);
int COBieColumnCount = thisSheet.Columns.Count;
//no checking on Sheet column count to XLS sheet column count, just extract up to the column number in the COBieSheet/Row
int rownumber = 0;
int columnCount = 0;
foreach (IRow row in excelSheet)
{
if (rownumber == 0) //this will be the headers so get how many we have
{
foreach (ICell cell in row)
{
columnCount++;
}
}
else
{
bool addRow = false;
//check we have some data on the row
for (int i = 0; i < columnCount; i++)
{
ICell cell = row.GetCell(i);
if ((cell != null) && (cell.CellType != CellType.Blank))
{
addRow = true;
break;
}
}
//add a none blank row
if (addRow)
{
COBieRow sheetRow = thisSheet.AddNewRow(); //add a new empty COBie row to the sheet
for (int i = 0; i < thisSheet.Columns.Count(); i++) //changed from columnCount to supported column count of the sheet
{
string cellValue = ""; //default value
ICell cell = row.GetCell(i);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Numeric:
if (sheetRow[i].COBieColumn.AllowedType == COBieAllowedType.ISODate)
{
cellValue = cell.DateCellValue.ToString(Constants.DATE_FORMAT);
}
else if (sheetRow[i].COBieColumn.AllowedType == COBieAllowedType.ISODateTime)
{
DateTime date = DateTime.Now;
try
{
date = cell.DateCellValue;
}
catch
{
// If we can't read a valid date, just use the current date.
date = DateTime.Now;
}
cellValue = date.ToString(Constants.DATETIME_FORMAT);
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Error:
cellValue = cell.ErrorCellValue.ToString();
break;
case CellType.Blank:
case CellType.Formula:
case CellType.Unknown:
cellValue = cell.StringCellValue;
break;
default:
break;
}
}
if (i < COBieColumnCount) //check we are in the column range of the COBieRow and add value
{
COBieColumn cobieColumn = thisSheet.Columns.Where(idxcol => idxcol.Key == i).Select(idxcol => idxcol.Value).FirstOrDefault();
sheetRow[i] = new COBieCell(cellValue, cobieColumn);
}
}
}
}
rownumber++;
}
WorkBook.Add(thisSheet);
}
}
}
catch (FileNotFoundException)
{
//TODO: Report this
throw;
}
catch (Exception)
{
throw;
}
WorkBook.CreateIndices();
return(WorkBook);
}
