public static IList <Company> ReadExcelFile1(string path = null, int headerRow = 2, int dataRow = 3)
{
/* Some important notes about header names:
* 1) As the header name is being used for the "property" name of the dynamic object there are many characters that are not valid to be a valid c# property name.
* During the reading of the file, the invalid characters in the header name are replaced using this regex [^A-Za-z0-9_]* with an empty string.
* Then if the return of that replace operation is empty, then the column is named "Col(i)". Where (i) is the zero based column index.
* Examples:
* Column Name---------------Property Name
* Company Id----------------row.CompanyId
* Aims Company Id-----------row.AimsCompanyId
* Some_Id_9-----------------row.Some_Id_9
* (@@@)---------------------row.Col1 - where 1 is the zero based index of that column.
*
* 2) Case sensitivity of the property names does not matter either. As the sender could change the case indiscrimiately.
* 3) If a column is removed that you were expecting, the property will return empty and will not throw an exception.
*/
Console.WriteLine("Running ReadExcelFile1");
/*This example shows reading a Company class from a file. */
if (String.IsNullOrWhiteSpace(path))
{
path = Common.ExcelDataPath;
}
//the parser is designed to read one sheet from a file at a time. Other sheets would require a new parser, or just use the same parser and change the sheet name.
IExcelFileParser parser = new ExcelFileParser(path, "Companies", headerRow, dataRow);
//this is where we will store any parser errors as we parse the file
var fileErrors = new Dictionary <int, IList <string> >();
//this validator provides validation when parsing the columns, and data attributes like [Required] and it will also invoke the IValidatableObject interface
IObjectValidator validator = new ObjectValidator();
//as the rowindex may not start at row 1, get it from the parser
int rowIndex = parser.RowStart;
var companies = new List <Company>();
foreach (dynamic row in parser.ParseFile())
{
List <string> errors = new List <string>();
//create a reference to a custom mapper. this provides complete control over the mapping of the file row to the object, and the interface is skipped
FileValuesMap <Company> mapper = Common.Company_FileMapper;
//this utility performs mapping of the row to the object and invokes column mapping validation
var rowObj = FileIOUtilities.MapObject <Company>(row, rowIndex, validator, mapper, ref errors);
//calling the TryValidate method invoke the data annotation validation, and the IValidatableObject interface
validator.TryValidate(rowObj, ref errors);
companies.Add(rowObj);
if (errors.Count > 0)
{
//we got errors for this row, so add them to the fileErrors dictionary
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
//write all the file errors out to the console
foreach (var errs in fileErrors)
{
foreach (var err in errs.Value)
{
Console.WriteLine("Line:{0}, Error: {1}", errs.Key, err);
}
}
return(companies);
}
/// <summary>
/// Maps the object.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="row">The row.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="validator">The validator.</param>
/// <param name="mapper">The mapper.</param>
/// <param name="errors">The errors.</param>
/// <returns>T.</returns>
/// <exception cref="System.ArgumentNullException">
/// row
/// or
/// validator
/// or
/// errors
/// </exception>
public static T MapObject <T>(dynamic row, int rowIndex, IObjectValidator validator, FileValuesMap <T> mapper, ref List <string> errors)
{
if (row == null)
{
throw new ArgumentNullException("row");
}
if (validator == null)
{
throw new ArgumentNullException("validator");
}
if (errors == null)
{
throw new ArgumentNullException("errors");
}
//if the object is not a dynamic object we do not need to recreate it, just use as is
if (!FileIOHelpers.IsDynamicType(row))
{
return(row);
}
T rowObj = (T)Activator.CreateInstance(typeof(T), true);
IFileRowMapper mapperObj = null;
//a custom mapper. will take priority if exists
if (mapper != null)
{
mapper.Invoke(ref rowObj, rowIndex, row, validator, ref errors);
}
else if ((mapperObj = rowObj as IFileRowMapper) != null)
{
mapperObj.MapValues(rowIndex, row, validator, ref errors);
}
else
{
//last ditch effort to map the values to the object if neither of the main mappers gets called
TryMapValues <T>(rowObj, row, validator, ref errors);
}
return(rowObj);
}
