/// <summary>
/// Creates an instance of <see cref="ExcelBinaryReader"/>
/// </summary>
/// <param name="fileStream">The file stream.</param>
/// <param name="configuration">The configuration object.</param>
/// <returns>The excel data reader.</returns>
public static IExcelDataReader CreateBinaryReader(Stream fileStream, ExcelReaderConfiguration configuration = null)
{
var probe = new byte[8];
fileStream.Seek(0, SeekOrigin.Begin);
fileStream.Read(probe, 0, probe.Length);
fileStream.Seek(0, SeekOrigin.Begin);
if (CompoundDocument.IsCompoundDocument(probe))
{
var document = new CompoundDocument(fileStream);
if (TryGetWorkbook(fileStream, document, out var stream))
{
return(new ExcelBinaryReader(stream, configuration));
}
else
{
throw new ExcelReaderException(Errors.ErrorStreamWorkbookNotFound);
}
}
else if (XlsWorkbook.IsRawBiffStream(probe))
{
return(new ExcelBinaryReader(fileStream, configuration));
}
else
{
throw new HeaderException(Errors.ErrorHeaderSignature);
}
}
private void CreateColours(string colourName, byte red, byte green, byte blue)
{
var palette = XlsWorkbook.GetCustomPalette();
var colour = palette.FindSimilarColor(red, green, blue) ?? palette.AddColor(red, green, blue);
_colours.Add(colourName, colour);
}
private void UpdateInstructions()
{
ISheet instructionsSheet = XlsWorkbook.GetSheet(InstructionsSheet);
if (instructionsSheet != null)
{
RecalculateSheet(instructionsSheet);
}
}
/// <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>
/// Creates an instance of <see cref="ExcelBinaryReader"/> or <see cref="ExcelOpenXmlReader"/>
/// </summary>
/// <param name="fileStream">The file stream.</param>
/// <param name="configuration">The configuration object.</param>
/// <returns>The excel data reader.</returns>
public static IExcelDataReader CreateReader(Stream fileStream, ExcelReaderConfiguration configuration = null)
{
if (configuration == null)
{
configuration = new ExcelReaderConfiguration();
}
if (configuration.LeaveOpen)
{
fileStream = new LeaveOpenStream(fileStream);
}
var probe = new byte[8];
fileStream.Seek(0, SeekOrigin.Begin);
fileStream.Read(probe, 0, probe.Length);
fileStream.Seek(0, SeekOrigin.Begin);
if (CompoundDocument.IsCompoundDocument(probe))
{
// Can be BIFF5-8 or password protected OpenXml
var document = new CompoundDocument(fileStream);
if (TryGetWorkbook(fileStream, document, out var stream))
{
return(new ExcelBinaryReader(stream, configuration.Password, configuration.FallbackEncoding));
}
else if (TryGetEncryptedPackage(fileStream, document, configuration.Password, out stream))
{
return(new ExcelOpenXmlReader(stream));
}
else
{
throw new ExcelReaderException(Errors.ErrorStreamWorkbookNotFound);
}
}
else if (XlsWorkbook.IsRawBiffStream(probe))
{
return(new ExcelBinaryReader(fileStream, configuration.Password, configuration.FallbackEncoding));
}
else if (probe[0] == 0x50 && probe[1] == 0x4B)
{
// zip files start with 'PK'
return(new ExcelOpenXmlReader(fileStream));
}
else
{
throw new HeaderException(Errors.ErrorHeaderSignature);
}
}
private void CreateColours(string colourName, byte red, byte green, byte blue)
{
HSSFPalette palette = XlsWorkbook.GetCustomPalette();
HSSFColor colour = palette.FindSimilarColor(red, green, blue);
if (colour == null)
{
// First 64 are system colours
//srl this code does not work with the latest version of NPOI
//if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 )
//{
// NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
//}
//NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
colour = palette.AddColor(red, green, blue);
}
_colours.Add(colourName, colour);
}
private void ReportErrors(COBieWorkbook workbook, ICOBieValidationTemplate validationTemplate = null)
{
var errorsSheet = XlsWorkbook.GetSheet(ErrorsSheet) ?? XlsWorkbook.CreateSheet(ErrorsSheet);
ICOBieSheetValidationTemplate sheetValidator = null;
foreach (var sheet in workbook.OrderBy(w => w.SheetName))
{
if (sheet.SheetName != Constants.WORKSHEET_PICKLISTS)
{
if (validationTemplate != null && validationTemplate.Sheet.ContainsKey(sheet.SheetName))
{
sheetValidator = validationTemplate.Sheet[sheet.SheetName];
}
// Ensure the validation is up to date
sheet.Validate(workbook, ErrorRowIndexBase.RowTwo, sheetValidator);
}
WriteErrors(errorsSheet, sheet.Errors);
}
}
/// <summary>
/// Creates an instance of <see cref="ExcelBinaryReader"/> or <see cref="ExcelOpenXmlReader"/>
/// </summary>
/// <param name="fileStream">The file stream.</param>
/// <param name="configuration">The configuration object.</param>
/// <returns>The excel data reader.</returns>
public static IExcelDataReader CreateReader(Stream fileStream, ExcelReaderConfiguration configuration = null)
{
var probe = new byte[8];
fileStream.Read(probe, 0, probe.Length);
fileStream.Seek(0, SeekOrigin.Begin);
if (XlsWorkbook.IsCompoundDocument(probe) || XlsWorkbook.IsRawBiffStream(probe))
{
return(CreateBinaryReader(fileStream, configuration));
}
// zip files start with 'PK'
if (probe[0] == 0x50 && probe[1] == 0x4B)
{
return(CreateOpenXmlReader(fileStream, configuration));
}
throw new NotSupportedException("Unknown file format");
}
private void CreateFormat(COBieAllowedType type, string formatString, string colourName)
{
HSSFCellStyle cellStyle;
cellStyle = XlsWorkbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat dataFormat = XlsWorkbook.CreateDataFormat() as HSSFDataFormat;
cellStyle.DataFormat = dataFormat.GetFormat(formatString);
cellStyle.FillForegroundColor = _colours[colourName].Indexed;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
// TODO:maybe clone from the template?
_cellStyles.Add(type, cellStyle);
}
private void CreateFormat(COBieAllowedType type, string formatString, string colourName)
{
var cellStyle = XlsWorkbook.CreateCellStyle() as HSSFCellStyle;
var dataFormat = XlsWorkbook.CreateDataFormat() as HSSFDataFormat;
if (cellStyle == null || dataFormat == null)
{
Log.LogWarning("Error producing Excel cell style or format in workbook.");
return;
}
cellStyle.DataFormat = dataFormat.GetFormat(formatString);
cellStyle.FillForegroundColor = _colours[colourName].Indexed;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
// TODO:maybe clone from the template?
_cellStyles.Add(type, cellStyle);
}
/// <summary>
/// Create a rules sheet
/// </summary>
private void ReportRules()
{
ISheet rulesSheet = XlsWorkbook.GetSheet(RulesSheet) ?? XlsWorkbook.CreateSheet(RulesSheet);
//Add Header
List <string> headings = new List <string>()
{
"Component Sheet Excluded Objects",
"Type Sheet Excluded Objects",
"Assembly Sheet Excluded Objects",
"Attributes Excludes All Sheets (Name Containing)",
"Attributes Excludes All Sheets (Name Equal)",
"Attributes Excludes All Sheets (Name Starts With)",
"Attributes Excludes Components (Name Containing)",
"Attributes Excludes Components (Name Equal)",
"Attributes Excludes Facility (Name Containing)",
"Attributes Excludes Facility (Name Equal)",
"Attributes Excludes Floor (Name Containing)",
"Attributes Excludes Floor (Name Equal)",
"Attributes Excludes Space (Name Containing)",
"Attributes Excludes Space (Name Equal)",
"Attributes Excludes Space (PropertySet Name)",
"Attributes Excludes Spare (Name Containing)",
"Attributes Excludes Spare (Name Equal)",
"Attributes Excludes Types (Name Containing)",
"Attributes Excludes Types (Name Equal)",
"Attributes Excludes Types (PropertySet Name)",
"Attributes Excludes Zone (Name Containing)"
};
int col = 0;
IRow excelRow = rulesSheet.GetRow(0) ?? rulesSheet.CreateRow(0);
foreach (string title in headings)
{
ICell excelCell = excelRow.GetCell(col) ?? excelRow.CreateCell(col);
excelCell.SetCellValue(title);
col++;
}
WriteExcludesObjects(0, rulesSheet, Excludes.ObjectType.Component);
WriteExcludesObjects(1, rulesSheet, Excludes.ObjectType.Types);
WriteExcludesObjects(2, rulesSheet, Excludes.ObjectType.Assembly);
WriteExcludesStrings(3, rulesSheet, Excludes.Common.AttributesContain);
WriteExcludesStrings(4, rulesSheet, Excludes.Common.AttributesEqualTo);
WriteExcludesStrings(5, rulesSheet, Excludes.Common.AttributesStartWith);
WriteExcludesStrings(6, rulesSheet, Excludes.Component.AttributesContain);
WriteExcludesStrings(7, rulesSheet, Excludes.Component.AttributesEqualTo);
WriteExcludesStrings(8, rulesSheet, Excludes.Facility.AttributesContain);
WriteExcludesStrings(9, rulesSheet, Excludes.Facility.AttributesEqualTo);
WriteExcludesStrings(10, rulesSheet, Excludes.Floor.AttributesContain);
WriteExcludesStrings(11, rulesSheet, Excludes.Floor.AttributesEqualTo);
WriteExcludesStrings(12, rulesSheet, Excludes.Space.AttributesContain);
WriteExcludesStrings(13, rulesSheet, Excludes.Space.AttributesEqualTo);
WriteExcludesStrings(14, rulesSheet, Excludes.Space.PropertySetsEqualTo);
WriteExcludesStrings(15, rulesSheet, Excludes.Spare.AttributesContain);
WriteExcludesStrings(16, rulesSheet, Excludes.Spare.AttributesEqualTo);
WriteExcludesStrings(17, rulesSheet, Excludes.Types.AttributesContain);
WriteExcludesStrings(18, rulesSheet, Excludes.Types.AttributesEqualTo);
WriteExcludesStrings(19, rulesSheet, Excludes.Types.PropertySetsEqualTo);
WriteExcludesStrings(20, rulesSheet, Excludes.Zone.AttributesContain);
for (int c = 0; c < headings.Count; c++)
{
rulesSheet.AutoSizeColumn(c);
}
}
