POI导入导出

1.场景一

      近期项目中的excel导入导出功能需求频繁的出现，趁此机会，今天笔者对POI的Excel数据的导入导出做一番详解，希望对大家有所帮助。

2.准备工作

①添加POI依赖

<!-- Excel操作包 --><dependency>  <groupId>org.apache.poi</groupId>  <artifactId>poi-ooxml</artifactId>  <version>3.14-beta1</version></dependency><dependency>  <groupId>org.apache.poi</groupId>  <artifactId>poi-ooxml-schemas</artifactId>  <version>3.14-beta1</version></dependency><dependency>  <groupId>org.apache.poi</groupId>  <artifactId>poi</artifactId>  <version>3.14-beta1</version></dependency>

以及excel Jar包依赖

<dependency>  <groupId>net.sourceforge.jexcelapi</groupId>  <artifactId>jxl</artifactId>  <version>2.6.10</version></dependency>

②自定义一个字段与属性名的excel注解

@Retention(RetentionPolicy.RUNTIME)@Target({ElementType.FIELD})public @interface ExcelColProAnnotation {    /**     * 列名     *     * @return     */    String columnName() default "";    /**     * 是否唯一     *     * @return     */    boolean isUnique() default false;}

3.Excel导出

代码如下：

@RequestMapping("/export")public void export(HttpServletRequest request) throws Exception {    List<User> list=userService.getUser(2);    HSSFWorkbook wb = new HSSFWorkbook();    request.setCharacterEncoding("UTF-8");    resp.setCharacterEncoding("UTF-8");    resp.setContentType("application/x-download");    String fileName = "user.xls";    fileName = URLEncoder.encode(fileName, "UTF-8");    resp.addHeader("Content-Disposition", "attachment;filename=" + fileName);    HSSFSheet sheet = wb.createSheet("会员交易记录");    sheet.setDefaultRowHeight((short) (2 * 256));    sheet.setColumnWidth(0, 50 * 160);    HSSFFont font = wb.createFont();    font.setFontName("宋体");    font.setFontHeightInPoints((short) 16);    HSSFRow row = sheet.createRow((int) 0);    sheet.createRow((int) 1);    sheet.createRow((int) 2);    sheet.createRow((int) 3);    sheet.createRow((int) 4);    sheet.createRow((int) 5);    HSSFCellStyle style = wb.createCellStyle();    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);    HSSFCell cell = row.createCell(0);    cell.setCellValue("姓名 ");    cell.setCellStyle(style);    cell = row.createCell(1);    cell.setCellValue("用户名 ");    cell.setCellStyle(style);    cell = row.createCell(2);    cell.setCellStyle(style);    cell.setCellValue("手机号");    cell = row.createCell(3);    cell.setCellStyle(style);    cell.setCellValue("密码");    cell = row.createCell(4);    cell.setCellStyle(style);    cell.setCellValue("状态");    for (int i = 0; i < list.size(); i++)    {        HSSFRow row1 = sheet.createRow((int) i + 1);        User vuserOrder = list.get(i);        row1.createCell(0).setCellValue(vuserOrder.getName());//姓名        row1.createCell(1).setCellValue(vuserOrder.getLoginname());//用户名        row1.createCell(2).setCellValue(vuserOrder.getPhone());//手机号        row1.createCell(3).setCellValue(vuserOrder.getPassword());//密码        row1.createCell(4).setCellValue(vuserOrder.getStatus());//状态    try    {        OutputStream out = resp.getOutputStream();        wb.write(out);        out.close();    }    catch (IOException e)    {        logger.info("=====导出excel异常====");    }    catch (Exception e1)    {        logger.info("=====导出excel异常====");    }}}

剖析详解：

①根据前端属性个数新建excel行数

 HSSFRow row = sheet.createRow((int) 0);    sheet.createRow((int) 1);    sheet.createRow((int) 2);    sheet.createRow((int) 3);    sheet.createRow((int) 4);    sheet.createRow((int) 5);

②根据前端具体表格进行属性名赋值

 HSSFCell cell = row.createCell(0);    cell.setCellValue("姓名 ");    cell.setCellStyle(style);    cell = row.createCell(1);    cell.setCellValue("用户名 ");    cell.setCellStyle(style);    cell = row.createCell(2);    cell.setCellStyle(style);    cell.setCellValue("手机号");    cell = row.createCell(3);    cell.setCellStyle(style);    cell.setCellValue("密码");    cell = row.createCell(4);    cell.setCellStyle(style);    cell.setCellValue("状态");

③根据对应的属性名赋对应的值，对号入座

HSSFRow row1 = sheet.createRow((int) i + 1);        User vuserOrder = list.get(i);        row1.createCell(0).setCellValue(vuserOrder.getName());//姓名        row1.createCell(1).setCellValue(vuserOrder.getLoginname());//用户名        row1.createCell(2).setCellValue(vuserOrder.getPhone());//手机号        row1.createCell(3).setCellValue(vuserOrder.getPassword());//密码        row1.createCell(4).setCellValue(vuserOrder.getStatus());//状态

④导出并下载

OutputStream out = resp.getOutputStream();        wb.write(out);        out.close();

效果图：

4.Excel导入

待导入excel文件如图：

①根据待导入的Excel自定义相应的实体，以Person为例

public class Person {    @ExcelColProAnnotation(columnName = "姓名")    private String name;    @ExcelColProAnnotation(columnName = "年龄")    private String age;    @ExcelColProAnnotation(columnName = "爱好")    private String hobby;    @ExcelColProAnnotation(columnName = "成绩")    private String score;    public String getScore() {        return score;    }    public void setScore(String score) {        this.score = score;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getAge() {        return age;    }    public void setAge(String age) {        this.age = age;    }    public String getHobby() {        return hobby;    }    public void setHobby(String hobby) {        this.hobby = hobby;    }}

其中引用了Excel的自定义注解，将excel中文的字段对应数据库的属性；注意：这里的所有属性最好都定义为String类型，要不然后期的导入效果会不尽人意的。

②PersonMapper

<!DOCTYPE mapper        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.cckj.dao.PersonDao">    <!--添加一个人 OK-->    <insert id="addPerson" parameterType="com.cckj.bean.Person">        insert into person (name,age,hobby,score)        values        (#{name},#{age},        #{hobby},        #{score}        )    </insert></mapper>

这里的字段类型可以不写，如果执意要写一定要跟数据库类型保持一致,有人可能会问，你刚才的Person属性都定义为String，这里又要跟数据库保持一致，不冲突么？放心，我清楚的告诉你，就该这么玩！

③数据库Person的id设备自增

④excel导入方法

public class POIExcelUtil {    //格式化器    private static DecimalFormat integerFormat = new DecimalFormat("0");// 格式化 number String    private static SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    private static DecimalFormat decimalFormat = new DecimalFormat("0.00");// 格式化数字    /**     * 为表格的特定区域绑定下拉框约束     *     * @param list   下拉框约束内容     * @param region 作用区域，长度为4的int数组， region[0]: firstRow, region[1]: lastRow, region[2]: firstCol, region[3]: lastCol     * @param sheet     */    public static void explicitListConstraint(String[] list, int[] region, HSSFSheet sheet) {        if (region.length != 4) {            throw new IllegalArgumentException("下拉框区域数据必须设置完全");        }        //DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);        CellRangeAddressList cellRegions = new CellRangeAddressList(region[0], region[1], region[2], region[3]);        DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRegions, constraint);        sheet.addValidationData(dataValidation);    }    /**     * 为单元格添加注释     *     * @param content   注释内容     * @param region    注释矩形框大小；1*1代表一个单元格； region[0] => width; region[1] => height     * @param patriarch     * @param cell     */    public static void commentForCell(String content, int[] region, HSSFPatriarch patriarch, Cell cell) {        int col = cell.getAddress().getColumn();        int row = cell.getAddress().getRow();        if (region.length != 2) {            region = new int[]{1, 1};        }        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) col, row, (short) (col + region[0]), row + region[1]));        comment.setString(new HSSFRichTextString(content));        cell.setCellComment(comment);    }    /**     * 将图片插入到指定位置，并设定图片所占区域大小，以单元格为单位     * @param imgPath     * @param region  图片位置以及大小；     *                图片左上角所在单元格 => region[0]：col; region[1]: row;     *                图片大小,单位为一个单元格的宽或高 => region[2]: width; region[3]: height     * @param patriarch     * @param workbook     */    public static void pictureToPosition(String imgPath, int[] region, HSSFPatriarch patriarch, Workbook workbook) {        try {            if (region.length != 4){                throw new IllegalArgumentException("the region should have 4 items which are col, row, width, height for image");            }            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();            BufferedImage bufferImg = ImageIO.read(new File(imgPath));            ImageIO.write(bufferImg, FilenameUtils.getExtension(imgPath), byteArrayOut);            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) region[0], region[1], (short) (region[0]+region[2]), region[1]+region[3]);            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));            byteArrayOut.close();        } catch (IOException e) {            e.printStackTrace();        }    }    //为合并区域设置边框    public static void setBorderForRegion(int border, CellRangeAddress region, HSSFSheet sheet, Workbook wb) {        RegionUtil.setBorderBottom(border, region, sheet, wb);        RegionUtil.setBorderLeft(border, region, sheet, wb);        RegionUtil.setBorderRight(border, region, sheet, wb);        RegionUtil.setBorderTop(border, region, sheet, wb);    }    /**     * 将excel行转为List，要求实体的个属性使用@ExcelColProAnnotation注解确定excel列名与实体属性对应关系     * @param clazz 实体类的class实例     * @param is    excel文件流     * @param <T>     * @return     * @throws Exception     */    public static <T> List<T> readExcel(Class<T> clazz, InputStream is) throws Exception {        List<T> resultList = new ArrayList<T>();        Workbook workbook = WorkbookFactory.create(is);        //默认读取第一页表格，第一行为列名行，其余为数据行        Sheet sheet = workbook.getSheetAt(0);        //列名-字段名        Map<String, String> columnMap = new HashMap<String, String>();        Field[] fields = clazz.getDeclaredFields();        for (Field field : fields) {            ExcelColProAnnotation annotation = field.getAnnotation(ExcelColProAnnotation.class);            if (annotation != null) {                if (!columnMap.containsKey(annotation.columnName())) {                    columnMap.put(annotation.columnName(), field.getName());                }            }        }        //临时变量        T t = null;        Object value = null;        Row row = null;        Cell cell = null;        for (int i = 1, maxRow = sheet.getLastRowNum(); i <= maxRow; i++) {            row = sheet.getRow(i);            if (row == null) {                continue;            }            t = clazz.newInstance();            //列，            for (int j = 0, maxCol = row.getLastCellNum(); j <= maxCol; j++) {                cell = row.getCell(j);                value = getCellValue(cell);                if (value == null || "".equals(value)) {                    continue;                }                //获取列名                String columnName = sheet.getRow(0).getCell(j).toString();                BeanUtils.setProperty(t, columnMap.get(columnName), value);            }            resultList.add(t);        }        return resultList;    }    public static Object getCellValue(Cell cell){        Object value = null;        if (cell == null) {            return value;        }        switch (cell.getCellType()) {            case XSSFCell.CELL_TYPE_STRING:                value = cell.getStringCellValue();                break;            case XSSFCell.CELL_TYPE_NUMERIC:                if ("@".equals(cell.getCellStyle().getDataFormatString())) {                   value = integerFormat.format(cell.getNumericCellValue());                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {                    value = decimalFormat.format(cell.getNumericCellValue());                } else {                    value = timeFormat.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));                }                break;            case XSSFCell.CELL_TYPE_BOOLEAN:                value = cell.getBooleanCellValue();                break;            case XSSFCell.CELL_TYPE_BLANK:                value = "";                break;            default:                value = cell.toString();        }        return value;    }}

⑤好了，直接看controller

@RequestMapping("/import")public String importExcel(){    String path = "C:/Users/zhangxing/Desktop/hello.xlsx";    File file = new File(path);    try {        InputStream inputStream = new FileInputStream(file);        try {            List<Person> list= POIExcelUtil.readExcel(Person.class,inputStream);            for (Person p :list){                personService.addPerson(p);            }        } catch (Exception e) {            e.printStackTrace();        }    } catch (FileNotFoundException e) {        e.printStackTrace();    }    return success();    }

当然，path肯定是前端传过来的参数，这里为了方便测试才写死的！一般地，前端是传文件类型给后台，末尾有正解，耐心看！

效果图：

5.实际需求补充：

①待导入的Excel中的字段有可能对应数据库中的多张表，那该怎么处理呢？

1>待导入Excel文件截图

该表中的字段对应数据库中的两个表，person以及staff，另外staff中的person_id是两表连接的桥梁

person表

staff表

2>自定义接受实体类

public class Person {    private int personId;    @ExcelColProAnnotation(columnName = "姓名")    private String name;    @ExcelColProAnnotation(columnName = "年龄")    private String age;    @ExcelColProAnnotation(columnName = "爱好")    private String hobby;    @ExcelColProAnnotation(columnName = "成绩")    private String score;    @ExcelColProAnnotation(columnName = "联系电话")    private String contactPhone;    public int getPersonId() {        return personId;    }    public void setPersonId(int personId) {        this.personId = personId;    }    public String getContactPhone() {        return contactPhone;    }    public void setContactPhone(String contactPhone) {        this.contactPhone = contactPhone;    }    public String getScore() {        return score;    }    public void setScore(String score) {        this.score = score;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getAge() {        return age;    }    public void setAge(String age) {        this.age = age;    }    public String getHobby() {        return hobby;    }    public void setHobby(String hobby) {        this.hobby = hobby;    }}

切记，这里不要忘了申明personId,后面这个是关键

3>配置mybatis获取自增主键值

这样配置了，就可以得到该插入数据的id,测试类

@Testpublic void test11(){   Person p = new Person();   p.setAge("13");   p.setHobby("听书");   p.setName("laojia");   p.setScore("90");    System.out.println("插入前"+p.getPersonId());    personService.addPerson(p);    System.out.println("插入后"+p.getPersonId());}

效果：

好了，既然能够的得到插入数据的id,重头戏在后头

4>通过调用Excel导入方法得到对应的list,然后解析相应的字段插入到对应的表

@RequestMapping("/import")public String importExcel(){    String path = "C:/Users/zhangxing/Desktop/hello.xlsx";    File file = new File(path);    try {        InputStream inputStream = new FileInputStream(file);        try {            List<Person> list= POIExcelUtil.readExcel(Person.class,inputStream);            for (Person p :list){                //personService.addPerson(p);                Person person = new Person();                Staff staff = new Staff();                person.setName(p.getName());                person.setScore(p.getScore());                person.setAge(p.getAge());                person.setName(p.getName());                person.setHobby(p.getHobby());                personService.addPerson(person);                staff.setContactPhone(p.getContactPhone());                staff.setPersonId(person.getPersonId());                staffService.addStaff(staff);            }        } catch (Exception e) {            e.printStackTrace();        }    } catch (FileNotFoundException e) {        e.printStackTrace();    }    return success("导入成功");    }

只有person真正插入了，得到主键值才能起作用

如果涉及到更多的表插入，需要理清表与表的连接桥梁，然后在按先后顺序插入；

访问效果：

person表

staff表

②上传文件接口入参怎么申明？前端代码怎么把参数传给后台？

1>前端传给后台的是file类型

<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>File upload</title><script type="text/javascript" src="js/jQuery.js"></script></head><body><div id="uploadForm"><input id="file" type="file" name="file" value="" /><button id="upload" type="button" onclick="getUpload()">upload</button></div><script type="text/javascript">        function getUpload(){        alert("haha");        alert($('#file')[0].files[0]);        var formData = new FormData();        formData.append('file', $('#file')[0].files[0]);        $.ajax({        url: 'http://localhost:8080/excel/importStaff',        type: 'POST',        dataType:"json",        cache: false,        data: formData,        processData: false,        contentType: false        }).done(function(res) {        alert("成功");        alert(res.data);        }).fail(function(res) {        alert(res);        });        }</script></body></html>

2>后台的文件上传接口必须是post请求

//导入用户表@PostMapping("/importStaff")public ResultInfo importStaff(@RequestParam("file") MultipartFile file) {    try {            List<StaffDTO> list = POIExcelUtil.readExcel(StaffDTO.class, file.getInputStream());            for (StaffDTO s:list){                StaffDTO staff = new StaffDTO();                Workshop workshop= new Workshop();                staff.setStaffNo(s.getStaffNo());                staff.setOrigionId(s.getOrigionId());                staff.setStaffName(s.getStaffName());                staff.setPositionTitle(s.getPositionTitle());                staff.setContactPhone(s.getContactPhone());                staff.setContactMail(s.getContactMail());                staffService.insertStaff(staff);                workshop.setStaffId(staff.getId());                workshop.setWorkshopName(s.getWorkshopName());                workshopService.insert(workshop);            }        } catch (Exception e) {            e.printStackTrace();            return success("导入失败");        }       return success("导入成功");}

3>待上传的excel

4>上传结果截图