java 使用 POI 操作Excel实现导入导出功能

主要依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
   	<version>3.14</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

Excel 导入

/**
 *
 * @param filePath 文件绝对路径
 * @param head 是否读标题行
 * @return List<Map<第几列, 单元格数据>>
 */
public static List<Map<Integer, String>> inExcel(String filePath, Boolean head) {
    try {
    	// 读取文件
        File file = new File(filePath);
        FileInputStream fileInputStream = new FileInputStream(file);
        // 定义数据保存的集合
        List<Map<Integer, String>> lists = new ArrayList<>();
        // 打开Excel
        XSSFWorkbook book = new XSSFWorkbook(fileInputStream);
        // 获取第一个Sheet
        XSSFSheet sheet = book.getSheetAt(0);
        if (head) {
            //读标题行(第一行)
            XSSFRow rowHead = sheet.getRow(0);
            // 列数
            int cols = rowHead.getPhysicalNumberOfCells();
            Map<Integer, String> map = new HashMap<>(cols);
            for (int j = 0; j < cols; j++) {
            	// 设置序列化方式
                rowHead.getCell(j).setCellType(CellType.STRING);
                // 获取单元格数据
                String row1 = rowHead.getCell(j).getStringCellValue();
                // map.put("哪一列", "数据")
                map.put(j + 1, row1);
            }
            lists.add(map);
        }
        //读数据行(第二行开始)
        for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
            XSSFRow row = sheet.getRow(i);
            // 列数
            int cols = row.getPhysicalNumberOfCells();
            Map<Integer, String> map = new HashMap<>(cols);
            for (int j = 0; j <= cols; j++) {
                XSSFCell cell = row.getCell(j);
                    if (cell == null){
                        map.put(j + 1, "");
                    }else {
                    	// 设置序列化方式
                        cell.setCellType(CellType.STRING);
                        // 获取单元格数据
                        String row1 = cell.getStringCellValue();
                        // map.put("哪一列", "数据")
                        map.put(j + 1, row1);
                    }
            }
            lists.add(map);
        }
        return lists;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

Excel 导出

/**
 * name-列的标题; value-列的数据
 */
@ToString
@Getter
public class ExportDto {
    private List<ExportPart>  exportParts;
    public ExportDto() {
        exportParts = new ArrayList<>(20);
    }
    public void add(String name, Object value){
        ExportPart exportPart = new ExportPart(name, value);
        exportParts.add(exportPart);
    }
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class ExportPart{
        private String name;
        private Object value;
    }
}

/**
 *
 * @param exportDtos 需要导出的数据
 * @return XSSFWorkbook 
 */
private XSSFWorkbook export(List<ExportDto> exportDtos) {
    try {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        //设置列宽
        sheet.setDefaultColumnWidth((short) 18);
        //创建第一行,填充标题内容
        XSSFRow row = sheet.createRow(0);
        if (CollectionUtil.isEmpty(exportDtos)){
            return workbook;
        }
        List<ExportDto.ExportPart> exportParts = exportDtos.get(0).getExportParts();
        for (short i = 0; i < exportParts.size(); i++) {
            //创建单元格
            XSSFCell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(exportParts.get(i).getName())
            //给单元格设置内容
            cell.setCellValue(text);
        }
        //填充多行
        for (int i = 0; i < exportDtos.size(); i++) {
            //从第二行开始填充数据
            row = sheet.createRow(i + 1);
            ExportDto exportDto = exportDtos.get(i);
            List<ExportDto.ExportPart> partList = exportDto.getExportParts();
            //填充一行
            for (int j = 0; j < partList.size(); j++) {
                Object value = partList.get(j).getValue();
                String string = value == null ? "" : value.toString();
                //创建单元格
                XSSFCell cell = row.createCell(j);
                XSSFRichTextString richString = new XSSFRichTextString(string);
                XSSFFont font = workbook.createFont();
                richString.applyFont(font);
                cell.setCellValue(richString);
            }
        }
        return workbook;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
}

/**
 * 将输出流写入HttpServletResponse
 */
private void setResponseOutputStream(HttpServletResponse response, XSSFWorkbook hssfWorkbook, String fileName) {
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    try {
        response.addHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    }
    response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
    try {
        hssfWorkbook.write(response.getOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

/**
 *
 * @param HttpServletResponse 
 */
public void export(HttpServletResponse response) {
	// 从数据库中获取数据
    List<IndicatorsEntity> entities = indicatorsDao.queryAllList();
    ArrayList<ExportDto> exportDtos = new ArrayList<>();
    AtomicReference<Integer> num = new AtomicReference<>(1);
    // 生成需要导出的数据格式
    entities.forEach(entity -> {
        ExportDto exportDto = new ExportDto();
        exportDto.add("序号", String.valueOf(num.getAndSet(num.get() + 1)));
        exportDto.add("所属期", entity.getBelongYear());
        exportDtos.add(exportDto);
    });
    String fileName = UUIDUtil.generateDefaultUUID() + ".xlsx";
    // 定义在上方
    XSSFWorkbook workbook = export(exportDtos);
    // 定义在上方
    setResponseOutputStream(response, workbook, fileName);
}