poi导入excel
请看excel分类,其他的博客,下面的博客写的比较早,没啥太大利用价值,纯粹是复制代码
贴代码:
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
@Override
public List<String> upload(PProduct product, InputStream is,String lastName)throws Exception {
HqlBean hqlBean = HqlBeanCacheUtil.gethqlBeanCache().get("cn.digitalpublishing.dao.PProductDao").get("getList");
String statusYes = DicCache.getIdByCode(DicConstants.DIC_STATUS, DicConstants.DATA_STATUS_AVAILABLE);
Map<String, Object> condition = new HashMap<String, Object>();
Map<String, Object> params = new HashMap<String, Object>();
List<String> isbnList = new ArrayList<String>();
byte[] excelByte = IOUtils.toByteArray(is);
Workbook xwb = null;
try {
if(lastName.equals("xlsx")){
xwb = new XSSFWorkbook(new ByteArrayInputStream(excelByte));
}else{
xwb = new HSSFWorkbook(new ByteArrayInputStream(excelByte));
}
Sheet sheet = xwb.getSheetAt(0);
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
condition.clear();
Row row = sheet.getRow(i);
if (row != null) {
Cell isbn = row.getCell(0);
Cell title = row.getCell(1);
Cell publish = row.getCell(2);
Cell price = row.getCell(3);
Cell firstAuthor = row.getCell(4);
Cell translator = row.getCell(5);
Cell size = row.getCell(6);
Cell binding = row.getCell(7);
Cell pubDate = row.getCell(8);
String isDiagiTal = DicCache.getIdByCode(DicConstants.ISDIGITAL, DicConstants.ISDIGITAL_0);
int packageNum = 4;
int bookNum = 4;
String isTeaching = "";//DicCache.getIdByCode(DicConstants.DIC_STATUS, DicConstants.DATA_STATUS_AVAILABLE)
if ((isbn != null)||title != null||publish != null||price != null||firstAuthor != null) {
params.put("isbn", isbn.toString());
params.put("title", title.toString());
//params.put("publish", publish.toString()); 出版社
BigDecimal _price = new BigDecimal(price.toString());
params.put("price", _price);
params.put("firstAuthor", firstAuthor.toString());
//PProduct _product = this.daoFacade.getProductDao().getList(params, "", hqlBean).get(0);
List<PProduct> _listProduct = this.daoFacade.getProductDao().getList(params, "", hqlBean);
if(_listProduct.size()==0){
ServiceFactory service = new ServiceFactoryImpl();
PProductTypeService saleItemService = service.getPProductTypeService();
PProductType productTypeByCode = saleItemService.getProductTypeByCode("book");
PProduct _proLine = new PProduct();
_proLine.setIsbn(isbn.toString());
_proLine.setTitle(title.toString());
_proLine.setPublish(publish.toString());
_proLine.setPrice(_price);
_proLine.setFirstAuthor(firstAuthor.toString());
_proLine.setTranslator(translator.toString());
_proLine.setSize(size.toString());
_proLine.setBinding(binding.toString());
/*String _date = pubDate.toString();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
Date cd=sdf.parse(_date);
_proLine.setPubDate(cd);*/
_proLine.setIsDiagiTal(isDiagiTal);
_proLine.setPackageNum(packageNum);
_proLine.setBookNum(bookNum);
_proLine.setIsTeaching(isTeaching);
_proLine.setProductType(productTypeByCode);
_proLine.setOwnerFlag(DicCache.getIdByCode(DicConstants.PRODUCT_OWNER_FLAG, DicConstants.PRODUCT_OWNER_FLAG_OTHER));
_proLine.setStatus(DicCache.getIdByCode(DicConstants.DIC_STATUS, DicConstants.DATA_STATUS_AVAILABLE));
_proLine.setCreateOn(new Date());
/*String projectCode = EditorailSerialCode.getCode(Integer.valueOf(DicConstants.CODE_TYPE_PROJECT), crmCorpTypeRelationship.getCorp().getCode());
_proLine.setCode(projectCode);
_proLine.setProductIntegerNum(projectCode);*/
this.insertProduct(_proLine);
}else{
isbnList.add(title.toString());
}
}else{
throw new RuntimeException("第"+i+"行数据不完整,请认真填写");
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return isbnList;
}