public MainWindow()
{
InitializeComponent();
Templates = Directory.GetFiles(Environment.CurrentDirectory+"\\KPTeml\\").Where(m => m.Contains("Шаблон")).ToArray();
comboBox.ItemsSource = Templates;
var ad= new AllData() { Our = LoadOurData() };
DataContext = ad;
dataGridExtra1.ItemsSource = ad.e1;
dataGridExtraSod.ItemsSource = ad.e2;
dataGridContacts.ItemsSource = ad.Contacts;
}
AllData LoadDataFromExcel(string fn)
{
var ad = new AllData();
try
{
ExcelApp = new Ex.Application()
{
Visible = false
};
ExcelWorkbook = ExcelApp.Workbooks.Open(fn);
var ws = ExcelWorkbook.Worksheets["Свод"];
ad.Address = ws.Cells[4, 3].Value;
ad.ForWho = ws.Cells[5, 3].Value;
ad.ServiceType = ws.Cells[7, 3].Value;
ad.DateStart = o2s(ws.Cells[8, 3].Value);
ad.ContractPeriod = Convert.ToString(ws.Cells[9, 3].Value);
ad.CostMonth = Convert.ToString(Math.Round(ws.Cells[10, 3].Value, 2));
ad.CostMonthString = GetCostText(o2d(ws.Cells[10, 3].Value));
ad.ContractPeriodYears = (o2d(ad.ContractPeriod) / 12).ToString();
ad.DaysToStart = "1";
ad.Date = DateTime.Now.ToString("dd.MM.yyyy");
//////расх материалы
ad.RashMaterials = new List <ExtraItem>();
var wsData = ExcelWorkbook.Worksheets["Данные"];
if (wsData != null)
{
for (int i = 200; i < 400; ++i)
{
if (o2s(wsData.Cells[i, 3].Value).Contains("Расходные материалы для санзон"))
{
while (o2s(wsData.Cells[i + 2, 3].Value).Contains("норма расхода"))
{
var val = o2s(wsData.Cells[i + 1, 7].Value);
double dd;
if (double.TryParse(val, out dd))
{
double dd1;
var val1 = o2s(wsData.Cells[i + 2, 7].Value);
double.TryParse(val1, out dd1);
if (dd != 0)
{
ad.RashMaterials.Add(new ExtraItem()
{
Name = o2s(wsData.Cells[i + 1, 3].Value), Quant = dd1, CostMonth = dd, Price = dd / dd1
});
}
}
i += 2;
}
break;
}
}
}
dataGridRash.ItemsSource = ad.RashMaterials;
//////осн средства
ad.OsnSredstva = new List <ExtraItem>();
var wsOS = ExcelWorkbook.Worksheets["ОС"];
if (wsOS != null)
{
int K = 1;
for (int i = 1; i < 120; ++i)
{
var Cost = o2d(wsOS.Cells[i, 7].Value);
if (Cost != 0)
{
double quant = o2d(wsOS.Cells[i, 6].Value);
string name = o2s(wsOS.Cells[i, 3].Value);
if (!name.ToLower().Contains("итого") && !string.IsNullOrWhiteSpace(name))
{
ad.OsnSredstva.Add(new ExtraItem()
{
Name = name, Quant = quant, CostMonth = Cost, Num = K
});
++K;
}
}
}
}
dataGridOsnSredst.ItemsSource = ad.OsnSredstva;
//////штат расстановка
ad.ShtatRasstanovka = new List <ExtraItem>();
var wsFOT = ExcelWorkbook.Worksheets["ФОТ"];
if (wsFOT != null)
{
int K = 1;
for (int i = 1; i < 150; ++i)
{
var days = o2s(wsFOT.Cells[i, 4].Value);
var start = o2s(wsFOT.Cells[i, 5].Value);
var end = o2s(wsFOT.Cells[i, 6].Value);
var q = o2d(wsFOT.Cells[i, 8].Value);
if (!string.IsNullOrWhiteSpace(days) && !string.IsNullOrWhiteSpace(start) && !string.IsNullOrWhiteSpace(end) && q != 0)
{
string name = o2s(wsFOT.Cells[i, 3].Value);
ad.ShtatRasstanovka.Add(new ExtraItem()
{
Name = name, Quant = q, Smena = days + " c " + start + " до " + end, Num = K
});
++K;
}
}
}
dataGridShtat.ItemsSource = ad.ShtatRasstanovka;
//////себестоимость
ad.Sebestimost = new List <ExtraItem>();
var wsSS = ExcelWorkbook.Worksheets["С-сть"];
if (wsSS != null)
{
int K = 1;
for (int i = 1; i < 150; ++i)
{
string code = o2s(wsSS.Cells[i, 2].Value);
if (code.Contains("_10"))
{
double cost = o2d(wsSS.Cells[i, 4].Value);
{
if (cost != 0)
{
string name = o2s(wsSS.Cells[i, 3].Value);
ad.Sebestimost.Add(new ExtraItem()
{
Name = name, CostMonth = cost
});
}
}
}
}
}
dataGridRashStruct.ItemsSource = ad.Sebestimost;
//////Общее
var wsPAR = ExcelWorkbook.Worksheets["Параметры"];
if (wsPAR != null)
{
int K = 1;
for (int i = 1; i < 50; ++i)
{
string code = o2s(wsPAR.Cells[i, 2].Value);
if (code.Contains("Общая площадь предприятия"))
{
double quant = o2d(wsPAR.Cells[i, 3].Value);
ad.Area = quant;
break;
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
if (ExcelWorkbook != null)
{
ExcelWorkbook.Close(false);
ExcelWorkbook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
}
}
return(ad);
}
AllData LoadDataFromExcel(string fn)
{
var ad = new AllData();
try
{
ExcelApp = new Ex.Application() { Visible = false };
ExcelWorkbook = ExcelApp.Workbooks.Open(fn);
var ws = ExcelWorkbook.Worksheets["Свод"];
ad.Address = ws.Cells[4, 3].Value;
ad.ForWho = ws.Cells[5, 3].Value;
ad.ServiceType = ws.Cells[7, 3].Value;
ad.DateStart = o2s(ws.Cells[8, 3].Value);
ad.ContractPeriod = Convert.ToString(ws.Cells[9, 3].Value);
ad.CostMonth = Convert.ToString(Math.Round(ws.Cells[10, 3].Value,2));
ad.CostMonthString = GetCostText(o2d(ws.Cells[10, 3].Value));
ad.ContractPeriodYears =( o2d(ad.ContractPeriod) / 12).ToString();
ad.DaysToStart = "1";
ad.Date = DateTime.Now.ToString("dd.MM.yyyy");
//////расх материалы
ad.RashMaterials = new List<ExtraItem>();
var wsData= ExcelWorkbook.Worksheets["Данные"];
if (wsData != null)
{
for (int i =200; i < 400; ++i)
{
if (o2s(wsData.Cells[i, 3].Value).Contains("Расходные материалы для санзон"))
{
while (o2s(wsData.Cells[i + 2, 3].Value).Contains("норма расхода"))
{
var val = o2s(wsData.Cells[i + 1, 7].Value);
double dd;
if (double.TryParse(val, out dd))
{
double dd1;
var val1 = o2s(wsData.Cells[i + 2, 7].Value);
double.TryParse(val1, out dd1);
if (dd != 0)
{
ad.RashMaterials.Add(new ExtraItem() { Name = o2s(wsData.Cells[i + 1, 3].Value), Quant = dd1, CostMonth = dd , Price=dd/dd1});
}
}
i += 2;
}
break;
}
}
}
dataGridRash.ItemsSource = ad.RashMaterials;
//////осн средства
ad.OsnSredstva = new List<ExtraItem>();
var wsOS = ExcelWorkbook.Worksheets["ОС"];
if (wsOS != null)
{
int K = 1;
for (int i = 1; i < 120; ++i)
{
var Cost = o2d(wsOS.Cells[i, 7].Value);
if(Cost!=0)
{
double quant= o2d(wsOS.Cells[i, 6].Value);
string name = o2s(wsOS.Cells[i, 3].Value);
if (!name.ToLower().Contains("итого")&&!string.IsNullOrWhiteSpace(name))
{
ad.OsnSredstva.Add(new ExtraItem() { Name = name, Quant = quant, CostMonth = Cost,Num=K});
++K;
}
}
}
}
dataGridOsnSredst.ItemsSource = ad.OsnSredstva;
//////штат расстановка
ad.ShtatRasstanovka = new List<ExtraItem>();
var wsFOT = ExcelWorkbook.Worksheets["ФОТ"];
if (wsFOT != null)
{
int K = 1;
for (int i = 1; i < 150; ++i)
{
var days = o2s(wsFOT.Cells[i, 4].Value);
var start = o2s(wsFOT.Cells[i, 5].Value);
var end= o2s(wsFOT.Cells[i, 6].Value);
var q= o2d(wsFOT.Cells[i, 8].Value);
if (!string.IsNullOrWhiteSpace(days)&&!string.IsNullOrWhiteSpace(start) &&!string.IsNullOrWhiteSpace(end)&&q!=0)
{
string name = o2s(wsFOT.Cells[i, 3].Value);
ad.ShtatRasstanovka.Add(new ExtraItem() { Name = name, Quant = q, Smena=days+" c "+start+" до "+end,Num=K});
++K;
}
}
}
dataGridShtat.ItemsSource = ad.ShtatRasstanovka;
//////себестоимость
ad.Sebestimost = new List<ExtraItem>();
var wsSS = ExcelWorkbook.Worksheets["С-сть"];
if (wsSS != null)
{
int K = 1;
for (int i = 1; i < 150; ++i)
{
string code = o2s(wsSS.Cells[i, 2].Value);
if(code.Contains("_10"))
{
double cost= o2d(wsSS.Cells[i, 4].Value);
{
if(cost!=0)
{
string name= o2s(wsSS.Cells[i, 3].Value);
ad.Sebestimost.Add(new ExtraItem() { Name = name, CostMonth=cost});
}
}
}
}
}
dataGridRashStruct.ItemsSource = ad.Sebestimost;
//////Общее
var wsPAR = ExcelWorkbook.Worksheets["Параметры"];
if (wsPAR != null)
{
int K = 1;
for (int i = 1; i < 50; ++i)
{
string code = o2s(wsPAR.Cells[i, 2].Value);
if (code.Contains("Общая площадь предприятия"))
{
double quant = o2d(wsPAR.Cells[i, 3].Value);
ad.Area = quant;
break;
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
if (ExcelWorkbook != null)
{
ExcelWorkbook.Close(false);
ExcelWorkbook = null;
}
if (ExcelApp != null)
ExcelApp.Quit();
}
return ad;
}