I have following requirements in subtotals. Please confirm me if they are supported by Aspose library or not? If the library supports, please provide me steps and sample examples in achieving the functionality.
FYI, we are using Aspose totals java 18.5 licensed version currently.
Our requirement are,
Label name changes to Subtotal row, both Change of column and Aggregation column.
Aspose.Cells fully supports the Subtotals feature found in MS Excel. Refer to our documentation on the Subtotals feature for detailed guidance. It’s important to note that Aspose.Cells adheres to the same rules and specifications as Excel, including any limitations present in Excel’s Subtotals feature.
Should you need specific labels or formulas for Subtotal rows, you can manually adjust them after applying the Subtotals feature to your dataset. Additionally, you have the option to merge cells and format rows to enhance the presentation of Subtotal and Grand Total sections. For customization beyond standard functionality, consider leveraging Aspose.Cells’ Find and Search options to locate the cells/row/col and modify specific elements within your spreadsheet. If you encounter any challenges or require further assistance, please provide a sample Excel file along with your code, and we’ll promptly address any issues you encounter.
See the following sample code to accomplish your tasks as per your mentioned points. You may refer to the sample code to understand it and then write/update your own code accordingly for your custom needs.
e.g., Sample code:
// Instantiate a new workbook
Workbook workbook = null;
try {
workbook = new Workbook("d:\\files\\book1.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 = 16;
ca.EndColumn = 1;
// Get the workbook datasorter object.
DataSorter sorter = workbook.getDataSorter();
// Set the first order for datasorter object.
sorter.setOrder1(SortOrder.ASCENDING);
// Define the first key.
sorter.setKey1(0);
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, 0, ConsolidationFunction.SUM, new int[] {1});
Style style;
StyleFlag styleFlag;
String str =null;
String strFind = "Total";
FindOptions opts = new FindOptions();
opts.setLookInType(LookInType.VALUES);
opts.setLookAtType(LookAtType.END_WITH);
Cell cell = null;
do
{
cell = cells.find(strFind, cell, opts);
if(cell!=null)
{
int row = cell.getRow();
int col = cell.getColumn();
str = cell.getStringValue().replace(strFind,"").trim();
switch (str)
{
case"Apples":
case"Bananas":
case"Oranges":
cell.putValue("Quantity of " + str);
break;
case "Grand":
cell.putValue("Quantity of Fruits");
}
//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.getYellow());
style.setPattern(BackgroundType.SOLID);
styleFlag = new StyleFlag();
styleFlag.setFontName(true);
styleFlag.setFontSize(true);
styleFlag.setFontBold(true);
styleFlag.setCellShading(true);
cells.applyRowStyle(cell.getRow(),style, styleFlag);
}
}
while(cell!=null);
//Extend the column width a bit
cells.setColumnWidthPixel(0,172);
//Auto-fit rows in the worksheet
workbook.getWorksheets().get(0).autoFitRows();
// Save the excel file
try {
workbook.save("d:\\files\\AsposeCreateSubtotals_out1.xlsx");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Print message
System.out.println("Process completed successfully");
The solution you provided for label changes and formatting subtotal rows for now looks fine.
One more question, is there any solution available to off the repeating data in the change of columns. i.e. in our input xlsx, Change of column is “Fruit Name”, so whenever there is a change in the fruit name, the subtotal is applying and that’s fine. But the data in the “Fruit Name” column is repeating, Example: The name “Apples” is repeating in each row, instead it should come once in the first row only. i have enclosed the sample output xlsx file. Please check and update if any solution exists to achieve this kind of behaviour. Please check cells in yellow/amber colour in the attached sheet. create_subtotal_not_repeating_data_in_change_of_column.zip (7.6 KB)
I think you may either remove those repeating values in the specified cells (via Cell.putValue(“”), etc.) or merge those repeating range of cells. When you merge cells, only top(left) cell value would be retained and single cell would be formed, see the document on merging cells for your reference.