这里有个Excel，怎么使用java读取excel中的数据呢？
文件存放位置：
首先引入poi的jar包
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency>我这里写了一个People类，目的是想把Excel中的数据读取出来后转成这个类，方便后续进行操作。
@Data
@NoArgsConstructor
@AllArgsConstructor
public class People {private String name;private String age;private String sex;private String area;
}
然后看代码：
/*** FileName: MyExcelTest* Author: zp* Date: 2020/2020/10/11/10:16* Description:*/
package excel;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
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.io.IOException;
import java.util.ArrayList;
import java.util.List;/*** Description: * @author zpzp6* @create 2020/2020/10/11/10:16* @since 1.0.0*/
public class MyExcelTest {public static void main(String[] args) throws IOException {//获取工作簿XSSFWorkbook book = new XSSFWorkbook("E:\\我的文件\\测试\\测试.xlsx");//获取工作表XSSFSheet sheet = book.getSheetAt(0);
// //第一种读取读取所有数据，实际中不需要
// //获取行
// for (Row cells : sheet) {
// //获取单元格
// for (Cell cell : cells) {
// //获取单元格中的内容
// cell.setCellType(CellType.STRING);
// System.out.println(cell.getStringCellValue());
// }
// }List<People> peopleList=new ArrayList<>();//普通for循环//开始索引0 结束索引int lastRowNum = sheet.getLastRowNum();System.out.println("最后一行："+lastRowNum);for (int i = 1; i <= lastRowNum; i++) {//获取单元格XSSFRow row = sheet.getRow(i);if(row!=null){List<String> list =new ArrayList<>();for (Cell cell : row) {if(cell!=null && !"".equals(cell)){//此处是把单元格都转换成String类型cell.setCellType(CellType.STRING);String cellValue = cell.getStringCellValue();System.out.println("单元格数据："+cellValue);list.add(cellValue);}}if(list.size()>0){People people = new People(list.get(0), list.get(1), list.get(2), list.get(3));peopleList.add(people);}}}for (People people : peopleList) {System.out.println(people);}//释放资源book.close();}}结果：
如果有这样的报错是因为打开了Excel文件，关闭就好。
测试的写得差不多了。那么就来点正式的。
控制层：
@ApiOperation("读取资源文件")@PostMapping("/read-file")public List<PartyMember> readFile(@RequestParam(required = false) String path, @RequestParam(required = false) MultipartFile file) throws Exception{return new PartyMember().getExcelData(file);}MultipartFile： 前端可以把excel上传，后端通过此MultipartFile来接收。path可以不写 /*** @return * @param null* @Author* @Description //TODO* @Date 2019/8/15 12:14* @Param file ：上传的excel文件*/@Transactionalpublic List<PartyMember> getExcelData(MultipartFile file) throws IOException {List<PartyMember> partyMembers = new ArrayList<>();PartyMember member = new PartyMember();SnapMember snapMember = new SnapMember();String fileName = checkFile(file);//获得Workbook工作薄对象Workbook workbook = getWorkBook(file);//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回List<List<String>> list = new ArrayList<>();if (workbook != null) {for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {//获得当前sheet工作表Sheet sheet = workbook.getSheetAt(sheetNum);if (sheet == null) {continue;}//获得当前sheet的开始行int firstRowNum = sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum = sheet.getLastRowNum();//循环除了所有行,如果要循环除第一行以外的就firstRowNum+1for (int rowNum = firstRowNum + 2; rowNum <= lastRowNum; rowNum++) {//业务逻辑}}workbook.close();}return partyMembers;}/*** 检查文件** @param file* @throws IOException*/public static String checkFile(MultipartFile file) throws IOException {//判断文件是否存在if (null == file) {throw new CustomException("文件不存在！",HttpStatus.BAD_REQUEST);}//获得文件名String fileName = file.getOriginalFilename();//判断文件是否是excel文件if (!StringUtils.lowerCase(fileName).endsWith("xls") && !StringUtils.lowerCase(fileName).endsWith("xlsx")) {throw new CustomException("不是excel文件",HttpStatus.BAD_REQUEST);}return fileName;}public static Workbook getWorkBook(MultipartFile file) {//获得文件名String fileName = file.getOriginalFilename();//创建Workbook工作薄对象，表示整个excelWorkbook workbook = null;try {//获取excel文件的io流InputStream is = file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if (StringUtils.lowerCase(fileName).endsWith("xls")) {//2003workbook = new HSSFWorkbook(is);} else if (StringUtils.lowerCase(fileName).endsWith("xlsx")) {//2007 及2007以上workbook = new XSSFWorkbook(is);}} catch (IOException e) {e.getMessage();}return workbook;}public static String getCellValue(Cell cell) {String cellValue = "";if (cell == null) {return cellValue;}//判断数据的类型//判断数据的类型switch (cell.getCellTypeEnum()) {case NUMERIC: //数字cellValue = stringDateProcess(cell);break;case STRING: //字符串cellValue = String.valueOf(cell.getStringCellValue());break;case BOOLEAN: //BooleancellValue = String.valueOf(cell.getBooleanCellValue());break;case FORMULA: //公式cellValue = String.valueOf(cell.getCellFormula());break;case BLANK: //空值cellValue = "";break;case ERROR: //故障cellValue = "非法字符";break;default:cellValue = "未知类型";break;}return cellValue;}public static String stringDateProcess(Cell cell) {String result = new String();if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式SimpleDateFormat sdf = null;if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {sdf = new SimpleDateFormat("HH:mm");} else {// 日期sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");}Date date = cell.getDateCellValue();result = sdf.format(date);} else if (cell.getCellStyle().getDataFormat() == 58) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);result = sdf.format(date);} else {double value = cell.getNumericCellValue();CellStyle style = cell.getCellStyle();DecimalFormat format = new DecimalFormat();String temp = style.getDataFormatString();// 单元格设置成常规if (temp.equals("General")) {format.applyPattern("#");}result = format.format(value);}return result;}
