Excel doc with group

How i can do this doc? c#

  1. It should be group
  2. Sum results should be SUM
  3. Sum result should be formula
    image.png (110.5 KB)

@azamatik471,

Please provide the following to evaluate your requirements and to assist you accordingly:

  1. You input Excel file
  2. You output Excel file generated by Aspose.Cells (if you are already using it)
  3. Sample code (runnable) if 2) is true.
  4. Your expected Excel file. You may create your expected Excel file in MS Excel manually. Give steps details in MS Excel (if it is complex one).

After we have received the above resources and artifacts, we will start evaluating it.

PS. please zip the Excel files or other resources prior attaching here.

Desktop.zip (15.2 KB)
i dont know how group rows in columns

  1. Columns D,E,F - should be SUM
  2. Coluns G,H - formula. because SUM PERCENTS - ERROR

@azamatik471,

Thanks for the sample files.

You can accomplish the task using bit of both Subtotal (Excel feature) and manual formula insertion via Aspose.Cells APIs for your needs. See the sample code for your requirements for your reference:
e.g.
Sample code:


//Instantiate a new workbook
//Open the template file
Workbook workbook = new Workbook(@"e:\test2\input.xlsx");

            //Get the Cells collection in the first worksheet
            Cells cells = workbook.Worksheets[0].Cells;


            //Create a cellarea 
            CellArea ca = new CellArea();
            ca.StartRow = 1;
            ca.StartColumn = 1;
            ca.EndRow = cells.MaxDataRow;
            ca.EndColumn = cells.MaxDataColumn;

            //Apply subtotal, the consolidation function is Sum and it will applied to
            //Second column (B) in the list
            cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 2,3,4 }, true, false, true);
                        
            //Now we got to find the total (summary) rows to put formulas in the relevant columns cells (last two columns)for percentages.
            FindOptions opts = new FindOptions();
            opts.LookInType = LookInType.Values;
            opts.LookAtType = LookAtType.Contains;
            opts.RegexKey = true;
            Aspose.Cells.Cell cell = null;
            Aspose.Cells.Cell fcell = null;
            do
            {   //find the cells containing the word.  
                cell = cells.Find("Total", cell, opts);
                if (cell != null)
                {
                    int row = cell.Row +1;
                    //set first manuall formula in G column 
                    fcell = cells["G" + row];
                    fcell.SetFormula("=D" + row + "/F" + row, null);  
                    
                    //set second manual formula in H column
                    fcell = cells["H" + row];
                    fcell.SetFormula("=E" + row + "/F" + row, null);  
                    
                }
            }
            while (cell != null);

            workbook.CalculateFormula();

//Save the excel file
workbook.Save(@"e:\test2\out1.xlsx");

Hope, this helps a bit.

Thanks! it works

@azamatik471,

Good to know that the suggested code segment works for your needs. In the event of further queries or issue, feel free to write us back.