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