网站做微信支付/百度竞价返点开户

前言：

（当前是横竖单元格没有出现合并的情况的简单实现）

一般需要用模板，每一个表都是固定做具体的功能，每一个表的每一列都是有具体格式要求。否则数据处理的时候格式不规范，数据无法使用。

1. 导入依赖

        <!--POI 读取文件--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.8</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.8</version></dependency>

2. 工具类

package com.xyz.utils;import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.util.ArrayList;
import java.util.List;public class ExcelReadUtils {/*** 读取excel文件：列值可为空*/public static List<List<List<String>>> getData(XSSFWorkbook wb, Integer ignoreRows, Integer sheetNum) {if (sheetNum == null) {sheetNum = wb.getNumberOfSheets();}List<List<List<String>>> lists = new ArrayList<>();//for循环：取前N个表,下标从0开始for (int i = 0; i < sheetNum; i++) {XSSFSheet sheetI = wb.getSheetAt(i);List<List<String>> list = new ArrayList<>();int cellSize = sheetI.getRow(0).getLastCellNum();//列数//第N+1行开始，可以通过传参，从第N+1行开始取for (int rowIndex = ignoreRows; rowIndex <= sheetI.getLastRowNum(); rowIndex++) {XSSFRow row = sheetI.getRow(rowIndex);if (row == null) {continue;}List<String> rowList = new ArrayList<>();//在每行中的每一列，从下标0开始，一直取到所有for (int a = 0; a < cellSize; a++) {String cellValue = getCellValue(row.getCell(a));rowList.add(cellValue);}list.add(rowList);}lists.add(list);}return lists;}private static String getCellValue(XSSFCell cell) {String value = "";if (cell != null) {switch (cell.getCellType()) {case XSSFCell.CELL_TYPE_NUMERIC:double d = cell.getNumericCellValue();value = String.valueOf(d).substring(0, String.valueOf(d).indexOf("."));break;case XSSFCell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;case XSSFCell.CELL_TYPE_FORMULA://导入时如果为公式生成的数据则无值if (!cell.getStringCellValue().equals("")) {value = cell.getStringCellValue();} else {value = cell.getNumericCellValue() + "";}break;case XSSFCell.CELL_TYPE_BLANK:break;case XSSFCell.CELL_TYPE_ERROR:value = "";break;case XSSFCell.CELL_TYPE_BOOLEAN:value = (cell.getBooleanCellValue() == true ? "Y" : "N");break;}}return value;}
}

3. 具体使用

直接在controller成使用。Result是我个人的封装。

    public Result uploadExcel(MultipartFile myFile) {//两种方式，一种直接上传的文件MultipartFile，一种用本地的文件：File//        File file2 = new File("这里是filePath");//        InputStream inputStream = new FileInputStream(file2);String fileName = myFile.getOriginalFilename();assert fileName != null;if (!fileName.endsWith(".xlsx")) {return new Result(ResultCode.FILE_WRONG);}List<List<List<String>>> list = null;InputStream inputStream = null;try {inputStream = myFile.getInputStream();XSSFWorkbook wb = new XSSFWorkbook(inputStream);list = ExcelReadUtils.getData(wb, 0, null);//3个参数。} catch (Exception e) {log.info("文件读取失败");} finally {if (inputStream != null) {try {inputStream.close();} catch (Exception e) {log.info("文件流关闭失败");}inputStream = null;}}return new Result(list);//读取到的list（表（行（列））），直接返回，也可以按需求做别的操作。}