HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，擴充名是.xls
XSSFWorkbook:是操作Excel2007的版本，擴充名是.xlsx
對于不同版本的EXCEL文檔要使用不同的工具類，如果使用錯了，會提示如下錯誤資訊。
org.apache.poi.openxml4j.exceptions.InvalidOperationException
org.apache.poi.poifs.filesystem.OfficeXmlFileException
package com.tonicare.service.mgmt.sales;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
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.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.components.Else;
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);
vo.getAjaxResult().setMessage("檔案 " + vo.getExcelFileName() + "導入失敗！");
}
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);
XSSFWorkbook xb=new XSSFWorkbook(ins);//通過得到的檔案流 建立一個HSSFWorkbook對象
XSSFSheet sheet=xb.getSheetAt(0);
int rows=sheet.getPhysicalNumberOfRows();
XSSFRow row=null;
//周遊
for(int i=1;i<=rows;i++){
row=sheet.getRow(i);
if (row == null) {
continue;
}
String sDate=vo.getStoredate().toString();
String aId=String.valueOf(this.getCell(row.getCell(3)));
List<CsStoreDate> store=dao.getCsStoreDateByDateAndID(sDate,aId, null, null);
CsStoreDate storebean=null;
if(store==null || store.size()<=0){
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(vo.getShopid());//商超編碼
storebean.setStoreName(String.valueOf(this.getCell(row.getCell(1))));//商超名稱
if(this.getCell(row.getCell(2))==null || this.getCell(row.getCell(2)).equals("")){
storebean.setWarehouse(null);//倉庫
}else{
storebean.setWarehouse(String.valueOf(this.getCell(row.getCell(2))));//倉庫
}
storebean.setProductId(String.valueOf(this.getCell(row.getCell(3))));//商品編碼
storebean.setSkuId(null);//sku編碼String.valueOf(this.getCell(row.getCell(4)))
storebean.setProductName(String.valueOf(this.getCell(row.getCell(5))));//商品名稱
if(this.getCell(row.getCell(7))==null || this.getCell(row.getCell(7)).equals("")){
storebean.setSupplierCode(null);//供應商id
}else{
storebean.setSupplierCode(Integer.valueOf(String.valueOf(this.getCell(row.getCell(7)))));//供應商id
}
storebean.setSupplierName(String.valueOf(this.getCell(row.getCell(8))));//供應商名稱
storebean.setMca1(String.valueOf(this.getCell(row.getCell(11))));//mcas一級類目
storebean.setMca2(String.valueOf(this.getCell(row.getCell(12))));//mcas二級類目
storebean.setMca3(String.valueOf(this.getCell(row.getCell(13))));//mcas三級類目
storebean.setMcaLast(String.valueOf(this.getCell(row.getCell(14))));//mcas末級類目
storebean.setWaiter(String.valueOf(this.getCell(row.getCell(16))));//招商小二 責任人
if(this.getCell(row.getCell(20))==null || this.getCell(row.getCell(20)).equals("")){
storebean.setStore(0);//庫存數量 物流寶可銷售庫存數
}else{
storebean.setStore(Integer.parseInt(String.valueOf(this.getCell(row.getCell(20)))));//庫存數量 物流寶可銷售庫存數
}
if(this.getCell(row.getCell(21))==null || this.getCell(row.getCell(21)).equals("")){
storebean.setLockup(0);//庫存數量 物流寶可銷售庫存數
}else{
storebean.setLockup(Integer.parseInt(String.valueOf(this.getCell(row.getCell(21)))));//鎖定庫存 物流寶鎖定庫數
}
storebean.setGoodId(null);//商品條碼
storebean.setSkuName(null);//規格名稱 規格的值每次都是空的 直接做空處理
storebean.setStoreStatus(null);//庫存狀态
storebean.setOccupy(null);//占用數量
storebean.setCatId(null);//淘寶類目id
storebean.setCatName(null);//淘寶類目名稱
storebean.setWarehouse(null);
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;
}
public Object getCell(XSSFCell cell){
Object value=null;
DecimalFormat df=new DecimalFormat("0");// 格式化 number String 字元
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");// 格式化日期字元串
DecimalFormat nf=new DecimalFormat();
switch(cell.getCellType()){
case XSSFCell.CELL_TYPE_STRING:
value=cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if(("@").equals(cell.getCellStyle().getDataFormatString())){
value=df.format(cell.getNumericCellValue());
}else if("General".equals(cell.getCellStyle().getDataFormatString())){
value=nf.format(cell.getNumericCellValue());
}else{
value=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value=cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
value="";
break;
default:
value=cell.toString();
break;
}
return value;
}
//将導入的庫存的資訊裡面的新産品添加進産品資訊裡面 如果是已經存在的産品則更新産品名稱和産品條碼
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());//商品名稱
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;
}
}
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)