How i can do this doc? c#
- It should be group
- Sum results should be SUM
- Sum result should be formula
image.png (110.5 KB)
How i can do this doc? c#
Please provide the following to evaluate your requirements and to assist you accordingly:
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
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
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.