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