Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

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
        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, @"..\..\Output\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(@"..\..\Data\GroupingBikes.csv");
            if (catId==2)
                dt = GetCSVData(@"..\..\Data\GroupingClothing.csv");
            if (catId==3)
                dt = GetCSVData(@"..\..\Data\GroupingClothing.csv");
            if (catId==4)
                dt = GetCSVData(@"..\..\Data\GroupingAccessories.csv");
            return dt;
        }

        //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;
        }

Downloads

  • Data:

...

...

...