JAVA语言(POI数据导入导出Excel)

POI数据导入导出Excel(样式可以自己设置)

//----------------------------Controller层 ------------------------------------------------
@Api(tags = "商品管理")
@RestController
@Slf4j
@RequestMapping("/goods")
public class GoodsController {
   

    @Autowired
    private GoodsService GoodsService;
    
	@ApiOperation("导入信息")
	@PostMapping("/importGoods")
	public RestResult importGoods(MultipartFile file) throws Exception {
   
	    return goodsService.importGoods(file);
	}
	
	@ApiOperation("导出信息")
	@GetMapping("/exportGoods")
	public ResponseEntity<byte[]> exportGoods(String startTime,String endTime) throws Exception {
   
	    return goodsService.exportGoods(startTime,endTime);
	}
}
//----------------------------Service层---------------------------------------------------
public interface GoodsService extends IService<Goods> {
   
	//导入
	RestResult  importGoods(MultipartFile file) throws IOException;
	//导出
	ResponseEntity<byte[]> exportGoods(String startTime,String endTime) throws Exception;
}

//-----------------------------业务实现层-----------------------------------------------
@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsDao, Goods> implements GoodsService {
   

    @Autowired
    private GoodsDao goodsDao;
    
	//导入
	public RestResult  importGoods(MultipartFile file) throws IOException {
   
		 InputStream is = file.getInputStream();
		 String fileName = file.getOriginalFilename();
		 //获取工作簿
		 Workbook hssfWorkbook = null;
		 if (fileName.endsWith("xlsx")) {
   
		     hssfWorkbook = new XSSFWorkbook(is);// Excel 2007
		 } else if (fileName.endsWith("xls")) {
   
		     hssfWorkbook = new HSSFWorkbook(is);// Excel 2003
		 }
		 //获取工作表的页数
		 int numberOfSheets = hssfWorkbook.getNumberOfSheets();
		 //利用反射获取实体类的属性名称
		 Class clazz = Goods.class;
		 Field[] fields = clazz.getDeclaredFields();
		 List<Goods> list = new ArrayList<>();
		  //获取工作表
	            for (int i = 0; i < numberOfSheets; i++) {
   
	                Sheet sheetAt = hssfWorkbook.getSheetAt(i);
	                //获取行,确认数据是从第几行导入
	                for (int rowNum = 2; rowNum <= sheetAt.getLastRowNum(); rowNum++) {
   
	                    Row hssfRow = sheetAt.getRow(rowNum);
	                    Goods goods= new Goods();
				/**
				* 此处可灵活多变处理，根据实际的Excel表格形式;主要是将数据放在list集合中
				* 1.也可以进行按列读取
				*   // 得到Excel的列数
				*	int totalCells = 0;
				*	if (lastRowNum >= 1 && hssfSheet.getRow(2) != null) {
				*	   totalCells = hssfSheet.getRow(2).getPhysicalNumberOfCells();
				*	}
				*	//循环列
				*	for (int cellNum = 1; cellNum <= totalCells; cellNum++) {
				*   budgetSettlement = new BudgetSettlement();
				*   for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				*       Row row = hssfSheet.getRow(rowNum);
				*       if (row != null) {
				*           for (Field field : fields) {
				*              field.setAccessible(true);
				*               if (StringUtils.equals(field.getName(), Constant.GOODS_LIST_ENTITY.get(rowNum - 2))) {
				*                   getCellValue(field, row, cellNum, goods);
				*               }
				*           }
				*       }
				*   }
				* }
				* 2.特殊的数据可以单独设置赋值
				* 
				*/
		             for (Field field : fields) {
   
		                //此处使用反射设置private属性可读取
		                             field.setAccessible(true);
		                             if (StringUtils.equals(field.getName(), Constant.GOODS_LIST_ENTITY.get(rowNum - 2))) {
   
		                                 getCellValue(field, row, cellNum, goods);
		                             }
		                         }
		                  list.add(goods);
		               }
		           }
          saveBatch(list);
}
    /**
     * @Description:导入数据格式处理（无返回值）
     * @Author: Yrc
     * @Data: 2022/5/13 10:58
     * @Param: [field, row, cellNum, goods]
     * @Return: void
     * @Version: 1.0
     * Throws:
     */
     public void getCellValue(Field field, Row row, int cellNum,  Goods goods) throws IllegalAccessException {
   

        Cell cell = ImportExportUtil.getCell(row, cellNum