package com.goafanti.common.utils; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Set; import java.util.TreeSet; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.goafanti.customer.bo.CustomerExcelBo; public class ExcelUtils { private Set repeatRows = new TreeSet(); //数据重复的 private Set vacantRows = new TreeSet(); //数据不完整的 DecimalFormat df = new DecimalFormat("0"); @SuppressWarnings({ "unused", "resource" }) public List parseExcel(String excelPath) throws IOException{ InputStream excelFile = new FileInputStream(new File(excelPath)); XSSFWorkbook wb = new XSSFWorkbook(excelFile); List result = new ArrayList(); XSSFSheet sheet = wb.getSheetAt(0); Iterator rowIterator = sheet.iterator(); Row currentRow = rowIterator.next(); //表头 int rowNumber = 1; while(rowIterator.hasNext()){ rowNumber++; System.out.println(rowNumber); currentRow = rowIterator.next(); Iterator cellIterator = currentRow.iterator(); CustomerExcelBo cin = new CustomerExcelBo(); result.add(cin); } return result; } @SuppressWarnings("unused") private String checkCustomerType(String customerType,int rowNumber){ if(StringUtils.isEmpty(customerType)) { vacantRows.add(rowNumber); return ""; } if(customerType.equals("个人客户")) return "0"; if(customerType.equals("公司客户")) return "1"; if(customerType.equals("团体客户")) return "2"; vacantRows.add(rowNumber); return ""; } @SuppressWarnings("unused") private String checkFollowSituation(String followSituation,int rowNumber){ if(StringUtils.isEmpty(followSituation)){ vacantRows.add(rowNumber); return ""; } if(followSituation.equals("已发项目介绍资料")) return "0"; if(followSituation.equals("已约面谈")) return "1"; if(followSituation.equals("已发合同计划书")) return "2"; if(followSituation.equals("已报价")) return "3"; if(followSituation.equals("已发合同")) return "4"; if(followSituation.equals("已签合同")) return "5"; if(followSituation.equals("面谈中")) return "6"; if(followSituation.equals("已面签")) return "7"; if(followSituation.equals("无进度")) return "8"; vacantRows.add(rowNumber); return ""; } @SuppressWarnings("unused") private String checkCustomerStatus(String customerStatus,int rowNumber){ if(StringUtils.isEmpty(customerStatus)) { vacantRows.add(rowNumber); return ""; } if(customerStatus.equals("新客户")) return "0"; if(customerStatus.equals("意向客户")) return "1"; if(customerStatus.equals("重点客户")) return "2"; if(customerStatus.equals("面谈客户")) return "3"; if(customerStatus.equals("签单客户")) return "4"; if(customerStatus.equals("被拒绝客户")) return "5"; vacantRows.add(rowNumber); return ""; } public boolean checkRepeat(CustomerExcelBo cin,List cinList){ boolean flag = false; return flag; } @SuppressWarnings("unused") private String checkCompanyIntention(String companyIntention,int rowNumber){ if(StringUtils.isEmpty(companyIntention)){ vacantRows.add(rowNumber); return ""; } if(companyIntention.equals("发明专利")) return "0"; if(companyIntention.equals("实用型新型专利")) return "1"; if(companyIntention.equals("外观专利")) return "2"; if(companyIntention.equals("软件著作权")) return "3"; if(companyIntention.equals("知识产权贯标")) return "4"; if(companyIntention.equals("高企认定")) return "5"; if(companyIntention.equals("技术成果")) return "6"; if(companyIntention.equals("技术需求")) return "7"; if(companyIntention.equals("专家咨询")) return "8"; if(companyIntention.equals("团单合作")) return "9"; if(companyIntention.equals("商标")) return "10"; if(companyIntention.equals("系统集成")) return "11"; vacantRows.add(rowNumber); return ""; } @SuppressWarnings("unused") private String checkOther(String cellValue,int rowNumber){ if(StringUtils.isEmpty(cellValue)){ vacantRows.add(rowNumber); return ""; }else{ return String.valueOf(cellValue); } } public String getRepeatRows(){ if(repeatRows.size()>0) return StringUtils.join(repeatRows.toArray(),",")+ " 行数据重复;"; else return ""; } public String getVacantRows(){ if(vacantRows.size()>0) return StringUtils.join(vacantRows.toArray(),",")+" 行数据不全或格式有误;"; else return ""; } }