This is Part 3 of the three-part tutorial series Financial Report scenario. It is recommended that you complete Part 1 - Using Modifiers and Ordinal Syntax and Part 2 - Using Styles and Formatting before starting this section.

This tutorial also assumes a basic understanding of how to open and manipulate Excel files with ExcelApplication.

In the downloadable ExcelWriter_Basic_Tutorials.zip, there are completed template files located in CompleteFinancialReport/templates.

protected void MergeTemplates()
{

}

MERGEDWB.Worksheets.CopySheet(wbOther[0], 1, wbOther[0].Name);

protected void BindDataToMergedTemplate()
{

}

XLT = new ExcelTemplate();
XLT.Open(XLA, MERGEDWB);

If there are duplicate data markers in a workbook, ExcelWriter will import the matching data to all the data markers, across all the worksheets in the workbook.

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

//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" };

using SoftArtisans.OfficeWriter.ExcelWriter;
using GenericParsing; //See Part 1 for more information about the CSV parser
...
//Declare globals
 ExcelApplication XLA;
 ExcelTemplate XLT;
 Workbook MERGEDWB;

//Merges the reports and binds data to the templates
protected void RunReport()
{
          //Join the two reports using CopySheet
          MergeTemplates();

          //bind the data for the newly modified file
          BindDataToMergedTemplate();

}


protected void JoinReports()
{
	//GetInstantiate theExcelApplication
populated	XLA files= from BindTemplateData
        XLTTo = BindTemplateData(new ExcelApplication();

	//Open the first template
	MERGEDWB = XLA.Open(Page.MapPath("//templates//Part1_Financial_Template.xlsx"));
 
      XLTFrom = BindTemplateData(	//Open the second template
	Workbook wbOther = XLA.Open(Page.MapPath("//templates//Part2_Financial_Template.xlsx"));

	//JoinCall theCopySheet
two reports using CopySheet
	JoinReports(      MERGEDWB.Worksheets.CopySheet(wbOther[0], 1, wbOther[0].Name);

}

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


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

	//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,   XLT.Save(Page.Response, "Part3_Output.xlsx", false);
}