Excel Export from database to Pre Designed Template

Question

Hello All!!

I want Help regarding Exporting Data from Database to Excel Sheet which is already Company Designed My problem is I want to export table / datatable data into the cells .. the cells where i want to show data are in 12 row and aabove them i have other fields. i need to sshow data in this column from sql datatable please help me i tried several hours and its been 2 days im struggling with it. If possible anyone can explain as well how things are going on. thank you its only values i need to display template of excel is already created 

Source :

 

public void ExcelSummaryForPgenLocal(string fileName)

{

FileInfo newFile = new FileInfo(fileName + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx");

//Dim templateFile As New FileInfo("F:\BBApps\PhotoCatDev\SellPro\Templates\template.xlsx")

string templatePath = HttpRuntime.AppDomainAppPath + "Template/sample.xlsx";

string UsertemplatePath = HttpRuntime.AppDomainAppPath + "Template/TimeTable.xlsx";

var outputDir = Context.Server + "TimeTable.xlsx";

if ((File.Exists(UsertemplatePath)))

{

File.Delete(UsertemplatePath);

File.Copy(templatePath, UsertemplatePath);

}

else

{

File.Copy(templatePath, UsertemplatePath);

}

// File.Copy(templatePath, UsertemplatePath)

FileInfo templateFile = new FileInfo(UsertemplatePath);

using (SqlConnection con = new SqlConnection(conString))

{

con.Open();

SqlDataReader dr;

System.Data.DataTable td = new System.Data.DataTable();

SqlCommand cmd = new SqlCommand("Select * from EmployeeWorkData", con);

dr = cmd.ExecuteReader();

td.Load(dr);

using (ExcelPackage MyExcel = new ExcelPackage(templateFile))

{

ExcelWorksheet objWS = MyExcel.Workbook.Worksheets.Add("WorkSheetTable");

for (int i= 1; 1<td.Rows.Count;i++)

{

for(int j = 0; j<td.Columns.Count;j++)

{

objWS.Cells["B12"].Value = td.Columns[9].ItemArray[i].ToString();

}