之前的项目中有遇到上传Excel文件的需求,简单说就是解析一个固定格式的Excel表格,然后存到数据库对应的表中,表格如下:
项目采用SSM架构,mvc模式,显而易见,这个Excel表需要拆成两个表,分别存入数据库中,这种表会解析,那其他的应该就难不倒我们了。我自己写的java后台的相关解析代码如下。
注意Excel文件中每个单元格的格式要准确,否则可能解析错误,如果有更加简洁的方案,欢迎大家在留言评论区,相互学习,共同进步!
依赖文件主要是ExcelUtil,其他的直接用maven导入就好,ExcelUtil的路径:https://i.cnblogs.com/Files.aspx
package com.yuandi.car.platform.controller; import java.io.ByteArrayInputStream; import java.math.BigDecimal; import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.text.SimpleDateFormat; import org.apache.commons.lang.time.DateUtils; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import com.yuandi.car.common.bean.RestDataResult; import com.yuandi.car.common.enums.ResponseEnum; import com.yuandi.car.common.exception.ServiceException; import com.yuandi.car.common.util.ExcelUtils; import com.yuandi.car.common.util.UUIDutil; import com.yuandi.car.platform.service.IManufactService; import io.swagger.annotations.Api; import java.util.UUID; import com.yuandi.car.platform.dao.model.T301_merchant; import com.yuandi.car.platform.dao.model.T701_car_order; import com.yuandi.car.platform.dao.model.T701_car_order_ext; @Controller @RequestMapping("/order") @Api(value = "order接口") public class OrderController extends BaseController { private static Logger logger = LoggerFactory.getLogger(OrderController.class); @Autowired private IManufactService manufactService; @RequestMapping(value = "/uploadDeal", method = RequestMethod.POST) @ResponseBody public RestDataResult uploadDeal(@RequestParam("file") MultipartFile file) { RestDataResult result = new RestDataResult(); try { if (validToken()) { String contentType = file.getContentType(); String fileName = file.getOriginalFilename(); Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(file.getBytes())); Sheet sheet = wb.getSheetAt(0); Row row = null; FormulaEvaluator formulaEvaluator = null; if (fileName.endsWith("xlsx")) { formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb); } else { formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb); } T701_car_order order = new T701_car_order(); String orderid = UUIDutil.getUUID(); order.setOrderid(orderid); //manufactService.addcarOrder(order); String ownerEntity = null; String mertPhone = null; boolean flag = false; for (int i = 1; i < 6; i++) { //i < sheet.getLastRowNum() - 0 + 1; row = sheet.getRow(i); if (null == row) { continue; } switch (i) { case 1: for (Cell c : row) {//循环单行的每一个空格 String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { order.setOwnerentity(value); ownerEntity = value; System.out.println("经销商法人姓名:" + value); } else if (c.getColumnIndex() == 5) { order.setMerchantphone(value); mertPhone = value; System.out.println("联系电话:" + value); } } break; case 2: for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { order.setManagername(value); System.out.println("公司分管经理:" + value); } else if (c.getColumnIndex() == 5) { order.setManagerphone(value); System.out.println("联系电话:" + value); } } break; case 3: for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { order.setMerchantname(value); System.out.println("经销商全称:" + value); } else if (c.getColumnIndex() == 5) { order.setAddress(value); System.out.println("收货地址:" + value); } } break; case 4: for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { Double f = Double.valueOf(value); Integer intvalue = (int)Math.ceil(f); order.setCapacity(intvalue); System.out.println("经销商库容:" + intvalue); } else if (c.getColumnIndex() == 5) { Date expDate = ExcelUtils.stringToNormalDate(value); order.setExptime(expDate); System.out.println("期望发货时间:" + expDate); } } break; case 5: for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { order.setMonopolize(value); System.out.println("经销商品牌经营:" + value); } else if (c.getColumnIndex() == 5) { order.setBrandnumber(value); System.out.println("品牌经营数量:" + value); } } break; } } for (int i = sheet.getLastRowNum() - 3 + 1; i < sheet.getLastRowNum() - 0 + 1; i++) { //i < sheet.getLastRowNum() - 0 + 1; row = sheet.getRow(i); if (null == row) { continue; } switch (i - sheet.getLastRowNum()-1) { case -3: for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { // TODO //Integer intvalue = Integer.parseInt(value); Double f = Double.valueOf(value); Integer intvalue = (int)Math.ceil(f); order.setTotal(intvalue); System.out.println("合计:" + value + " 辆"); } else if (c.getColumnIndex() == 5) { //Long f = Long.valueOf(value);Long f = Long.parseLong(value); BigDecimal bd=new BigDecimal(value); order.setTotalprice(bd); System.out.println("总价:" + value + " 元"); } } break; case -2: for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { // TODO order.setOrganizername(value); System.out.println("编制人姓名:" + value); } else if (c.getColumnIndex() == 4) { order.setOrganizerphone(value); System.out.println("编制人电话:" + value); } else if (c.getColumnIndex() == 6) { Date subTime = ExcelUtils.stringToNormalDate(value); order.setSubmittime(subTime); System.out.println("订单提报时间:" + subTime); } } break; case -1: for (Cell c : row) {//循环单行的每一个空格 String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { order.setRegulatorname(value); System.out.println("接收人姓名:" + value); } else if (c.getColumnIndex() == 4) { order.setRegulatorphone(value); System.out.println("接收人电话:" + value); } else if (c.getColumnIndex() == 6) { Date receiveTime = ExcelUtils.stringToNormalDate(value); order.setReceivetime(receiveTime); System.out.println("订单接收确认时间:" + receiveTime); } } break; } } if(ownerEntity.isEmpty()||mertPhone.isEmpty()){ result.setRespcode(ResponseEnum.PARAM_NULL.getCode()); result.setMessage(ResponseEnum.PARAM_NULL.getMessage()); }else{ T301_merchant merchant = manufactService.qryMerchantByOwnerPhone(ownerEntity, mertPhone); Date now = new Date(); order.setManufactid(merchant.getEntityid()); order.setName(merchant.getEntityname()); order.setMerchantid(merchant.getMerchantid()); order.setCreatetime(now); manufactService.addcarOrder(order); flag = true; } //以下内容添加到扩展表 for (int i = 7; i < sheet.getLastRowNum() - 3 + 1; i++) { row = sheet.getRow(i); if (null == row) { continue; } T701_car_order_ext orext = new T701_car_order_ext(); orext.setOrderid(orderid); for (Cell c : row) { String value = null; boolean isMerge = ExcelUtils.isMergedRegion(sheet, i, c.getColumnIndex()); // 判断是否具有合并单元格 if (isMerge) { value = ExcelUtils.getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); } else { value = ExcelUtils.getCellValueFormula(c, formulaEvaluator); } if (c.getColumnIndex() == 2) { orext.setModel(value); } else if (c.getColumnIndex() == 3) { orext.setVersion(value); } else if (c.getColumnIndex() == 4) { Double f = Double.valueOf(value); Integer intvalue = (int)Math.ceil(f); orext.setTotal(intvalue); System.out.println("数量:" + value + " 辆"); } else if (c.getColumnIndex() == 5) { BigDecimal bd=new BigDecimal(value); orext.setUnitprice(bd); System.out.println("单价:" + bd + " 元"); } else if (c.getColumnIndex() == 6) { //Long f = Long.valueOf(value);Long f = Long.parseLong(value); BigDecimal bd=new BigDecimal(value); orext.setTotalprice(bd); System.out.println("金额:" + value + " 元"); } else if (c.getColumnIndex() == 7) { orext.setProperty(value); System.out.println("属性:" + value); } } if(flag){ manufactService.addcarOrderExt(orext); }else{ result.setRespcode(ResponseEnum.PARAM_NULL.getCode()); result.setMessage(ResponseEnum.PARAM_NULL.getMessage()); } } setSucc(result); } else { setInvalidToken(result); } } catch (ServiceException e) { result.setCodeMessage(e.getErrorCode(), e.getMessage()); } catch (Exception e) { logger.error("", e); result.setRespcode(ResponseEnum.SYS_ERROR.getCode()); result.setMessage(ResponseEnum.SYS_ERROR.getMessage()); } return result; } }