SSM实现Excel表的导入导出

POI:

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

HSSF

HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。

---

点击这里查询HSSF的中文API
pom.xml

 <!--处理excel-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>

文件解析器

<!--定义文件上传解析器-->
    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!--默认编码-->
        <property name="defaultEncoding" value="UTF-8"/>
        <!--最大上传容量100MB,10*1024*1024-->
        <property name="maxUploadSize" value="104857600"/>
        <!--缓存-->
        <property name="maxInMemorySize" value="4096"/>
    </bean>

JSP页面

<form action="/upload/importExcel" method="post" enctype="multipart/form-data">
    <input type="file" name="excelFile"/></br>
    <input type="submit" value="excel文件上传"/>
</form>
</br>
</br>
<form id="downExcelFile" action="/upload/exportExcel" method="post" enctype="multipart/form-data">
    <input type="submit" value="excel文件下载"/>
</form>

Excel上传

上传的Excel

Controller

/**
     * 导入指定格式的Excel
     * @param excelFile
     * @return
     */
    @PostMapping("/importExcel")
    @ResponseBody
    public String importExcel(@RequestParam("excelFile") MultipartFile excelFile){
        if(excelFile!=null){
            if(!excelFile.getOriginalFilename().matches("^.+\\.(?i)((xls)|(xlsx))$")){
                return "请上传后缀名为xls或xlsx的Excel文件!";
            }
            try {
                if(fileUploadService.importExcel(excelFile)==1){
                    return "excel上传成功!";
                }else {
                    return "excel上传失败";
                }

            }catch (InvalidFormatException |IOException e){
                e.printStackTrace();
                return "excel上传失败";
            }
        }
        return "excel上传失败";
    }

ServiceImpl

 @Override
    public int importExcel(MultipartFile excelFile) throws IOException, InvalidFormatException {
        List<student> studentList=new ArrayList<student>();
        InputStream excelStream=excelFile.getInputStream();
        //获取工作簿
        Workbook workbook= WorkbookFactory.create(excelStream);
        logger.info("****sheetName:"+workbook.getSheetName(0));
        //获取工作表
        Sheet sheet=workbook.getSheetAt(0);
        //获取Excel总行
        int rowNum=sheet.getLastRowNum();
        logger.info("****rowNum:"+rowNum);
        //从内容开始
        if(rowNum>2){
            for (int i=2;i<rowNum;i++){
                Row row=sheet.getRow(i);
                student student=new student();

                student.setId((int) row.getCell(0).getNumericCellValue());
                student.setName(row.getCell(1).getStringCellValue());
                student.setAge((int) row.getCell(2).getNumericCellValue());
                student.setSex(row.getCell(3).getStringCellValue());
                student.setBanji((int)row.getCell(4).getNumericCellValue());
                student.setMajor(row.getCell(5).getStringCellValue());
                student.setTime(row.getCell(6).getDateCellValue());

                studentList.add(student);
//                fileUploadDao.exportStudent()
            }
        }
        logger.info("****studentList:"+studentList);
        int i = fileUploadDao.importExcel(studentList);
        logger.info("***returnNum:"+i);
        if(i==rowNum-2){
            return 1;
        }
        return 0;
    }

mapper

<insert id="importExcel" parameterType="java.util.List">
        insert into student(id,name,age,sex,banji,major,time) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id,jdbcType=INTEGER},
            #{item.name,jdbcType=VARCHAR},
            #{item.age,jdbcType=INTEGER},
            #{item.sex,jdbcType=VARCHAR},
            #{item.banji,jdbcType=INTEGER},
            #{item.major,jdbcType=VARCHAR},
            #{item.time,jdbcType=DATE}
            )
        </foreach>
    </insert>

导出Excel

Controller

/**
     * 导出Excel
     * @return
     */
    @PostMapping("/exportExcel")
    @ResponseBody
    public void exportExcel(HttpServletResponse response) throws IOException {

        //获取导出的学生
        List<student> allStudent = fileUploadService.findAllStudent();
        //把下载文件的日期添加到文件名上
        SimpleDateFormat date=new SimpleDateFormat("yyyy-MM-dd HH:mmss");
        String excelName=date+"学生信息.xls";
        logger.info(excelName);
        //响应 导出Excel
        response.setHeader("Content-Disposition","attachment;filename="+excelName);
        response.setContentType("application/x-excel;charset=UTF-8");
        OutputStream excelOutStream=response.getOutputStream();
        if(allStudent!=null){
            fileUploadService.exportExcel(allStudent,excelOutStream);
        }
        excelOutStream.close();
//        return "";
    }

ServiceImpl

@Override
    public void exportExcel(List<student> studentList, OutputStream excelOutStream)throws IOException {
        HSSFWorkbook workbook=new HSSFWorkbook();
        //合并单元格
        CellRangeAddress cellRangeAddress=new CellRangeAddress(0,0,0,6);
        //创建表
        HSSFSheet sheet=workbook.createSheet("学生信息");
        HSSFRow nameRow=sheet.createRow(0);
        nameRow.createCell(0).setCellValue("学生信息表");
        //第一行设置为标题
        sheet.addMergedRegion(cellRangeAddress);
        //第二行设置字段名
        HSSFRow titleRow=sheet.createRow(1);
        String[] fieldName={"学号","姓名","年龄","性别","班级","专业","日期"};
        for(int i=0;i<fieldName.length;i++){
            titleRow.createCell(i).setCellValue(fieldName[i]);
        }
        int studentNum=studentList.size();
        for(int i=0;i<studentNum;i++){
            student everStudent=studentList.get(i);
            HSSFRow row=sheet.createRow(i+2);
            row.createCell(0).setCellValue(everStudent.getId());
            row.createCell(1).setCellValue(everStudent.getName());
            row.createCell(2).setCellValue(everStudent.getAge());
            row.createCell(3).setCellValue(everStudent.getSex());
            row.createCell(4).setCellValue(everStudent.getBanji());
            row.createCell(5).setCellValue(everStudent.getMajor());
            row.createCell(6).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(everStudent.getTime()));
        }
        //响应给前台
        workbook.write(excelOutStream);
    }

    @Override
    public List<student> findAllStudent() {
        List<student> allStudent=fileUploadDao.findAllStudent();
        return allStudent;
    }

导出的Excel