Message-ID: <1553725273.8147.1711634294381.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8146_1225181587.1711634294381" ------=_Part_8146_1225181587.1711634294381 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Part 3 - Combine Reports with CopySheet

Part 3 - Combine Reports with CopySheet

Table of Contents

 

 

Introduction

= =20
=20 Icon=20
=20

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

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

=20
=20
=20

Getting Started=

This section covers using E= xcelApplication to join two Worksheets into one Workbook. These tw= o worksheets were created in Part 1 and Part 2 of this tutorial.&n= bsp;

=20
=20

Following the Sample Code

=20 Icon=20
=20

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

=20
=20
=20

Adding an ExcelWriter Reference in Visual Studio

=20
=20

Following the Sample Code

=20 Icon=20
=20

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

=20
=20
=20

Create a .NET project and add a reference to the ExcelWriter library.

  1. Open Visual Studio and create a .NET project.
    • The sample code uses a web application.
  2. Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
    • SoftArtisans.OfficeWriter.ExcelWriter.dll is located under Prog= ram Files > SoftArtisans > OfficeWriter > dotnet > bin=

Writing the Cod= e

Merging template together with CopySheet

1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the co= de behind

=20
using SoftArtisans.OfficeWriter.ExcelWriter;
=20

2. Globally declare the ExcelTemp= late, ExcelApplication, a= nd Workbook objects.

=20
ExcelApplication XLA;
ExcelTemplate XLT;
Workbook MERGEDWB;
=20

3. Define a method that will open the two template files and merge them = together with CopySheet= . In this case, the method is called MergeTemplates().

=20
protected void MergeTemplates()
{

}
=20

4. In MergeTemplates(), instantiate the ExcelApplication object.

=20
XLA =3D new ExcelApplication();
=20

5. Open the workbook that the worksheets will be copied into. For this e= xample, the second template will be copied into the first template workbook= .

=20
MERGEDWB =3D XLA.Open(Page.MapPath("//templates//Part1_Fina=
ncial_Template.xlsx"));
=20

6. Open the second workbook that will be merged into the first.

=20
Workbook wbOther =3D XLA.Open(Page.MapPath("//templates//Pa=
rt2_Financial_Template.xlsx"));
=20

7. Copy the worksheet from the second template into the first workbook. = Worksheet.Name can be used to p= ass along the original name of the worksheet ("Percent by Quarter"= ;). The worksheet should be copied to the end of the current worksheet coll= ection (index 1).

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

Binding Data to the Merged Template

1. Create a method to handle binding and processing the data import for = the combined template. For this example, the method is called BindDat= aToMergedTemplate().

=20
protected void BindDataToMergedTemplate()
{

}
=20

2. Instantiate ExcelTemplate = and open the template file with ExcelTemplate.Open(ExcelApplication, Workbook) to open directl= y from the pre-existing ExcelApplication instance.

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

3. Use the code from Part 1 and Part 2 to bind data to the template. Note that both= templates used the same data set, so the calls to bind the data only need = to be made once.

=20
=20 Icon=20
=20

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

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

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

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

//Get the datatables to be bound into the template.
//This example uses CSV data, but the data can come from
//anywhere
DataTable dtAssets =3D GetCSVData("//data//Assets.csv");
DataTable dtLosses =3D GetCSVData("//data//Losses.csv");
DataTable dtOther =3D 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);
=20

Final Code

=20
using SoftArtisans.OfficeWriter.ExcelWriter;
using GenericParsing; //See Part 1 for more information about the CSV parse=
r
...
//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()
{
=09//Instantiate ExcelApplication
=09XLA =3D new ExcelApplication();

=09//Open the first template
=09MERGEDWB =3D XLA.Open(Page.MapPath("//templates//Part1_Financial_Te=
mplate.xlsx"));

=09//Open the second template
=09Workbook wbOther =3D XLA.Open(Page.MapPath("//templates//Part2_Fina=
ncial_Template.xlsx"));

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

}

protected void BindTemplateData()
{
=09XLT =3D new ExcelTemplate();

=09//Open the template file
=09XLT.Open(XLA, MERGEDWB);

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

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

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

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

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

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

=09//Bind the header row data
=09XLT.BindRowData(headerValues, headerNames, "Header", bindingPr=
ops);

=09//end

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

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

=20

Downloads

You can download the code for the Financial Report here.

------=_Part_8146_1225181587.1711634294381--