Exporting the C1GridView to PDF and Excel

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.

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<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 rcPage = doc.PageRectangle;
  rcPage.Inflate(-72, -72);
  var rc = rcPage;

  // 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<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
  if (!header && rcCell.Bottom > rc.Bottom)
  {
    doc.NewPage();
    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 C1GridViewExport class described here allows you to do that easily, using components you already have.