CODEWITHSUNDEEP
javaexcelapache-poi
makeItEasier
makeItEasier

Reputation: 216

Multiple groups of columns on excel with apache poi

I'm using apache poi version 4.0.0 and I'm trying to make many groups of columns like the image below: I don't know if it is possible using poi.

enter image description here

I have tried with XSSFSHeet.groupColumn() method inside a loop .

            int indexColumnMonth = INDEX_START_RESULTS;
            for(int i =0; i< meses.size(); i++) {
                int columnFim = indexColumnMonth + (turmas.size());
                planilha.groupColumn(indexColumnMonth, columnFim);
                indexColumnMonth = columnFim +1;
            }

But the result of that is a sheet with just one group of columns that get all the columns. I've checked if the positions were generated right , but i'ts correct.

Is there a way to do that with POI?

Upvotes: 1

Views: 5619

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

[rant start, mode:sarcastic] As often it lacks the understanding how Excel works. Many programmers seems to think they can programming Excel functionalities without knowing Excel. They might think: "Excel why shall I bothering with this? I am a programmer, not a office worker." But for programming Excel functionalities one must know Excel at first. There is no way around. [/rant end]

If you are opening the Excel file showed in your picture in Excel's GUI and do clicking on the first - above J, then is J part of the group? No it is not. And is it part of the second group? No it also is not. That is because there must be at least one column between single groups of columns if they shall appearing as in your picture.

So as shown in your picture you have grouped E:I, K:O and Q:U.

Source:

enter image description here

Code:

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

class ExcelGroupColumns {

    public static void main(String[] args) throws Exception{

        FileInputStream in = new FileInputStream("Test.xlsx");
        Workbook workbook = WorkbookFactory.create(in);

        Sheet sheet = workbook.getSheetAt(0);

        int firstColumn = 4;
        int countGroupedColumns = 5;
        int countGroups = 3;

        for (int i = 0; i < countGroups; i++) {
            sheet.groupColumn(firstColumn, firstColumn + countGroupedColumns - 1);
            firstColumn += countGroupedColumns + 1;
        }

        FileOutputStream out = new FileOutputStream("Test_1.xlsx");
        workbook.write(out);
        out.close();
        workbook.close();

    }
}

Result:

enter image description here

Upvotes: 7

Related Questions