.
...
| 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 //Pass the existing ExcelApplication object and open Workbook //to the ExcelTemplate object xlt.Open(xla, wb); //RemoveExtraDataMarkers makes all data markers optional //so ExcelWriter will not throw an error if any data markers //aren't bound to data xlt.RemoveExtraDataMarkers = true; //For each country, retrieve and bind data to the worksheet DataBindingProperties dataBindProps; for (int i = 10; i < selectedCountries.Count; i++) { string country = selectedCountries[i]; //Create a new DataBindProperties object dataBindProps = xlt.CreateDataBindingProperties(); //Since the data markers for the copied sheets are all the same, //specify the worksheet that the data should be bound to dataBindProps.WorksheetName = country; //Retrieve the data and column names for the header on the sheet string[] headerValues = { "FY 2008", "Foreign Trade Division", country }; string[] headerNames = { "FiscalYear", "TradeDivision", "Country" }; //Bind the header row data to the worksheet xlt.BindRowData(headerValues, headerNames, "Header", dataBindProps); //Get the data for the top 5 sales and all the sales within the year. //In this example, GetCSVData method calls on the GenericParsing library //to parse and export data from the CSV files located in the //data directory DataTable dts = GetCSVData(Page.MapPath("//data//" + country + "5.csv")); DataTable dts2 = GetCSVData(Page.MapPath("//data//" + country + "All.csv")); //Call ExcelTemplate.BindData to bind the data to the template xlt.BindData(dts, "Top", dataBindProps); xlt.BindData(dts2, "Details", dataBindProps); } //Process and stream the final report back to the user xlt.Process(); xlt.Save(Page.Response, "Output.xlsx", false); } |
17. Now you may run your code. Just call GenerateTemplate() and PopulateTemplate() to customize and populate your template.
...
You can download the code for the Extended Sales Summary here.