Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / productivity / Office / MS-Excel
 

Create an Excel file using Report Viewer control

4.00/5 (3 votes)
20 Jun 2012CPOL4 min read 77.3K  
Create an Excel file from data table without Excel using Report Viewer control.

Introduction 

I work as software developer in a small, local and independent company. We have a payroll, human resources, and time and attendance for "maquila" companies in my region (north of Mexico). The environments of work are so diverse, Windows XP, Windows 7, good servers, bad servers, no servers at all (PC's used as servers), we needed to be able to generate Microsoft Excel files. Our software is developed in C# using SQL Server as database, and we have no problem to generate Excel from reports, but we needed the ability to generate Excel files from data tables, grids, even from capture screens. At this point things turned very hard because of the diverse working environments. 

I did an extensive search for a solution to my specific situation, and I find many options to generate Excel files, but all of them that I tried did't work in some of the environments that I described.  

However, almost all of them involved a more or less direct interface with Excel (typically by adding a reference to your VS project, instantiating Excel, etc). The lower tech ones don't require that Microsoft Office be installed, since some generate an HTML or XML or CSV file then change the extension to XLS/XLSX, but they needed that we configure Excel 2010 file blocking settings to allow open files. The same thing happened with a solution that creates an Excel 2.0 file from scratch.

Background    

I'm so tired of seeking a fully compatible way to generate Excel files without Excel, and we not have the resources and the logistic to go to every final user PC to configure Excel 2010, or take the phone calls of our clients to explain how to use the "Save as" option to make files generated by our system writable.  

As many of you know, Reporting Services is included with Visual Studio, and is capable of generating reports based on an XML file definition. the only thing that you need to do is create a report, view in screen and save the report as Excel file. 

Please note that this article is not intended to explain how to create a report or using of report viewer, to see that there is a good article here 

Suppose that you has created a report based on "TestTable" table, now you have an rdlc file, that contains XML code like this: 

XML
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" 
        xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <DataSources>
    <DataSource Name="demoConnectionString">
      <rd:DataSourceID>355af724-cf11-4965-afbe-b557985d7a67</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=somepc\SQLEXPRESS;Initial Catalog=northwind;Integrated Security=True</ConnectString>
      </ConnectionProperties>
    </DataSource>
  </DataSources>
  <InteractiveHeight>11in</InteractiveHeight>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>8.5in</InteractiveWidth>
  <rd:SnapToGrid>true</rd:SnapToGrid>
  <RightMargin>1in</RightMargin>
  <LeftMargin>1in</LeftMargin>
  <BottomMargin>1in</BottomMargin>
  <rd:ReportID>2c57f813-06b5-4d66-ba3c-30a110b82129</rd:ReportID>
  <DataSets>
    <DataSet Name="testDataSet_testtable">
      <Fields>
        <Field Name="column1">
          <DataField>COLUMN</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="column2">
          <DataField>COLUMN2</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>tesConnectionString</DataSourceName>
        <CommandText>SELECT COLUMN1, COLUMMN FROM dbo.TESTTABLE</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <rd:DataSetInfo>
        <rd:DataSetName>testDataSet</rd:DataSetName>
        <rd:TableName>TestTable</rd:TableName>
        <rd:TableAdapterName>TestTableAdapter</rd:TableAdapterName>
        <rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
        <rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
      </rd:DataSetInfo>
    </DataSet>
  </DataSets>
  <Width>6.5in</Width>
  <Body>
    <ReportItems>
      <Table Name="table1">
        <DataSetName>testDataSet_testtable</DataSetName>
        <Width>4.33334in</Width>
        <Details>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="COLUMN1">
                      <rd:DefaultName>COLUMN1</rd:DefaultName>
                      <Style>
                        <BorderColor>
                          <Default>Green</Default>
                        </BorderColor>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <BorderWidth>
                          <Default>0.5pt</Default>
                        </BorderWidth>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>1pt</PaddingLeft>
                        <PaddingRight>1pt</PaddingRight>
                        <PaddingTop>1pt</PaddingTop>
                        <PaddingBottom>1pt</PaddingBottom>
                      </Style>
                      <Value>=Fields!COLUMN1.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="COLUMN2">
                      <rd:DefaultName>COLUMN2</rd:DefaultName>
                      <Style>
                        <BorderColor>
                          <Left>Green</Left>
                        </BorderColor>
                        <BorderStyle>
                          <Left>Solid</Left>
                        </BorderStyle>
                        <BorderWidth>
                          <Left>0.5pt</Left>
                        </BorderWidth>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>=Fields!COLUMN2.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>0.125in</Height>
            </TableRow>
          </TableRows>
        </Details>
        <Header>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox1">
                      <rd:DefaultName>textbox1</rd:DefaultName>
                      <Style>
                        <Color>White</Color>
                        <BackgroundColor>DimGray</BackgroundColor>
                        <BorderColor>
                          <Bottom>Green</Bottom>
                        </BorderColor>
                        <BorderStyle>
                          <Bottom>Solid</Bottom>
                        </BorderStyle>
                        <BorderWidth>
                          <Bottom>0.5pt</Bottom>
                        </BorderWidth>
                        <TextAlign>Right</TextAlign>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>COLUMN1</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="textbox2">
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                      <CanGrow>true</CanGrow>
                      <Value>COLUMN2</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>0.25in</Height>
            </TableRow>
          </TableRows>
        </Header>
        <TableColumns>
          <TableColumn>
            <Width>2.16667in</Width>
          </TableColumn>
          <TableColumn>
            <Width>2.16667in</Width>
          </TableColumn>
        </TableColumns>
        <Height>0.625in</Height>
      </Table>
    </ReportItems>
    <Height>0.75in</Height>
  </Body>
  <Language>en-US</Language>
  <TopMargin>1in</TopMargin>
</Report>

in a form with report viewer control, you assign the file to LocalReport.ReporPath property and call RefreshReport method to preview report, at this point, you can save the report in PDF or Excel formats. But, we cannot design one report for each table, or redesign them when column is added or removed from the table. 

Removing connections to SQL

We want to generate report based on DataTable object, not in SQL Server table, because data table can be populated from server or with results of memory processes that not exists in the server, to do that change the datasources section on XML file. 

XML
<DataSources> 
    <DataSource Name=ConexionLocal>
      <rd:DataSourceID>dsid</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString></ConnectString>
      </ConnectionProperties>
    </DataSource>
</DataSources>

Change Datasets section  

Dataset Name property is very important, because this code is designed to work with datables that must vary on columns and names, so you need to give a common name. 

XML
<DataSets>
<DataSet Name=dsReporte>
  <Fields>
    <Field Name="column1">
          <DataField>column1</DataField>
          <rd:TypeName>System.String32</rd:TypeName>
    </Field>
    <Field Name="column2">
          <DataField>column2</DataField>
          <rd:TypeName>System.String32</rd:TypeName>
    </Field>
  </Fields>
  <Query>
    <DataSourceName>ConexionLocal</DataSourceName>
    <CommandText></CommandText>
    <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  </Query>
  <rd:DataSetInfo>
    <rd:DataSetName>dsReporte</rd:DataSetName>
  </rd:DataSetInfo>
</DataSet>
</DataSets>

At this point, our XML file code is

XML
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" 
      xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <DataSources>
    <DataSource Name="ConexionLocal">
      <rd:DataSourceID>dsid</rd:DataSourceID>
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString></ConnectString>
      </ConnectionProperties>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="dsReporte">
      <Fields>
        <Field Name="column1">
          <DataField>column1</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="column2">
          <DataField>column2</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>ConexionLocal</DataSourceName>
        <CommandText></CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <rd:DataSetInfo>
        <rd:DataSetName>dsReporte</rd:DataSetName>
      </rd:DataSetInfo>
    </DataSet>
  </DataSets>
  <Width>8.25in</Width>
  <InteractiveHeight>11in</InteractiveHeight>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>8.5in</InteractiveWidth>
  <rd:SnapToGrid>false</rd:SnapToGrid>
  <RightMargin>0.125in</RightMargin>
  <LeftMargin>0.125in</LeftMargin>
  <BottomMargin>0in</BottomMargin>
  <rd:ReportID>c574eb02-0ff6-4305-bfa1-5bfbffb4f42c</rd:ReportID>
  <Body>
    <ReportItems>
      <Table Name="Detalle">
        <DataSetName>dsReporte</DataSetName>
        <Top>0in</Top>
        <Width>12in</Width>
        <Details>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="column1">
                      <rd:DefaultName>column1</rd:DefaultName>
                      <Style>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <BorderWidth>
                          <Default>0.5pt</Default>
                        </BorderWidth>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>1pt</PaddingLeft>
                        <PaddingRight>1pt</PaddingRight>
                        <PaddingTop>1pt</PaddingTop>
                        <PaddingBottom>1pt</PaddingBottom>
                      </Style>
                      <CanGrow>false</CanGrow>
                      <Value>=Fields!column1.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="column2">
                      <rd:DefaultName>column2</rd:DefaultName>
                      <Style>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <BorderWidth>
                          <Default>0.5pt</Default>
                        </BorderWidth>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>1pt</PaddingLeft>
                        <PaddingRight>1pt</PaddingRight>
                        <PaddingTop>1pt</PaddingTop>
                        <PaddingBottom>1pt</PaddingBottom>
                      </Style>
                      <CanGrow>false</CanGrow>
                      <Value>=Fields!column2.Value</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>0.1875in</Height>
            </TableRow>
          </TableRows>
        </Details>
        <Header>
          <TableRows>
            <TableRow>
              <TableCells>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="header_column1">
                      <rd:DefaultName>header_column1</rd:DefaultName>
                      <Style>
                        <Color>White</Color>
                        <BackgroundColor>DimGray</BackgroundColor>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <BorderWidth>
                          <Default>0.5pt</Default>
                        </BorderWidth>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>1pt</PaddingLeft>
                        <PaddingRight>1pt</PaddingRight>
                        <PaddingTop>1pt</PaddingTop>
                        <PaddingBottom>1pt</PaddingBottom>
                      </Style>
                      <CanGrow>false</CanGrow>
                      <Value>column1</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
                <TableCell>
                  <ReportItems>
                    <Textbox Name="header_column2">
                      <rd:DefaultName>header_column2</rd:DefaultName>
                      <Style>
                        <Color>White</Color>
                        <BackgroundColor>DimGray</BackgroundColor>
                        <BorderStyle>
                          <Default>Solid</Default>
                        </BorderStyle>
                        <BorderWidth>
                          <Default>0.5pt</Default>
                        </BorderWidth>
                        <TextAlign>Left</TextAlign>
                        <PaddingLeft>1pt</PaddingLeft>
                        <PaddingRight>1pt</PaddingRight>
                        <PaddingTop>1pt</PaddingTop>
                        <PaddingBottom>1pt</PaddingBottom>
                      </Style>
                      <CanGrow>false</CanGrow>
                      <Value>column2</Value>
                    </Textbox>
                  </ReportItems>
                </TableCell>
              </TableCells>
              <Height>0.1875in</Height>
            </TableRow>
          </TableRows>
        </Header>
        <TableColumns>
          <TableColumn>
            <Width>1in</Width>
          </TableColumn>
          <TableColumn>
            <Width>1in</Width>
          </TableColumn>
        </TableColumns>
        <Height>0.1875in</Height>
      </Table>
    </ReportItems>
    <Height>2in</Height>
  </Body>
  <Language>en-US</Language>
  <TopMargin>0in</TopMargin>
</Report>

Now we have an rdlc file with XML code that we can use as source for report viewer  

Create ReportViewer control from code 

Remember, I use ReportViewer control to generate Excel file from DataTable objects, is not intended to preview data on screen (Microsoft.Reporting.WinForms namespace). 

C#
private ReportViewerControl = new ReportViewer();

ReportViewer control uses a ReportDataSource object as data source, Note that the name assigned in contructor parameter is the same name of DataSet Name property in DataSets section of the XML file:  

C#
private ReportDataSource ReportDataSourceObject = new ReportDataSource("dsReporte");

ReportDataSource object, uses a BindingSource control as his own datasource, create BindingSource control from code: 

C#
private BindingSource BindingSourceObject = new BindingSource();

Generating Excel file 

First, we need to create and populate DataTable object: 

C#
//create very basic data table
DataTable TestTable=new DataTable();

//add two columns to data table
TestTable.Columns.Add("column1",typeof(string));
TestTable.Columns.Add("column2",typeof(string));
//create datarow object to add data to datatable
DataRow nRow = null;
//adding two records to data table
nRow=TestTable.NewRow();
nRow["column1"]="row1 column1";
nRow["column2"] = "row1 column2";
nRow.EndEdit();
TestTable.Rows.Add(nRow);
nRow = TestTable.NewRow();
nRow["column1"] = "row2 column1";
nRow["column2"] = "row2 column2";
nRow.EndEdit();
TestTable.Rows.Add(nRow);

Running the example 

C#
//assign default view of the data table binding source
BindingSourceObject.DataSource = TestTable.DefaultView;
//assign binding source to report data source
ReportDataSourceObject.Value = BindingSourceObject;
//clean all previous datasources on report viewer
ReportViewerControl.LocalReport.DataSources.Clear();
//add report data source
ReportViewerControl.LocalReport.DataSources.Add(ReportDataSourceObject);
//define path of rdcl file (XML code defined previously)
ReportViewerControl.LocalReport.ReportPath = "c:\\report1.rdlc";
//generate report as stream of bytes
Microsoft.Reporting.WinForms.Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;
byte[] bytes = ReportViewerControl.LocalReport.Render("Excel", "", 
         out mimeType, out encoding, out extension, out streamids, out warnings);
//create file stream in create mode
FileStream fs = new FileStream("c:\\report1.xls", FileMode.Create);
//create Excel file
fs.Write(bytes, 0, bytes.Length);
fs.Close();

Review (functional example) 

Now put the code together and make a call properly.

C#
using System; 
using System.IO;
using System.Text;
using System.Data;
using Microsoft.Win32;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;
using Microsoft.VisualBasic;
using Microsoft.Reporting.WinForms;
namespace ExcelGen
{
    {
        private ReportViewer ReportViewerControl = new ReportViewer();
        private ReportDataSource ReportDataSourceObject = new ReportDataSource("dsReporte");
        private BindingSource BindingSourceObject = new BindingSource();
        public void GenerateExcelRS(ref DataTable pTestTable)
        {
            //assign default view of the data table binding source
            this.BindingSourceObject.DataSource = pTestTable.DefaultView;
            //assign binding source to report data source
            this.ReportDataSourceObject.Value = this.BindingSourceObject;
            //clean all previous datasources on report viewer
            this.ReportViewerControl.LocalReport.DataSources.Clear();
            //add report data source
            this.ReportViewerControl.LocalReport.DataSources.Add(this.ReportDataSourceObject);
            //define path of rdcl file (XML code defined previously)
            this.ReportViewerControl.LocalReport.ReportPath = "c:\\report1.rdlc";
            //generate report as stream of bytes
            Warning[] warnings;
            string[] streamids;
            string mimeType;
            string encoding;
            string extension;
            byte[] bytes = this.ReportViewerControl.LocalReport.Render("Excel", "", 
               out mimeType, out encoding, out extension, out streamids, out warnings);
            //create file stream in create mode
            FileStream fs = new FileStream("c:\\report1.xls", FileMode.Create);
            //create Excel file
            fs.Write(bytes, 0, bytes.Length);
            fs.Close();
        }
 
    }
}

To call and generate Excel: 

C#
static void Main()
{
    //create very basic data table
    DataTable TestTable=new DataTable();
     
    //add two columns to data table
    TestTable.Columns.Add("column1",typeof(string));
    TestTable.Columns.Add("column2",typeof(string));
    //create datarow object to add data to datatable
    DataRow nRow = null;
    //adding two records to data table
    nRow=TestTable.NewRow();
    nRow["column1"]="row1 column1";
    nRow["column2"] = "row1 column2";
    nRow.EndEdit();
    TestTable.Rows.Add(nRow);
    nRow = TestTable.NewRow();
    nRow["column1"] = "row2 column1";
    nRow["column2"] = "row2 column2";
    nRow.EndEdit();
    TestTable.Rows.Add(nRow);
    ExcelGen.ExcelRS ers = new ExcelRS();
    ers.GenerateExcelRS(ref TestTable) 
}

Not yet   

Now we can generate Excel file from data table, but, what if data table changes? Column number and type? Then we need generate the rdlc on the fly, much better, generate string and pass that string instead of report path to ReportViewerControl.

Generate XML report definition on the fly 

Based on rdlc file contents, we can generate a string with the same XML code encoded, here is my solution:   

First, add this function to generate a string with XML code inside. 

C#
//add this function to ExcelRS class 
private string GenerateXMLString(ref DataTable pTestTable)
{ 
    //I use the same code of the rdlc file
    //only add quotation codes and carriage return at the end of the lines
    string ReturnString = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n" +
        "<Report xmlns=\"http://schemas.microsoft.com/sqlserver/reporting/2005/01/" + 
        "reportdefinition\" xmlns:rd=\"http://schemas.microsoft.com/" + 
        "SQLServer/reporting/reportdesigner\">\n" +
        "  <DataSources>\n" +
        "    <DataSource Name=\"ConexionLocal\">\n" +
        "      <rd:DataSourceID>dsid</rd:DataSourceID>\n" +
        "      <ConnectionProperties>\n" +
        "        <DataProvider>SQL</DataProvider>\n" +
        "        <ConnectString></ConnectString>\n" +
        "      </ConnectionProperties>\n" +
        "    </DataSource>\n" +
        "  </DataSources>\n" +
        "  <DataSets>\n" +
        "    <DataSet Name=\"dsReporte\">\n" +
        "      <Fields>\n";
    //Generate field definition for each column in data table
    foreach (DataColumn lColumn in pTestTable.Columns)
    {
        ReturnString += "        <Field Name=\"" + lColumn.ColumnName + "\">\n" +
            "          <DataField>" + lColumn.ColumnName + "</DataField>\n" +
            "          <rd:TypeName>" + lColumn.DataType.ToString() + "</rd:TypeName>\n" +
            "        </Field>\n";
    }
    ReturnString += "      </Fields>\n" +
        "      <Query>\n" +
        "        <DataSourceName>ConexionLocal</DataSourceName>\n" +
        "        <CommandText></CommandText>\n" +
        "        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>\n" +
        "      </Query>\n" +
        "      <rd:DataSetInfo>\n" +
        "        <rd:DataSetName>dsReporte</rd:DataSetName>\n" +
        "      </rd:DataSetInfo>\n" +
        "    </DataSet>\n" +
        "  </DataSets>\n" +
        "  <Width>8.25in</Width>\n" +
        "  <InteractiveHeight>11in</InteractiveHeight>\n" +
        "  <rd:DrawGrid>true</rd:DrawGrid>\n" +
        "  <InteractiveWidth>8.5in</InteractiveWidth>\n" +
        "  <rd:SnapToGrid>false</rd:SnapToGrid>\n" +
        "  <RightMargin>0.125in</RightMargin>\n" +
        "  <LeftMargin>0.125in</LeftMargin>\n" +
        "  <BottomMargin>0in</BottomMargin>\n" +
        "  <rd:ReportID>c574eb02-0ff6-4305-bfa1-5bfbffb4f42c</rd:ReportID>\n" +
        "  <Body>\n" +
        "    <ReportItems>\n" +
        "      <Table Name=\"Detalle\">\n" +
        "        <DataSetName>dsReporte</DataSetName>\n" +
        "        <Top>0in</Top>\n" +
        "        <Width>12in</Width>\n" +
        "        <Details>\n" +
        "          <TableRows>\n" +
        "            <TableRow>\n" +
        "              <TableCells>\n";
    //Generate cell and textbox definition for each column in data table
    foreach (DataColumn lColumn in pTestTable.Columns)
    {
        ReturnString += "                <TableCell>\n" +
            "                  <ReportItems>\n" +
            "                    <Textbox Name=\"" + lColumn.ColumnName + "\">" +
            "                      <rd:DefaultName>" + lColumn.ColumnName + "</rd:DefaultName>\n" +
            "                      <Style>\n" +
            "                        <BorderStyle><Default>Solid</Default></BorderStyle>\n" +
            "                        <BorderWidth><Default>0.5pt</Default></BorderWidth>\n" +
            "                        <TextAlign>Left</TextAlign>\n" +
            "                        <PaddingLeft>1pt</PaddingLeft>\n" +
            "                        <PaddingRight>1pt</PaddingRight>\n" +
            "                        <PaddingTop>1pt</PaddingTop>\n" +
            "                        <PaddingBottom>1pt</PaddingBottom>\n" +
            "                      </Style>\n" +
            "                      <CanGrow>false</CanGrow>\n" +
            "                      <Value>=Fields!" + lColumn.ColumnName + ".Value</Value>\n" +
            "                    </Textbox>\n" +
            "                  </ReportItems>\n" +
            "                </TableCell>\n";
    }
    ReturnString += "</TableCells>\n" +
        "            <Height>0.1875in</Height>\n" +
        "            </TableRow>\n" +
        "          </TableRows>\n" +
        "        </Details>\n" +
        "        <Header>\n" +
        "          <TableRows>\n" +
        "            <TableRow>\n" +
        "              <TableCells>\n";
    //Generate cell and header textbox definition for each column in data table
    foreach (DataColumn lColumn in pTestTable.Columns)
    {
        ReturnString += "                <TableCell>\n" +
            "                  <ReportItems>\n" +
            "                    <Textbox Name=\"header_" + lColumn.ColumnName + "\">\n" +
            "                      <rd:DefaultName>header_" + lColumn.ColumnName + "</rd:DefaultName>\n" +
            "                      <Style>\n" +
            "                        <Color>White</Color>\n" +
            "                        <BackgroundColor>DimGray</BackgroundColor>\n" +
            "                        <BorderStyle><Default>Solid</Default></BorderStyle>\n" +
            "                        <BorderWidth><Default>0.5pt</Default></BorderWidth>\n" +
            "                        <TextAlign>Left</TextAlign>\n" +
            "                        <PaddingLeft>1pt</PaddingLeft>\n" +
            "                        <PaddingRight>1pt</PaddingRight>\n" +
            "                        <PaddingTop>1pt</PaddingTop>\n" +
            "                        <PaddingBottom>1pt</PaddingBottom>\n" +
            "                      </Style>\n" +
            "                      <CanGrow>false</CanGrow>\n" +
            "                      <Value>" + lColumn.ColumnName + "</Value>\n" +
            "                    </Textbox>\n" +
            "                  </ReportItems>\n" +
            "                </TableCell>\n";
    }
    ReturnString += "</TableCells>\n" +
        "            <Height>0.1875in</Height>\n" +
        "            </TableRow>\n" +
        "          </TableRows>\n" +
        "        </Header>\n" +
        "        <TableColumns>\n";
    //Generate generate report columns for each column in data table
    foreach (DataColumn lColumn in pTestTable.Columns)
    {
        ReturnString += "          <TableColumn>\n" +
        "            <Width>1in</Width>\n" +
        "          </TableColumn>\n";
    }
    ReturnString += "        </TableColumns>\n" +
        "        <Height>0.1875in</Height>\n" +
        "      </Table>\n" +
        "    </ReportItems>\n" +
        "    <Height>2in</Height>\n" +
        "  </Body>\n" +
        "  <Language>en-US</Language>\n" +
        "  <TopMargin>0in</TopMargin>\n" +
        "</Report>";
    return ReturnString;
}

Now that we have the string with the report definition, we need to convert that string in bytes stream, encoded on UTF-8 and convert those bytes to Memory stream. Add this function to the ExcelRS class: 

C#
private MemoryStream EncodeString(ref DataTable pTestTable)
{
    //Call function that returns string with report definition
    string strDef = GenerateXMLString(ref pTestTable);

    //Convert the string in byte stream enconded on UTF-8
    //note that this encoding is showing in the first line
    //of rdlc files, well, in my applications
    byte[] strbytes = Encoding.UTF8.GetBytes(strDef);


    //now return bytes as memory stream 
    //just like the same structure of filestream
    return new System.IO.MemoryStream(strbytes);
}

and now we are capable to generate Excel files for any data table,  replace this function on ExcelRS class: 

C#
public void GenerateExcelRS(ref DataTable pTestTable)
{
    //get the report definition generated for the data table
    MemoryStream lStream = EncodeString(ref pTestTable);
    //assign default view of the data table binding source
    this.BindingSourceObject.DataSource = pTestTable.DefaultView;
    //assign binding source to report data source
    this.ReportDataSourceObject.Value = this.BindingSourceObject;
    //clean all previous datasources on report viewer
    this.ReportViewerControl.LocalReport.DataSources.Clear();
    //add report data source
    this.ReportViewerControl.LocalReport.DataSources.Add(this.ReportDataSourceObject);
    //load report definition from memory
    this.ReportViewerControl.LocalReport.LoadReportDefinition(lStream);

    //generate report as stream of bytes
    Warning[] warnings;
    string[] streamids;
    string mimeType;
    string encoding;
    string extension;
    byte[] bytes = this.ReportViewerControl.LocalReport.Render("Excel", 
      "", out mimeType, out encoding, out extension, out streamids, out warnings);
    //create file stream in create mode
    FileStream fs = new FileStream("c:\\report1.xls", FileMode.Create);
    //create Excel file
    fs.Write(bytes, 0, bytes.Length);
    fs.Close();
}

Dynamic reporting

As you can see, if you change the word "Excel" on render method to "PDF", you can generate PDF files on the fly too, and as you noted, you can generate dynamic reports modifying this implementation. good luck and enjoy! 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2012 by Antonio Lopez R
Everything else Copyright © CodeProject, 1999-2024

Web01 2.8:2024-10-20:1