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