package com.shlanbao.tzsc.pms.cos.spare.controller;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileNotFoundException;
|
import java.io.InputStream;
|
import java.math.BigDecimal;
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.Map;
|
|
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.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.xssf.usermodel.XSSFCell;
|
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
|
import org.apache.poi.xssf.usermodel.XSSFRow;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
import com.shlanbao.tzsc.base.mapping.MdEqpType;
|
import com.shlanbao.tzsc.base.mapping.MdEquipment;
|
import com.shlanbao.tzsc.base.mapping.MdWorkshop;
|
import com.shlanbao.tzsc.pms.cos.spare.beans.CosSparePartsBean;
|
import com.shlanbao.tzsc.pms.equ.overhaul.beans.EqmMaintainBean;
|
import com.shlanbao.tzsc.pms.sys.datadict.beans.SysEqpTypeBean;
|
import com.shlanbao.tzsc.utils.params.SysEqpTypeBase;
|
import com.shlanbao.tzsc.utils.tools.StringUtil;
|
|
public class InputFilesController {
|
public List<CosSparePartsBean> readXlsx2007(String url) {
|
try {
|
List<CosSparePartsBean> listAll = new ArrayList<CosSparePartsBean>();
|
Map<String, String> unitMap=SysEqpTypeBase.getUnit();
|
Map<String, String> eqpTypeMap=SysEqpTypeBase.getEqpTypeNameAndID();
|
// String fileName = url;
|
FileInputStream is = new FileInputStream(new File(url));
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet - 2007excel
|
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
|
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
|
if (xssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
|
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
|
if (xssfRow == null) {
|
continue;
|
}
|
CosSparePartsBean cspb = new CosSparePartsBean();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
|
XSSFCell xssfCell = xssfRow.getCell(cellNum);
|
if (xssfCell == null) {
|
continue;
|
}
|
String vl = getValue2007(xssfCell);
|
//型号（编码）
|
if (cellNum == 0) {
|
if(vl.trim().equals("")){
|
continue;
|
}
|
cspb.setSpareCode(vl.replace(".0", ""));
|
} else if (cellNum == 1) {
|
cspb.setName(vl);//物料名称
|
} else if (cellNum == 2) {
|
cspb.setType(vl);//物料号
|
} else if (cellNum == 3) {
|
cspb.setEqpTypeId(eqpTypeMap.get(vl));//所属机型
|
} else if (cellNum == 4) {
|
cspb.setUnitId(unitMap.get(vl));//物料单位
|
} else if (cellNum == 5) {
|
try{
|
cspb.setPrice(Double.parseDouble(vl));//单价
|
}catch(Exception e){
|
cspb.setPrice(0.0);
|
};
|
} else if (cellNum == 6) {
|
cspb.setNumber(vl);//库存
|
}else if(cellNum == 7){
|
cspb.setRemark(vl);//备注；物料组描述
|
}
|
}
|
listAll.add(cspb);
|
}
|
}
|
return listAll;
|
} catch (Exception e) {
|
// TODO: handle exception
|
}
|
return null;
|
}
|
/**
|
* shisihai 2015-10-19
|
* @param xssfCell
|
* @return
|
*/
|
@SuppressWarnings("static-access")
|
private String getValue2007(XSSFCell xssfCell) {
|
if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
|
return String.valueOf(xssfCell.getBooleanCellValue());
|
} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
|
String value="";
|
// 纯数字
|
BigDecimal big=new BigDecimal(xssfCell.getNumericCellValue());
|
value = big.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
|
//解决1234.0 去掉后面的.0
|
if(null!=value&&!"".equals(value.trim())){
|
String[] item = value.split("[.]");
|
if(1<item.length&&"00".equals(item[1])){
|
value=item[0];
|
}
|
}
|
return value;
|
// Double itf = xssfCell.getNumericCellValue();
|
// return String.valueOf(itf.toString());
|
} else {
|
return String.valueOf(xssfCell.getStringCellValue());
|
}
|
}
|
|
// 循环解析 - 2003excel
|
public List<CosSparePartsBean> readXls2003(String url) {
|
List<CosSparePartsBean> listAll = new ArrayList<CosSparePartsBean>();
|
Map<String, String> unitMap=SysEqpTypeBase.getUnit();
|
Map<String, String> eqpTypeMap=SysEqpTypeBase.getEqpTypeNameAndID();
|
try {
|
InputStream is = new FileInputStream(url);
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet
|
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
|
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
|
if (hssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
|
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
|
if (hssfRow == null) {
|
continue;
|
}
|
CosSparePartsBean cspb = new CosSparePartsBean();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
|
HSSFCell hssfCell = hssfRow.getCell((short) cellNum);
|
if (hssfCell == null) {
|
continue;
|
}
|
String vl = getValue2003(hssfCell);
|
//型号（编码）
|
if (cellNum == 0) {
|
if(vl.trim().equals("")){
|
continue;
|
}
|
cspb.setSpareCode(vl.replace(".0", ""));
|
} else if (cellNum == 1) {
|
cspb.setName(vl);//物料名称
|
} else if (cellNum == 2) {
|
cspb.setType(vl);//物料号
|
} else if (cellNum == 3) {
|
cspb.setEqpTypeId(eqpTypeMap.get(vl));//所属机型
|
} else if (cellNum == 4) {
|
cspb.setUnitId(unitMap.get(vl));//物料单位
|
} else if (cellNum == 5) {
|
try{
|
cspb.setPrice(Double.parseDouble(vl));//单价
|
}catch(Exception e){
|
cspb.setPrice(0.0);
|
};
|
} else if (cellNum == 6) {
|
cspb.setNumber(vl);//库存
|
}else if(cellNum == 7){
|
cspb.setRemark(vl);//备注；物料组描述
|
}
|
}
|
listAll.add(cspb);
|
}
|
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return listAll;
|
}
|
|
/**
|
* 数据字典批量导入excel，润滑、点检、日保、轮保
|
* shisihai
|
* @param url
|
* @return 1 点检 , 2轮保 , 3润滑, 4日保
|
* @throws Exception
|
*/
|
public List<SysEqpTypeBean> readXls2003_2(String url, String type) throws Exception {
|
List<SysEqpTypeBean> listAll = new ArrayList<SysEqpTypeBean>();
|
//点检类型map
|
Map<String,String> DJmap=SysEqpTypeBase.getDJRoleAlls();
|
//轮保类型map
|
Map<String,String> LBmap=SysEqpTypeBase.getLBRoleAlls();
|
// 润滑部位
|
Map<String, String> rb = SysEqpTypeBase.getRHRoleAlls();
|
// id综合
|
Map<String, String> ids = SysEqpTypeBase.getBaseDatas();
|
//rh zhouqi
|
Map<String,String> rhIds=SysEqpTypeBase.getRh2();
|
InputStream is = new FileInputStream(url);
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet
|
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
|
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
|
if (hssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
|
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
|
if (hssfRow == null) {
|
continue;
|
}
|
SysEqpTypeBean etb = new SysEqpTypeBean();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
|
HSSFCell hssfCell = hssfRow.getCell((short) cellNum);
|
if (hssfCell == null) {
|
continue;
|
}
|
String vl = getValue2003(hssfCell);
|
// 点检 点检4列数据
|
if (type.equals("1")) {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
} else if (cellNum == 2) {
|
etb.setDjType(DJmap.get(vl));
|
} else if (cellNum == 3) {
|
etb.setDjMethodId(vl);
|
}
|
}
|
// 2轮保 轮保3列
|
else if (type.equals("2")) {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
} else if (cellNum == 2) {
|
etb.setLxType(LBmap.get(vl));
|
}
|
}
|
// 3润滑 润滑10列
|
else if (type.equals("3")) {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
}
|
// 点数
|
else if (cellNum == 2) {
|
etb.setNumber(vl);
|
}
|
// 润滑油 id
|
else if (cellNum == 3) {
|
etb.setOilId(ids.get(vl));
|
}
|
// 加注量
|
else if (cellNum == 4) {
|
etb.setFillQuantity(vl);
|
}
|
// 加注单位 id
|
else if (cellNum == 5) {
|
etb.setCategoryId(ids.get(vl));
|
}
|
// 方式
|
else if (cellNum == 6) {
|
etb.setFashion(ids.get(vl));
|
}
|
// 润滑部位ok
|
else if (cellNum == 7) {
|
if(rb.get(vl)!=null){
|
etb.setRhPart(rb.get(vl));
|
}else{
|
etb.setRhPart(vl);
|
}
|
// etb.setRhPart(rb.get(vl));
|
}
|
// 润滑周期ok
|
else if (cellNum == 8) {
|
try {
|
etb.setPeriod(Integer.valueOf(vl.trim()
|
.substring(0, vl.indexOf("."))));
|
} catch (Exception e) {
|
etb.setPeriod(0);
|
}
|
}
|
// 周期单位
|
else if (cellNum == 9) {
|
etb.setPeriodUnit2(rhIds.get(vl));
|
}
|
}
|
// 4日保 日保2列
|
else {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
}
|
}
|
}
|
listAll.add(etb);
|
}
|
|
}
|
return listAll;
|
}
|
|
/**
|
* 数据字典批量导入excel，润滑、点检、日保、轮保
|
* shisihai
|
* @param url
|
* @return
|
* @throws Exception
|
*/
|
public List<SysEqpTypeBean> readXlsx2007_2(String url, String type) throws Exception {
|
List<SysEqpTypeBean> listAll = new ArrayList<SysEqpTypeBean>();
|
//点检类型map
|
Map<String,String> DJmap=SysEqpTypeBase.getDJRoleAlls();
|
//轮保类型map
|
Map<String,String> LBmap=SysEqpTypeBase.getLBRoleAlls();
|
// 润滑部位
|
Map<String, String> rb = SysEqpTypeBase.getRHRoleAlls();
|
// id综合
|
Map<String, String> ids = SysEqpTypeBase.getBaseDatas();
|
//rh zhouqi
|
Map<String,String> rhIds=SysEqpTypeBase.getRh2();
|
// String fileName = url;
|
FileInputStream is = new FileInputStream(new File(url));
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet - 2007excel
|
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
|
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
|
if (xssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
|
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
|
if (xssfRow == null) {
|
continue;
|
}
|
SysEqpTypeBean etb = new SysEqpTypeBean();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
|
XSSFCell xssfCell = xssfRow.getCell(cellNum);
|
if (xssfCell == null) {
|
continue;
|
}
|
String vl = getValue2007(xssfCell);
|
// //点检 点检4列数据
|
if (type.equals("1")) {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
} else if (cellNum == 2) {
|
etb.setDjType(DJmap.get(vl));
|
} else if (cellNum == 3) {
|
etb.setDjMethodId(vl);
|
}
|
}
|
// 2轮保 轮保3列
|
else if (type.equals("2")) {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
} else if (cellNum == 2) {
|
etb.setLxType(LBmap.get(vl));
|
}
|
}
|
// 3润滑 润滑10列
|
else if (type.equals("3")) {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
}
|
// 点数
|
else if (cellNum == 2) {
|
etb.setNumber(vl);
|
}
|
// 润滑油 id
|
else if (cellNum == 3) {
|
etb.setOilId(ids.get(vl));
|
}
|
// 加注量
|
else if (cellNum == 4) {
|
etb.setFillQuantity(vl);
|
}
|
// 加注单位 id
|
else if (cellNum == 5) {
|
etb.setCategoryId(ids.get(vl));
|
}
|
// 方式
|
else if (cellNum == 6) {
|
etb.setFashion(ids.get(vl));
|
}
|
// 润滑部位ok
|
else if (cellNum == 7) {
|
if(rb.get(vl)!=null){
|
etb.setRhPart(rb.get(vl));
|
}else{
|
etb.setRhPart(vl);
|
}
|
}
|
// 润滑周期ok
|
else if (cellNum == 8) {
|
try {
|
etb.setPeriod(Integer.valueOf(vl.trim()
|
.substring(0, vl.indexOf("."))));
|
} catch (Exception e) {
|
etb.setPeriod(0);
|
}
|
}
|
// 周期单位
|
else if (cellNum == 9) {
|
etb.setPeriodUnit2(rhIds.get(vl));
|
}
|
}
|
// 4日保 日保2列
|
else {
|
if (cellNum == 0) {
|
etb.setName(vl);
|
} else if (cellNum == 1) {
|
etb.setDes(vl);
|
}
|
}
|
}
|
listAll.add(etb);
|
}
|
}
|
return listAll;
|
}
|
/**
|
* shisihai 2015-10-19
|
* @param hssfCell
|
* @return
|
*/
|
@SuppressWarnings("static-access")
|
private String getValue2003(HSSFCell hssfCell) {
|
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
|
return String.valueOf(hssfCell.getBooleanCellValue());
|
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
|
String value="";
|
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
|
//如果是date类型则 ，获取该cell的date值
|
Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
value = format.format(date);;
|
}else {// 纯数字
|
BigDecimal big=new BigDecimal(hssfCell.getNumericCellValue());
|
value = big.setScale(3, BigDecimal.ROUND_HALF_UP).toString();
|
//解决1234.0 去掉后面的.0
|
if(null!=value&&!"".equals(value.trim())){
|
String[] item = value.split("[.]");
|
if(1<item.length&&"000".equals(item[1])){
|
value=item[0];
|
}
|
}
|
}
|
return value;
|
} else {
|
return String.valueOf(hssfCell.getStringCellValue());
|
}
|
}
|
|
/**
|
* 设备检修excel导入xls2003
|
*
|
* @param url
|
* @return
|
* @throws FileNotFoundException
|
*/
|
public List<EqmMaintainBean> readXls2003_3(String url) throws Exception {
|
|
List<EqmMaintainBean> listAll = new ArrayList<EqmMaintainBean>();
|
|
// 设备name和id综合
|
Map<String, String> eqpMap = SysEqpTypeBase.getAllEqpNameAndID();
|
InputStream is = new FileInputStream(url);
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet
|
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
|
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
|
if (hssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
|
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
|
if (hssfRow == null) {
|
continue;
|
}
|
EqmMaintainBean etb = new EqmMaintainBean();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
|
HSSFCell hssfCell = hssfRow.getCell((short) cellNum);
|
if (hssfCell == null) {
|
continue;
|
}
|
String vl = getValue2003(hssfCell);
|
// 设备名 需要转化成设备id
|
/*if (cellNum == 0) {
|
if (vl == "" || vl.equals("null")) {
|
return listAll;
|
}
|
etb.setEqp_id(eqpMap.get(vl));
|
}
|
// 部位
|
else if (cellNum == 1) {
|
etb.setPlace(vl);
|
}
|
// 检修内容
|
else if (cellNum == 2) {
|
etb.setContents(vl);
|
}
|
// 计划执行日期
|
else if (cellNum == 3) {
|
etb.setDate_plans(getDate2003(hssfCell));
|
}
|
// 计划负责人 需要转化成id
|
else if (cellNum == 4) {
|
etb.setBlame_usr_id(vl);
|
}
|
// 解决措施
|
else if (cellNum == 5) {
|
etb.setSolution(vl);
|
}
|
// 实际完成人 转id 可能有多个
|
else if (cellNum == 6) {
|
etb.setReal_usr_id(vl);
|
}
|
// 实际完成日期
|
else if (cellNum == 7) {
|
etb.setReal_times(getDate2003(hssfCell));
|
}
|
// 检修注意事项
|
else if (cellNum == 8) {
|
etb.setNote(vl);
|
}
|
// 复查情况
|
else if (cellNum == 9) {
|
etb.setReview(vl);
|
}
|
//后期跟进 （ 可能没有）
|
else if(cellNum==10){
|
etb.setFollowup(vl);
|
}
|
//备注（可能没有）
|
else if(cellNum==11){
|
etb.setRemark(vl);
|
}*/
|
}
|
listAll.add(etb);
|
}
|
}
|
return listAll;
|
}
|
|
/**
|
* 设备检修excel导入xls2007
|
*
|
* @param url
|
* @return
|
* @throws FileNotFoundException
|
*/
|
public List<EqmMaintainBean> readXlsx2007_3(String url) throws Exception {
|
// 设备name和id综合
|
Map<String, String> eqpMap = SysEqpTypeBase.getAllEqpNameAndID();
|
List<EqmMaintainBean> listAll = new ArrayList<EqmMaintainBean>();
|
FileInputStream is = new FileInputStream(new File(url));
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet - 2007excel
|
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
|
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
|
if (xssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
|
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
|
if (xssfRow == null) {
|
continue;
|
}
|
EqmMaintainBean etb = new EqmMaintainBean();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
|
XSSFCell xssfCell = xssfRow.getCell(cellNum);
|
if (xssfCell == null) {
|
continue;
|
}
|
String vl = getValue2007(xssfCell);
|
// 设备名 需要转化成设备id
|
/*if (cellNum == 0) {
|
if (vl == "" || vl.equals("null")) {
|
return listAll;
|
}
|
etb.setEqp_id(eqpMap.get(vl));
|
}
|
// 部位
|
else if (cellNum == 1) {
|
etb.setPlace(vl);
|
}
|
// 检修内容
|
else if (cellNum == 2) {
|
etb.setContents(vl);
|
}
|
// 计划执行日期
|
else if (cellNum == 3) {
|
etb.setDate_plans(getDate2007(xssfCell));
|
}
|
// 计划负责人 需要转化成id
|
else if (cellNum == 4) {
|
etb.setBlame_usr_id(vl);
|
}
|
// 解决措施
|
else if (cellNum == 5) {
|
etb.setSolution(vl);
|
}
|
// 实际完成人 转id
|
else if (cellNum == 6) {
|
etb.setReal_usr_id(vl);
|
}
|
// 实际完成日期
|
else if (cellNum == 7) {
|
etb.setReal_times(getDate2007(xssfCell));
|
}
|
// 检修注意事项
|
else if (cellNum == 8) {
|
etb.setNote(vl);
|
}
|
// 复查情况
|
else if (cellNum == 9) {
|
etb.setReview(vl);
|
}
|
//后期跟进
|
else if(cellNum==10){
|
etb.setFollowup(vl);
|
}
|
//备注
|
else if(cellNum==11){
|
etb.setRemark(vl);
|
}*/
|
}
|
listAll.add(etb);
|
}
|
}
|
return listAll;
|
}
|
|
public String getDate2003(HSSFCell hssfCell) throws Exception{
|
String vl = getValue2003(hssfCell);
|
if(vl.trim().equals("")||vl==null){
|
return "";
|
}else{
|
try {
|
Date d = hssfCell.getDateCellValue();
|
if(d==null){
|
return "";
|
}
|
return new SimpleDateFormat("yyyy-MM-dd").format(d);
|
} catch (Exception e) {
|
return new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat("yyyy/MM/dd").parse(vl));
|
}
|
}
|
}
|
|
public String getDate2007(XSSFCell xssfCell) throws Exception {
|
|
String vl = getValue2007(xssfCell);
|
if(vl.trim().equals("")||vl==null){
|
return "";
|
}else{
|
try {
|
Date d = xssfCell.getDateCellValue();
|
if(d==null){
|
return "";
|
}
|
return new SimpleDateFormat("yyyy-MM-dd").format(d);
|
} catch (Exception e) {
|
return new SimpleDateFormat("yyyy-MM-dd").format(new SimpleDateFormat("yyyy/MM/dd").parse(vl));
|
}
|
}
|
|
}
|
|
/**
|
* 设备主数据导入excel
|
*
|
* @param url
|
* @return
|
* @throws Exception
|
*/
|
public List<MdEquipment> readXls2003_4(String url) throws Exception {
|
List<MdEquipment> listAll = new ArrayList<MdEquipment>();
|
// 设备型号
|
Map<String, String> eqpmap = SysEqpTypeBase.getEqpTypeNameAndID();
|
// 车间
|
Map<String, String> wsmap = SysEqpTypeBase.getworkshopNameAndID();
|
// 启用状态
|
Map<String, String> eqpSta = SysEqpTypeBase.eqpStat();
|
// 固入状态
|
Map<String, String> eqpIpS = SysEqpTypeBase.eqpPutStat();
|
InputStream is = new FileInputStream(url);
|
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet
|
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
|
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
|
if (hssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
|
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
|
if (hssfRow == null) {
|
continue;
|
}
|
MdEquipment etb = new MdEquipment();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
|
HSSFCell hssfCell = hssfRow.getCell((short) cellNum);
|
if (hssfCell == null) {
|
continue;
|
}
|
String vl = getValue2003(hssfCell).trim();
|
// 设备编号
|
if (cellNum == 0) {
|
if (vl == "" || vl.equals("null")) {
|
return listAll;
|
}
|
|
try {
|
etb.setEquipmentCode(vl.substring(0,
|
vl.indexOf('.')));
|
} catch (Exception e) {
|
etb.setEquipmentCode(vl);
|
}
|
}
|
// 设备名
|
else if (cellNum == 1) {
|
etb.setEquipmentName(vl);
|
}
|
// 设备描述
|
else if (cellNum == 2) {
|
etb.setEquipmentDesc(vl);
|
}
|
// 机型
|
else if (cellNum == 3) {
|
if (StringUtil.notNull(vl)) {
|
MdEqpType mt = new MdEqpType(eqpmap.get(vl));
|
etb.setMdEqpType(mt);
|
}
|
}
|
// 车间
|
else if (cellNum == 4) {
|
if (StringUtil.notNull(vl)) {
|
MdWorkshop ms = new MdWorkshop();
|
ms.setId(wsmap.get(vl));
|
etb.setMdWorkshop(ms);
|
}
|
}
|
// 工序段
|
else if (cellNum == 5) {
|
etb.setWorkCenter(vl);
|
}
|
// 设备位置
|
else if (cellNum == 6) {
|
etb.setEquipmentPosition(vl);
|
}
|
// 额定车速
|
else if (cellNum == 7) {
|
etb.setRatedSpeed(Double.parseDouble(vl));
|
}
|
// 额定车速单位
|
else if (cellNum == 8) {
|
etb.setRateSpeedUnit(vl);
|
}
|
// 台时产量
|
else if (cellNum == 9) {
|
etb.setYieId(Double.parseDouble(vl));
|
}
|
// 台时产量单位
|
else if (cellNum == 10) {
|
etb.setYieldUnit(vl);
|
}
|
// 是否启用
|
else if (cellNum == 11) {
|
etb.setEnabled(eqpSta.get(vl));
|
}
|
// 固定资产编号
|
else if (cellNum == 12) {
|
etb.setFixedAssetNum(vl);
|
}
|
// 出厂编号
|
else if (cellNum == 13) {
|
etb.setManufacturingNum(vl);
|
}
|
// 批准文号
|
else if (cellNum == 14) {
|
etb.setApprovalNum(vl);
|
}
|
// 准运证编号
|
else if (cellNum == 15) {
|
etb.setNavicertNum(vl);
|
}
|
// 已固入
|
else if (cellNum == 16) {
|
etb.setFixedAssetFlag(eqpIpS.get(vl));
|
}
|
// 使用部门
|
else if (cellNum == 17) {
|
etb.setUsingDepartment(vl);
|
}
|
|
}
|
listAll.add(etb);
|
}
|
}
|
|
return listAll;
|
}
|
|
public List<MdEquipment> readXlsx2007_4(String url) throws Exception {
|
|
// 设备型号
|
Map<String, String> eqpmap = SysEqpTypeBase.getEqpTypeNameAndID();
|
// 车间
|
Map<String, String> wsmap = SysEqpTypeBase.getworkshopNameAndID();
|
// 启用状态
|
Map<String, String> eqpSta = SysEqpTypeBase.eqpStat();
|
// 固入状态
|
Map<String, String> eqpIpS = SysEqpTypeBase.eqpPutStat();
|
List<MdEquipment> listAll = new ArrayList<MdEquipment>();
|
// String fileName = url;
|
// XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
|
FileInputStream is = new FileInputStream(new File(url));
|
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
|
is.close();
|
// 循环工作表Sheet - 2007excel
|
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
|
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
|
if (xssfSheet == null) {
|
continue;
|
}
|
// 循环行Row
|
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
|
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
|
if (xssfRow == null) {
|
continue;
|
}
|
MdEquipment etb = new MdEquipment();
|
// 循环列Cell
|
for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
|
XSSFCell xssfCell = xssfRow.getCell(cellNum);
|
if (xssfCell == null) {
|
continue;
|
}
|
String vl = getValue2007(xssfCell);
|
// 设备名 需要转化成设备id
|
// 设备编号
|
if (cellNum == 0) {
|
if (vl == "" || vl.equals("null")) {
|
return listAll;
|
}
|
try {
|
etb.setEquipmentCode(vl.substring(0,
|
vl.indexOf('.')));
|
} catch (Exception e) {
|
etb.setEquipmentCode(vl);
|
}
|
}
|
// 设备名
|
else if (cellNum == 1) {
|
etb.setEquipmentName(vl);
|
}
|
// 设备描述
|
else if (cellNum == 2) {
|
etb.setEquipmentDesc(vl);
|
}
|
// 机型
|
else if (cellNum == 3) {
|
if (StringUtil.notNull(vl)) {
|
MdEqpType mt = new MdEqpType(eqpmap.get(vl));
|
etb.setMdEqpType(mt);
|
}
|
}
|
// 车间
|
else if (cellNum == 4) {
|
if (StringUtil.notNull(vl)) {
|
MdWorkshop ms = new MdWorkshop();
|
ms.setId(wsmap.get(vl));
|
etb.setMdWorkshop(ms);
|
}
|
}
|
// 工序段
|
else if (cellNum == 5) {
|
etb.setWorkCenter(vl);
|
}
|
// 设备位置
|
else if (cellNum == 6) {
|
etb.setEquipmentPosition(vl);
|
}
|
// 额定车速
|
else if (cellNum == 7) {
|
etb.setRatedSpeed(Double.parseDouble(vl));
|
}
|
// 额定车速单位
|
else if (cellNum == 8) {
|
etb.setRateSpeedUnit(vl);
|
}
|
// 台时产量
|
else if (cellNum == 9) {
|
etb.setYieId(Double.parseDouble(vl));
|
}
|
// 台时产量单位
|
else if (cellNum == 10) {
|
etb.setYieldUnit(vl);
|
}
|
// 是否启用
|
else if (cellNum == 11) {
|
etb.setEnabled(eqpSta.get(vl));
|
}
|
// 固定资产编号
|
else if (cellNum == 12) {
|
etb.setFixedAssetNum(vl);
|
}
|
// 出厂编号
|
else if (cellNum == 13) {
|
etb.setManufacturingNum(vl);
|
}
|
// 批准文号
|
else if (cellNum == 14) {
|
etb.setApprovalNum(vl);
|
}
|
// 准运证编号
|
else if (cellNum == 15) {
|
etb.setNavicertNum(vl);
|
}
|
// 已固入
|
else if (cellNum == 16) {
|
etb.setFixedAssetFlag(eqpIpS.get(vl));
|
}
|
// 使用部门
|
else if (cellNum == 17) {
|
etb.setUsingDepartment(vl);
|
}
|
}
|
listAll.add(etb);
|
}
|
}
|
return listAll;
|
|
}
|
}
|
