在C＃中将所选数据从网格导出到excel模板 [英] Export selected data from grid to excel template in C#

问题描述

我有这个带有搜索，导入和导出按钮的表单。当用户单击搜索时，网格将显示数据库中的记录。用户可以选择一个或多个记录，然后单击导出按钮将数据提取到我使用水晶报告的excel文件。但现在需要的是应该将数据提取到导入中使用的同一个excel文件，即excel模板。所以我需要做的是当用户导出数据时，它将使用所选数据填充空白excel模板。



以下是我现有的代码。请帮帮我或给我一些如何做的提示。

提前谢谢。



编辑：我改了下面的代码用我试过的最新代码。但是，它会在没有任何数据的情况下下载excel文件。请帮我知道原因。谢谢！



我尝试了什么：

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.

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

推荐答案

这可能会有所帮助：将选定的GridView Rows导出到ASP.Net中的Excel文件 [ ^ ]

This may helps: Export selected GridView Rows to Excel file in ASP.Net[^]

我首先使用一个可以帮助处理Office文档的OpenXML格式的组件。



EPPlus， EPPlus-在服务器上创建高级Excel电子表格 - 主页 [ ^ ]

或

Open XML SDK，欢迎使用Open XML SDK 2.5 for Office [ ^ ]

是两个运行良好的组件。我个人在我的几个项目中使用EPPlus。

I would start with using a component that can help work with OpenXML format for Office documents.

EPPlus, EPPlus-Create advanced Excel spreadsheets on the server - Home[^]
Or
Open XML SDK, Welcome to the Open XML SDK 2.5 for Office[^]
are two components that work well. I personally use EPPlus in several of my project.

public void GridviewToExcel()
{
 
    StringBuilder builder = new StringBuilder();
    string strFileName = "GridviewExcel_" + DateTime.Now.ToShortDateString() + ".csv";
    builder.Append("Name ,Education,Location" + Environment.NewLine);
    foreach (GridViewRow row in GridView1.Rows)
    {
        string name = row.Cells[0].Text;
        string education = row.Cells[1].Text;
        string location = row.Cells[2].Text;
        builder.Append(name + "," + education + "," + location + Environment.NewLine);
    }
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AddHeader("Content-Disposition", "attachment;filename=" + strFileName);
    Response.Write(builder.ToString());
    Response.End();
}
 
protected void Button1_Click1(object sender, EventArgs e)
{
    GridviewToExcel();
}

这篇关于在C＃中将所选数据从网格导出到excel模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！