Add Data Export Functionality to Your ASP.NET Grids

Add Data Export Functionality to Your ASP.NET Grids

This article describes how you can use ComponentOne’s PDF and Excel components to export the content of Grid controls, so users can easily analyze, annotate, and distribute the data. The GridView, PDF, and Excel components are all included in Studio Enterprise.

Introduction

Before we begin, let me briefly share some background on the ComponentOne components we will be looking out. Firstly, the grid control (ComponentOne GridView for ASP.NET) is a robust, easy-to-use grid control featuring fast data access and data presentation such as Microsoft Outlook-style grouping, filtering, and customized navigation. Our PDF component (ComponentOne PDF for .NET) helps to easily create rich Adobe PDF documents from your data including, security, compression, outlining, hyper-linking, and more. Finally, the Excel component (ComponentOne Excel for .NET) is a powerful component for creating or loading Excel files, making data-export in any .NET apps easy.

Background

So you finished your latest and greatest ASP.NET application. It efficiently loads, formats, and displays important information. Everyone loves your application, but every once in a while you get comments like "the app is nice, but it would be great if I could analyze the data using Excel", or "I wish I could make annotations right on the data and send that to my boss", or "copying the data to HTML and then cleaning it up takes a lot of time". In other words, many users would love to be able to export the data to useful formats like PDF and Excel.

Why? Data is important, but data accessibility is the key to any successful organization. In today’s world we have an abundance of data flowing through our businesses. Your data and its usage is an asset. By making your data more accessible, you will be adding great value to your application and enabling your organization to thrive.

The good news is this is easy to do using the C1Pdf and C1Excel components from ComponentOne. We will show you how to implement simple methods that can render the content of any C1GridView control into PDF and Excel files. Best of all, adding this great feature to your apps will take only a few minutes (copy the code to your project, add a few lines of code to invoke the export methods, and you're done).

Using the Code

To begin, download the code: C1GridViewExport.zip

The class that exports the C1GridView to PDF and Excel is called C1GridViewExport. Using it is very easy: just call the static ExportPdf or ExportExcel methods from any ASP.NET page as shown below:

// export the grid to PDF when the user clicks the PDF button
protected void Pdf_Click(object sender, EventArgs e)
{
    C1GridViewExport.ExportPdf(this.Page, this.C1GridView1);
}
// export the grid to XLS when the user clicks the Excel button
protected void XLS_Click(object sender, EventArgs e) 
{ 
    C1GridViewExport.ExportExcel(this.Page, this.C1GridView1);
}

The C1GridViewExport will render the grid into a stream of the appropriate type and fill the page response with the stream content. The browser will show the result to the user, who can then use it, save it, print it, or send it to someone by e-mail.

The Sample Application

The sample application included with this article shows a C1GridView loaded with the Northwind Products data. Below the C1GridView there are two buttons used to export the grid to PDF or Excel documents. Both buttons cause postbacks which create streams with the desired content and copy the streams into the page's Response object. The desired document is then shown to the user, and can be saved or printed.

No files are created at any point, so you don't have to worry about permissions or cleaning up.

Adding the Components to the Application

The C1Pdf and C1Excel components are licensed. To download them, along with C1GridView, visit http://www.componentone.com/SuperProducts/StudioASPNET/ and download the free, full-featured trial of Studio for ASP.NET, which contains all three controls. After downloading, open Visual Studio and add them to your project.

To add the components to your project, follow these steps:

1. Open the ASP.NET page that contains the grid you want to export.
2. If the components don't appear in the Visual Studio Toolbox, add them to the toolbox by dragging the C1.C1Pdf.2.dll and C1.C1Excel.2.dll to the toolbox, or right-click the toolbox, select "Choose Items...", then select the files.
3. Still with the page open, select the View | Component Designer menu option. This will show a blank pane. Drag the C1XLBook and C1PdfDocument components onto the pane. This will add the necessary licensing information to the application. (If you don't have a license, the application will still work but the output will contain a licensing watermark).

Exporting to PDF

The ExportPdf method starts by creating a PDF stream with the grid contents. Then it writes the stream into the page's Response object.

To create the PDF stream, the code retrieves the content of each grid cell using the Cells[index].Text property in the C1GridView class. Then it measures and renders the content using the MeasureString and DrawString methods in the C1PdfDocument class. These methods are analogous to the ones in the System.Drawing.Graphics class.

Here is the implementation of the ExportPdf method:

// export a C1GridView to pdf
public static bool ExportPdf(Page page, C1GridView grid)
{
 // get pdf stream
 var stream = GetPdfStream(grid);
 
 // no stream? we're done
 if (stream == null || stream.Length == 0)
 {
     return false;
 }
 
 // copy stream to Page's Response object
 WriteStreamToPage(page, stream, "application/pdf");
 
 // done
 return true;
}

The core of the export code is the GetPdfStream method. It performs these tasks:

1. Check that the grid has at least one visible column
2. Create a new C1PdfDocument where the grid will be rendered
3. Calculate the size of the pages in the PDF document (we use a one-inch margin)
4. Use the GetColumnWidths to calculate the column widths so they will fit on the pages
5. Use the RenderGridRow method to render the grid content
6. Create a memory stream, save the document into it, and return it.

Here is the implementation of the GetPdfStream method:

    // create a Pdf stream with the grid contents
 static MemoryStream GetPdfStream(C1GridView grid)
 {
 int[] mapping;
 List<C1BaseField> columns = GetVisibleColumnsWithMapping(grid, out mapping);
 
 // make sure grid has at least one visible column  
 if (columns.Count == 0)
 {
     return null;
 }
 
 // start with new empty document
 var doc = new C1.C1Pdf.C1PdfDocument();
 
 // get render rectangle (1-inch margin all around)
 var rc = GetPageRectangle(doc);
 
 // calculate column widths
 int cellOffset = grid.RowHeader.Visible ? 1 : 0;
 var widths = GetColumnWidths(doc, grid, rc, cellOffset, columns, mapping);
 
 // render the grid header
 if (grid.ShowHeader)
 {
     var row = grid.HeaderRows[grid.HeaderRows.Length - 1];
     rc = RenderGridRow(doc, rc, grid, row, widths, true, cellOffset, columns, mapping);
 }
 
 // render the grid body
 foreach (C1GridViewRow row in grid.Rows)
 {
    rc = RenderGridRow(doc, rc, grid, row, widths, false, cellOffset, columns, mapping);
 }
 
 // create output stream
 var ms = new MemoryStream();
 doc.Save(ms);
 return ms;
}

Before rendering anything, we’ll use the GetColumnWidths method to measure the columns and make sure they will all fit on the page. The easiest way to do this is to divide the page width by the number of columns, making all columns the same width. But we have an even better method.

The GetColumnWidths method shown below measures the width of each column based on its content, then adjusts the total width to ensure it fits the page. This way, columns that show long strings will be wider than ones with short numeric values.

The content of each cell is measured using the MeasureString method in the C1PdfDocument class. This method is analogous to the MeasureString method in the System.Drawing.Graphics class. The MeasureString method takes as parameters the string to be measured and the font used to render it. Our implementation uses two fonts, one for the grid headers and one for the body. Both fonts are defined as class constants.

Here is the code that calculates the column widths:

// calculate column widths to fit the page
static float[] GetColumnWidths(C1.C1Pdf.C1PdfDocument doc, C1GridView grid, RectangleF rc,
int cellOffset, List<C1BaseField> columns, int[] mapping)
{
    // dimension column width vector
    var widths = new float[columns.Count];

    // measure header cells
    if (grid.ShowHeader)
    {
        var lastHeaderRow = grid.HeaderRows[grid.HeaderRows.Length - 1];

        for (int col = 0; col < columns.Count; col++)
        {
            string text = HttpUtility.HtmlDecode(columns[col].HeaderText);
            var width = doc.MeasureString(text, _headerFont).Width;
            widths[col] = width;
        }
    }
    // measure body cells
    foreach (C1GridViewRow row in grid.Rows)
    {
         for (int col = 0; col < columns.Count; col++)
         {
             string text = HttpUtility.HtmlDecode(row.Cells[mapping[col] + cellOffset].Text);
             var width = doc.MeasureString(text, _bodyFont).Width;
             widths[col] = Math.Max(widths[col], width);
         }
    }
    // adjust to fit the page
    float totalWidth = 0;
    for (int col = 0; col < widths.Length; col++)
    {
         totalWidth += widths[col];
    }
    var adjustment = rc.Width / totalWidth;
    if (adjustment < 1)
    {
        for (int col = 0; col < widths.Length; col++)
        {
            widths[col] *= adjustment;
        }
    }
    // done

   return widths;
}

We are almost done. The only remaining method is the one that renders the grid rows.

The RenderGridRow method takes a row and a layout rectangle as parameters. It then calculates the height needed to render the row. If there is enough room on the page, it renders the row and returns an updated rectangle that should be used to render the next row. If the row won't fit on the current page, RenderGridRow starts a new page, renders a header row at the top of the page, then renders the current row.

To render individual cells, RenderGridRow starts by getting the cell content as text. If the text can be parsed as a number, then the cell is aligned to the right; otherwise, it is aligned to the left.

If the cell contains a checkbox, then the code selects a symbol font (WingDings) and the appropriate character that represents a checkbox with or without the check mark.

Here is the RenderGridRow implementation, the last piece in our PDF renderer class:

   // render a grid row
   static RectangleF RenderGridRow(C1.C1Pdf.C1PdfDocument doc, RectangleF rc,
       C1GridView grid, C1GridViewRow row, float[] widths, bool header, int cellOffset,
       List<C1BaseField> columns, int[] mapping)
   {
       const int CELL_MARGIN = 4;
       // get row cells
       var cells = row.Cells;
       // calculate cell rectangle
       RectangleF rcCell = rc;
       rcCell.Height = 0;
       // calculate cell height (max of all columns)
       var font = header ? _headerFont : _bodyFont;
       for (int col = 0; col < columns.Count; col++)
       {
            rcCell.Width = widths[col];
            string text = (header)
            ? HttpUtility.HtmlDecode(columns[col].HeaderText)
            : HttpUtility.HtmlDecode(cells[mapping[col] + cellOffset].Text);
            rcCell.Inflate(-CELL_MARGIN, 0);
            float height = doc.MeasureString(text, font, rcCell.Width).Height;
            rcCell.Inflate(CELL_MARGIN, 0);
            rcCell.Height = Math.Max(rcCell.Height, height);
       }
       // break page if we have to
       var rcPage = GetPageRectangle(doc);
       if (!header && rcCell.Bottom > rcPage.Bottom)
       {
           doc.NewPage();
           rc = rcPage;
           if (grid.ShowHeader)
           {
               var lastHeaderRow = grid.HeaderRows[grid.HeaderRows.Length - 1];
               rc = RenderGridRow(doc, rc, grid, lastHeaderRow, widths, true, cellOffset,
                   columns, mapping);
           }
           rcCell.Y = rc.Y;
       }

       // center cell content vertically
       var sf = new StringFormat();
       sf.LineAlignment = StringAlignment.Center;

       // render data cells
       using (Pen pen = new Pen(Brushes.Gray, 0.1f))
       {
       for (int col = 0; col < columns.Count; col++)
       {
            // get font
            font = header ? _headerFont : _bodyFont;
            // get content
            var cell = cells[mapping[col] + cellOffset];
            string text = (header)
            ? HttpUtility.HtmlDecode(columns[col].HeaderText)
            : HttpUtility.HtmlDecode(cell.Text);

            // set horizontal alignment
            double d;
            sf.Alignment = (double.TryParse(text, NumberStyles.Any,
                CultureInfo.CurrentCulture, out d))
            ? StringAlignment.Far
            : StringAlignment.Near;

            // handle check boxes
            if (string.IsNullOrEmpty(text) && cell.Controls.Count > 0 &&
                 cell.Controls[0] is CheckBox)
            {
                sf.Alignment = StringAlignment.Center;
                var cb = cell.Controls[0] as CheckBox;
                text = cb.Checked ? CHKSTR_CHECKED : CHKSTR_UNCHECKED;
                font = _symbolFont;
            }
            // render cell
            rcCell.Width = widths[col];
            doc.DrawRectangle(pen, rcCell);
            rcCell.Inflate(-CELL_MARGIN, 0);
            doc.DrawString(text, font, Brushes.Black, rcCell, sf);
            rcCell.Inflate(CELL_MARGIN, 0);
            rcCell.Offset(rcCell.Width, 0);
        }
    }
    // update rectangle and return it
    rc.Offset(0, rcCell.Height);
    return rc;
}

Exporting to Excel

The ExportExcel method is similar to ExportPdf, except instead of rendering strings into the document, it sets cell values using the Sheet[row, col].Value method in the C1XLBook class.

Here is the implementation of the ExportExcel method:

// export a C1GridView to an Excel stream
public static bool ExportExcel(Page page, C1GridView grid)
{
    // get excel stream
    var stream = GetExcelStream(grid);
 
    // no stream? we're done
    if (stream == null || stream.Length == 0)
    {
        return false;
    }
 
    // copy stream to Page's Response object
    WriteStreamToPage(page, stream, "application/vnd.ms-excel");
    // done
    return true;
}

The core of the export code is the GetExcelStream method. It performs these tasks:

1. Check that the grid has at least one visible column
2. Create a new C1XLBook where the grid will be rendered
3. Scan and parse the cells (numbers, dates, booleans, strings)
4. Assign the cell value to the corresponding grid cell
5. Create a memory stream, save the document into it, and return it.

Here is the implementation of the GetExcelStream method:

// create an Excel stream for a C1GridView
static MemoryStream GetExcelStream(C1GridView grid)
{
    int[] mapping;
    List<C1BaseField> columns = GetVisibleColumnsWithMapping(grid, out mapping);

    // make sure grid has at least one visible column
    if (columns.Count == 0)
    {
        return null;
    }
    // start with new empty book
    var book = new C1.C1Excel.C1XLBook();
    var sheet = book.Sheets[0];

    // export header cells
    if (grid.ShowHeader)
    {
        for (int col = 0; col < columns.Count; col++)
        {
            sheet[0, col].Value = columns[col].HeaderText;
        }
    }

    // get row and cell offset
    int rowOffset = grid.ShowHeader ? 1 : 0;
    int cellOffset = grid.RowHeader.Visible ? 1 : 0;

    // export body
    double dbl;
    DateTime dateTime;
    for (int row = 0; row < grid.Rows.Count; row++)
    {
        for (int col = 0; col < columns.Count; col++)
        {
            // get cell and value
            var cell = grid.Rows[row].Cells[mapping[col] + cellOffset];
            string text = HttpUtility.HtmlDecode(cell.Text);
            if (string.IsNullOrEmpty(text) && cell.Controls.Count > 0 &&
                cell.Controls[0] is CheckBox)
            {
                // boolean value
                var cb = cell.Controls[0] as CheckBox;
                sheet[row + rowOffset, col].Value = cb.Checked;
            }
            else if (double.TryParse(text, NumberStyles.Any, CultureInfo.CurrentCulture,
                out dbl))
            {
                // numeric value
                sheet[row + rowOffset, col].Value = dbl;
            }
            else if (DateTime.TryParse(text, CultureInfo.CurrentCulture,
                DateTimeStyles.None, out dateTime))
            {
                // date/time value
                sheet[row + rowOffset, col].Value = dateTime;
            }
            else
            {
                // everything else is text
                sheet[row + rowOffset, col].Value = text;
            }
        }
    }
    // freeze header row and give it a background
    if (grid.ShowHeader)
    {
        sheet.Rows.Frozen = 1;
        var style = new C1.C1Excel.XLStyle(book);
        style.BackColor = Color.LightGray;
        sheet.Rows[0].Style = style;
    }

    // create and return stream
    var ms = new MemoryStream();
    book.Save(ms);
    return ms;
}

The GetExcelStream method is much simpler than the GetPdfStream method described earlier. In this case, we are not measuring the content, setting column widths, row heights, or handling page breaks.

The most important part of the code is parsing the cell contents to assign values of the proper type to the cell's Value property. This ensures that numbers, dates, and boolean values will be saved as such in the Excel stream (as opposed to saving all the content as strings).

Writing Streams to the Page

Once the output streams are ready (either PDF or Excel), they must be written into the page's Response stream. This is done by the WriteStreamToPage method shown below:

// write a stream into the page response object
static void WriteStreamToPage(Page page, MemoryStream stream, string contentType)
{
    // get response object, clear it
    var rsp = page.Response;
    rsp.Clear();
    rsp.ClearContent();
    rsp.ClearHeaders();
  
    // add Accept-Header header (required when https is used)
    string len = stream.Length.ToString();
    rsp.AddHeader("Accept-Header", len);
 
    // add Content-Length header
    rsp.AddHeader("Content-Length", len);
  
    // write pdf stream into response buffer
    rsp.ContentType = contentType;
    rsp.OutputStream.Write(stream.GetBuffer(), 0, (int)stream.Length);
 
    // done
    rsp.Flush();
    rsp.SuppressContent = true;
}

As you can see, the WriteStreamToPage method is quite simple. The only thing you have to worry about is passing in the right value for the contentType parameter. For PDF streams, this should be "application/pdf". For Excel streams, it should be "application/vnd.ms-excel".

Conclusion

Adding PDF and Excel output to your web applications can make them substantially more useful. The end users of your applications will be grateful to have this data at their fingertips. With just some help from ComponentOne’s PDF and Excel libraries, you can extend any data in your apps. The C1GridViewExport class described here allows you to do just that. So take the time to make your data accessible.

To learn more about Studio for ASP.NET controls, visit the ComponentOne website.