...
Code Block |
---|
private ExcelApplication xlw;
private Workbook wb;
private Worksheet ws, wsChart;
private Area importedArea;
private bool ChartsYtd;
private string chartFontName = "Garamond";
private Color clrChartArea, clrLegend;
/// <summary>
/// Build the report with ExcelApplication
/// </summary>
public void GenerateReport(bool ytd)
{
//If ChartsYtd the pie chart will show year to date data
ChartsYtd = ytd;
// Create an instance of ExcelApplication
// and open the template file.
xlw = new ExcelApplication();
// Open the template workbook
string templatePath = @"..\..\Templates\SalesCommission.xlsx";
wb = xlw.Open(templatePath);
ws = wb.Worksheets[0];
// Setup the colors that will be used in the workbook
this.SetupColors();
// Import the data into the worksheet
this.PopulateWorksheet();
// Create a new worksheet to hold the charts.
// Set the gridline visibilty and tab color.
wsChart = wb.Worksheets.CreateWorksheet("Charts");
wsChart.ShowGridlines = false;
wsChart.TabColor = clrLegend;
// Add the charts
this.AddPieChart();
this.AddColumnChart();
// Save the report by streaming
// it to the client's browser
string reportName;
if (this.ChartsYtd)
{
reportName = "SalesCommissionReport-YTD.xlsx";
}
else
{
reportName = "SalesCommissionReport-LastYear.xlsx";
}
xlw.Save(wb, @"..\..\Output\"+reportName);
}
/// <summary> Initialize the colors to be used in
/// the workbook. These colors will be applied
/// to the chart areas and chart legends.
/// </summary>
private void SetupColors()
{
// Get the colors from the Workbook Palette object
Palette pal = wb.Palette;
clrChartArea = pal.GetClosestColor(240, 243, 248);
clrLegend = pal.GetClosestColor(234, 234, 234);
}
/// <summary> Add the column chart to the worksheet.</summary>
private void AddColumnChart()
{
// This chart will sit near cell A25
Anchor anch = wsChart.CreateAnchor(24, 0, 50, 50);
// Create the chart
Chart colChart = wsChart.Charts.CreateChart(ChartType.Column.Clustered3D, anch);
// Set title, title font, and shadow settings
colChart.ChartArea.HasShadow = true;
colChart.Title.Text = "Commission Payout Comparison";
colChart.Title.Font.Name = chartFontName;
colChart.Title.Font.Size = 22;
colChart.Legend.Font.Name = chartFontName;
// Set the chart area and legend colors.
// These colors were initialized in the setupColors() method.
colChart.ChartArea.Interior.ForegroundColor = clrChartArea;
colChart.Legend.Interior.ForegroundColor = clrLegend;
// Create areas for the chart's category data, and for the
// YTD and last year series objects.
Area categoryArea = ws.CreateArea(6, 1, importedArea.RowCount, 1);
Area seriesAreaLast = ws.CreateArea(6, 5, importedArea.RowCount, 1);
Area seriesAreaYTD = ws.CreateArea(6, 4, importedArea.RowCount, 1);
// Set category data. This will be the employee names on the X axis.
colChart.SeriesCollection.CategoryData = categoryArea.Dimensions;
// Create two series objects. Last Year and YTD will be displayed
// as chart columns side-by-side.
Series srsLast = colChart.SeriesCollection.CreateSeries(seriesAreaLast);
srsLast.Name = "Last Year";
Series srsYTD = colChart.SeriesCollection.CreateSeries(seriesAreaYTD);
srsYTD.Name = "YTD";
}
/// <summary> Add a pie chart to the chart worksheet.</summary>
private void AddPieChart()
{
// This chart will sit near cell A1
Anchor anch = wsChart.CreateAnchor(0, 0, 50, 50);
// Create a Pie3D chart
Chart pieChart = wsChart.Charts.CreateChart(ChartType.Pie.Pie3D, anch);
pieChart.ChartArea.HasShadow = true;
// Determine whether to show YTD or Last Year data
// based on the value passed in by the user.
int commCol;
if (ChartsYtd)
{
// YTD data is in the 5th column.
commCol = 4;
pieChart.Title.Text = "Commission Distribution - YTD";
wsChart.Name = "Charts - YTD";
}
else
{
// Last Year data is in the 6th column.
commCol = 5;
pieChart.Title.Text = "Commission Distribution - Last Year";
wsChart.Name = "Charts - Last Year";
}
// Set Chart title font appearance
pieChart.Title.Font.Name = chartFontName;
pieChart.Title.Font.Size = 22;
pieChart.Legend.Font.Name = chartFontName;
// Set chart area and legend shading.
// These colors were initialized in the setupColors() method.
pieChart.ChartArea.Interior.ForegroundColor = clrChartArea;
pieChart.Legend.Interior.ForegroundColor = clrLegend;
// Create Area objects for category and series data
Area categoryArea = ws.CreateArea(6, 1, importedArea.RowCount, 1);
Area seriesArea = ws.CreateArea(6, commCol, importedArea.RowCount, 1);
// Set the category and series data
pieChart.SeriesCollection.CategoryData = categoryArea.Dimensions;
Series commSeries = pieChart.SeriesCollection.CreateSeries(seriesArea);
// Show chart leader lines and values as percentages.
commSeries.SettingsPieDoughnut.ShowLeaderLines = true;
commSeries.DataLabels.ContainsValueAsPercentage = true;
commSeries.DataLabels.Font.Name = chartFontName;
commSeries.Line.Visible = true;
}
/// <summary> Import the data from a 2-D Object array
/// into the worksheet.
/// </summary>
private void PopulateWorksheet()
{
DataImportProperties dp = wb.CreateDataImportProperties();
dp.Truncate = true;
// The workbook has a named range "DataRange"
// defined that marks where the data should be imported.
// Get a reference to this range.
Range dataRange = wb.GetNamedRange("DataRange");
Area dataArea = dataRange.Areas[0];
// These arrays are the data to import
object[,] data = this.GetData();
string[] fieldNames = { "Name", "SalesYTD", "SalesLastYear" };
// Import the data into the data area.
importedArea = dataArea.ImportData(data, fieldNames, dp);
// Remove extra rows from the worksheet
int lastDataAreaRow = dataArea.FirstRow + dataArea.RowCount - 1;
int lastImportedAreaRow = importedArea.FirstRow + importedArea.RowCount - 1;
while (lastDataAreaRow > lastImportedAreaRow)
{
ws.DeleteRow(lastDataAreaRow);
--lastDataAreaRow;
}
}
/// <summary> ExcelWriter can import data from arrays as well as DataTables.
/// The data returned by this method will be imported into the worksheet.
/// </summary>
/// <returns> 2-D Object array of data
/// </returns>
private object[,] GetData()
{
object[,] data = {{"Ansman-Wolfe, Pamela", 2488342.5141, 1927059.1780},
{"Blythe, Michael", 2590055.1774, 1750406.4785},
{"Campbell, David", 1870183.5288, 1371635.3158},
{"Caro, Fernando", 1958815.8056, 1997186.2037},
{"Ito, Shu", 1679586.6629, 2073505.9999},
{"Mitchell, Linda", 2343461.0492, 1439156.0291},
{"Pak, Jae", 2568244.0549, 1635823.3967},
{"Reiter, Tsvi", 1855106.2631, 1849640.9418},
{"Saraiva, Jos?", 2153295.1978, 2038234.6549},
{"Tsoflias, Lynn", 2486869.8048, 2278548.9776},
{"Valdez, Rachel", 2160347.3087, 1307949.7917},
{"Vargar, Garrett", 2088272.9112, 1620276.8966},
{"Varkey Chudukatil, Ranjit", 2177055.6488, 2396539.7601}};
return data;
}
}
}
|
Downloads
...