Add Data Export Functionality to Your Grids
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:
- Open the ASP.NET page that contains the grid you want to export.
- 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.
- Still with the page open, select the
View | Component Designermenu option. This will show a blank pane. Drag aC1XLBookand aC1PdfDocumentcomponents 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:
- Check that the grid has at least one visible column
- Create a new
C1PdfDocumentwhere the grid will be rendered - Calculate the size of the pages in the PDF document (we use a one-inch margin)
- Use the
GetColumnWidthsto calculate the column widths so they will fit on the pages - Use the
RenderGridRowmethod to render the grid content - 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:
- Check that the grid has at least one visible column
- Create a new
C1XLBookwhere the grid will be rendered - Scan and parse the cells (numbers, dates, booleans, strings)
- Assign the cell value to the corresponding grid cell
- 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.
MESCIUS inc.