...
Info | ||
---|---|---|
| ||
In the downloadable 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 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.