101 lines
2.9 KiB
C#
101 lines
2.9 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using ExcelLibrary.SpreadSheet;
|
|
using MileageTraker.Web.Utility;
|
|
using MileageTraker.Web.ViewModels.Log;
|
|
|
|
namespace MileageTraker.Web.DAL
|
|
{
|
|
public static class LogImporter
|
|
{
|
|
public static IEnumerable<ImportLogViewModel> Import(string filename)
|
|
{
|
|
var fileInfo = new FileInfo(filename);
|
|
return Import(Workbook.Load(fileInfo.FullName));
|
|
}
|
|
|
|
private static IList<string> GetMissingHeaderColumns(IEnumerable<string> headerColumns)
|
|
{
|
|
var properties = typeof(ImportLogViewModel).GetPropertyNames();
|
|
var pset = new HashSet<string>(properties);
|
|
var cset = new HashSet<string>(headerColumns);
|
|
return pset.Except(cset).ToList();
|
|
}
|
|
|
|
private static IEnumerable<ImportLogViewModel> Import(Workbook workbook)
|
|
{
|
|
var sheet = workbook.Worksheets[0];
|
|
|
|
// get column header -> index
|
|
const int columnCount = 8;
|
|
var columnIndex = GetColumnIndex(sheet.Cells.GetRow(0), columnCount);
|
|
|
|
var missingHeaderColumns = GetMissingHeaderColumns(columnIndex.Keys);
|
|
if (missingHeaderColumns.Any())
|
|
{
|
|
throw new ImportException(string.Format("Excel document missing columns: {0}", string.Join(", ", missingHeaderColumns)));
|
|
}
|
|
|
|
for (var r = 1; r <= sheet.Cells.LastRowIndex; r++)
|
|
{
|
|
var row = sheet.Cells.GetRow(r);
|
|
|
|
var emptyCells =
|
|
Enumerable.Range(0, columnCount)
|
|
.Select(i => row.GetCell(i).StringValue)
|
|
.Count(s => s == "");
|
|
|
|
if (emptyCells > columnCount - 2)
|
|
continue;
|
|
|
|
Func<string, string> getRowValue = i => row.GetCell(columnIndex[i]).StringValue.Trim();
|
|
Func<string, string> getRowDateValue = i =>
|
|
{
|
|
var cell = row.GetCell(columnIndex[i]);
|
|
if (cell.IsEmpty || cell.StringValue == "")
|
|
return null;
|
|
var dateTimeValue = cell.DateTimeValue;
|
|
return dateTimeValue.ToShortDateString();
|
|
};
|
|
|
|
ImportLogViewModel logImportViewModel;
|
|
|
|
try
|
|
{
|
|
logImportViewModel = new ImportLogViewModel
|
|
{
|
|
CityName = getRowValue("Destination City"),
|
|
Date = getRowDateValue("Date"),
|
|
EndOdometer = getRowValue("End Odometer"),
|
|
GasPurchased = getRowValue("Gas Purchased"),
|
|
LogType = getRowValue("Type"),
|
|
Notes = getRowValue("Notes"),
|
|
Purpose = getRowValue("Purpose"),
|
|
VehicleId = getRowValue("Vehicle ID")
|
|
};
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new ImportException(ex.Message + " on row " + (r + 1));
|
|
}
|
|
yield return logImportViewModel;
|
|
}
|
|
}
|
|
|
|
private static IDictionary<string, int> GetColumnIndex(Row headerRow, int columnCount)
|
|
{
|
|
var dictionary = new Dictionary<string, int>(columnCount);
|
|
for (var i = 0; i < columnCount; i++)
|
|
{
|
|
var title = headerRow.GetCell(i).StringValue.Trim();
|
|
if (string.IsNullOrEmpty(title))
|
|
continue;
|
|
//throw new LogImportError("Missing column in the excel document");
|
|
dictionary.Add(title, i);
|
|
}
|
|
return dictionary;
|
|
}
|
|
}
|
|
} |