Export selected data from grid to excel template in C#

See more:

C#

ASP.NET

I have this form with search, import and export button. When user click on search and a grid will show records from database. User can select one or more records then click on the export button to extract the data to excel file where I used crystal report. But now the required is the data should be extracted to the same excel file used in importing which is the excel template. So what I need to do is when user export the data, it will populate the blank excel template with the selected data.

Below is my existing code. Please help me or give me some tips on how to do it.
Thank you in advance.

EDIT: I changed the code below with the latest code I tried. But what happens is it downloads the excel file without any data inside. Please help me know why. Thanks!

What I have tried:

private void ExportListReport()
        {
            try
            {

                UpdateDataSelection();

                string pFileName = "LineList_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";

                string pLists = string.Empty;
                string pJobCodeKey = string.Empty;

                string pCountryCode = string.Empty;
                string pWorkCategoryCode = string.Empty;
                string pCapacityCode = string.Empty;
                string pOperativeCountryCode = string.Empty;

                string pNameAbbr = string.Empty;
                string pAffiliates = string.Empty;

                pLists = string.Join(",", DataSelection);
                if (pLists == string.Empty)
                {
                    GetDatabaseFilter(out pLists, out pNameAbbr, out pJobCodeKey, out pCountryCode, out pOperativeCountryCode, out pWorkCategoryCode, out pAffiliates, out pCapacityCode);
                }

                string pGridFilter = (rgvSubcontractor.MasterTableView.FilterExpression == null ? string.Empty : rgvSubcontractor.MasterTableView.FilterExpression);
                string pSortString = "";
                if (rgvSubcontractor.MasterTableView.SortExpressions != null)
                {
                    pSortString = ((rgvSubcontractor.MasterTableView.SortExpressions.GetSortString() == null) || (rgvSubcontractor.MasterTableView.SortExpressions.GetSortString() == string.Empty) ? pSortString : rgvSubcontractor.MasterTableView.SortExpressions.GetSortString());
                }
                pSortString = (pSortString == string.Empty ? "COMPANY_NAME ASC" : pSortString + ", COMPANY_NAME ASC");

                DataTable pDTSubconOneLineList = mSubContractorBS.getRptSubContractorOneLineList(pSubConCodeLists, pNameAbbr, string.Empty, string.Empty, pJobCodeKey, pCountryCode, pOperativeCountryCode, pWorkCategoryCode, pAffiliates, pCapacityCode);
                DataView pDVSubconOneLineList = new DataView(pDTSubconOneLineList);
                if (pGridFilter != string.Empty)
                {
                    pDVSubconOneLineList.RowFilter = pGridFilter;
                }
                pDVSubconOneLineList.Sort = pSortString;
                pDTSubconOneLineList = pDVSubconOneLineList.ToTable();

                pDTSubconOneLineList.TableName = "USP_RPT_SUBCON_ONE_LINE_LIST";
                

                Process[] processList = Process.GetProcesses();
                

                string path = Server.MapPath("~") + "\\SIS\\Template\\Download\\Subcon_Profile_List_Import_Template.xlsx";
                //string targetPath = Convert.ToString(Session["App_Data_Path"]) + "EXPORT_OUTPUT";
                string targetPath = Convert.ToString(Server.MapPath("~")) + "EXPORT_OUTPUT";
                string destFile = System.IO.Path.Combine(targetPath, pFileName);

                if (!Directory.Exists(targetPath))
                {
                    Directory.CreateDirectory(targetPath);
                }

                File.Copy(path, destFile, true);

                object misValue = System.Reflection.Missing.Value;

                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(destFile, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

                Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];
                xlWorkSheet.get_Range("A2", "AN" + xlWorkSheet.Rows.Count.ToString()).Clear();

                object[,] objData = null;

                int rowcount = pDTSubconOneLineList.Rows.Count;

                objData = new Object[pDTSubconOneLineList.Rows.Count, pDTSubconOneLineList.Columns.Count];

                for (int row = 0; row < pDTSubconOneLineList.Rows.Count; row++)
                {
                    for(int column= 0; column < pDTSubconOneLineList.Columns.Count; column++)
                    {
                        objData[row, column] = pDTSubconOneLineList.Rows[row][column].ToString();
                    }
                }

                ((Excel.Worksheet)xlWorkBook.Sheets[1]).Select(Type.Missing);

                xlWorkBook.Save();
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
                xlWorkSheet = null;
                xlWorkBook = null;
                xlApp = null;
                GC.Collect();



                //ReportDataSource pRds = new ReportDataSource("USP_RPT_SUBCON_ONE_LINE_LIST", pDTSubconOneLineList);
                //Microsoft.Reporting.WebForms.ReportViewer pRptViewer = new Microsoft.Reporting.WebForms.ReportViewer();
                //pRptViewer.LocalReport.EnableHyperlinks = true;
                //pRptViewer.ProcessingMode = ProcessingMode.Local;
                //pRptViewer.LocalReport.ReportPath = Server.MapPath("~") + "\\SIS\\Report\\SubconOneLineList_RPT.rdlc";
                //pRptViewer.LocalReport.DataSources.Add(pRds);

                //Warning[] pWarningArrays;
                //string[] pStreamIDsArrays;
                string pMimeType = string.Empty;
                string pEncoding = string.Empty;
                string pExtension = string.Empty;

                //byte[] pExcelFileBytesArray = pRptViewer.LocalReport.Render("Excel", null, out pMimeType, out pEncoding, out pExtension, out pStreamIDsArrays, out pWarningArrays);
                Response.Buffer = true;
                Response.Clear();
                Response.AppendCookie(new HttpCookie("fileDownloadToken", hdDownLoadToken.Value));
                Response.ContentType = pMimeType;
                Response.AddHeader("content-disposition", "attachment; filename=" + pFileName);
                //Response.BinaryWrite(pExcelFileBytesArray);
                Response.Flush();
            }
            catch (Exception ex)
            {
                ErrorHelper.HandleError(ex);
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            ExportListReport();
        }