Excle上传,POI解析,并做批处理保存

/**
* 出库上传
*
* @param file
* @param map
* @return
* @throws IOException
*/
@SuppressWarnings({“rawtypes”, “unchecked”, “resource”})
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
@FileResolver
public void excelImport(UploadFile file, Map<String, Object> map) throws Exception {
try {
BigDecimal suminvQty = BigDecimal.ZERO;
BigDecimal suminvWgt = BigDecimal.ZERO;
String fileName = file.getFileName();
String typeName = fileName.substring(fileName.lastIndexOf(".") + 1);
InputStream is = file.getInputStream();
//解析
Workbook wb = create(is);
// Workbook wb = “xls”.equals(typeName) ? new XSSFWorkbook(is) : new XSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(1);
//导入的Excel表数据总行数(下标从0开始)
int rowNum = sheet.getLastRowNum() + 1;
//导入的Excel表数据总列数(下标从0开始，
int colNum = row.getPhysicalNumberOfCells();
String loginUserName = AppUtils.getLoginUserName();
String loginUserDept = glBusinessUtil.getManageDeptByEmpId(loginUserName);
Collection dataList = new ArrayList();
List list = new ArrayList<>();
for (int i = 1; i < rowNum; i++) {
Tbglck1003 tbglck1003 = new Tbglck1003();
tbglck1003.setPkGl1003(AppUtils.getUUIDKey());
tbglck1003.setCompId(map.get(“compId”).toString());
tbglck1003.setCreateEmp(AppUtils.getLoginUserName());
tbglck1003.setCreateDate(new Date());
tbglck1003.setGlckNo(map.get(“glckNo”).toString());
tbglck1003.setItemNo(map.get(“itemNo”).toString());
tbglck1003.setDefs01(“0001”);//导入数据
//获取当前行数据
row = sheet.getRow(i);
if (StringUtils.isNotBlank(getCellFormatValue(row.getCell(1)).trim())) {
for (int j = 0; j < colNum; j++) {
switch (j) {
case 0://结算单号
String billNo = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(billNo)) {
tbglck1003.setBillNo(billNo);
}
break;
case 2://单据日期
String billDate = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(billDate)) {
tbglck1003.setBillDate(sdf.parse(billDate));
}
break;
case 3://入库单号
String stockNo = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(stockNo)) {
tbglck1003.setStockNo(stockNo);
}
break;
case 26://业务部门
String billDept = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(billDept)) {
tbglck1003.setBillDept(billDept);
}
break;
case 27://业务人员
String billEmp = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(billEmp)) {
tbglck1003.setBillEmp(billEmp);
}
break;
case 6://结算厂商名称
String settleVendorName = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(settleVendorName)) {
tbglck1003.setSettleVendorName(settleVendorName);
}
break;
case 4://子板号
String invId = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(invId)) {
tbglck1003.setInvId(invId);
}
break;
case 8://品名
String goodsName = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(goodsName)) {
tbglck1003.setGoodsName(goodsName);
}
break;
case 12://数量
String invQty = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(invQty)) {
suminvQty = suminvQty.add(new BigDecimal(invQty));
tbglck1003.setInvQty(new BigDecimal(invQty));
} else {
tbglck1003.setInvQty(BigDecimal.ZERO);
}
break;
case 13://重量
String invWgt = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(invWgt)) {
suminvWgt = suminvWgt.add(new BigDecimal(invWgt));
tbglck1003.setInvWgt(new BigDecimal(invWgt));
} else {
tbglck1003.setInvWgt(BigDecimal.ZERO);
}
break;
case 15://订单编号
String orderItem = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(orderItem)) {
tbglck1003.setOrderItem(orderItem);
}
break;
case 16://新工厂提单号
String newfacNo = getCellFormatValue(row.getCell(j)).trim();
if (StringUtils.isNotBlank(newfacNo)) {
tbglck1003.setNewfacNo(newfacNo);
}
break;
default:
}
}
list.add(tbglck1003);
}
}
save(list);
Tbglck1002 tbglck1002 = new Tbglck1002();
tbglck1002.setPkGl1002(AppUtils.getUUIDKey());
tbglck1002.setPkGl1001(map.get(“pkGl1001”).toString());
tbglck1002.setCompId(map.get(“compId”).toString());
tbglck1002.setGlckNo(map.get(“glckNo”).toString());
tbglck1002.setItemNo(“0001”);
tbglck1002.setCheckDate(new Date());
tbglck1002.setItemName(“上传数据”);
tbglck1002.setStatus(“C”);
tbglck1002.setRemark(“共”+rowNum+“笔,”+“数量合计:”+suminvQty+",重量合计:"+suminvWgt);
saveGlck1002(tbglck1002,this.getSession());
} catch (IOException e) {
e.printStackTrace();
throw new IOException(e.getMessage());
}
}

/**
*POI解析Excle
**/
public static Workbook create(InputStream in) throws
IOException, InvalidFormatException {
if (!in.markSupported()) {
in = new PushbackInputStream(in, 8);
}
if (POIFSFileSystem.hasPOIFSHeader(in)) {
return new HSSFWorkbook(in);
}
if (POIXMLDocument.hasOOXMLHeader(in)) {
return new XSSFWorkbook(OPCPackage.open(in));
}
throw new IllegalArgumentException(“你的excel版本目前poi解析不了”);
}

/**
*批处理保存
**/
public void save(List list) throws Exception{
Session session = getSessionFactory().openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<list.size(); i++ ) {
session.save(list.get(i));
if ( i % 50 == 0 ) { //50, same as the JDBC batch size //50,与JDBC批量设置相同
//flush a batch of inserts and release memory:
//将本批插入的对象立即写入数据库并释放内存
session.flush();
session.clear();
}
}
tx.commit();
session.close();
}