Aspose cells java subtotal

Aspose.Cells Product Family
,
1

I have enclosed 3 files,

  1. excel input containing the data
  2. excel subtotal output generated using MS Excel Subtotal function manually
  3. excel subtotal output generated using Aspose cells (18.5) subtotal feature programmatically

There is a discrepancy between both the outputs. Please check the outline menu on left side of the worksheet.

FYI, applied subtotal feature twice,

  1. Region change of column, aggregate function SUM on the aggregation column Sale Price
  2. Region change of column, aggregate function SUM on the aggregate column Sale Unit
    And i have used subtotal(, false, false, true) method so that it will not override previous generated subtotal

I am also attaching the sample aspose code. Please check and help me in achieving the same behaviour as MS Excel

Sample Java Code

import java.io.IOException;
import java.io.InputStream;

import com.aspose.cells.BackgroundType;
import com.aspose.cells.Cell;
import com.aspose.cells.CellArea;
import com.aspose.cells.Cells;
import com.aspose.cells.ConsolidationFunction;
import com.aspose.cells.DataSorter;
import com.aspose.cells.License;
import com.aspose.cells.Row;
import com.aspose.cells.SortOrder;
import com.aspose.cells.Style;
import com.aspose.cells.StyleFlag;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class AsposeCreateSubtotals2 {

public static void main(String[] args) {
	
			// Instantiate a new workbook
			Workbook workbook = null;
			try {
				workbook = new Workbook(Thread.currentThread().getContextClassLoader().getResourceAsStream("5375.xlsx"));
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// Get the Cells collection in the first worksheet
			Cells cells = workbook.getWorksheets().get(0).getCells();

			// Create a cellarea i.e.., B3:C19
			CellArea ca = new CellArea();
			ca.StartRow = 1;// don't count header
			ca.StartColumn = 0;
			ca.EndRow = 49037;
			ca.EndColumn = 3;
			
			// Get the workbook datasorter object.
			DataSorter sorter = workbook.getDataSorter();
			// Set the first order for datasorter object.
			sorter.addKey(1,SortOrder.ASCENDING);
			sorter.addKey(0,SortOrder.ASCENDING);
							
			sorter.sort(workbook.getWorksheets().get(0).getCells(), ca);

			// Apply subtotal, the consolidation function is Sum and it will applied
			// to
			// Second column (C) in the list
			cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {3});
			
			cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {2}, false, false, true);
		       
		       applyStylesToSubtotalRow(workbook);

		       //Extend the column width a bit
		       cells.setColumnWidthPixel(0,172);
			// Save the excel file
			try {
				//Auto-fit rows in the worksheet
				workbook.getWorksheets().get(0).autoFitRows();
				workbook.save("AsposeCreateSubtotals_out2.xlsx");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// Print message
			System.out.println("Process completed successfully");

}

private static void applyStylesToSubtotalRow(Workbook wb) {
	Style style;
	StyleFlag styleFlag;		
	Cells cells = wb.getWorksheets().get(0).getCells();
	Cell cell = null;
	do {
		cell = cells.findFormulaContains("=SUBTOTAL(", cell);
		if (cell != null) {				
			// Apply formatting to subtotal and grand total rows
			style = wb.createStyle();
			style.getFont().setName("Calibri");
			style.getFont().setSize(14);
			style.getFont().setBold(true);
			style.setForegroundColor(com.aspose.cells.Color.getAliceBlue());
			style.setPattern(BackgroundType.SOLID);

			styleFlag = new StyleFlag();
			styleFlag.setFontName(true);
			styleFlag.setFontSize(true);
			styleFlag.setFontBold(true);
			styleFlag.setCellShading(true);
			
			cell.setStyle(style, styleFlag);

			//checkGrandTotalRow(wb.getWorksheets().get(0), cell.getRow(),cell.getColumn());
			
		}

	} while (cell != null);
}


private static void checkGrandTotalRow(Worksheet ws, int rowNum, int colNum) {
	for (int i = 0; i <= colNum; i++) {
		Object obj = ws.getCells().get(rowNum, i).getValue();
		System.out.println("obj >>" + obj);
		if (null != obj && obj.toString().toUpperCase().contains("GRAND")) {
			hideGrandTotalRow(ws, rowNum);
			break;
		}
	}
}

private static void hideGrandTotalRow(Worksheet ws, int rowNum) {
	System.out.println("delete");
	ws.getCells().deleteRow(rowNum);
}

}
AsposeCreateSubtotals_out2.zip (2.7 MB)

2

@koteswaragunda,

Thanks for the sample files and details.

I checked your sample files and evaluated the code segment a bit. I think you have manually adjusted (cut/paste a cells/range at the end) to mimic your needs in MS Excel. In newer versions (e.g., I used Aspose.Cells for Java v24.5) of Aspose.Cells, we provide insertCutCells method that can help to achieve your requirements. See the updated code segment (I mostly used your code segment with some additions/adaptations) using latest version of the API to try to mimic your requirements. I simply removed the filters and subtotals of your file and make it as input Excel file (attached in the zipped archive at the bottom).
e.g.,
Sample code:

        // Instantiate a new workbook
        Workbook workbook = null;
        try {
            workbook = new Workbook("d:\\files\\sample input1.xlsx");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        // Get the Cells collection in the first worksheet
        Cells cells = workbook.getWorksheets().get(0).getCells();

        // Create a cellarea i.e.., B3:C19
        CellArea ca = new CellArea();
        ca.StartRow = 1;// don't count header
        ca.StartColumn = 0;
        ca.EndRow = 49037;
        ca.EndColumn = 3;

        // Get the workbook datasorter object.
        DataSorter sorter = workbook.getDataSorter();
        // Set the first order for datasorter object.
        sorter.addKey(1,SortOrder.ASCENDING);
        sorter.addKey(0,SortOrder.ASCENDING);

        sorter.sort(workbook.getWorksheets().get(0).getCells(), ca);

        // Apply subtotal, the consolidation function is Sum and it will applied
        // to
        // Second column (C) in the list
        cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {3});

        cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {2}, false, false, true);

        //applyStylesToSubtotalRow(workbook);

        Style style;
        StyleFlag styleFlag;
        cells = workbook.getWorksheets().get(0).getCells();
        FindOptions findOptions = new FindOptions();
        findOptions.setLookInType(LookInType.FORMULAS);
        findOptions.setLookAtType(LookAtType.CONTAINS);
        Cell cell = null;
        do {
            cell = cells.find("=SUBTOTAL(", cell, findOptions);
            if (cell != null) {
                // Apply formatting to subtotal and grand total rows
                style = workbook.createStyle();
                style.getFont().setName("Calibri");
                style.getFont().setSize(14);
                style.getFont().setBold(true);
                style.setForegroundColor(com.aspose.cells.Color.getAliceBlue());
                style.setPattern(BackgroundType.SOLID);

                styleFlag = new StyleFlag();
                styleFlag.setFontName(true);
                styleFlag.setFontSize(true);
                styleFlag.setFontBold(true);
                styleFlag.setCellShading(true);

                cell.setStyle(style, styleFlag);

                //checkGrandTotalRow(wb.getWorksheets().get(0), cell.getRow(),cell.getColumn());

            }

        } while (cell != null);



        //Extend the column width a bit
        cells.setColumnWidthPixel(0,172);
        // Save the excel file
        try {
            int usedRangeLastColumnIndex = workbook.getWorksheets().get(0).getCells().getMaxDataColumn();
            int startRow = workbook.getWorksheets().get(0).getCells().getMaxDataRow() -1;
            Range rowRangeToCut = workbook.getWorksheets().get(0).getCells().createRange(startRow,1,1,usedRangeLastColumnIndex);

            // Cut the rows and insert them above the destination row
            workbook.getWorksheets().get(0).getCells().insertCutCells(rowRangeToCut,startRow-1, 1, ShiftType.DOWN);

            //Auto-fit rows in the worksheet
            workbook.getWorksheets().get(0).autoFitRows();
            workbook.save("d:\\files\\out1.xlsx");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        // Print message
        System.out.println("Process completed successfully");

Please find attached the zipped archive containing the input and output Excel file for your reference.
files1.zip (1.8 MB)

3

@amjad.sahi

I think you did not understand my question sorry for that.

In my first post, I enclosed subtotal output generated using Microsoft Excel and subtotal output generated using Aspose.cells. When I compare, both the outputs are not same. The output generated using MS Excel has 4 outlines (1,2,3,4) where as Aspose generated output has 5 outlines (1,2,3,4,5). Please pass through the outline numbers you will see the difference. I want similar output file like MS Excel generated using Aspose library too.

4

@koteswaragunda
By using sample file and code for testing on the latest version v24.5, we can reproduce the issue. Found that extra outlines appeared when adding subtotals.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45986

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

5

@John.He

Thanks for your reply. My organisation is having 18.5 licensed version. And I am sure we too have paid support. May be with other user id/name. Let me check and update here.

6

@koteswaragunda
You are welcome. Thank you for your feedback.

Please note, your issue/ticket is logged under normal (free) support model where issues are resolved on first come first serve basis. The resolution time of the issue depends upon the number of issues or other tasks logged prior to it.

You may check our paid support option where issues are prioritized and posses highest priority if your issue is a real blocker.

7

@koteswaragunda
1). If cells.subtotal() method is called, some subtotal rows will be inserted, so please change range before calling it again:

cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {3});
ca.EndRow = 49041;//cells.getMaxDataRow();
cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {2}, false, false, true);

2). It’s better that you can subtotal together as the following:

cells.subtotal(ca, 1, ConsolidationFunction.SUM, new int[] {2, 3});
8

Thanks @simon.zhao will change the code as per your suggestions.

9

@koteswaragunda,

You are welcome. And, sure, you may change the code as per the suggestions. Please feel free to write us back if you have further queries or comments, we will be happy to assist you soon.