poi导入导出excel

最新推荐文章于 2023-04-21 14:22:31 发布

展会融媒体代运营_龙

最新推荐文章于 2023-04-21 14:22:31 发布

阅读量289

点赞数

分类专栏： spring学习总结文章标签： spring poi maven 导出导入Excel Excel

本文链接：https://blog.csdn.net/qq_19167629/article/details/78581033

版权

spring学习总结专栏收录该内容

8 篇文章 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下载

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详解

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