poi与easyexcel的基本使用

1.poi

先加载到内存，再写入文件

xlsx使用的是XSSF

Workbook workbook = new XSSFWorkbook(inputStream);

xls使用的是HSSF

 Workbook workbook = new HSSFWorkbook(inputStream);

读取一个sheet中的所有数据

Sheet sheet = book.getSheetAt(page);
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
  Row row = sheet.getRow(i);
  for (int j = 0; j < row.getLastCellNum(); j++) {
      Cell cell = row.getCell(j);
      System.out.println(cell.toString());
  }
}

将数据写入

sheet.creatRow(第几行)
row.createCell(第几列)

生成表

FileOutputStream fileOutputStream = new FileOutputStream("...xlsx");
book.writer(fileOutputStream)

poi的excel工具类

public class ExcelUtils {



    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

    /**
     * 输出数据到自定义模版的Excel输出流
     *
     * @param excelTemplate 自定义模版文件
     * @param data          数据
     * @param outputStream  Excel输出流
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static void writeDataToTemplateOutputStream(File excelTemplate, List<List<Object>> data, OutputStream outputStream) throws IOException {
        Workbook book = ExcelUtils.getWorkbookFromExcel(excelTemplate);
        ExcelUtils.writeDataToWorkbook(null, data, book, 0);
        ExcelUtils.writeWorkbookToOutputStream(book, outputStream);
    }

    /**
     * 从Excel文件获取Workbook对象
     *
     * @param excelFile Excel文件
     * @return Workbook对象
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static Workbook getWorkbookFromExcel(File excelFile) throws IOException {
        try (
                InputStream inputStream = new FileInputStream(excelFile);
        ) {

            if (excelFile.getName().endsWith(XLS)) {
                return new HSSFWorkbook(inputStream);
            } else if (excelFile.getName().endsWith(XLSX)) {
                return new XSSFWorkbook(inputStream);
            } else {
                throw new IOException("文件类型错误");
            }
        }
    }

    /**
     * 把Workbook对象内容输出到Excel文件
     *
     * @param book Workbook对象
     * @param file Excel文件
     * @throws FileNotFoundException 找不到文件异常，文件已创建，实际不存在该异常
     * @throws IOException           输入输出异常
     */
    public static void writeWorkbookToFile(Workbook book, File file) throws FileNotFoundException, IOException {
        if (!file.exists()) {
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            file.createNewFile();
        }
        try (
                OutputStream outputStream = new FileOutputStream(file);
        ) {
            writeWorkbookToOutputStream(book, outputStream);
        }
    }

    /**
     * 把Workbook对象输出到Excel输出流
     *
     * @param book         Workbook对象
     * @param outputStream Excel输出流
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static void writeWorkbookToOutputStream(Workbook book, OutputStream outputStream) throws IOException {
        book.write(outputStream);
    }

    /**
     * 输出数据到Workbook对象中指定页码
     *
     * @param title 标题，写在第一行，可传null
     * @param data  数据
     * @param book  Workbook对象
     * @param page  输出数据到Workbook指定页码的页面数
     */
    public static void writeDataToWorkbook(List<String> title, List<List<Object>> data, Workbook book, int page) {
        Sheet sheet = book.getSheetAt(page);

        Row row = null;
        Cell cell = null;

        // 设置表头
        if (null != title && !title.isEmpty()) {
            row = sheet.getRow(0);
            if (null == row) {
                row = sheet.createRow(0);
            }

            for (int i = 0; i < title.size(); i++) {
                cell = row.getCell(i);
                if (null == cell) {
                    cell = row.createCell(i);
                }
                cell.setCellValue(title.get(i));
            }
        }

        List<Object> rowData = null;
        for (int i = 0; i < data.size(); i++) {

            row = sheet.getRow(i + 1);
            if (null == row) {
                row = sheet.createRow(i + 1);
            }
            rowData = data.get(i);
            if (null == rowData) {
                continue;
            }
            for (int j = 0; j < rowData.size(); j++) {
                cell = row.getCell(j);
                if (null == cell) {
                    cell = row.createCell(j);
                }
                setValue(cell, rowData.get(j));
            }
        }
    }

    /**
     * 读取Excel文件第一页
     *
     * @param pathname 文件路径名
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readExcelFirstSheet(String pathname) throws IOException {
        File file = new File(pathname);
        return readExcelFirstSheet(file);

    }

    /**
     * 读取Excel文件第一页
     *
     * @param file Excel文件
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readExcelFirstSheet(File file) throws IOException {
        try (
                InputStream inputStream = new FileInputStream(file);
        ) {
            if (file.getName().endsWith(XLS)) {
                return readXlsFirstSheet(inputStream);
            } else if (file.getName().endsWith(XLSX)) {
                return readXlsxFirstSheet(inputStream);
            } else {
                throw new IOException("文件类型错误");
            }
        }

    }

    /**
     * 读取xls格式Excel文件第一页
     *
     * @param inputStream Excel文件输入流
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readXlsFirstSheet(InputStream inputStream) throws IOException {
        Workbook workbook = new HSSFWorkbook(inputStream);
        return readExcelFirstSheet(workbook);
    }

    /**
     * 读取xlsx格式Excel文件第一页
     *
     * @param inputStream Excel文件输入流
     * @return 第一页数据集合
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static List<List<Object>> readXlsxFirstSheet(InputStream inputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        return readExcelFirstSheet(workbook);
    }

    /**
     * 读取Workbook第一页
     *
     * @param book Workbook对象
     * @return 第一页数据集合
     */
    public static List<List<Object>> readExcelFirstSheet(Workbook book) {
        return readExcel(book, 0);
    }

    /**
     * 读取指定页面的Excel
     *
     * @param book Workbook对象
     * @param page 页码
     * @return 指定页面数据集合
     */
    public static List<List<Object>> readExcel(Workbook book, int page) {
        List<List<Object>> list = new ArrayList<>();
        Sheet sheet = book.getSheetAt(page);
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            // 如果当前行为空，则加入空，保持行号一致
            if (null == row) {
                list.add(null);
                continue;
            }
            List<Object> columns = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                columns.add(getValue(cell));
            }
            list.add(columns);
        }
        return list;
    }

    /**
     * 解析单元格中的值
     *
     * @param cell 单元格
     * @return 单元格内的值
     */
    private static Object getValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        Object value = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                // 日期类型，转换为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                }
                // 数值类型
                else {

                    // 默认返回double，创建BigDecimal返回准确值
                    value = new BigDecimal(cell.getNumericCellValue());
                }
                break;
            default:
                value = cell.toString();
                break;
        }
        return value;
    }

    /**
     * 设置单元格值
     *
     * @param cell  单元格
     * @param value 值
     */
    private static void setValue(Cell cell, Object value) {
        if (null == cell) {
            return;
        }
        if (null == value) {
            cell.setCellValue((String) null);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue(FORMAT.format((Date) value));
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

}

2.easyExcel

一行一行返回

1.读excel

controller层调用service

@RequestMapping(value = "/uploadRank", method = RequestMethod.POST)
public CommonResponse fileRank(@RequestParam("uploadFile") MultipartFile uploadFile, HttpServletRequest request) throws IOException {
    boolean b = studentrankService.readExcel(uploadFile);
    if (!b){
      throw new BusinessException(403, "导入错误");
    }
    return CommonResponse.ok("导入成功");
}

service层

@Override
public boolean readExcel(MultipartFile file) {
    try {
        //三个参数  文件流对象，对应的类.class， easyexcel的监听器
        EasyExcel.read(file.getInputStream(),Studentrank.class,new EasyExcelListener()).sheet().
                //跳过头部从第一行开始
                headRowNumber(1).doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
    return true;
}

实体类

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Studentrank对象", description="")
public class Studentrank implements Serializable {

    @ExcelIgnore
    @TableId(value = "Id", type = IdType.ASSIGN_ID)
    private String id;

    @ExcelProperty(value = "理科分数", index = 0)
    @ApiModelProperty(value = "理科分数")
    private String scienceScore;

    @ExcelProperty(value = "理科人数", index = 1)
    @ApiModelProperty(value = "理科人数")
    private String scienceNumber;

    @ExcelProperty(value = "理科位次", index = 2)
    @ApiModelProperty(value = "理科位次")
    private String scienceRank;

    @ExcelProperty(value = "文科分数", index = 3)
    @ApiModelProperty(value = "文科分数")
    private String liberalScore;

    @ExcelProperty(value = "文科人数", index = 4)
    @ApiModelProperty(value = "文科人数")
    private String liberalNumber;

    @ExcelProperty(value = "文科位次", index = 5)
    @ApiModelProperty(value = "文科位次")
    private String liberalRank;

    @ExcelProperty(value = "文科位次", index = 6)
    @ApiModelProperty(value = "年度")
    private String year;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getScienceScore() {
        return scienceScore;
    }

    public void setScienceScore(String scienceScore) {
        this.scienceScore = scienceScore;
    }

    public String getScienceNumber() {
        return scienceNumber;
    }

    public void setScienceNumber(String scienceNumber) {
        this.scienceNumber = scienceNumber;
    }

    public String getScienceRank() {
        return scienceRank;
    }

    public void setScienceRank(String scienceRank) {
        this.scienceRank = scienceRank;
    }

    public String getLiberalScore() {
        return liberalScore;
    }

    public void setLiberalScore(String liberalScore) {
        this.liberalScore = liberalScore;
    }

    public String getLiberalNumber() {
        return liberalNumber;
    }

    public void setLiberalNumber(String liberalNumber) {
        this.liberalNumber = liberalNumber;
    }

    public String getLiberalRank() {
        return liberalRank;
    }

    public void setLiberalRank(String liberalRank) {
        this.liberalRank = liberalRank;
    }

    public String getYear() {
        return year;
    }

    public void setYear(String year) {
        this.year = year;
    }
}

easyexcel监听器

@Slf4j
@Component
public class EasyExcelListener extends AnalysisEventListener<Studentrank> {

    private static StudentrankService studentrankService;

    public EasyExcelListener (){
    }

    @Autowired
    public void setStudentrankService(StudentrankService studentrankService){
        EasyExcelListener.studentrankService = studentrankService;
    }
    /**
     *  设置最大数
     */
    private static final int BATCH_COUNT = 100;

    /**
     *   临时缓存List
     */
    public static List<Studentrank> importList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        log.info(exception.getMessage());
    }

    @Override
    public void invoke(Studentrank studentrank, AnalysisContext analysisContext) {
        importList.add(studentrank);
        if (importList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            importList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }

    private void saveData(){
        log.info("{}条数据，开始存储数据库！", importList.size());
        boolean b = studentrankService.saveDate(importList);
        if (b){
            log.info("存储数据库成功！");
        } else {
            log.info("存储数据库失败！");
        }
    }
}

数据库插入多条数据 mybatis-plus

@Override
public boolean saveDate(List<Studentrank> studentrank) {
    boolean b = saveBatch(studentrank);
    return b;
}

数据库插入多条数据 mybatis

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.school.zhaoban.mapper.StudentrankMapper">
    <insert id="saveAll" parameterType="java.util.ArrayList">
        insert into studentrank (science_score, science_number, science_rank, liberal_score, liberal_number, liberal_rank, `year`)
        values 
        <foreach collection="list" index="index" separator="," item="item">
            (#item.science_score, #item.science_number, #item.science_rank, #item.liberal_score, #item.liberal_number, #item.liberal_rank, #item.year)
        </foreach>
    </insert>
</mapper>

2.写excel

实体类 注解@ExcelProperty(“属性名”)

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="UserInfo对象", description="")
public class UserInfo implements Serializable {

    @ExcelProperty("编号")
    @TableId(value = "Id", type = IdType.ASSIGN_ID)
    private String id;

    @ExcelProperty("用户姓名")
    @ApiModelProperty(value = "用户姓名")
    @TableField("userName")
    private String username;

    @ApiModelProperty(value = "用户图像")
    @TableField("userAvatar")
    private String useravatar;

    @ExcelIgnore
    @ApiModelProperty(value = "小程序身份识别")
    @TableField("openId")
    private String openid;

    @ExcelProperty("电话")
    @ApiModelProperty(value = "电话")
    @TableField("phoneNumber")
    private String phonenumber;

    @ExcelIgnore
    @ExcelProperty("性别")
    @ApiModelProperty(value = "性别")
    private String sex;

    @ExcelProperty("加入时间")
    @ApiModelProperty(value = "插入时间")
    @TableField(value = "creatTime", fill = FieldFill.INSERT)
    private String creattime;

    @ExcelProperty("更新时间")
    @ApiModelProperty(value = "更新时间")
    @TableField(value = "updateTime", fill = FieldFill.INSERT_UPDATE)
    private String updatetime;

    @ExcelProperty("QQ")
    @TableField("QQ")
    private String qq;

    @ExcelProperty("高中学校")
    @ApiModelProperty(value = "高中学校")
    private String highSchool;

    @ExcelProperty("市")
    @ApiModelProperty(value = "市")
    private String city;
}

controller层

 //查询所有数据
List<UserInfo> list = userService.list();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
String fileName = "用户信息";
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");
                 //输出流对象                对应的类
EasyExcel.write(response.getOutputStream(), UserInfo.class).autoCloseStream(Boolean.FALSE).sheet("用户信息").doWrite(list);