.
...
| Info | ||
|---|---|---|
| ||
In the downloadable C# project ExcelWriter_Basic_Tutorials.zip, there is a completed template file located in ExtendedSalesSummary/templates/template.xlsx. |
...
2. At the top of the class definition, define a global variable variations for the ExcelTemplate object. ExcelApplication, ExcelTemplate, and Workbook objects:
| Code Block |
|---|
private ExcelApplication xla; private ExcelTemplate xlt; private Workbook wb; |
| Info | ||
|---|---|---|
| ||
In the sample code, you will also see |
...
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()
{
}
|
2. Instantiate In the helper method, instantiate the ExcelApplication object.
| Code Block |
|---|
ExcelApplication xla = new ExcelApplication(); |
3. Open the Workbook template file with ExcelApplication.Open(ExcelTemplate) method.
| Code Block |
|---|
Workbook wb = xla.Open(Page.MapPath(@"templates\template.xlsx")); |
...
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()
{
}
|
2. Instantiate In the helper method, instantiate a new ExcelTemplate object.
...
3. Pass the existing ExcelApplication and Workbook to the ExcelTemplate object using ExcelTemplate.Open(ExcelApplication, Workbook).
| Code Block |
|---|
xlt.Open(xla, wb); |
4. Although not necessary, it may be useful to set ExcelTemplate.RemoveExtraDataMarkers to true. This will tell ExcelWriter to ignore any data markers that are not bound to data sets. This is helpful if you are adding the data binding calls iterativelyto the code one at a time.
| Code Block |
|---|
xlt.RemoveExtraDataMarkers = true; |
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.