SpringMVC实现poi 解析excel 导入导出

SpringMVC实现poi 解析excel 导入导出 详解

---

听说csdn编辑支持markdown了，果断从博客园转了过来（谁让我是markdown中毒依赖者～～）。这是我在csdn博客上的第一篇文章。最近，我在公司的项目上做了许多个模块涉及excel的导入导出解析，想把这些模块总结成文字，与大家分享，有不足之处，还望指正！

1.引入poi jar

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

• maven:

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.14-beta1</version></dependency>
1
2
3
4
5

• jar: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方法}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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;    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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); // 居中}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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);                }            }        }    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

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

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);        }    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

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;    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

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("导出成功");    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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("上传文件出错");        }    }