SpringBoot实现数据导入导出（POI）

一、简介

1、Excel文件的组织形式和POI中基本结构：

• 一个Excel文件对应一个workbook

• 一个workerbook是有若干个sheet组成的

• 一个sheet有多个row

• 一个row一般存在多个cell

2、Excel和POI对象对应关系：

Excel结构	POI对象
excel 的文档对象	HSSFWorkbook
excel 的表单（sheet）	HSSFSheet
excel 的行	HSSFRow
excel 的格子单元	HSSFCell

3、POI创建Excel的步骤

1. 生成文档对象HSSHWorkbook。

2. 通过HSSFWorkbook生成表单HSSFSheet。

3. 通过HSSFSheet生成行HSSFRow

4. 通过HSSFRow生成单元格HSSFCell。

二、利用POI实现excel的导出

1、配置POM

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>

2. 页面添加导出按钮

 <el-button type="success" @click="downExcel">导出数据</el-button>

3. 添加处理函数

downExcel(){
     window.location.href="http://localhost:9000/user/excel";
},

4. 工具类PageData，用来传递数据

package com.wnxy.wateraffair.util;

/**
 * @author ：fengSir
 * @date ：Created By 2022-08-30 10:07
 * @description ：TODO
 */
import java.util.HashMap;
import java.util.Map;

public class PageData extends HashMap {
    Map map = null;

    public PageData() {
        map = new HashMap();
    }

    public String getString(Object key) {
        return String.valueOf( map.get(key));
    }

    @SuppressWarnings("unchecked")
    @Override
    public Object put(Object key, Object value) {
        return map.put(key, value);
    }

    @Override
    public Object remove(Object key) {
        return map.remove(key);
    }

    public int size() {
        // TODO Auto-generated method stub
        return map.size();
    }

}

5. 导出Excel核心处理类ObjectExcelView，继承自AbstractXlsView

package com.wnxy.wateraffair.util;

import org.apache.poi.ss.usermodel.*;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ObjectExcelView extends AbstractXlsView {
    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String filename = sdf.format(new Date());
        //设置下载头部文件信息
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        //创建sheet，相当于一个excelsheel
        Sheet sheet = workbook.createSheet("sheet1");
        //得到excel标题内容
        List<String> titles = (List<String>) model.get("titles");
        int len = titles.size();
        //设置单元格样式
        CellStyle cellStyle = workbook.createCellStyle(); //标题样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置字体
        Font headerFont = workbook.createFont();    //标题字体
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 11);
        cellStyle.setFont(headerFont);
        short width = 20, height = 25 * 20;
        sheet.setDefaultColumnWidth(width);
        //创建第一行，用来放标题
        Row header = sheet.createRow(0);
        for (int i = 0; i < len; i++) { //设置标题
            String title = titles.get(i);
            Cell cell = header.createCell(i);
            cell.setCellValue(title);
            cell.setCellStyle(cellStyle);
        }
        header.setHeight(height);
        //设置内容样式
        CellStyle contentStyle = workbook.createCellStyle(); //内容样式
        contentStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        //得到controller传过来导出的数据，并填充到每一行中
        List<PageData> varList = (List<PageData>) model.get("varList");
        int varCount = varList.size();
        for (int i = 0; i < varCount; i++) {
            Row userRow = sheet.createRow(i + 1);
            PageData vpd = varList.get(i);
            int mapLen = vpd.size();
            for (int j = 0; j < mapLen; j++) {
                String varStr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
                Cell cell = userRow.createCell(j);
                cell.setCellValue(varStr);
                cell.setCellStyle(contentStyle);
            }

        }
    }
}

6. Controller功能实现

 @RequestMapping("excel")
    public ModelAndView exportExcel(@RequestParam(value = "name", required = false) String name,
                                    @RequestParam(value = "dateRange", required = false) String[] dateRange) {
        String[] dateRangeArr = dateRange == null ? new String[]{} : dateRange;
        UserDto user = new UserDto();
        user.setDateRange(dateRangeArr);
        user.setName(name);
        ModelAndView mv = new ModelAndView();
        PageData pd = new PageData();
        try {
            Map<String, Object> dataMap = new HashMap<>();
            List<String> titles = new ArrayList<>();
            titles.add("用户编号"); // 1
            titles.add("姓名"); // 2
            titles.add("电话");//3
            titles.add("部门"); // 4
            titles.add("上级"); // 5
            titles.add("籍贯"); // 6
            titles.add("生日"); // 7
            dataMap.put("titles", titles);
            List<UserVO> varOList = userService.queryAllExcel(user);
            List<PageData> varList = new ArrayList<>();
            for (int i = 0; i < varOList.size(); i++) {
                PageData vpd = new PageData();
                vpd.put("var1", varOList.get(i).getId()); // 1
                vpd.put("var2", varOList.get(i).getUname()); // 2
                vpd.put("var3", varOList.get(i).getTel()); // 3
                vpd.put("var4", varOList.get(i).getDeptName()); // 4
                vpd.put("var5", varOList.get(i).getUsuperior()); // 5
                vpd.put("var6", varOList.get(i).getUorigo()); // 6
                Date ubirthday = varOList.get(i).getUbirthday();
                vpd.put("var7", DateUtil.convertDateToString(ubirthday)); // 7


                varList.add(vpd);
            }
            dataMap.put("varList", varList);
            ObjectExcelView erv = new ObjectExcelView();
            mv = new ModelAndView(erv, dataMap);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return mv;
    }

7. Map文件

    <select id="queryAllExcel" resultMap="UserVoResultMap">
        select
        su.*,sd.d_name
        from user su left join dept sd on sd.d_id = su.d_id
        <where>
            <if test="name!=null and name!=''">
                u_name like '%${name}%'
            </if>
            <if test="dateRange.length!=0">
                and u_birthday between #{dateRange[0],javaType=string} and #{dateRange[1],javaType=string}
            </if>

        </where>
    </select>

三、数据导入功能

1、页面

<el-col :span="4">
              <el-upload class="clearfix" action="http://localhost:9000/user/importExcel" name="excel"
                :on-progress="onExcelProgress" :on-success="handleExcelSuccess"
                accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
                multiple :limit="1">
                <el-button type="primary">数据导入</el-button>
              </el-upload>
            </el-col>

2、axios回调

handleExcelSuccess(res, result) {
      this.$message.success("导入成功!!");
      if (result.response.code == 200) {
        this.loadUsers();
      }
    },

3、核心处理类

package com.wnxy.wateraffair.util;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

public class ObjectExcelRead {
    /**
     * @param filepath //文件路径
     * @param filename //文件名
     * @param startrow //开始行号
     * @param startcol //开始列号
     * @param sheetnum //sheet
     * @return list
     */
    public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
        List<Object> varList = new ArrayList<Object>();

        try {
            File target = new File(filepath, filename);
            FileInputStream fi = new FileInputStream(target);
            HSSFWorkbook wb = new HSSFWorkbook(fi);
            HSSFSheet sheet = wb.getSheetAt(sheetnum);                    //sheet 从0开始
            int rowNum = sheet.getLastRowNum() + 1;                    //取得最后一行的行号

            for (int i = startrow; i < rowNum; i++) {                    //行循环开始

                PageData varpd = new PageData();
                HSSFRow row = sheet.getRow(i);                            //行
                int cellNum = row.getLastCellNum();                    //每行的最后一个单元格位置

                for (int j = startcol; j < cellNum; j++) {                //列循环开始

                    HSSFCell cell = row.getCell(Short.parseShort(j + ""));
                    Object cellValue = null;
                    if (null != cell) {
                        switch (cell.getCellType()) {                    // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                            case _NONE:
                                cellValue = String.valueOf((int) cell.getNumericCellValue());
                                break;
                            case STRING:
                                cellValue = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                cellValue = cell.getNumericCellValue() + "";
                                // cellValue = String.valueOf(cell.getDateCellValue());
                                break;
                            case BLANK:
                                cellValue = "";
                                break;
                            case BOOLEAN:
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case ERROR:
                                cellValue = String.valueOf(cell.getErrorCellValue());
                                break;
                        }
                    } else {
                        cellValue = "";
                    }
                    varpd.put("var" + j, cellValue);
                }
                varList.add(varpd);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return varList;
    }
}

4、控制器

 @RequestMapping("importExcel")
    public Result readExcel(
            @RequestParam(value="excel",required=false) MultipartFile file,HttpServletRequest request
    ) throws Exception{
        System.out.println("----------------------------------------------");
        PageData pd = new PageData();
        if (null != file && !file.isEmpty()) {
            String filePath = request.getSession().getServletContext().getRealPath("/upload");							//文件上传路径
            String fileName = FileUpload.fileUp(file, filePath, "userexcel");//执行上传
            List<PageData> listPd = (List) ObjectExcelRead.readExcel(filePath, fileName, 1, 0, 0);	//执行读EXCEL操作,读出的数据导入List 2:从第2行开始；0:从第A列开始；0:第0个sheet

            for(int i=0;i<listPd.size();i++){
                User user = new User();
                String strId = listPd.get(i).getString("var0");
                int id=0;
                if(strId.indexOf(".")!=-1){
                    id = Integer.parseInt(strId.substring(0,strId.indexOf(".")));
                }else {
                    id = Integer.parseInt(strId);
                }
                user.setUId(id);
                user.setUName(listPd.get(i).getString("var1"));
                String var2 = listPd.get(i).getString("var2");
                if(var2.indexOf(".")!=-1){
                    var2 = var2.substring(0,var2.indexOf("."));
                }
                user.setUTel(var2);
                String strDeptName = listPd.get(i).getString("var3");
                int deptId=0;
                switch (strDeptName){
                    case "总务部":deptId = 1;break;
                    case "职能部":deptId = 2;break;
                    case "设备部":deptId = 3;break;
                    case "采购部":deptId = 4;break;
                }
                user.setDId(deptId);
                user.setUSuperior(listPd.get(i).getString("var4"));
                user.setUOrigo(listPd.get(i).getString("var5"));
                user.setUBirthday(DateUtil.convertStringToDate(listPd.get(i).getString("var6")));
                userService.insertSelective(user);
            }
        }
        return Result.ok();
    }

5、上传工具类

package com.wnxy.wateraffair.util;

import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;

public class FileUpload {
    /**
     * @param file        //文件对象
     * @param path    //上传路径
     * @param name    //文件名
     * @return  文件名
     */
    public static String fileUp(MultipartFile file, String path, String name){
        //获取文件在服务器的储存位置
        File filePath = new File(path);
        if (!filePath.exists() && !filePath.isDirectory()) {
            filePath.mkdir();
        }
        //获取原始文件名称(包含格式)
        String originalFileName = file.getOriginalFilename();
        //获取文件类型，以最后一个`.`为标识
        String type = originalFileName.substring(originalFileName.lastIndexOf(".") + 1);
        String fileName = name + "." + type;
        //在指定路径下创建一个文件
        File targetFile = new File(path, fileName);
        //将文件保存到服务器指定位置
        try {
            file.transferTo(targetFile);

        } catch (IOException e) {
            e.printStackTrace();
        }
        return fileName;
    }
}

（注意表格中数据格式的处理）