Message-ID: <1384507772.8873.1711666043084.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8872_1889464223.1711666043068" ------=_Part_8872_1889464223.1711666043068 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Template to Application Sample

Template to Application Sample

Intro

Populate a t= emplate with ExcelTemplate and then programmatically manipulate or 'post pr= ocess' the workbook with ExcelApplication.

This demo begins with an ExcelTemplate workbook with data markers. = First, the ExcelTemplate object binds data to the data markers while prese= rving all existing Excel formats that were in the original template.  = Then, the populated workbook is opened as an ExcelApplication Workbook wher= e it can be programmatically manipulated.  ExcelApplication adds a cha= rt.

Code

=20
public class TempToApp
    {
        // Both ExcelApplication and ExcelTemplate
        //objects will be used for this demo.
        
        private ExcelTemplate xlt;
        private ExcelApplication xlw;
        private Workbook wb;
       
        /// <summary>
        /// Build the report with ExcelApplication
        /// </summary>
        public void GenerateReport()
        {
            
            PopulateTemplate();
            AddChart();

            // Save the report to specified f=
older 
            xlw.Save(wb, @"..\..\ExcelOu=
tputFiles\TempToApp_output.xlsx");
        }

        /// <summary> Add a column chart to the s=
econd worksheet using ExcelApplication.
        /// The chart will show data imported with the =
ExcelTemplate
        /// object.</summary>
        private void AddChart()
        {
            // Get the first two worksheets a=
nd give them names 
            Worksheet ws =3D wb.Worksheets[0]=
;
            ws.Name =3D "Data";

            Worksheet ws2 =3D wb.Worksheets.C=
reateWorksheet("ChartSheet");

            // Create a chart on the second w=
orksheet 
            Anchor anch =3D ws2.CreateAnchor(=
0, 0, 50, 50);
            Chart chrt =3D ws2.Charts.CreateC=
hart(ChartType.Column.Clustered, anch);

            // Set series and category data&n=
bsp;
            Series srs1 =3D chrt.SeriesCollec=
tion.CreateSeries("=3DData!B2:B7");
            chrt.SeriesCollection.CategoryDat=
a =3D "=3DData!A2:A7";
            srs1.NameFormula =3D "=3DDat=
a!A1";

            // Configure the chart's legend&n=
bsp;
            Legend lgnd =3D chrt.Legend;
            lgnd.Visible =3D true;
            lgnd.Location =3D Legend.LegendLo=
cation.Right;

            // Set the chart's Title string a=
nd display properties. 
            chrt.Title.Text =3D "Adventu=
reWorks Global Sales";
        }

        /// <summary> Populate the template workb=
ook with database
        /// data using the ExcelTemplate object.  =
The ExcelTemplate
        /// save() method returns a Workbook.
        /// </summary>
        private void PopulateTemplate()
        {
            // Create an instance of ExcelTem=
plate and open
            //the template workbook
            
            xlt =3D new ExcelTemplate();
            xlt.PreserveStrings =3D false;
            // Open the template workbook&nbs=
p;
            string templatePath =3D @"..=
\..\ExcelTemplateFiles\TemplateToAppTemplate.xlsx";
            xlt.Open(templatePath);

            // Programmatically contruct data=
table
            DataTable dt =3D new DataTable();
            for (int i =3D 0; i < 3; i++)
                dt.Columns.Add();
            dt.Rows.Add(new string[] {"A=
U","$12,197,515.53"});
            dt.Rows.Add(new string[] {"C=
A","$21,515,540.46"});
            dt.Rows.Add(new string[] {"D=
E","$5,939,763.50"});
            dt.Rows.Add(new string[] {"F=
R","$9,136,704.47"});
            dt.Rows.Add(new string[] {"G=
B","$9,506,447.59"});
            dt.Rows.Add(new string[] {"U=
S","$85,387,883.82"});

            xlt.BindData(dt, "Sales"=
;, xlt.CreateDataBindingProperties());
            xlt.Process();

            // Here the populated ExcelTempla=
te object
            //is being opened as an ExcelAppl=
ication Workbook.
            //The object can now be programat=
ically manipulated
            //with the ExcelApplication API
            
            xlw =3D new ExcelApplication();
            wb =3D xlw.Open(xlt);
        }

    }

=20

Downloads

------=_Part_8872_1889464223.1711666043068--