/// <summary>If the user has specified that there is a header row, remap the class properties based
/// on the columns in the row</summary>
/// <param name="headerRow">The top (first) row with header text</param>
/// <param name="columns">Information about the property that corresponds to the column (cell)</param>
/// <param name="sst"></param>
private void RemapExcelColumnLetters(Row headerRow, List <ClassToExcelColumn> columns, SharedStringTable sst)
{
// Erase all existing column letters
foreach (var column in columns)
{
column.ExcelColumnLetter = string.Empty;
}
// Assign new column letter
foreach (Cell oneCell in headerRow.Elements <Cell>())
{
if (oneCell.CellValue == null)
{
continue;
}
string columnName = GetCellText(oneCell, sst);
ClassToExcelColumn column = columns.FirstOrDefault(w => string.CompareOrdinal(w.ColumnName, columnName) == 0);
if (column != null)
{
column.ExcelColumnLetter = GetColumnLetter(oneCell);
}
}
// Log problems
foreach (var column in columns)
{
if (string.IsNullOrWhiteSpace(column.ExcelColumnLetter) && column.IsOptional == false)
{
LogMessage(new ClassToExcelMessage(ClassToExcelMessageType.HeaderProblem, 1, column, "Could not find column in the header row!"));
}
}
}
/// <summary>Use PropertyInfo and column information assign a property a value based on incoming cell data.</summary>
/// <param name="newItem">The entire object</param>
/// <param name="column">Information about the property that corresponds to the column (cell)</param>
/// <param name="oneCell">A single OpenXML cell with data in it</param>
/// <param name="sst">The shared strings table</param>
/// <param name="rowIndex">The row number</param>
private void AssignValue(T newItem, ClassToExcelColumn column, Cell oneCell, SharedStringTable sst, int rowIndex)
{
if (column == null || column.Property == null || oneCell.CellValue == null)
{
return;
}
string stringValue = GetCellText(oneCell, sst);
if (string.IsNullOrEmpty(stringValue))
{
return;
}
var resultType = _stringToPropertyConverter.AssignValue(column.Property, newItem, stringValue);
if (resultType == StringToPropertyConverterEnum.Error || resultType == StringToPropertyConverterEnum.Warning)
{
var classToExcelMessageType = resultType == StringToPropertyConverterEnum.Error ? ClassToExcelMessageType.Error : ClassToExcelMessageType.Warning;
LogMessage(new ClassToExcelMessage(classToExcelMessageType, rowIndex, column, _stringToPropertyConverter.LastMessage));
}
}
/// <summary>Finds or creates a style index. Style indices point to the INDEX position of a cell format. In other words,
/// if I return 5 from this method, it is the 6th element (yes, 6 it is zero based array) in the CellFormats array that will tell Excel
/// how to format the column.</summary>
private uint FindOrCreateStyleIndex(ClassToExcelColumn column)
{
uint styleIndex = 0;
// There are a bunch of built in numeric (includes dates) formatting styles in Excel. Look to see if the user is trying
// to use one of them. If they are, we don't need to create a custome numeric format; otherwise, we do.
// Creating numeric formats helpful links:
// http://stackoverflow.com/questions/16607989/numbering-formats-in-openxml-c-sharp
uint numberFormatId = GetStandardNumericStyle(column.StyleFormat);
if (numberFormatId == NumberFormatDoesNotExist)
{
for (int i = 0; i < _spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.ChildElements.Count; i++)
{
var data = (NumberingFormat)_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.ChildElements[i];
if (data.FormatCode == column.StyleFormat)
{
numberFormatId = data.NumberFormatId;
break;
}
}
if (numberFormatId == NumberFormatDoesNotExist)
{
numberFormatId = _numberFormatId++;
var newNumberingFormat = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(numberFormatId),
FormatCode = StringValue.FromString(column.StyleFormat)
};
_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.AppendChild(newNumberingFormat);
}
}
// Ok, at this point we know the numberFormatId. Is there already an existing CellFormat record point to it?
// If so, great return its index in the CellFormats array; otherwise, create it.
bool foundCellFormat = false;
for (int i = 0; i < _spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements.Count; i++)
{
var data = (CellFormat)_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[i];
if (data.NumberFormatId != null && data.NumberFormatId.Value == numberFormatId)
{
styleIndex = (uint)i;
foundCellFormat = true;
break;
}
}
if (foundCellFormat == false)
{
var newCellFormat = new CellFormat
{
NumberFormatId = numberFormatId,
ApplyNumberFormat = true
};
_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild(newCellFormat);
styleIndex = (uint)_spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements.Count - 1;
}
return(styleIndex);
}
/// <summary>Using reflection, create the columns and pull the attribute information off the class so
/// that we know how to format the columns later.</summary>
protected List <ClassToExcelColumn> CreateColumns(string worksheetName, Type typeOfClass)
{
List <ClassToExcelColumn> columns = new List <ClassToExcelColumn>();
foreach (PropertyInfo property in typeOfClass.GetProperties())
{
if ((property.PropertyType != typeof(string) && property.PropertyType.IsClass) || property.PropertyType.IsArray)
{
continue;
}
if (property.CanWrite == false)
{
LogMessage(ClassToExcelMessageType.Info, string.Format("Ignoring property because it is read-only: {0}", property.Name));
continue;
}
var newData = new ClassToExcelColumn {
ColumnName = property.Name, Order = 99999, Property = property
};
// If the property has a dislay name attribute, use it as the column name.
object firstAttribute = property.GetCustomAttributes(typeof(ClassToExcelAttribute), false).FirstOrDefault();
if (firstAttribute != null)
{
ClassToExcelAttribute displayAttribute = firstAttribute as ClassToExcelAttribute;
if (displayAttribute != null)
{
if (displayAttribute.Ignore)
{
continue; // do not add this column
}
// Does it have a column name?
if (string.IsNullOrWhiteSpace(displayAttribute.ColumnName) == false)
{
newData.ColumnName = displayAttribute.ColumnName;
}
// Is it optional?
newData.IsOptional = displayAttribute.IsOptional;
// Does it have an order?
if (displayAttribute.Order > 0)
{
newData.Order = displayAttribute.Order;
}
// Is it a number, date or boolean?
if (property.PropertyType != typeof(string))
{
if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))
{
newData.IsInteger = true;
}
else if (property.PropertyType == typeof(double) || property.PropertyType == typeof(double?))
{
newData.IsDouble = true;
}
else if (property.PropertyType == typeof(decimal) || property.PropertyType == typeof(decimal?))
{
newData.IsDecimal = true;
}
else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
{
newData.IsDate = true;
}
else if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(bool?))
{
newData.IsBoolean = true;
}
}
// Any special styling?
if (string.IsNullOrWhiteSpace(displayAttribute.StyleFormat) == false && (newData.IsNumber() || newData.IsDate))
{
newData.StyleFormat = displayAttribute.StyleFormat.Trim();
}
}
}
columns.Add(newData);
}
columns = columns.OrderBy(o => o.Order).ThenBy(o => o.ColumnName).ToList();
for (int i = 1; i <= columns.Count; i++)
{
columns[i - 1].ExcelColumnLetter = GetExcelColumnName(i);
}
if (WorksheetColumns.ContainsKey(worksheetName))
{
WorksheetColumns[worksheetName] = columns.OrderBy(o => o.Order).ToList();
}
else
{
WorksheetColumns.Add(worksheetName, columns);
}
return(WorksheetColumns[worksheetName]);
}
/// <summary>Reads data from a work sheet.</summary>
/// <param name="dataStream">The stream that contains the worksheet</param>
/// <param name="worksheetName">The worksheet name/tab that you want to read.</param>
/// <param name="hasHeaderRow">Indicates if the first row in the worksheet is a header row. If it does, we will use it
/// to determine how things should map to the class; otherwise, we will just assue the class is indexed properly with
/// the ExcelMappperAtttribute (Order property) and try to pull the data out that way.</param>
/// <param name="startRow">Starting row. This is a ONE based number, but keep in mind that if hasHeaderRow is true, the header row IS row 1.
/// If the value is null, it is not used.</param>
/// <param name="endRow">Ending row. This is a ONE based number, but keep in mind that if hasHeaderRow is true, the header row IS row 1.
/// If the value is null, it is not used.</param>
/// <returns>List of objects</returns>
public List <T> ReadWorksheet(Stream dataStream, string worksheetName, bool hasHeaderRow = true, int?startRow = null, int?endRow = null)
{
var result = new List <T>();
if (dataStream == null)
{
throw new ArgumentException("You must specify data!");
}
if (string.IsNullOrEmpty(worksheetName))
{
throw new ArgumentException("You must specify a worksheet name!");
}
List <ClassToExcelColumn> columns = CreateColumns(worksheetName, typeof(T));
//Open the Excel file
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(dataStream, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
SharedStringTable sst = sstpart != null ? sstpart.SharedStringTable : null;
Sheet theSheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == worksheetName);
if (theSheet == null)
{
return(result);
}
WorksheetPart theWorksheetPart = (WorksheetPart)workbookPart.GetPartById(theSheet.Id);
Worksheet theWorksheet = theWorksheetPart.Worksheet;
var rows = theWorksheet.Descendants <Row>();
//Loop through the Worksheet rows.
int rowIndex = 0;
foreach (Row oneRow in rows)
{
rowIndex++;
// Use the first row to add columns to DataTable.
if (hasHeaderRow && rowIndex == 1)
{
RemapExcelColumnLetters(oneRow, columns, sst);
}
if (startRow.HasValue && startRow.Value > rowIndex)
{
continue;
}
if (hasHeaderRow == false || rowIndex > 1)
{
//Add rows to DataTable.
var newItem = new T();
foreach (Cell oneCell in oneRow.Elements <Cell>())
{
string columnLetter = GetColumnLetter(oneCell);
ClassToExcelColumn column = columns.FirstOrDefault(w => w.ExcelColumnLetter == columnLetter);
AssignValue(newItem, column, oneCell, sst, rowIndex);
}
result.Add(newItem);
}
if (endRow.HasValue && endRow.Value == rowIndex)
{
break;
}
}
}
return(result);
}
