【Java用法】使用EasyPoi导入与导出Excel文档的解决方案，这是另外一种方法导入导出文档
第一步：添加Maven依赖
<!--poi的依赖--> <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(); } }*/ }
完结！
