...
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 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()
{
}
|
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 = 10; 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 = 10; 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 = 10; 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.