.
...
| Info | ||
|---|---|---|
| ||
There is a downloadable ExcelWriter_Basic_Tutorials.zip with completed templates and code. The completed example of the template is available under templates/part2_template.xlsx. The code for this part of the tutorial can be found in Part2.aspx.cs. |
...
1. Define a method to contain the ExcelApplication code to create a new worksheet and customize it. In the sample, there is an AddCoverSheet() method that holds the code for the ExcelApplicattion code in this part of the tutorial.
| Code Block |
|---|
protected void AddCoverSheet()
{
}
|
2. You should have already completed Part 1 of this tutorial. To include the AddCoverSheet() method, just add a call in GenerateTemplate()
| 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();
/*********Part 2*************/
AddCoverSheet();
/**************************/
}
|
...
4. Then insert the Picture object using Pictures.CreatePicture.
| Code Block |
|---|
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);
|
...
1. Set the Cell.Value of cell A3 to the string "Date Executed :". In cell A4, set Cell.Value to "Report For:". In cell A6, set the Cell.Value to "Table of Contents".
| Code Block |
|---|
ws.Cells["A3"].Value = "Date Executed :";
ws.Cells["A4"].Value = "Report For :";
ws.Cells["A6"].Value = "Table of Contents";
|
2. Insert the data markers for the "Date Executed" and "Report For" data, which will be imported using ExcelTemplate.
| Code Block |
|---|
ws.Cells["C3"].Value = "%%=WebFormData.Date";
ws.Cells["C4"].Value = "%%=WebFormData.Name";
|
...
Iterate through the country sheet names using a for loop, excluding the "Summary" and hidden "SimpleTemplate" sheets, which are the first two worksheets in the workbook.
| Code Block |
|---|
for (int i = 2; i < wb.Worksheets.Count; i++)
{
string sheetName = wb.Worksheets[i].Name.ToString();
ws.Cells[16 + i, 1].Formula = "=HYPERLINK(\"#"+sheetName+"!A1\", \""+sheetName+"\")";
}
|
...
2. Set the Font.Bold to true and Font.Size to 12.
| Code Block |
|---|
labels.Font.Bold = true;
labels.Font.Size = 12;
|
3. Apply the labels style to the label cells with Cell.ApplyStyle(Style).
| Code Block |
|---|
ws.Cells["A3"].ApplyStyle(labels);
ws.Cells["A4"].ApplyStyle(labels);
ws.Cells["A6"].ApplyStyle(labels);
|
...
5. The final code for AddCoverSheet() should be:
| Code Block |
|---|
protected void AddCoverSheet()
{
Worksheet ws = wb.Worksheets.CreateWorksheet("Summary", 0);
ws.Select();
ws.ShowGridlines = false;
/*******Inserting the Image********/
ws.GetRowProperties(0).Height = 90;
string imagePath = RBImage.SelectedItem.Value;
Anchor anc = wb.Worksheets[0].CreateAnchor(0, 0, 0, 0);
Picture logo = ws.Pictures.CreatePicture(Page.MapPath(@imagePath), anc);
/*******Writing values*************/
ws.Cells["A3"].Value = "Date Executed :";
ws.Cells["A4"].Value = "Report For :";
ws.Cells["A6"].Value = "Table of Contents";
ws.Cells["C3"].Value = "%%=WebFormData.Date";
ws.Cells["C4"].Value = "%%=WebFormData.Name";
for (int i = 2; i < wb.Worksheets.Count; i++)
{
string sheetName = wb.Worksheets[i].Name.ToString();
ws.Cells[7 + i, 1].Formula = "=HYPERLINK(\"#" + sheetName +
"!A1\", \"" + sheetName + "\")";
}
/******Adding Styles to Text*******/
GlobalStyle labels = wb.CreateStyle();
labels.Font.Bold = true;
labels.Font.Size = 12;
ws.Cells["A3"].ApplyStyle(labels);
ws.Cells["A4"].ApplyStyle(labels);
ws.Cells["A6"].ApplyStyle(labels);
ws.Cells["A6"].Style.Font.Underline = Font.UnderlineStyle.Single;
}
|
...
ExcelTemplate can be bound to numerous types of .NET data structures, some of which have built in column names, such as the DataTable. When working with arrays, which don't have built in column names, you have to define the column names in a separate string array.
| Code Block |
|---|
string[] coverData = {recipient, DateTime.Now.Date.ToString("M/dd/yyyy")};
string[] coverMarkers = { "Name", "Date" };
|
...
4. The final code for PopulateTemplate(), including the code from Part 1 should look like this:
| Code Block |
|---|
protected void PopulateTemplate()
{
xlt = new ExcelTemplate();
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);
}
/**********Part 2***********/
string recipient = TextBox1.Text;
string[] coverValues = { recipient, DateTime.Now.Date.ToString("M/dd/yyyy") };
string[] coverNames = { "Name", "Date" };
xlt.BindRowData(coverValues, coverNames, "WebFormData",
xlt.CreateDataBindingProperties());
/***************************/
xlt.Process();
xlt.Save(Page.Response, "Output.xlsx", false);
}
|
...
You can download the code for the Extended Sales Summary here.