...
Code Block |
---|
private ExcelApplication xlw;
private Workbook wb;
private Worksheet ws;
private int[] catIdList = { 1, 2, 3, 4 };
private bool bLegend = true;
/// <summary>
/// Build the report with ExcelApplication
/// </summary>
public void GenerateReport()
{
xlw = new ExcelApplication();
// Open the template workbook
string templatePath = @"..\..\Templates\MultiChart.xlsx";
wb = xlw.Open(templatePath);
ws = wb.Worksheets[0];
this.PopulatePieCharts();
this.PopulateMainChart();
// Save the report by streaming it
// to the client's browser
xlw.Save(wb, @"..\..\Output\MultiChartReport_output.xlsx");
}
/// <summary> Populate the worksheet with data for the main chart.
/// Reset the series collection objects and the category data
/// based on how many products were chosen for display.
/// </summary>
private void PopulateMainChart()
{
// Get Data for Quarterly Sales.
DataTable dtQuart = new DataTable();
for (int i=0;i<5;i++)
dtQuart.Columns.Add();
dtQuart.Rows.Add(new string[] { "Name", "Q1", "Q2", "Q3", "Q4" });
dtQuart.Rows.Add(new string[] { "Bikes", "6099507.645869", "7022584.175748", "10510509.832846", "11290678.586395" });
dtQuart.Rows.Add(new string[] {"Components", "459073.391596", "1111225.617307", "2524193.294746", "1391022.528312" });
dtQuart.Rows.Add(new string[] { "Clothing", "105669.610552", "191410.762794", "388216.721639", "326687.409399" });
dtQuart.Rows.Add(new string[] { "Accessories", "15623.156936", "32654.322865", "258987.196838", "282992.908554" });
// Find the main chart based on its title text, and get
// a reference to it.
Chart mainChart = FindChart("Quarterly Sales 2008");
DataImportProperties dProps = wb.CreateDataImportProperties();
dProps.UseColumnNames = false;
dProps.ConvertStrings = true;
ws.ImportData(dtQuart, ws.Cells["B29"], dProps);
// Re-set the category data. The size will vary
// depending on the selected categories.
seriesCol.CategoryData = ws.CreateArea(29, 1, 4, 1).Dimensions;
// Add a legend to the chart if desired
if (bLegend)
{
mainChart.Legend.Visible = true;
mainChart.Legend.Location = Legend.LegendLocation.Top;
}
else
{
// The legend is hidden in the template workbook.
// Hide it again in case it's made visible.
mainChart.Legend.Visible = false;
}
}
/// <summary> Obtain the database data and populate data for the
/// two pie charts on the right-hand side of the sheet.
/// </summary>
private void PopulatePieCharts()
{
DataTable dCS = new DataTable();
for (int i = 0; i < 4; i++)
dCS.Columns.Add();
dCS.Rows.Add(new string[] { "Clothing", null, "588594.532331" });
dCS.Rows.Add(new string[] { "Bikes", null, "22579811.983331" });
dCS.Rows.Add(new string[] { "Accessories", null, "568844.582411" });
dCS.Rows.Add(new string[] { "Components", null, "2091511.003980" });
DataImportProperties dProps = wb.CreateDataImportProperties();
dProps.UseColumnNames = false;
dProps.ConvertStrings = true;
ws.ImportData(dCS, ws.Cells["H27"], dProps);
DataTable topSales = new DataTable();
for (int i = 0; i < 4; i++)
topSales.Columns.Add();
topSales.Rows.Add(new string[]{ "Mitchell, Linda", null, "2126790.5635" });
topSales.Rows.Add(new string[]{ "Blythe,Michael", null, "1732868.0076" });
topSales.Rows.Add(new string[]{ "Pak, Jae", null, "2037152.8042" });
topSales.Rows.Add(new string[]{ "Varkey Chudukatil, Ranjit", null, "1549000.1782" });
topSales.Rows.Add(new string[]{ "Carson, Jillian", null, "1546519.7757" });
ws.ImportData( topSales, ws.Cells["H52"], dProps);
}
private Chart FindChart(string title)
{
Console.WriteLine("Chart Count " + ws.Charts.Count);
for (int iChart = 0; iChart < ws.Charts.Count; iChart++)
{
Chart chrt = ws.Charts[iChart];
if (chrt.Title.Text == title)
return chrt;
}
return null;
}
//loops through datatable and converts values to doubles
private void toDoubles(DataTable dt)
{
for (int i = 0; i < 4; i++)
{
for (int j = 1; j < 5; j++)
{
String s = dt.Rows[j].ItemArray[i].ToString();
Console.WriteLine(s);
Convert.ToDouble(s);
Double d1 = Convert.ToDouble(s);
dt.Rows[j].ItemArray[i] = d1;
Console.WriteLine(d1.GetType());
}
}
}
}
}
|
Downloads
Template MultiChart.xlsx
...