Click here to Skip to main content
15,867,330 members
Articles / Web Development / ASP.NET
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
24 Oct 2011CPOL 41.8K   3   3
Export whole page to Excel with infragistics
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.[^]

C#
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:

C#
    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)


Written By
Software Developer (Senior)
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralReason for my vote of 5 Nice article and code implementation... Pin
Member 432084425-Oct-11 10:37
Member 432084425-Oct-11 10:37 
GeneralPlease fix the formatting of the last code snippet which is ... Pin
Philippe Mori24-Oct-11 13:32
Philippe Mori24-Oct-11 13:32 
Questionthanks to Deeksha Shenoy for the update on my formatting. Pin
rj4518-Oct-11 9:32
rj4518-Oct-11 9:32 

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

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