Java操作excel_excelhandler-CSDN博客

本文链接：https://blog.csdn.net/chy_18883701161/article/details/108662372

概述

操作excel的主流方式有3种

JXL 很耗内存
POI 操作excel的主流方式，成熟稳定，可以操作office的全套文档，eg. excel、ppt、word、outlook，虽然在一定程度上可以减少内存占用，但内存占用依旧不低
EasyExcel 阿里开源的工具，内存占用低，提供了更上层的封装，简单易用，但不如POI成熟稳定

前端

上传excel文件

<!-- ./表示项目根路径 -->
<form action="./excel-handler" method="post" enctype="multipart/form-data">
    请选择excel文件：<input name="excelFile" type="file" /><br />
    <button type="submit">上传</button>
</form>

导出数据为excel文件

场景：点击按钮，导出数据为excel文件，并自动下载excel文件

<!-- 写法一 -->
<a href="./exportExcel">下载excel文件</a>

<!-- 写法二 -->
<button><a href="./exportExcel">下载excel文件</a></button>

<!-- 写法三 -->
<button onclick="downloadExcel()">下载excel文件</button>
<script>
    function downloadExcel(){
        window.location = "./exportExcel";
        // 也可以写为 location.href = "./exportExcel";
    }
</script>

后端使用POI操作excel

HSSF：操作2003及之前版本的excel，文件格式是xls
XSSF：操作2007及之后版本的excel，文件格式是xlsx
SXSSF：当行数超过65536后，使用HSSF、XSSF会报错——内存溢出，从POI 3.8开始提供了SXSSF，基于XSSF，但降低了内存占用。数据量较大时，尽量用SXSSF

依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

解析上传的excel文件

/**
 * 处理上传的excel文件
 *
 * @param file
 * @return List<List<String>> 一个List<String>封装一行。返回值一定不为null，如果用户未选择文件、选择的excel文件没有内容，则返回值的isEmpty()为true
 */
@PostMapping("/excel-handler")
@ResponseBody
public List<List<String>> excelHandler(@RequestParam("excelFile") MultipartFile file) {
    //既然new了对象，则返回值一定不为null，可以用 excelData.isEmpty() 判断是否有元素
    List<List<String>> excelData = new LinkedList<>();;

    // 判断用户是否上传了文件。有可能用户只是点击了提交按钮，但并未选择文件
    if (!file.isEmpty()){
        //判断excel文件的类型，创建对应的workbook
        String fileName = file.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf("."));
        Workbook workbook = null;
        try{
            if (".xls".equals(suffix)) {
                workbook = new HSSFWorkbook(file.getInputStream());
            }else if (".xlsx".equals(suffix)){
                workbook = new XSSFWorkbook(file.getInputStream());
            // 上传的文件不是excel文件
            }else{
                return excelData;
            }
        }catch (IOException e) {
            e.printStackTrace();
        }

        if (workbook!=null){
            //可以通过下标或sheet名称获取指定的sheet
            Sheet sheet = workbook.getSheetAt(0);

            //获取首行、最后一行的行号。eg. 第1、2行是空行，第三行才有内容，则首行为2（下标）。如果sheet没有内容，则得到的行号均为-1
            Row row;
            int firstRowNum = sheet.getFirstRowNum();
            int lastRowNum = sheet.getLastRowNum();

            Cell cell;
            int cellNum, lastCellNum;

            // 日期格式化器，根据需求使用
            // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            //遍历所有行
            for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
                //如果这行没有内容，则row是null
                row = sheet.getRow(rowNum);
                if (row != null) {
                    LinkedList<String> rowData = new LinkedList<>();
                    //获得当前行的结束列（不含）
                    lastCellNum = row.getLastCellNum();

                    //遍历当前行的所有列
                    for (cellNum = 0; cellNum < lastCellNum; cellNum++) {
                        cell = row.getCell(cellNum);
                        String cellValue = "";
                        if (cell != null) {
                            //根据数据类型获取单元格的值
                            switch (cell.getCellType()) {
                                case NUMERIC:  //数字。poi中的数字包括数值、日期2种类型
                                    //判断是数字还是日期
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        cellValue = sdf.format(cell.getDateCellValue());
                                    } else {
                                        //取得的数值，如果是整数，会自动加上.0
                                        cellValue = String.valueOf(cell.getNumericCellValue());
                                        //判断是否是整数，是整数就去掉.0，根据需求使用
                                        if (cellValue.endsWith(".0")){
                                            cellValue = cellValue.replace(".0", "");
                                        }
                                    }
                                    break;
                                case STRING:  //字符串
                                    cellValue = String.valueOf(cell.getStringCellValue());
                                    break;
                                case BOOLEAN:  //布尔
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                // 公式、空值、错误、不能识别的类型，解析为默认值空串。如果要解析为null，将cellValue的初始值设置为null即可
                            }
                        }
                        rowData.add(cellValue);
                    }
                    excelData.add(rowData);
                }
            }
        }
    }
    return excelData;
}

写的基本操作

基本使用

//创建一个工作簿。操作xlsx用XSSF，操作xls用HSSF
Workbook workbook=new XSSFWorkbook();

//创建一个工作表，参数为sheet页的名字，缺省默认为sheet0到n
Sheet sheet1 = workbook.createSheet();
Sheet sheet2 = workbook.createSheet("用户信息表");

//创建一行，参数指定是第几行（下标）
Row row=sheet1.createRow(0);

//创建一个单元格，参数指定列的坐标（是这行的第几个单元格）
Cell cell=row.createCell(0);
//设置单元格的内容
cell.setCellValue("张三");

//可以连写
row.createCell(1).setCellValue("李四");

//输出到文件
FileOutputStream fos= null;
try {
    fos= new FileOutputStream("C:\\Users\\chy\\Desktop\\1.xlsx");
    //如果文件不存在，会自动创建，但要保证前面的目录存在，不然会报 FileNotFoundException
    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
}finally {
    try{
        workbook.close();
        if (fos!=null){
            fos.close();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

设置样式

Workbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet("用户信息表");
//设置默认列宽，单位字符数
sheet.setDefaultColumnWidth(15);

//单元格样式
Font font = workbook.createFont();
font.setBold(true);
CellStyle style = workbook.createCellStyle();
style.setFont(font);

//表头
String[] titleList = {"姓名", "手机号", "住址"};
Row row = sheet.createRow(0);
for (int i = 0; i < titleList.length; i++) {
    Cell cell = row.createCell(i);
    cell.setCellStyle(style);
    cell.setCellValue(titleList[i]);
}

FileOutputStream fos = null;
try {
    fos = new FileOutputStream("C:\\Users\\chy\\Desktop\\1.xlsx");
    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
}finally {
    try{
        workbook.close();
        if (fos!=null){
            fos.close();
        }
    }catch (IOException e) {
        e.printStackTrace();
    }
}

导出数据到excel文件中

@GetMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) {
    //表格数据一般是前端传递查询参数，后端controller用参数接收查询参数，controller->service->dao 根据参数查询得到表格数据
    //此处略过，写一些测试数据
    List<User> userList = new LinkedList<>();
    userList.add(new User(1L, "张三", new Date(), 170, 60.5));
    userList.add(new User(2L, "李四", new Date(), 170, 60.5));
    userList.add(new User(3L, "王五", new Date(), 170, 60.5));

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("用户信息表");
    sheet.setDefaultColumnWidth(15);

    //表头的单元格样式：粗体、文本居中
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBold(true);
    style.setFont(font);
    style.setAlignment(HorizontalAlignment.CENTER);

    //设置表头
    String[] titleList = {"学号", "姓名", "出生日期", "身高", "体重"};
    Row row = sheet.createRow(0);
    for (int i = 0; i < titleList.length; i++) {
        sheet.setDefaultColumnStyle(i,style);
        Cell cell = row.createCell(i);
        cell.setCellStyle(style);
        cell.setCellValue(titleList[i]);
    }

    //写入数据
    User user;
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
    for (int i=0;i<userList.size();i++){
        user = userList.get(i);
        row = sheet.createRow(i + 1);
        row.createCell(0).setCellValue(user.getId());
        row.createCell(1).setCellValue(user.getName());
        //日期时间类型要格式化一下，才能正确写入
        row.createCell(2).setCellValue(sdf.format(user.getBirthday()));
        row.createCell(3).setCellValue(user.getHeight());
        row.createCell(4).setCellValue(user.getWeight());
    }

    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/x-download");
    response.addHeader("Cache-Control", "no-cache");

    String fileName = "学生信息表" + sdf.format(new Date()) + ".xlsx";
    OutputStream os = null;
    try{
        //设置下载保存的文件名，文件名需要编码以防止中文乱码
        response.setHeader("Content-Disposition", "attachment;fileName=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();

        os = response.getOutputStream();
        workbook.write(os);
        os.flush();
    }catch (IOException e) {
        e.printStackTrace();
    }finally {
        try {
            workbook.close();
            if (os!=null) {
                os.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

说明：设置单元格的对齐方式，我使用wps打开效果不对，尤其是tbody的单元格对齐，不知道是poi版本的原因，还是wps的原因。

后端使用EasyExcel操作excel

依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

实体类

用于映射表格中的数据

@Getter
@Setter
@ToString
@AllArgsConstructor
@ColumnWidth(15)  //指定列宽，可用在类上、字段上
@HeadFontStyle(fontHeightInPoints = 12)  //指定表头字体设置，此处设置字号
public class UserVO {

    //index指定excel中的列索引，value指定列名。解析excel时优先使用index，导出为excel时自动使用value作为列名
    @ExcelProperty(index = 0, value = "学号")
    private Long id;

    @ExcelProperty(index = 1, value = "姓名")
    private String name;

    @ExcelProperty(index = 2, value = "生日")
    @DateTimeFormat("yyyy.MM.dd")   //指定日期格式
    @ColumnWidth(20)  //指定列宽
    private Date birthday;

    @ExcelProperty(index = 3, value = "身高")
    @NumberFormat("#")  //指定数字格式
    private Double height;

    @ExcelProperty(index = 4, value = "体重")
    private Double weight;

}

@DateTimeFormat、@NumberFormat导入的是阿里的，这2个注解是在导出为excel时格式化数据的。

@NumberFormat("#")  //整数
@NumberFormat("#.##")  //保留2位小数
@NumberFormat("#.##%")  //百分数格式，%前面部分保留2位小数

监听器

用于解析上传的excel文件

/**
 * 注意：这个类每次使用时都要重新new，不要放到spring容器中
 */
public class UserVOListener extends AnalysisEventListener<UserVO> {

    private List<UserVO> userVOList = new ArrayList<>();

    /**
     * 用于将读取的excel数据保存到数据库，也可以是dao。此处不能标注自动装配的注解，这个成员变量要手动调用构造函数注入
     */
    private UserService userService;

    /**
     * 1次存2000条
     */
    private static final int BATCH_COUNT = 2000;
    
    
    public UserVOListener() {
        
    }

    
    public UserVOListener(UserService userService) {
        this.userService = userService;
    }

    
    /**
     * 解析完1行的数据后会自动调用此函数，data是封装好的读取到的一行数据
     */
    @Override
    public void invoke(UserVO data, AnalysisContext context) {
        System.out.println(data);
        userVOList.add(data);
        // 达到BATCH_COUNT时入库1次
        if (userVOList.size() >= BATCH_COUNT) {
            userService.saveUserVO(userVOList);
            // 入库后清空userVOList
            userVOList.clear();
        }
    }

    
    /**
     * 解析完整个excel文件后会自动调用此函数
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 剩下的不足BATCH_COUNT的数据也入库
        userService.saveUserVO(userVOList);
    }
    

}

如果数据不入库，去掉入库操作即可

controller

@Controller
public class ExcelController {

    @Autowired
    private UserService userService;

    /**
     * 解析上传的excel文件
     */
    @PostMapping("/excel-handler")
    @ResponseBody
    public String excelHandler(@RequestParam("excelFile") MultipartFile file) {
        InputStream is = null;
        try {
            is = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
            return "fail";
        }

        if (is != null) {
            // UserVOListener userVOListener = new UserVOListener();  //不入库
            UserVOListener userVOListener = new UserVOListener(userService);  //要入库
            EasyExcel.read(is, UserVO.class, userVOListener).sheet().doRead();
        }

        return "success";
    }


    /**
     * 导出为excel文件
     */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) {
        //list应该是传入查询参数，调用dao查询得到的，此处为模拟数据
        List<UserVO> userVOList = new LinkedList<>();
        userVOList.add(new UserVO(1L, "张三", new Date(), 170.3, 60.5));
        userVOList.add(new UserVO(2L, "李四", new Date(), 170.3, 60.5));
        userVOList.add(new UserVO(3L, "王五", new Date(), 170.3, 60.5));

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String fileName = "学生信息表" + sdf.format(new Date()) + ".xlsx";
        try{
            //文件名编码，防止中文乱码
            response.setHeader("Content-disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
            ServletOutputStream os = response.getOutputStream();
            EasyExcel.write(os, UserVO.class).sheet("学生信息表").doWrite(userVOList);
        }catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    

}