springboot excel工具类（包含导入和导出）

西琳的小贝

已于 2024-09-02 15:34:41 修改

阅读量166

点赞数 5

于 2024-09-02 13:36:16 首次发布

本文链接：https://blog.csdn.net/qq_59523971/article/details/141816475

版权

第一步：

先安装依赖

<!-- Poi依赖，操作Excel -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>

第二步：

新增如下 ExcelUtils.class

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 模块：Excel处理工具类
 * 作者：PadoPhyllis
 * 日期：2024/2/1
 */
@Component
@Slf4j
public class ExcelUtils {

    /**
     * 功能：根据行数和列数读取Excel
     * @param file 前端发送的文件
     * @param StatrRow 读取的开始行数（默认填0）
     * @param EndRow 读取的结束行数（填-1为全部）
     * @param ExistTop 是否存在头部（如存在则读取数据时会把头部拼接到对应数据，若无则为当前列数）
     * @return 返回一个List<Map<Integer,Object>>
     */
    public List<Map<Integer, Object>> readExcel(MultipartFile file, int StatrRow, int EndRow, boolean ExistTop){

        // 判断输入的开始值是否少于等于结束值
        if (StatrRow > EndRow && EndRow != -1) {
            log.warn("(╯▔＾▔)╯ => 输入的开始行值比结束行值大，请重新输入正确的行数");
            return null;
        }

        // 声明返回的结果集
        List<Map<Integer, Object>> result = new ArrayList<>();
        // 声明一个工作薄
        Workbook workbook = null;
        // 声明一个文件输入流
        InputStream is = null;

        String fileName = null;

        try{

            fileName = file.getOriginalFilename();

            // 获取Excel后缀名，判断文件类型
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);

            // 获取Excel工作簿
            workbook = getWorkbook(file.getInputStream(), fileType);

            // 处理Excel内容
            result = getListData(workbook, StatrRow, EndRow, ExistTop);


        }catch (Exception e){

            log.warn("(╯▔＾▔)╯ => 解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());

        }finally {

            try {

                if (null != workbook) {
                    workbook.close();
                }

                if (null != is) {
                    is.close();
                }

            } catch (Exception e) {

                log.warn("(╯▔＾▔)╯ => 关闭数据流出错！错误信息：" + e.getMessage());
                return null;


            }

        }

        return result;
    }

    /**
     * 功能：导出excel表格(只有一个sheet)
     * @param sheetName sheet名字
     * @param title 标题
     * @param values 数据
     * @param wb HSSFWorkbook对象(默认null)
     * @return 返回一个HssFWorkbook
     */
    public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb){

        // 创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

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

        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);

        // 基础样式
        HSSFCellStyle titleStyle = getStyle(wb,true);
        HSSFCellStyle textStyle = getStyle(wb,false);

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

        // 存储最大列宽
        Map<Integer,Integer> maxWidth = new HashMap<>();

        // 创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(titleStyle);
            maxWidth.put(i,cell.getStringCellValue().getBytes().length  * 256 + 200);
        }

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

                if (cell.getStringCellValue().getBytes().length > 255){
                    maxWidth.put(j,254 * 256);
                }else{
                    if (maxWidth.get(j)<cell.getStringCellValue().getBytes().length * 256 + 200) {
                        maxWidth.put(j,cell.getStringCellValue().getBytes().length * 256 + 200);
                    }
                }
            }
        }

        // 列宽自适应
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, maxWidth.get(i));
        }

        return wb;
    }

    /**
     * 功能：导出excel表格(多个sheet)
     * @param list  List<ArrayWrapper> list = new ArrayList<>();
     * @param wb HSSFWorkbook对象(默认null)
     * @return 返回一个HssFWorkbook
     */
    public HSSFWorkbook getHSSFWorkbook(List<ExcelEntity> list, HSSFWorkbook wb){

        // 创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 基础样式
        HSSFCellStyle titleStyle = getStyle(wb,true);
        HSSFCellStyle textStyle = getStyle(wb,false);

        // 初始化
        HSSFSheet sheet;
        HSSFRow row;
        HSSFCell cell;
        Map<Integer,Integer> maxWidth;
        String[] title;
        String[][] values;

        // 循环处理list
        for (ExcelEntity e: list
        ) {

            // 在workbook中添加一个sheet,对应Excel文件中的sheet
            sheet = wb.createSheet(e.getSheetName());

            // 在sheet中添加表头第0行
            row = sheet.createRow(0);

            // 声明列对象
            cell = null;

            // 存储最大列宽
            maxWidth = new HashMap<>();

            // 创建标题
            title = e.getTitle();
            for(int i=0;i<title.length;i++){
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(titleStyle);
                maxWidth.put(i,cell.getStringCellValue().getBytes().length * 256 + 200);
            }

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

                    if (cell.getStringCellValue().getBytes().length > 255){
                        maxWidth.put(j,254 * 256);
                    }else{
                        if (maxWidth.get(j)<cell.getStringCellValue().getBytes().length * 256 + 200) {
                            maxWidth.put(j,cell.getStringCellValue().getBytes().length * 256 + 200);
                        }
                    }
                }
            }

            // 列宽自适应
            for (int i = 0; i < title.length; i++) {
                sheet.setColumnWidth(i, maxWidth.get(i));
            }

        }

        return wb;
    }


    /**
     * 功能：style初始化
     * @param wb HSSFWorkbook
     * @param isTitle 是否为标题
     * @return HSSFCellStyle
     */
    private HSSFCellStyle getStyle(HSSFWorkbook wb,Boolean isTitle){
        HSSFCellStyle style = wb.createCellStyle();

        style.setAlignment(HorizontalAlignment.CENTER);  // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);  // 垂直居中
        style.setWrapText(true);  // 自动化换行

        // 设置字体
        Font font = wb.createFont();
        font.setFontName("Arial");
        if (isTitle){
            font.setFontHeightInPoints((short) 12);
            font.setColor(IndexedColors.BLACK.getIndex());
            font.setBold(true);  // 设置字体加粗

        }else{
            font.setFontHeightInPoints((short) 11);
            font.setColor(IndexedColors.GREY_80_PERCENT.getIndex());
        }
        style.setFont(font);

        return style;
    }


    /**
     * 功能：Workbook初始化
     * @param inputStream 文件流
     * @param fileType 后缀名
     * @return
     * @throws IOException
     */
    private Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        // 后缀判断有版本转换问题
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 功能：根据读取的单元格类型，对数据预处理
     * @param cell 单元格
     * @return 数据格式化
     */
    private String cellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:  // 数字
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数，如取小数，值如0.0,取小数点后几位就写几个0
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:  // 字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:  // 布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:  // 空值
                break;
            case FORMULA:  // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:  // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 功能：处理Excel内容转为 List<Map<Integer,Object>> 输出
     * @param workbook Workbook
     * @param StatrRow 开始行
     * @param EndRow 结束行（-1：全部）
     * @param ExistTop 是否读取头部
     * @return List<Map<Integer, Object>>
     */
    private List<Map<Integer, Object>> getListData(Workbook workbook, int StatrRow, int EndRow, boolean ExistTop){
        // 声明返回结果集result
        List<Map<Integer, Object>> result = new ArrayList<>();
        // 声明一个Excel头部函数
        ArrayList<String> top = new ArrayList<>();

        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }
            // 如存在头部，处理头部数据
            if (ExistTop) {
                int firstRowNum = sheet.getFirstRowNum();
                Row firstRow = sheet.getRow(firstRowNum);
                if (null == firstRow) {
                    log.warn("(╯▔＾▔)╯ => 解析Excel失败，在第一行没有读取到任何数据！");
                }
                for (int i = 0; i < firstRow.getLastCellNum(); i++) {
                    top.add(cellValueToString(firstRow.getCell(i)));
                }
            }
            // 处理Excel数据内容
            int endRowNum;
            // 获取结束行数
            if (EndRow == -1) {
                endRowNum = sheet.getPhysicalNumberOfRows();
            } else {
                endRowNum = EndRow <= sheet.getPhysicalNumberOfRows() ? EndRow : sheet.getPhysicalNumberOfRows();
            }
            // 遍历行数
            for (int i = StatrRow - 1; i < endRowNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                Map<Integer, Object> map = new HashMap<>();
                // 获取所有列数据
                for (int y = 0; y < row.getLastCellNum(); y++) {
                    if (top.size() > 0) {
                        if (top.size() >= y) {
                            map.put(Integer.parseInt(top.get(y)), cellValueToString(row.getCell(y)));
                        } else {
                            map.put(y+1, cellValueToString(row.getCell(y)));
                        }
                    } else {
                        map.put(y + 1, cellValueToString(row.getCell(y)));
                    }
                }
                result.add(map);
            }
        }
        return result;
    }
}

第三步：

实际调用

1.导入

@PostMapping("setExcel")
public Result setExcel(@RequestParam("files[]") MultipartFile file) throws Exception {
    try {
        List<Map<Integer, Object>> list = ExcelUtils.readExcel(file,2,-1,false);
        List<Received> newList = new ArrayList();

        //设置参数
        for(int i = 0;i<list.size(); i++) {
            Map<Integer, Object> map = list.get(i);
            Received received = new Received();
            received.setId(String.valueOf(map.get(1)));
            received.setName(String.valueOf(map.get(2)));
            received.setAge(String.valueOf(map.get(3)));

            newList.add(received);
        }

        int num = receivedDao.setReceivedExcel(newList);

        return new Result(Code.SUCCESS,null,"一共成功导入" + num + "条数据！");

    } catch (Exception e){
        return new Result(Code.EXCELERROR,null,"导入数据失败！");
    }

}

2.导出（这里只展示多个sheet的情况，原理是一样的）

import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * 实体类：表格包装
 * 作者：PadoPhyllis
 * 日期：2024/2/1
 */
@Data
@AllArgsConstructor
public class ExcelEntity {

    private String sheetName;  // sheet名字

    private String[] title;  // 标题

    private String[][] values;  // 数据

}

/**
    * 功能：查询数据库，并转成表格
    * @return HSSFWorkbook
*/
public HSSFWorkbook conversionMultiAppExcel() {

    // 标题
    String[] title = {"ID", "姓名", "年龄"};

    // 处理数据
    List<ExcelEntity> list = new ArrayList<>();
    list.add(new ExcelEntity("全部",title,conversionMultiApp(multiappDao.selectAll())));
    list.add(new ExcelWrapper("一班",title,conversionMultiApp(multiappDao.selectOne())));
    list.add(new ExcelWrapper("二班",title,conversionMultiApp(multiappDao.selectTwo())));

    HSSFWorkbook wb = excelUtils.getHSSFWorkbook(list,null);

    return wb;
}

/**
    * 功能：将 List<MultiApp> 转成 二维数组
    * @param list List<MultiApp>
    * @return String[][]
*/
public String[][] conversionMultiApp(List<MultiApp> list){ 
    String [][] content = new String[list.size()][];

    for (int i = 0; i < list.size(); i++) {
        // 这里的new String[3]为字段数量
        content[i] = new String[3];
        MultiApp m = list.get(i);
        content[i][0] = m.getId();
        content[i][1] = m.getName();
        content[i][2] = m.getAge();

    }

    return content;
}

3.备注

一、当前导出表格，已经设置了宽度自适应，这里是默认写好的了，可以自行删除。

二、可通过修改 ExcelUtils 中的 getStyle() 方法来自定义你的表格样式。

第四步：

喜欢的话就点赞收藏一下，这是我第一次发布文章/(^o^)//(^o^)//(^o^)/