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.

In the sample code, the reference to SoftArtisans.OfficeWriter.ExcelWriter.dll has already been added to the CompleteFinancialReport project.

using SoftArtisans.OfficeWriter.ExcelWriter;

ExcelApplication XLA;
ExcelTemplate XLT;
Workbook MERGEDWB;

protected void MergeTemplates()
{

}

XLA = new ExcelApplication();

MERGEDWB = XLA.Open(Page.MapPath("//templates//Part1_Financial_Template.xlsx"));

Workbook wbOther = XLA.Open(Page.MapPath("//templates//Part2_Financial_Template.xlsx"));

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

//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 header row data
XLT.BindRowData(headerValues, headerNames, "Header", bindingProps);

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

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

XLT.Save(Page.Response, "Part3_Output.xlsx", false);

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()
{
	//Instantiate ExcelApplication
	XLA = new ExcelApplication();

	//Open the first template
	MERGEDWB = XLA.Open(Page.MapPath("//templates//Part1_Financial_Template.xlsx"));

	//Open the second template
	Workbook wbOther = XLA.Open(Page.MapPath("//templates//Part2_Financial_Template.xlsx"));

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

}

protected void BindTemplateData()
{
	XLT = new ExcelTemplate();

	//Open the template file
	XLT.Open(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();

        XLT.Save(Page.Response, "Part3_Output.xlsx", false);
}