...
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"); } //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 AppToTemp_output.xlsx
...