/*excel上传*/
public void readExcel(String filePath, SysUser sysUser) throws Exception {
filePath=file_path+filePath;
InputStream is = new FileInputStream(new File(filePath));
Workbook hssfWorkbook = null;
if (filePath.endsWith("xlsx")) {
hssfWorkbook = new XSSFWorkbook(is);
//Excel 2007
} else if (filePath.endsWith("xls")) {
//Excel 2003
hssfWorkbook = new HSSFWorkbook(is);
}
Trademark trademark = null;
List<Trademark> list = new ArrayList<Trademark>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
//HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
//HSSFRow hssfRow = hssfSheet.getRow(rowNum);
Row hssfRow = hssfSheet.getRow(rowNum);
//if (hssfRow != null && null != hssfRow.getCell(1)) {
if (hssfRow != null){
trademark = new Trademark();
trademark.setTrademarkId(ID16NumberGenerator.get16NumberID());
try{
double userPhone = hssfRow.getCell(0).getNumericCellValue();
String newuserPhone = new DecimalFormat("#").format(userPhone);
CustomerUser customerUser = customerUserService.getByPhone(Long.parseLong(newuserPhone));
Cell trademarkName = hssfRow.getCell(1);
double trademarkRegisterNo = hssfRow.getCell(2).getNumericCellValue();
String newtrademarkRegisterNo = new DecimalFormat("#").format(trademarkRegisterNo);
Cell classfily = hssfRow.getCell(3);
Cell useRange = hssfRow.getCell(4);
String patentTermStart = hssfRow.getCell(5).toString();
Cell trademarkPrice = hssfRow.getCell(6);
Cell transferFee = hssfRow.getCell(7);
double shared = hssfRow.getCell(8).getNumericCellValue();
String newshared = new DecimalFormat("#").format(shared);
double trademarkType = hssfRow.getCell(9).getNumericCellValue();
String newtrademarkType = new DecimalFormat("#").format(trademarkType);
String[] str = classfily.toString().split(";");
if (str.length == 1) {
str = classfily.toString().trim().split("；");
}
List<TrademarkClassify> lists = new ArrayList();
for (int a = 0; a < str.length; a++) {
str[a]=str[a].trim();
if(str[a] !=null && !"".equals(str[a])){
TrademarkClassify trademarkClassify = new TrademarkClassify();
trademarkClassify.setTrademarkId(trademark.getTrademarkId());
trademarkClassify.setTrademarkClassify(str[a]);
lists.add(trademarkClassify);
}
}
trademarkClassifyService.addAllByList(lists);
//这里是自己的逻辑
if(trademarkName !=null && trademarkName.toString() !=""){
trademark.setTrademarkName(trademarkName.toString());
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行 ，第2列商标名称数据不正确，请检查");
}
if(newtrademarkRegisterNo !=null){
trademark.setTrademarkRegisterNo(Long.parseLong(newtrademarkRegisterNo));
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行 ，第3列注册号数据不正确，请检查");
}
if(customerUser !=null){
trademark.setUserId(customerUser.getUserId());
}else{
CustomerUser newCustomerUser=new CustomerUser();
newCustomerUser.setUserPhone(Long.parseLong(newuserPhone));
newCustomerUser.setUserPassword(newuserPhone);
newCustomerUser=customerUserService.add(newCustomerUser,sysUser);
trademark.setUserId(newCustomerUser.getUserId());
}
if(patentTermStart !=null && patentTermStart !=""){
//String[] strings=patentTermStart.split("至");
SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日");
Date newDate=sdf.parse(patentTermStart);
trademark.setPatentTermStart(newDate);
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行，第6行日期数据不正确，请检查");
}
trademark.setSysUserId(sysUser.getSysUserId());
trademark.setCreateTime(Calendar.getInstance().getTime());
trademark.setCreator(sysUser.getSysUserId());
if(newshared !=null){
trademark.setShared(Integer.parseInt(newshared));
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行，第9行共有商标数据不正确，请检查");
}
if(newtrademarkType !=null){
trademark.setTrademarkType(Integer.parseInt(newtrademarkType));
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行，第10行共有商标数据不正确，请检查");
}
trademark.setTrademarkState(TrademarkConstant.TRADEMARK_STATE_IN_STOCK);
if(transferFee !=null){
trademark.setTransferFee(new BigDecimal(transferFee.getNumericCellValue()));
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行，第8行转让费数据不正确，请检查");
}
if(trademarkPrice !=null){
trademark.setTrademarkPrice(new BigDecimal(trademarkPrice.getNumericCellValue()));
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行，第7行商标价格数据类型不正确，请检查");
}
if(useRange !=null){
trademark.setUseRange(useRange.getStringCellValue());
}else{
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行，第7行商标价格数据类型不正确，请检查");
}
trademarkService.add(trademark);
list.add(trademark);
}catch(Exception e){
throw new BaseException(ExceptionCode.FAILED,"第"+rowNum+"行有数据类型不正确");
}
}
}
}
}