Beispiel #1
0
        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();
            }
        }
Beispiel #2
0
        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();
            }
        }
Beispiel #3
0
        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();
            }
        }
Beispiel #4
0
        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();
            }
        }