Message-ID: <420361035.8903.1711667307618.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8902_149902938.1711667307618" ------=_Part_8902_149902938.1711667307618 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Grouping and Subtotals

Grouping and Subtotals

Intro

Add Excel's groupin= g and outlining functionality to a workbook with ExcelWriter to display nes= ted data.

The sales data is based on the 2008 sales data from the sample Adventure= Works data base. The sales data is written into the worksheet and grouped b= y major product category.  Subtotal lines are written after each major= product category.  Each group of product sales line items is also gro= uped into a collapsible outline.

If you need to display nested, subtotaled data in your Excel report, thi= s demo will be particularly useful to you.

=20
=20 Icon=20
=20

This sample stores some of the data in a CSV file, which is available fo= r download under Downloads. The CSV parser used in the exa= mple code was developed by Andrew Rissing and can be downloaded from Code Project.

=20
=20
=20

Code

=20
     public class GroupingSubtotals
    {
        private ExcelApplication xlw;
        private Workbook wb;
        private Worksheet ws;
        private int year =3D 2008;
        private bool bCollapsed =3D true;

        /// <summary>
        /// Build the report with ExcelApplication
        /// </summary>
        public void GenerateReport()
        {
            // Creat a blank workbook and a s=
ingle worksheet 
            xlw =3D new ExcelApplication();
            wb =3D xlw.Create(ExcelApplicatio=
n.FileFormat.Xlsx);
            ws =3D wb.Worksheets[0];

            // Populate 
            this.PopulateWorksheet(year);

            //TODO Not implemented
            //ws.createRangeOfColumns  

            // Save the report 
            xlw.Save(wb, @"..\..\ExcelOu=
tputFiles\GroupingSubtotals_output.xlsx");
        }

        /// <summary>
        /// Write values and import data into the  =
;Worksheet. 
        /// Also, sets up outlining and formulas.
        /// </summary>
        /// <param name=3D"year">Displa=
y orders from this year</param> 
        /// <param name=3D"bCollapsed">=
Show outlines as collapsed?</param>
        /// <param name=3D"bAddChart">A=
dd chart to worksheet?</param>
        private void PopulateWorksheet(int year)
        {
            // Create a style for the total r=
ows.
             //Make the font bold-faced,=
 and shade
             //the cells with light grey=
.
             
            GlobalStyle totalStyle =3D wb.Cre=
ateStyle();
            totalStyle.Font.Bold =3D true;
            totalStyle.BackgroundColor =3D wb=
.Palette.GetClosestColor(211, 211, 211);

            // This area will be used to dete=
rmine how
             //many rows of data were im=
ported from the DataTable
             
            Area area =3D null;

            // These row and col variables ar=
e counters to
             //keep track of the current=
 row and column
             
            int row =3D 0;
            int col =3D 0;

            // Cell references for the total =
rows will be
             //added into these vectors =
for the later use in
             //a chart
             
            ArrayList categoryList =3D new Ar=
rayList();
            ArrayList seriesList =3D new Arra=
yList();

            string[] categories =3D { "B=
ike", "Component", "Clothing", "Accessory&quo=
t; };

            // Loop through the DataTables re=
turned
             //from the getData method
             
            for (int i =3D 0; i < 4; i++)
            {
                Cell c =3D ws[row, =
col];

                // Write the catego=
ry name 
                c.Value =3D categor=
ies[i];

                // Add the category=
 cell to the category vector 
                categoryList.Add(St=
ring.Format("{0}!{1}", ws.Name, ws[row, col].Name));

                ws[row, col + 1].Va=
lue =3D "Total:";

                // Apply totalStyle=
 to the Area, 
                 //and create =
a border around the row.
                 
                Area totalArea =3D =
ws.CreateArea(row, col, 1, 3);
                totalArea.SetStyle(=
totalStyle);
                ws[row, col + 1].St=
yle.HorizontalAlignment =3D Style.HAlign.Right;
                totalArea.BorderAro=
und.Style =3D Border.LineStyle.Thin;

                DataImportPropertie=
s dProps =3D wb.CreateDataImportProperties();
                dProps.ConvertStrin=
gs =3D true;
                dProps.Truncate =3D=
 true;

                // Import the data =
from the DataTable 
                DataTable dt =3D Ge=
tData(i + 1);
                area =3D ws.ImportD=
ata(dt, ws[row + 1, 1], dProps);

                // Compute where th=
e last row of data was imported to 
                int lastRow =3D row=
 + area.RowCount + 1;

                // Loop through all=
 rows that were just imported
                 //and set the=
 outline level to group the rows.
                 
                for (int iRow =3D r=
ow + 1; iRow < lastRow; iRow++)
                {
                    RowPr=
operties rp =3D ws.GetRowProperties(iRow);
                    rp.Ou=
tlineCollapsed =3D bCollapsed;
                    rp.Ou=
tlineLevel =3D 1;
                }

                // Add a SUBTOTAL f=
ormula to the total row. 
                string formula =3D =
String.Format("=3DSUBTOTAL(9,{0}:{1})", ws[row + 1, 2].Name, ws[l=
astRow, 2].Name);
                ws[row, 2].Formula =
=3D formula;

                // Add the total ce=
ll reference to the series vector 
                seriesList.Add(Stri=
ng.Format("{0}!{1}", ws.Name, ws[row, 2].Name));

                // Increment the ro=
w counter to the end of the 
                 //imported da=
ta area, and skip two.
                 
                row =3D row + area.=
RowCount + 2;
            }

            // TODO Not implemented
            //ws.createRange("A1:C1"=
;).autoFitWidth();

            // Set column widths 
            ws.GetColumnProperties(0).Width =
=3D 80;
            ws.GetColumnProperties(1).Width =
=3D 195;
            ws.GetColumnProperties(2).Width =
=3D 110;

            // Create a style to format the c=
urrency column 
            GlobalStyle stylCurrency =3D wb.C=
reateStyle();

            // Create and apply a typical Acc=
ounting format
             //to the currency column.
             
            NumberFormat nf =3D wb.NumberForm=
at;
            String fmtString =3D nf.CreateAcc=
ounting(2, true, null);
            stylCurrency.NumberFormat =3D fmt=
String;
            Area ar =3D ws.CreateArea(0, 2, r=
ow - 1, 1);
            ar.ApplyStyle(stylCurrency);

            // Join the cell reference vector=
s into formula 
             //strings for the charts.
             
            string categoryFormula =3D String=
.Join(",", (string[])categoryList.ToArray(typeof(string)));
            string seriesFormula =3D String.J=
oin(",", (string[])seriesList.ToArray(typeof(string)));
            Console.WriteLine(categoryFormula=
);
            // Create a 3D pie chart on a cha=
rtsheet 

            Chartsheet wsChart =3D
                wb.Worksheets.Creat=
eChartsheet(ChartType.Pie.Pie3D, "Chart");
            Chart chrt =3D wsChart.Chart;
            chrt.SeriesCollection.CategoryDat=
a =3D categoryFormula;

            Series srsSales =3D chrt.SeriesCo=
llection.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=3Dnew DataTable();
            if (catId=3D=3D1)
                dt =3D GetCSVData(@=
"..\..\ExcelData\GroupingBikes.csv");
            if (catId=3D=3D2)
                dt =3D GetCSVData(@=
"..\..\ExcelData\GroupingClothing.csv");
            if (catId=3D=3D3)
                dt =3D GetCSVData(@=
"..\..\ExcelData\GroupingClothing.csv");
            if (catId=3D=3D4)
                dt =3D GetCSVData(@=
"..\..\ExcelData\GroupingAccessories.csv");
            return dt;
        }

         #region Utility Methods
        //Uses CSV reader
        System.Data.DataTable GetCSVData(string csvFile=
Name)
        {
            DataTable dt;
            using (GenericParserAdapter parse=
r =3D new GenericParserAdapter(csvFileName))
            {
                parser.ColumnDelimi=
ter =3D ',';
                parser.FirstRowHasH=
eader =3D true;

                dt =3D parser.GetDa=
taTable();
            }
            return dt;
        }

        #endregion
        }
=20

Downloads

------=_Part_8902_149902938.1711667307618--