Click here to Skip to main content
Click here to Skip to main content

Exporting a DataGridView to an Excel/PDF/image file by using Reporting Services report generation

By , 20 Aug 2008
Rate this:
Please Sign up or sign in to vote.

ample xls-file opened in MS Excel

The ReportExporters uses the Microsoft Report Viewer Redistributable 2005. You can download it from here. Also, if you have installed .NET Framework 3.5, you can try to change the references to the libraries from the Microsoft Report Viewer Redistributable 2008.

Introduction

Some time ago, I was looking for a solution for exporting data from a DataGridView to MS Excel. I tried a well-known method by using the Microsoft.Office.Interop.Excel.Application class, but the application was too slow. So, I tried to find another method.

While working with SQL Server 2005 Reporting Services, I found out that the component ReportViewer can export report data to a native XLS file. When I took a closer look at the Report Definition Language Specification, the idea of creating a library for programmatically generating an RDLC-file occured. The resulting RDLC-file should reproduce the metadata of the DataGridView control (content and layout). This RDLC-file can be loaded to the Microsoft.Reporting.WinForms.LocalReport class by using the method LoadReportDefinition, and the Render method allows to export reports to the selected format. The description of classes and examples for using this library can be found below:

Classes Overview

I have written a few interfaces, a lot of data model classes, and RDLC element wrapper classes. These are the main ones:

IReportExporter Provides common methods for exporting to different formats
WinFormsReportExporter IReportExporter implementation for the Microsoft.Reporting.WinForms library
IReportDataAdapter Provides common methods for gathering meta information about exporting data (type, formatting, grouping etc.); should be used for generation of reports which reproduce the data-container (control)
DataGridViewReportDataAdapter IReportDataAdapter implementation for the System.Windows.Forms.DataGridView control

Main interfaces and classes

The simplest usage of the DataGridViewReportDataAdapter and the WinFormsReportExporter looks like:

DataGridView myDataGridView;
...
//Create DataGridViewReportDataAdapter instance
IReportDataAdapter reportDataAdapter = new DataGridViewReportDataAdapter(myDataGridView);

//Create WinFormsReportExporter instance for reportDataAdapter
IReportExporter winFormsReportExporter = new WinFormsReportExporter(reportDataAdapter);

//Execute method ExportToXls to get Excel file content
MemoryStream xlsFileData = winFormsReportExporter.ExportToXls();
...
//Than xlsFileData can be saved to local disk or etc.

ReportColumns And Styles classes

ReportColumn Base class for ReportDataColumn and ReportHyperlinkColumn.
ReportDataColumn Provides content and view information for the exported column (DataGridViewColumn etc.).
ReportHyperlinkColumn Contains a hyperlink for the ReportDataColumn cell. ValueType for this column can be System.String or System.Uri. It doesn't have its own properties, only inherited.

ReportColumn Properties

Index Column index in ReportColumnCollection.
Name The name of the column.
ValueType The data type of the values in the column's cells. For example, it can be initialized from DataGridViewColumn.ValueType.

ReportDataColumn Properties

DataCellViewType Type of rendering ReportControl for column item cell (TextBox or Image).
DefaultCellStyle Style for column item cell. Type is ReportStyle.
HeaderCellHyperlink Column header cell hyperlink.
HeaderCellViewType Type of ReportControl for column header cell.
HeaderStyle Style for column header cell.
HeaderText Text in column header cell.
HyperlinkColumn Column that contains hyperlink for item cell.
TemplateFormat Format string which allow to pass an argument like "Image size is {0} Kb".
ValueConverter Custom TypeConverter for converting a value at generating report. For example, there is CustomBooleanConverter which can convert a Boolean value to "+"/"-" or "Yes"/"No"

BaseStyle Properties

BackgroundColor The color of the background.
BackgroundGradientEndColor The end color for the background gradient.
BackgroundGradientType The type of the background gradient.
BackgroundImage The background image.
Border The border (includes BorderColor, BorderStyle, BorderWidth).
Calendar The calendar to use for formatting dates.
Color The foreground color.
Direction Indicates whether text is written left-to-right or right-to-left.
Font The font (includes FontFamily, FontSize, FontStyle, FontWeight).
Format The .NET Framework formatting string.
Padding The padding.
TextAlign The horizontal alignment of text.
TextDecoration The text formatting.
VerticalAlign The vertical alignment of text.
WritingMode The writing mode for the text. Indicates whether text is written horizontally or vertically.

ReportStyle Properties

Width The current width of the column.
Height The column cell height.
NullValue A string displayed in a column cell null value.
Wrap Specifies the content of the cell to wrap in the cell.

Demo Application

In the demo application, I want to show you an example of exporting data of different types (System.String, System.Double, System.Byte[], System.Drawing.Image) with the custom formatting and hyperlinks. The demo uses an API for Google Image Search library of Ilan Assayag in order to get images data for displaying them in a DataGridView.

Here is the result:

DemoApplication

For binding as a datasource, I'm using an array of GImage objects, which can be initialized by an instance of Ilan.Google.API.ImageSearch.SearchResult that contains information returned from Google Image Search for each image.

public class GImage
{
  // Methods
  public GImage(SearchResult _searchResult, string _searchTag);

  // Properties
  public string FullFileName { get; set; }
  public double ImageSize { get; set; }
  public string ImageUrl { get; set; }
  public string SearchTag { get; set; }
  public byte[] ThumbnailData { get; set; }
  public Image ThumbnailImage { get; set; }
  public string ThumbnailUrl { get; set; }
}

Custom ReportDataAdapter

In order to change metadata (columns width, formatting, DataCellViewType, etc.) gathered in DataGridViewReportDataAdapter, or to add grouping of rows, it's necessary to create a custom ReportDataAdapter class (in this demo, it is GImageReportDataAdapter) inherited from the DataGridViewReportDataAdapter class. The DataGridViewReportDataAdapter cannot initialize the ReportDataColumn.DefaultCellStyle.Border, because the DataGridView class does not contain the information about the border of its columns.

public class GImageReportDataAdapter : DataGridViewReportDataAdapterter
{
  private bool useGrouping;
  
  public GImageReportDataAdapter(DataGridView _dataGridView, 
         bool _useGrouping) : base(_dataGridView)
  {
    useGrouping = _useGrouping;
  }

  public override ReportColumnCollection GetColumns()
  {
    ReportColumnCollection toRet = new ReportColumnCollection();

    ReportColumnCollection baseColumns = base.GetColumns();

    ReportDataColumn rcSearchTag = baseColumns[0] as ReportDataColumn;
    ReportDataColumn rcFullFileName = baseColumns[1] as ReportDataColumn;
    ReportDataColumn rcThumbnailUrl = baseColumns[2] as ReportDataColumn;
    ReportDataColumn rcImageSize = baseColumns[3] as ReportDataColumn;
    ReportDataColumn rcImageUrl = baseColumns[4] as ReportDataColumn;
    ReportDataColumn rcThumbnailImage = baseColumns[5] as ReportDataColumn;
    ReportDataColumn rcThumbnailData = baseColumns[6] as ReportDataColumn;

    //Replace ReportDataColumn to ReportHyperlinkColumn
    ReportHyperlinkColumn hyperlinkColumnImageUrl = 
                   ReportHyperlinkColumn.ReplaceDataColumn(rcImageUrl);

    //set ImageUrl column as Hyperlink for ThumbnailUrl Column
    rcThumbnailUrl.HyperlinkColumn = hyperlinkColumnImageUrl;

    // Change ReportControl for ThumbnailData(byte[]) to Image instead TextBox
    CellViewImage databaseCellViewImage = 
       CellViewImage.CreateDatabaseImage(ImageMIMEType.Jpeg);
    databaseCellViewImage.Properties.Sizing = ImageSizing.FitProportional;
    rcThumbnailData.DataCellViewType = databaseCellViewImage;
    
    toRet.Add(rcSearchTag);
    toRet.Add(rcFullFileName);
    toRet.Add(rcThumbnailUrl);
    toRet.Add(rcImageSize);
    // add ReportHyperlinkColumn instead ReportDataColumn
    toRet.Add(hyperlinkColumnImageUrl);
    toRet.Add(rcThumbnailImage);

    //Skip rcThumbnailData because thumbnailDataDataGridViewImageColumn 
    //in DataGridView is invisible
    if (this.dataGridView.Columns[6].Visible)
    {
      toRet.Add(rcThumbnailData);
    }
    
    #region Apply custom formatting for ImageSize ReportDataColumn
    
    rcImageSize.DefaultCellStyle.Format = "N2";
    rcImageSize.TemplateFormat = "{0} Kb";
    
    #endregion

    #region Set custom BackgroundImage for FullFileName ReportDataColumn
    //Get embedded image
    Stream cellBackgroundImageStream = 
      GetResourceStream(Assembly.GetExecutingAssembly(), 
      "cellBackground.png");
    if (cellBackgroundImageStream != null)
    {
      BackgroundImage bgrdImage = new BackgroundImage(
        new EmbeddedImage("cellBackground_png", 
        Image.FromStream(cellBackgroundImageStream)));

      
      rcFullFileName.DefaultCellStyle.BackgroundImage = bgrdImage;
    }
    
    #endregion

    #region Apply custom border to all reportDataColumns instead first(rcSearchTag)

    Border customBorder = new Border();
    customBorder.Color = Color.Red;
    customBorder.Style = System.Web.UI.WebControls.BorderStyle.Dashed;
    customBorder.Width = new System.Web.UI.WebControls.Unit(2, 
                                  System.Web.UI.WebControls.UnitType.Point);
    foreach (ReportColumn rColumn in toRet)
    {
      if ((rColumn is ReportDataColumn) && (rColumn != rcSearchTag))
      {
        ((ReportDataColumn)rColumn).DefaultCellStyle.Border = customBorder;
      }
    }
    
    #endregion

    return toRet;
  }
 ......
}

Grouping

Microsoft Reporting Services allows to group data by criteria. In order to set grouping data, it's necessary to override the GetTableGroups method of the interface IReportDataAdapter. If grouping is not needed, the function should return null.

There is the implementation for GImageReportDataAdapter below:

public override ReportTableGroupList GetTableGroups(ReportColumnCollection columns)
{
  if (!useGrouping)
  {
    return null;
  }
  else
  {
    ReportTableGroupList gImagesGroupList = new ReportTableGroupList();
    {
      ReportTableGroup reportTableGroupBySearchTag = new ReportTableGroup();
      //group by SearchTag
      reportTableGroupBySearchTag.ColumnGrouping.Add(columns[0]);
      //sort by SearchTag(Descending)
      reportTableGroupBySearchTag.ColumnSorting.Add(columns[0],
        ReportExporters.Common.Rdlc.Enums.SortOrder.Descending);

      gImagesGroupList.Add(reportTableGroupBySearchTag);
    }
    return gImagesGroupList;
  }
}

In this demo, I used the ApplyRandomOrderToArray method for applying a random order for the GImage array. You can see the result of row data grouping by using SearchTag; also sort descending by SearchTag as in the screenshot below. The Google search query was "Paris; London; New York" and the checkbox "Use Grouping" was checked.

Grouping rows by SearchTag

Custom Export Settings

Report rendering can be customized by using Device Information Settings. I've written classes inherited from BaseDeviceInfoSettings for each available report rendering type. The PdfDeviceInfoSettings class (ExcelDeviceInfoSettings for exporting to Excel, and ImageDeviceInfoSettings for exporting to image) can be used for getting the DeviceInfo XML element and passing it to the method ExportToPdf(string deviceInfo) of the interface IReportExporter.

PdfDeviceInfoSettings deviceInfo = new PdfDeviceInfoSettings();
//set paper size to A3 (11in × 17in)
deviceInfo.PageHeight = new Unit(11, UnitType.Inch);
deviceInfo.PageWidth = new Unit(17, UnitType.Inch);
string deviceInfoXml = deviceInfo.ToString(); 
MemoryStream pdfFileData = winFormsReportExporter.ExportToPdf(deviceInfoXml);

Several Worksheets in one Excel Workbook

Much to my surprise MS Reporting services allows us to export reports in Excel workbook with several worksheets. It can be designed by placing one by one a few Rectangle controls in the main report and placing in each Rectangle control one Subreport control. In this case we can create an Excel workbook with up to 1000 worksheets. But there is a small problem in this method — on the second and further worksheets the first row is hidden (his height is equal to 1 Pixel).

In the code, in order to use the ability of adding extra worksheets to a workbook it's necessary to put List<IReportDataAdapter> (one instance of IReportDataAdapter per worksheet) in the constructor WinFormsReportExporter.

    List<IReportDataAdapter> sameAdapters = new List<IReportDataAdapter>();
    for (int aIndex = 0; aIndex < nudSheets.Value; aIndex++)
    {
        sameAdapters.Add(gImageReportDataAdapter);
    }
    winFormsReportExporter = new WinFormsReportExporter(sameAdapters);
    
    //Execute method ExportToXls to get Excel WorkBook with several worksheets
    MemoryStream xlsFileData = winFormsReportExporter.ExportToXls();
    ...
    //Than xlsFileData can be saved to local disk or etc.

Several worksheets

How to Export a DataSet

Class DataSetAdapterProvider allows you to create set of IReportDataAdapter for dataset tables. By default it creates objects of class DataViewReportDataAdapter (can be initialized by DataTable.DefaultView). Implementation of the DataViewReportDataAdapter class does not format data(columns). This task is for custom application developers.

DataSetAdapterProvider Methods

CreateAdapter Create IReportDataAdapter for DataTable (by default DataViewReportDataAdapter). Override to provide custom ReportDataAdapter with formatting.
ReorderAdapters Used to order ReportDataAdapter's list(Excel sheets).
GetAdapters Retreive ReportDataAdapter's list (one ReportDataAdapter per dataset table).
    DataSet myDataSet;
    ....
    DataSetAdapterProvider dsaProvider = new DataSetAdapterProvider(myDataSet);
    
    //Retreive list of IReportDataAdapters
    List<IReportDataAdapter> datasetAdapters = dsaProvider.GetAdapters();
    
    //Pass list of IReportDataAdapters to WinFormsReportExporter constructor
    winFormsReportExporter = new WinFormsReportExporter(datasetAdapters);
    
    //Execute method ExportToXls to get Excel file content
    MemoryStream xlsFileData = winFormsReportExporter.ExportToXls();
    ...
    //Than xlsFileData can be saved to local disk or etc.

Displayed in DataGrid exporting DataSet

Other Formats

Anton Ponomarev describes in his article, Adding DOC, RTF, and OOXML Export Formats to the Microsoft Report Viewer Control, adding a custom rendering extension to the standard Microsoft Report Viewer control. He modified the ReportViewer assemblies by using .NET tools. As a result, he got the Microsoft.ReportViewer.WinForms.Modified.dll assembly in which the Report Viewer component is able to generate reports in Microsoft Word formats (DOC, RTF, WordprocessingML, and OOXML) when it works in local mode.

I suppose if references in ReportExporters to Microsoft.ReportViewer.WinForms.dll is changed to Microsoft.ReportViewer.WinForms.Modified.dll, and the IReportExporter interface is extended, then it will be possible to export a DataGridView to DOC, RTF, WordprocessingML, and OOXML formats.

I don't have the Microsoft.ReportViewer.WinForms.Modified.dll yet. I'm going to investigate this problem soon.

Conclusion

With the ReportExporters library, you can:

  • Export a DataGridView/DataSet to native XLS instead of SpeedSheetXML. It is not required that MS Excel be installed.
  • Also export to PDF (uncompressed), BMP, EMF, GIF, JPEG, PNG, and TIFF formats.
  • Specify formatting (font, alignment, number format, border, background etc.) for exporting cells and column headers.
  • Specify the column list to export.
  • Add grouping and sorting for rows.
  • Add hyperlinks to Excel and PDF documents.
  • Add extra worksheets in Excel workbook.
  • Export embedded, external (located in the local system or in the Internet) images.

Points of Interest

It was very interesting and exciting for me to write this library. I hope it will be useful for developers who are looking for a similar solution.

Any suggestions and questions are welcome!

History

  • 2008-07-31: Article created.
  • 2008-08-18: Added features:
    • export to Excel workbook with several worksheets;
    • export System.Data.DataSet to workbook(one worksheet for each of the DataSet tables);

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)

About the Author

Andriy Protskiv
Software Developer
Ukraine Ukraine
No Biography provided

Comments and Discussions

 
QuestionIt's not working. Pinmemberxmans6-Apr-14 18:28 
Questionnot working Pinmembernasdasdc28-Dec-13 5:53 
AnswerExport GridView or DataGrid Data In Excel And Word File PinmemberMember 848689114-Oct-12 19:50 
Questionvb.net Pinmemberswapnil nashte9-Oct-12 1:12 
AnswerRe: vb.net PinmemberAndriy Protskiv9-Oct-12 1:26 
GeneralRe: vb.net Pinmemberswapnil nashte9-Oct-12 1:36 
GeneralMy vote of 5 Pinmembershalini_pahwa11-Apr-12 16:32 
Questioni want to upload images which is hyperlink on excel that images i want to save on Data Base Pinmembera20kush9-Nov-11 20:04 
AnswerRe: i want to upload images which is hyperlink on excel that images i want to save on Data Base PinmemberAndriy Protskiv9-Nov-11 21:59 
GeneralMy vote of 5 Pinmemberthatraja24-Sep-10 5:20 
QuestionHow do you use the ValueConverter, CustomBooleanConverter specifically? Pinmembermilmik29-Sep-09 22:17 
AnswerRe: How do you use the ValueConverter, CustomBooleanConverter specifically? Pinmemberandriy_p30-Sep-09 23:20 
GeneralRe: How do you use the ValueConverter, CustomBooleanConverter specifically? Pinmembermilmik5-Oct-09 18:21 
GeneralNice article PinmemberVirat Kothari11-Sep-09 9:50 
QuestionClasses missing? Pinmemberjsimunac8-Sep-09 8:26 
AnswerRe: Classes missing? Pinmemberandriy_p8-Sep-09 21:26 
GeneralRe: Classes missing? Pinmemberjsimunac9-Sep-09 9:35 
GeneralPowershell Script PinmemberMember 219725211-Apr-09 8:26 
GeneralNice! PinmemberRickey McWicky31-Mar-09 2:06 
Questionhow to export image from flexgrid to excel? Pinmemberngoctuan160830-Mar-09 0:32 
QuestionGreat job!! Is it possible to implement some new improvements to this library? [modified] PinmemberXikitin_2518-Feb-09 21:04 
General[Message Deleted] Pinmembergeorge.dt31-Jan-09 7:29 
GeneralRe: error PinmemberAndriy Protskiv9-Feb-09 23:28 
GeneralImpressive Work PinmemberAlaa Jebran19-Jan-09 6:42 
QuestionAnybody knows how to add page number on the pdf report? Pinmemberiiiiii16816-Jan-09 4:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140421.2 | Last Updated 20 Aug 2008
Article Copyright 2008 by Andriy Protskiv
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid