CODEWITHSUNDEEP
javaexcelapache-poi
Joe
Joe

Reputation: 37

Write to specific cell in excel in java

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

Upvotes: 0

Views: 2232

Answers (2)

b.GHILAS
b.GHILAS

Reputation: 2313

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();
    }
}

Upvotes: 1

Volokh
Volokh

Reputation: 390

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));

Upvotes: 1

Related Questions