poi导入导出excel

迷南。 2022-06-05 06:25 479阅读 0赞

1.引入poi jar

要良好支持XSSF的话，下载poi 3.1以上的哦！

  • maven:


    org.apache.poi
    poi
    3.14-beta1

  • jar:poi 3.14-beta1下载

2. excel对应映射model

  1. public class ExcelMapping {
  3.     private String headTextName;//列头（标题）名
  4.     private String propertyName;//对应字段名
  5.     private Integer cols;//合并单元格数
  6.     private XSSFCellStyle cellStyle;//单元格样式
  8.     public ExcelMapping() {
  9.     }
  11.     public ExcelMapping(String headTextName, String propertyName) {
  12.         this.headTextName = headTextName;
  13.         this.propertyName = propertyName;
  14.     }
  17.     public ExcelMapping(String headTextName, String propertyName, Integer cols) {
  18.         super();
  19.         this.headTextName = headTextName;
  20.         this.propertyName = propertyName;
  21.         this.cols = cols;
  22.     }
  23.     ....//get、set方法
  24. }

3. poi 生成 excel

  1. /** * 多列头创建EXCEL * * @param sheetName 工作簿名称 * @param clazz 数据源model类型 * @param objs excel标题列以及对应model字段名 * @param map 标题列行数以及cell字体样式 * @return * @throws IllegalArgumentException * @throws IllegalAccessException * @throws InvocationTargetException * @throws ClassNotFoundException * @throws IntrospectionException * @throws ParseException */
  2. public static XSSFWorkbook createExcelFile(Class clazz, List objs,Map<Integer, List<ExcelMapping>> map,String sheetName) throws IllegalArgumentException,IllegalAccessException,
  3. InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException{
  4.         // 创建新的Excel 工作簿
  5.         XSSFWorkbook workbook = new XSSFWorkbook();
  6.         // 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称
  7.         XSSFSheet sheet = workbook.createSheet(sheetName);
  8.         // 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;
  9.         createFont(workbook);//字体样式
  10.         createTableHeader(sheet, map);//创建标题（头）
  11.         createTableRows(sheet, map, objs, clazz);//创建内容
  12.         return workbook;
  13.     }

4.样式createFont

  1. public static void createFont(XSSFWorkbook workbook) {
  2.         // 表头
  3.         XSSFCellStyle fontStyle = workbook.createCellStyle();
  4.         XSSFFont font1 = workbook.createFont();
  5.         font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
  6.         font1.setFontName("黑体");
  7.         font1.setFontHeightInPoints((short) 14);// 设置字体大小
  8.     fontStyle.setFont(font1);
  9.         fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
  10.         fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
  11.         fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
  12.         fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
  13.         fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
  15.         // 内容
  16. XSSFCellStyle fontStyle2 =workbook.createCellStyle();
  17. XSSFFont font2 = workbook.createFont();
  18. font2.setFontName("宋体");
  19. font2.setFontHeightInPoints((short) 10);// 设置字体大小
  20. fontStyle2.setFont(font2); fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
  21.         fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
  22.         fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
  23.         fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
  24.         fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
  25. }

4.多行列头的生成以及单元格的合并 createTableHeader(sheet, map)

  1. /** * 根据ExcelMapping 生成列头（多行列头） * * @param sheet * 工作簿 * @param map * 每行每个单元格对应的列头信息 */
  2.     public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelMapping>> map) {
  3.         int startIndex=0;//cell起始位置
  4.         int endIndex=0;//cell终止位置
  6.         for (Map.Entry<Integer, List<ExcelMapping>> entry : map.entrySet()) {
  7.             XSSFRow row = sheet.createRow(entry.getKey());
  8.             List<ExcelMapping> excels = entry.getValue();
  9.             for (int x = 0; x < excels.size(); x++) {
  10.                 //合并单元格
  11.                 if(excels.get(x).getCols()>1){
  12.                     if(x==0){                                      endIndex+=excels.get(x).getCols()-1;
  13.                         CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
  14.                         sheet.addMergedRegion(range);
  15.                         startIndex+=excels.get(x).getCols();
  16.                     }else{
  17.                         endIndex+=excels.get(x).getCols();
  18.                         CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
  19.                         sheet.addMergedRegion(range);
  20.                         startIndex+=excels.get(x).getCols();
  21.                     }
  22.                     XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
  23.                     cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
  24.                     if (excels.get(x).getCellStyle() != null) {
  25.                         cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
  26.                     }
  27.                     cell.setCellStyle(fontStyle);
  28.                 }else{
  30.                     XSSFCell cell = row.createCell(x);
  31.                     cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
  32.                     if (excels.get(x).getCellStyle() != null) {
  33.                         cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
  34.                     }
  35.                     cell.setCellStyle(fontStyle);
  36.                 }
  38.             }
  39.         }
  40.     }

5. 创建excel内容文本 createTableRows(sheet, map, objs, clazz)

PropertyDescriptor获取get、set详解

  1. /** * * @param sheet * @param map * @param objs * @param clazz */
  2.     @SuppressWarnings("rawtypes")
  3.     public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelMapping>> map, List objs, Class clazz)
  4.             throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
  5.             ClassNotFoundException, ParseException {
  7.         // Class clazz = Class.forName(classBeanURL);
  8.         int rowindex = map.size();
  9.         int maxKey = 0;
  10.         List<ExcelMapping> ems = new ArrayList<>();
  11.         for (Map.Entry<Integer, List<ExcelMapping>> entry : map.entrySet()) {
  12.             if (entry.getKey() > maxKey) {
  13.                 maxKey = entry.getKey();
  14.             }
  15.         }
  16.         ems = map.get(maxKey);
  18.         List<Integer> widths = new ArrayList<Integer>(ems.size());
  19.         for (Object obj : objs) {
  20.             XSSFRow row = sheet.createRow(rowindex);
  21.             for (int i = 0; i < ems.size(); i++) {
  22.                 ExcelMapping em = (ExcelMapping) ems.get(i);
  23.             // 获得get方法 
  24.                 PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
  25.                 Method getMethod = pd.getReadMethod();
  26.                 Object rtn = getMethod.invoke(obj);
  27.                 String value = "";
  28.                 // 如果是日期类型 进行 转换
  29.                 if (rtn != null) {
  30.                     if (rtn instanceof Date) {
  31.                         value = DateUtils.formatFullDate((Date) rtn);
  32.                     } else {
  33.                         value = rtn.toString();
  34.                     }
  35.                 }
  36.                 XSSFCell cell = row.createCell(i);
  37.                 if (null != fontStyle2) {
  38.                     cell.setCellStyle(fontStyle2);
  39.                 }
  40.                 cell.setCellValue(value);
  41.                 cell.setCellType(XSSFCell.CELL_TYPE_STRING);
  42.                 cell.setCellStyle(FontStyle4);
  43.                 // 获得最大列宽
  44.                 int width = value.getBytes().length * 300;
  45.                 // 还未设置，设置当前
  46.                 if (widths.size() <= i) {
  47.                     widths.add(width);
  48.                     continue;
  49.                 }
  50.                 // 比原来大，更新数据
  51.                 if (width > widths.get(i)) {
  52.                     widths.set(i, width);
  53.                 }
  54.             }
  55.             rowindex++;
  56.         }
  57.         // 设置列宽
  58.         for (int index = 0; index < widths.size(); index++) {
  59.             Integer width = widths.get(index);
  60.             width = width < 2500 ? 2500 : width + 300;
  61.             width = width > 10000 ? 10000 + 300 : width + 300;
  62.             sheet.setColumnWidth(index, width);
  63.         }
  64.     }

6.生成对应model数据源的excel

  1. public XSSFWorkbook exportWorkBook(UserModel model) throws CommonException {
  2.     List<UserModel> users = userService.selectByExample(model);//数据源
  3.         if (users == null || users.size()==0) {
  4.             throw new Exception("暂无导出数据");
  5.         }
  6.         Map<String, List<UserModel>> resultMap = userModel.getResultMap();//多列标题头情况
  8.         List<ExcelMapping> ems = new ArrayList<>();
  9.         Map<Integer, List<ExcelMapping>> map = new LinkedHashMap<>();
  11.         XSSFWorkbook book = null;
  13.         // 添加列头信息
  14.         ems.add(new ExcelMapping("人员基本信息", "basicInformation", 3));
  15.         ems.add(new ExcelMapping("证件信息", "idcInformation", 6));
  16.         ems.add(new ExcelMapping("其他信息", "otherInformation", 10));
  17.         ems.add(new ExcelMapping("公司信息", "bigCsrInformation", 5));
  18.         map.put(0, ems);
  20.         // 添加第二列头信息
  21.         List<ExcelMapping> ems2 = new ArrayList<>();
  22.         ems2.add(new ExcelMapping("员工姓名", "userName", 0));
  23.         ems2.add(new ExcelMapping("员工姓名(英/拼音)", "userNameEn", 0));
  24.         ems2.add(new ExcelMapping("生日", "birthday", 0));
  25.         ems2.add(new ExcelMapping("身份证号码", "idcNo", 0));
  26.         ems2.add(new ExcelMapping("护照号码", "passNo", 0));
  27.         ems2.add(new ExcelMapping("护照有效期至", "passEndTime", 0));
  28.         ems2.add(new ExcelMapping("其他证件类型", "otherIdcName", 0));
  29.         ems2.add(new ExcelMapping("其他证件号", "otherIdcNo", 0));
  30.         ems2.add(new ExcelMapping("其他证件有效期", "otherIdcEnd", 0));
  31.         ems2.add(new ExcelMapping("所属部门", "departName", 0));
  32.         ems2.add(new ExcelMapping("职务", "job", 0));
  33.         ems2.add(new ExcelMapping("会员卡号", "vipNo", 0));
  34.         ems2.add(new ExcelMapping("联系电话", "contactPhone", 0));
  35.         ems2.add(new ExcelMapping("手机", "mobile", 0));
  36.         ems2.add(new ExcelMapping("email", "email", 0));
  37.         ems2.add(new ExcelMapping("国籍", "nationality", 0));
  38.         ems2.add(new ExcelMapping("居住地", "address", 0));
  39.         ems2.add(new ExcelMapping("邮编", "postCode", 0));
  40.         ems2.add(new ExcelMapping("备注", "remark", 0));
  41.         ....
  42.         map.put(1, ems2);
  44.         try {
  45.             book=createExcelFile
  46.             (UserModel.class, users, map);
  47.         } catch (IllegalArgumentException | IllegalAccessException | InvocationTargetException | ClassNotFoundException
  48.                 | IntrospectionException | ParseException e) {
  49.             e.printStackTrace();
  50.         }
  51.         return book;
  52.     }

7. springMVC excel导出

  1. @RequestMapping(value = "/export/auto", method = RequestMethod.GET)
  2.     @ResponseBody
  3.     public JsonResult exportUserByAuto(HttpServletRequest request,
  4.             HttpServletResponse response,@ModelAttribute UserModel model)
  5.             throws CommonException {
  6.         response.reset();
  8.         SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmssms");
  9.         String dateStr = sdf.format(new Date());
  11.         // 指定下载的文件名
  12.         response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xls");
  13.         response.setContentType("application/vnd.ms-excel;charset=UTF-8");
  14.         response.setHeader("Pragma", "no-cache");
  15.         response.setHeader("Cache-Control", "no-cache");
  16.         response.setDateHeader("Expires", 0);
  18.         XSSFWorkbook workbook=exportWorkBook(model);
  19.         try {
  20.             OutputStream output = response.getOutputStream();
  21.             BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
  22.             bufferedOutPut.flush();
  23.             workbook.write(bufferedOutPut);
  24.             bufferedOutPut.close();
  25.         } catch (IOException e) {
  26.             e.printStackTrace();
  27.         }
  28.         return ResultRender.renderResult("导出成功");
  29.     }

8. springMVC 导入excel

  1. @RequestMapping(value = "/upload",method=RequestMethod.POST)
  2.     @ResponseBody
  3.     public JsonResult uploadUsers(HttpServletRequest request,
  4.             @RequestParam MultipartFile file) throws CommonException{
  5.         //手工导入
  6.         try {
  7.             MultipartRequest multipartRequest=(MultipartRequest) request;
  8.             MultipartFile excelFile=multipartRequest.getFile("file");
  9.             if(excelFile!=null){
  10.                 List<UserModel> models=userService.insertUserByExcel(excelFile);
  11.                 if(models!=null && models.size()>0){
  12.                     return ResultRender.renderResult("名单导入成功", models);
  13.                 }else{
  14.                     return ResultRender.renderResult("名单导入失败", models);
  15.                 }
  16.             }else{
  17.                 return ResultRender.renderResult("上传失败");
  18.             }
  19.         } catch (Exception e) {
  20.             throw new Exception("上传文件出错");
  21.         }
  22.     }

发表评论

表情：
评论列表 （有 0 条评论，479人围观）

还没有评论，来说两句吧...

相关阅读