/// <summary>
/// Reads the given Excel file.
/// </summary>
/// <param name="filePath">The Excel file path.</param>
/// <param name="progress">The progress reporter.</param>
/// <returns>A <see cref="LogTable"/>.</returns>
private static LogTable ReadXlFile(string filePath, IProgress <int> progress = null)
{
var returnTable = new LogTable();
var excelApp = new Excel.Application
{
DisplayAlerts = false,
ScreenUpdating = false,
Visible = true
};
var workbooks = excelApp.Workbooks;
returnTable.BeginLoadData();
var thisWorkBook = workbooks.Open(filePath, ReadOnly: true);
// Initialize progress reporting
var rowsRead = 0;
var totalRows = thisWorkBook.Sheets.OfType <Excel.Worksheet>().Sum(w => w.UsedRange.Rows.Count - 1); // Minus header row
progress?.Report(0);
try
{
foreach (var thisSheet in thisWorkBook.Sheets.OfType <Excel.Worksheet>())
{
var thisRange = thisSheet.UsedRange;
var thisDate = Convert.ToDateTime(thisSheet.Name, Values.AppCulture);
// Get column headers
var colDict = new Dictionary <int, int>();
for (var col = 1; col <= thisRange.Columns.Count; col++)
{
var headerName = Convert.ToString(thisRange.Cells[1, col].Value2);
var colName = returnTable.Columns.OfType <DataColumn>()
.First(c => c.Caption == headerName || c.ColumnName == headerName).ColumnName;
var colIndex = returnTable.Columns.IndexOf(colName);
if (colIndex > 0)
{
colDict.Add(col, colIndex);
continue;
}
Tools.DebugLog($"Error importing column '{colName}': column not found in target table.");
}
// Read each row below the column headers
for (var row = 2; row <= thisRange.Rows.Count; row++)
{
var newRow = returnTable.NewRow();
newRow.Date = thisDate;
for (var col = 1; col <= thisRange.Columns.Count; col++)
{
if (colDict.ContainsKey(col))
{
// Safe casts in case cell values are null
var cellValue = Convert.ToString(thisRange.Cells[row, col].Value2) ?? string.Empty;
if (returnTable.Columns[colDict[col]].DataType == typeof(bool))
{
newRow[colDict[col]] = cellValue.ToUpper() == Values.BoolExportString;
}
else
{
newRow[colDict[col]] = cellValue;
}
}
}
returnTable.Add(newRow);
// Report progress
rowsRead++;
progress?.Report(100 * rowsRead / totalRows);
}
}
}
catch (Exception error)
{
Tools.DebugLog(error.Message);
}
finally
{
thisWorkBook.Close();
workbooks.Close();
}
excelApp.Quit();
returnTable.EndLoadData();
return(returnTable);
}