How to transfer sheet from POI SS / XSSF?

Question

I am using the XSSF POI API and I would like to transpose the sheet.

How can i do this?

Thank.

Answer 1

Transpose, as in swap A2 with B1 and A3 with C1 (so columns become rows)?

If so, there is nothing built in, so you need to work a bit on coding. You probably want to grab a couple of cells, save the contents of one (value and style), copy the second to the first, and then overwrite the second.

See the quick start guide if you are unsure of all parts of the read / write.

Answer 2

. , :

• ,
• / "CellModel"
• , CellModels
• CellModel .

:

public static void transpose(Workbook wb, int sheetNum, boolean replaceOriginalSheet) {
    Sheet sheet = wb.getSheetAt(sheetNum);

    Pair<Integer, Integer> lastRowColumn = getLastRowAndLastColumn(sheet);
    int lastRow = lastRowColumn.getFirst();
    int lastColumn = lastRowColumn.getSecond();

    LOG.debug("Sheet {} has {} rows and {} columns, transposing ...", new Object[] {sheet.getSheetName(), 1+lastRow, lastColumn});

    List<CellModel> allCells = new ArrayList<CellModel>();
    for (int rowNum = 0; rowNum <= lastRow; rowNum++) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }
        for (int columnNum = 0; columnNum < lastColumn; columnNum++) {
            Cell cell = row.getCell(columnNum);
            allCells.add(new CellModel(cell));
        }
    }
    LOG.debug("Read {} cells ... transposing them", allCells.size());

    Sheet tSheet = wb.createSheet(sheet.getSheetName() + "_transposed");
    for (CellModel cm : allCells) {
        if (cm.isBlank()) {
            continue;
        }

        int tRow = cm.getColNum();
        int tColumn = cm.getRowNum();

        Row row = tSheet.getRow(tRow);
        if (row == null) {
            row = tSheet.createRow(tRow);
        }

        Cell cell = row.createCell(tColumn);
        cm.insertInto(cell);
    }

    lastRowColumn = getLastRowAndLastColumn(sheet);
    lastRow = lastRowColumn.getFirst();
    lastColumn = lastRowColumn.getSecond();
    LOG.debug("Transposing done. {} now has {} rows and {} columns.", new Object[] {tSheet.getSheetName(), 1+lastRow, lastColumn});

    if (replaceOriginalSheet) {
        int pos = wb.getSheetIndex(sheet);
        wb.removeSheetAt(pos);
        wb.setSheetOrder(tSheet.getSheetName(), pos);
    }

}

private static Pair<Integer, Integer> getLastRowAndLastColumn(Sheet sheet) {
    int lastRow = sheet.getLastRowNum();
    int lastColumn = 0;
    for (Row row : sheet) {
        if (lastColumn < row.getLastCellNum()) {
            lastColumn = row.getLastCellNum();
        }
    }
    return new Pair<Integer, Integer>(lastRow, lastColumn);
}

CellModel , , ( , , , ,...):

static class CellModel {
    private int rowNum = -1;
    private int colNum = -1;
    private CellStyle cellStyle;
    private int cellType = -1;
    private Object cellValue;

    public CellModel(Cell cell) {
        if (cell != null) {
            this.rowNum = cell.getRowIndex();
            this.colNum = cell.getColumnIndex();
            this.cellStyle = cell.getCellStyle();
            this.cellType = cell.getCellType();
            switch (this.cellType) {
                case Cell.CELL_TYPE_BLANK:
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellValue = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    cellValue = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    cellValue = cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getRichStringCellValue();
                    break;
            }
        }
    }

    public boolean isBlank() {
        return this.cellType == -1 && this.rowNum == -1 && this.colNum == -1;
    }

    public void insertInto(Cell cell) {
        if (isBlank()) {
            return;
        }

        cell.setCellStyle(this.cellStyle);
        cell.setCellType(this.cellType);
        switch (this.cellType) {
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cell.setCellValue((boolean) this.cellValue);
                break;
            case Cell.CELL_TYPE_ERROR:
                cell.setCellErrorValue((byte) this.cellValue);
                break;
            case Cell.CELL_TYPE_FORMULA:
                cell.setCellFormula((String) this.cellValue);
                break;
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellValue((double) this.cellValue);
                break;
            case Cell.CELL_TYPE_STRING:
                cell.setCellValue((RichTextString) this.cellValue);
                break;
        }
    }

    public CellStyle getCellStyle() {
        return cellStyle;
    }

    public int getCellType() {
        return cellType;
    }

    public Object getCellValue() {
        return cellValue;
    }

    public int getRowNum() {
        return rowNum;
    }

    public int getColNum() {
        return colNum;
    }

}