110 lines
3.3 KiB
C#
110 lines
3.3 KiB
C#
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Text.RegularExpressions;
|
|
using ExcelLibrary.SpreadSheet;
|
|
using MileageTraker.Web.Models;
|
|
using MileageTraker.Web.Utility;
|
|
|
|
namespace MileageTraker.Web.DAL
|
|
{
|
|
public static class VehicleImporter
|
|
{
|
|
private const string WorksheetName = "Vehicles";
|
|
private const string WorksheetTitle = "EAST TENNESSEE HUMAN RESOURCE AGENCY";
|
|
|
|
public static void Export(IEnumerable<Vehicle> vehicles, string filename)
|
|
{
|
|
ExcelWriter<Vehicle>.WriteXls(vehicles, filename, WorksheetTitle, WorksheetName);
|
|
}
|
|
|
|
public static byte[] Export(IEnumerable<Vehicle> vehicles)
|
|
{
|
|
return ExcelWriter<Vehicle>.WriteXls(vehicles, WorksheetTitle, WorksheetName);
|
|
}
|
|
|
|
public static IEnumerable<City> ImportCities(string filename)
|
|
{
|
|
var fileInfo = new FileInfo(filename);
|
|
return ImportCities(Workbook.Load(fileInfo.FullName));
|
|
}
|
|
|
|
private static IEnumerable<City> ImportCities(Workbook workbook)
|
|
{
|
|
var sheet = workbook.Worksheets[0];
|
|
|
|
for (var r = sheet.Cells.FirstRowIndex + 1; r <= sheet.Cells.LastRowIndex; r++)
|
|
{
|
|
var row = sheet.Cells.GetRow(r);
|
|
|
|
var city = new City
|
|
{
|
|
County = row.GetCell(0).StringValue.Trim(),
|
|
Name = row.GetCell(1).StringValue.Trim()
|
|
};
|
|
|
|
yield return city;
|
|
}
|
|
}
|
|
|
|
public static IEnumerable<Vehicle> Import(string filename)
|
|
{
|
|
var fileInfo = new FileInfo(filename);
|
|
return Import(Workbook.Load(fileInfo.FullName));
|
|
}
|
|
|
|
private static IEnumerable<Vehicle> Import(Workbook workbook)
|
|
{
|
|
var sheet = workbook.Worksheets[0];
|
|
var first = true;
|
|
for (var r = sheet.Cells.FirstRowIndex; r <= sheet.Cells.LastRowIndex; r++)
|
|
{
|
|
var row = sheet.Cells.GetRow(r);
|
|
if (row.LastColIndex != 11)
|
|
continue;
|
|
if (first)
|
|
{
|
|
first = false;
|
|
continue;
|
|
}
|
|
|
|
var vehicle = new Vehicle();
|
|
int key;
|
|
if (!int.TryParse(row.GetCell(0).StringValue.Trim(), out key))
|
|
continue; // WARN
|
|
|
|
vehicle.Key = key;
|
|
vehicle.VehicleId = row.GetCell(1).StringValue.Trim();
|
|
vehicle.ModelYear = row.GetCell(2).StringValue.Trim();
|
|
|
|
var makeModel = row.GetCell(3).StringValue.Trim();
|
|
const string pattern = @"(\w+)\s+(.+)";
|
|
var make = Regex.Replace(makeModel, pattern, "$1");
|
|
var model = Regex.Replace(makeModel, pattern, "$2");
|
|
vehicle.Make = make;
|
|
vehicle.CarModel = model;
|
|
|
|
vehicle.Color = row.GetCell(4).StringValue.Trim();
|
|
if (vehicle.Color == "0")
|
|
vehicle.Color = "Unknown";
|
|
vehicle.Type = row.GetCell(5).StringValue.Trim();
|
|
vehicle.Vin = row.GetCell(6).StringValue.Trim().Replace('O','0').Substring(0,17);
|
|
vehicle.Price = decimal.Parse(row.GetCell(7).StringValue.Trim());
|
|
vehicle.PurDate = row.GetCell(8).StringValue.Trim();
|
|
vehicle.TagNumber = row.GetCell(9).StringValue.Trim();
|
|
vehicle.Prog = row.GetCell(10).StringValue.Trim();
|
|
vehicle.Assigned = row.GetCell(11).StringValue.Trim();
|
|
vehicle.Assigned = Regex.Replace(vehicle.Assigned, @"(\w+),\s*(\w+)", "$2 $1");
|
|
var match = Regex.Match(vehicle.Assigned, @"\s*\((?<Notes>.*)\)\s*");
|
|
if (match.Success)
|
|
{
|
|
vehicle.Notes = match.Groups["Notes"].Value;
|
|
vehicle.Assigned = Regex.Replace(vehicle.Assigned, @"\s*\(.*\)\s*", " ").Trim();
|
|
}
|
|
if (vehicle.Assigned == "Unassigned")
|
|
vehicle.Assigned = null;
|
|
|
|
yield return vehicle;
|
|
}
|
|
}
|
|
}
|
|
} |