Click here to Skip to main content
Click here to Skip to main content

Export whole page to Excel with infragistics in ASP.NET C#

By , 24 Oct 2011
Rate this:
Please Sign up or sign in to vote.
Override the render for the ASP.NET page. Look through all the controls (See my other tiptrick for iterating controls).
 
Notes:
  • You will probably need to handle itemtemplates in your grid/details views if you use anything other than a string literal.
  • I put all detailsViews on the first page and grids on their own worksheet as that was my need but you probably won't want it exactly that way.
 
Note: Utilities.FindControls is located here:
Findcontrol using a non recursive approach with no new fangled crap.[^]
 
 protected override void Render(HtmlTextWriter writer)
    {
        base.Render(writer);
        
        Workbook workBook = new Workbook();
        List<control> controlList = Utilities.FindControls(this);
        int detailsRowStart = 0;
        Worksheet detailsSheet = workBook.Worksheets.Add("Details");
        detailsSheet.Columns[0].Width = 8000;
        foreach (var item in controlList )
        {
            if (item.GetType() == typeof(GridView))
            {
                Utilities.RenderControlInExcel((GridView)item, workBook);
            }
            if (item.GetType() == typeof(DetailsView))
            {
                detailsRowStart = Utilities.RenderControlInExcel((DetailsView)item, detailsSheet, detailsRowStart);
            }
        }
 
        ExcelExporter.WriteToResponse(workBook,String.Format( "report{0}.xls", Guid.NewGuid().ToString()), Response);
    }
 
Render the gridview detailsview:
 
    public static void RenderControlInExcel(GridView view, Workbook book)
    {
 
        if (view.Rows.Count == 0 || view.Columns.Count==0)
        {
            return;
        }
        
        Worksheet workSheet = book.Worksheets.Add(view.ID);
 
        for (int i = 0; i < view.Columns.Count; ++i)
        {
            WorksheetCell cell = workSheet.Rows[0].Cells[i];
            cell.Value = view.Columns[i].HeaderText;
            cell.CellFormat.Font.Bold =ExcelDefaultableBoolean.True;
        }
 
        const int HeaderOffset = 1;
        // Add one row for header columns
        for (int row = 0; row < view.Rows.Count; ++row)
        {
            for (int col = 0; col < view.Columns.Count; ++col)
            {
                int destRow = row + HeaderOffset;
                try
                {
                    if (view.Rows[row].Cells[col].Controls.Count > 0)
                    {
                        if (view.Rows[row].Cells[col].Controls[0].GetType() == typeof(DataBoundLiteralControl))
                        {
                            workSheet.Rows[destRow].Cells[col].Value = ((System.Web.UI.DataBoundLiteralControl)(view.Rows[row].Cells[col].Controls[0])).Text.Trim();
                        }
                    }
                    else
                    {
                        if (view.Rows[row].Cells[col].Text == " ")
                        {
                            continue;
                        }
                        workSheet.Rows[destRow].Cells[col].Value = view.Rows[row].Cells[col].Text;
                    }
                }
                catch
                {
                    workSheet.Rows[destRow].Cells[col].Value = "Error";
                }
            }
        }
    }
}
 
 public static int RenderControlInExcel(DetailsView view, Worksheet workSheet, int rowStart )
    {
 
        if (view.Rows.Count == 0 )
        {
            return rowStart;
        }
       
        // Add A space up front in case we are the second detailsview printing
        ++rowStart;
        WorksheetCell cell = workSheet.Rows[rowStart].Cells[0];
        cell.Value = view.HeaderRow.Cells[0].Text;
        cell.CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
        // Add a space to put the cursor past the header row
        ++rowStart;
 
        for (int row=0; row < view.Rows.Count; ++row)
        {
            for (int col = 0; col <view.rows[row].cells.count;>            {
                int destRow = rowStart + row;
                if (view.Rows[row].Cells[col].Controls.Count > 0)
                {
                    if (view.Rows[row].Cells[col].Controls[0].GetType() == typeof(DataBoundLiteralControl))
                    {
                        workSheet.Rows[destRow].Cells[col].Value = ((System.Web.UI.DataBoundLiteralControl)(view.Rows[row].Cells[col].Controls[0])).Text.Trim();    
                    }
                }
                else
                {
                    if (view.Rows[row].Cells[col].Text == " ")
                    {
                        continue;
                    }
                    workSheet.Rows[destRow].Cells[col].Value = view.Rows[row].Cells[col].Text;
                }
            }
        }
 
        // Set the start of the next grid.
        rowStart += view.Rows.Count;
        return rowStart;
    }
 
Write with infragistics Excel object (could be rewritten easily).
 
    /// <summary>
    /// This helper function will take an Excel Workbook 
    /// and write it to the response stream. In this fashion, 
    /// the end user will be prompted to download or open the
    /// resulting Excel file. 
    /// 
    /// <param name="theWorkBook"></param>
    /// <param name="FileName"></param>
    /// <param name="resp"></param>
    public static void WriteToResponse(Workbook theWorkBook, string FileName, HttpResponse resp)
    {
        //Create the Stream class
        System.IO.MemoryStream theStream = new System.IO.MemoryStream();
 
        if (theWorkBook.Worksheets.Count == 0)
        {
            return;
        }
        //Write the in memory Workbook object to the Stream
        BIFF8Writer.WriteWorkbookToStream(theWorkBook, theStream);
 
        //Create a Byte Array to contain the stream
        byte[] byteArr = (byte[])Array.CreateInstance(typeof(byte), theStream.Length);
 
        theStream.Position = 0;
        theStream.Read(byteArr, 0, (int)theStream.Length);
        theStream.Close();
 
        resp.Clear();
 
        resp.AddHeader("content-disposition", "attachment; filename=" + FileName);
 
        resp.BinaryWrite(byteArr);
 
        resp.End();
    }

License

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

About the Author

rj45
Software Developer (Senior)
Canada Canada
No Biography provided

Comments and Discussions

 
GeneralReason for my vote of 5 Nice article and code implementation... PinmemberMember 432084425-Oct-11 10:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140421.2 | Last Updated 24 Oct 2011
Article Copyright 2011 by rj45
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid