...
Code Block |
---|
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, // 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(@"..\..\Data\AppToTemplateData.csv"); xlt.BindData(dt, dsrcName, dataProps); xlt.Process(); //Save The Output xlt.Save(@"..\..\Output\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; } |
Downloads
- Output Data: AppToTemplateData.csv
- Output: AppToTemp_output.xlsx
- Data AppToTemplateData.csv