private void WritePlaceColumn(ExcelDoc doc, RaceReport report)
{
for (int i = 0; i < report.Results.Count(); i++)
{
doc.WriteToCell(0, 2, "Place:");
doc.WriteToCell(i + 1, 2, report.Results[i].Place.ToString());
}
}
private void WriteTimeColumn(ExcelDoc doc, RaceReport report)
{
for (int i = 0; i < report.Results.Count(); i++)
{
doc.WriteToCell(0, 1, "Time:");
doc.WriteToCell(i + 1, 1, report.Results[i].FinishingTime.ToString());
}
}
public List <string> Load()
{
using (ExcelDoc excelDoc = new ExcelDoc(path))
{
loaders[fuelReport].Invoke(excelDoc);
}
return(erorrs);
}
private void WriteNameColumn(ExcelDoc doc, RaceReport report)
{
for (int i = 0; i < report.Results.Count(); i++)
{
var currentRunnerId = report.Results[i].Runner.Id;
var currentRunner = _context.Runners.SingleOrDefault(r => r.Id == currentRunnerId);
doc.WriteToCell(0, 0, "Name:");
doc.WriteToCell(i + 1, 0, currentRunner.Name);
}
}
public void Show()
{
ExcelDoc excel = new ExcelDoc();
CreateHeader(excel);
CreateBody(excel);
excel.Show();
}
private void CreateHeader(ExcelDoc excel)
{
int i = 1;
foreach (DataColumn column in _dt.Columns)
{
excel.setValue(1, i, column.ColumnName);
i++;
}
}
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;
}
}
public void HeaderTest()
{
var fixture = new FixtureUtil();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fixture.ExcelSample.FullName, false))
{
var ws = document.WorkbookPart.WorksheetParts.First().Worksheet;
var xl = new ExcelDoc(fixture.ExcelSample);
var first = ws.Elements <HeaderFooter>().First().DifferentFirst;
var odd = ws.Elements <HeaderFooter>().First().DifferentOddEven;
var innertext = ws.Descendants <HeaderFooter>().First().OddHeader.InnerText;
Assert.IsTrue(innertext != null);
}
}
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
ExcelStream.Dispose();
ExcelDoc.Dispose();
}
ExcelStream = null;
ExcelDoc = null;
disposedValue = true;
}
}
public void EffectiveDateTest()
{
string effDate = "2020-04-12";
var fixture = new FixtureUtil();
ExcelDoc doc = new ExcelDoc(fixture.ExcelSample);
var state = doc.Inspect();
var initial = state.EffectiveDate.Value;
Assert.AreEqual("2018-11-26", initial);
state.EffectiveDate.Value = effDate;
var targetDoc = doc.Process(state, fixture.ProcessingDir);
var result = (string)targetDoc.Inspect().EffectiveDate.Value;
Assert.AreEqual(effDate, result);
}
public void RevisionTest()
{
string rev = "20";
var fixture = new FixtureUtil();
ExcelDoc doc = new ExcelDoc(fixture.ExcelSample);
var state = doc.Inspect();
var initial = state.Revision.Value;
Assert.AreEqual("2", initial);
state.Revision.Value = rev;
var targetDoc = doc.Process(state, fixture.ProcessingDir);
var result = (string)targetDoc.Inspect().Revision.Value;
Assert.AreEqual(rev, result);
}
public void InStreamDataTest()
{
excelDoc = new ExcelDoc();
excelDoc.Open();
excelDoc.InStreamData();
excelDoc.Close();
StringBuilder sb = new StringBuilder();
foreach (KeyValuePair <string, ExcelPartNumber> kvp in excelDoc.partNumList)
{
sb.AppendLine(kvp.Key + " " + kvp.Value.restockSODate + " " + kvp.Value.bracketsPerSheet);
}
System.IO.File.WriteAllText(@"\\msw-fp1\user$\wchan\Documents\InventoryManagementAppTest\Part Numbers.txt", sb.ToString());
}
/// <summary>
/// Opens the specified filepath for reading
/// </summary>
/// <returns></returns>
public void OpenExcelReader()
{
if (ExcelDoc != null)
{
ExcelDoc.Close();
ExcelDoc.Dispose();
}
if (ExcelStream != null)
{
ExcelStream.Close();
ExcelStream.Dispose();
}
ExcelStream = System.IO.File.Open(ExcelFilePath, FileMode.Open, FileAccess.Read);
ExcelDoc = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(ExcelStream, false);
}
private void CreateBody(ExcelDoc excel)
{
int i = 2;
foreach (DataRow row in _dt.Rows)
{
for (int j = 0; j < row.ItemArray.Count(); j++)
{
if (row.ItemArray[j].ToString() == string.Empty)
{
continue;
}
excel.setValue(i, (j + 1), row.ItemArray[j].ToString());
}
i++;
}
}
public void PolyMinMaxTest()
{
QuickBooksDataTable itemTable = new ItemDataTable();
QuickBooksDataTable soTable = new SODataTable();
itemTable.BuildTable();
soTable.BuildTable();
using (excelDoc = new ExcelDoc())
{
excelDoc.Open();
excelDoc.InStreamData();
excelDoc.Close();
DataTable minMaxDt = new DataTable().BuildTable(soTable, itemTable, excelDoc.partNumList);
minMaxDt.Write(@"\\msw-fp1\user$\wchan\Documents\Visual Studio 2015\Projects\InventoryManagementApp\InventoryManagementApp\bin\Debug\Test\PolyMinMax.csv");
}
}
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;
}
}
public void Create()
{
var doc = new ExcelDoc();
try
{
var i = 1;
foreach (MileageReport item in _mileageReportList)
{
if (item.IsFailed)
{
doc.setValue(i, 1, item.ToString());
i++;
}
}
doc.SetList(2);
i = 1;
foreach (MileageReport item in _mileageReportList)
{
if (!item.IsFailed)
{
doc.setValue(i, 1, item.ToString());
i++;
}
}
doc.Show();
}
catch
{
doc.Dispose();
}
}
private void ImportOneList(ExcelDoc excel, string date)
{
try
{
using (excel)
{
double i = 2;
string curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
mm.Save();
i++;
curCell = "D" + 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);
}
}
public void StartImport()
{
try
{
using (ExcelDoc excel = new ExcelDoc(FilePath))
{
excel.SetList("Январь");
if (excel.getValue("M2") == null)
{
LogManager.Logger.Debug("В файле {file} на листе Январь в ячейке M2 должен быть указан год!", FilePath);
}
string year = excel.getValue("M2").ToString();
string date = year + "-01-01";
int i = 2;
string curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
int psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToInt32(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
int psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToInt32(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
int mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToInt32(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Январь. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Февраль");
date = year + "-02-01";
//ImportOneList(excel, date);
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
int psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToInt32(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
int psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToInt32(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
int mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToInt32(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Февраль. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Март");
date = year + "-03-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
int psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToInt32(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
int psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToInt32(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
int mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToInt32(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Март. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Апрель");
date = year + "-04-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Апрель. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Май");
date = year + "-05-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Май. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Июнь");
date = year + "-06-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Июнь. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Июль");
date = year + "-07-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Июль. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Август");
date = year + "-08-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Август. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Сентябрь");
date = year + "-09-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Сентябрь. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Октябрь");
date = year + "-10-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Октябрь. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Ноябрь");
date = year + "-11-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Ноябрь. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + i;
}
excel.SetList("Декабрь");
date = year + "-12-01";
i = 2;
curCell = "D" + i;
while (excel.getValue(curCell) != null)
{
curCell = "D" + i;
string number = excel.getValue(curCell).ToString(); // номер машины
curCell = "F" + i;
double gasBegin = 0;
if (excel.getValue(curCell) != null)
{
gasBegin = Convert.ToDouble(excel.getValue(curCell).ToString()); // на начало
}
curCell = "E" + i;
double gas = 0;
if (excel.getValue(curCell) != null)
{
gas = Convert.ToDouble(excel.getValue(curCell).ToString()); // получено
}
curCell = "G" + i;
double gasEnd = 0;
if (excel.getValue(curCell) != null)
{
gasEnd = Convert.ToDouble(excel.getValue(curCell).ToString()); // на конец
}
curCell = "H" + i;
double psn = 0;
if (excel.getValue(curCell) != null)
{
psn = Convert.ToDouble(excel.getValue(curCell).ToString()); // псн
}
curCell = "I" + i;
double psk = 0;
if (excel.getValue(curCell) != null)
{
psk = Convert.ToDouble(excel.getValue(curCell).ToString()); // пск
}
curCell = "J" + i;
double mileage = 0;
if (excel.getValue(curCell) != null)
{
mileage = Convert.ToDouble(excel.getValue(curCell).ToString()); // пробег
}
curCell = "K" + i;
double norm = 0;
if (excel.getValue(curCell) != null)
{
norm = Convert.ToDouble(excel.getValue(curCell).ToString()); // норма
}
MileageMonth mm = new MileageMonth(number, date, gas, gasBegin, gasEnd, norm, psn, psk, mileage);
string res = mm.Save();
if (res != "1")
{
LogManager.Logger.Debug("Не записана строка {i} на листе Декабрь. Ошибка: {res}", i, res);
}
i++;
curCell = "D" + 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);
}
}
public void ProcessRaceReport(RaceReport raceReport)
{
var currentReportsRace = _raceService.GetRace(raceReport.Race.Id.Value);
_context.Races.Attach(currentReportsRace);
raceReport.Race = currentReportsRace;
CheckForPersonalBests(raceReport);
ProcessIndividualResults(raceReport);
var datestring = currentReportsRace.Date.ToShortDateString();
var path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/content/excelfiles/"),
currentReportsRace.RaceName + datestring.Replace('/', '-') + ".xlsx");
ExcelDoc ex = new ExcelDoc();
ex.CreateNewFile();
WriteNameColumn(ex, raceReport);
WriteTimeColumn(ex, raceReport);
WritePlaceColumn(ex, raceReport);
ex.SaveAs(path);
ex.Close();
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
BinaryReader reader = new BinaryReader(fs);
RaceReportBinary binary = new RaceReportBinary
{
RaceId = raceReport.Race.Id,
Data = reader.ReadBytes((Int32)fs.Length),
FileName = currentReportsRace.RaceName + datestring.Replace('/', '-') + ".xlsx"
};
var reportindb = _context.RaceReports.SingleOrDefault(r => r.FileName == binary.FileName);
if (reportindb == null)
{
_context.RaceReports.Add(binary);
}
else
{
reportindb.FileName = binary.FileName;
reportindb.Data = binary.Data;
}
try
{
_context.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
System.Diagnostics.Debug.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
System.Diagnostics.Debug.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
fs.Close();
reader.Close();
System.IO.File.Delete(path);
}
private static void LoadChecks(ExcelDoc excel)
{
throw new NotImplementedException();
}
private void excelClick(object sender, EventArgs e)
{
ExcelDoc excel = new ExcelDoc();
excel.CreateExcelDocument(_dt);
}
