| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143 |
- 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<Integer> repeatRows = new TreeSet<Integer>(); //数据重复的
- private Set<Integer> vacantRows = new TreeSet<Integer>(); //数据不完整的
- DecimalFormat df = new DecimalFormat("0");
- @SuppressWarnings({ "unused", "resource" })
- public List<CustomerExcelBo> parseExcel(String excelPath) throws IOException{
- InputStream excelFile = new FileInputStream(new File(excelPath));
- XSSFWorkbook wb = new XSSFWorkbook(excelFile);
- List<CustomerExcelBo> result = new ArrayList<CustomerExcelBo>();
- XSSFSheet sheet = wb.getSheetAt(0);
- Iterator<Row> rowIterator = sheet.iterator();
- Row currentRow = rowIterator.next(); //表头
- int rowNumber = 1;
- while(rowIterator.hasNext()){
- rowNumber++;
- System.out.println(rowNumber);
- currentRow = rowIterator.next();
- Iterator<Cell> 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<CustomerExcelBo> 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 "";
- }
- }
|