/// <summary>Converts a bunch of rows into a class. The class must be decorated with the ClassToExcelRowAttribute</summary>
public T Convert(List <ClassToExcelRawRow> rows)
{
var result = new T();
foreach (ClassToExcelRowConverterPropertyData item in _properties)
{
ClassToExcelRawRow row = rows.FirstOrDefault(w => w.RowNumber == item.RowNumber);
if (row == null)
{
LogMessage(ClassToExcelMessageType.Error, $"Row number is invalid ({item.RowNumber}) for the '{item.Property.Name}' property");
continue;
}
ClassToExcelRawColumn column = row.Columns.FirstOrDefault(w => w.ColumnLetter == item.ColumnLetter);
if (column == null)
{
LogMessage(ClassToExcelMessageType.Error, $"Column letter is invalid ({item.ColumnLetter}) for the '{item.Property.Name}' property");
continue;
}
var convertResult = _stringToPropertyConverter.AssignValue(item.Property, result, column.Data, item.DecimalPlaces);
if (convertResult == StringToPropertyConverterEnum.Error || convertResult == StringToPropertyConverterEnum.Warning)
{
var classToExcelMessageType = convertResult == StringToPropertyConverterEnum.Error ? ClassToExcelMessageType.Error : ClassToExcelMessageType.Warning;
LogMessage(new ClassToExcelMessage(classToExcelMessageType, item.RowNumber, null, item.ColumnLetter, _stringToPropertyConverter.LastMessage));
}
}
return(result);
}
/// <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="startRow">Starting row (this is a ONE based number)</param>
/// <param name="endRow">Ending row (this is a ONE based number)</param>
/// <returns>List of rows</returns>
public List <ClassToExcelRawRow> ReadWorksheet(Stream dataStream, string worksheetName, int?startRow = null, int?endRow = null)
{
var result = new List <ClassToExcelRawRow>();
if (dataStream == null)
{
throw new ArgumentException("You must specify data!");
}
if (string.IsNullOrEmpty(worksheetName))
{
throw new ArgumentException("You must specify a worksheet name!");
}
//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 rowNumber = 0;
foreach (Row oneRow in rows)
{
rowNumber++;
if (startRow.HasValue && startRow.Value > rowNumber)
{
continue;
}
//Add rows to DataTable.
var newRow = new ClassToExcelRawRow {
RowNumber = rowNumber
};
foreach (Cell oneCell in oneRow.Elements <Cell>())
{
var newColumn = new ClassToExcelRawColumn();
newColumn.ColumnLetter = GetColumnLetter(oneCell);
newColumn.Data = GetCellText(oneCell, sst);
newRow.Columns.Add(newColumn);
}
result.Add(newRow);
if (endRow.HasValue && endRow.Value == rowNumber)
{
break;
}
}
}
return(result);
}