java使用springcloud读excel文档

准备工作

在pom文件中导入jar包

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

一、读取excel文件获取指定行

   //获取excel中某一列数据
    public List<String> readExcelColumn(String filePath, int columnIndex) throws IOException {
        List<String> columnData = new ArrayList<>();

        // 读取Excel文件
        InputStream inputStream = new FileInputStream(filePath);
        Workbook workbook = WorkbookFactory.create(inputStream);

        // 获取第一个Sheet页（通常为Sheet0）
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            // Assuming column index 0
            Cell cell = row.getCell(0);
            if (cell != null) {
                switch (cell.getCellType()) {
                    case STRING:
                        String stringValue = cell.getStringCellValue();
                        System.out.println("String Value: " + stringValue);
                        break;
                    case NUMERIC:
                        double numericValue = cell.getNumericCellValue();
                        System.out.println("Numeric Value: " + numericValue);
                        break;
                    case BOOLEAN:
                        boolean booleanValue = cell.getBooleanCellValue();
                        System.out.println("Boolean Value: " + booleanValue);
                        break;
                    case BLANK:
                        System.out.println("Blank cell");
                        break;
                    default:
                        System.out.println("Cell type not supported");
                }
            }
        }

        // 遍历每一行（从第一行开始，索引为0）
        for (Row row : sheet) {
            Cell cell = row.getCell(columnIndex);
            if (cell != null) {
                columnData.add(cell.getStringCellValue());
            } else {
                columnData.add(""); // 如果单元格为空，添加空字符串
            }
        }

        // 关闭流
        workbook.close();
        inputStream.close();

        return columnData;
    }

控制层调用：

    @GetMapping("/column/{columnIndex}")
    public List<String> readExcelColumn(@PathVariable int columnIndex) {
        String filePath = "D:\\ThePleasureOfWork\\test.xlsx"; // 替换为实际的Excel文件路径
        try {
            return excelService.readExcelColumn(filePath, columnIndex);
        } catch (IOException e) {
            e.printStackTrace();
            // 处理异常
            return null;
        }
    }

二、获取excel指定列数据

  //获取excel中某一行数据
    public void readExcelFile(String filePath) throws IOException {
        // 读取Excel文件
        InputStream inputStream = new FileInputStream(filePath);
        Workbook workbook = WorkbookFactory.create(inputStream);

        // 获取第一个Sheet页（通常为Sheet0）
        Sheet sheet = workbook.getSheetAt(0);

        for (Row row : sheet) {
            // Assuming column index 0
            Cell cell = row.getCell(0);
            if (cell != null) {
                switch (cell.getCellType()) {
                    case STRING:
                        String stringValue = cell.getStringCellValue();
                        System.out.println("String Value: " + stringValue);
                        break;
                    case NUMERIC:
                        double numericValue = cell.getNumericCellValue();
                        System.out.println("Numeric Value: " + numericValue);
                        break;
                    case BOOLEAN:
                        boolean booleanValue = cell.getBooleanCellValue();
                        System.out.println("Boolean Value: " + booleanValue);
                        break;
                    case BLANK:
                        System.out.println("Blank cell");
                        break;
                    default:
                        System.out.println("Cell type not supported");
                }
            }
        }


        // 获取行数和列数
        int rowCount = sheet.getPhysicalNumberOfRows();
        int columnCount = sheet.getRow(0).getPhysicalNumberOfCells();

        // 假设要获取第一行（索引为0）的数据
        Row row = sheet.getRow(0);

        // 打印第一行每列的数据
        for (int col = 0; col < columnCount; col++) {



            Cell cell = row.getCell(col);
            System.out.print(cell.getStringCellValue() + "\t");
        }

        // 关闭流
        workbook.close();
        inputStream.close();
    }

控制层调用

   @GetMapping("/read")
    public void readExcel() {
        String filePath = "D:\\ThePleasureOfWork\\test.xlsx"; // 替换为实际的Excel文件路径
        try {
            excelService.readExcelFile(filePath);
        } catch (IOException e) {
            e.printStackTrace();
            // 处理异常
        }
    }

获取整个excel文件的数据

package com.tianlong.springcloud.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @ProjectName: spting_cloud_demo01
 * @Package: com.tianlong.springcloud.utils
 * @ClassName: ExcelUtils
 * @Author: TianLong
 * @Description:
 * @Date: 2024/7/23 22:44
 * @Version: 1.0
 */
@Service
public class ExcelUtils {

    /**
     * 读取excel数据写到集合中
     * @param excelFilePath 文件地址
     * @return
     */
    public static List<List<String>> readExcel(String excelFilePath) {
        // 存储Excel数据的List
        List<List<String>> excelData = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {

            // 仅处理第一个Sheet
            Iterator<Row> rowIterator = workbook.getSheetAt(0).iterator();

            // 迭代处理每一行
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.iterator();

                // 存储一行数据的List
                List<String> rowData = new ArrayList<>();
                // 迭代处理每个单元格
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    // 将所有单元格的内容转为String类型
                    rowData.add(cellToString(cell));
                }

                // 将一行数据添加到总数据List中
                excelData.add(rowData);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return excelData;
    }

    /**
     * 将单元格内容转为String
     * @param cell 单个单元格对象
     * @return
     */
    private static String cellToString(Cell cell) {
        //获取 Excel 单元格的数据类型
        switch (cell.getCellType()) {
            //字符串类型
            case STRING:
                return cell.getStringCellValue();
            //数字类型（包括日期和时间）
            case NUMERIC:
                return Double.toString(cell.getNumericCellValue());
            //布尔类型
            case BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());
            //单元格为空
            case BLANK:
                return "";
            //其他类型
            default:
                return "";
        }
    }
}

控制层调用

    @GetMapping("/read12")
    public void readExcel1() {
        List<List<String>> excelData = excelUtils.readExcel("D:\\ThePleasureOfWork\\test.xlsx");
        System.out.println(excelData);
        List<Map<String,String>> liatMap = new ArrayList<>();
        Map<String,String> map = new HashMap<>();
        // 打印读取的数据
        for (int i = 0; i < excelData.size(); i++) {
            int j = 0;
            for (String cellValue : excelData.get(i)) {
                map.put("name"+ j++,cellValue);
            }
            liatMap.add(map);

        }
        System.out.println(liatMap);
//        for (List<String> row : excelData) {
//            for (String cellValue : row) {
//                System.out.print(cellValue + "\t");
//                map.put("name",cellValue);
//            }
//
//            System.out.println();
//        }

    }