[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export to Excel.

Posted on 2009-02-10
18
Medium Priority
?
1,361 Views
Last Modified: 2013-11-07
I have 59,000 records in my gridview.While exporting to Excel it is showing System.OutOfMemory exception.Pls check my code below and kindly provide the solution.
private void ExportToExcel()
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + this.ReportName + ".xls");
        this.EnableViewState = false;
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        HttpContext.Current.Response.ContentType = "application/vnd.xls";
        DataSet ds = (DataSet)Session[this.ReportDSID];
 
        this.Page.DataBind();
 
        this.ReportGridView.AllowPaging = false;
        if (ds.Tables[0].Rows.Count < 60000)
        {
 
            //DataTable dttemp = new DataTable();
 
            //dttemp = ds.Tables[0].Clone();
 
 
            //foreach (DataColumn dc in dttemp.Columns)
            //{
            //    if (dc.DataType == typeof(DateTime))
            //    {
            //        dc.DataType = typeof(string);
            //    }
            //}
 
            //foreach (DataRow dr in ds.Tables[0].Rows)
            //{
            //    dttemp.ImportRow(dr);
            //}
            this.ReportGridView.DataSource = ds;
            this.ReportGridView.DataBind();
            #region Modified Again
 
 
            using (System.IO.StringWriter sw = new System.IO.StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    Table table = new Table();
 
                    table.GridLines = ReportGridView.GridLines;
                    if (this.ReportGridView.HeaderRow != null)
                    {
                        PrepareControlForExport(this.ReportGridView.HeaderRow);
                        table.Rows.Add(this.ReportGridView.HeaderRow);
                    }
 
                    foreach (GridViewRow row in this.ReportGridView.Rows)
                    {
                        PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }
 
                    if (this.ReportGridView.FooterRow != null)
                    {
                        PrepareControlForExport(this.ReportGridView.FooterRow);
                        table.Rows.Add(this.ReportGridView.FooterRow);
                    }
                    #region Modified by Srikanth on 30102008
                    globalwms_userobj ObjUser = new globalwms_userobj();
                    ObjUser = (globalwms_userobj)Session["userinfo"];
                    globalwms_bll objBLL = new globalwms_bll();
                    GeoReturnObject objReturn = new GeoReturnObject();
                    objReturn = objBLL.GetGMTOffsetTime(ObjUser.UserName);
                    DataSet dsGMTOffsetTime = new DataSet();
                    dsGMTOffsetTime = (DataSet)objReturn.RetObject;
                    double GMTOffsetTime = Convert.ToDouble(dsGMTOffsetTime.Tables[0].Rows[0][1].ToString());
                    string TempDate = DateTime.Now.AddHours(GMTOffsetTime).ToString();
                    if (ObjUser.DateFormat == "dd/MM/yyyy")
                    {
                        htw.WriteLine("<b><u><i><font size='5'>" +
                        "Downloaded Date and Time: " + DateTime.Now.ToString("dd/MM/yyyy") + "&nbsp;" + DateTime.Now.AddHours(GMTOffsetTime).ToShortTimeString() +
                        "</font></i></u></b>");
                    }
                    else if (ObjUser.DateFormat == "MM/dd/yyyy")
                    {
                        htw.WriteLine("<b><u><i><font size='5'>" +
                        "Downloaded Date and Time: " + DateTime.Now.ToString("MM/dd/yyyy") + "&nbsp;" + DateTime.Now.AddHours(GMTOffsetTime).ToShortTimeString() +
                        "</font></i></u></b>");
                    }
                    else
                    {
                        htw.WriteLine("<b><u><i><font size='5'>" +
                        "Downloaded Date and Time: " + DateTime.Now.ToString("yyyy/MM/dd") + "&nbsp;" + DateTime.Now.AddHours(GMTOffsetTime).ToShortTimeString() +
                        "</font></i></u></b>");
                    }
                    #endregion
 
                    table.RenderControl(htw);
                    //for (int count = 0; count < table.Rows.Count; count++)
                    //{
                    //    if (Response.BufferOutput == true && count % 500 == 0)
                    //    {
                    //        Response.Flush();
                    //    }
                    //}
                    string finalHTML = sw.ToString();
                    finalHTML = finalHTML.Replace("<a", "<o");
                    finalHTML = finalHTML.Replace("</a", "</o");
                    HttpContext.Current.Response.Write(finalHTML);
                    HttpContext.Current.Response.End();
                }
            }
            #endregion
        }
        else
        {
            Response.Write("<script>alert('The Record count was crossed the Excel Sheet Limit.');history.back(1);</script>");
        }
    }
 
 
    private void PrepareControlForExport(Control control)
    {
        globalwms_userobj ObjUser = new globalwms_userobj();
        ObjUser = (globalwms_userobj)Session["userinfo"];
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }
           
            else if (current is DataControlFieldCell)
            {
                DataControlFieldCell cell = current as DataControlFieldCell;
                try
                {
                    if (!string.IsNullOrEmpty(cell.Text) && cell.Text.Length > 8)
                    {
                        decimal outDec;
                        if (decimal.TryParse(cell.Text, out outDec))
                        {
                            cell.Text += "&nbsp;";
                        }
 
                    }
                }
                catch
                {
                    
                }
            }
            if (current.HasControls())
            {
                PrepareControlForExport(current);
            }
        }
    }

Open in new window

0
Comment
Question by:COANetwork
18 Comments
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 23599420
Wich version of ms office you are using?
0
 
LVL 6

Expert Comment

by:avnish_tanna
ID: 23611242
Check if any solution from below link helps, as it has a lot of examples:
http://steveorr.net/reviews/..%5CArticles%5CExcelExport.aspx
0
 
LVL 9

Author Comment

by:COANetwork
ID: 23619176
Hi avnish,Could you pls provide the C# code for this.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Author Comment

by:COANetwork
ID: 23619614
Can any one pls provide the above code in C#
 
0
 
LVL 15

Expert Comment

by:Praveen Venu
ID: 23619659


 
     private void Page_Load(object sender, System.EventArgs e)
     {
         Excel.Application oExcel = new Excel.Application();
         Excel.Workbooks oBooks = default(Excel.Workbooks);
         Excel.Workbook oBook = default(Excel.Workbook);
         Excel.Sheets oSheets = default(Excel.Sheets);
         Excel.Worksheet oSheet = default(Excel.Worksheet);
         Excel.Range oCells = default(Excel.Range);
         string sFile = null;
         string sTemplate = null;
         DataTable dt = (DataTable)Application.Item("MyDataTable");
        
         sFile = Server.MapPath(Request.ApplicationPath) + "\\MyExcel.xls";
        
         sTemplate = Server.MapPath(Request.ApplicationPath) + "\\MyTemplate.xls";
        
         oExcel.Visible = false;
         oExcel.DisplayAlerts = false;
        
         //Start a new workbook
         oBooks = oExcel.Workbooks;
         oBooks.Open(Server.MapPath(Request.ApplicationPath) + "\\MyTemplate.xls");
         //Load colorful template with chart
         oBook = oBooks.Item(1);
         oSheets = oBook.Worksheets;
         oSheet = (Excel.Worksheet)oSheets.Item(1);
         oSheet.Name = "First Sheet";
         oCells = oSheet.Cells;
        
         DumpData(dt, oCells);
         //Fill in the data
        
         oSheet.SaveAs(sFile);
         //Save in a temporary file
         oBook.Close();
        
         //Quit Excel and thoroughly deallocate everything
         oExcel.Quit();
         ReleaseComObject(oCells);
         ReleaseComObject(oSheet);
         ReleaseComObject(oSheets);
         ReleaseComObject(oBook);
         ReleaseComObject(oBooks);
         ReleaseComObject(oExcel);
         oExcel = null;
         oBooks = null;
         oBook = null;
         oSheets = null;
         oSheet = null;
         oCells = null;
         System.GC.Collect();
         Response.Redirect(sFile);
         //Send the user to the file
     }
    
     //Outputs a DataTable to an Excel Worksheet
     private string DumpData(DataTable dt, Excel.Range oCells)
     {
         DataRow dr = default(DataRow);
         object[] ary = null;
         int iRow = 0;
         int iCol = 0;
        
         //Output Column Headers
         for (iCol = 0; iCol <= dt.Columns.Count - 1; iCol++) {
             oCells(2, iCol + 1) = dt.Columns(iCol).ToString;
         }
        
         //Output Data
         for (iRow = 0; iRow <= dt.Rows.Count - 1; iRow++) {
             dr = dt.Rows.Item(iRow);
             ary = dr.ItemArray;
             for (iCol = 0; iCol <= Information.UBound(ary); iCol++) {
                 oCells(iRow + 3, iCol + 1) = ary(iCol).ToString;
                 Response.Write(ary(iCol).ToString + Constants.vbTab);
             }
         }
     }

Open in new window

0
 
LVL 9

Author Comment

by:COANetwork
ID: 23619711
I am using MS Office 2003 and I need above code in C#.
0
 
LVL 9

Author Comment

by:COANetwork
ID: 23620175
When I am executing this code the following errors are occuring.
 
Error 2 No overload for method 'Open' takes '1' arguments
Error 3 No overload for method 'SaveAs' takes '1' arguments
Error 4 No overload for method 'Close' takes '0' arguments
 
 
could you pls help on this Issue.
0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 23620427
use code like this
if (DGVRecords.Rows.Count < 1)
            {
                MessageBox.Show("There is no data to export!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            Microsoft.Office.Interop.Excel.Application wapp;
            Microsoft.Office.Interop.Excel.Worksheet wsheet;
            Microsoft.Office.Interop.Excel.Workbook wbook;
            wapp = new Microsoft.Office.Interop.Excel.Application();
            wapp.Visible = false;
            wbook = wapp.Workbooks.Add(true);
            wsheet = (Microsoft.Office.Interop.Excel.Worksheet)wbook.ActiveSheet;
            try
            {
                for (int i = 0; i < this.DGVRecords.Columns.Count; i++)
                {
                    wsheet.Cells[1, i + 1] = this.DGVRecords.Columns[i].HeaderText;
                    ((Microsoft.Office.Interop.Excel.Range)wsheet.Cells[1, i + 1]).EntireColumn.NumberFormat = "@";
                }
                for (int i = 0; i < this.DGVRecords.Rows.Count; i++)
                {
                    DataGridViewRow row = this.DGVRecords.Rows[i];
                    for (int j = 0; j < row.Cells.Count; j++)
                    {
                        DataGridViewCell cell = row.Cells[j];
                        try
                        {
                            wsheet.Cells[i + 2, j + 1] = (cell.Value == null) ? "" : cell.Value.ToString();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
                wsheet.Rows.AutoFit();
                wsheet.Columns.AutoFit();
                wapp.Visible = true;
            }
            catch (Exception ex1)
            {
                MessageBox.Show(ex1.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
wbook.SaveAs(@"C:\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
wbook.Close(false, null, null);
wapp=null;

Open in new window

0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 23620439
or if you want to allow user save where he/she wants,   last 3 lines as

wapp.UserControl = true;


0
 
LVL 9

Author Comment

by:COANetwork
ID: 23621188
You suggested last 3 lines as wapp.userControl=true;
where we need to put this part of the code.Could you pls help me.
0
 
LVL 9

Author Comment

by:COANetwork
ID: 23621195
Also Apart from above,I am using Asp.Net with C# not only C#.
So pls provide the code for this.
0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 23621218
Think the code will be the same, but DGVRecords will be not datagridview, but gridview name from which you are trying to export. Check it out



You suggested last 3 lines as wapp.userControl=true;
where we need to put this part of the code.Could you pls help me.     <---- just instead of last 3 lines put that one line wapp.UserControl = true;
0
 
LVL 9

Author Comment

by:COANetwork
ID: 23629874
I want above code with OpenSave dialog box.Could you help on this Issue.
0
 
LVL 9

Author Comment

by:COANetwork
ID: 23630268
Can any one provide the code for Exporting to Excel With Multiple sheets with OpenSave Dialog box in C#(Asp.Net)
0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 23630278
There is no opensave dialog in asp.net.  You can use Response.WriteFile and it allows user to save file where he wants
0
 
LVL 7

Expert Comment

by:nkhelashvili
ID: 23630291
You can change lines

wbook.SaveAs(@"C:\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
wbook.Close(false, null, null);
wapp=null;

to:

wbook.SaveAs(Environment.GetFolderPath(System.Environment.SpecialFolder.InternetCache) + @"\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
wbook.Close(false, null, null);
wapp=null;
Response.WriteFile(Environment.GetFolderPath(System.Environment.SpecialFolder.InternetCache) + @"\test.xls");


to implement this


0
 
LVL 7

Accepted Solution

by:
nkhelashvili earned 2000 total points
ID: 23630310
oops change

Response.WriteFile(Environment.GetFolderPath(System.Environment.SpecialFolder.InternetCache) + @"\test.xls");

to

Response.WriteFile(Environment.GetFolderPath(System.Environment.SpecialFolder.InternetCache) + @"\test.xls",false);






0
 
LVL 9

Author Closing Comment

by:COANetwork
ID: 31544971
Thank you very much for your help!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question