java - 在 POI 中复制 Excel 工作表

Question

有人知道使用 POI 将工作表从一个工作簿复制到另一个工作簿的方法吗？ Workbook 类有 cloneSheet 方法，但是好像不能将克隆的工作表插入到新的工作簿中？

如果没有 API 可以轻松做到这一点，是否有人拥有将所有数据(样式、列宽、数据等)从一张纸复制到另一张纸的代码？

jxls 有复制工作表的方法，但是在工作簿之间复制时它们不起作用。

Answer 1

我已经用 poi 实现了一些功能。请参阅代码供您引用。

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelReadAndWrite {

    public static void main(String[] args) throws IOException {
        ExcelReadAndWrite excel = new ExcelReadAndWrite();
        excel.process("D:/LNN/My Workspace/POI/src/tables.xls");
    }

    public void process(String fileName) throws IOException {
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(fileName));
        HSSFWorkbook workbook = new HSSFWorkbook(bis);
        HSSFWorkbook myWorkBook = new HSSFWorkbook();
        HSSFSheet sheet = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        HSSFSheet mySheet = null;
        HSSFRow myRow = null;
        HSSFCell myCell = null;
        int sheets = workbook.getNumberOfSheets();
        int fCell = 0;
        int lCell = 0;
        int fRow = 0;
        int lRow = 0;
        for (int iSheet = 0; iSheet < sheets; iSheet++) {
            sheet = workbook.getSheetAt(iSheet);
            if (sheet != null) {
                mySheet = myWorkBook.createSheet(sheet.getSheetName());
                fRow = sheet.getFirstRowNum();
                lRow = sheet.getLastRowNum();
                for (int iRow = fRow; iRow <= lRow; iRow++) {
                    row = sheet.getRow(iRow);
                    myRow = mySheet.createRow(iRow);
                    if (row != null) {
                        fCell = row.getFirstCellNum();
                        lCell = row.getLastCellNum();
                        for (int iCell = fCell; iCell < lCell; iCell++) {
                            cell = row.getCell(iCell);
                            myCell = myRow.createCell(iCell);
                            if (cell != null) {
                                myCell.setCellType(cell.getCellType());
                                switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_BLANK:
                                    myCell.setCellValue("");
                                    break;

                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    myCell.setCellValue(cell.getBooleanCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR:
                                    myCell.setCellErrorValue(cell.getErrorCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_FORMULA:
                                    myCell.setCellFormula(cell.getCellFormula());
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    myCell.setCellValue(cell.getNumericCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_STRING:
                                    myCell.setCellValue(cell.getStringCellValue());
                                    break;
                                default:
                                    myCell.setCellFormula(cell.getCellFormula());
                                }
                            }
                        }
                    }
                }
            }
        }
        bis.close();
        BufferedOutputStream bos = new BufferedOutputStream(
                new FileOutputStream("workbook.xls", true));
        myWorkBook.write(bos);
        bos.close();
    }
}