...
Code Block |
---|
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 } |
...