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) { }