运用jui 读取excel文件

1.freemarker页面

 

<div class="pageHeader">
<form id="pagerForm" onsubmit="return iframeCallback(this);" enctype="multipart/form-data" action="sales/salesTmallStoreOrder.do?opType=UPLOAD" method="post">
		<div class="searchBar">
			<table class="searchContent">
				<tr>
					<td>选择时间：</td>
					<td><input type="text" name="storedate" class="date required" readonly="true" /></td>
					<td>天猫超市库存Excel：<input type="file" name="excel" /></td>
					<td><div class="buttonActive"><div class="buttonContent"><button type="submit">提交</button></div></div></td>
				</tr>
			</table>
		</div>
</form>
	</div>

 

 

2.struts配置文件

 

<struts>
	<package name="sales" extends="struts-tonicare" namespace="/mgmt/sales">
		<global-results>
			<result name="notLogin" type="freemarker">/WEB-INF/template${theme}/mgmt/main/ajaxResult.ftl</result>
		</global-results>
		<action name="salesTmallStoreOrder" class="mgmt.sales.SalesTmallStoreOrderAction">
			<result name="success" type="freemarker">/WEB-INF/template${theme}/mgmt/sales/salesTmallStoreOrder.ftl</result>
		</action>
	</package>
	
</struts>

 

 

3.相关的Java读取代码

 

import java.io.File;
import java.io.FileInputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.tonicare.action.mgmt.sales.vo.SalesTmallStoreOrderVo;
import com.tonicare.data.bean.CsStoreDate;
import com.tonicare.data.bean.LmProduct;
import com.tonicare.data.dao.SalesTMallDao;
import com.tonicare.framework.layer.MgmtService;
import com.tonicare.framework.layer.TonicareException;
import com.tonicare.framework.layer.AjaxResultVo.StatusCode;
import com.tonicare.util.BeanFactory;
import com.tonicare.util.Config;
import com.tonicare.util.Const;
import com.tonicare.util.LogHelper;

public class SalesTmallStoreOrderService extends MgmtService {

	@Override
	public Object invoke(Object inData) throws TonicareException {
		// TODO Auto-generated method stub
		SalesTmallStoreOrderVo vo=(SalesTmallStoreOrderVo) inData;
		if (Const.OPERATE_UPLOAD.equals(vo.getOpType())) {
			if (Config.DEBUG) 
				LogHelper.info("ExcelFileName=" + vo.getExcelFileName() + "\tExcelContentType=" + vo.getExcelContentType() + "\tExcel.AbsolutePath=" + vo.getExcel().getAbsolutePath());
				vo.getAjaxResult().init(StatusCode.error);
			
			if (vo.getExcel()!=null) {
				Date now = new Date();
				String fileName = DateFormatUtils.format(now, Const.TIME_FORMAT_NUMBER);
				int records= this.readExcel(vo.getExcel(), fileName,vo);
				vo.getAjaxResult().init(StatusCode.ok);
				vo.getAjaxResult().setMessage("文件 " + vo.getExcelFileName() + "(" + records + "条订单) 导入成功！");
			}
		}	
		return null;
	}
	
	public int readExcel(File aExcel, String aFile,SalesTmallStoreOrderVo vo) throws TonicareException{
		int number=0;
		
		FileInputStream ins=null;
		try {
			Map<Long,LmProduct> maps=this.getProductsBymap();
			SalesTMallDao dao=(SalesTMallDao) BeanFactory.getDao(Const.DAO_SALES_SALESTMALLDAO);
			
			ins=new FileInputStream(aExcel);
			HSSFWorkbook wb=new HSSFWorkbook(ins);//通过得到的文件流 创建一个HSSFWorkbook对象
			
			HSSFSheet sheet=wb.getSheetAt(0);//第一个工作表
			int rows=sheet.getLastRowNum();
			
			HSSFRow row=null;
			//遍历
			for(int i=1;i<rows;i++){
				row=sheet.getRow(i);				
				String sDate=vo.getStoredate().toString();
				String aId=String.valueOf(this.getCellLong(row, 2));
				String store_status=this.getCellString(row, 7);
				String warehouse=this.getCellString(row, 11);
				
				List<CsStoreDate> store=dao.getCsStoreDateByDateAndID(sDate, aId, store_status, warehouse);
				CsStoreDate storebean=null;
				if(store.size()<=0 ||store==null){
					 storebean=new CsStoreDate();
				}else{
					storebean=store.get(0);
					storebean.setId(store.get(0).getId());
				}	
					storebean.setStoreDate(DateUtils.parseDate(vo.getStoredate(), Const.DEFAULT_DATE_FORMAT_ARRAY));
					storebean.setStoreCode(this.getCellString(row, 0));
					storebean.setStoreName(this.getCellString(row, 1));
					storebean.setProductId(String.valueOf(this.getCellLong(row, 2)));
					storebean.setProductName(this.getCellString(row, 3));
					storebean.setSkuId(this.getCellString(row, 4));			
	 				storebean.setGoodId(this.getCellString(row, 6));
					storebean.setStoreStatus(this.getCellString(row, 7);
					storebean.setLockup((int)this.getCellLong(row, 8));
					storebean.setWarehouse(this.getCellString(row, 11);
					storebean.setStore((int)this.getCellLong(row, 9));
					storebean.setOccupy((int)this.getCellLong(row, 10);
					storebean.setCatId(String.valueOf(this.getCellLong(row, 12)));
					storebean.setCatName(this.getCellString(row, 13));
					storebean.setSupplierCode((int)this.getCellLong(row, 14));
					storebean.setSupplierName(this.getCellString(row, 15));
					storebean.setWaiter(this.getCellString(row, 16));
					storebean.setMca1(this.getCellString(row, 17));
					storebean.setMca2(this.getCellString(row, 18));
					storebean.setMca3(this.getCellString(row, 19));
					storebean.setMcaLast(this.getCellString(row, 20));
					storebean.setCreateTime(new Date());
					
					LmProduct pros=this.dealProduct(storebean,maps);
					if(pros!=null){
						storebean.setDeleted(0);
					}else{
						storebean.setDeleted(1);//删除标志
					}
					dao.saveOrUpdate(storebean);//添加cs_store_date
					number++;
			}
			this.saveProductByMap(maps);//订单中有新品存在 就保存 
			this.saveExcel(aExcel, aFile);//备份excel文件
			
		} catch (Exception e) {
			// TODO: handle exception
			number = 0;
			e.printStackTrace();
			throw new TonicareException();
		}
		return number;
	}
	
	//lmproduct产品表里面有的产品 库存表里面在标记为正常否则为删除商品
	private LmProduct dealProduct(CsStoreDate aOrder, Map<Long,LmProduct> aMap) {
		LmProduct result = null;
		if (aOrder!=null) {
			Long productId = Long.valueOf(aOrder.getProductId().toString());
			result = aMap.get(productId);
			if (result==null) {
				result = new LmProduct();
				result.setId(Long.valueOf(aOrder.getProductId().toString()));
				result.setUpdateTime(new Date());
			}
			result.setName(aOrder.getProductName());
			result.setCode(aOrder.getGoodId());
			aMap.put(productId, result);
		}
		return result;
	}
	
	//将导入的库存的信息里面的新产品添加进产品信息里面  如果是已经存在的产品则更新产品名称和产品条码
	private void saveProductByMap(Map<Long,LmProduct> aMap) {
		if (aMap!=null) {
			SalesTMallDao dao = (SalesTMallDao) BeanFactory.getDao(Const.DAO_SALES_SALESTMALLDAO);
			try {
				Iterator it = aMap.entrySet().iterator();
				while (it.hasNext()) {
					Map.Entry<Long,LmProduct> entry = (Map.Entry<Long,LmProduct>) it.next();
					long key = entry.getKey();
					LmProduct val = entry.getValue();
					
					LmProduct bean = dao.getProductById(key);
					if (bean==null) {
						bean = new LmProduct();
						bean.setId(key);//商品id
						bean.setPlatform(2);//2  代表的是天猫平台
						bean.setDeleted(0);//0 代表在售商品
						bean.setUpdateTime(new Date());
					}
					bean.setName(val.getName());//商品名称
					bean.setCode(val.getCode());//商品条码
					dao.saveOrUpdate(bean);
				}
			}
			catch(Exception ex) {
				ex.printStackTrace();
			}
		}
	}
	
	//得到天猫所有的产品
	private Map<Long,LmProduct> getProductsBymap(){
		Map<Long, LmProduct> map=new HashMap<Long, LmProduct>();
		SalesTMallDao dao=(SalesTMallDao) BeanFactory.getDao(Const.DAO_SALES_SALESTMALLDAO);
		try {
			List<LmProduct> list=dao.getTmallProduct();
			for(LmProduct row:list){
				map.put(row.getId(),row);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return map;
	}
	
	//保存excel文件
	private boolean saveExcel(File aSrc, String aFileName) {
		boolean result = false;
		String path = Config.UPLOAD_PATH + File.separator + "tmall" + File.separator + aFileName + ".xls";
		try {
			File dst = new File(path);
			FileUtils.moveFile(aSrc, dst);
			result = true;
		}catch (Exception e) {
			result = false;
			e.printStackTrace();
		}
		return result;
	}
	
	private String getCellString(HSSFRow aRow, int aIndex) {
		String result = StringUtils.EMPTY;
		if (aRow!=null) {
			HSSFCell cell = aRow.getCell(aIndex);
			if (cell!=null){			
				result = cell.toString();
			}
		}
		return result;
	}
	private long getCellLong(HSSFRow aRow, int aIndex) {
		long result = 0;
		if (aRow!=null) {
			double t = this.getCellDouble(aRow, aIndex);
			result = Math.round(t);
		}
		return result;
	}
	private double getCellDouble(HSSFRow aRow, int aIndex) {
		double result = 0;
		
		if (aRow!=null) {
			HSSFCell cell = aRow.getCell(aIndex);
			if (cell!=null)
				result = cell.getNumericCellValue();
		}
		return result;
	}
	
}