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
Table of Contents |
---|
=20
|
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 =20Create a .NET project and add a reference to the ExcelWriter library.
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the co= de behind
using SoftArtisans.OfficeWriter.ExcelWriter;=20
2. Globally declare the ExcelTemp= late, ExcelApplication, a= nd Workbook objects.
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()
.
protected void MergeTemplates() { }=20
4. In MergeTemplates()
, instantiate the ExcelApplication object.
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= .
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.
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).
MERGEDWB.Worksheets.CopySheet(wbOther[0], 1, wbOther[0].Name);=20
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()
.
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.
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 =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
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
You can download the code for the Financial Report here.