/// <summary>
/// Reads the given CSV file.
/// </summary>
/// <param name="filePath">The CSV file path.</param>
/// <returns>A <see cref="LogTable"/>.</returns>
private static LogTable ReadCsvFile(string filePath)
{
var returnTable = new LogTable();
returnTable.BeginLoadData();
// AR csv report formatting
const char Delimiter = '|';
try
{
using (var readFile = new StreamReader(filePath))
{
// Skip headers
var line = readFile.ReadLine();
// Get lines
line = readFile.ReadLine();
while (line != null)
{
var newLine = line.Split(Delimiter);
var newRow = returnTable.NewRow();
// Parse account name, date, amount
newRow.AccountNum = newLine[0].Substring(0, 5).Replace("-", string.Empty).Trim();
newRow.Date = DateTime.Parse(newLine[1]);
newRow.Amount = Convert.ToDecimal(newLine[2]);
returnTable.Rows.Add(newRow);
line = readFile.ReadLine();
}
}
}
catch (Exception error)
{
Tools.DebugLog($"Error reading file {filePath}: {error.Message}");
}
returnTable.EndLoadData();
return(returnTable);
}
/// <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);
}