Example #1
0
        public void CreateXlsReport(bool Use2Pos, ReportInfo Info, List <DeviceItemWithParams> Data, bool PrivateDocument, bool NoPrint)
        {
            m_PrivateDocument = PrivateDocument;

            var fi = new FileInfo(TemplateXlsFilePath);

            if (fi.Exists)
            {
                m_XlApp = new Microsoft.Office.Interop.Excel.Application();

                try
                {
                    m_XlWorkBook = m_XlApp.Workbooks.Open(TemplateXlsFilePath, m_MissValue, m_MissValue, m_MissValue,
                                                          m_MissValue, m_MissValue, m_MissValue, m_MissValue,
                                                          m_MissValue, m_MissValue, m_MissValue, m_MissValue, m_MissValue,
                                                          m_MissValue, m_MissValue);
                    try
                    {
                        m_XlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_XlWorkBook.Worksheets.Item[1];
                        m_XlWorkSheet.EnableSelection =
                            Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoRestrictions;

                        int pageCount;

                        var groupedData = Data.GroupBy(Arg => Arg.GeneralInfo.Code).OrderBy(Arg => Arg.Key).ToList();

                        var actualNumOfItemsInFirstPage = Use2Pos
                            ? NUM_OF_ITEMS_IN_FIRST_PAGE / 2
                            : NUM_OF_ITEMS_IN_FIRST_PAGE;
                        var actualNumOfItemsRestPage = Use2Pos
                            ? NUM_OF_ITEMS_IN_THE_REST_OF_THE_PAGES / 2
                            : NUM_OF_ITEMS_IN_THE_REST_OF_THE_PAGES;

                        if (groupedData.Count <= actualNumOfItemsInFirstPage)
                        {
                            pageCount = 1;
                            m_XlWorkSheet.Range["A50", "O99"].Delete(
                                Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
                        }
                        else
                        {
                            var itemsWithoutFirstPage = Data.Count - actualNumOfItemsInFirstPage;
                            pageCount = 1 + itemsWithoutFirstPage / actualNumOfItemsRestPage;

                            if (itemsWithoutFirstPage % actualNumOfItemsRestPage != 0)
                            {
                                pageCount++;
                            }
                        }

                        PopulateHeader(Data, Info, pageCount);
                        PopulateBody(Use2Pos, groupedData, pageCount);

                        m_XlWorkSheet.PageSetup.CenterFooter = Info.GroupName;
                        m_XlWorkSheet.PageSetup.LeftFooter   = Info.CustomerName;
                        m_XlWorkBook.CheckCompatibility      = false;
                        if (!NoPrint)
                        {
                            m_XlWorkBook.PrintOutEx(1, pageCount, Copies, false, PrinterName, false, true, m_MissValue,
                                                    true);
                        }

                        if (SaveToFile)
                        {
                            m_XlApp.DisplayAlerts = false;
                            m_XlWorkBook.SaveAs(SaveXlsFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
                                                m_MissValue, m_MissValue,
                                                false, false,
                                                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                                m_MissValue, m_MissValue,
                                                m_MissValue, m_MissValue, m_MissValue);
                            m_XlApp.DisplayAlerts = true;
                        }
                    }
                    finally
                    {
                        m_XlWorkBook.Close(false, m_MissValue, m_MissValue);
                    }
                }
                finally
                {
                    m_XlApp.Quit();
                    m_XlApp = null;
                }
            }
            else
            {
                throw new FileNotFoundException(String.Format("Template '{0}' not found", TemplateXlsFilePath));
            }
        }
Example #2
0
        private void PopulateHeader(ICollection <DeviceItemWithParams> Data, ReportInfo Info, int PageCount)
        {
            m_XlWorkSheet.Range["C2", m_MissValue].Value2 = Info.ModuleType;
            m_XlWorkSheet.Range["C3", m_MissValue].Value2 = Info.GroupName;
            m_XlWorkSheet.Range["I2", m_MissValue].Value2 = DateTime.Now.ToShortDateString();
            m_XlWorkSheet.Range["I3", m_MissValue].Value2 = PageCount.ToString(CultureInfo.InvariantCulture);
            m_XlWorkSheet.Range["M2", m_MissValue].Value2 = Data.Count;
            m_XlWorkSheet.Range["M3", m_MissValue].Value2 = Info.CustomerName;

            if (Info.Conditions.Any(C => C.Name == "SL_ITM"))
            {
                m_XlWorkSheet.Range["F8", m_MissValue].Value2 =
                    Info.Conditions.First(C => C.Name == "SL_ITM").Value.ToString(CultureInfo.InvariantCulture);
            }
            else
            {
                m_XlWorkSheet.Range["F8", m_MissValue].Value2 = "-";
            }

            if (Info.Conditions.Any(C => C.Name == "BVT_I"))
            {
                m_XlWorkSheet.Range["I8", m_MissValue].Value2 = m_XlWorkSheet.Range["I9", m_MissValue].Value2 =
                    Info.Conditions.First(C => C.Name == "BVT_I").Value.ToString(CultureInfo.InvariantCulture);
            }
            else
            {
                m_XlWorkSheet.Range["I8", m_MissValue].Value2 = m_XlWorkSheet.Range["I9", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["E15", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "RG"))
            {
                (m_XlWorkSheet.Range["E16", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "RG").Max ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["E16", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["F15", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "IGT"))
            {
                (m_XlWorkSheet.Range["F16", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "IGT").Max ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["F16", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["G15", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "VGT"))
            {
                (m_XlWorkSheet.Range["G16", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "VGT").Max ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["G16", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["H15", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "IH"))
            {
                (m_XlWorkSheet.Range["H16", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "IH").Max ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["H16", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["I15", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "IL"))
            {
                (m_XlWorkSheet.Range["I16", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "IL").Max ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["I16", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["J15", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "VTM"))
            {
                (m_XlWorkSheet.Range["J16", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "VTM").Max ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["J16", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["K16", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "VRRM"))
            {
                (m_XlWorkSheet.Range["K15", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "VRRM").Min ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["K15", m_MissValue].Value2 = "-";
            }

            m_XlWorkSheet.Range["L16", m_MissValue].Value2 = "-";
            if (Info.Normatives.Any(N => N.Name == "VDRM"))
            {
                (m_XlWorkSheet.Range["L15", m_MissValue].Value2 = Info.Normatives.First(N => N.Name == "VDRM").Min ?? 0.0f).ToString();
            }
            else
            {
                m_XlWorkSheet.Range["L15", m_MissValue].Value2 = "-";
            }

            if (!m_PrivateDocument)
            {
                m_XlWorkSheet.Shapes.Item("WaterMark").Delete();
            }
        }