/// <summary>
/// Reads all elements from the excel file
/// </summary>
/// <param name="clientFolderName">
/// The path to the excel file.
/// </param>
/// <returns>
/// the list of contacts
/// </returns>
public override List <StdElement> GetAll(string clientFolderName)
{
var result = new List <StdElement>();
var mappingFileName = GetColumnDefinitionFileName(clientFolderName);
var mapping = this.GetColumnDefinition(mappingFileName, typeof(T));
var excelFile = GetFileName(clientFolderName);
this.LogProcessingEvent("Opening Excel file {0} ...", excelFile);
// get the array of values from the first sheet inside the array
var valueArray = OpenXmlHelper.GetValueArrayFromExcelFile(excelFile);
if (valueArray.Length <= 0)
{
this.LogProcessingEvent("no cells inside file - read aborted");
// exit if we don't have cells
return(result);
}
this.LogProcessingEvent(
"{0} values found in {1} rows and {2} columns.",
valueArray.Length,
valueArray.GetLength(0),
valueArray.GetLength(1));
var exceptionCounter = 0;
var exceptionReport = excelFile + ".ExceptionReport.xml";
XmlWriter exceptionFile = null;
// copy the values into objects
for (var rowId = 1; rowId < valueArray.GetLength(0); rowId++)
{
var colIndex = 0;
var newElement = new T();
for (var colId = 0; colId < mapping.Count - 1; colId++)
{
try
{
Tools.SetPropertyValue(newElement, mapping[colIndex].Selector, valueArray[rowId, colId]);
}
catch (Exception ex)
{
exceptionFile = WriteExceptionFile(valueArray, rowId, colId, newElement, exceptionFile, exceptionReport, ex);
exceptionCounter++;
}
colIndex++;
}
result.Add(newElement);
this.LogProcessingEvent(newElement, "contact read");
}
if (exceptionCounter > 0)
{
if (exceptionFile != null)
{
exceptionFile.WriteEndElement();
exceptionFile.WriteEndDocument();
exceptionFile.Close();
}
this.LogProcessingEvent(
"{0} exceptions while reading data ... see exception report ({1}) for details.",
exceptionCounter,
exceptionReport);
}
this.LogProcessingEvent("cleaning up entities");
CleanUpEntities(result);
this.LogProcessingEvent("reading finished");
return(result);
}