private static void FormatRows(ICOBieSheet<COBieRow> cOBieSheet)
{
int columns = 0;
foreach (var column in cOBieSheet.Columns.OrderBy(c => c.Key))
{
Console.Write(column.Value.ColumnName);
Console.Write(", ");
columns++;
}
for (int i = 0; i < cOBieSheet.RowCount; i++)
{
COBieRow row = cOBieSheet[i];
Console.WriteLine("");
for (int col = 0; col < columns; col++)
{
Console.Write(row[col].CellValue);
Console.Write(",");
}
}
}
/// <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);
}
public COBieConnectionRow(ICOBieSheet<COBieConnectionRow> parentSheet)
: base(parentSheet) { ExtIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); }
public COBieJobRow(ICOBieSheet<COBieJobRow> parentSheet)
: base(parentSheet) { }
public COBieConnectionRow(ICOBieSheet<COBieConnectionRow> parentSheet)
: base(parentSheet) { }
/// <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);
}
public COBieSpaceRow(ICOBieSheet<COBieSpaceRow> parentSheet)
: base(parentSheet) { }
public COBieFacilityRow(ICOBieSheet <COBieFacilityRow> parentSheet)
: base(parentSheet)
{
}
public COBieAttributeRow(ICOBieSheet<COBieAttributeRow> parentSheet)
: base(parentSheet) { }
public COBieAssemblyRow(ICOBieSheet <COBieAssemblyRow> parentSheet)
: base(parentSheet)
{
}
public COBieCoordinateRow(ICOBieSheet <COBieCoordinateRow> parentSheet)
: base(parentSheet)
{
}
public COBieAttributeRow(ICOBieSheet <COBieAttributeRow> parentSheet)
: base(parentSheet)
{
}
public COBiePickListsRow(ICOBieSheet<COBiePickListsRow> parentSheet)
: base(parentSheet) { }
public COBieFacilityRow(ICOBieSheet<COBieFacilityRow> parentSheet)
: base(parentSheet) { }
public COBieAssemblyRow(ICOBieSheet<COBieAssemblyRow> parentSheet)
: base(parentSheet) { }
public COBieIssueRow(ICOBieSheet <COBieIssueRow> parentSheet)
: base(parentSheet)
{
}
public COBieResourceRow(ICOBieSheet<COBieResourceRow> parentSheet)
: base(parentSheet) { }
public COBieFacilityRow(ICOBieSheet <COBieFacilityRow> parentSheet)
: base(parentSheet)
{
ExternalFacilityIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); ExternalProjectIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); ExternalSiteIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid());
}
public COBieDocumentRow(ICOBieSheet<COBieDocumentRow> parentSheet)
: base(parentSheet) { }
public COBieJobRow(ICOBieSheet <COBieJobRow> parentSheet)
: base(parentSheet)
{
}
public COBieSystemRow(ICOBieSheet <COBieSystemRow> parentSheet)
: base(parentSheet)
{
}
public COBieComponentRow(ICOBieSheet<COBieComponentRow> parentSheet)
: base(parentSheet) { }
public COBieResourceRow(ICOBieSheet <COBieResourceRow> parentSheet)
: base(parentSheet)
{
}
public COBieContactRow(ICOBieSheet<COBieContactRow> parentSheet)
: base(parentSheet) { }
public COBieIssueRow(ICOBieSheet<COBieIssueRow> parentSheet)
: base(parentSheet) { }
public COBieFloorRow(ICOBieSheet<COBieFloorRow> parentSheet)
: base(parentSheet) { }
public COBieCoordinateRow(ICOBieSheet<COBieCoordinateRow> parentSheet)
: base(parentSheet) { }
public COBieFacilityRow(ICOBieSheet<COBieFacilityRow> parentSheet)
: base(parentSheet) { ExternalFacilityIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); ExternalProjectIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); ExternalSiteIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); }
public COBieRow(ICOBieSheet<COBieRow> parentSheet)
{
ParentSheet = parentSheet;
}
/// <summary>
/// Creates an excel comment in each cell with an associated error
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="sheet"></param>
private void HighlightErrors(ISheet excelSheet, ICOBieSheet<COBieRow> sheet)
{
//sort by row then column
var errors = sheet.Errors.OrderBy(err => err.Row).ThenBy(err => err.Column);
// The patriarch is a container for comments on a sheet
IDrawing patr = excelSheet.CreateDrawingPatriarch();
int sheetCommnetCount = 0;
foreach (var error in errors)
{
if (error.Row > 0 && error.Column >= 0)
{
if ((error.Row + 3) > 65280)//UInt16.MaxValue some reason the CreateCellComment has 65280 as the max row number
{
// TODO: Warn overflow of XLS 2003 worksheet
break;
}
//limit comments to 1000 per sheet
if (sheetCommnetCount == 999)
break;
IRow excelRow = excelSheet.GetRow(error.Row);
if (excelRow != null)
{
ICell excelCell = excelRow.GetCell(error.Column);
if (excelCell != null)
{
string description = error.ErrorDescription;
if(hasErrorLevel)
{
if (error.ErrorLevel == COBieError.ErrorLevels.Warning)
description = "Warning: " + description;
else
description = "Error: " + description;
}
if (excelCell.CellComment == null)
{
try
{
// A client anchor is attached to an excel worksheet. It anchors against a top-left and bottom-right cell.
// Create a comment 3 columns wide and 3 rows height
IClientAnchor anchor = null;
if (IsXlsx)
{
anchor = new XSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
else
{
anchor = new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
}
IComment comment = patr.CreateCellComment(anchor);
IRichTextString str = null;
if (IsXlsx)
{
str = new XSSFRichTextString(description);
}
else
{
str = new HSSFRichTextString(description);
}
comment.String = str;
comment.Author = "XBim";
excelCell.CellComment = comment;
_commentCount++;
sheetCommnetCount++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
else
{
if (IsXlsx)
{
((XSSFRichTextString)excelCell.CellComment.String).Append(" Also " + description);
}
else
{
description = excelCell.CellComment.String.ToString() + " Also " + description;
excelCell.CellComment.String = new HSSFRichTextString(description);
}
}
}
}
}
}
}
public COBieImpactRow(ICOBieSheet <COBieImpactRow> parentSheet)
: base(parentSheet)
{
}
public COBieResourceRow(ICOBieSheet<COBieResourceRow> parentSheet)
: base(parentSheet) { ExtIdentifier = IFCGuid.ToIfcGuid(Guid.NewGuid()); }
public COBieConnectionRow(ICOBieSheet <COBieConnectionRow> parentSheet)
: base(parentSheet)
{
}
