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
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:
- 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 theC1XLBookandC1PdfDocumentcomponents 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<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:
- 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<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.