Springboot之Excel表格导出
表格导出使用的还是POI,POI的介绍请查看
https://blog.csdn.net/qq_36654629/article/details/90172911
使用前需引入poi相关依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>RELEASE</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>RELEASE</version> </dependency>
表格导出工具
import org.apache.poi.hssf.usermodel.*; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.servlet.http.HttpServletResponse; import java.io.BufferedOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.Date; import java.util.List; @Service @Transactional(rollbackFor = Throwable.class) public class GoodsExportService { /** * @param sheetName sheet标签名称，headers第一行标题名称，list 数据 * @author * @description excel 导出 * @date 2019-06-27 * @throws IOException */ public void exportTemplate(HttpServletResponse response, String sheetName, String[] headers, List list) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(sheetName); setTitle(headers,workbook, sheet); //新增数据行，并且设置单元格数据 for(int i=0;i<list.size();i++){ List clist = (List)list.get(i); HSSFRow hssfRow = sheet.createRow(i+1); for(int j=0;j<clist.size();j++){ hssfRow.createCell(j).setCellValue((String)clist.get(j)); } } Date date = new Date(); String fileName =String.valueOf(date.getTime()); fileName = fileName + ".xls"; //清空response response.reset(); //设置response的Header response.setContentType("application/vnd.ms-exce;charset=GBK"); response.setCharacterEncoding("GBK"); response.setHeader("content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode(new String(fileName.getBytes("GBK"),"ISO-8859-1"), "GBK")); response.setDateHeader("Expires", 0); OutputStream os = new BufferedOutputStream(response.getOutputStream()); //将excel写入到输出流中 workbook.write(os); os.flush(); os.close(); } // 创建表头 public void setTitle(String[] headers,HSSFWorkbook workbook, HSSFSheet sheet) { HSSFRow row = sheet.createRow(0); // 设置为居中加粗 HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); style.setFont(font); // 设置表格默认列宽度为15个字节 for (int i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); cell.setCellStyle(style); } } }
表格导入
@Service @Transactional(rollbackFor = Throwable.class) public class GoodsImportService { @Value("${image.uploadPath}") private String imageUploadPath; @Autowired private CustomGoodsUnitService customGoodsUnitService; @Autowired private CustomMerchantInfoService customMerchantInfoService; @Autowired private CustomGoodsCategoryService customGoodsCategoryService; /** * 批量导入商品专用 * * @throws IOException * @author liyueken * @description excel 导入 * @date 2019-06-27 */ public List analysisExcelByGoods(HttpServletRequest request, MultipartFile file, String userId, MerchantInfo info, Boolean updownState) throws IOException, InvalidFormatException { Workbook wb = readExcel(request, file);//读取 Sheet sheet = null; Row row = null; List<Goods> list = null; if (wb != null) { //用来存放表中数据 list = new ArrayList<>(); //获取第一个sheet sheet = wb.getSheetAt(0); //获取最大行数 int rownum = sheet.getLastRowNum(); for (int i = 0; i < rownum; i++) { //从第二行开始 row = sheet.getRow(i + 1); if (row != null) { //第一个单元格为空，定义为最后一行 if (row.getCell(0) == null) { return list; } Goods goods = new Goods(); goods.setId(UUIDUtils.generator()); goods.setCreateUser(userId); goods.setLastModifyUser(userId); goods.setMerchantId(info.getId()); goods.setMerchantName(info.getCompanyName()); goods.setState(true); goods.setGoodsType(GoodsTypeEnum.NORMAL.getCode()); //设置Excel数据读入实体 goods.setGoodsName(getCellFormatValue(row.getCell(0))); if (row.getCell(1) != null) { goods.setGoodsDescription(getCellFormatValue(row.getCell(1))); } if (row.getCell(2) == null) { throw new GenericException(GoodsErrorEnum.GOODS_CATEGORY.getCode(), GoodsErrorEnum.GOODS_CATEGORY.getDesc()); } //================================================= //-1表示不限制库存 goods.setGoodsInventory(new BigDecimal("-1")); goods.setGoodsInventoryType(GoodsInventoryTypeEnum.UNRESTRICTED.getCode()); String categoryName = getCellFormatValue(row.getCell(2)); boolean flag = true; String categoryFullName = ""; String categoryFullId = ""; String[] split = categoryName.split(","); GoodsCategory goodsCategory = customGoodsCategoryService.getByName(split[0]); if (goodsCategory == null) { //商品分类不存在异常 throw new GenericException(GoodsCategoryErrorEnum.CATEGORY_ISEMPTY.getCode(), GoodsCategoryErrorEnum.CATEGORY_ISEMPTY.getDesc()); } goods.setCategoryId(goodsCategory.getId()); //todo 待定 categoryFullName = goodsCategory.getCategoryName(); categoryFullId = goodsCategory.getId(); String pid = goodsCategory.getPid(); while (flag) { GoodsCategory bean = customGoodsCategoryService.getEqualsIdByPid(pid); if (bean == null) { flag = false; } else { categoryFullName = bean.getCategoryName() + "," + categoryFullName; categoryFullId = bean.getId() + "," + categoryFullId; pid = bean.getPid(); } } goods.setCategoryFullId(categoryFullId); goods.setCategoryFullName(categoryFullName); goods.setGoodsOriginalPrice(new BigDecimal(getCellFormatValue(row.getCell(3)))); if (row.getCell(4) == null) { throw new GenericException(GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getCode(), GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getDesc()); } String goodsUnit = getCellFormatValue(row.getCell(4)); //校验单位是否符合平台规范 GoodsUnit goodsUnit1 = customGoodsUnitService.checkName(goodsUnit); if (goodsUnit1 == null) { throw new GenericException(GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getCode(), GoodsErrorEnum.GOODS_UNIT_ISEMPTY.getDesc()); } //设置商品单位编码 goods.setGoodsUnit(goodsUnit); goods.setGoodsUnitCode(goodsUnit1.getCode()); goods.setGoodsMinUnit(Integer.valueOf((row.getCell(5).getStringCellValue()))); goods.setGoodsSalesPrice(new BigDecimal(getCellFormatValue(row.getCell(6)))); goods.setAuditState(GoodsAuditStateEnum.AUDIT_OK.getCode()); goods.setIfShortage(false);//默认不缺货 goods.setUpdownState(updownState); //0否1是 称重商品 默认false 0 goods.setIfWeighGoods(false); if (row.getCell(9).getStringCellValue() == "1") { goods.setIfWeighGoods(true); } goods.setSupplierId(row.getCell(10).getStringCellValue()); goods.setSupplierName(row.getCell(11).getStringCellValue()); try { goods.setQualityGuaranteePeriod(DateUtils.format(row.getCell(12).getStringCellValue(),"yyyy/MM/dd")); } catch (ParseException e) { throw new GenericException(2011001,"日期转换异常"); } if (row.getCell(13) != null) { goods.setGoodsFirstPic(row.getCell(13).getStringCellValue()); } else { throw new GenericException(GoodsErrorEnum.GOODS_FIRST_PIC_ISEMPTY_ISEMPTY.getCode(), GoodsErrorEnum.GOODS_FIRST_PIC_ISEMPTY_ISEMPTY.getDesc()); } list.add(goods); } else { break; } } } return list; } /** * @param file 附件 * @return 文件名称 * @date 2019-6-27 * @author zl * @description 读取附件 */ public Workbook readExcel(HttpServletRequest request, MultipartFile file) throws IOException, InvalidFormatException { String excelPath = upload(request, file);//上传excel文件 File excel = new File(excelPath); InputStream in = new FileInputStream(excel); String fileName = excel.getName(); Workbook wb = null; if (excel.isFile() && excel.exists()) { //判断文件是否存在 InputStream is = in; if (fileName.endsWith("xls")) { wb = new HSSFWorkbook(is); } else if (fileName.endsWith("xlsx")) { wb = WorkbookFactory.create(is); } else { throw new GenericException(AttachErrorEnum.ATTACH_TYPE_EXCEL_ERROR.getCode(), AttachErrorEnum.ATTACH_TYPE_EXCEL_ERROR.getDesc()); } } return wb; } /** * @param file 附件 * @return 文件名称 * @date 2019-6-27 * @author zl * @description 附件上传 */ public String upload(HttpServletRequest request, MultipartFile file) { List<FileBean> filePath = new ArrayList<>(); Calendar cal = Calendar.getInstance(); String folder = "" + cal.get(Calendar.YEAR) + (cal.get(Calendar.MONTH) + 1) + cal.get(Calendar.DATE); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getRequestURI(); String path = ""; try { //todo 前端传递业务类型。判断能否上传 String fullFileName = System.currentTimeMillis() + file.getOriginalFilename(); String serveFileName = fullFileName.substring(0, fullFileName.lastIndexOf(".")); String originalName = file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf(".")); path = imageUploadPath + File.separator + folder + File.separator + fullFileName; File target = new File(path); if (!target.getParentFile().exists()) { target.getParentFile().mkdirs(); } file.transferTo(target); String fileResource = basePath + "/" + folder + "/" + fullFileName; //信息封装 FileBean fileBean = new FileBean(fullFileName, originalName, fileResource, file.getContentType()); //返回文件在服务器的地址 filePath.add(fileBean); } catch (Exception e) { e.printStackTrace(); } return path; } public static String getCellFormatValue(Cell cell) { String cellValue = null; if (cell != null) { //判断cell类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { cellValue = String.valueOf(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_FORMULA: { //判断cell是否为日期格式 if (DateUtil.isCellDateFormatted(cell)) { //转换为日期格式YYYY-mm-dd // cellValue = cell.getDateCellValue(); cellValue = String.valueOf(cell.getNumericCellValue()); } else { //数字 cellValue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING: { cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } } else { cellValue = ""; } return cellValue; } }
业务层实现导出具体业务
@Service @Transactional(rollbackFor = Throwable.class) public class MerchantGoodsService extends GoodsService { @Autowired private MerchantGoodsDao merchantGoodsDao; @Autowired private MerchantMerchantInfoService merchantMerchantInfoService; @Autowired @Qualifier("goodsMapStructImpl") private GoodsMapStruct goodsMapStruct; @Autowired private CustomMerchantInfoService customMerchantInfoService; @Autowired private GoodsExportService excelExport; /** * @Description: 批量导入商品 * @Param: [] * @return: void * @Author: * @Date: 2019/6/29 */ public void batchImport(HttpServletRequest request, MultipartFile file, String userId) throws IOException, InvalidFormatException { //卖家 MerchantInfo info = customMerchantInfoService.getByUserId(userId); //卖家添加商品默认下架状态 List<Goods> list = excelImport.analysisExcelByGoods(request, file, userId, info, false); //插入 merchantGoodsDao.batchInsert(list); } /** * @Description: 批量导出商品逻辑 * @Param: [vo, response, userId] * @return: void * @Author: * @Date: 2019/7/15 */ public void batchExport(GoodsQueryVo vo, HttpServletResponse response, String userId) throws IOException { //表头headers String[] headers = {"商品名(可修改)", "商品描述(可修改)", "所属分类", "原价", "基础单位", "最小销售规格", "销售价", "审核状态(可修改，0待审核，1审核通过，2审核失败，3暂存)", "销售状态(可修改，1上架，0下架)", "是否称重(可修改，1称重，0不称)", "供应商id", "供应商", "保质期", "首图url"}; // 获取所有当前卖家的未删除状态的商品 List<Goods> goodsList = merchantGoodsDao.selectGoodsList(vo, userId); List<List<String>> target = new ArrayList(); for (Goods goods : goodsList) { List<String> row = new ArrayList<>(); row.add(goods.getGoodsName()); row.add(goods.getGoodsDescription()); row.add(goods.getCategoryFullName()); row.add(String.valueOf(goods.getGoodsOriginalPrice())); row.add(goods.getGoodsUnit()); row.add(String.valueOf(goods.getGoodsMinUnit())); row.add(String.valueOf(goods.getGoodsSalesPrice())); //审核状态 Byte auditState = null; if (GoodsAuditStateEnum.AUDITING.getCode() == goods.getAuditState()) { auditState = GoodsAuditStateEnum.AUDITING.getCode(); } else if (GoodsAuditStateEnum.AUDIT_FAIL.getCode() == goods.getAuditState()) { auditState = GoodsAuditStateEnum.AUDIT_FAIL.getCode(); } else if (GoodsAuditStateEnum.AUDIT_OK.getCode() == goods.getAuditState()) { auditState = GoodsAuditStateEnum.AUDIT_OK.getCode(); }else if (GoodsAuditStateEnum.TEMP_SAVE.getCode() == goods.getAuditState()){ auditState = GoodsAuditStateEnum.TEMP_SAVE.getCode(); } row.add(String.valueOf(auditState)); //销售状态(上下架状态) Integer updown_state = null; if (goods.getUpdownState()) { updown_state = GoodsStateEnum.GOODS_ONLINE.getCode(); } else { updown_state = GoodsStateEnum.GOODS_DOWNLINE.getCode(); } row.add(String.valueOf(updown_state)); //是否称重 Byte ifWeight = null; if (goods.getIfWeighGoods()) { ifWeight = GoodsWeightEnum.WEIGHT.getCode(); } else { ifWeight = GoodsWeightEnum.UN_WEIGHT.getCode(); } row.add(String.valueOf(ifWeight)); //供应商id 供应商 保质期 首图url row.add(goods.getSupplierId()); row.add(goods.getSupplierName()); if (goods.getQualityGuaranteePeriod() != null) { row.add(DateUtils.format(goods.getQualityGuaranteePeriod(), "yyyy/MM/dd")); } row.add(goods.getGoodsFirstPic()); target.add(row); } String sheetName = "在售商品"; excelExport.exportTemplate(response, sheetName, headers, target); } }
商品导入控制层
@Controller @RequestMapping("goods") public class MerchantGoodsController extends GenericController { private static final Logger LOGGER = LoggerFactory.getLogger(MerchantGoodsController.class); @Autowired private FileService fileService; @Autowired private MerchantGoodsService merchantGoodsService; @Autowired private SecurityHandler securityHandler; /** * @Param [request, file] * @return com.hrt.framework.web.core.Result * @Author youjp * @Description //TODO 批量导入商品 * @throw **/ @PostMapping(value = "/goodsBatchImport") @ResponseBody public Result goodsBatchImport(HttpServletRequest request, MultipartFile file) throws IOException, InvalidFormatException { merchantGoodsService.batchImport(request, file, securityHandler.getUserId()); return Result.success(); } /** * @Param [response, vo] * @return void * @Author youjp * @Description //TODO 批量导出商品 * @throw **/ @GetMapping(value = "/goodsBatchExport") public void goodsBatchExport(HttpServletResponse response, GoodsQueryVo vo) throws IOException { merchantGoodsService.batchExport(vo, response, securityHandler.getUserId()); } }
前端接口请求：
- Excel导出时，只要请求导出接口即可
var url = window.mconfig.mbaseUrl + path.goodsBatchExport + "?access_token=" + access_token + "&goodsName=" + goodsName + "&categoryFullId=" + categoryFullId; window.location.href = url;
Excel导入：
var uploadInst2 = upload.render({ elem: '#batchUploadGoods', //绑定元素 accept: 'file', //普通文件 exts: 'xls|xlsx', //只允许上传压缩文件 url: window.mconfig.mbaseUrl + path.goodsBatchImport + "?access_token=" + access_token, //上传接口 done: function (res) { if (res.code == window.httpStatus.success) { layer.msg("导入成功", { icon: 6 }); table.reload('goodsTableId', { url: window.mconfig.mbaseUrl + path.page, async: false, request: { pageName: 'pageNo', // 页码的参数名称，默认：page limitName: 'pageSize' // 每页数据量的参数名，默认：limit }, page: { curr: 1 }, }); } else { layer.alert('导入失败：' + res.msg, { icon: 0, }); } }, error: function () { layer.msg("请联系管理员", { icon: 5 }); }, headers: { //通过 request 头传递 Authorization: layui.data(setter.tableName)[setter.headers.accessTokenName] } });
页面代码：
<div class="layui-input-inline"> <button class="layui-btn layui-btn-normal" lay-filter="exportGoods" lay-submit=""> <i class="layui-icon"></i>导出 </button> </div> <div class="layui-btn layui-btn-sm" id="batchUploadGoods"> <i class="layui-icon"></i>批量导入 <span id="batchUploadTag"></span> </div>
更详细的可以看一下这篇博文：
https://blog.csdn.net/typ1805/article/details/83279532
