• Java:Excel文件上传至后台


    之前的项目中有遇到上传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;
    	}
    }  
  • 相关阅读:
    job_queue_processes参数讲解
    Oracle trunc()函数的用法
    JSON字符串转JavaBean,net.sf.ezmorph.bean.MorphDynaBean cannot be cast to ……
    javax.swing.JList 设置分割线
    清除SVN版本控制文件
    MyEclipse SVN安装方法
    ExtJs xtype类型介绍
    Android中的“再按一次返回键退出程序”实现
    Android之NetworkOnMainThreadException异常
    HDU 3613 Best Reward
  • 原文地址:https://www.cnblogs.com/doufuyu/p/10992793.html
Copyright © 2020-2023  润新知