Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Info
titleFollowing the Sample Code

In the downloadable ExcelWriter_Basic_Tutorials.zip, there is a completed template file located in ExtendedSalesSummary/templates/template.xlsx.
A copy of the completed template file is also available here.

...

2. At the top of the class definition, define global variations for the ExcelApplication, ExcelTemplate, and Workbook objects:

Code Block

private ExcelApplication xla;
private ExcelTemplate xlt;
private Workbook wb;

...

1. Define a method to contain the ExcelApplication code for customizing the sheet. In the sample, this method is called GenerateTemplate()

Code Block

//Use ExcelApplication to make a copy of a regional worksheet for each
//country that is selected by the user. 
protected void GenerateTemplate()
{
            
}

...

In this example, the sheet that needs to be copied is the first worksheet in the template file. It can be accessed through Workbook.Worksheets by index (0) or by name ("SimpleTemplate").

Code Block

for (int i = 0; i < selectedCountries.Count; i++)
{
    wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
}

...

8. The final code for the GenerateTemplate() method should look like this:

Code Block

protected void GenerateTemplate()
{
	xla = new ExcelApplication();
	wb = xla.Open(Page.MapPath(@"templates\template.xlsx"));

	for (int i = 0; i < selectedCountries.Count; i++)
	{
		wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
	}
						
	wb.Worksheets["SimpleTemplate"].Visibility = Worksheet.SheetVisibility.Hidden;
						
	wb.Worksheets[1].Select();
}

...

1. Define a method to contain the ExcelTemplate code for binding the data to the template. In the sample, this method is called PopulateTemplate()

Code Block

//Use ExcelTemplate to bind data for each selected country
//to worksheets in the template, then populate the report
//with that data
protected void PopulateTemplate()
{
            
}

...

5. For each selected country, data needs to be bound to the corresponding worksheet. Define a DataBindingProperties object for future use and set up a for loop to go through all the selected countries.

Code Block

DataBindingProperties dataBindProps;

for (int i = 0; i < selectedCountries.Count; i++)
{

}

...

These calls are to a helper method GetCSVData that parses the CSV files and returns a DataTable with the values.

Code Block

DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

...

Recall that the data source names (Top, Details) need to match the data marker names exactly.

Code Block

xlt.BindData(dts, "Top", dbp);
xlt.BindData(dts2, "Details", dbp);

13. The final for loop should look like this:

Code Block

for (int i = 0; i < selectedCountries.Count; i++)
{
    string country = selectedCountries[i];

    dataBindProps = xlt.CreateDataBindingProperties();

    dataBindProps.WorksheetName = country;

    string[] headerValues = { "FY 2008", "Foreign Trade Division", country };
    string[] headerNames = { "FiscalYear", "TradeDivision", "Country" };

    xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps);

    DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
    DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

     xlt.BindData(dts, "Top", dataBindProps);
     xlt.BindData(dts2, "Details", dataBindProps);
}

...

16. The final code for PopulateTemplate() should look like this:

Code Block

protected void PopulateTemplate()
{
            xlt = new ExcelTemplate(); //Create a new ExcelTemplate object

            xlt.Open(xla, wb);

            xlt.RemoveExtraDataMarkers = true;

            DataBindingProperties dataBindProps;

            for (int i = 0; i < selectedCountries.Count; i++)
            {
                string country = selectedCountries[i];

                dataBindProps = xlt.CreateDataBindingProperties();

                dataBindProps.WorksheetName = country;

                string[] headerValues = { "FY 2008", "Foreign Trade Division", country };
                string[] headerNames = { "FiscalYear", "TradeDivision", "Country" };

                xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps);

                DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv"));
                DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv"));

                xlt.BindData(dts, "Top", dataBindProps);
                xlt.BindData(dts2, "Details", dataBindProps);
            }
            xlt.Process();
            xlt.Save(Page.Response, "Output.xlsx", false);
}

...

You can download the code for the Extended Sales Summary here.

Next Steps

Continue on to Part 2 - Adding a Coversheet