...
- Open Visual Studio and create a .NET project.
- The sample code uses a web application.
- Add a reference to SoftArtisans.OfficeWriter.ExcelWriter.dll
- SoftArtisans.OfficeWriter.ExcelWriter.dll is located under Program Files > SoftArtisans > OfficeWriter > dotnet > bin
Writing the Code
1. Include the SoftArtisans.OfficeWriter.ExcelWriter namespace in the code behind
Code Block | ||
---|---|---|
| ||
using SoftArtisans.Office.ExcelWriter; |
Info | |||||
---|---|---|---|---|---|
If you are following along and intend to use AdventureWorks2008R2, you will also want to make a reference to System.Data.SqlClient namespace. To do so, type into the code behind
|
...
2. In the method that will run the report, instantiate the ExcelApplication object.
Code Block | ||
---|---|---|
| ||
ExcelApplication xla = new ExcelApplication(); |
3. Open the ExcelApplication with the ExcelApplication.Open() method and instantiate it as a Workbook object.
Code Block |
---|
Workbook wb = xla.Open(Page.MapPath(@"templates\template.xlsx")) |
4. Assuming that you taking the selections from a ASP.NEt ListBox, using the following utility method, create a string list of all the countries that were selected.
Code Block |
---|
protected List<string> GetListBoxSelections(){
//Get the ListBox selections and make the appropriate number of copies
List<string> countryNames = new List<string>();
foreach (int i in ListBox1.GetSelectedIndices())
{
countryNames.Add(ListBox1.Items[i].Text);
}
return countryNames;
} |
Info |
---|
A utility method refers to a piece of code/function that is not affected by the ExcelWriter API framework. For the purpose of this tutorial, utility methods will be included. |
5. ExcelApplication can make copies of a specified worksheet using the Worksheets.CopySheet() method. This method requires a Worksheet object representing the sheet to be copied; an integer representing the new worksheet's position and, the string representing the name of the new worksheet.
Insert the following code that iterates through the selected countries in the string list to make copies of the "SimpleTemplate" in the original template.xlsx file. Each new worksheet is placed after the "SimpleTemplate" sheet and is named based on the selected country from the string list. The <i>for<i> loop uses the <i>List<i>.Count method to determine how many copies must be made.
Code Block |
---|
for (int i = 0; i < selectedCountries.Count; i++)
{
wb.Worksheets.CopySheet(wb.Worksheets[0], wb.Worksheets.Count, selectedCountries[i]);
} |
6. At this point, there is a worksheet named after each selected country and the original worksheet contained in the wb
Workbook object. We need to hide the original worksheet using the Worksheet.Visibility method. This method can also make a worksheet 'visible' or 'very hidden.'
Code Block |
---|
wb.Worksheets[0].Visibility = Worksheet.SheetVisibility.Hidden; |
7. Select the first worksheet after the now hidden one to be displayed when the file first is opened using the Worksheets.Select() method.
Code Block |
---|
wb.Worksheets[1].Select(); |
8.