I haven't done this before but you can try extracting all the excel worksheet names, filter out the unknown names and then iterate to each of them before you call
bulkcopy.ColumnMappings.Add()
.
For example (Untested and trimmed down for simplicity):
var sheetNames = GetExcelSheetNames("ThePathToYourExcelFileHere");
var unknownNames = sheetNames.Where(o => string.IsNullOrEmpty(o));
SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
bulkcopy.DestinationTableName = ssqltable;
bulkcopy.ColumnMappings.Add("BeneficiaryID", "[BeneficiaryID]");
bulkcopy.ColumnMappings.Add("BeneficiaryName", "[BeneficiaryName]");
bulkcopy.ColumnMappings.Add("BranchNameID", "[BranchNameID]");
bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]");
bulkcopy.ColumnMappings.Add("AccountType", "[AccountType]");
bulkcopy.ColumnMappings.Add("AccountNumber", "[AccountNumber]");
bulkcopy.ColumnMappings.Add("TotalWages", "[TotalWages]");
bulkcopy.ColumnMappings.Add("PaymentDate", "[PaymentDate]");
for(int i = 1; i <= unknownNames.Length; i++){
bulkcopy.ColumnMappings.Add("Unknown" + i.ToString(), "[Unknown" + i.ToString() + "]");
}
The
GetExcelSheetNames()
method is taken from this article:
C# - Retrieve Excel Workbook Sheet Names.[
^]