如何将动态创建的Datagrid值导出到多个工作表中 [英] How to export Dynamically created Datagrid values into multiple worksheets

问题描述

嗨朋友..

我正在生成Excel Report表单，动态创建了Gridviews.在这里，我正在动态生成3个Gridview，并使用String Builder和这些函数...

我在Single Excel Work Book中得到它...

Hi friends..

I am generating Excel Report form Dynamically created Gridviews.. here I am generating 3 Gridviews Dynamically and using String Builder and these functions...

I am getting it in Single Excel Work Book...

Response.Write(tw.ToString());
            Response.AddHeader("content-disposition","attachment;filename=PerformanceReport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.xls";

            this.EnableViewState = false;
            Response.End();

但是实际上我必须将相同的数据生成到其他两个工作表中.有没有可用的方法来解决我的问题...

请提供一些想法.

But actually I have to generate Same data into Other two worksheets Is there any methods available... to solve my problem...

Please give some Ideas

推荐答案

您将需要使用Office Interop来创建Excel工作簿并适当地填充工作表.或者，如果使用更高版本的Office，则可以尝试OpenXML.

You would need to use the Office Interop to create the Excel workbook and populate the worksheets as appropriate. Or if using later versions of Office you could try OpenXML.

首先，我们必须生成所有Excel文件，我们需要将单个工作簿放入Server中的特定文件夹中...


和使用合并方法您可以提供一个Source文件夹，然后使用Excel工作簿方法(使用Interop)
您可以将所有这些Excel作为不同的工作表复制到单个Excel工作簿中...


代码来了......

First of All we have to Generate All Excel Files we need to get in a single Workbook into a specific folder in Server...


and Using Merge Method You can give a Source folder and and Using Excel Workbook method(Using Interop)
You can copy all this Excels Into a Single Excel work Book as Different Worksheets...


Here Come the Code....

private void Merge(string strSourceFolder, string strDestinationFile)
        {
            try
            {
                object missing = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                // ExcelApp.Visible = false;
                ExcelApp.UserControl = true;
 

       
 
                Microsoft.Office.Interop.Excel.Workbook objBookDest = ExcelApp.Workbooks.Add(missing);
 
                foreach (string filename in Directory.GetFiles(strSourceFolder))
                {
                   
 

                        Microsoft.Office.Interop.Excel.Workbook objBookSource = ExcelApp.Workbooks._Open
                        (New, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                        , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 

                        //Browse through all files.
                        foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in objBookSource.Worksheets)
                        {
 
                            string Sheetname = sheet.Name.ToLower();
                            sheet.Copy(Type.Missing, objBookDest.Worksheets[objBookSource.Worksheets.Count]);
                            Marshal.FinalReleaseComObject(sheet);
 
                        }
                        objBookSource.Close(Type.Missing, Type.Missing, Type.Missing);
                        // System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBookSource);
                        Marshal.FinalReleaseComObject(objBookSource);
                        // Marshal.FinalReleaseComObject(objBookDest);

                    }
 
                }
 foreach (string filename1 in Directory.GetFiles(Server.MapPath("~\\MergeReports\\")))
                {
                    if (File.Exists(filename1))
                    {
                        File.Delete(strDestinationFile);
                        break;
                    }
                }
 
 foreach (Microsoft.Office.Interop.Excel.Worksheet sheet1 in objBookDest.Worksheets)
                {
 

  foreach (string filename1 in Directory.GetFiles(Server.MapPath("~\\MergeReports\\")))
                {
                    if (File.Exists(filename1))
                    {
                        File.Delete(strDestinationFile);
                        break;
                    }
                }
 

 
                string[] filePaths = Directory.GetFiles(Server.MapPath("~\\Merge\\"));
                foreach (string filePath in filePaths)
                    File.Delete(filePath);
 
                objBookDest.Saved = true;
                objBookDest.SaveAs(strDestinationFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, myExcel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
                //  objBookDest = null;
 

                GC.Collect();
                GC.WaitForPendingFinalizers();
                objBookDest.Close(Type.Missing, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(objBookDest);
                // System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBookDest);
 
                ExcelApp.Quit();
                //ExcelApp = null;
                //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ExcelApp);
 

                Marshal.FinalReleaseComObject(ExcelApp);
 

            }
            catch (System.Exception e)
            {
                objException = new BaseException(BasePage.GetCurrentUser.UserID.ToString(), e);
            }

在您的目标文件中，您将获得所有合并为单个工作簿的工作表

In Your Destionation File You will get All the Worksheets Merged as Single Workbook

Hi Mark

您是否有与此相关的示例材料.

Hi Mark

Do you have any sample stuffs regarding this..

这篇关于如何将动态创建的Datagrid值导出到多个工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！