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

//----------------------------Controller层 ------------------------------------------------@Api(tags = "商品管理")@RestController@Slf4j@RequestMapping("/goods")public class GoodsController {@Autowiredprivate 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;}

//-----------------------------业务实现层-----------------------------------------------@Servicepublic class GoodsServiceImpl extends ServiceImpl<GoodsDao, Goods> implements GoodsService {@Autowiredprivate 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 } else if (fileName.endsWith("xls")) {hssfWorkbook = new HSSFWorkbook(is);// Excel }//获取工作表的页数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: /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);Class fieldType = field.getType();if (String.class == fieldType) {if (cell.getCellTypeEnum() == CellType.STRING && Optional.ofNullable(cell).isPresent()) {field.set(goods, row.getCell(cellNum).toString());} else {field.set(goods, null);}} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {if (cell.getCellTypeEnum() == CellType.NUMERIC && Optional.ofNullable(cell).isPresent()) {String str = row.getCell(cellNum).toString();String k = str.substring(0, str.indexOf("."));field.set(goods, Integer.parseInt(k));} else {field.set(goods, null);}} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {if (cell.getCellTypeEnum() == CellType.NUMERIC && Optional.ofNullable(cell).isPresent()) {field.set(goods, Long.parseLong(row.getCell(cellNum).toString()));} else {field.set(goods, null);}} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {if (cell.getCellTypeEnum() == CellType.NUMERIC && Optional.ofNullable(cell).isPresent()) {field.set(goods, Double.parseDouble(row.getCell(cellNum).toString()));} else {field.set(goods, null);}} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {if (cell.getCellTypeEnum() == CellType.NUMERIC && Optional.ofNullable(cell).isPresent()) {field.set(goods, Float.parseFloat(row.getCell(cellNum).toString()));} else {field.set(goods, null);}} else if (Date.class == fieldType) {if (cell.getCellTypeEnum() == CellType.NUMERIC && Optional.ofNullable(cell).isPresent()) {// 如果注解指定了日期格式， 安注解指定的格式格式化Excel annotation = field.getAnnotation(Excel.class);SimpleDateFormat formatter;if (annotation != null && StringUtils.isNotBlank(annotation.dateFormat())) {formatter = new SimpleDateFormat(annotation.dateFormat());} else {// 未指定时采用默认年月日的形式格式化formatter = new SimpleDateFormat("yyyy-MM-dd");}if (row.getCell(cellNum).getDateCellValue() != null) {String dateString = formatter.format(row.getCell(cellNum).getDateCellValue());field.set(goods, dateString);}else{field.set(goods, null);}}else if(cell.getCellTypeEnum()==CellType.STRING && Optional.ofNullable(cell).isPresent()){String stringCellValue = row.getCell(cellNum).getStringCellValue();field.set(goods, stringCellValue );}else{throw new RuntimeException();}}}//导出public ResponseEntity<byte[]> exportGoods(String startTime,String endTime) throws Exception {Optional.ofNullable(startTime).orElseThrow(() -> new CustomException("请指定导出的开始时间"));Optional.ofNullable(endTime).orElseThrow(() -> new CustomException("请指定导出的结束时间"));Workbook workbook = new XSSFWorkbook();CellStyle titleCellStyle = createCellStyle(workbook);Sheet sheet = workbook.createSheet(startTime,endTime);// 第0行，表头设置createTitleRow(sheet, startTime,endTime, titleCellStyle);/将字段名称赋值到单元格（按列设置或按行设置，根据实际需求设置）for (int cIndex = 0; cIndex < Constant.GOODS_LIST.size(); cIndex++) {row = sheet.createRow(cIndex + 3);cell = row.createCell(0);cell.setCellValue(Constant.GOODS_LIST.get(cIndex));// 给单元格赋值cell.setCellStyle(dataStyle);}//利用反射获取实体类的属性名称Class clazz = Goods.class;Field[] fields = clazz.getDeclaredFields();//将数据循环List<Goods> goodsList = goodsDao.selectByTime(startTime,endTime);for(Goods goods: goodsList ){for (int cIndex = 0; cIndex < Constant.GOODS_LIST_ENTITY.size(); cIndex++) {for (Field field : fields) {field.setAccessible(true);if (StringUtils.equals(field.getName(), Constant.GOODS_LIST_ENTITY.get(cIndex))) {row = ImportExportUtil.getRow(sheet, cIndex);cell = ImportExportUtil.getCell(row, cIndex);setCellValueByCell(cell, field, goods);}}}ByteArrayOutputStream outputStream = new ByteArrayOutputStream();workbook.write(outputStream);byte[] bytes = outputStream.toByteArray();return CommonUtils.createResponseEntity(bytes, startTime+ "-"+endTime+"购物清单.xlsx");}/*** @Description:设置表头* @Author: Yrc* @Data: /5/11 16:57* @Param: [sheet, startTime,endTime, cellStyle]* @Return: void* @Version: 1.0* Throws:*/private void createTitleRow(Sheet sheet, String startTime,String endTime, CellStyle cellStyle) {// 第0行合并String title =startTime+ "-"+endTime+"购物清单";CellRangeAddress titleRangeAddress = new CellRangeAddress(0, 0, 0, 30);sheet.addMergedRegion(titleRangeAddress);// 设置内容Row row = ImportExportUtil.getRow(sheet, 0);row.setRowStyle(cellStyle);//设置宽度和高度row.setHeight((short) (Constant.CELL_HEIGHT_MULTIPLE * 40));sheet.setColumnWidth(0, 20 * 256);//设置列的宽度Cell titleCell = ImportExportUtil.getCell(row, 0);titleCell.setCellValue(title);// 设置样式titleCell.setCellStyle(cellStyle);}/*** @Description:处理单元格不同类型的取值* @Author: Yrc* @Data: /5/10 16:15* @Param: [cell, field, t]* @Return: void* @Version: 1.0* Throws:*/public void setCellValueByCell(Cell cell, Field field, Goods t) throws IllegalAccessException {Class fieldType = field.getType();if (String.class == fieldType) {if (field.get(t) == null) {cell.setCellValue((String) null);} else {cell.setCellValue((String) field.get(t));}} else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {if (field.get(t) == null) {cell.setCellValue((String) null);} else {cell.setCellValue((Integer) field.get(t));}} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {if (field.get(t) == null) {cell.setCellValue((String) null);} else {cell.setCellValue((Long) field.get(t));}} else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {if (field.get(t) == null) {cell.setCellValue((String) null);} else {cell.setCellValue((Double) field.get(t));}} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {if (field.get(t) == null) {cell.setCellValue((String) null);} else {cell.setCellValue((Float) field.get(t));}} else if (Date.class == fieldType) {// 如果注解指定了日期格式， 安注解指定的格式格式化Excel annotation = field.getAnnotation(Excel.class);SimpleDateFormat formatter;if (annotation != null && StringUtils.isNotBlank(annotation.dateFormat())) {formatter = new SimpleDateFormat(annotation.dateFormat());} else {// 未指定时采用默认年月日的形式格式化formatter = new SimpleDateFormat("yyyy-MM-dd");}if (field.get(t) != null) {String dateString = formatter.format(field.get(t));cell.setCellValue(dateString);}}}/*** @Description:表头样式设置* @Author: Yrc* @Data: /5/17 15:48* @Param: [workbook]* @Return: org.apache.poi.ss.usermodel.CellStyle* @Version: 1.0* Throws:*/private CellStyle createCellStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();//字体大小font.setFontHeightInPoints((short) 15);//设置是否加粗font.setBold(true);//选择需要用到的字体格式cellStyle.setFont(font);//设置自动换行cellStyle.setWrapText(true);//设置水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置下边框cellStyle.setBorderBottom(BorderStyle.THIN);//设置上边框cellStyle.setBorderTop(BorderStyle.THIN);//设置走边框cellStyle.setBorderLeft(BorderStyle.THIN);//设置右边框cellStyle.setBorderRight(BorderStyle.THIN);return cellStyle;}

/*** @Description:工具类* @Author: Yrc* @Data: /5/17 15:48* @Version: 1.0*/public class ImportExportUtil {/*** @Description:获取每一行单元格的数据，判断是否为空，* @Author: Yrc* @Data: /4/18 17:16* @Param: [row, colIndex]* @Return: org.apache.poi.ss.usermodel.Cell* Throws:*/public static Cell getCell(Row row, int colIndex) {return Optional.ofNullable(row.getCell(colIndex)).orElseGet(() -> row.createCell(colIndex));}}public class Constant {public static final List<String> GOODS_LIST=Arrays.asList("名称","价格")public static final List<String> GOODS_LIST_ENTITY=Arrays.asList("name","price")}public class CommonUtils {/*** 用字节数组和文件名生成 ResponseEntity对象* * @param bytes 字节数组* @param fileName 文件名* @return ResponseEntity<byte>* @author Yrc* @date -04-08*/public static ResponseEntity<byte[]> createResponseEntity(byte[] bytes, String fileName) throws Exception {HttpHeaders headers = new HttpHeaders();headers.setContentDispositionFormData("attachment",URLEncoder.encode(fileName, Charset.defaultCharset().name()));headers.setContentLength(bytes.length);headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);headers.setAccessControlExposeHeaders(Arrays.asList(HttpHeaders.CONTENT_DISPOSITION, HttpHeaders.CONTENT_LENGTH,HttpHeaders.CONTENT_TYPE));return ResponseEntity.status(HttpStatus.SC_OK).headers(headers).body(bytes);}public static ResponseEntity<byte[]> createResponseEntity(Workbook workbook, String fileName) throws Exception {ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();workbook.write(byteArrayOutputStream);byte[] bytes = byteArrayOutputStream.toByteArray();return CommonUtils.createResponseEntity(bytes, fileName);}}