Intro

Create a template file for ExcelTemplate using ExcelApplication to write in data markers.

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

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

Code

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

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

            // Loop once through each data column 
            for (int i = 0; i < cols.Length; i++)
            { 
                // For the current data marker.
                 //Data markers are in this format:
                 //%%=[dsrcName].[colName]
                 
                string curMarker = String.Format( "%%={0}.{1}", dsrcName, cols[i].ToString());
                
                // 
                 //Write (fieldname) on the first row to mark as headers
                 
                ws[firstRow, firstCol + i].Value = curMarker + "(fieldname)";
                ws[firstRow, firstCol + i].Style.Font.Bold = true;
                ws[firstRow+1, firstCol + i].Value = curMarker;
            }

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

            PopulateTemplate();
        }

            private void PopulateTemplate()
            {
                // Open the ExcelApplication Workbook as
                 //an ExcelTemplate object.
                 
                xlt = new ExcelTemplate();
                xlt.Open(xlw, wb);

                // Create a DataBindingProperties object 
                DataBindingProperties dataProps = xlt.CreateDataBindingProperties();
                
                // Parse the csv file and save the data in a DataTable
                 //Bind data
                 //Process Data
                
                DataTable dt = GetCSVData(@"..\..\ExcelData\AppToTemplateData.csv");
                xlt.BindData(dt, dsrcName, dataProps);
                xlt.Process();

                //Save The Output
                xlt.Save(@"..\..\ExcelOutputFiles\AppToTemp_output.xlsx");
            }
        
        #region Utility Methods
        //Uses CSV reader
        System.Data.DataTable GetCSVData(string csvFileName)
        {
            DataTable dt;
            using (GenericParserAdapter parser = new GenericParserAdapter(csvFileName))
            {
                parser.ColumnDelimiter = ',';
                parser.FirstRowHasHeader = true;

                dt = parser.GetDataTable();
            }
            return dt;
        }

        #endregion
    }

 

Downloads