leonardo-m
2/19/2020 - 5:14 PM

Generate excel from C#

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


    }
}