java基于poi的Excel导入导出功能

一、Excel的导入导出工具类

1、Excel导出工具类

package com.djw.excel.utils;

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

/**
 * @Author djw
 * @Description //TODO
 * @Date 2018/9/5 15:52
 */
public class ExcelExport {

    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @return
     */
    public static HSSFWorkbook createWorkbook(String sheetName,String []title,String [][]values){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
}

2、Excel导入工具类

package com.djw.excel.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

/**
 * @Author djw
 * @Description //TODO
 * @Date 2018/9/5 16:16
 */
public class ExcelImport {

    /**
     * @param dest 表格文件
     * @param cellLength 一行多少个单元格
     * @return 返回list集合
     * @throws Exception
     */
    public static List<Map<Integer,String>> read(File dest, Integer cellLength) throws Exception{
        Workbook wookbook = null;
        FileInputStream fis = null;
        int cellType = 1;
        try {
            fis = new FileInputStream(dest);
            //用HSSF来处理，有异常即为xlsx格式，用XSSF处理
            wookbook = new HSSFWorkbook(fis);//得到工作簿
            cellType = HSSFCell.CELL_TYPE_STRING;
        } catch (Exception e) {
            try {
                fis = new FileInputStream(dest);//这里不创建输入流就会报错stream close
                wookbook = new XSSFWorkbook(fis);
                cellType = XSSFCell.CELL_TYPE_STRING;
            } catch (Exception e1) {
                //返回文件格式错误异常
                throw new Exception("This file is not in excel format");
            }//得到工作簿
        } finally {
            fis.close();
        }
        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        //要获得属性
        List<Map<Integer,String>> list = new ArrayList<Map<Integer,String>>();
        Map<Integer,String> map = null;
        //获得所有数据
        //从第x行开始获取
        for(int x = 1 ; x <= totalRowNum ; x++){
            map = new HashMap<Integer,String>();
            //获得第i行对象
            Row row = sheet.getRow(x);
            //如果一行里的所有单元格都为空则不放进list里面
            int a = 0;
            for(int y=0;y<cellLength;y++){
                Cell cell = row.getCell(y);
                if(cell == null){
                    map.put(y,"");
                }else{
                    cell.setCellType(cellType);
                    map.put(y, cell.getStringCellValue().toString());
                }
                if(map.get(y)==null||"".equals(map.get(y))){
                    a++;
                }
            }
            if(a!=cellLength){
                list.add(map);
            }
        }
        return list;
    }
}

二、controller层的具体操作方法

1、为了演示，首先创建了一个user实体类

package com.djw.excel.entiy;

/**
 * @Author djw
 * @Description 演示所创建的对象
 * @Date 2018/9/5 14:52
 */
public class User {
    Integer userId;
    String userName;
    String sex;
    Integer age;
    String school;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSchool() {
        return school;
    }

    public void setSchool(String school) {
        this.school = school;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", school='" + school + '\'' +
                '}';
    }
}

2、具体实现方法

package com.djw.excel.controller;

import com.djw.excel.entiy.User;
import com.djw.excel.utils.ExcelExport;
import com.djw.excel.utils.ExcelImport;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("excel")
@Api(value = "ecxel相关操作",description = "excel相关操作")
public class ExcelController {

//    @GetMapping("getExcel")
//    @ApiOperation(value = "导出Excel",notes = "导出Excel")
//    public void getExcel(HttpServletResponse response) {
//        //excel标题
//        String[] title = {"名称", "性别", "年龄", "学校", "班级"};
//        //excel文件名
//        String fileName = "学生信息表";
//        String sheetName = "学生信息表";
//        String[][] values = {{"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"},
//                {"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"}, {"张三", "男", "18", "家里蹲大学", "14电信1班"},
//                {"张三", "男", "18", "家里蹲大学", "14电信1班"}};
//        //创建HSSFWorkbook
//        HSSFWorkbook wb = ExcelUtil.createWorkbook(sheetName, title, values);
//        //响应到客户端
//        try {
//            //设置编码、输出文件格式
//            response.reset();
//            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
//            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
//            OutputStream os = response.getOutputStream();
//            wb.write(os);
//            os.flush();
//            os.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    @GetMapping("createExcel")
    @ApiOperation(value = "创建excel表格",notes = "创建Excel表格")
    public void createExcel(HttpServletResponse response){
        //添加测试数据
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 20; i++) {
            User user = new User();
            user.setUserId(i);
            user.setUserName("张三"+i);
            user.setSex("男");
            user.setAge(i);
            user.setSchool("学校"+i);
            userList.add(user);
        }
        //excel标题
        String[] title = {"id", "姓名", "性别", "年龄", "学校"};
        String[][] objects = new String[userList.size()][title.length];
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);
            objects[i][0] = String.valueOf(user.getUserId());
            objects[i][1] = user.getUserName();
            objects[i][2] = user.getSex();
            objects[i][3] = String.valueOf(user.getAge());
            objects[i][4] = user.getSchool();
        }
        String fileName = "学生信息表";
        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelExport.createWorkbook(fileName, title, objects);
        //响应到客户端
        try {
            //设置编码、输出文件格式
            response.reset();
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @GetMapping("importExcel")
    @ApiOperation(value = "导入Excel",notes = "将数据以Excel的形式导入到数据库中")
    public Boolean importExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception {
        //获取用户上传表格的地址，执行完程序后表格将会删除，避免占用内存
        String filePath = request.getSession().getServletContext().getRealPath("/");
        //根据地址和文件名准确获取用户上传的表格
        File tempFile = new File(filePath+file.getOriginalFilename());
        try {
            //将MultipartFile转换为File类型
            file.transferTo(tempFile);
            List<Map<Integer,String>> dataList = ExcelImport.read(tempFile,5);
            User user = new User();
            for (int i = 0; i < dataList.size(); i++) {
                //此处的取值顺序取决于Excel表的表头顺序，一一对应
                //学生id  姓名  性别  年龄  学校
                user.setUserId(Integer.valueOf(dataList.get(i).get(0)));
                user.setUserName(dataList.get(i).get(1));
                user.setSex(dataList.get(i).get(2));
                user.setAge(Integer.valueOf(dataList.get(i).get(3)));
                user.setSchool(dataList.get(i).get(4));
                //此处应该将user对象插入数据库中
                //UserService.insert(user);
            }
            //执行完程序后删除用户上传文件
            tempFile.delete();
        } catch (Exception e) {
            tempFile.delete();
            throw new Exception("批量录入用户失败，请检查表格中的数据是否和数据库中的数据冲突！");
        }
        return true;
    }
}

3、相关jar包

<!--Excel导入导出相关jar包 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.7</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.7</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.7</version>
</dependency>

最后附上我的小demo项目地址：https://github.com/djw2014/ExcelUtils