poi导入导出excel

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

### 1.引入poi jar ###

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

*  maven:

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

*  jar:[poi 3.14-beta1下载][poi 3.14-beta1]

### 2. excel对应映射model ###

public class ExcelMapping {
    
        private String headTextName;//列头（标题）名
        private String propertyName;//对应字段名
        private Integer cols;//合并单元格数
        private XSSFCellStyle cellStyle;//单元格样式
    
        public ExcelMapping() {
        }
    
        public ExcelMapping(String headTextName, String propertyName) {
            this.headTextName = headTextName;
            this.propertyName = propertyName;
        }
    
    
        public ExcelMapping(String headTextName, String propertyName, Integer cols) {
            super();
            this.headTextName = headTextName;
            this.propertyName = propertyName;
            this.cols = cols;
        }
        ....//get、set方法
    }

### 3. poi 生成 excel ###

/** * 多列头创建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 */
    public static XSSFWorkbook createExcelFile(Class clazz, List objs,Map<Integer, List<ExcelMapping>> map,String sheetName) throws IllegalArgumentException,IllegalAccessException,
    InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException{
            // 创建新的Excel 工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称
            XSSFSheet sheet = workbook.createSheet(sheetName);
            // 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;
            createFont(workbook);//字体样式
            createTableHeader(sheet, map);//创建标题（头）
            createTableRows(sheet, map, objs, clazz);//创建内容
            return workbook;
        }

### 4.样式createFont ###

public static void createFont(XSSFWorkbook workbook) {
            // 表头
            XSSFCellStyle fontStyle = workbook.createCellStyle();
            XSSFFont font1 = workbook.createFont();
            font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            font1.setFontName("黑体");
            font1.setFontHeightInPoints((short) 14);// 设置字体大小
        fontStyle.setFont(font1);
            fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
            fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
            fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
            fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
            fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
    
            // 内容
    XSSFCellStyle fontStyle2 =workbook.createCellStyle();
    XSSFFont font2 = workbook.createFont();
    font2.setFontName("宋体");
    font2.setFontHeightInPoints((short) 10);// 设置字体大小
    fontStyle2.setFont(font2); fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
            fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
            fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
            fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
            fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
    }

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

/** * 根据ExcelMapping 生成列头（多行列头） * * @param sheet * 工作簿 * @param map * 每行每个单元格对应的列头信息 */
        public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelMapping>> map) {
            int startIndex=0;//cell起始位置
            int endIndex=0;//cell终止位置
    
            for (Map.Entry<Integer, List<ExcelMapping>> entry : map.entrySet()) {
                XSSFRow row = sheet.createRow(entry.getKey());
                List<ExcelMapping> excels = entry.getValue();
                for (int x = 0; x < excels.size(); x++) {
                    //合并单元格
                    if(excels.get(x).getCols()>1){
                        if(x==0){                                      endIndex+=excels.get(x).getCols()-1;
                            CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
                            sheet.addMergedRegion(range);
                            startIndex+=excels.get(x).getCols();
                        }else{
                            endIndex+=excels.get(x).getCols();
                            CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
                            sheet.addMergedRegion(range);
                            startIndex+=excels.get(x).getCols();
                        }
                        XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
                        cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
                        if (excels.get(x).getCellStyle() != null) {
                            cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
                        }
                        cell.setCellStyle(fontStyle);
                    }else{
    
                        XSSFCell cell = row.createCell(x);
                        cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
                        if (excels.get(x).getCellStyle() != null) {
                            cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
                        }
                        cell.setCellStyle(fontStyle);
                    }
    
                }
            }
        }

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

[PropertyDescriptor获取get、set详解][PropertyDescriptor_get_set]

/** * * @param sheet * @param map * @param objs * @param clazz */
        @SuppressWarnings("rawtypes")
        public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelMapping>> map, List objs, Class clazz)
                throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
                ClassNotFoundException, ParseException {
    
            // Class clazz = Class.forName(classBeanURL);
            int rowindex = map.size();
            int maxKey = 0;
            List<ExcelMapping> ems = new ArrayList<>();
            for (Map.Entry<Integer, List<ExcelMapping>> entry : map.entrySet()) {
                if (entry.getKey() > maxKey) {
                    maxKey = entry.getKey();
                }
            }
            ems = map.get(maxKey);
    
            List<Integer> widths = new ArrayList<Integer>(ems.size());
            for (Object obj : objs) {
                XSSFRow row = sheet.createRow(rowindex);
                for (int i = 0; i < ems.size(); i++) {
                    ExcelMapping em = (ExcelMapping) ems.get(i);
                // 获得get方法 
                    PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
                    Method getMethod = pd.getReadMethod();
                    Object rtn = getMethod.invoke(obj);
                    String value = "";
                    // 如果是日期类型 进行 转换
                    if (rtn != null) {
                        if (rtn instanceof Date) {
                            value = DateUtils.formatFullDate((Date) rtn);
                        } else {
                            value = rtn.toString();
                        }
                    }
                    XSSFCell cell = row.createCell(i);
                    if (null != fontStyle2) {
                        cell.setCellStyle(fontStyle2);
                    }
                    cell.setCellValue(value);
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(FontStyle4);
                    // 获得最大列宽
                    int width = value.getBytes().length * 300;
                    // 还未设置，设置当前
                    if (widths.size() <= i) {
                        widths.add(width);
                        continue;
                    }
                    // 比原来大，更新数据
                    if (width > widths.get(i)) {
                        widths.set(i, width);
                    }
                }
                rowindex++;
            }
            // 设置列宽
            for (int index = 0; index < widths.size(); index++) {
                Integer width = widths.get(index);
                width = width < 2500 ? 2500 : width + 300;
                width = width > 10000 ? 10000 + 300 : width + 300;
                sheet.setColumnWidth(index, width);
            }
        }

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

public XSSFWorkbook exportWorkBook(UserModel model) throws CommonException {
        List<UserModel> users = userService.selectByExample(model);//数据源
            if (users == null || users.size()==0) {
                throw new Exception("暂无导出数据");
            }
            Map<String, List<UserModel>> resultMap = userModel.getResultMap();//多列标题头情况
    
            List<ExcelMapping> ems = new ArrayList<>();
            Map<Integer, List<ExcelMapping>> map = new LinkedHashMap<>();
    
            XSSFWorkbook book = null;
    
            // 添加列头信息
            ems.add(new ExcelMapping("人员基本信息", "basicInformation", 3));
            ems.add(new ExcelMapping("证件信息", "idcInformation", 6));
            ems.add(new ExcelMapping("其他信息", "otherInformation", 10));
            ems.add(new ExcelMapping("公司信息", "bigCsrInformation", 5));
            map.put(0, ems);
    
            // 添加第二列头信息
            List<ExcelMapping> ems2 = new ArrayList<>();
            ems2.add(new ExcelMapping("员工姓名", "userName", 0));
            ems2.add(new ExcelMapping("员工姓名(英/拼音)", "userNameEn", 0));
            ems2.add(new ExcelMapping("生日", "birthday", 0));
            ems2.add(new ExcelMapping("身份证号码", "idcNo", 0));
            ems2.add(new ExcelMapping("护照号码", "passNo", 0));
            ems2.add(new ExcelMapping("护照有效期至", "passEndTime", 0));
            ems2.add(new ExcelMapping("其他证件类型", "otherIdcName", 0));
            ems2.add(new ExcelMapping("其他证件号", "otherIdcNo", 0));
            ems2.add(new ExcelMapping("其他证件有效期", "otherIdcEnd", 0));
            ems2.add(new ExcelMapping("所属部门", "departName", 0));
            ems2.add(new ExcelMapping("职务", "job", 0));
            ems2.add(new ExcelMapping("会员卡号", "vipNo", 0));
            ems2.add(new ExcelMapping("联系电话", "contactPhone", 0));
            ems2.add(new ExcelMapping("手机", "mobile", 0));
            ems2.add(new ExcelMapping("email", "email", 0));
            ems2.add(new ExcelMapping("国籍", "nationality", 0));
            ems2.add(new ExcelMapping("居住地", "address", 0));
            ems2.add(new ExcelMapping("邮编", "postCode", 0));
            ems2.add(new ExcelMapping("备注", "remark", 0));
            ....
            map.put(1, ems2);
    
            try {
                book=createExcelFile
                (UserModel.class, users, map);
            } catch (IllegalArgumentException | IllegalAccessException | InvocationTargetException | ClassNotFoundException
                    | IntrospectionException | ParseException e) {
                e.printStackTrace();
            }
            return book;
        }

### 7. springMVC excel导出 ###

@RequestMapping(value = "/export/auto", method = RequestMethod.GET)
        @ResponseBody
        public JsonResult exportUserByAuto(HttpServletRequest request,
                HttpServletResponse response,@ModelAttribute UserModel model)
                throws CommonException {
            response.reset();
    
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmssms");
            String dateStr = sdf.format(new Date());
    
            // 指定下载的文件名
            response.setHeader("Content-Disposition", "attachment;filename=" +dateStr+".xls");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
    
            XSSFWorkbook workbook=exportWorkBook(model);
            try {
                OutputStream output = response.getOutputStream();
                BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
                bufferedOutPut.flush();
                workbook.write(bufferedOutPut);
                bufferedOutPut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return ResultRender.renderResult("导出成功");
        }

### 8. springMVC 导入excel ###

@RequestMapping(value = "/upload",method=RequestMethod.POST)
        @ResponseBody
        public JsonResult uploadUsers(HttpServletRequest request,
                @RequestParam MultipartFile file) throws CommonException{
            //手工导入
            try {
                MultipartRequest multipartRequest=(MultipartRequest) request;
                MultipartFile excelFile=multipartRequest.getFile("file");
                if(excelFile!=null){
                    List<UserModel> models=userService.insertUserByExcel(excelFile);
                    if(models!=null && models.size()>0){
                        return ResultRender.renderResult("名单导入成功", models);
                    }else{
                        return ResultRender.renderResult("名单导入失败", models);
                    }
                }else{
                    return ResultRender.renderResult("上传失败");
                }
            } catch (Exception e) {
                throw new Exception("上传文件出错");
            }
        }

[poi 3.14-beta1]: http://poi.apache.org/download.html
[PropertyDescriptor_get_set]: http://blog.csdn.net/chenanjun2015/article/details/50776711