【Java用法】使用poi写Java代码导出Excel文档的解决方案

爱被打了一巴掌 2023-01-18 04:14 156阅读 0赞

[【Java用法】使用EasyPoi导入与导出Excel文档的解决方案][Java_EasyPoi_Excel]，这是另外一种方法导入导出文档

## 第一步：添加Maven依赖 ##

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

## 第二步：添加注解 ##

下面是需要导出的实体类，需要添加@ExcelField注解；如果没有，请查看第二步中的一个注解类。

@ApiModel(value = "未提交日报实体")
    @Data
    public class DailyNoSubmitReport implements Serializable { 
    
        @ApiModelProperty(value = "未提交日报id", example = "1")
        @ExcelField("编号")
        private Integer id;
    
        @ApiModelProperty(value = "手机号")
        @ExcelField("手机号")
        private String userName;
    
        @ApiModelProperty(value = "姓名")
        @ExcelField("姓名")
        private String trueName;
    
        @ApiModelProperty(value = "部门id")
        @ExcelField("部门id")
        private String deptId;
    
        @ApiModelProperty(value = "部门名称")
        @ExcelField("部门名称")
        private String deptName;
    
        @ExcelField(value = "未提交日期", dateFormat = "yyyy-MM-dd HH:mm:ss")
        @ApiModelProperty(value = "未提交日期")
        @DateTimeFormat(pattern = "yyyy-MM-dd")
        @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
        private Date noSubmitDate;
    
        @ExcelField("每周周几")
        @ApiModelProperty(value = "每周周几")
        private String dayOfWeek;
    
        @ExcelField(value = "创建日期", dateFormat = "yyyy-MM-dd HH:mm:ss")
        @ApiModelProperty(value = "创建日期")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
        private Date createTime;
    }

以下是注解类：

package com.iot.daily.annotation;
    
    import java.lang.annotation.*;
    
    /** * Description:ExcelField * * @author Jin * @create 2017-4-10 */
    @Documented
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.FIELD)
    public @interface ExcelField { 
    
        String value() default "";
    
        String dateFormat() default "";
    
        boolean isOnlyImport() default false;
    
        String isNullDefaultValue() default "N/A";
    }

## 第三步：编写导出类 ##

@Override
        public JsonResult exportExcel(User user, QueryParamDTO queryParamDTO) { 
    
            if (user == null) { 
                log.error("E|DailyStatisticsServiceImpl|exportExcel()|分页导出Excel日报未提交人员列表时，获取当前登录人失败！");
                return JsonResult.fail("获取当前登录人失败！");
            }
            int pageNum = queryParamDTO.getPageNum();
            int pageSize = queryParamDTO.getPageSize();
    
            try { 
    
                PageInfo<Object> pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(() ->
                        dailyNoSubmitReportMapper.pageList(queryParamDTO));
    
                ExcelUtil.exports2007("日报未提交人员列表", pageInfo.getList());
                return JsonResult.ok("导出日报未提交人员列表成功！");
            } catch (Exception e) { 
                log.error("E|DailyStatisticsServiceImpl|exportExcel()|分页导出Excel日报未提交人员列表失败！原因 = {}", e.getMessage());
            }
    
            return JsonResult.fail("分页导出Excel日报未提交人员列表时失败！");
        }

说明：上面导出类中的ExcelUtil工具类可以直接使用。即以下代码：

package com.iot.daily.common.util;
    
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.date.DateUtil;
    import cn.hutool.core.util.ObjectUtil;
    import cn.hutool.core.util.StrUtil;
    import com.google.common.collect.Lists;
    import com.uiotsoft.daily.account.exception.ApplicationException;
    import com.uiotsoft.daily.account.exception.ErrorCode;
    import com.uiotsoft.daily.annotation.ExcelField;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.beans.PropertyDescriptor;
    import java.io.FileInputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.util.Date;
    import java.util.List;
    
    /** * Description: * * @author Jin * @create 2017-07-27 */
    public class ExcelUtil { 
    
        public static void exports(String sheetName, List<?> list) { 
    
            if (CollUtil.isEmpty(list)) { 
                throw new ApplicationException(ErrorCode.E_100106);
            }
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = attributes.getResponse();
            try { 
                ServletOutputStream out = response.getOutputStream();
                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet(sheetName);
                for (int i = 0; i < list.size(); i++) { 
                    Object obj = list.get(i);
                    Class clazz = obj.getClass();
                    if (i == 0) { 
                        HSSFRow row = sheet.createRow(i);
                        Field[] fields = clazz.getDeclaredFields();
                        int tmp = 0;
                        for (Field field : fields) { 
                            HSSFCell cell = row.createCell(tmp);
                            ExcelField excelFiled = field.getAnnotation(ExcelField.class);
                            // 判断是否只支持导入
                            if (excelFiled == null || excelFiled.isOnlyImport()) { 
                                continue;
                            }
                            cell.setCellValue(excelFiled.value());
                            tmp++;
                        }
                    }
                    HSSFRow row = sheet.createRow(i + 1);
                    Field[] fields = clazz.getDeclaredFields();
                    int tmp = 0;
                    for (Field field : fields) { 
                        ExcelField excelFiled = field.getAnnotation(ExcelField.class);
                        if (excelFiled == null || excelFiled.isOnlyImport()) { 
                            continue;
                        }
                        HSSFCell cell = row.createCell(tmp);
                        PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
                        Method getMethod = pd.getReadMethod();
                        Object o = getMethod.invoke(obj);
                        if (!StrUtil.isBlank(excelFiled.dateFormat()) && o instanceof Date) { 
                            cell.setCellValue(DateUtil.format((Date) o, excelFiled.dateFormat()));
                        } else { 
                            cell.setCellValue(ObjectUtil.isNull(o) ? excelFiled.isNullDefaultValue() : String.valueOf(o));
                        }
                        tmp++;
                    }
                }
                response.reset();
                response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("utf-8"), "ISO8859-1") + ".xls");
                response.setContentType("application/msexcel");
                wb.write(out);
                out.flush();
                out.close();
            } catch (Exception e) { 
                e.printStackTrace();
                throw new ApplicationException(ErrorCode.E_100106 + "导出excel失败，【" + e.getMessage() + "】");
            }
        }
    
        public static void exports2007(String sheetName, List<?> list) { 
    
            if (CollUtil.isEmpty(list)) { 
                throw new ApplicationException(ErrorCode.E_100106);
            }
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = attributes.getResponse();
            try { 
                ServletOutputStream out = response.getOutputStream();
                XSSFWorkbook wb = new XSSFWorkbook();
                XSSFSheet sheet = wb.createSheet(sheetName);
                for (int i = 0; i < list.size(); i++) { 
                    Object obj = list.get(i);
                    Class clazz = obj.getClass();
                    if (i == 0) { 
                        XSSFRow row = sheet.createRow(i);
                        Field[] fields = clazz.getDeclaredFields();
                        int tmp = 0;
                        for (Field field : fields) { 
                            XSSFCell cell = row.createCell(tmp);
                            ExcelField excelFiled = field.getAnnotation(ExcelField.class);
                            if (excelFiled == null || excelFiled.isOnlyImport()) { 
                                continue;
                            }
                            cell.setCellValue(excelFiled.value());
                            tmp++;
                        }
                    }
                    XSSFRow row = sheet.createRow(i + 1);
                    Field[] fields = clazz.getDeclaredFields();
                    int tmp = 0;
                    for (Field field : fields) { 
                        ExcelField excelFiled = field.getAnnotation(ExcelField.class);
                        if (excelFiled == null || excelFiled.isOnlyImport()) { 
                            continue;
                        }
                        XSSFCell cell = row.createCell(tmp);
                        PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
                        Method getMethod = pd.getReadMethod();
                        Object o = getMethod.invoke(obj);
                        if (!StrUtil.isBlank(excelFiled.dateFormat()) && o instanceof Date) { 
                            cell.setCellValue(DateUtil.format((Date) o, excelFiled.dateFormat()));
                        } else { 
                            cell.setCellValue(ObjectUtil.isNull(o) ? excelFiled.isNullDefaultValue() : String.valueOf(o));
                        }
                        tmp++;
                    }
                }
                response.reset();
                response.setHeader("Content-disposition", "attachment;filename=" + new String(sheetName.getBytes("utf-8"), "ISO8859-1") + ".xlsx");
                response.setContentType("application/msexcel");
                wb.write(out);
                out.flush();
                out.close();
    
            } catch (Exception e) { 
                e.printStackTrace();
                throw new RuntimeException("导出excel失败，[" + e.getMessage() + "]");
            }
        }
    
    
        public static <T> List<T> imports(FileInputStream is, Class<T> clazz) { 
            List<T> result = Lists.newArrayList();
            try { 
                HSSFWorkbook workbook = new HSSFWorkbook(is);
                Sheet sheet = workbook.getSheetAt(0);
                // 获取第0行标题
                Row row0 = sheet.getRow(0);
                // 遍历每一列
                for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) { 
                    T obj = clazz.newInstance();
                    Field[] fields = obj.getClass().getDeclaredFields();
                    Row row = sheet.getRow(r);
                    for (int c = 0; c < row.getPhysicalNumberOfCells(); c++) { 
                        for (Field field : fields) { 
                            ExcelField excelFiled = field.getAnnotation(ExcelField.class);
                            String title = getCellValue(row0.getCell(c).getCellType(), row0.getCell(c));
                            if (excelFiled == null && !title.equals(excelFiled.value())) { 
                                continue;
                            }
                            String cellValue = getCellValue(row.getCell(c).getCellType(), row.getCell(c));
                            PropertyDescriptor pd = new PropertyDescriptor(field.getName(), obj.getClass());
                            if ("错误".equals(cellValue) || field.getType() == String.class) { 
                                pd.getWriteMethod().invoke(obj, cellValue);
                                continue;
                            }
                            if (field.getType() == Integer.class) { 
                                pd.getWriteMethod().invoke(obj, Integer.parseInt(cellValue));
                                continue;
                            }
                            if (field.getType() == Long.class) { 
                                pd.getWriteMethod().invoke(obj, Long.parseLong(cellValue));
                                continue;
                            }
                            if (field.getType() == Float.class) { 
                                pd.getWriteMethod().invoke(obj, Float.parseFloat(cellValue));
                                continue;
                            }
                            if (field.getType() == Double.class) { 
                                pd.getWriteMethod().invoke(obj, Double.parseDouble(cellValue));
                                continue;
                            }
                            if (field.getType() == Date.class) { 
                                pd.getWriteMethod().invoke(obj, DateUtil.parseDate(cellValue));
                            }
                        }
                    }
                    result.add(obj);
                }
            } catch (Exception e) { 
                throw new RuntimeException("导如excel失败,[" + e.getMessage() + "]");
            }
            return result;
        }
    
        public static <T> List<T> imports2007(FileInputStream is, Class<T> clazz) { 
            List<T> result = Lists.newArrayList();
            try { 
                XSSFWorkbook workbook = new XSSFWorkbook(is);
                Sheet sheet = workbook.getSheetAt(0);
                // 获取第0行标题
                Row row0 = sheet.getRow(0);
                // 遍历每一列
                for (int r = 1; r < sheet.getPhysicalNumberOfRows(); r++) { 
                    T obj = clazz.newInstance();
                    Field[] fields = obj.getClass().getDeclaredFields();
                    Row row = sheet.getRow(r);
                    for (int c = 0; c < row.getPhysicalNumberOfCells(); c++) { 
                        for (Field field : fields) { 
                            ExcelField excelFiled = field.getAnnotation(ExcelField.class);
                            String title = getCellValue(row0.getCell(c).getCellType(), row0.getCell(c));
                            if (excelFiled == null || !title.equals(excelFiled.value())) { 
                                continue;
                            }
                            String cellValue = getCellValue(row.getCell(c).getCellType(), row.getCell(c));
                            PropertyDescriptor pd = new PropertyDescriptor(field.getName(), obj.getClass());
                            if ("错误".equals(cellValue) || field.getType() == String.class) { 
                                pd.getWriteMethod().invoke(obj, cellValue);
                                continue;
                            }
                            if (field.getType() == Integer.class) { 
                                pd.getWriteMethod().invoke(obj, Integer.parseInt(cellValue));
                                continue;
                            }
                            if (field.getType() == Long.class) { 
                                pd.getWriteMethod().invoke(obj, Long.parseLong(cellValue));
                                continue;
                            }
                            if (field.getType() == Float.class) { 
                                pd.getWriteMethod().invoke(obj, Float.parseFloat(cellValue));
                                continue;
                            }
                            if (field.getType() == Double.class) { 
                                pd.getWriteMethod().invoke(obj, Double.parseDouble(cellValue));
                                continue;
                            }
                            if (field.getType() == Date.class) { 
                                pd.getWriteMethod().invoke(obj, DateUtil.parseDate(cellValue));
                            }
                        }
                    }
                    result.add(obj);
                }
            } catch (Exception e) { 
                e.printStackTrace();
                throw new RuntimeException("导如excel失败,[" + e.getMessage() + "]");
            }
            return result;
        }
    
        public static String getCellValue(int cellType, Cell cell) { 
            switch (cellType) { 
                // 文本
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
                // 数字、日期
                case Cell.CELL_TYPE_NUMERIC:
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { 
                        // 日期型
                        return DateUtil.formatDate(cell.getDateCellValue());
                    } else { 
                        // 数字
                        return String.valueOf(cell.getNumericCellValue());
                    }
                    // 布尔型
                case Cell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                // 空白
                case Cell.CELL_TYPE_BLANK:
                    return cell.getStringCellValue();
                // 错误
                case Cell.CELL_TYPE_ERROR:
                    return "错误";
                // 公式
                case Cell.CELL_TYPE_FORMULA:
                    return "错误";
                default:
                    return "错误";
            }
        }
    
        /*public static void main(String[] args) { File file = new File("E:\\123.xlsx"); try { FileInputStream inputStream = new FileInputStream(file); List<TCmsContent> list = imports2007(inputStream, TCmsContent.class); System.out.println(list.get(0).getTitle()); } catch (FileNotFoundException e) { e.printStackTrace(); } }*/
    }

完结！

[Java_EasyPoi_Excel]: https://blog.csdn.net/weixin_44299027/article/details/117996003