java 对Excel 操作 例子(上)

需求：一个excel附件模板：我需要拿到这个文件对其做处理，做修改，修改完后把这个修改后的附件发送。

需要的依赖包

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

• 本地例子测试：简单实现对excel修改保存到本地

 工具类：

ExcelUtil

public class ExcelUtil {

    // 本地调试
    public static void main(String[] args) throws IOException {
        readTemplate("C:\\Users\\悲伤敌敌畏\\Desktop\\测试导入数据.xlsx");
    }

    /**
     * 读取并但打印模板中数据的单元格位置，调试的时候使用
     *
     * @param filePath
     */
    public static void readTemplate(String filePath) {
        // 解析工作簿
        Workbook workbook = getWorkbook(new File(filePath));
        // 获取工作表的数量
        int size = workbook.getNumberOfSheets();
        System.out.println("工作表数量： " + size);
        // 循环读取每一个工作表中的数据
        for (int i = 0; i < size; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 工作表
            System.out.println("===================== 工作表： " + sheet.getSheetName() + " =====================");
            // 获取有效的行数
            int rowNumber = sheet.getPhysicalNumberOfRows();
            for (int rowIndex = 0; rowIndex < rowNumber; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    //循环读取每一列的数据
                    for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
                        if (cell != null) {
                            switch (cell.getCellTypeEnum()) {
                                case STRING:
                                    System.out.println(String.format("(%d,%d) 列的值： %s", rowIndex, cellIndex, cell.getStringCellValue()));
                                    break;
                                case NUMERIC:
                                    System.out.println(String.format("(%d,%d) 列的值： %s", rowIndex, cellIndex, String.valueOf(cell.getNumericCellValue())));
                                    break;
                                case BLANK:
                                case _NONE:
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                }
            }
        }
    }

    /**
     * 获取一行 没有则创建
     *
     * @param sheet
     * @param rowIndex
     * @return
     */
    public static Row getRow(Sheet sheet, int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        return row == null ? sheet.createRow(rowIndex) : row;
    }

    /**
     * 获取单元格
     *
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @return
     */
    public static Cell getCell(Sheet sheet, int rowNum, int columnNum) {
        Row row = getRow(sheet, rowNum);
        if (row != null) {
            return getCell(row, columnNum);
        }
        return null;
    }

    /**
     * 获取单元格
     *
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @param cellStyle 单元格样式
     * @return
     */
    public static Cell getCell(Sheet sheet, int rowNum, int columnNum, CellStyle cellStyle) {
        Cell cell = getCell(sheet, rowNum, columnNum);
        if (cell != null) {
            cell.setCellStyle(cellStyle);
            return cell;
        }
        return null;
    }

    /**
     * 获取单元格
     *
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @param font      字体
     * @return
     */
    public static Cell getCell(Sheet sheet, int rowNum, int columnNum, Font font) {
        Cell cell = getCell(sheet, rowNum, columnNum);
        if (cell != null) {
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle == null) {
                cellStyle = sheet.getWorkbook().createCellStyle();
            }
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            return cell;
        }
        return null;
    }

    /**
     * 获取一个单元格 没有则创建
     *
     * @param row
     * @param cellIndex
     * @return
     */
    public static Cell getCell(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        return cell == null ? row.createCell(cellIndex) : cell;
    }


    /**
     * 读取单元格的值
     *
     * @param cell 单元格
     * @return
     */
    public static String getCellValue(Cell cell) {
        Object value = null;
        if (cell != null) {
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    value = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    short format = cell.getCellStyle().getDataFormat();
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf;
                        if (format == 20 || format == 32) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        } else {
                            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        }
                        try {
                            value = sdf.format(cell.getDateCellValue());
                        } catch (Exception e) {
                            e.printStackTrace();
                            value = null;
                        }
                    } else {
                        value = cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    value = cell.getCellFormula();
                    break;
                case ERROR:
                    value = cell.getErrorCellValue();
                    break;
                case BLANK:
                case _NONE:
                    break;
                default:
                    break;
            }
        }
        return value == null ? "" : value.toString();
    }

    /**
     * 获取 Excel 文件类型
     *
     * @param inputStream
     * @return
     * @throws Exception
     */
    public static ExcelTypeEnum getExcelType(InputStream inputStream) throws IOException {
        BufferedInputStream bufferedInputStream;
        if (!(inputStream instanceof BufferedInputStream)) {
            bufferedInputStream = new BufferedInputStream(inputStream);
        } else {
            bufferedInputStream = (BufferedInputStream) inputStream;
        }
        try {
            FileMagic fileMagic = FileMagic.valueOf(bufferedInputStream);
            if (FileMagic.OLE2.equals(fileMagic)) {
                return ExcelTypeEnum.XLS;
            }
            if (FileMagic.OOXML.equals(fileMagic)) {
                return ExcelTypeEnum.XLSX;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        throw new ExcelCommonException("不支持的文件类型");
    }

    /**
     * 获取 Workbook
     *
     * @param fileName
     * @return
     */
    public static Workbook getWorkbook(String fileName) {
        return getWorkbook(new File(fileName));
    }

    /**
     * 获取 Workbook
     *
     * @param file
     * @return
     */
    public static Workbook getWorkbook(File file) {
        Workbook workbook;
        try {
            ExcelTypeEnum excelType = ExcelUtil.getExcelType(new FileInputStream(file));
            if (ExcelTypeEnum.XLS == excelType) {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            } else {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ExcelCommonException("读取 Excel 文件失败");
        }
        return workbook;
    }

}

本地测试：

    /**
     * 获取 checkbox 框框字体
     *
     * @param workbook
     * @return
     */
    private static Font getBlockFont(Workbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        return font;
    }

    /**
     * 设置单元格颜色
     */
    private static CellStyle getColorFont(Workbook workbook) {
        CellStyle redStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        redStyle.setFont(font);
        return redStyle;
    }

    //测试把一个 excel文件做修改后 再写到一个新的文件中去
    @Test
    public void aVoid() throws IOException {
        User user = userService.getById(1);
        Workbook workbook = ExcelUtil.getWorkbook("C:\\Users\\悲伤敌敌畏\\Desktop\\测试导入数据.xlsx");
        Sheet sheet = workbook.getSheetAt(0);
        ExcelUtil.getCell(sheet,0,5).setCellValue("我是测试1号");
        ExcelUtil.getCell(sheet,0,6).setCellValue("我是测试2号");
        ExcelUtil.getCell(sheet,0,7).setCellValue("我是测试3号");
        ExcelUtil.getCell(sheet,1,0,getColorFont(sheet.getWorkbook())).setCellValue("■A规格变更");
        ExcelUtil.getCell(sheet,1,10,getColorFont(sheet.getWorkbook())).setCellValue(user.getName());

        //把导出到本地 (excel)
        FileOutputStream fileOutputStream = null;
        try {
            // 保存到目标路径
            fileOutputStream = new FileOutputStream("C:\\Users\\悲伤敌敌畏\\Desktop\\修改后导出.xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(fileOutputStream != null){
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }