POI操作

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

//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook("D:\\hello.xlsx");
//获取工作表，既可以根据工作表的顺序获取，也可以根据工作表的名称获取
XSSFSheet sheet = workbook.getSheetAt(0);
//遍历工作表获得行对象
for (Row row : sheet) {
  //遍历行对象获取单元格对象
  for (Cell cell : row) {
    //获得单元格中的值
    String value = cell.getStringCellValue();
    System.out.println(value);
  }
}
workbook.close();

//创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook("D:\\hello.xlsx");
//获取工作表，既可以根据工作表的顺序获取，也可以根据工作表的名称获取
XSSFSheet sheet = workbook.getSheetAt(0);
//获取当前工作表最后一行的行号，行号从0开始
int lastRowNum = sheet.getLastRowNum();
for(int i=0;i<=lastRowNum;i++){
  //根据行号获取行对象
  XSSFRow row = sheet.getRow(i);
  short lastCellNum = row.getLastCellNum();
  for(short j=0;j<lastCellNum;j++){
    String value = row.getCell(j).getStringCellValue();
    System.out.println(value);
  }
}
workbook.close();

//在内存中创建一个Excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
//创建工作表，指定工作表名称
XSSFSheet sheet = workbook.createSheet("sheet");

//创建行，0表示第一行
XSSFRow row = sheet.createRow(0);
//创建单元格，0表示第一个单元格
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("名称");
row.createCell(2).setCellValue("年龄");

XSSFRow row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("1");
row1.createCell(1).setCellValue("小明");
row1.createCell(2).setCellValue("10");

XSSFRow row2 = sheet.createRow(2);
row2.createCell(0).setCellValue("2");
row2.createCell(1).setCellValue("小王");
row2.createCell(2).setCellValue("20");

//通过输出流将workbook对象下载到磁盘
FileOutputStream out = new FileOutputStream("D:\\itcast.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();

@RequestMapping("/exportBusinessReport")
	public void exportBusinessReport(HttpServletRequest request, HttpServletResponse response){
		try {
			List<TbGoods> goodsList = goodsService.findAll();

			//获得Excel模板文件绝对路径
			String temlateRealPath = request.getSession().getServletContext().getRealPath("template") +
					File.separator + "goods_template.xlsx";

			//读取模板文件创建Excel表格对象
			XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(temlateRealPath)));
			XSSFSheet sheet = workbook.getSheetAt(0);

			int rowNum=2;
			for (TbGoods goods : goodsList) {
				XSSFRow row = sheet.getRow(rowNum);
				row.getCell(0).setCellValue(goods.getId());
				row.getCell(1).setCellValue(goods.getSellerId());
				row.getCell(2).setCellValue(goods.getGoodsName());
				Long defaultItemId = goods.getDefaultItemId();
				String defaultItemIdStr="";
				if (defaultItemId != null) {
					defaultItemIdStr=defaultItemId+"";
				}
				row.getCell(3).setCellValue(defaultItemIdStr);
				row.getCell(4).setCellValue(goods.getAuditStatus());
				row.getCell(5).setCellValue(goods.getIsMarketable());
				Long brandId = goods.getBrandId();
				TbBrand brand = brandService.getById(brandId);
				if (brand != null) {
					row.getCell(6).setCellValue(brand.getName());
				}
				row.getCell(7).setCellValue(goods.getCaption());
				Long category1Id = goods.getCategory1Id();
				TbItemCat itemCat1 = itemCatService.getById(category1Id);
				if (itemCat1 != null) {
					row.getCell(8).setCellValue(itemCat1.getName());
				}
				Long category2Id = goods.getCategory2Id();
				TbItemCat itemCat2 = itemCatService.getById(category2Id);
				if (itemCat2 != null) {
					row.getCell(9).setCellValue(itemCat2.getName());
				}
				Long category3Id = goods.getCategory3Id();
				TbItemCat itemCat3 = itemCatService.getById(category3Id);
				if (itemCat3 != null) {
					row.getCell(10).setCellValue(itemCat3.getName());
				}
				row.getCell(11).setCellValue(goods.getSmallPic());
				BigDecimal price = goods.getPrice();
				if (price != null) {
					row.getCell(12).setCellValue(price.doubleValue());
				}
				Long typeTemplateId = goods.getTypeTemplateId();
				TbTypeTemplate template = typeTemplateService.getById(typeTemplateId);
				if (template != null) {
					row.getCell(13).setCellValue(template.getName());
				}
				row.getCell(14).setCellValue(goods.getIsEnableSpec());
				row.getCell(15).setCellValue(goods.getIsDelete());

				rowNum++;
			}
			//通过输出流进行文件下载
			ServletOutputStream out = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("content-Disposition", "attachment;filename=goods_report.xlsx");
			workbook.write(out);

			out.flush();
			out.close();
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

@RequestMapping("/upload")
	public Result upload(MultipartFile excelFile, Long parentId) {
		try {
			List<String[]> list = readExcel(excelFile);

			List<TbItemCat> itemCatList = new ArrayList<>();
			if (list != null && list.size() > 0) {
				for (String[] row : list) {
					String name = row[0];
					if (name == null) {
						continue;
					}

					TbItemCat itemCat = new TbItemCat();
					itemCat.setName(name);

					String typeName = row[1];
					TbTypeTemplate typeTemplate = typeTemplateService.findByName(typeName);
					itemCat.setTypeId(typeTemplate.getId());

					itemCat.setParentId(parentId);

					itemCatList.add(itemCat);
				}
			}
			itemCatService.addList(itemCatList);
			return new Result(true, "上传成功");
		} catch (Exception e) {
			e.printStackTrace();
			return new Result(false, "上传失败");
		}
	}

	private List<String[]> readExcel(MultipartFile file) throws IOException {
		//检查文件
		POIUtils.checkFile(file);
		//获得Workbook工作薄对象
		Workbook workbook = POIUtils.getWorkBook(file);
		//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
		List<String[]> list = new ArrayList<String[]>();
		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();
				//循环除了前三行的所有行
				for (int rowNum = firstRowNum + 3; rowNum <= lastRowNum; rowNum++) {
					//获得当前行
					Row row = sheet.getRow(rowNum);
					if (row == null) {
						continue;
					}
					//获得当前行的开始列
					int firstCellNum = row.getFirstCellNum();
					//获得当前行的列数
					int lastCellNum = row.getPhysicalNumberOfCells();
					String[] cells = new String[row.getPhysicalNumberOfCells()];
					//循环当前行
					for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
						Cell cell = row.getCell(cellNum);
						cells[cellNum] = POIUtils.getCellValue(cell);
					}
					list.add(cells);
				}
			}
			workbook.close();
		}
		return list;
	}

<el-upload :action="'/itemCat/upload.do?parentId='+parentId"
            name="excelFile"
           :show-file-list="false"
           :on-success="handleSuccess"
           :before-upload="beforeUpload">
        <el-button  size="small">导入规格数据</el-button>
</el-upload>

//上传成功提示
            handleSuccess(response, file) {
                if(response.success){
                    this.$message({
                        message: response.message,
                        type: 'success'
                    });
                }else{
                    this.$message.error(response.message);
                }
                this.findByParentId2(this.parentId);
            },
            //上传之前进行文件格式校验
            beforeUpload(file){
                var FilenameExtension=file.name.substr(file.name.lastIndexOf("."));
                const isXLS=FilenameExtension==='.xls';
                if(isXLS){
                    return true;
                }
                const isXLSX=FilenameExtension==='.xlsx';
                if(isXLSX){
                    return true;
                }
                this.$message.error('上传文件只能是xls或者xlsx格式!');
                return false;
                /*const isXLS = file.type === 'application/vnd.ms-excel';
                if(isXLS){
                    return true;
                }
                const isXLSX = file.type === 'application/vnd.openxmlformats-officedocument.spreads heetml.sheet';
                if (isXLSX) {
                    return true;
                }
                this.$message.error('上传文件只能是xls或者xlsx格式!');
                return false;*/
            }