Message-ID: <373167234.10235.1711718722722.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10234_477766081.1711718722722" ------=_Part_10234_477766081.1711718722722 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
ExcelWriter allows you to generate a spreadsheet from script alo=
ne - using the ExcelApplication obje=
ct - or from a template spreadsheet and a script, using ExcelTemplate. ExcelTemplate provides an intuitive high=
-performance way to import database values to a spreadsheet, but cannot oth=
erwise modify a spreadsheet at runtime. ExcelApplication's rich object mode=
l allows you to modify every aspect of the spreadsheet at runtime. You can =
take advantage of the features of both ExcelApplication and ExcelTemplate b=
y using them together. For example, you can use ExcelTemplate to open and p=
opulate an ExcelWriter template, then pass the populated workbook to ExcelA=
pplication and add a chart.
To pass a workbook from ExcelTemplate to =
ExcelApplication, do not call ExcelTemplate.Save. Instead, pass the ExcelTe=
mplate object to ExcelApplication's Open method:
ExcelTemplate xlt =3D new ExcelTemplate(); xlt.Open(templatePath); xlt.BindData(data, "Sales", xlt.CreateDataBindingProperties()); xlt.Process(); //--- Create an instance of ExcelApplication and //--- open the spreadsheet you created with ExcelTemplate. //--- The spreadsheet will be returned as a Workbook //--- object. ExcelApplication xla =3D new ExcelApplication(); Workbook wb =3D xla.Open(xlt);=20
//-------------------------------------------------------------- //--- 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 btnTemplateToA= pp; private string connString =3D System.Configuration.ConfigurationManager.AppSettings["con= nString"]; private void btnTemplateToApp_ServerClick(object sender, System.Eve= ntArgs 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", fals= e); } /// <summary> Add a column chart to the second worksheet usin= g 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 =3D wb.Worksheets[0]; ws.Name =3D "Data"; Worksheet ws2 =3D wb.Worksheets.CreateWorksheet("ChartShee= t"); /* Create a chart on the second worksheet */ Anchor anch =3D ws2.CreateAnchor(0, 0, 50, 50); Chart chrt =3D ws2.Charts.CreateChart(ChartType.Column.Clustere= d, anch); /* Set series and category data */ Series srs1 =3D chrt.SeriesCollection.CreateSeries("=3DDat= a!B2:B7"); chrt.SeriesCollection.CategoryData =3D "=3DData!A2:A7"= ;; srs1.NameFormula =3D "=3DData!A1"; /* Configure the chart's legend */ Legend lgnd =3D chrt.Legend; lgnd.Visible =3D true; lgnd.Location =3D Legend.LegendLocation.Right; /* Set the chart's Title string and display properties. */ chrt.Title.Text =3D "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 =3D new ExcelTemplate(); /* Open the template workbook */ string templatePath =3D Page.MapPath("templates/TemplateTo= AppTemplate.xls"); xlt.Open(templatePath); /* Get the data from the database and set it as * a data source. */ DataTable dt =3D GetData(); xlt.BindData(dt, "Sales", xlt.CreateDataBindingProper= ties()); 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 =3D new ExcelApplication(); wb =3D 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 =3D "SELECT Person.StateProvince.CountryRegionC= ode As Country, " + "SUM(Sales.SalesOrderHeader.TotalDue) As TotalSales &q= uot; + "FROM Sales.SalesOrderHeader " + "JOIN Sales.CustomerAddress " + "ON Sales.SalesOrderHeader.CustomerID=3DSales.Customer= Address.CustomerID " + "JOIN Person.Address " + "ON Sales.CustomerAddress.AddressID=3DPerson.Address.A= ddressID " + "JOIN Person.StateProvince " + "ON Person.StateProvince.StateProvinceID=3DPerson.Addr= ess.StateProvinceID " + "GROUP BY Person.StateProvince.CountryRegionCode"= ; DataTable dt =3D new DataTable(); using(SqlConnection conn =3D 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 Desig= ner. // 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 +=3D new System.EventHandler(= this.btnTemplateToApp_ServerClick); } #endregion } }=20
<p>Option Strict On<br /> Option Explicit On</p> <p>'--------------------------------------------------------= ------<br /> '--- SoftArtisans OfficeWriter ExcelApplication Template-To-App Sa= mple<br /> '---<br /> '--- Demonstrates how the ExcelTemplate and ExcelApplication objec= ts<br /> '--- can be used together in the same request.<br /> '--- Populate an ExcelTemplate workbook with data and then open th= e<br /> '--- populated workbook with the ExcelApplication API for further = edits.<br /> '---<br /> '--- (c) 2009 SoftArtisans, Inc.<br /> '--- Support: <a class=3D"external-link" href=3D"= ;http://support.softartisans.com" rel=3D"nofollow">http:/= /support.softartisans.com</a><br /> '--- Sales: sales@softartisans.com<br /> '--------------------------------------------------------------<= ;/p> <p>Imports System.Data<br /> Imports System.Data.SqlClient<br /> Imports System.Configuration<br /> Imports SoftArtisans.OfficeWriter.ExcelWriter</p> <p>Namespace SoftArtisans.OfficeWriter.ExcelWriter.Samples</p> <p>Public Class TemplateToApp<br /> Inherits System.Web.UI.Page</p> <p> ' Both ExcelApplication and ExcelTemplate<br /= > '* objects will be used for this demo.<br /> '<br /> Private xlt As ExcelTemplate<br /> Private xlw As ExcelApplication<br /> Private wb As Workbook<br /> Private connString As String =3D System.Configuration.Configuration= Manager.AppSettings("connString")</p> <p> Private Sub Page_Load(ByVal sender As System.Object, ByVal e A= s System.EventArgs) Handles MyBase.Load<br /> 'Put user code to initialize the page here<br /> End Sub</p> <p> Private Sub btnTemplateToApp_ServerClick(ByVal sender As Syste= m.Object, ByVal e As System.EventArgs) Handles btnTemplateToApp.ServerClick= <br /> GenerateReport()<br /> End Sub</p> <p> ''' <summary><br /= > ''' Build the report with ExcelApplication&l= t;br /> ''' </summary><br /> Private Sub GenerateReport()<br /> PopulateTemplate()<br /> AddChart()</p> <p> ' Save the report by streaming it<br /> '* to the client's browser<br /> xlw.Save(wb, Page.Response, "TemplateToApp.xls", = False)<br /> End Sub</p> <p> ''' <summary> Add a c= olumn chart to the second worksheet using ExcelApplication.<br /> ''' The chart will show data imported with t= he ExcelTemplate<br /> ''' object.</summary><br /&= gt; Private Sub AddChart()<br /> ' Get the first two worksheets and give them names<br /= > Dim ws As Worksheet =3D wb.Worksheets(0)<br /> ws.Name =3D "Data"</p> <p> Dim ws2 As Worksheet =3D wb.Worksheets.CreateWorksheet(&am= p;quot;ChartSheet")</p> <p> ' Create a chart on the second worksheet<br /&= gt; Dim anch As Anchor =3D ws2.CreateAnchor(0, 0, 50, 50)<br /> Dim chrt As Chart =3D ws2.Charts.CreateChart(ChartType.Column.Clust= ered, anch)</p> <p> ' Set series and category data<br /> Dim srs1 As Series =3D chrt.SeriesCollection.CreateSeries("= ;=3DData!B2:B7")<br /> chrt.SeriesCollection.CategoryData =3D "=3DData!A2:A7&= quot;<br /> srs1.NameFormula =3D "=3DData!A1"</p> <p> ' Configure the chart's legend<br /&g= t; Dim lgnd As Legend =3D chrt.Legend<br /> lgnd.Visible =3D True<br /> lgnd.Location =3D Legend.LegendLocation.Right</p> <p> ' Set the chart's Title string and displ= ay properties.<br /> chrt.Title.Text =3D "AdventureWorks Global Sales"= <br /> End Sub</p> <p> ''' <summary> Populat= e the template workbook with database<br /> ''' data using the ExcelTemplate object. Th= e ExcelTemplate<br /> ''' save() method returns a Workbook.<br = /> ''' /summary><br /> Private Sub PopulateTemplate()<br /> ' Create an instance of ExcelTemplate and open<br /> '* the template workbook<br /> '<br /> xlt =3D New ExcelTemplate</p> <p> ' Open the template workbook<br /> Dim templatePath As String =3D Page.MapPath("templates/Tem= plateToAppTemplate.xls")<br /> xlt.Open(templatePath)</p> <p> ' Get the data from the database and set it as<= ;br /> '* a data source.<br /> '<br /> Dim dt As DataTable =3D GetData()<br /> xlt.BindData(dt,"Sales",xlt.CreateDataBindingProp= erties())<br /> xlt.Process()</p> <p> ' Here the populated ExcelTemplate object<br /= > '* is being opened as an ExcelApplication Workbook.<br = /> '* The object can now be programatically manipulated<br= /> '* with the ExcelApplication API<br /> '<br /> xlw =3D New ExcelApplication<br /> wb =3D xlw.Open(xlt)<br /> End Sub</p> <p> ''' <summary> Query t= he database for the data that will<br /> ''' be imported by the ExcelTemplate object.= <br /> ''' </summary><br /> ''' <returns>DataTable of data= for the report</returns><br /> Private Function GetData() As DataTable<br /> Dim sql As String =3D "SELECT Person.StateProvince.Cou= ntryRegionCode As Country, " + _<br /> "SUM(Sales.SalesOrderHeader.TotalDue) As TotalSale= s " + _<br /> "FROM Sales.SalesOrderHeader " + _<br = /> "JOIN Sales.CustomerAddress " + _<br /= > "ON Sales.SalesOrderHeader.CustomerID=3DSales.Cust= omerAddress.CustomerID " + _<br /> "JOIN Person.Address " + _<br /> "ON Sales.CustomerAddress.AddressID=3DPerson.Addre= ss.AddressID " + _<br /> "JOIN Person.StateProvince " + _<br /&= gt; "ON Person.StateProvince.StateProvinceID=3DPerson.= Address.StateProvinceID " + _<br /> "GROUP BY Person.StateProvince.CountryRegionCode&a= mp;quot;</p> <p> Dim dt As DataTable =3D New DataTable<br /> Dim conn As SqlConnection =3D New SqlConnection(connString)<br /= > Try<br /> Dim adpt As New SqlDataAdapter(sql, conn)<br /> adpt.Fill(dt)<br /> Finally<br /> If Not conn Is Nothing Then<br /> conn.Dispose()<br /> End If<br /> End Try</p> <p> Return dt<br /> End Function</p> <p>#Region " Web Form Designer Generated Code "<= /p> <p> 'This call is required by the Web Form Designer.<b= r /> <System.Diagnostics.DebuggerStepThrough()> Private Sub In= itializeComponent()</p> <p> End Sub<br /> Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm<br= /> Protected WithEvents btnTemplateToApp As System.Web.UI.HtmlControls.Htm= lInputButton</p> <p> 'NOTE: The following placeholder declaration is requi= red by the Web Form Designer.<br /> 'Do not delete or move it.<br /> Private designerPlaceholderDeclaration As System.Object</p> <p> Private Sub Page_Init(ByVal sender As System.Object, ByVal e A= s System.EventArgs) Handles MyBase.Init<br /> 'CODEGEN: This method call is required by the Web Form Des= igner<br /> 'Do not modify it using the code editor.<br /> InitializeComponent()<br /> End Sub</p> <p>#End Region</p> <p>End Class<br /> End Namespace</p>=20