Java POI通用导入导出Excel

深碍√TFBOYSˉ_ 2023-07-17 04:40 9阅读 0赞

Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

POI从版本4.0.1开始，需要Java 8或更高版本。按照需要导入jar包及相关依赖jar包。**官方对架包和API有详细的介绍**

poi-xx.jar  (excel文件生成需要)

poi-examples-xx.jar(官方示例，开发不需要)

poi-excelant-xx.jar(不需要)

poi-ooxml-xx.jar(excel,word,ppt均需要)

poi-ooxml-schemas-xx.jar(excel需要)

poi-scratchpad-xx.jar(ppt,vsd,word,viso,outlook等需要)

**官方地址：[https://poi.apache.org/overview.html][https_poi.apache.org_overview.html]**

**API：[https://poi.apache.org/apidocs/4.1/][https_poi.apache.org_apidocs_4.1]**

**这里POI使用 4.1.2版本，简单实现POI通用导入导出Excel（工具类使用接口对象），针对一个sheet的处理，多个sheet同理**

创建Springboot项目，引入POI依赖：

<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>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.1.2</version>
            </dependency>

建表，这里主键自增

CREATE TABLE `t_poi_entity` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `sex` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `height` double DEFAULT NULL,
      `birthday` date DEFAULT NULL,
      `intro` text,
      `createtime` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 一、下面实例用到的EXCEL知识点 ###

**1、POI结构说明**

HSSFWorkbook类提供读写 Microsoft Excel XLS 格式的excel功能。

XSSFWorkbook类提供读写 Microsoft Excel OOXML XLSX 格式的excel功能。

**2、POI常用类说明**

Sheet 工作表是电子表格的中心结构，用户在其中完成大部分电子表格工作。对row，合并拆分单元格，设置列宽，获取画笔，页眉页脚等操作

Row 电子表格中的行。对cell，行高，行索引，行样式等操作

Cell 单元格。单元格可以包含文本、数字、日期、链接和公式等。对单元格内容的类型，内容，cell索引，cell样式等操作

Font 字体。对单元格中内容的字体，大小，颜色，加粗，下划线等操作

CellStyle 单元格样式。对边框，字体，对齐方式，背景色等操作

### 二、导入excel ###

俩分excel数据差不多

![20200320114806391.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70][]

**1、controller**

@GetMapping("/importExcel")
        @ResponseBody
        public String importExcel(String filePath) {
            File file = new File(filePath);
            if (ImportExcelUtil.isExcel(file.getName())) {
                String reslut = poiSerivce.importExcel(file);
                return "importExcel--" + reslut;
            } else {
                return "importExcel-- 文件类型与模板不符！";
            }
        }

**2、service**

public String importExcel(File file) {
            // poi_xlsx.xlsx
            String fileName = file.getName();
            List<String[]> dataList = null;
            String headData = null;
            Workbook workbook = null;
            String dateFormat = "yyyy-MM-dd";
            try {
                if (ImportExcelUtil.isExcel2007(fileName)) {
                    workbook = new XSSFWorkbook(new FileInputStream(file));
                } else if (ImportExcelUtil.isExcel2003(fileName)) {
                    workbook = new HSSFWorkbook(new FileInputStream(file));
                }
                if (workbook != null) {
                    headData = ImportExcelUtil.getExcelHeadData(workbook);
                    dataList = ImportExcelUtil.getExcelData(workbook, 1, 2, dateFormat);
                }
                //拿到数据进行数据库操作
                System.out.println(headData);
                SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
                Optional.ofNullable(dataList).orElse(new ArrayList<>()).forEach(strArr -> {
                    PoiEntityDO entity = new PoiEntityDO();
                    for (int i = 0; i < strArr.length; i++) {
                        String cellValue = strArr[i];
                        switch (i) {
                            case 0:
                                entity.setName(cellValue);
                                break;
                            case 1:
                                entity.setSex(cellValue);
                                break;
                            case 2:
                                entity.setAge(Integer.valueOf(cellValue));
                                break;
                            case 3:
                                entity.setHeight(Double.valueOf(cellValue));
                                break;
                            case 4:
                                try {
                                    entity.setBirthday(sdf.parse(cellValue));
                                } catch (ParseException e) {
                                    e.printStackTrace();
                                }
                                break;
                            case 5:
                                entity.setIntro(cellValue);
                                break;
                            default:
                                break;
                        }
                    }
                    entity.setCreatetime(new Date());
                    poiEntityDOMapper.insert(entity);
                });
                return "success";
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (workbook != null) {
                    try {
                        workbook.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
            return "false";
        }

**3、工具类**

import org.apache.poi.ss.usermodel.*;
    
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    
    public class ImportExcelUtil {
        private ImportExcelUtil() {};
    
        /**
         * 读取Excel表头首行信息
         *
         * @param workbook
         * @return 首行表头信息
         */
        public static String getExcelHeadData(Workbook workbook) {
            String headValue;
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(sheet.getFirstRowNum());
            headValue = getCellValue(row.getCell(0), null);
            return headValue;
        }
    
        /**
         * 读取Excel数据
         *
         * @param workbook
         * @param fieldRow     - 字段所在行，
         * @param dataStartRow - 数据开始行
         * @param dateFormat   - 日期类型处理的格式
         * @return 数据集合
         */
        public static List<String[]> getExcelData(Workbook workbook, int fieldRow, int dataStartRow, String dateFormat) {
            List<String[]> dataList = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(0);
            // 获取总行数(不包含合计行)
            int rowNum = sheet.getLastRowNum();
            // 根据字段的行获取总列数
            int colNum = sheet.getRow(fieldRow).getPhysicalNumberOfCells();
            // 从数据开始行读取excel数据
            for (int i = dataStartRow; i <= rowNum; i++) {
                String[] rowArr = new String[colNum];
                Row row = sheet.getRow(i);
                for (int j = 0; j < colNum; j++) {
                    Cell cell = row.getCell(j);
                    rowArr[j] = getCellValue(cell, dateFormat);
                }
                dataList.add(rowArr);
            }
            return dataList;
        }
    
        /**
         * 获取单元格的值，返回String
         *
         * @param cell
         * @param dateFormat 日期的格式
         * @return 返回String
         */
        private static String getCellValue(Cell cell, String dateFormat) {
            String cellValue = null;
            if (cell == null) {
                return cellValue;
            }
            SimpleDateFormat sdf = new SimpleDateFormat((dateFormat == null || "".equals(dateFormat.trim()) ? "yyyy-MM-dd" : dateFormat));
            DecimalFormat df = new DecimalFormat("#0.####################");
            switch (cell.getCellType()) {
                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case NUMERIC: // 数值，货币，日期，时间等都是这种格式，在此要进行判断
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = sdf.format(cell.getDateCellValue());
                    } else {
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN: // Boolean
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA: // 公式
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case BLANK: // 空值
                    cellValue = null;
                    break;
                default:
                    cellValue = "";
                    break;
            }
            return cellValue;
        }
    
        /**
         * 是否是excel
         *
         * @param filePath 文件名或者文件路径
         * @return true-是, fasle-否
         */
        public static boolean isExcel(String filePath) {
            return filePath.matches("^.+\\.(?i)(xlsx|xls)$");
        }
    
        /**
         * 是否是2003的excel
         *
         * @param filePath - 文件名或者文件路径
         * @return true-是, fasle-否
         */
        public static boolean isExcel2003(String filePath) {
            return filePath.matches("^.+\\.(?i)(xls)$");
        }
    
        /**
         * 是否是2007的excel
         *
         * @param filePath -文件名或者文件路径
         * @return true-是, fasle-否
         */
        public static boolean isExcel2007(String filePath) {
            // 以任意个字符开头，用.分割，最后的xlsx不区分大小写。
            return filePath.matches("^.+\\.(?i)(xlsx)$");
        }
    
    }

### **三、导出excel** ###

**    **![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 1][]

**注意：填充sheet数据时，这里使用 map通过字段名取获取值**

<select id="selectByExport" resultType="java.util.Map">
        select name, sex, age, height, DATE_FORMAT(birthday,'%Y-%m-%d') birthday, DATE_FORMAT(createtime,'%Y-%m-%d %H:%i:%s') createtime, intro
        from t_poi_entity
      </select>

**1、controller**

@RequestMapping(value="/exportXLSX")
        public ResponseEntity<byte[]> exportExcel() throws Exception{
            String fileName = "导出xlsx数据.xlsx";
            HttpHeaders headers = new HttpHeaders();
            //下载显示的文件名，并解决中文名称乱码问题
            String downloadFileName = new String(fileName.getBytes(StandardCharsets.UTF_8.name()),StandardCharsets.ISO_8859_1.name());
            //通知浏览器以attachment（下载方式）打开
            headers.setContentDispositionFormData("attachment", downloadFileName);
            //applicatin/octet-stream: 二进制流数据（最常见的文件下载）
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            byte[] byteArr = poiSerivce.exportExcel(fileName);
            return new ResponseEntity<byte[]>(byteArr, headers, HttpStatus.CREATED);
        }

**2、service**

public byte[] exportExcel(String fileName) {
            ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
            List<Map<String, Object>> exportDataList = poiEntityDOMapper.selectByExport();
            String head = "2020-3-20人员信息登记表";
            String[] fieldNameCNArr = new String[]{"姓名", "性别", "年龄", "身高（m）", "出生日期", "创建时间", "简介"};
            String[] fieldNameENArr = new String[]{"name", "sex", "age", "height", "birthday", "createtime", "intro"};
    
            Workbook workbook = null;
            try {
                if (ImportExcelUtil.isExcel2007(fileName)) {
                    workbook = new XSSFWorkbook();
                } else if (ImportExcelUtil.isExcel2003(fileName)) {
                    workbook = new HSSFWorkbook();
                }
                //创建一张excel表
                Sheet sheet = workbook.createSheet("sheet1");
                // 初始化列的宽度
                for (int i = 0; i < fieldNameCNArr.length; i++) {
                    switch (i) {
                        case 4:
                            ExportExcelUtil.createColumnWidth(sheet, i, 20);
                            break;
                        case 5:
                            ExportExcelUtil.createColumnWidth(sheet, i, 20);
                            break;
                        case 6:
                            ExportExcelUtil.createColumnWidth(sheet, i, 30);
                            break;
                        default:
                            ExportExcelUtil.createColumnWidth(sheet, i, 10);
                            break;
                    }
                }
    
                //创建并初始化表头
                int headStartRow = 0;
                CellStyle style = ExportExcelUtil.createCellStyle(workbook, "宋体", true, (short) 18, Font.COLOR_NORMAL);
                ExportExcelUtil.setCellStyle(style, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, false);
                ExportExcelUtil.creatSheetHead(sheet, style, head, headStartRow, 0, 0, 0, 6);
                //创建并初始化字段行
                int feildStartRow = 1;
                Row fieldRow = sheet.createRow(feildStartRow);
                style = ExportExcelUtil.createCellStyle(workbook, "宋体", true, (short) 14, Font.COLOR_NORMAL);
                ExportExcelUtil.setCellStyle(style, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, true);
                for (int i = 0; i < fieldNameCNArr.length; i++) {
                    Cell cell = fieldRow.createCell(i);
                    cell.setCellStyle(style);
                    cell.setCellValue(fieldNameCNArr[i]);
                }
    
                //从数据开始行创建并初始化数据单元格
                int dataStartRow = 2;
                style = ExportExcelUtil.createCellStyle(workbook, "宋体", false, (short) 12, Font.COLOR_NORMAL);
                ExportExcelUtil.setCellStyle(style, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, true);
                ExportExcelUtil.fillSheetData(sheet, style, dataStartRow, exportDataList, fieldNameENArr);
                workbook.write(byteOut);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (workbook != null) {
                    try {
                        workbook.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
            return byteOut.toByteArray();
        }

**3、工具类**

import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    import java.util.Optional;
    
    
    public class ExportExcelUtil {
    
        /**
         * 创建表头
         *
         * @param sheet
         * @param style        - 表头样式
         * @param head         - 表头内容
         * @param headStartRow - 表头开始行，从0开始
         * @param firstRow     - 合并开始行
         * @param lastRow      - 合并结束行
         * @param firstCol     - 合并开始列
         * @param lastCol      - 合并结束列
         */
        public static void creatSheetHead(Sheet sheet, CellStyle style, String head, int headStartRow, int firstRow, int lastRow, int firstCol, int lastCol) {
            Row row = sheet.createRow(headStartRow);
            Cell cell = row.createCell(headStartRow);
            cell.setCellValue(head);
            cell.setCellStyle(style);
            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }
    
        /**
         * 创建单元格样式 - 默认左对齐
         *
         * @param workbook
         * @param name     - 字体名称
         * @param bold     - 是否加粗
         * @param height   - 字体大小
         * @param color    - 字体颜色
         * @return
         */
        public static CellStyle createCellStyle(Workbook workbook, String name, boolean bold, short height, short color) {
            CellStyle style = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontName(name);
            font.setBold(bold);
            font.setColor(color);
            font.setFontHeightInPoints(height);
            //斜体
            font.setItalic(false);
            style.setFont(font);
            return style;
        }
    
        /**
         * 设置单元格样式 - 对齐方式和边框
         *
         * @param style
         * @param halign - 水平对齐
         * @param valign - 垂直对齐
         * @param border - true-四边框，false-无边框
         * @return
         */
        public static CellStyle setCellStyle(CellStyle style, HorizontalAlignment halign, VerticalAlignment valign, boolean border) {
            style.setAlignment(halign);
            style.setVerticalAlignment(valign);
            if (border) {
                style.setBorderTop(BorderStyle.THIN);
                style.setBorderBottom(BorderStyle.THIN);
                style.setBorderLeft(BorderStyle.THIN);
                style.setBorderRight(BorderStyle.THIN);
            }
            return style;
        }
    
        /**
         * 设置列宽
         *
         * @param sheet
         * @param columnIndex - 从0开始
         * @param width       - 行宽
         * @return
         */
        public static void createColumnWidth(Sheet sheet, int columnIndex, int width) {
            // 256 * width
            sheet.setColumnWidth(columnIndex, 256 * width + 184);
        }
    
        /**
         * 填充sheet数据，这里使用map通过字段名取获取值
         *
         * @param sheet
         * @param style
         * @param rowIndex       - 数据开始行，从0开始
         * @param dataList       - 要填充的数据
         * @param fieldNameENArr - 字段的名称
         */
        public static void fillSheetData(Sheet sheet, CellStyle style, int rowIndex, List<Map<String, Object>> dataList, String[] fieldNameENArr) {
            if (dataList != null && dataList.size() > 0 && fieldNameENArr.length > 0) {
                for (Map<String, Object> map : dataList) {
                    int index = rowIndex++;
                    Row row = sheet.createRow(index);
                    for (int i = 0; i < fieldNameENArr.length; i++) {
                        Cell cell = row.createCell(i);
                        cell.setCellValue(map.get(fieldNameENArr[i]) != null ? map.get(fieldNameENArr[i]).toString() : "");
                        cell.setCellStyle(style);
                    }
                }
    
            }
    
        }
    }

这里简单做了工具类处理，可以根据业务来改变（查看官网API），主要还是理解POI的结构和常用类。

**参考文章：**

[JAVA POI的使用][JAVA POI]

[Java 中使用POI设置EXCEL单元格格式为文本、小数、百分比、货币、日期、科学计数法和中文大写、单元格边框等][Java _POI_EXCEL]

[java用POI设置Excel的列宽][java_POI_Excel]

[JAVA POI自动导出数据和图片到excel][JAVA POI_excel]

ends~

[https_poi.apache.org_overview.html]: https://poi.apache.org/overview.html
[https_poi.apache.org_apidocs_4.1]: https://poi.apache.org/apidocs/4.1/
[20200320114806391.png]: /images/20230528/5bc58e1635a94e40a8b872582c1f2f73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70]: /images/20230528/c1a8b768273f4991a38e6467281f3553.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 1]: /images/20230528/1a895529c6ca451b8897ab80a421471a.png
[JAVA POI]: https://blog.csdn.net/qq_21137441/article/details/79226171
[Java _POI_EXCEL]: https://blog.csdn.net/z_play_du/article/details/34831953
[java_POI_Excel]: https://blog.csdn.net/duqian42707/article/details/51491312
[JAVA POI_excel]: https://blog.csdn.net/apbbbbb/article/details/78740663