快捷導(dǎo)航

Java如何使用poi導(dǎo)入導(dǎo)出excel工具類

更新時(shí)間：2024年06月17日 09:46:17 作者：編程經(jīng)驗(yàn)分享

這篇文章主要介紹了Java如何使用poi導(dǎo)入導(dǎo)出excel工具類問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

問題

常規(guī) CRUD 項(xiàng)目中，會(huì)有導(dǎo)入 / 導(dǎo)出數(shù)據(jù)這兩個(gè)接口，一般使用 excel 來存儲(chǔ)數(shù)據(jù)，接口需要實(shí)現(xiàn)對(duì) excel 文件的讀寫

如何解決

基于 poi 實(shí)現(xiàn)對(duì) excel 文件的讀寫。

提取工具類，方便以后復(fù)用。

代碼

工具類

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;
            }
            // 循環(huán)處理每一行，會(huì)讀取到第一行
            int rows = sheet.getPhysicalNumberOfRows();
            int minCells = 0;
            int maxCells = 0;
            // 獲取最小列數(shù)和最大列數(shù)，以第一行為準(zhǔn)
            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);
        // 創(chuàng)建單元格，設(shè)置表頭
        int titleSize = title.size();
        for (int i = 0; i < titleSize; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(title.get(i));
        }
        // 寫入數(shù)據(jù)
        int dataSize = dataList.size();
        for (int i = 0; i < dataSize; i++) {
            Row row1 = sheet.createRow(i + 1);
            List<String> rowData = dataList.get(i);
            // 創(chuàng)建單元格設(shè)值
            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("目錄不存在，創(chuàng)建目錄");
        }
        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 表格行數(shù)據(jù)
     * @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:
                //數(shù)字
                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();
            // 單元格設(shè)置成常規(guī)
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
        }
 
        return result;
    }
 
    /**
     * 判斷指定的單元格是否是合并單元格
     * @param sheet 表格
     * @param row 行下標(biāo)
     * @param column 列下標(biāo)
     * @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 行下標(biāo)
     * @param column 列下標(biāo)
     * @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 ;
    }
}

測(cè)試類

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