ExcelUtils.java 4.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143
  1. package com.goafanti.common.utils;
  2. import java.io.File;
  3. import java.io.FileInputStream;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.text.DecimalFormat;
  7. import java.util.ArrayList;
  8. import java.util.Iterator;
  9. import java.util.List;
  10. import java.util.Set;
  11. import java.util.TreeSet;
  12. import org.apache.poi.ss.usermodel.Cell;
  13. import org.apache.poi.ss.usermodel.Row;
  14. import org.apache.poi.xssf.usermodel.XSSFSheet;
  15. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  16. import com.goafanti.customer.bo.CustomerExcelBo;
  17. public class ExcelUtils {
  18. private Set<Integer> repeatRows = new TreeSet<Integer>(); //数据重复的
  19. private Set<Integer> vacantRows = new TreeSet<Integer>(); //数据不完整的
  20. DecimalFormat df = new DecimalFormat("0");
  21. @SuppressWarnings({ "unused", "resource" })
  22. public List<CustomerExcelBo> parseExcel(String excelPath) throws IOException{
  23. InputStream excelFile = new FileInputStream(new File(excelPath));
  24. XSSFWorkbook wb = new XSSFWorkbook(excelFile);
  25. List<CustomerExcelBo> result = new ArrayList<CustomerExcelBo>();
  26. XSSFSheet sheet = wb.getSheetAt(0);
  27. Iterator<Row> rowIterator = sheet.iterator();
  28. Row currentRow = rowIterator.next(); //表头
  29. int rowNumber = 1;
  30. while(rowIterator.hasNext()){
  31. rowNumber++;
  32. System.out.println(rowNumber);
  33. currentRow = rowIterator.next();
  34. Iterator<Cell> cellIterator = currentRow.iterator();
  35. CustomerExcelBo cin = new CustomerExcelBo();
  36. result.add(cin);
  37. }
  38. return result;
  39. }
  40. @SuppressWarnings("unused")
  41. private String checkCustomerType(String customerType,int rowNumber){
  42. if(StringUtils.isEmpty(customerType)) {
  43. vacantRows.add(rowNumber);
  44. return "";
  45. }
  46. if(customerType.equals("个人客户")) return "0";
  47. if(customerType.equals("公司客户")) return "1";
  48. if(customerType.equals("团体客户")) return "2";
  49. vacantRows.add(rowNumber);
  50. return "";
  51. }
  52. @SuppressWarnings("unused")
  53. private String checkFollowSituation(String followSituation,int rowNumber){
  54. if(StringUtils.isEmpty(followSituation)){
  55. vacantRows.add(rowNumber);
  56. return "";
  57. }
  58. if(followSituation.equals("已发项目介绍资料")) return "0";
  59. if(followSituation.equals("已约面谈")) return "1";
  60. if(followSituation.equals("已发合同计划书")) return "2";
  61. if(followSituation.equals("已报价")) return "3";
  62. if(followSituation.equals("已发合同")) return "4";
  63. if(followSituation.equals("已签合同")) return "5";
  64. if(followSituation.equals("面谈中")) return "6";
  65. if(followSituation.equals("已面签")) return "7";
  66. if(followSituation.equals("无进度")) return "8";
  67. vacantRows.add(rowNumber);
  68. return "";
  69. }
  70. @SuppressWarnings("unused")
  71. private String checkCustomerStatus(String customerStatus,int rowNumber){
  72. if(StringUtils.isEmpty(customerStatus)) {
  73. vacantRows.add(rowNumber);
  74. return "";
  75. }
  76. if(customerStatus.equals("新客户")) return "0";
  77. if(customerStatus.equals("意向客户")) return "1";
  78. if(customerStatus.equals("重点客户")) return "2";
  79. if(customerStatus.equals("面谈客户")) return "3";
  80. if(customerStatus.equals("签单客户")) return "4";
  81. if(customerStatus.equals("被拒绝客户")) return "5";
  82. vacantRows.add(rowNumber);
  83. return "";
  84. }
  85. public boolean checkRepeat(CustomerExcelBo cin,List<CustomerExcelBo> cinList){
  86. boolean flag = false;
  87. return flag;
  88. }
  89. @SuppressWarnings("unused")
  90. private String checkCompanyIntention(String companyIntention,int rowNumber){
  91. if(StringUtils.isEmpty(companyIntention)){
  92. vacantRows.add(rowNumber);
  93. return "";
  94. }
  95. if(companyIntention.equals("发明专利")) return "0";
  96. if(companyIntention.equals("实用型新型专利")) return "1";
  97. if(companyIntention.equals("外观专利")) return "2";
  98. if(companyIntention.equals("软件著作权")) return "3";
  99. if(companyIntention.equals("知识产权贯标")) return "4";
  100. if(companyIntention.equals("高企认定")) return "5";
  101. if(companyIntention.equals("技术成果")) return "6";
  102. if(companyIntention.equals("技术需求")) return "7";
  103. if(companyIntention.equals("专家咨询")) return "8";
  104. if(companyIntention.equals("团单合作")) return "9";
  105. if(companyIntention.equals("商标")) return "10";
  106. if(companyIntention.equals("系统集成")) return "11";
  107. vacantRows.add(rowNumber);
  108. return "";
  109. }
  110. @SuppressWarnings("unused")
  111. private String checkOther(String cellValue,int rowNumber){
  112. if(StringUtils.isEmpty(cellValue)){
  113. vacantRows.add(rowNumber);
  114. return "";
  115. }else{
  116. return String.valueOf(cellValue);
  117. }
  118. }
  119. public String getRepeatRows(){
  120. if(repeatRows.size()>0)
  121. return StringUtils.join(repeatRows.toArray(),",")+ " 行数据重复;";
  122. else
  123. return "";
  124. }
  125. public String getVacantRows(){
  126. if(vacantRows.size()>0)
  127. return StringUtils.join(vacantRows.toArray(),",")+" 行数据不全或格式有误;";
  128. else
  129. return "";
  130. }
  131. }