Click here to Skip to main content
15,944,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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...

C#
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
Posted
Updated 9-Mar-12 5:22am
v2

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.
 
Share this answer
 
Comments
Tony Tom.k 10-Mar-12 1:18am    
Hi Mark

Do you have any sample stuffs regarding this..
[no name] 10-Mar-12 9:54am    
www.google.com
Tony Tom.k 13-Mar-12 3:11am    
ha ha
Tony Tom.k 13-Mar-12 3:12am    
I got some merging methods first I will generate it as different workbooks and merge it as one file..
Tony Tom.k 17-Apr-12 1:00am    
hey Mark..

I am using Excel.Interop to open and save Excel work books but its working properly on local machine while hosted it in IIS its not working.
its showing some error
for reference click this link

http://www.codeproject.com/Questions/365814/Excel-Merging-Working-in-Local-System-but-not-Work

do you have any idea to fix this problem
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....


C#
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
 
Share this answer
 
v2
Hi Mark

Do you have any sample stuffs regarding this..
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900