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
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 =20public 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