Java 使用 poi 导入导出 excel 工具类

编程经验分享

已于 2024-03-25 21:56:45 修改

阅读量516

点赞数 16

分类专栏： # Java 文章标签： excel java

于 2024-03-09 00:15:00 首次发布

本文链接：https://blog.csdn.net/typeracer/article/details/136440825

版权

Java 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

问题

常规 CRUD 项目中，会有导入 / 导出数据这两个接口，一般使用 excel 来存储数据，接口需要实现对 excel 文件的读写

如何解决

基于 poi 实现对 excel 文件的读写。提取工具类，方便以后复用。

代码

工具类

public class ExcelUtil {

    public static List<List<String>> readExcel(InputStream inputStream , int sheetIndex) {
        List<List<String>> excelDataList = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(inputStream);
            if (workbook == null) {
                return excelDataList;
            }
            // 读取Sheet
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (sheet == null) {
                return excelDataList;
            }
            // 循环处理每一行，会读取到第一行
            int rows = sheet.getPhysicalNumberOfRows();
            int minCells = 0;
            int maxCells = 0;
            // 获取最小列数和最大列数，以第一行为准
            if (rows >= 1) {
                minCells = sheet.getRow(0).getFirstCellNum();
                maxCells = sheet.getRow(0).getLastCellNum();
            }

            for (int i = 0; i < rows; i++) {
                Row row = sheet.getRow(i);
                if(rowIsEmpty(row)) {
                    continue;
                }
                List<String> rowList = new ArrayList<>();
                for (int j = minCells; j < maxCells; j++) {
                    rowList.add(getCellValue(row.getCell(j)));
                }
                excelDataList.add(rowList);
            }
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return excelDataList;
    }

    public static boolean writeExcel(List<String> title, List<List<String>> dataList, String sheetName, String filePath){
        if (filePath == null || !filePath.contains(".")) {
            return false;
        }
        String suffix = filePath.substring(filePath.lastIndexOf(".") + 1);
        Workbook workbook;
        if ("xls".equals(suffix)) {
            workbook = new HSSFWorkbook();
        } else if ("xlsx".equals(suffix)) {
            workbook = new XSSFWorkbook();
        } else {
            return false;
        }
        Sheet sheet = workbook.createSheet(sheetName);
        Row row = sheet.createRow(0);
        // 创建单元格，设置表头
        int titleSize = title.size();
        for (int i = 0; i < titleSize; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(title.get(i));
        }
        // 写入数据
        int dataSize = dataList.size();
        for (int i = 0; i < dataSize; i++) {
            Row row1 = sheet.createRow(i + 1);
            List<String> rowData = dataList.get(i);
            // 创建单元格设值
            for (int j = 0; j < rowData.size(); j++) {
                row1.createCell(j).setCellValue(rowData.get(j));
            }
        }

        File file = new File(filePath);
        File parentFile = file.getParentFile();
        if (!parentFile.exists() && parentFile.mkdirs()) {
            System.out.println("目录不存在，创建目录");
        }
        try {
            workbook.write(Files.newOutputStream(file.toPath()));
            workbook.close();
            return true;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 检查文件格式
     * @param fileName 文件名
     * @return boolean false：不是excel文件 true：是excel文件
     */
    public static boolean checkFile(String fileName) {
        return fileName != null && (fileName.endsWith("xls") || fileName.endsWith("xlsx"));
    }

    /**
     * 判断excel的row是否全为空
     * @param row 表格行数据
     * @return true or false
     */
    public static boolean rowIsEmpty(Row row) {
        if (null == row) {
            return true;
        }
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 获取单元格的值
     *
     * @param cell 单元格
     * @return 单元格的值
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:
                //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                //公式
                System.out.println(cell.getCellFormula());
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case BLANK:
                //空值
                cellValue = "";
                break;
            case ERROR:
                //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    public static String stringDateProcess(Cell cell) {
        String result;
        if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil
                    .getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }

        return result;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet 表格
     * @param row 行下标
     * @param column 列下标
     * @return true or false
     */
    public static  boolean isMergedRegion(Sheet sheet, int row , int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     * @param sheet 表格
     * @param row 行下标
     * @param column 列下标
     * @return String
     */
    public static String getMergedRegionValue(Sheet sheet , int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }
}

测试类

class ExcelUtilTest {

    @Test
    void readExcel() {
        String filePath = "C:\\Users\\XXX\\Desktop\\excel.xlsx";
        if (ExcelUtil.checkFile(filePath)) {
            try (InputStream inputStream = Files.newInputStream(Paths.get(filePath))) {
                List<List<String>> dataList = ExcelUtil.readExcel(inputStream, 0);

                for (List<String> rows : dataList) {
                    System.out.println(rows);
                }
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

    }

    @Test
    void writeExcel() {
        List<String> title = new ArrayList<>();
        title.add("colum1");

        List<List<String>> dataList = new ArrayList<>();
        List<String> data = new ArrayList<>();
        data.add("abc");
        dataList.add(data);

        String sheetName = "sheet1";

        String filePath  = "C:\\Users\\XXX\\Desktop\\excel.xlsx";

        ExcelUtil.writeExcel(title, dataList, sheetName, filePath);
    }
}