When handling Excel imports, hardcoding column indices or names can be fragile. A more resilient approach involves decoupling the Excel column headers from database model properties using an XML configuration file. This configuration maps the Excel header names to the corresponding database fields, allowing flexibility if the spreadsheet template changes.

Mapping Configuration Model

We start by defining a structure to hold the directional mappings between Excel headers and database properties. Using ganeric dictionaries provides better type safety than hashtables.

public class ColumnMappingProfile
{
    public Dictionary<string, string> ExcelToDbMap { get; set; } = new Dictionary<string, string>();
    public Dictionary<string, string> DbToExcelMap { get; set; } = new Dictionary<string, string>();
}

Parsing the XML Configuration

The following method reads the XML configuration and populates the mapping dictionaries. Using LINQ to XML provides a cleaner approach to querying the document. Only columns marked as enabled are included in the mapping.

public static ColumnMappingProfile LoadMappingConfig(string configPath)
{
    var profile = new ColumnMappingProfile();
    var doc = XDocument.Load(configPath);

    var columns = doc.Descendants("ColumnDef");
    foreach (var col in columns)
    {
        bool isEnabled = (string)col.Attribute("Active") == "true";
        if (!isEnabled) continue;

        string dbField = col.Element("DatabaseField").Value;
        string excelCol = col.Element("SpreadsheetHeader").Value;

        if (!profile.ExcelToDbMap.ContainsKey(excelCol))
        {
            profile.ExcelToDbMap[excelCol] = dbField;
        }
        if (!profile.DbToExcelMap.ContainsKey(dbField))
        {
            profile.DbToExcelMap[dbField] = excelCol;
        }
    }
    return profile;
}

Converting Excel Stream to DataTable

Using NPOI, we can convert the uploaded file stream into a DataTable. During this process, the Excel headers are replaced with the mapped database property names based on our XML profile.

public static DataTable TransformXlsxToDataTable(Stream inputStream, Dictionary<string, string> mapping = null, bool skipNoteRow = false)
{
    var resultTable = new DataTable();
    using (inputStream)
    {
        var workbook = new XSSFWorkbook(inputStream);
        var currentSheet = workbook.GetSheetAt(0);

        int startRow = currentSheet.FirstRowNum;
        if (skipNoteRow) startRow++;

        var headerRow = currentSheet.GetRow(startRow);
        var activeColumns = new List<int>();

        for (int c = 0; c < headerRow.LastCellNum; c++)
        {
            var cellValue = ExtractCellValue(headerRow.GetCell(c) as XSSFCell);
            string colName;
            if (cellValue == null || string.IsNullOrWhiteSpace(cellValue.ToString()))
            {
                colName = $"Col_{c}";
            }
            else
            {
                colName = mapping != null && mapping.ContainsKey(cellValue.ToString())
                    ? mapping[cellValue.ToString()]
                    : cellValue.ToString();
            }
            resultTable.Columns.Add(colName);
            activeColumns.Add(c);
        }

        for (int r = startRow + 1; r <= currentSheet.LastRowNum; r++)
        {
            var dataRow = resultTable.NewRow();
            bool hasData = false;
            var row = currentSheet.GetRow(r);
            if (row == null) continue;

            foreach (int colIdx in activeColumns)
            {
                var cell = row.GetCell(colIdx);
                if (cell != null && cell.CellType == CellType.Numeric)
                {
                    dataRow[colIdx] = DateUtil.IsCellDateFormatted(cell)
                        ? cell.DateCellValue
                        : cell.NumericCellValue;
                }
                else
                {
                    dataRow[colIdx] = ExtractCellValue(cell as XSSFCell);
                }

                if (dataRow[colIdx] != null && !string.IsNullOrWhiteSpace(dataRow[colIdx].ToString()))
                {
                    hasData = true;
                }
            }
            if (hasData) resultTable.Rows.Add(dataRow);
        }
    }
    return resultTable;
}

private static object ExtractCellValue(XSSFCell cell)
{
    if (cell == null) return null;
    return cell.CellType switch
    {
        CellType.Blank => null,
        CellType.Boolean => cell.BooleanCellValue,
        CellType.Numeric => cell.NumericCellValue,
        CellType.String => cell.StringCellValue,
        CellType.Error => cell.ErrorCellValue,
        _ => "=" + cell.CellFormula,
    };
}

DataTable to Generic List Conversion

Once the DataTable contains the database-mapped column names, we can easily convert it into a strongly typed list using reflection. This method also handles nullable types and boolean conversions gracefully.

public static List<T> MapToList<T>(this DataTable table)
{
    var entities = new List<T>();
    var properties = typeof(T).GetProperties().ToList();

    foreach (DataRow row in table.Rows)
    {
        T entity = Activator.CreateInstance<T>();
        foreach (DataColumn column in table.Columns)
        {
            var prop = properties.FirstOrDefault(p => p.Name == column.ColumnName);
            if (prop == null || Convert.IsDBNull(row[column])) continue;

            try
            {
                object convertedValue;
                if (Nullable.GetUnderlyingType(prop.PropertyType) != null)
                {
                    convertedValue = Convert.ChangeType(row[column], Nullable.GetUnderlyingType(prop.PropertyType));
                }
                else if (prop.PropertyType == typeof(bool))
                {
                    string valStr = row[column].ToString();
                    convertedValue = valStr.Equals("1") || valStr.Equals("true", StringComparison.OrdinalIgnoreCase);
                }
                else
                {
                    convertedValue = Convert.ChangeType(row[column], prop.PropertyType);
                }
                prop.SetValue(entity, convertedValue, null);
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"Failed to map property '{prop.Name}'. {ex.Message}", ex);
            }
        }
        entities.Add(entity);
    }
    return entities;
}

API Endpoint for File Upload

The controller action handles the HTTP request, triggers the parsing logic, processes the data, and generates a downloadable error report for failed entries using the reverse mapping configuration.

[HttpPost, Route("api/Products/UploadExcel")]
public IActionResult UploadExcel()
{
    var uploadedFiles = Request.Form.Files;
    var validRecords = new List<ProductEntity>();
    var failedRecords = new List<ProductEntity>();

    string configPath = HostingEnvironment.MapPath("/Config/ProductMapping.xml");
    var mappingProfile = ConfigLoader.LoadMappingConfig(configPath);

    try
    {
        foreach (var file in uploadedFiles)
        {
            var dataTable = TransformXlsxToDataTable(file.OpenReadStream(), mappingProfile.ExcelToDbMap);
            validRecords = dataTable.MapToList<ProductEntity>();
        }

        // Apply business validation logic here
        // Separate validRecords and failedRecords accordingly

        if (validRecords.Any())
        {
            BulkInsertHelper.InsertBatch(validRecords, "ProductTable");
        }

        string errorFileUrl = string.Empty;
        if (failedRecords.Any())
        {
            var errorTable = failedRecords.ToDataTable(mappingProfile.DbToExcelMap);
            errorFileUrl = FileSaver.Save(errorTable, "ImportErrors.xlsx");
        }

        return Ok(new { 
            Errors = failedRecords.Take(100), 
            ErrorCount = failedRecords.Count, 
            Message = "Import completed", 
            ErrorReportUrl = errorFileUrl 
        });
    }
    catch (Exception ex)
    {
        return BadRequest(new { Message = "Import failed", Error = ex.Message });
    }
}

XML Configuration Example

Here is an example of the XML mapping configuration. The Active attribute determines whether the column should be processed during import or ignored.

<?xml version="1.0" encoding="utf-8" ?>
<MappingDefinitions>
  <ColumnDef Active="true">
    <DatabaseField>ProductName</DatabaseField>
    <SpreadsheetHeader>Product Name</SpreadsheetHeader>
  </ColumnDef>
  <ColumnDef Active="true">
    <DatabaseField>FailureReason</DatabaseField>
    <SpreadsheetHeader>Reason</SpreadsheetHeader>
  </ColumnDef>
  <ColumnDef Active="true">
    <DatabaseField>CreatedAt</DatabaseField>
    <SpreadsheetHeader>Creation Date</SpreadsheetHeader>
  </ColumnDef>
  <ColumnDef Active="true">
    <DatabaseField>UpdatedAt</DatabaseField>
    <SpreadsheetHeader>Update Date</SpreadsheetHeader>
  </ColumnDef>
</MappingDefinitions>