Introduction

This article describes how you can use the C1Pdf and C1Excel components to export the content of C1GridView controls to PDF and Excel, so users can easily analyze, annotate, and distribute the data.

Download the code: C1GridViewExport.zip

Background

So you finished your latest and greatest ASP.NET application. It loads, formats, and displays really important information that allows people to do their jobs better, and everyone loves it. 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.

The good news is this is easy to do using the C1Pdf and C1Excel components you already have. 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

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 add them 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 a C1XLBook and a 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 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 use the GetColumnWidths method to measure the columns and make sure they will all fit on the page. The easiest way to do this would be to divide the page width by the number of columns, making all columns the same width. But we can do better than that.

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 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 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 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 C1GridViewExport class described here allows you to do that easily, using components you already have.