public class ExpenseReport
{
/// <summary>
/// Build the report with ExcelTemplate
/// </summary>
public void GenerateReport()
{
// Here we create some data to populate into
//the template for the sample In your application
//this data could possibly come straight from a database
// These strings will bind to variable data markers
string empPurpose = "Quarterly Expense Report";
string empName = "Sammy McDougal";
string empDept = "Sales (Eastern region)";
string empSSN = "111-222-1212";
string empPosition = "VP";
string empID = "242";
string empManager = "C.M. Burns";
string dateFrom = "1/1/2002";
string dateTo = "5/31/2002";
// These arrays hold data that bind to the array
//data markers and will be repeated
//in the template for each row imported
string[] colNames = { "Date", "Description", "Hotel", "Fuel", "Meals", "Misc" };
object[,] values = {{"1/2/2002", "2/5/2002", "3/15/2002", "4/10/2002", "5/17/2002"},
{"Dev Conference", "Lunch w/ Client", "Delivery", "Dinner w/ Client", "Sales Seminar"},
{200.04, 223.43, 184.23, 331.24, 112.11},
{10.00, 0.99, 11.23, 32.23, 10.00},
{30.00, 23.04, 44.44, 19.99, 5.00},
{5.00, 11.00, 13.03, 3.24, 33.12}};
// Create an instance of ExcelTemplate
ExcelTemplate xlt = new ExcelTemplate();
// Open the template workbook
xlt.Open(@"..\..\ExcelTemplateFiles\ExpenseReportTemplate.xlsx");
// Pass the strings to the BindCellData method
DataBindingProperties cellBindingProperties = xlt.CreateDataBindingProperties();
xlt.BindCellData(empPurpose, "EmpPurpose", cellBindingProperties);
xlt.BindCellData(empName, "EmpName", cellBindingProperties);
xlt.BindCellData(empDept, "EmpDept", cellBindingProperties);
xlt.BindCellData(empSSN, "EmpSSN", cellBindingProperties);
xlt.BindCellData(empPosition, "EmpPosition", cellBindingProperties);
xlt.BindCellData(empID, "EmpID", cellBindingProperties);
xlt.BindCellData(empManager, "EmpManager", cellBindingProperties);
xlt.BindCellData(dateFrom, "DateFrom", cellBindingProperties);
xlt.BindCellData(dateTo, "DateTo", cellBindingProperties);
// Limit the number of rows to the maximum number of rows that Excel
//can support. ExcelTemplate expects the array's first index to be
//the row number and the second index to be the column number. Our
//array is the opposite, so we have to transpose the data.
DataBindingProperties bindingProperties = xlt.CreateDataBindingProperties();
bindingProperties.MaxRows = ExcelTemplate.ALL_ROWS;
bindingProperties.Transpose = true;
xlt.BindData(values, colNames, "Exp", bindingProperties);
// Call the Process() method to populate the template
//with the data source values
xlt.Process();
// Save the report
xlt.Save(@"..\..\ExcelOutputFiles\ExpenseReport_output.xlsx");
}
}
|