/// <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); }