/// <summary>
/// Gets column mapping for a specific class.
/// </summary>
/// <param name="type">The type to get column mapping.</param>
/// <returns>A collection of column mapping between database and the class.</returns>
public static ColumnMapping Load(Type type)
{
var mapping = new ColumnMapping();
var customized = type.GetMethod("TableMapping", Type.EmptyTypes);
if (customized != null && customized.IsStatic)
{
return(customized.Invoke(null, null) as ColumnMapping);
}
foreach (var prop in type.GetProperties())
{
foreach (var attr in prop.GetCustomAttributes(typeof(ColumnMappingAttribute), true))
{
if (attr is not ColumnMappingAttribute info)
{
continue;
}
mapping.Add(info.Name, prop.Name);
}
}
return(mapping);
}
private void DetermineColumns(Worksheet worksheet)
{
string accountHeaderCaption = null, paymentDateCaption = null, idCaption = null, nipCaption = null, invoiceDateCaption = null;
try
{
nipCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => string.Compare(c.ID, ImportColumnName.NIP.ToString(), true) == 0).HeaderText.ToLower());
accountHeaderCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.AccountNumber.ToString()).HeaderText.ToLower());
paymentDateCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.PaymentDate.ToString()).HeaderText.ToLower());
idCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.LP.ToString()).HeaderText.ToLower());
invoiceDateCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.InvoiceDate.ToString()).HeaderText.ToLower());
}
catch (Exception e)
{
throw new SpreadSheetReaderException("Brak podstawowych kolumn w pliku konfiguracyjnym tj. konto bankowe, data zapłaty, lp, data faktury", e);
}
int maxNumOfColumnToRead = Enum.GetNames(typeof(ImportColumnName)).Length; // nip is read
string noteIDsCaption = null, noteAmountCaption = null, noteTitleCaption = null, noteDateCaption = null;
noteIDsCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteID.ToString()).HeaderText.ToLower());
noteAmountCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteAmount.ToString()).HeaderText.ToLower());
noteTitleCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteTitle.ToString()).HeaderText.ToLower());
noteDateCaption = DataFormatHelper.RemovePolishLetters(_columnsConfig.FirstOrDefault(c => c.ID == ImportColumnName.NoteDate.ToString()).HeaderText.ToLower());
for (int column = 1; column <= 75 && ColumnMapping.Count < maxNumOfColumnToRead; column++)
{
var tempCellContent = ((string)((Range)worksheet.Cells[HeaderRow, column]).Formula).ToLower().Trim();
tempCellContent = DataFormatHelper.RemovePolishLetters(tempCellContent);
if (tempCellContent.Contains(_columnPrefixToIgnore) || string.IsNullOrEmpty(tempCellContent))
{
continue;
}
if (tempCellContent.Contains(nipCaption) && !ColumnMapping.ContainsKey(ImportColumnName.NIP))
{
ColumnMapping.Add(ImportColumnName.NIP, column);
}
else if (tempCellContent.Contains(idCaption) && !ColumnMapping.ContainsKey(ImportColumnName.LP))
{
ColumnMapping.Add(ImportColumnName.LP, column);
}
else if (tempCellContent.Contains(accountHeaderCaption) && !ColumnMapping.ContainsKey(ImportColumnName.AccountNumber))
{
ColumnMapping.Add(ImportColumnName.AccountNumber, column);
}
else if (tempCellContent.Contains(paymentDateCaption) && !ColumnMapping.ContainsKey(ImportColumnName.PaymentDate))
{
ColumnMapping.Add(ImportColumnName.PaymentDate, column);
}
else if (_readInvoiceDate && tempCellContent.Contains(invoiceDateCaption) && !ColumnMapping.ContainsKey(ImportColumnName.InvoiceDate))
{
ColumnMapping.Add(ImportColumnName.InvoiceDate, column);
}
else if (noteIDsCaption != null && tempCellContent.Equals(noteIDsCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteID))
{
ColumnMapping.Add(ImportColumnName.NoteID, column);
}
else if (noteAmountCaption != null && tempCellContent.Equals(noteAmountCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteAmount))
{
ColumnMapping.Add(ImportColumnName.NoteAmount, column);
}
else if (noteTitleCaption != null && tempCellContent.Equals(noteTitleCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteTitle))
{
ColumnMapping.Add(ImportColumnName.NoteTitle, column);
}
else if (noteDateCaption != null && tempCellContent.Equals(noteDateCaption, StringComparison.InvariantCultureIgnoreCase) && !ColumnMapping.ContainsKey(ImportColumnName.NoteDate))
{
ColumnMapping.Add(ImportColumnName.NoteDate, column);
}
}
}