Intro
Excerpt |
---|
Add Excel's grouping and outlining functionality to a workbook with ExcelWriter to display nested data. |
The sales data is based on the 2008 sales data from the sample AdventureWorks data base. The sales data is written into the worksheet and grouped by major product category. Subtotal lines are written after each major product category. Each group of product sales line items is also grouped into a collapsible outline.
...
Note |
---|
This sample stores some of the data in a CSV file, which is available for download under Downloads. The CSV parser used in the example code was developed by Andrew Rissing and can be downloaded from Code Project. |
Code
Code Block |
---|
public class GroupingSubtotals
{
private ExcelApplication xlw;
private Workbook wb;
private Worksheet ws;
private int year = 2008;
private bool bCollapsed = true;
/// <summary>
/// Build the report with ExcelApplication
/// </summary>
public void GenerateReport()
{
// Creat a blank workbook and a single worksheet
xlw = new ExcelApplication();
wb = xlw.Create(ExcelApplication.FileFormat.Xlsx);
ws = wb.Worksheets[0];
// Populate
this.PopulateWorksheet(year);
//TODO Not implemented
//ws.createRangeOfColumns
// Save the report
xlw.Save(wb, @"..\..\ExcelOutputFiles\GroupingSubtotals_output.xlsx");
}
/// <summary>
/// Write values and import data into the Worksheet.
/// Also, sets up outlining and formulas.
/// </summary>
/// <param name="year">Display orders from this year</param>
/// <param name="bCollapsed">Show outlines as collapsed?</param>
/// <param name="bAddChart">Add chart to worksheet?</param>
private void PopulateWorksheet(int year)
{
// Create a style for the total rows.
//Make the font bold-faced, and shade
//the cells with light grey.
GlobalStyle totalStyle = wb.CreateStyle();
totalStyle.Font.Bold = true;
totalStyle.BackgroundColor = wb.Palette.GetClosestColor(211, 211, 211);
// This area will be used to determine how
//many rows of data were imported from the DataTable
Area area = null;
// These row and col variables are counters to
//keep track of the current row and column
int row = 0;
int col = 0;
// Cell references for the total rows will be
//added into these vectors for the later use in
//a chart
ArrayList categoryList = new ArrayList();
ArrayList seriesList = new ArrayList();
string[] categories = { "Bike", "Component", "Clothing", "Accessory" };
// Loop through the DataTables returned
//from the getData method
for (int i = 0; i < 4; i++)
{
Cell c = ws[row, col];
// Write the category name
c.Value = categories[i];
// Add the category cell to the category vector
categoryList.Add(String.Format("{0}!{1}", ws.Name, ws[row, col].Name));
ws[row, col + 1].Value = "Total:";
// Apply totalStyle to the Area,
//and create a border around the row.
Area totalArea = ws.CreateArea(row, col, 1, 3);
totalArea.SetStyle(totalStyle);
ws[row, col + 1].Style.HorizontalAlignment = Style.HAlign.Right;
totalArea.BorderAround.Style = Border.LineStyle.Thin;
DataImportProperties dProps = wb.CreateDataImportProperties();
dProps.ConvertStrings = true;
dProps.Truncate = true;
// Import the data from the DataTable
DataTable dt = GetData(i + 1);
area = ws.ImportData(dt, ws[row + 1, 1], dProps);
// Compute where the last row of data was imported to
int lastRow = row + area.RowCount + 1;
// Loop through all rows that were just imported
//and set the outline level to group the rows.
for (int iRow = row + 1; iRow < lastRow; iRow++)
{
RowProperties rp = ws.GetRowProperties(iRow);
rp.OutlineCollapsed = bCollapsed;
rp.OutlineLevel = 1;
}
// Add a SUBTOTAL formula to the total row.
string formula = String.Format("=SUBTOTAL(9,{0}:{1})", ws[row + 1, 2].Name, ws[lastRow, 2].Name);
ws[row, 2].Formula = formula;
// Add the total cell reference to the series vector
seriesList.Add(String.Format("{0}!{1}", ws.Name, ws[row, 2].Name));
// Increment the row counter to the end of the
//imported data area, and skip two.
row = row + area.RowCount + 2;
}
// TODO Not implemented
//ws.createRange("A1:C1").autoFitWidth();
// Set column widths
ws.GetColumnProperties(0).Width = 80;
ws.GetColumnProperties(1).Width = 195;
ws.GetColumnProperties(2).Width = 110;
// Create a style to format the currency column
GlobalStyle stylCurrency = wb.CreateStyle();
// Create and apply a typical Accounting format
//to the currency column.
NumberFormat nf = wb.NumberFormat;
String fmtString = nf.CreateAccounting(2, true, null);
stylCurrency.NumberFormat = fmtString;
Area ar = ws.CreateArea(0, 2, row - 1, 1);
ar.ApplyStyle(stylCurrency);
// Join the cell reference vectors into formula
//strings for the charts.
string categoryFormula = String.Join(",", (string[])categoryList.ToArray(typeof(string)));
string seriesFormula = String.Join(",", (string[])seriesList.ToArray(typeof(string)));
Console.WriteLine(categoryFormula);
// Create a 3D pie chart on a chartsheet
Chartsheet wsChart =
wb.Worksheets.CreateChartsheet(ChartType.Pie.Pie3D, "Chart");
Chart chrt = wsChart.Chart;
chrt.SeriesCollection.CategoryData = categoryFormula;
Series srsSales = chrt.SeriesCollection.CreateSeries(seriesFormula);
}
///<summary>
/// Uses a 3rd party generic CSV parser
/// DataTable of product line items
/// </summary>
/// <returns></returns>
private DataTable GetData(int catId)
{
DataTable dt=new DataTable();
if (catId==1)
dt = GetCSVData(@"..\..\ExcelData\GroupingBikes.csv");
if (catId==2)
dt = GetCSVData(@"..\..\ExcelData\GroupingClothing.csv");
if (catId==3)
dt = GetCSVData(@"..\..\ExcelData\GroupingClothing.csv");
if (catId==4)
dt = GetCSVData(@"..\..\ExcelData\GroupingAccessories.csv");
return dt;
}
#region Utility Methods
//Uses CSV reader
System.Data.DataTable GetCSVData(string csvFileName)
{
DataTable dt;
using (GenericParserAdapter parser = new GenericParserAdapter(csvFileName))
{
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
dt = parser.GetDataTable();
}
return dt;
}
#endregion
}
|
...