private void ReadFile(string filename)
{
try
{
using (ExcelDoc excelDoc = new ExcelDoc(filename))
{
try
{
excelDoc.SetList("Расходы по а-м");
string grz = (excelDoc.getValue("B4") != null) ? excelDoc.getValue("B4").ToString() : string.Empty;
Car car = GetCar(grz);
if (car == null)
{
string driverFIO = (excelDoc.getValue("B5") != null) ? excelDoc.getValue("B5").ToString() : string.Empty;
DriverList driverList = DriverList.getInstance();
Driver driver = driverList.getItemByFIO(driverFIO);
if (driver != null)
{
DriverCarList driverCarList = DriverCarList.getInstance();
car = driverCarList.GetCar(driver);
}
if (car == null)
_mileageReportList.Add(new MileageReport(null, string.Concat("Не найден автомобиль: ", grz, " сотрудник: ", driverFIO, ". Файл: ", filename)));
}
if (car != null)
{
string value = (excelDoc.getValue("C8") != null) ? excelDoc.getValue("C8").ToString() : string.Empty;
SetMileage(car, value);
}
}
catch (IndexOutOfRangeException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
catch (OverflowException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Указан слишком большой пробег в файле: ", filename)));
}
}
}
catch
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
}
public void StartImport()
{
try
{
using (ExcelDoc excel = new ExcelDoc(FilePath))
{
int i = 7;
string curCell = "H" + i;
while (excel.getValue(curCell) != null)
{
curCell = "A" + i;
if ((excel.getValue(curCell) == null) ||
((excel.getValue(curCell).ToString().ToUpper() != "AM") && (excel.getValue(curCell).ToString().ToUpper() != "АМ")))
{
curCell = "H" + i;
i++;
continue;
}
curCell = "H" + i;
string number = excel.getValue(curCell).ToString();
curCell = "D" + i;
DateTime dateBegin;
DateTime.TryParse(excel.getValue1(curCell).ToString(), out dateBegin);
curCell = "E" + i;
DateTime dateEnd;
DateTime.TryParse(excel.getValue1(curCell).ToString(), out dateEnd);
for (DateTime date = dateBegin; date <= dateEnd; date = date.AddDays(1))
{
Tabel tabel = new Tabel(number, date) { Comment = "businessTrip" };
tabel.Save();
}
curCell = "H" + i;
i++;
}
}
}
catch (NullReferenceException ex)
{
LogManager.Logger.Error(ex, "Error in file {file}", FilePath);
}
catch (COMException ex)
{
LogManager.Logger.Error(ex, "Error in file {file}", FilePath);
}
}
private void ReadFile(string filename)
{
try
{
using (ExcelDoc excelDoc = new ExcelDoc(filename))
{
try
{
excelDoc.SetList("Расходы по а-м");
string grz = (excelDoc.getValue("B4") != null) ? excelDoc.getValue("B4").ToString() : string.Empty;
Car car = GetCar(grz);
if (car == null)
{
string driverFIO = (excelDoc.getValue("B5") != null) ? excelDoc.getValue("B5").ToString() : string.Empty;
DriverList driverList = DriverList.getInstance();
Driver driver = driverList.getItemByFIO(driverFIO);
if (driver != null)
{
DriverCarList driverCarList = DriverCarList.getInstance();
car = driverCarList.GetCar(driver);
}
if (car == null)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Не найден автомобиль: ", grz, " сотрудник: ", driverFIO, ". Файл: ", filename)));
}
}
if (car != null)
{
string value = (excelDoc.getValue("C8") != null) ? excelDoc.getValue("C8").ToString() : string.Empty;
SetMileage(car, value);
}
}
catch (IndexOutOfRangeException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
catch (OverflowException)
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Указан слишком большой пробег в файле: ", filename)));
}
}
}
catch
{
_mileageReportList.Add(new MileageReport(null, string.Concat("Ошибка при чтении файла: ", filename)));
}
}
private string GetWaBillFullNumber(int currentNumber)
{
string[] wayBillFullNumber = _excelDoc.getValue("AM4").ToString().Split('/');
wayBillFullNumber[1] = (currentNumber < 10) ? "0" : string.Empty;
wayBillFullNumber[1] += currentNumber;
StringBuilder sb = new StringBuilder();
foreach (string item in wayBillFullNumber)
{
if (sb.Length > 0)
{
sb.Append("/");
}
sb.Append(item);
}
return(sb.ToString());
}
private static void LoadNeste(ExcelDoc excel)
{
int i = 4; //начальный индекс
string currentCell = "A" + i;
while (excel.getValue(currentCell) != null)
{
if (excel.getValue(currentCell).ToString() == "Grand Total")
break;
currentCell = "B" + i;
if (excel.getValue(currentCell) != null)
{
i++;
currentCell = "A" + i;
continue;
}
currentCell = "A" + i;
string number = excel.getValue(currentCell).ToString().Split(' ')[1]; //split example Карта: 7105066553656018
FuelCard fuelCard = fuelCardList.getItem(number);
if (fuelCard == null)
{
i++;
erorrs.Add("Не найдена карта №" + number); //throw new NullReferenceException("Не найдена карта №" + number);
continue;
}
currentCell = "C" + i;
DateTime datetime;
DateTime.TryParse(excel.getValue(currentCell).ToString(), out datetime);//присутствует время, не забываем убирать
currentCell = "D" + i;
string engineTypeName = excel.getValue(currentCell).ToString();
EngineType engineType = GetEngineType(engineTypeName);
currentCell = "E" + i;
double value;
double.TryParse(excel.getValue(currentCell).ToString(), out value);
Fuel fuel = new Fuel(fuelCard, datetime.Date, engineType);
fuel.AddValue(value);
fuel.Save();
i++;
currentCell = "A" + i;
}
}
private static void LoadPetrol(ExcelDoc excel)
{
int i = 4; //начальный индекс
string currentCell = "B" + i;
while (excel.getValue(currentCell) != null)
{
currentCell = "D" + i;
string number = excel.getValue(currentCell).ToString();
FuelCard fuelCard = fuelCardList.getItem(number);
if (fuelCard == null)
{
i++;
currentCell = "B" + i;
erorrs.Add("Не найдена карта №" + number); //throw new NullReferenceException("Не найдена карта №" + number);
continue;
}
currentCell = "B" + i;
string dateString = excel.getValue1(currentCell).ToString();
DateTime datetime;
DateTime.TryParse(dateString, out datetime);//присутствует время, не забываем убирать
currentCell = "G" + i;
string engineTypeName = excel.getValue(currentCell).ToString();
EngineType engineType = GetEngineType(engineTypeName);
currentCell = "H" + i;
double value;
double.TryParse(excel.getValue(currentCell).ToString(), out value);
Fuel fuel = new Fuel(fuelCard, datetime.Date, engineType);
fuel.AddValue(value);
fuel.Save();
i++;
currentCell = "B" + i;
}
}