private void FormatCell(ICell excelCell, COBieCell cell)
{
HSSFCellStyle style;
if (_cellStyles.TryGetValue(cell.COBieColumn.AllowedType, out style))
{
excelCell.CellStyle = style;
}
}
/// <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>
/// Set the cell to the correct colour and formate(as required)
/// </summary>
/// <param name="excelCell"></param>
/// <param name="cell"></param>
private void FormatCell(ICell excelCell, COBieCell cell)
{
string cellColour = GetCellColour(cell.COBieColumn.AttributeState);
var tuple = new Tuple <string, COBieAllowedType>(cellColour, cell.COBieColumn.AllowedType);
ICellStyle style;
if (_cellStyles.TryGetValue(tuple, out style))
{
excelCell.CellStyle = style;
}
}
private void SetCellValue(ICell excelCell, COBieCell cell)
{
if (SetCellTypedValue(excelCell, cell))
{
return;
}
//check text length will fit in cell
excelCell.SetCellValue(cell.CellValue.Length >= short.MaxValue
? cell.CellValue.Substring(0, short.MaxValue - 1)
: cell.CellValue);
}
/// <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);
}
private void SetCellValue(ICell excelCell, COBieCell cell)
{
if (SetCellTypedValue(excelCell, cell) == false)
{
//check text length will fit in cell
if (cell.CellValue.Length >= short.MaxValue)
{
//truncate cell text to max length
excelCell.SetCellValue(cell.CellValue.Substring(0, short.MaxValue - 1));
}
else
{
excelCell.SetCellValue(cell.CellValue);
}
}
}
private bool SetCellTypedValue(ICell excelCell, COBieCell cell)
{
var processed = false;
try
{
if (string.IsNullOrEmpty(cell.CellValue) || cell.CellValue == Constants.DEFAULT_STRING)
{
return(false);
}
// We need to set the value in the most appropriate overload of SetCellValue, so the parsing/formatting is correct
switch (cell.COBieColumn.AllowedType)
{
case COBieAllowedType.ISODateTime:
case COBieAllowedType.ISODate:
DateTime date;
if (DateTime.TryParse(cell.CellValue, CultureInfo.InvariantCulture,
DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal, out date))
{
excelCell.SetCellValue(date);
processed = true;
}
break;
case COBieAllowedType.Numeric:
double val;
if (double.TryParse(cell.CellValue, out val))
{
excelCell.SetCellValue(val);
processed = true;
}
break;
}
}
catch (SystemException se)
{
Log.LogError(0, se, "Error in the excel file management");
}
return(processed);
}
private bool SetCellTypedValue(ICell excelCell, COBieCell cell)
{
bool processed = false;
try
{
if (String.IsNullOrEmpty(cell.CellValue) || cell.CellValue == Constants.DEFAULT_STRING)
{
return false;
}
// We need to set the value in the most appropriate overload of SetCellValue, so the parsing/formatting is correct
switch (cell.COBieColumn.AllowedType)
{
case COBieAllowedType.ISODateTime:
case COBieAllowedType.ISODate:
DateTime date;
if (DateTime.TryParse(cell.CellValue, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal, out date))
{
excelCell.SetCellValue(date);
processed = true;
}
break;
case COBieAllowedType.Numeric:
Double val;
if (Double.TryParse(cell.CellValue, out val))
{
excelCell.SetCellValue(val);
processed = true;
}
break;
default:
break;
}
}
catch (SystemException)
{ /* Carry on */ }
return processed;
}
private void SetCellValue(ICell excelCell, COBieCell cell)
{
if (SetCellTypedValue(excelCell, cell) == false)
{
//check text length will fit in cell
if (cell.CellValue.Length >= short.MaxValue)
{
//truncate cell text to max length
excelCell.SetCellValue(cell.CellValue.Substring(0, short.MaxValue - 1));
}
else
{
excelCell.SetCellValue(cell.CellValue);
}
}
}
/// <summary>
/// Set the cell to the correct colour and formate(as required)
/// </summary>
/// <param name="excelCell"></param>
/// <param name="cell"></param>
private void FormatCell(ICell excelCell, COBieCell cell)
{
string cellColour = GetCellColour(cell.COBieColumn.AttributeState);
var tuple = new Tuple<string, COBieAllowedType>(cellColour, cell.COBieColumn.AllowedType);
ICellStyle style;
if (_cellStyles.TryGetValue(tuple, out style))
{
excelCell.CellStyle = style;
}
}
/// <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)
cellValue = cell.DateCellValue.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;
}
/// <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);
}
