juiでexcelファイルを読み込む
10908 ワード
1.freemarkerページ
2.strutsプロファイル
3.関連Java読取コード
<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;
}
}