excel文件导出和导入

pom.xml添加依赖

@RestController
@RequestMapping(value = "/excel")
public class ExpImpExcelController {

    // 导出user表
    @GetMapping(value = "/export/user")
    public String getUser(HttpServletResponse response) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 给sheet起个名字
        HSSFSheet sheet = workbook.createSheet("用户表");

        // 创建表头
        createTitle(workbook, sheet);

        // 准备数据，写入sheet
        List<User> userList = new ArrayList<User>();
        User user1 = new User("110", "zhangsan1", "张三1", new Date());
        User user2 = new User("120", "zhangsan2", "张三1", new Date());
        User user3 = new User("130", "zhangsan3", "张三1", new Date());
        userList.add(user1);
        userList.add(user2);
        userList.add(user3);
        List<User> rows = userList;
        // 设置日期格式
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("yyyy年MM月dd日"));
        // 新增数据行，并且设置单元格数据
        int rowNum = 1;
        for (User user : rows) {
            HSSFRow row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUserName());
            row.createCell(2).setCellValue(user.getNickName());
            HSSFCell cell = row.createCell(3);
            cell.setCellValue(user.getCreateTime());
            cell.setCellStyle(style);
            rowNum++;
        }

        // 生成excel文件
        String fileName = "用户表.xls";
        buildExcelFile(fileName, workbook);

        // 浏览器下载excel
        downloadExcelFile(fileName, workbook, response);

        return "";
    }

    // 创建表头
    private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(0);
        // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(3, 17 * 256);

        // 设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("ID");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("用户名");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("昵称");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("创建时间");
        cell.setCellStyle(style);
    }

    // 生成excel文件
    protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    // 浏览器下载excel
    protected void downloadExcelFile(String filename, HSSFWorkbook workbook, HttpServletResponse response)
            throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    //导入excel
    @PostMapping("/import/user")
    public boolean addUser(@RequestParam("file") MultipartFile file) {
        boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = batchImport(fileName, file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return a;
    }

    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
        Workbook wb = null;
        try {
            List<User> userList = new ArrayList<User>();
            if (!fileName.matches("^.+\.(?i)(xls)$") && !fileName.matches("^.+\.(?i)(xlsx)$")) {
                throw new RuntimeException("上传文件格式不正确");
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();

            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                throw new RuntimeException("sheet页内容为空");
            }
            User user;
            // 从第二行开始解析单元格
            for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                user = new User();
//              excel  单元格类型            
//            CELL_TYPE_NUMERIC    数值型    0
//            CELL_TYPE_STRING    字符串型    1
//            CELL_TYPE_FORMULA    公式型    2
//            CELL_TYPE_BLANK    空值        3
//            CELL_TYPE_BOOLEAN    布尔型    4
//            CELL_TYPE_ERROR    错误        5

                if (row.getCell(0).getCellType() != Cell.CELL_TYPE_STRING) {
                    throw new RuntimeException("导入失败(第" + (r + 1) + "行,ID请设为文本格式)");
                }
                String id = row.getCell(0).getStringCellValue();
                if (id == null || id.isEmpty()) {
                    throw new RuntimeException("导入失败(第" + (r + 1) + "行,ID未填写)");
                }

                String userName = row.getCell(1).getStringCellValue();
                if (userName == null || userName.isEmpty()) {
                    throw new RuntimeException("导入失败(第" + (r + 1) + "行,用户名未填写)");
                }

                String nickName = row.getCell(2).getStringCellValue();
                if (nickName == null || nickName.isEmpty()) {
                    throw new RuntimeException("导入失败(第" + (r + 1) + "行,昵称未填写)");
                }

                Date createTime;
                if (row.getCell(3).getCellType() != 0) {
                    throw new RuntimeException("导入失败(第" + (r + 1) + "行,创建日期格式不正确或未填写)");
                } else {
                    createTime = row.getCell(3).getDateCellValue();
                }
                user.setId(id);
                user.setUserName(userName);
                user.setNickName(nickName);
                user.setCreateTime(createTime);
                userList.add(user);
            }
        } finally {
            wb.close();
        }

        return true;
    }
}

public class User {

    private String id;

    private String userName;
    
    private String nickName;
    
    private Date createTime;
    
}