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

Application to Template Sample

Intro

Create a tem= plate file for ExcelTemplate using ExcelApplication to write in data marker= s.

This demo uses ExcelApplication API to programmatically create an ExcelT= emplate workbook with data markers.  The number, type, and position of= the data markers are defined by the values chosen below.  The workboo= k with data markers is then opened with ExcelTemplate and populated with da= ta.

=20
=20 Icon=20
=20

This sample stores some of the data in a CSV file, which is available fo= r download under Downloads. The CSV parser used in the exa= mple code was developed by Andrew Rissing and can be downloaded from Code Project.

=20
=20
=20

Code

=20
       public class AppToTemplate
    {
        private ExcelApplication xlw;
        private ExcelTemplate xlt;
        private Workbook wb;
        private string dsrcName =3D "MyDataSource&=
quot;;
        private int firstRow =3D 0;
        private int firstCol =3D 0;
        private string[] cols =3D { "Title", =
"FirstName", "LastName", "EmailAddress", &quo=
t;Phone"};

        public void GenerateReport()
        {
            // Create an instance of ExcelApp=
lication, f
            //create a Workbook and get the f=
irst Worksheet.
            
            xlw =3D new ExcelApplication();
            wb =3D xlw.Create(ExcelApplicatio=
n.FileFormat.Xlsx);
            Worksheet ws =3D wb.Worksheets[0]=
;

            // Loop once through each data co=
lumn 
            for (int i =3D 0; i < cols.Len=
gth; i++)
            { 
                // For the current =
data marker.
                 //Data marker=
s are in this format:
                 //%%=3D[dsrcN=
ame].[colName]
                 
                string curMarker =
=3D String.Format( "%%=3D{0}.{1}", dsrcName, cols[i].ToString());
                
                // 
                 //Write (fiel=
dname) on the first row to mark as headers
                 
                ws[firstRow, firstC=
ol + i].Value =3D curMarker + "(fieldname)";
                ws[firstRow, firstC=
ol + i].Style.Font.Bold =3D true;
                ws[firstRow+1, firs=
tCol + i].Value =3D curMarker;
            }

             //If only the template is n=
eeded uncomment below, and do not call PopulateTemplate()
             
            //xlw.Save(wb, Page.Response, &qu=
ot;AppToTemplate-Template.xlsx", false);

            PopulateTemplate();
        }

            private void PopulateTemplate()
            {
                // Open the ExcelAp=
plication Workbook as
                 //an ExcelTem=
plate object.
                 
                xlt =3D new ExcelTe=
mplate();
                xlt.Open(xlw, wb);

                // Create a DataBin=
dingProperties object 
                DataBindingProperti=
es dataProps =3D xlt.CreateDataBindingProperties();
                
                // Parse the csv fi=
le and save the data in a DataTable
                 //Bind data
                 //Process Dat=
a
                
                DataTable dt =3D Ge=
tCSVData(@"..\..\ExcelData\AppToTemplateData.csv");
                xlt.BindData(dt, ds=
rcName, dataProps);
                xlt.Process();

                //Save The Output
                xlt.Save(@"..\=
..\ExcelOutputFiles\AppToTemp_output.xlsx");
            }
        
        #region Utility Methods
        //Uses CSV reader
        System.Data.DataTable GetCSVData(string csvFile=
Name)
        {
            DataTable dt;
            using (GenericParserAdapter parse=
r =3D new GenericParserAdapter(csvFileName))
            {
                parser.ColumnDelimi=
ter =3D ',';
                parser.FirstRowHasH=
eader =3D true;

                dt =3D parser.GetDa=
taTable();
            }
            return dt;
        }

        #endregion
    }

 
=20

Downloads

------=_Part_7846_1757085637.1711624059845--