如何使用POI SS / XSSF转置工作表？

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

我正在使用POI XSSF API，我想转置一张表。

how can I do that?

我怎样才能做到这一点？

Thanks.

谢谢。

2 个解决方案

#1

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

转置，如交换A2与B1和A3与C1（所以列成为行）？

If so, there's nothing built in, so you'd need to do a little bit of coding yourself. You'd likely want to grab a pair of cells, save the contents of one (value and style), copy the second to the first, then overwrite the second.

如果是这样，那里面没有任何东西，所以你需要自己编写一些代码。您可能想要抓住一对单元格，保存一个单元格（值和样式），将第二个单元格复制到第一个单元格，然后覆盖第二个单元格。

See the quick guide if you're not sure on all the reading/writing parts.

如果您不确定所有读/写部件，请参阅快速指南。

#2

I was looking for the same answer and had to code it myself. I've attached my solution which is quite simple:

我正在寻找相同的答案，不得不自己编写代码。我附上了我的解决方案，非常简单：

1. Determine the number of rows
2. 确定行数
3. Determine the maximal number of columns used
4. 确定使用的最大列数
5. Iterator over every row, and every column
6. 遍及每一行和每一列的迭代器
7. Save the Cell from that row/column into a simple list as a 'CellModel' type
8. 将该行/列中的单元格保存为“CellModel”类型的简单列表
9. Once done, iterate over all CellModels
10. 完成后，迭代所有CellModel
11. Switch column and row index and save the CellModel into the sheet
12. 切换列和行索引并将CellModel保存到工作表中

The code I've used is:

我使用的代码是：

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

Whereby the CellModel is a wrapper which holds the data a Cell contained (you can add more attributes if you like e.g., comments, ...):

因此，CellModel是一个包装器，它包含一个包含Cell的数据（如果您喜欢，可以添加更多属性，例如，注释......）：

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

}