Excel具有强大的图表显示、分析功能,这点毋庸置疑,但是如果将常规MIS系统中的数据以报表的形式在Excel中显示,却并不那么容易。在VSTO中,我们可以借助RDLC报表组件来满足这种需求。
本系列所有示例代码均在 Visual Studio 2010 Ultimate RTM + Office 2010 Professional Plus Beta x64 下测试通过
注:本次数据库使用的是 SQL Server 2008 R2 x64 RTM
1、在VS2010中,新建一个Excel 2010 Workbook项目:
2、添加引用:
Microsoft.ReportViewer.WinForms
3、创建一个RDLC报表,添加一个报表参数p_Country:
报表的目的很简单,用的Northwind数据库,根据传递的参数,从Customers表中查询数据。
4、创建一个存储过程:
代码
BEGIN
DROP PROCEDURE dbo.sp_LinqTest
END
GO
CREATE PROCEDURE dbo.sp_LinqTest
(
@Country VARCHAR ( 20 )
)
AS
SELECT * FROM dbo.Customers WHERE Country = @Country
GO
5、在项目中添加一个Linq to SQL Class,将Customers表和sp_LinqTest存储过程添加进来:
6、打开Sheet1的设计界面,添加一个ComboBox:
7、初始化数据:
private void fnDataIni()
{
this .rptExcel.LocalReport.ReportEmbeddedResource = " RDLCInExcel.LinqRpt.rdlc " ;
Excel.Range range = this .Range[ " B4 " , " J22 " ];
this .Controls.AddControl( this .rptExcel, range, " rptInExcel " );
NorthwindDataContext ctx = new NorthwindDataContext();
var result = from c in ctx.Customers
select c.Country;
foreach ( string list in result.Distinct < string > ().ToList < string > ())
{
this .comCountry.Items.Add(list);
}
}
8、构造数据源,传递报表参数:
{
NorthwindDataContext ctx = new NorthwindDataContext();
var datasource = from c in ctx.sp_LinqTest(v_strCountry)
orderby c.CustomerID
select c;
ReportParameter rpCountry = new ReportParameter( " p_Country " , v_strCountry);
this .rptExcel.LocalReport.SetParameters( new ReportParameter[] { rpCountry });
this .rptExcel.LocalReport.DataSources.Add( new ReportDataSource( " sp_LinqTestResult " , datasource.ToList()));
this .rptExcel.RefreshReport();
}
9、关联事件:
{
this .fnDataIni();
this .fnBuildDataSource( this .comCountry.Text);
}
private void Sheet1_Shutdown( object sender, System.EventArgs e)
{
}
#region VSTO Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this .comCountry.SelectedIndexChanged += new System.EventHandler( this .comCountry_SelectedIndexChanged);
this .Startup += new System.EventHandler( this .Sheet1_Startup);
this .Shutdown += new System.EventHandler( this .Sheet1_Shutdown);
}
#endregion
private void comCountry_SelectedIndexChanged( object sender, EventArgs e)
{
this .rptExcel.LocalReport.DataSources.Clear();
this .fnBuildDataSource( this .comCountry.Text);
}
10、最终运行效果:
11、生产的Excel位于Debug/Release下:
小结:
本次我们在VSTO中做了一个简单的报表,使用了Linq to SQL,当然,如果能用Excel的原生功能做报表效果更好,使用VSTO可以大大简化实现相同功能的方法,使得我们可以用更熟悉的方法来完成一些需求。