使用报表查看器控件创建 Excel 文件
4.00/5 (3投票s)
使用 Report Viewer 控件从数据表中创建 Excel 文件，无需 Excel。
介绍
我在一家小型、本地、独立的公司担任软件开发人员。我们为我所在地区（墨西哥北部）的“maquila”公司提供薪资、人力资源和考勤管理。工作环境多种多样，有 Windows XP、Windows 7，有好的服务器、坏的服务器，甚至没有服务器（PC 用作服务器），我们需要能够生成 Microsoft Excel 文件。我们的软件是用 C# 开发的，使用 SQL Server 作为数据库，我们生成 Excel 报告没有问题，但我们需要能够从数据表、网格甚至屏幕捕获生成 Excel 文件。到目前为止，由于工作环境的多样性，事情变得非常困难。
我对我的具体情况进行了广泛的解决方案搜索，发现了很多生成 Excel 文件的选项，但**所有**我尝试过的选项在上述某些环境中都无法正常工作。
然而，几乎所有这些方法都或多或少地直接与 Excel 交互（通常是通过向您的 VS 项目添加引用、实例化 Excel 等）。技术含量较低的那些不需要安装 Microsoft Office，因为它们会生成 HTML、XML 或 CSV 文件，然后更改扩展名为 XLS/XLSX，但它们需要我们配置 Excel 2010 文件阻止设置以允许打开文件。使用从头开始创建 Excel 2.0 文件的解决方案也遇到了同样的问题。
背景
我厌倦了寻找一种完全兼容的、无需 Excel 即可生成 Excel 文件的方法，而且我们没有资源和后勤能力去逐个最终用户 PC 配置 Excel 2010，或者接听客户的电话来解释如何使用“另存为”选项来使我们系统生成的文件可写。
正如你们中的许多人所知，Reporting Services 包含在 Visual Studio 中，它能够基于 XML 文件定义生成报告。您需要做的就是创建一个报告，在屏幕上查看，然后将报告另存为 Excel 文件。
请注意，本文档无意解释如何创建报告或使用报表查看器，有关这方面的信息，请参阅这篇精彩文章。
假设您已经创建了一个基于“TestTable”表的报告，现在您有一个 rdlc 文件，其中包含如下 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>
在一个带有报表查看器控件的窗体中，您将文件分配给 LocalReport.ReporPath 属性并调用 RefreshReport 方法进行预览，此时，您可以将报告保存为 PDF 或 Excel 格式。但是，我们不能为每个表设计一个报告，或者在表中添加或删除列时重新设计它们。
移除 SQL 连接
我们希望基于 DataTable 对象生成报告，而不是基于 SQL Server 表，因为数据表可以从服务器填充，也可以用不存在于服务器的内存进程结果填充，要做到这一点，请更改 XML 文件中的数据源部分。
<DataSources>
<DataSource Name=ConexionLocal>
<rd:DataSourceID>dsid</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString></ConnectString>
</ConnectionProperties>
</DataSource>
</DataSources>
更改数据集部分
Dataset Name 属性非常重要，因为此代码设计用于处理列名和名称可变的 datables，因此您需要为其指定一个通用名称。
<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>
此时，我们的 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>
现在我们有了一个 rdlc 文件，其中包含我们可以用作报表查看器源的 XML 代码。
从代码创建 ReportViewer 控件
请记住，我使用 ReportViewer 控件从 DataTable 对象生成 Excel 文件，而不用于在屏幕上预览数据（Microsoft.Reporting.WinForms 命名空间）。
private ReportViewerControl = new ReportViewer();
ReportViewer 控件使用 ReportDataSource 对象作为数据源，请注意，构造函数参数中指定的名称与 XML 文件中 DataSets 部分的 DataSet Name 属性名称相同：
private ReportDataSource ReportDataSourceObject = new ReportDataSource("dsReporte");
ReportDataSource 对象使用 BindingSource 控件作为其自己的数据源，从代码创建 BindingSource 控件：
private BindingSource BindingSourceObject = new BindingSource();
生成 Excel 文件
首先，我们需要创建并填充 DataTable 对象：
//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);
运行示例
//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();
回顾（功能示例）
现在将代码放在一起并正确调用。
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();
}
}
}
调用并生成 Excel：
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)
}
尚未完成
现在我们可以从数据表中生成 Excel 文件，但是，如果数据表发生变化呢？列数和类型？然后我们需要即时生成 rdlc，更好的是，生成字符串并将其作为字符串传递给 ReportViewerControl，而不是报告路径。
动态生成 XML 报告定义
基于 rdlc 文件内容，我们可以生成一个包含相同 XML 代码的字符串。这是我的解决方案：
首先，添加此函数来生成包含 XML 代码的字符串。
//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;
}
现在我们有了报告定义字符串，我们需要将该字符串转换为字节流，用 UTF-8 编码，然后将这些字节转换为 Memory stream。将此函数添加到 ExcelRS 类：
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);
} 现在我们可以为任何数据表生成 Excel 文件，替换 ExcelRS 类中的此函数：
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();
}
动态报告
如您所见，如果您将 render 方法中的“Excel”更改为“PDF”，您也可以即时生成 PDF 文件，并且正如您所注意到的，您可以通过修改此实现来生成动态报告。祝您好运，享受！