poi读写xls和xlsx

首先读取xls和xlsx文档的所有内容。

XSSFWorkbook是读取xlsx文档07

HSSFWorkbook是读取xls文档03

    public static void main(String[] args) throws IOException {
        Resource resource = new ClassPathResource("excelTemplate/risk_1.xlsx");
//        Workbook workbook = new XSSFWorkbook(resource.getInputStream());
        Workbook workbook = new HSSFWorkbook(new FileInputStream(new File("E:\\desktop\\risk_1.xls")));
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                Row row = sheet.getRow(j);
                for (int k = 0; k < row.getLastCellNum(); k++) {
                    System.out.println("sheet_"+i+"_row_"+j+"_cell_"+"_"+k+row.getCell(k) + "\t");
                }
            }
        }
    }

 复制粘贴文档的行，xls和xlsx都可用

package com.sxygsj.util.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author ：llf
 * @date ：Created in 2020-05-13 9:23
 * @description：${description}
 * @version: v1.0
 */
public class ReadAndCreateExcel {

    public static void copyRows(int startRow, int endRow, int pPosition, Sheet sheet) {
        int pStartRow = startRow - 1;
        int pEndRow = endRow - 1;
        int targetRowFrom;
        int targetRowTo;
        int columnCount;
        CellRangeAddress region = null;
        int i;
        int j;
        if (pStartRow == -1 || pEndRow == -1) {
            return;
        }
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
            region = sheet.getMergedRegion(i);
            if ((region.getFirstRow() >= pStartRow)
                    && (region.getLastRow() <= pEndRow)) {
                targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
                targetRowTo = region.getLastRow() - pStartRow + pPosition;
                CellRangeAddress newRegion = region.copy();
                newRegion.setFirstRow(targetRowFrom);
                newRegion.setFirstColumn(region.getFirstColumn());
                newRegion.setLastRow(targetRowTo);
                newRegion.setLastColumn(region.getLastColumn());
                sheet.addMergedRegion(newRegion);
            }
        }
        for (i = pStartRow; i <= pEndRow; i++) {
            Row sourceRow = sheet.getRow(i);
            columnCount = sourceRow.getLastCellNum();
            if (sourceRow != null) {
                Row newRow = sheet.createRow(pPosition - pStartRow + i);
                newRow.setHeight(sourceRow.getHeight());
                for (j = 0; j < columnCount; j++) {
                    Cell templateCell = sourceRow.getCell(j);
                    if (templateCell != null) {
                        Cell newCell = newRow.createCell(j);
                        copyCell(templateCell, newCell);
                    }
                }
            }
        }
    }

    private static void copyCell(Cell srcCell, Cell distCell) {
        distCell.setCellStyle(srcCell.getCellStyle());
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            } else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        } else if (srcCellType == Cell.CELL_TYPE_STRING) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
        } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
            distCell.setCellFormula(srcCell.getCellFormula());
        } else { 
        }
    }

}

 实战代码示例：

读取文档模板导出多行数据

    public static Workbook shenheRisk(List<Map<String,Object>> map,String path) throws IOException {
        Resource resource = new ClassPathResource(path);
        Workbook workbook = new XSSFWorkbook(resource.getInputStream());
        int count = 5;
        int rcount = 4;
        for(int i = 0; i < map.size();i++){
            if(i != map.size()-1){
                ReadAndCreateExcel.copyRows(5,5,count,((XSSFWorkbook) workbook).getSheetAt(0));
            }
            Row row = workbook.getSheetAt(0).getRow(rcount);
            if(row != null){
                row.getCell(0).setCellValue(map.get(i).get("rname").toString());
                row.getCell(1).setCellValue(map.get(i).get("rdescription").toString());
                row.getCell(2).setCellValue(map.get(i).get("rtype").toString());
                row.getCell(3).setCellValue(map.get(i).get("rcategory").toString());
                row.getCell(4).setCellValue(map.get(i).get("rstarttime").toString());
                row.getCell(5).setCellValue(map.get(i).get("rendtime").toString());
                row.getCell(6).setCellValue(map.get(i).get("result").toString());
                row.getCell(7).setCellValue(map.get(i).get("grade").toString());
                row.getCell(8).setCellValue(map.get(i).get("gkobject").toString());
                row.getCell(9).setCellValue(map.get(i).get("fgname").toString());
                row.getCell(10).setCellValue(map.get(i).get("contrlname").toString());
                row.getCell(11).setCellValue(map.get(i).get("glbz").toString());
                row.getCell(12).setCellValue(map.get(i).get("glcs").toString());
                row.getCell(13).setCellValue(map.get(i).get("bcglcs").toString());
                row.getCell(14).setCellValue(map.get(i).get("method").toString());
                row.getCell(15).setCellValue(map.get(i).get("rmay").toString());
                row.getCell(16).setCellValue(map.get(i).get("rloss").toString());
                row.getCell(17).setCellValue(map.get(i).get("rgrade").toString());
                row.getCell(18).setCellValue(map.get(i).get("rgradevalue").toString());
                row.getCell(19).setCellValue(map.get(i).get("acreatename").toString());
                row.getCell(20).setCellValue(map.get(i).get("acreatetime").toString());
                row.getCell(21).setCellValue(map.get(i).get("updatename").toString());
                row.getCell(22).setCellValue(map.get(i).get("updatetime").toString());
                row.getCell(23).setCellValue(map.get(i).get("createname").toString());
                row.getCell(24).setCellValue(map.get(i).get("createtime").toString());
            }
            rcount++;
            count++;
        }
        return workbook;
    }