Group feature in Aspose.cells JAVA

Aspose.Cells Product Family
1

Hello Team, Good Morning. How can we apply grouping logic to columns using Aspose cells.

I am attaching sample sheet with desired output.

Basically I want to be able to group columns Quarter and year wise.

Sample Excel.zip (6.7 KB)

2

@sourav24
Please refer to the following documents for grouping and ungrouping Rows and Columns:

3

@sourav24
Firstly, delete the group feature from the file, and then run the following code to achieve your goal. Please refer to the attachment (13.9 KB).

The sample code as follows:

//load your sample file
Workbook book = new Workbook(filePath + "sample.xlsx");
Cells cells = book.getWorksheets().get(0).getCells();
cells.groupColumns(0, 15);
cells.groupColumns(0, 2);
cells.groupColumns(4, 6);
cells.groupColumns(8, 10);
cells.groupColumns(12, 14);
book.save(filePath + "out.xlsx");

Hope helps a bit.

4

Thanks a lot @John.He This is exactly what I needed.

5

@sourav24
You are welcome. If you have any questions, please feel free to contact us.

6

Hello @John.He Another quick question, how can we copy cells from one sheet to another with values and style preserved.

Basically I have a cell which is SUM(A1:A15) with evaluated value as 150 and is red in color.

I want to copy this cell with just the value (formula removed) and color as well.

7

@sourav24
You can copy data and styles through range copying. Please refer to the following documents:

8

@sourav24,

Moreover, since you are using Aspose.Cells for Java, so I will paste the parallel (Java) document links for your reference.
https://docs.aspose.com/cells/java/copy-range-data-only/
https://docs.aspose.com/cells/java/copy-range-data-with-style/
https://docs.aspose.com/cells/java/copy-range-style-only/
https://docs.aspose.com/cells/java/copy-ranges-of-excel/

Hope, this helps a bit.

9

Thanks @amjad.sahi @John.He

10

@sourav24 ,

You are welcome. If you have any further queries or comments, please feel free to contact us anytime.

11

Yes @amjad.sahi I have another question.

Please find attached sample sheet. I want to put conditional formatting on the pivot with the logic as follows

  1. If Jan=Feb value then color green
  2. If Jan>Feb then color Red
  3. If Feb>Jan then color Blue

How can this be achieved?

Sample.zip (9.4 KB)

12

@sourav24,

Could you please perform your desired conditional formatting upon pivot table in MS Excel manually, save the Excel file and provide us. We will check on how to do it via Aspose.Cells for Java API.

13

Hello @amjad.sahi Please find attached excel with sample output. Basically actual and forecast values need to be compared and then formatting applied on actual values.

Sample.zip (9.7 KB)

14

@sourav24
Setting conditional styles by comparing two rows of data is not supported in pivot tables. You can search for corresponding values through the data range of the pivot table and compare them, and then set styles for the corresponding cells. Please refer to the following code to achieve the goal.

The sample code as follows:

Workbook wb = new Workbook(filePath + "Sample.xlsx");

Style blueStyle = new CellsFactory().createStyle();
blueStyle.setForegroundColor(Color.getBlue());
Style redStyle = new CellsFactory().createStyle();
redStyle.setForegroundColor(Color.getRed());
Style greenStyle = new CellsFactory().createStyle();
greenStyle.setForegroundColor(Color.getGreen());

Worksheet sheet = wb.getWorksheets().get(0);
Cells cells = sheet.getCells();
PivotTable pivot = sheet.getPivotTables().get(0);
CellArea dataBodyRange = pivot.getDataBodyRange();

int row1 = dataBodyRange.StartRow;
int row2 = row1 + 1;
int startCol = dataBodyRange.StartColumn;
int endCol = dataBodyRange.EndColumn;
for (int col = startCol; col <= endCol; col++)
{
    double val1 = cells.get(row1, col).getDoubleValue();
    double val2 = cells.get(row2, col).getDoubleValue();
    if (val1 > val2)
    {
        pivot.format(row1, col, redStyle);
    }
    else if (val1 < val2)
    {
        pivot.format(row1, col, blueStyle);
    }
    else
    {
        pivot.format(row1, col, greenStyle);
    }
}

wb.save(filePath + "out_java.xlsx");

Hope helps a bit.