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
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 =20public 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