0

How to write text/image to specific merge cell using Apache POI. Specific cell means, I write a text or image directly to B3:D7. The code below is manual per index and not specific name and number of cell. I want to put via cell name and number.

    ClientAnchor anchor = helper.createClientAnchor();

    //create an anchor with upper left cell _and_ bottom right cell
    anchor.setCol1(1); //Column B
    anchor.setRow1(2); //Row 3
    anchor.setCol2(2); //Column C
    anchor.setRow2(3); //Row 4
Joe
  • 37
  • 7

2 Answers2

1

You can use sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol)); for creating a merged region. In order to get the indexes you can use CellReference.convertColStringToIndex("B"); in order to get the index of the column. The index of the row is easy, it is simply the number - 1, e.g. for B3 the index is 2.

Example solution for D3:G16:

int firstRow = 2; // 3-1
int lastRow = 15; // 16-1
int firstCol = CellReference.convertColStringToIndex("D");
int lastCol = CellReference.convertColStringToIndex("G");
sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
Volokh
  • 380
  • 3
  • 16
1

here an example

public class MMM {
    static void mergeCells(XSSFSheet sheet, String cells) {
        String regex = "([A-Z]+)(\\d+):([A-Z]+)(\\d+)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(cells);

        if(matcher.matches()) {
            int col1 = CellReference.convertColStringToIndex(matcher.group(1));
            int col2 = CellReference.convertColStringToIndex(matcher.group(3));

            int row1 = Integer.parseInt(matcher.group(2)) - 1;
            int row2 = Integer.parseInt(matcher.group(4)) - 1;

            sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));
        }
    }

    public static void main(String[] args) throws IOException {
        OutputStream outputStream = new FileOutputStream("wwww2.xlsx");

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();

        mergeCells(sheet, "AAD10:ADD23");

        workbook.write(outputStream);
        outputStream.close();
    }
}
b.GHILAS
  • 2,273
  • 1
  • 8
  • 16
  • Can you put the whole code in here. Like for example Merge D3:G16. thanks. – Joe Nov 27 '19 at 10:25
  • Thanks for this. How about putting a style on the merge cell? for example, I will put a background color to it? thanks. – Joe Nov 27 '19 at 11:01
  • That's right, I just assumed that the column is a single character. You can use regex to split the cells addresses – b.GHILAS Nov 27 '19 at 11:34
  • THANKS MUCH. But, it should be subtracted by 1 for row 1 and row 2, to get the specific row. See below. int row1 = Integer.parseInt(matcher.group(2)) - 1; int row2 = Integer.parseInt(matcher.group(4)) - 1; – Joe Nov 27 '19 at 12:06
  • how about adding a new cell style like background color to the merge cells? Thanks. – Joe Nov 28 '19 at 06:03
  • Check this link https://stackoverflow.com/questions/11529542/changing-cell-color-using-apache-poi/11530150 – b.GHILAS Nov 28 '19 at 08:00