public String bookExcelUpload(@Param("filePath") String filePath,@Param("dateMonth") String dateMonth)throws IOException{
String bf="";//错误信息提醒
if (isEmpty(filePath)) {
throw new IllegalArgumentException("参数错误");
}
if(dateMonth==null||dateMonth.length()==0){
throw new IllegalArgumentException("请选择上传的月份");
}
//获取文件的完整路径
filePath = FileUtils.getInstance().getFilePath(FileUtils.getInstance().getBasePath(), filePath);
File file = new File(filePath);
if (!file.exists()) {
//异常处理
throw new IllegalArgumentException("要解析的excel不存在");
}
//查询人员信息列表
//List<Map<String,Object>> userList=sysCommonQueryService.queryUserAndAgency();
boolean flag=false;
try {
//获取文件流
List<BookTicketBean> list = new ArrayList<BookTicketBean>();
Map<String, List<BookTicketBean>> map = new HashMap<String, List<BookTicketBean>>();
try {
Workbook wb = Workbook.getWorkbook(file);
Sheet st = wb.getSheet(0);
int clos = st.getColumns();//得到所有的列
int rows = st.getRows();//得到所有的行
System.out.println(clos + "rows:" + rows);
for(int i = 1; i < rows; i++){
for(int j = 1; j< clos; j++){
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String billDept=st.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
String deptId = st.getCell(j++, i).getContents();
String name=st.getCell(j++, i).getContents();
Cell cell=st.getCell(j++, i);
String bookTicketDate1 = null;
if(cell.getType() == CellType.DATE){
DateCell dc = (DateCell)cell;
Date bookTicketDate = dc.getDate(); //获取单元格的date类型
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar c=Calendar.getInstance();
c.setTime(bookTicketDate);
c.add(Calendar.HOUR, -8);
bookTicketDate1 = sdf.format(c.getTime());
}
String passageName=st.getCell(j++, i).getContents();
Cell cell1=st.getCell(j++, i);
String takeOffDate1 = null;
if(cell1.getType() == CellType.DATE){
DateCell dc = (DateCell)cell1;
Date takeOffDate = dc.getDate(); //获取单元格的date类型
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar c=Calendar.getInstance();
c.setTime(takeOffDate);
c.add(Calendar.HOUR, -8);//获取的数据需要转换时区问题,北京时间在东8区,所以-8
takeOffDate1 = sdf.format(c.getTime());
}
String flight=st.getCell(j++, i).getContents();
String airLine=st.getCell(j++, i).getContents();
String nominalPrice=st.getCell(j++, i).getContents();
String taxation=st.getCell(j++, i).getContents();
String insurance=st.getCell(j++, i).getContents();
String cancellFee=st.getCell(j++, i).getContents();
String receivable=st.getCell(j++, i).getContents();
String status=st.getCell(j++, i).getContents();
String paymentMethod=st.getCell(j++, i).getContents();
String ticketId=st.getCell(j++, i).getContents();
String identifying=st.getCell(j++, i).getContents();
String itemNumber=st.getCell(j++, i).getContents();
String itemName=st.getCell(j++, i).getContents();
String projectManager=st.getCell(j++, i).getContents();
String comment=st.getCell(j++, i).getContents();
BookTicketBean bookTicket = new BookTicketBean();
bookTicket.setBookTicketId(UUIDHexGenerator.getInstance().generate());
bookTicket.setDeptId(deptId);
bookTicket.setBillDept(billDept);
bookTicket.setName(name);
if(bookTicketDate1 != null &&!bookTicketDate1.equals("")){
bookTicket.setBookTicketDate(bookTicketDate1);
}else{
bf="页中: 订票时间不能为空";
break;
}
bookTicket.setPassageName(passageName);
if(passageName != null && !"".equals(passageName)){
bookTicket.setPassageName(passageName);
}else{
bf="页中: 乘机人不能为空";
break;
}
if(takeOffDate1 != null &&!takeOffDate1.equals("")){
bookTicket.setTakeOffDate(takeOffDate1);
}else{
bf="页中: 起飞时间不能为空";
break;
}
Date bookTicketDate2 = formatter.parse(bookTicketDate1);
Date takeOffDate2 = formatter.parse(takeOffDate1);
if(takeOffDate2.compareTo(bookTicketDate2) < 0){
bf="页中: 起飞时间必须在订票时间之后,请核对!";
break;
}
if(billDept == null||billDept.length() == 0){
//默认为:中国联合网络通信有限公司研究院
bf="页中: 票据归属部门/公司不能为空";
break;
}
if(deptId == null||deptId.length() == 0){
bf="页中: 部门(公司)不能为空";
break;
}
if(name == null||name.length() == 0){
bf="页中: 姓名不能为空";
break;
}
if(passageName == null || passageName.length()==0){
bf="页中: 乘机人不能为空";
break;
}
if(flight == null ||flight.length()==0){
bf="页中: 航班不能为空";
break;
}else if(flight.length()>200){
bf="页中: 航班长度不能大于200";
break;
}
if(airLine == null||airLine.length() == 0){
bf="页中: 航线不能为空";
break;
}else if(airLine.length()>200){
bf="页中: 航线长度不能大于200";
break;
}
if(nominalPrice == null){
bf="页中: 票面价不能为空或非数字";
break;
}else if(new BigDecimal(nominalPrice).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 票面价不能大于9位";
break;
}
if(taxation == null){
bf="页中: 税费不能为空或非数字";
break;
}else if(new BigDecimal(taxation).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 税费不能大于9位";
break;
}
if(insurance==null){
bf="页中: 保险不能为空或非数字";
break;
}else if(new BigDecimal(insurance).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 保险不能大于9位";
break;
}
if(cancellFee == null){
bf="页中: 退票费不能为空或非数字";
break;
}else if(new BigDecimal(cancellFee).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 退票费不能大于9位";
break;
}
if(receivable == null){
bf="页中: 应收不能为空或非数字";
break;
}else if(new BigDecimal(receivable).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 应收不能大于9位";
break;
}
if(status==null||status.length()==0){
bf="页中: 状态不能为空";
break;
}
if(paymentMethod==null||paymentMethod.length()==0){
bf="页中: 支付方式不能为空";
break;
}else if(paymentMethod.length()>255){
bf="页中: 支付方式长度不能大于255";
break;
}
if(itemNumber==null||itemNumber.length()==0){
bf="页中: 项目号不能为空";
break;
}
if(itemName==null||itemName.length()==0){
bf="页中: 项目名称不能为空";
break;
}
bookTicket.setFlight(flight);
bookTicket.setAirLine(airLine);
bookTicket.setNominalPrice(new BigDecimal(nominalPrice));
bookTicket.setTaxation(new BigDecimal(taxation));
bookTicket.setInsurance(new BigDecimal(insurance));
bookTicket.setCancellFee(new BigDecimal(cancellFee));
bookTicket.setReceivable(new BigDecimal(receivable));
if(status.equals("正常票") && !"".equals(status)){
bookTicket.setStatus(100);
}else if(status.equals("退票") && !"".equals(status)){
bookTicket.setStatus(200);
}
bookTicket.setPaymentMethod(paymentMethod);
bookTicket.setTicketId(ticketId);
bookTicket.setIdentifying(identifying);
bookTicket.setItemNumber(itemNumber);
bookTicket.setItemName(itemName);
bookTicket.setProjectManager(projectManager);
bookTicket.setComment(comment);
bookTicket.setCreateUser(getUserInfo().getUserRealCode());//创建人
bookTicket.setUpdateUser(getUserInfo().getUserRealCode());//更新人
bookTicket.setIsDel("0");
bookTicket.setExt2(dateMonth);
list.add(bookTicket);
}
}
map.put("bookTicketBean", list);
} catch (Exception e) {
e.printStackTrace();
}
//读取完毕,进行业务处理
if(bf.length()==0){
bookTicketService.insertBookInfoMap(map,dateMonth);
setBookResult("文件解析成功");
}
} catch (ToftException e) {
e.printStackTrace();
setBookResult(e.getMessage());
}
catch (Exception e) {
e.printStackTrace();
setBookResult( "文件解析错误,请检查文件数据");
} finally {
if(bf.length()!=0){
setBookResult(bf);
}
}
return AJAX;
}
String bf="";//错误信息提醒
if (isEmpty(filePath)) {
throw new IllegalArgumentException("参数错误");
}
if(dateMonth==null||dateMonth.length()==0){
throw new IllegalArgumentException("请选择上传的月份");
}
//获取文件的完整路径
filePath = FileUtils.getInstance().getFilePath(FileUtils.getInstance().getBasePath(), filePath);
File file = new File(filePath);
if (!file.exists()) {
//异常处理
throw new IllegalArgumentException("要解析的excel不存在");
}
//查询人员信息列表
//List<Map<String,Object>> userList=sysCommonQueryService.queryUserAndAgency();
boolean flag=false;
try {
//获取文件流
List<BookTicketBean> list = new ArrayList<BookTicketBean>();
Map<String, List<BookTicketBean>> map = new HashMap<String, List<BookTicketBean>>();
try {
Workbook wb = Workbook.getWorkbook(file);
Sheet st = wb.getSheet(0);
int clos = st.getColumns();//得到所有的列
int rows = st.getRows();//得到所有的行
System.out.println(clos + "rows:" + rows);
for(int i = 1; i < rows; i++){
for(int j = 1; j< clos; j++){
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String billDept=st.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
String deptId = st.getCell(j++, i).getContents();
String name=st.getCell(j++, i).getContents();
Cell cell=st.getCell(j++, i);
String bookTicketDate1 = null;
if(cell.getType() == CellType.DATE){
DateCell dc = (DateCell)cell;
Date bookTicketDate = dc.getDate(); //获取单元格的date类型
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar c=Calendar.getInstance();
c.setTime(bookTicketDate);
c.add(Calendar.HOUR, -8);
bookTicketDate1 = sdf.format(c.getTime());
}
String passageName=st.getCell(j++, i).getContents();
Cell cell1=st.getCell(j++, i);
String takeOffDate1 = null;
if(cell1.getType() == CellType.DATE){
DateCell dc = (DateCell)cell1;
Date takeOffDate = dc.getDate(); //获取单元格的date类型
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Calendar c=Calendar.getInstance();
c.setTime(takeOffDate);
c.add(Calendar.HOUR, -8);//获取的数据需要转换时区问题,北京时间在东8区,所以-8
takeOffDate1 = sdf.format(c.getTime());
}
String flight=st.getCell(j++, i).getContents();
String airLine=st.getCell(j++, i).getContents();
String nominalPrice=st.getCell(j++, i).getContents();
String taxation=st.getCell(j++, i).getContents();
String insurance=st.getCell(j++, i).getContents();
String cancellFee=st.getCell(j++, i).getContents();
String receivable=st.getCell(j++, i).getContents();
String status=st.getCell(j++, i).getContents();
String paymentMethod=st.getCell(j++, i).getContents();
String ticketId=st.getCell(j++, i).getContents();
String identifying=st.getCell(j++, i).getContents();
String itemNumber=st.getCell(j++, i).getContents();
String itemName=st.getCell(j++, i).getContents();
String projectManager=st.getCell(j++, i).getContents();
String comment=st.getCell(j++, i).getContents();
BookTicketBean bookTicket = new BookTicketBean();
bookTicket.setBookTicketId(UUIDHexGenerator.getInstance().generate());
bookTicket.setDeptId(deptId);
bookTicket.setBillDept(billDept);
bookTicket.setName(name);
if(bookTicketDate1 != null &&!bookTicketDate1.equals("")){
bookTicket.setBookTicketDate(bookTicketDate1);
}else{
bf="页中: 订票时间不能为空";
break;
}
bookTicket.setPassageName(passageName);
if(passageName != null && !"".equals(passageName)){
bookTicket.setPassageName(passageName);
}else{
bf="页中: 乘机人不能为空";
break;
}
if(takeOffDate1 != null &&!takeOffDate1.equals("")){
bookTicket.setTakeOffDate(takeOffDate1);
}else{
bf="页中: 起飞时间不能为空";
break;
}
Date bookTicketDate2 = formatter.parse(bookTicketDate1);
Date takeOffDate2 = formatter.parse(takeOffDate1);
if(takeOffDate2.compareTo(bookTicketDate2) < 0){
bf="页中: 起飞时间必须在订票时间之后,请核对!";
break;
}
if(billDept == null||billDept.length() == 0){
//默认为:中国联合网络通信有限公司研究院
bf="页中: 票据归属部门/公司不能为空";
break;
}
if(deptId == null||deptId.length() == 0){
bf="页中: 部门(公司)不能为空";
break;
}
if(name == null||name.length() == 0){
bf="页中: 姓名不能为空";
break;
}
if(passageName == null || passageName.length()==0){
bf="页中: 乘机人不能为空";
break;
}
if(flight == null ||flight.length()==0){
bf="页中: 航班不能为空";
break;
}else if(flight.length()>200){
bf="页中: 航班长度不能大于200";
break;
}
if(airLine == null||airLine.length() == 0){
bf="页中: 航线不能为空";
break;
}else if(airLine.length()>200){
bf="页中: 航线长度不能大于200";
break;
}
if(nominalPrice == null){
bf="页中: 票面价不能为空或非数字";
break;
}else if(new BigDecimal(nominalPrice).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 票面价不能大于9位";
break;
}
if(taxation == null){
bf="页中: 税费不能为空或非数字";
break;
}else if(new BigDecimal(taxation).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 税费不能大于9位";
break;
}
if(insurance==null){
bf="页中: 保险不能为空或非数字";
break;
}else if(new BigDecimal(insurance).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 保险不能大于9位";
break;
}
if(cancellFee == null){
bf="页中: 退票费不能为空或非数字";
break;
}else if(new BigDecimal(cancellFee).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 退票费不能大于9位";
break;
}
if(receivable == null){
bf="页中: 应收不能为空或非数字";
break;
}else if(new BigDecimal(receivable).compareTo(new BigDecimal(1000000000))>=0){
bf="页中: 应收不能大于9位";
break;
}
if(status==null||status.length()==0){
bf="页中: 状态不能为空";
break;
}
if(paymentMethod==null||paymentMethod.length()==0){
bf="页中: 支付方式不能为空";
break;
}else if(paymentMethod.length()>255){
bf="页中: 支付方式长度不能大于255";
break;
}
if(itemNumber==null||itemNumber.length()==0){
bf="页中: 项目号不能为空";
break;
}
if(itemName==null||itemName.length()==0){
bf="页中: 项目名称不能为空";
break;
}
bookTicket.setFlight(flight);
bookTicket.setAirLine(airLine);
bookTicket.setNominalPrice(new BigDecimal(nominalPrice));
bookTicket.setTaxation(new BigDecimal(taxation));
bookTicket.setInsurance(new BigDecimal(insurance));
bookTicket.setCancellFee(new BigDecimal(cancellFee));
bookTicket.setReceivable(new BigDecimal(receivable));
if(status.equals("正常票") && !"".equals(status)){
bookTicket.setStatus(100);
}else if(status.equals("退票") && !"".equals(status)){
bookTicket.setStatus(200);
}
bookTicket.setPaymentMethod(paymentMethod);
bookTicket.setTicketId(ticketId);
bookTicket.setIdentifying(identifying);
bookTicket.setItemNumber(itemNumber);
bookTicket.setItemName(itemName);
bookTicket.setProjectManager(projectManager);
bookTicket.setComment(comment);
bookTicket.setCreateUser(getUserInfo().getUserRealCode());//创建人
bookTicket.setUpdateUser(getUserInfo().getUserRealCode());//更新人
bookTicket.setIsDel("0");
bookTicket.setExt2(dateMonth);
list.add(bookTicket);
}
}
map.put("bookTicketBean", list);
} catch (Exception e) {
e.printStackTrace();
}
//读取完毕,进行业务处理
if(bf.length()==0){
bookTicketService.insertBookInfoMap(map,dateMonth);
setBookResult("文件解析成功");
}
} catch (ToftException e) {
e.printStackTrace();
setBookResult(e.getMessage());
}
catch (Exception e) {
e.printStackTrace();
setBookResult( "文件解析错误,请检查文件数据");
} finally {
if(bf.length()!=0){
setBookResult(bf);
}
}
return AJAX;
}