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)); } }
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(); } }