使用Excel批量导入数据

导入依赖

<!--操作excel依赖-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

测试

@RequestMapping("/exportXls")
public void exportXls(HttpServletResponse response) throws Exception{
    Workbook wb=new HSSFWorkbook();
    //设置头信息 告诉浏览器你输出的文件是什么格式的文件
    response.setHeader("Content-Disposition","attachment;filename=employee.xls");
   //创建一张纸
    Sheet sheet = wb.createSheet("员工列表");
    //创建行
    Row row=sheet.createRow(0);
    //创建列
    Cell cell = row.createCell(0);
    //写入内容
    cell.setCellValue("张三");
    wb.write(response.getOutputStream());
}

将数据写入到Excel中

@Override
public Workbook exportXls() {
    List<Employee> employees = employeeMapper.selectAll();
    Workbook wb = new HSSFWorkbook();
    //创建一张纸
    Sheet sheet = wb.createSheet("员工列表");
    //创建行
    //创建标题
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("姓名");
    row.createCell(1).setCellValue("邮箱");
    row.createCell(2).setCellValue("年龄");
    for (int i = 0; i < employees.size(); i++) {
        Employee employee = employees.get(i);
        row = sheet.createRow(i + 1);
        row.createCell(0).setCellValue(employee.getUsername());
        row.createCell(1).setCellValue(employee.getEmail());
        row.createCell(2).setCellValue(employee.getAge());
    }
    return wb;
}

导出还需要告诉浏览器以什么文件格式输出

@RequestMapping("/exportXls")
public void exportXls(HttpServletResponse response) throws Exception{
    //设置头信息 告诉浏览器你输出的文件是什么格式的文件
    response.setHeader("Content-Disposition","attachment;filename=employee.xls");
    Workbook wb = employeeService.exportXls();
    wb.write(response.getOutputStream());
}

导入：
读取Excel的数据，给对象赋值实现添加操作

@Override
public void importXls(MultipartFile file) throws IOException {
    //读取Excel
    HSSFWorkbook wb = new HSSFWorkbook(file.getInputStream());
    //读取纸张 读取第一页
    HSSFSheet sheet = wb.getSheetAt(0);
    //或取最大行数 用于遍历
    int lastRowNum = sheet.getLastRowNum();
    //循环是获取标题的数据
    for (int i = 1; i <= lastRowNum; i++) {
        Employee employee = new Employee();
        //读取每一行
        HSSFRow row = sheet.getRow(i);
        String username = row.getCell(0).getStringCellValue();
        employee.setUsername(username);
        String email = row.getCell(1).getStringCellValue();
        employee.setEmail(email);
        HSSFCell cell = row.getCell(2);
        int age;
        //判断单元格的格式
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            //以数字方式获取
            age = (int) cell.getNumericCellValue();
        } else {
            //以字符串的方式获取
            age = Integer.valueOf(cell.getStringCellValue());
        }
        employee.setAge(age);
        //设置默认密码
        employee.setPassword("1");
        Department dept=new Department();
        dept.setId(3L);
        employee.setDept(dept);
        saveOrUpdate(employee, null);
    }
}