//导入
@RequestMapping(params = "upload3")
public ModelAndView upload3(HttpServletRequest req) {
req.setAttribute("controller_name","decOrderController");
req.setAttribute("method_name","importonlyone");
return new ModelAndView("com/jeecg/decorder/dec_excel_upload");
}
//判断行是不是有效的,非空，不仅仅包含空格
public static boolean isRow(Row row){
for(int i=0 ; i<34;i++){
if(row.getCell(i) != null && !"".equals(row.getCell(i).toString().trim())){
return true;
}
}
return false;
}
@SuppressWarnings("unchecked")
@RequestMapping(params = "importonlyone", method = RequestMethod.POST)
@ResponseBody
public AjaxJson importonlyone(HttpServletRequest request, HttpServletResponse response) throws Exception {
AjaxJson j = new AjaxJson();
j.setMsg("导入成功");
j.setSuccess(true);
//拿到页面的账单日期
String zdDate = request.getParameter("zdDate");
if(StringUtils.isEmpty(zdDate)){
j.setMsg("账单日期不能为空！！");
return j;
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date zdDate1 = sdf.parse(zdDate);
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
List<MultipartFile> contactFile= new ArrayList<MultipartFile>();
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
MultipartFile file = entity.getValue();// 获取上传文件对象
contactFile.add(file);
}
XSSFWorkbook wb;//2007以前的是HSSFWorkbook
// 页
XSSFSheet sheet;//成品表
// 行
XSSFRow row;//成品表行
// 打开文件
try {
wb = new XSSFWorkbook(contactFile.get(0).getInputStream());
} catch (IOException e) {
e.printStackTrace();
wb = new XSSFWorkbook();
}
sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
int yxRowNum = 0;
int emptyNum = 0;
for(int i=0 ; i<=rowNum ; i++){
if(sheet.getRow(i) == null ){
emptyNum++;
if(emptyNum == 10){
yxRowNum -= 9;
break;
}
yxRowNum++;
}else{
if(isRow(sheet.getRow(i)) && emptyNum != 10){
emptyNum =0;
yxRowNum++;
}
}
}
System.out.println("有效行数："+yxRowNum);
String order_supplyid = "";//代理商id
String order_supply = "";//代理商name
String sql = "";
for(int i=2;i<yxRowNum-1;i++){
order_supply = getCellFormatValue(sheet.getRow(i).getCell(0)).trim();//物流公司名称
sql = " select count(1) from ldc_customer a where a.cus_company = '"+order_supply+"' order by cus_company ";
int cnt1 = jdbcTemplate.queryForInt(sql);
if(cnt1<1){
j.setMsg("导入失败:物流公司名称"+order_supply+"数据库没有对应数据匹配!!");
return j;
}
//验证发票号是否存在
String trim = getCellFormatValue(sheet.getRow(i).getCell(1)).trim();//发票号
sql = " select count(1) from ldc_order_tax a where a.Invoiceno = '"+trim+"'";
int cnt = jdbcTemplate.queryForInt(sql);
if(cnt>0){
j.setMsg("导入失败:"+trim+"数据库已存在!!");
return j;
}
sql = " select count(1) from ldc_order_tax a where a.order_tax_name = '物流费用合计金额' and a.Invoiceno = '"+trim+"' ";
cnt = jdbcTemplate.queryForInt(sql);
if(cnt>0){
j.setMsg("导入失败:发票号为"+trim+"的数据物流费已有'物流费用合计金额'!!");
return j;
}
}
for(int i=2;i<rowNum;i++){
XSSFCell cell = sheet.getRow(i).getCell(1);
String cellFormatValue = getCellFormatValue(cell);
if(StringUtils.isNotEmpty(cellFormatValue.trim())){
String invoiceno = getCellFormatValue(sheet.getRow(i).getCell(1));
if(StringUtils.isNotEmpty(invoiceno)){
//去将要set进入json字段的值
String uuid = UUID.randomUUID().toString().replaceAll("-","");
String jswlName = getCellFormatValue(sheet.getRow(i).getCell(0));//物流公司名称
String jsBLorawbNo = getCellFormatValue(sheet.getRow(i).getCell(2));//B/L OR AWB NO
String jsEta = getCellFormatValue(sheet.getRow(i).getCell(3));//ETA
String jsHy = getCellFormatValue(sheet.getRow(i).getCell(4));//海运整柜（注明箱型）
String jsJzxNum = getCellFormatValue(sheet.getRow(i).getCell(5));//集装箱数量
String jsWeight = getCellFormatValue(sheet.getRow(i).getCell(6));//毛重
String jsTj = getCellFormatValue(sheet.getRow(i).getCell(7));//体积
String jsSumfy = getCellFormatValue(sheet.getRow(i).getCell(26));//物流费用合计金额
String jsGsje = getCellFormatValue(sheet.getRow(i).getCell(27));//关税金额（必须和关税单金额核对）
String jsZzs = getCellFormatValue(sheet.getRow(i).getCell(28));//增值税（必须和关税单金额核对）
String jsFybm = getCellFormatValue(sheet.getRow(i).getCell(29));//费用部门
Map<String, Object> map = new HashMap<>();
map.put("id", uuid);
map.put("jswlName", jswlName);
map.put("jsBLorawbNo", jsBLorawbNo);
map.put("jsEta", jsEta);
map.put("jsHy", jsHy);
map.put("jsJzxNum", jsJzxNum);
map.put("jsWeight", jsWeight);
map.put("jsTj", jsTj);
map.put("jsSumfy", jsSumfy);
map.put("jsGsje", jsGsje);
map.put("jsZzs", jsZzs);
map.put("jsFybm", jsFybm);
String jsonString = JSONObject.toJSONString(map);
String hql =" from DecOrderEntity where 1=1 and invoice_Code = '"+invoiceno.trim()+"'";
List<DecOrderEntity> findHql = decOrderService.findHql(hql);
System.out.println(jsonString);
if(findHql!=null && findHql.size()>0){
for(int g=0;g<findHql.size();g++){
DecOrderEntity decOrderEntity = findHql.get(g);
decOrderEntity.setJsonExtract(jsonString);
decOrderService.saveOrUpdate(decOrderEntity);
}
}
}
for(int k =12;k<31;k++){
LdcOrderTaxEntity ldcordertaxentity = new LdcOrderTaxEntity();
String order_tax = getCellFormatValue(sheet.getRow(i).getCell(k));//费用
String order_tax_name = getCellFormatValue(sheet.getRow(1).getCell(k));//费用类型
if("物流费用合计金额".equals(order_tax_name)){
order_tax_name="物流费";
}
String jswlName = getCellFormatValue(sheet.getRow(i).getCell(0));//物流公司名称
sql = " select id from ldc_customer a where a.cus_company = '"+jswlName+"' order by cus_company ";
order_supplyid = jdbcTemplate.queryForObject(sql, String.class);
ldcordertaxentity.setInvoiceno(invoiceno);//设置票号
ldcordertaxentity.setOrderTax(order_tax);//设置费用
ldcordertaxentity.setOrderTaxName(order_tax_name);//设置费用类型
ldcordertaxentity.setZdDate(zdDate1);//设置账单时间
ldcordertaxentity.setOrderBizhi("CNY");
ldcordertaxentity.setOrdersl("0.0");
ldcordertaxentity.setOrderse("0.0");
ldcordertaxentity.setOrderSupplyid(order_supplyid);
ldcordertaxentity.setOrderSupply(jswlName);
if(StringUtils.isNotEmpty(order_tax.trim())){
ldcOrderTaxService.save(ldcordertaxentity);//保存
}
}
}
}
return j;
}
private String getCellFormatValue(XSSFCell xssfCell) {
String cellvalue = "";
if (xssfCell != null) {
// 判断当前Cell的Type
switch (xssfCell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case XSSFCell.CELL_TYPE_NUMERIC:
case XSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
Date date = xssfCell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue = String.valueOf((int)xssfCell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case XSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = xssfCell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
