...
Code Sample: Passing ExcelTemplate to ExcelApplication
Csharp |
---|
//--------------------------------------------------------------
//--- SoftArtisans OfficeWriter ExcelApplication Template-To-App Sample
//---
//--- Demonstrates how the ExcelTemplate and ExcelApplication objects
//--- can be used together in the same request.
//--- Populate an ExcelTemplate workbook with data and then open the
//--- populated workbook with the ExcelApplication API for further edits.
//---
//--- (c) 2009 SoftArtisans, Inc.
//--- Support: http://support.softartisans.com
//--- Sales: sales@softartisans.com
//--------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SoftArtisans.OfficeWriter.ExcelWriter;
namespace SoftArtisans.OfficeWriter.ExcelWriter.Samples
{
/// <summary>
/// Summary description for TemplateToApp.
/// </summary>
public class TemplateToApp : System.Web.UI.Page
{
/* Both ExcelApplication and ExcelTemplate
* objects will be used for this demo.
*/
private ExcelTemplate xlt;
private ExcelApplication xlw;
private Workbook wb;
protected System.Web.UI.HtmlControls.HtmlInputButton btnTemplateToApp;
private string connString =
System.Configuration.ConfigurationManager.AppSettings["connString"];
private void btnTemplateToApp_ServerClick(object sender, System.EventArgs e)
{
GenerateReport();
}
/// <summary>
/// Build the report with ExcelApplication
/// </summary>
private void GenerateReport()
{
PopulateTemplate();
AddChart();
/* Save the report by streaming it
* to the client's browser */
xlw.Save(wb, Page.Response, "TemplateToApp.xls", false);
}
/// <summary> Add a column chart to the second worksheet using ExcelApplication.
/// The chart will show data imported with the ExcelTemplate
/// object.</summary>
private void AddChart()
{
/* Get the first two worksheets and give them names */
Worksheet ws = wb.Worksheets[0];
ws.Name = "Data";
Worksheet ws2 = wb.Worksheets.CreateWorksheet("ChartSheet");
/* Create a chart on the second worksheet */
Anchor anch = ws2.CreateAnchor(0, 0, 50, 50);
Chart chrt = ws2.Charts.CreateChart(ChartType.Column.Clustered, anch);
/* Set series and category data */
Series srs1 = chrt.SeriesCollection.CreateSeries("=Data!B2:B7");
chrt.SeriesCollection.CategoryData = "=Data!A2:A7";
srs1.NameFormula = "=Data!A1";
/* Configure the chart's legend */
Legend lgnd = chrt.Legend;
lgnd.Visible = true;
lgnd.Location = Legend.LegendLocation.Right;
/* Set the chart's Title string and display properties. */
chrt.Title.Text = "AdventureWorks Global Sales";
}
/// <summary> Populate the template workbook with database
/// data using the ExcelTemplate object. The ExcelTemplate
/// save() method returns a Workbook.
/// </summary>
private void PopulateTemplate()
{
/* Create an instance of ExcelTemplate and open
* the template workbook
*/
xlt = new ExcelTemplate();
/* Open the template workbook */
string templatePath = Page.MapPath("templates/TemplateToAppTemplate.xls");
xlt.Open(templatePath);
/* Get the data from the database and set it as
* a data source.
*/
DataTable dt = GetData();
xlt.BindData(dt, "Sales", xlt.CreateDataBindingProperties());
xlt.Process();
/* Here the populated ExcelTemplate object
* is being opened as an ExcelApplication Workbook.
* The object can now be programatically manipulated
* with the ExcelApplication API
*/
xlw = new ExcelApplication();
wb = xlw.Open(xlt);
}
/// <summary> Query the database for the data that will
/// be imported by the ExcelTemplate object.
/// </summary>
/// <returns>DataTable of data for the report</returns>
private DataTable GetData()
{
string sql = "SELECT Person.StateProvince.CountryRegionCode As Country, " +
"SUM(Sales.SalesOrderHeader.TotalDue) As TotalSales " +
"FROM Sales.SalesOrderHeader " +
"JOIN Sales.CustomerAddress " +
"ON Sales.SalesOrderHeader.CustomerID=Sales.CustomerAddress.CustomerID " +
"JOIN Person.Address " +
"ON Sales.CustomerAddress.AddressID=Person.Address.AddressID " +
"JOIN Person.StateProvince " +
"ON Person.StateProvince.StateProvinceID=Person.Address.StateProvinceID " +
"GROUP BY Person.StateProvince.CountryRegionCode";
DataTable dt = new DataTable();
using(SqlConnection conn = new SqlConnection(connString))
new SqlDataAdapter(sql, conn).Fill(dt);
return dt;
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnTemplateToApp.ServerClick += new System.EventHandler(this.btnTemplateToApp_ServerClick);
}
#endregion
}
}
|
Vbnet |
---|
Option Strict On '-------------------------------------------------------------- Imports System.Data Namespace SoftArtisans.OfficeWriter.ExcelWriter.Samples Public Class TemplateToApp ' Both ExcelApplication and ExcelTemplate Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Private Sub btnTemplateToApp_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemplateToApp.ServerClick ''' <summary> ' Save the report by streaming it ''' <summary> Add a column chart to the second worksheet using ExcelApplication. Dim ws2 As Worksheet = wb.Worksheets.CreateWorksheet("ChartSheet") ' Create a chart on the second worksheet ' Set series and category data ' Configure the chart's legend ' Set the chart's Title string and display properties. ''' <summary> Populate the template workbook with database ' Open the template workbook ' Get the data from the database and set it as ' Here the populated ExcelTemplate object ''' <summary> Query the database for the data that will Dim dt As DataTable = New DataTable Return dt #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. End Sub 'NOTE: The following placeholder declaration is required by the Web Form Designer. Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init #End Region End Class |