/// <summary>
/// Writes the Excel worksheet for this COBie sheet
/// </summary>
/// <param name="sheet"></param>
private void WriteSheet(ICOBieSheet <COBieRow> sheet)
{
ISheet excelSheet = ExcelWorkbook.GetSheet(sheet.SheetName) ?? ExcelWorkbook.CreateSheet(sheet.SheetName);
var datasetHeaders = sheet.Columns.Values.ToList();
var sheetHeaders = GetTargetHeaders(excelSheet);
ValidateHeaders(datasetHeaders, sheetHeaders, sheet.SheetName);
// Enumerate rows
for (int r = 0; r < sheet.RowCount; r++)
{
if (r >= UInt16.MaxValue && !IsXlsx)
{
throw new Exception(string.Format("Row count exceeds the XLS file type limit {0}", UInt16.MaxValue));
//break;
}
COBieRow row = sheet[r];
// GET THE ROW + 1 - This stops us overwriting the headers of the worksheet
IRow excelRow = excelSheet.GetRow(r + 1) ?? excelSheet.CreateRow(r + 1);
for (int c = 0; c < sheet.Columns.Count; c++)
{
COBieCell cell = row[c];
ICell excelCell = excelRow.GetCell(c) ?? excelRow.CreateCell(c);
SetCellValue(excelCell, cell);
FormatCell(excelCell, cell);
}
}
if ((sheet.RowCount == 0)
//&& (_colours.ContainsKey("Grey"))
)
{
if (IsXlsx)
{
((XSSFSheet)excelSheet).SetTabColor(IndexedColors.Grey50Percent.Index);
}
else if (_colours.ContainsKey("Grey"))
{
excelSheet.TabColorIndex = _colours["Grey"].Indexed;
}
}
if (sheet.SheetName != Constants.WORKSHEET_PICKLISTS)
{
HighlightErrors(excelSheet, sheet);
}
RecalculateSheet(excelSheet);
}
/// <summary>
/// Writes the Excel worksheet for this COBie sheet
/// </summary>
/// <param name="sheet"></param>
private void WriteSheet(ICOBieSheet <COBieRow> sheet)
{
ISheet excelSheet = XlsWorkbook.GetSheet(sheet.SheetName) ?? XlsWorkbook.CreateSheet(sheet.SheetName);
var datasetHeaders = sheet.Columns.Values.ToList();
var sheetHeaders = GetTargetHeaders(excelSheet);
ValidateHeaders(datasetHeaders, sheetHeaders, sheet.SheetName);
// Enumerate rows
for (int r = 0; r < sheet.RowCount; r++)
{
if (r >= UInt16.MaxValue)
{
// TODO: Warn overflow of XLS 2003 worksheet
break;
}
COBieRow row = sheet[r];
// GET THE ROW + 1 - This stops us overwriting the headers of the worksheet
IRow excelRow = excelSheet.GetRow(r + 1) ?? excelSheet.CreateRow(r + 1);
for (int c = 0; c < sheet.Columns.Count; c++)
{
COBieCell cell = row[c];
ICell excelCell = excelRow.GetCell(c) ?? excelRow.CreateCell(c);
SetCellValue(excelCell, cell);
FormatCell(excelCell, cell);
}
}
if ((sheet.RowCount == 0) &&
(_colours.ContainsKey("Grey"))
)
{
excelSheet.TabColorIndex = _colours["Grey"].Indexed;
}
if (sheet.SheetName != Constants.WORKSHEET_PICKLISTS)
{
HighlightErrors(excelSheet, sheet);
}
RecalculateSheet(excelSheet);
}
/// <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);
}
