using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using Intel.CQSI.QDART.API.Models;
using Microsoft.Office.Interop.Excel;
using System.Data;
namespace Intel.CQSI.QDART.API.Routines
{
public class ExcelGenerator
{
public Workbook GetExcelDocument(List<pr_getQpeIssues_Result> pData)
{
var excel = new Application();
Worksheet worksheet;
Range celLrangE;
excel.Visible = false;
excel.DisplayAlerts = false;
var workbook = excel.Workbooks.Add(Type.Missing);
System.Data.DataTable Dt_Issues = ExportToExcel(pData);
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "QDART Report";
worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[1, 8]].Merge();
//worksheet.Cells[1, 1] = "Student Report Card";
worksheet.Cells.Font.Size = 15;
int rowcount = 2;
foreach (DataRow datarow in Dt_Issues.Rows)
{
rowcount += 1;
for (int i = 1; i <= Dt_Issues.Columns.Count; i++)
{
if (rowcount == 3)
{
worksheet.Cells[2, i] = Dt_Issues.Columns[i - 1].ColumnName;
worksheet.Cells.Font.Color = System.Drawing.Color.Black;
}
worksheet.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == Dt_Issues.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worksheet.Range[worksheet.Cells[rowcount, 1], worksheet.Cells[rowcount, Dt_Issues.Columns.Count]];
}
}
}
}
}
celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowcount, Dt_Issues.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Borders border = celLrangE.Borders;
border.LineStyle = XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[2, Dt_Issues.Columns.Count]];
workbook.SaveAs(HttpContext.Current.Server.MapPath("~/TempFile/tempReport.xlsx")); ;
workbook.Close();
excel.Quit();
// System.Diagnostics.Process.Start("sample.xlsx");
}
catch (Exception)
{
throw;
}
return workbook;
}
public System.Data.DataTable ExportToExcel(List<pr_getQpeIssues_Result> pData)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("CIRS number", typeof(string));
table.Columns.Add("Notes", typeof(string));
table.Columns.Add("Indicator", typeof(int));
table.Columns.Add("Root", typeof(int));
table.Columns.Add("Category", typeof(int));
table.Columns.Add("Subcategory", typeof(int));
table.Columns.Add("Product", typeof(string));
table.Columns.Add("Ip family", typeof(int));
table.Columns.Add("Ip config", typeof(int));
table.Columns.Add("Functional block", typeof(int));
table.Columns.Add("Tag", typeof(int));
table.Columns.Add("Commets", typeof(string));
table.Columns.Add("Customer contact", typeof(int));
table.Columns.Add("Issue title", typeof(string));
table.Columns.Add("QPE owner", typeof(string));
table.Columns.Add("TPT", typeof(int));
table.Columns.Add("QPE TPT", typeof(string));
table.Columns.Add("Region", typeof(string));
table.Columns.Add("Complete", typeof(string));
table.Columns.Add("Type", typeof(string));
table.Columns.Add("PLRT owner", typeof(string));
table.Columns.Add("PLRT TPT", typeof(string));
table.Columns.Add("DR owner", typeof(string));
table.Columns.Add("DR TPT", typeof(string));
table.Columns.Add("Linked to type", typeof(string));
table.Columns.Add("Linked to MRB", typeof(string));
table.Columns.Add("CQIR", typeof(string));
table.Columns.Add("CQIR date", typeof(string));
table.Columns.Add("Issue owner", typeof(string));
table.Columns.Add("Platform", typeof(int));
table.Columns.Add("Family name", typeof(string));
table.Columns.Add("Group name", typeof(string));
table.Columns.Add("Source", typeof(string));
table.Columns.Add("Sub source", typeof(string));
table.Columns.Add("Fail conditions", typeof(int));
table.Columns.Add("Issue details", typeof(int));
table.Columns.Add("Debug details", typeof(int));
foreach (var item in pData)
{
table.Rows.Add(item.CIRS_no,item.Notes, item.IndicatorID, item.RootID, item.CategoryID,item.SubcategoryID,
item.Product,item.ipfamily_id, item.ipconfig_id, item.Functional_BlockID,item.TAGID,item.Comments,
item.Customer_ContactID,item.Issue_Title,item.QPE_owner,item.TPT,item.QPE_TPT,item.Region,item.Completed,
item.Type,item.PLRT_owner,item.PLRT_TPT,item.DR_owner,item.DR_TPT,item.Linked_to_Type,item.Linked_to_MRB_,
item.CQIR,item.CQIR_Date,item.Issue_Owner,item.Platform,item.Family_Name,item.Group_Name,item.Source,item.Sub_Source,
item.Fail_Conditions,item.Issue_Details,item.Debug_Details);
}
//table.Rows.Add(2, "Mohit", "M", 76, 65, 85, 87, 72, 90);
//table.Rows.Add(3, "Garima", "F", 77, 73, 83, 64, 86, 63);
//table.Rows.Add(4, "jyoti", "F", 55, 77, 85, 69, 70, 86);
//table.Rows.Add(5, "Avinash", "M", 87, 73, 69, 75, 67, 81);
//table.Rows.Add(6, "Devesh", "M", 92, 87, 78, 73, 75, 72);
return table;
}
}
}