public void makeRepVagOnCleanWays()
{
if (view.getDialogPeriod())
{
List <RepVagOnCleanWays> list = new ReportsDB().getVagOnCleaWays(view.repPeriodBd, view.repPeriodEd, view.selectedStation);
if (list.Count == 0)
{
view.showErrorMessage("Нет данных за выбранный период.");
return;
}
Excel.Application ExcelApp = new Excel.Application();
//Книга.
Excel.Workbook WBE = ExcelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet WSE = (Excel.Worksheet)WBE.Sheets[1];
try
{
int row = 5;
// отбираем строки по путям, считая общее кол-во вагонов на кажд.
var distinctWays = list.GroupBy(l => l.NumWay).Select(c1 =>
new { numWay = c1.First().NumWay, vagAmount = c1.Count() }).ToList();
// заголовок
WSE.Cells[1, 1].Value = "Вагоны на путях очистки станции " + view.selectedStation.Name + " в период с " + view.repPeriodBd.ToString("g") + " по " + view.repPeriodEd.ToString("g");
// ----------
WSE.Cells[3, 1].Value = "№ вагона";
int col = 2;
List <int> distinctVagons = list.GroupBy(l => l.NumVag).Select(c1 => c1.First().NumVag).ToList();
foreach (var item in distinctWays)
{
row = 5;
WSE.Cells[3, col].Value = item.numWay;
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[3, 1], (Excel.Range)WSE.Cells[4, 1]).Merge();
WSE.get_Range((Excel.Range)WSE.Cells[3, col], (Excel.Range)WSE.Cells[3, col + 1]).Merge();
// --
WSE.Cells[4, col].Value = "Дата постановки";
WSE.Cells[4, col + 1].Value = "Дата снятия";
foreach (int vag in distinctVagons)
{
if (distinctWays.IndexOf(item) == 0)
{
WSE.Cells[row, 1].Value = vag;
}
WSE.Cells[row, col].Value = (from v in list where v.NumWay == item.numWay && v.NumVag == vag select v.DtOnWay.ToString("g")).FirstOrDefault();
string dtFrom = (from v in list where v.NumWay == item.numWay && v.NumVag == vag select v.DtFromWay.ToString("g")).FirstOrDefault();
if (dtFrom != "01.01.1900 0:00")
{
WSE.Cells[row, col + 1].Value = dtFrom;
}
row++;
}
col += 2;
}
// -- f o r m a t t i n g --
Excel.Range range = WSE.get_Range((Excel.Range)WSE.Cells[3, 1], (Excel.Range)WSE.Cells[row - 1, col - 1]);
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.WrapText = true;
//range.Columns.AutoFit();
range.ColumnWidth = 13;
range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
// --
}
catch (Exception ex)
{
view.showErrorMessage(ex.Message);
WBE.Close(false, Type.Missing, Type.Missing); // ---- закрыть Excel не сохраняя
}
ExcelApp.Visible = true;
GC.Collect();
}
}
public void makeRepLoadWayStat()
{
if (view.getDialogPeriod())
{
List <RepWaysLoaded> list = new ReportsDB().getWaysLoaded(view.repPeriodBd, view.repPeriodEd);
if (list.Count == 0)
{
view.showErrorMessage("Нет данных за выбранный период.");
return;
}
Excel.Application ExcelApp = new Excel.Application();
//Книга.
Excel.Workbook WBE = ExcelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet WSE = (Excel.Worksheet)WBE.Sheets[1];
try
{
int row = 3;
// отбираем строки по станциям, считая общее время на кажд.
var distinctStations = list.GroupBy(l => l.Station_name).Select(c1 =>
new { station_name = c1.First().Station_name, vag_amount = c1.Sum(l => l.Vag_amount) }).ToList();
// заголовок
WSE.Cells[1, 1].Value = "Загруженность путей и станций предприятия в период с " + view.repPeriodBd.ToString("g") + " по " + view.repPeriodEd.ToString("g") + ", вагонов";
// ----------
int col = 1;
foreach (var item in distinctStations)
{
var waysOfStation = from v in list where v.Station_name == item.station_name select v;
WSE.Cells[row, col].Value = item.station_name;
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row, col], (Excel.Range)WSE.Cells[row, col + waysOfStation.Count()]).Merge();
// --
WSE.Cells[row + 1, col].Value = "№ пути";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 1, col + waysOfStation.Count() - 1]).Merge();
// --
foreach (var subItem in waysOfStation)
{
WSE.Cells[row + 2, col].Value = subItem.Way_num;
WSE.Cells[row + 3, col++].Value = subItem.Vag_amount.ToString();
}
WSE.Cells[row + 1, col].Value = "ИТОГО";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 2, col]).Merge();
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 3, col]).Font.Bold = true;
// --
WSE.Cells[row + 3, col++].Value = item.vag_amount.ToString();
}
// -- f o r m a t t i n g --
Excel.Range range = WSE.get_Range((Excel.Range)WSE.Cells[row, 1], (Excel.Range)WSE.Cells[row + 3, col - 1]);
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
//range.WrapText = true;
//range.Columns.AutoFit();
range.ColumnWidth = 10;
range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
// --
}
catch (Exception ex)
{
view.showErrorMessage(ex.Message);
WBE.Close(false, Type.Missing, Type.Missing); // ---- закрыть Excel не сохраняя
}
ExcelApp.Visible = true;
GC.Collect();
}
}
public void makeRepGfUnlTurnover()
{
if (view.getDialogPeriod())
{
List <RepGfUnlTurnover> list = new ReportsDB().getGfUnloadTurnover(view.repPeriodBd, view.repPeriodEd);
if (list.Count == 0)
{
view.showErrorMessage("Нет данных за выбранный период.");
return;
}
Excel.Application ExcelApp = new Excel.Application();
//Книга.
Excel.Workbook WBE = ExcelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet WSE = (Excel.Worksheet)WBE.Sheets[1];
try
{
int row = 3;
int subRow = 0;
// отбираем строки по станциям, считая общее время на кажд.
var distinctStations = list.GroupBy(l => l.Station_name).Select(c1 =>
new { station_name = c1.First().Station_name }).ToList();
// заголовок
WSE.Cells[1, 1].Value = "Перерабатывающая способность грузовых фронтов с " + view.repPeriodBd.ToString("g") + " по " + view.repPeriodEd.ToString("g") + ", вагонов";
// ----------
int col = 1;
foreach (var item in distinctStations)
{
var unlOfStation = from v in list where v.Station_name == item.station_name select v;
WSE.Cells[row, col].Value = item.station_name;
var distinctGruzs = unlOfStation.GroupBy(l => l.Gruz_name).Select(c1 => c1.First().Gruz_name).ToList();
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row, col], (Excel.Range)WSE.Cells[row, col + distinctGruzs.Count() * 3]).Merge();
// --
var distinctGF = unlOfStation.GroupBy(l => l.Gf_sh).Select(c1 => c1.First().Gf_sh).ToList();
col++;
foreach (var gruz in distinctGruzs)
{
WSE.Cells[row + 1, col].Value = "Выгрузка " + gruz;
WSE.Cells[row + 1, col + 2].Value = "Отклонение";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 1, col + 1]).Merge();
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col + 2], (Excel.Range)WSE.Cells[row + 2, col + 2]).Merge();
WSE.Columns[col + 2].ColumnWidth = 12;
// --
WSE.Cells[row + 2, col].Value = "План";
WSE.Cells[row + 2, col + 1].Value = "Факт";
col += 3;
subRow = row + 3;
foreach (var gf in distinctGF)
{
if (distinctGruzs.IndexOf(gruz) == 0)
{
WSE.Cells[subRow, 1].Value = gf;
}
try
{
WSE.Cells[subRow, col - 2].Value = (from v in unlOfStation where v.Gf_sh == gf && v.Gruz_name == gruz select v.Vag_amount).FirstOrDefault();
}
catch (Exception) { }
String ColFact = Convert.ToChar(65 + ((col - 2 - 1) % 26)).ToString();
if (col - 2 > 27)
{
ColFact = "A" + ColFact;
}
String ColPlan = Convert.ToChar(65 + ((col - 3 - 1) % 26)).ToString();
if (col - 3 > 27)
{
ColPlan = "A" + ColPlan;
}
WSE.Cells[subRow, col - 1].Formula = "=" + ColFact + "" + subRow + "-" + ColPlan + "" + subRow;
subRow++;
}
}
//row = row+3;
//foreach (var gfItem in distinctGF)
//{
// WSE.Cells[row++, col].Value = gfItem.gf_sh;
// //var unlOfGf = from v in distinctGF where v.gf_sh == gfItem.gf_sh.Gf_sh select v;
//}
//foreach (var subItem in unlOfStation)
//{
// WSE.Cells[row + 2, col].Value = subItem.Way_num;
// WSE.Cells[row + 3, col++].Value = subItem.Vag_amount.ToString();
//}
//WSE.Cells[row + 1, col].Value = "ИТОГО";
//// -- f o r m a t t i n g --
//WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 2, col]).Merge();
//WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 3, col]).Font.Bold = true;
//// --
//WSE.Cells[row + 3, col++].Value = item.vag_amount.ToString();
}
// -- f o r m a t t i n g --
Excel.Range range = WSE.get_Range((Excel.Range)WSE.Cells[3, 1], (Excel.Range)WSE.Cells[subRow - 1, col - 1]);
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
WSE.Columns[1].ColumnWidth = 20;
range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
}
catch (Exception ex)
{
view.showErrorMessage(ex.Message);
WBE.Close(false, Type.Missing, Type.Missing); // ---- закрыть Excel не сохраняя
}
ExcelApp.Visible = true;
GC.Collect();
}
}
public void makeRepVagHistory()
{
if (view.getDialogVagHistResult())
{
List <RepVagHist> list = new ReportsDB().getVagHist(view.repVagHistNumVag);
if (list.Count == 0)
{
view.showErrorMessage("Нет данных по запрашиваемому вагону.");
return;
}
Excel.Application ExcelApp = new Excel.Application();
//Книга.
Excel.Workbook WBE = ExcelApp.Workbooks.Add(Type.Missing);
Excel._Worksheet WSE = (Excel.Worksheet)WBE.Sheets[1];
try
{
int row = 3;
// отбираем строки по станциям, считая общее время на кажд.
var distinctStations = list.GroupBy(l => new { l.Station_name, l.DtOnStat }).Select(c1 =>
new { station_name = c1.First().Station_name, hours = c1.Sum(l => l.HoursAtPoint), minutes = c1.Sum(l => l.MinAtPoint), dtOnStat = c1.First().DtOnStat, dtFromStat = c1.Last().DtFromStat }).ToList();
// заголовок
WSE.Cells[1, 1].Value = "Длительность простоя вагона №" + view.repVagHistNumVag + " по путям и станциям предприятия";
// ----------
WSE.Cells[row, 1].Value = "Номер вагона";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row, 1], (Excel.Range)WSE.Cells[row + 2, 1]).Merge();
// --
WSE.Cells[row + 3, 1].Value = view.repVagHistNumVag;
int col = 2;
foreach (var item in distinctStations)
{
var waysOfStation = from v in list where v.Station_name == item.station_name && v.DtOnStat == item.dtOnStat select v;
WSE.Cells[row, col].Value = item.station_name;
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row, col], (Excel.Range)WSE.Cells[row, col + 2 + waysOfStation.Count()]).Merge();
// --
WSE.Cells[row + 1, col + 2].Value = "№ пути";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col + 2], (Excel.Range)WSE.Cells[row + 1, col + 2 + waysOfStation.Count() - 1]).Merge();
// --
WSE.Cells[row + 2, col].Value = "Дата приб.на станц.";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 2, col]).Merge();
// --
WSE.Cells[row + 3, col++].Value = item.dtOnStat;
WSE.Cells[row + 2, col].Value = "Дата отпр.со станц.";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 2, col]).Merge();
// --
WSE.Cells[row + 3, col++].Value = item.dtFromStat;
foreach (var subItem in waysOfStation)
{
WSE.Cells[row + 2, col].Value = subItem.Point;
WSE.Cells[row + 3, col++].Value = subItem.HoursAtPoint.ToString() + " ч " + subItem.MinAtPoint.ToString() + " мин";
}
WSE.Cells[row + 1, col].Value = "ИТОГО";
// -- f o r m a t t i n g --
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 2, col]).Merge();
WSE.get_Range((Excel.Range)WSE.Cells[row + 1, col], (Excel.Range)WSE.Cells[row + 3, col]).Font.Bold = true;
// --
WSE.Cells[row + 3, col++].Value = item.hours.ToString() + " ч " + item.minutes.ToString() + " мин";
}
// -- f o r m a t t i n g --
Excel.Range range = WSE.get_Range((Excel.Range)WSE.Cells[row, 1], (Excel.Range)WSE.Cells[row + 3, col - 1]);
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
//range.WrapText = true;
range.Columns.AutoFit();
range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.Weight = Excel.XlBorderWeight.xlThin;
// --
}
catch (Exception ex)
{
view.showErrorMessage(ex.Message);
WBE.Close(false, Type.Missing, Type.Missing); // ---- закрыть Excel не сохраняя
}
ExcelApp.Visible = true;
GC.Collect();
}
}