Page History

using SoftArtisans.OfficeWriter.ExcelWriter;
...
//Declare Template objects globaly
ExcelTemplate XLTTo;
ExcelTemplate XLTFrom;

protected void RunReport()
{
	//Get the populated files from BindTemplateData
        XLTTo = BindTemplateData("//templates//Part1_Financial_Template.xlsx");
        XLTFrom = BindTemplateData("//templates//Part2_Financial_Template.xlsx");

	//Join the two reports using CopySheet
	JoinReports();
}

protected ExcelTemplate BindTemplateData(string filename)
{
	ExcelTemplate XLT = new ExcelTemplate();


	//Open the template file
	XLT.Open(Page.MapPath(filename));

	//Create data binding properties for ExcelTemplate
	DataBindingProperties bindingProps = XLT.CreateDataBindingProperties(); 

	//Get the datatables to be bound into the template.
	//This example uses CSV data, but the data can come from
	//anywhere
	DataTable dtAssets = GetCSVData("//data//Assets.csv");
	DataTable dtLosses = GetCSVData("//data//Losses.csv");
	DataTable dtOther = GetCSVData("//data//Other.csv");

	//Bind the datatables
	XLT.BindData(dtAssets, "Assets", bindingProps);
	XLT.BindData(dtLosses, "Losses", bindingProps);
	XLT.BindData(dtOther, "Other", bindingProps);

	/*This section only applies to the first template. It will be ignored by the 
	second template, since there are no markers to bind to.
	*/

	//Create the array of header values. This example only binds a single item
	string[] headerValues = { "2011" };

	//Create the array of header names.
	string[] headerNames = { "FiscalYear" };

	//Bind the header row data
	XLT.BindRowData(headerValues, headerNames, "Header", bindingProps);

	/*/end*/

	//Process to import the data to the file
	XLT.Process();

	//Return the populated file
	return XLT;
}

protected void JoinReports()
{
	//Instantiate ExcelApplication for the workbook being copied to
	ExcelApplication XLATo = new ExcelApplication();

	//Open the populated template
	Workbook wbTo = XLATo.Open(XLTTo);

	//Open the first worksheet
	Worksheet wsTo = wbTo.Worksheets[0];

	//Call AutoFit to set the height and width
	wsTo.PopulatedCells.AutoFitWidth();
	wsTo.PopulatedCells.AutoFitHeight();

	//Instantiate ExcelApplication for the workbook being copied from
	ExcelApplication XLAFrom = new ExcelApplication();

	//Open the populated template
	Workbook wbFrom = XLAFrom.Open(XLTFrom);

	//Open the first worksheet, with the table
	Worksheet wsFrom = wbFrom[0];
	//Open the second worksheet, with the data
	Worksheet wsFromData = wbFrom[1];

	//Call CopySheet, making sure to copy the data sheet first
	wbTo.Worksheets.CopySheet(wsFromData, 1, "Data");
	wbTo.Worksheets.CopySheet(wsFrom, 1, "Percent by Quarter");

	//Save the file
	XLATo.Save(wbTo, Page.Response, "Part3_Output.xlsx", false);
}