ExcelUtils.java 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813
  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.math.BigDecimal;
  7. import java.text.DecimalFormat;
  8. import java.text.SimpleDateFormat;
  9. import java.util.Date;
  10. import java.util.HashSet;
  11. import java.util.Iterator;
  12. import java.util.List;
  13. import java.util.Set;
  14. import java.util.regex.Pattern;
  15. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  16. import org.apache.poi.xssf.usermodel.XSSFSheet;
  17. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  18. import org.springframework.web.multipart.MultipartFile;
  19. import com.goafanti.common.constant.AFTConstants;
  20. import com.goafanti.customer.bo.CustomerExcelBo;
  21. import com.goafanti.order.bo.OrderReportBo;
  22. import org.apache.commons.lang3.StringUtils;
  23. import org.apache.poi.hssf.usermodel.*;
  24. import org.apache.poi.hssf.util.HSSFColor;
  25. import org.apache.poi.ss.formula.functions.T;
  26. import org.apache.poi.ss.usermodel.*;
  27. import org.apache.poi.xssf.usermodel.*;
  28. import javax.servlet.http.HttpServletResponse;
  29. import java.awt.Color;
  30. import java.io.OutputStream;
  31. import java.lang.reflect.Field;
  32. import java.lang.reflect.InvocationTargetException;
  33. import java.lang.reflect.Method;
  34. import java.net.URLEncoder;
  35. import java.util.*;
  36. import java.util.regex.Matcher;
  37. public class ExcelUtils {
  38. private Set<Integer> vacantRows = new HashSet<Integer>(); //数据不完整的
  39. private final String DEFAULT_MOBILE = "0000-0000000";
  40. DecimalFormat df = new DecimalFormat("0");
  41. @SuppressWarnings ("resource")
  42. public Set<CustomerExcelBo> parseExcel(String excelPath) throws IOException{
  43. InputStream excelFile = new FileInputStream(new File(excelPath));
  44. XSSFWorkbook wb = new XSSFWorkbook(excelFile);
  45. Set<CustomerExcelBo> result = new HashSet<CustomerExcelBo>();
  46. XSSFSheet sheet = wb.getSheetAt(0);
  47. Iterator<Row> rowIterator = sheet.iterator();
  48. Row currentRow = sheet.getRow(1); //表头
  49. Cell configCell = currentRow.getCell(1); //配置
  50. int totalNumber = 2;
  51. if(configCell.getCellTypeEnum() == CellType.NUMERIC){
  52. totalNumber = Integer.valueOf(df.format(configCell.getNumericCellValue()));
  53. }else if(configCell.getCellTypeEnum() == CellType.STRING){
  54. totalNumber = Integer.valueOf(configCell.getStringCellValue());
  55. }
  56. int rowNumber = 2;
  57. rowIterator.next();
  58. rowIterator.next();
  59. while(rowIterator.hasNext() && rowNumber < totalNumber){
  60. rowNumber++;
  61. currentRow = rowIterator.next();
  62. Iterator<Cell> cellIterator = currentRow.iterator();
  63. CustomerExcelBo bo = new CustomerExcelBo();
  64. bo.setCustomerType(checkCustomerType(cellIterator.next().getStringCellValue(), rowNumber));
  65. bo.setIdentifyName(checkOther(cellIterator.next().getStringCellValue(),rowNumber,64));
  66. bo.setFollowSituation(checkFollowSituation(cellIterator.next().getStringCellValue(), rowNumber));
  67. bo.setCustomerStatus(checkCustomerStatus(cellIterator.next().getStringCellValue(), rowNumber));
  68. // bo.setBusinessGlossoryId(checkCustomerIntention(cellIterator.next().getStringCellValue(), rowNumber));
  69. bo.setContacts(checkOther(cellIterator.next().getStringCellValue(),rowNumber,32));
  70. Cell phoneCell = cellIterator.next();
  71. if(phoneCell.getCellTypeEnum() == CellType.NUMERIC) {
  72. bo.setMobile(checkMobile(df.format(phoneCell.getNumericCellValue()), rowNumber));
  73. }else if(phoneCell.getCellTypeEnum() == CellType.STRING){
  74. bo.setMobile(checkMobile(phoneCell.getStringCellValue(), rowNumber));
  75. }
  76. bo.setRowNumber(rowNumber);
  77. result.add(bo);
  78. }
  79. return result;
  80. }
  81. private String checkCustomerType(String customerType,int rowNumber){
  82. if(StringUtils.isEmpty(customerType)) {
  83. vacantRows.add(rowNumber);
  84. return "";
  85. }
  86. if(customerType.equals("个人客户")) return "0";
  87. if(customerType.equals("单位客户")) return "1";
  88. vacantRows.add(rowNumber);
  89. return "";
  90. }
  91. private String checkFollowSituation(String followSituation,int rowNumber){
  92. if(StringUtils.isEmpty(followSituation)){
  93. vacantRows.add(rowNumber);
  94. return "";
  95. }
  96. if(followSituation.equals("已发项目介绍资料")) return "0";
  97. if(followSituation.equals("已约面谈")) return "1";
  98. if(followSituation.equals("已发合同计划书")) return "2";
  99. if(followSituation.equals("已报价")) return "3";
  100. if(followSituation.equals("已发合同")) return "4";
  101. if(followSituation.equals("已签合同")) return "5";
  102. if(followSituation.equals("面谈中")) return "6";
  103. if(followSituation.equals("已面签")) return "7";
  104. if(followSituation.equals("无进度")) return "8";
  105. vacantRows.add(rowNumber);
  106. return "";
  107. }
  108. private String checkCustomerStatus(String customerStatus,int rowNumber){
  109. if(StringUtils.isEmpty(customerStatus)) {
  110. vacantRows.add(rowNumber);
  111. return "";
  112. }
  113. if(customerStatus.equals("新客户")) return "0";
  114. if(customerStatus.equals("意向客户")) return "1";
  115. if(customerStatus.equals("重点客户")) return "2";
  116. if(customerStatus.equals("面谈客户")) return "3";
  117. if(customerStatus.equals("签单客户")) return "4";
  118. if(customerStatus.equals("被拒绝客户")) return "5";
  119. if(customerStatus.equals("停止跟进")) return "6";
  120. vacantRows.add(rowNumber);
  121. return "";
  122. }
  123. public boolean checkRepeat(CustomerExcelBo cin,List<CustomerExcelBo> cinList){
  124. boolean flag = false;
  125. return flag;
  126. }
  127. /*private String checkCustomerIntention(String companyIntention,int rowNumber){
  128. if(StringUtils.isEmpty(companyIntention)){
  129. vacantRows.add(rowNumber);
  130. return "";
  131. }
  132. if(companyIntention.equals("发明专利")) return "0";
  133. if(companyIntention.equals("实用型新型专利")) return "1";
  134. if(companyIntention.equals("外观专利")) return "2";
  135. if(companyIntention.equals("软件著作权")) return "3";
  136. if(companyIntention.equals("知识产权贯标")) return "4";
  137. if(companyIntention.equals("高企认定")) return "5";
  138. if(companyIntention.equals("技术成果")) return "6";
  139. if(companyIntention.equals("技术需求")) return "7";
  140. if(companyIntention.equals("专家咨询")) return "8";
  141. if(companyIntention.equals("团单合作")) return "9";
  142. if(companyIntention.equals("商标")) return "10";
  143. if(companyIntention.equals("系统集成")) return "11";
  144. vacantRows.add(rowNumber);
  145. return "";
  146. }*/
  147. private String checkOther(String cellValue,int rowNumber,int maxLength){
  148. if(StringUtils.isBlank(cellValue) || cellValue.length() > maxLength){
  149. vacantRows.add(rowNumber);
  150. return "";
  151. }else{
  152. return String.valueOf(cellValue).trim();
  153. }
  154. }
  155. public String checkMobile(String mobile, int rowNumber){
  156. mobile = mobile.trim();
  157. String mobileRegex = "^1[3|4|5|7|8][0-9]{9}$";
  158. String telRegex = "^\\d{3,4}-\\d{7,8}$";
  159. if(StringUtils.isBlank(mobile)) return "";
  160. if(DEFAULT_MOBILE.equals(mobile)) return mobile;
  161. if(!Pattern.matches(mobileRegex, mobile) && !Pattern.matches(telRegex, mobile)){
  162. vacantRows.add(rowNumber);
  163. return "";
  164. }
  165. return mobile;
  166. }
  167. public String getVacantRows(){
  168. if(vacantRows.size()>0)
  169. return StringUtils.join(vacantRows.toArray(),",")+" 行数据不全或格式有误;";
  170. else
  171. return "";
  172. }
  173. @SuppressWarnings("resource")
  174. public Set<OrderReportBo> parseOrderExcel(String excelPath) throws IOException{
  175. InputStream excelFile = new FileInputStream(new File(excelPath));
  176. XSSFWorkbook wb = new XSSFWorkbook(excelFile);
  177. Set<OrderReportBo> result = new HashSet<OrderReportBo>();
  178. XSSFSheet sheet = wb.getSheetAt(0);
  179. Iterator<Row> rowIterator = sheet.iterator();
  180. Row currentRow = sheet.getRow(1); //表头
  181. int totalNumber = sheet.getLastRowNum();
  182. int rowNumber = 1;
  183. rowIterator.next();
  184. while(rowIterator.hasNext() && rowNumber <= totalNumber){
  185. rowNumber++;
  186. currentRow = rowIterator.next();
  187. Iterator<Cell> cellIterator = currentRow.iterator();
  188. OrderReportBo bo = new OrderReportBo();
  189. bo.setSaleName(checkEmptyStr(cellIterator.next().getStringCellValue(),rowNumber));
  190. //电话
  191. Cell cell = cellIterator.next();
  192. if(cell.getCellTypeEnum() == CellType.NUMERIC) {
  193. bo.setMobile(checkMobile(df.format(cell.getNumericCellValue()), rowNumber));
  194. }else if(cell.getCellTypeEnum() == CellType.STRING){
  195. bo.setMobile(checkMobile(cell.getStringCellValue(), rowNumber));
  196. }
  197. //月份
  198. cell = cellIterator.next();
  199. if(cell.getCellTypeEnum() == CellType.NUMERIC){
  200. bo.setMonth(checkOrderMonth(df.format(cell.getNumericCellValue()),rowNumber));
  201. }else if(cell.getCellTypeEnum() == CellType.STRING){
  202. bo.setMonth(checkOrderMonth(cell.getStringCellValue(),rowNumber));
  203. }
  204. bo.setType(checkOrderType(cellIterator.next().getStringCellValue(),rowNumber));
  205. //数量
  206. cell = cellIterator.next();
  207. if(cell.getCellTypeEnum() == CellType.NUMERIC){
  208. bo.setProofCount(Float.valueOf(checkEmptyStr(df.format(cell.getNumericCellValue()),rowNumber)));
  209. }else if(cell.getCellTypeEnum() == CellType.STRING){
  210. bo.setProofCount(Float.valueOf(checkEmptyStr(cell.getStringCellValue(),rowNumber)));
  211. }
  212. //金额
  213. cell = cellIterator.next();
  214. if(cell.getCellTypeEnum() == CellType.NUMERIC){
  215. bo.setAmount(new BigDecimal(checkEmptyStr(df.format(cell.getNumericCellValue()),rowNumber)));
  216. }else if(cell.getCellTypeEnum() == CellType.STRING){
  217. bo.setAmount(new BigDecimal(checkEmptyStr(cell.getStringCellValue(),rowNumber)));
  218. }
  219. bo.setRowNumber(rowNumber);
  220. result.add(bo);
  221. }
  222. return result;
  223. }
  224. private Integer checkOrderType(String OrderType,int rowNumber){
  225. if(checkEmpty(OrderType, rowNumber)){
  226. return null;
  227. }
  228. if(OrderType.equals("认证项目")) return 0;
  229. if(OrderType.equals("科技项目")) return 1;
  230. vacantRows.add(rowNumber);
  231. return null;
  232. }
  233. private Integer checkOrderMonth(String month,int rowNumber){
  234. if(checkEmpty(month, rowNumber)){
  235. return null;
  236. }
  237. Integer m = null;
  238. if(month.length() == 2 || month.length() == 6){
  239. try {
  240. m = Integer.valueOf(month.substring(month.length()-2, month.length()));
  241. } catch (Exception e) {
  242. vacantRows.add(rowNumber);
  243. return null;
  244. }
  245. }else{
  246. if(month.length() == 1){
  247. try {
  248. m = Integer.valueOf(month);
  249. } catch (Exception e) {
  250. vacantRows.add(rowNumber);
  251. return null;
  252. }
  253. }else{
  254. vacantRows.add(rowNumber);
  255. }
  256. }
  257. if(m>0 && m<=12){
  258. Integer re = Integer.valueOf(month);
  259. if(re>m){
  260. return re;
  261. }else{
  262. String ym = new SimpleDateFormat("yyyy").format(new Date()) + (m>10?m:"0"+m);
  263. return Integer.valueOf(ym);
  264. }
  265. }else{
  266. vacantRows.add(rowNumber);
  267. }
  268. return null;
  269. }
  270. private Boolean checkEmpty(String str,int rowNumber){
  271. if(StringUtils.isEmpty(str)) {
  272. vacantRows.add(rowNumber);
  273. return true;
  274. }else{
  275. return false;
  276. }
  277. }
  278. private String checkEmptyStr(String str,int rowNumber){
  279. if(StringUtils.isEmpty(str)) {
  280. vacantRows.add(rowNumber);
  281. return "";
  282. }else{
  283. return str;
  284. }
  285. }
  286. //检查表头
  287. // 2007 版本以上 最大支持1048576行
  288. public final static String EXCEL_FILE_2007 = "2007";
  289. // 2003 版本 最大支持65536 行
  290. public final static String EXCEL_FILE_2003 = "2003";
  291. /**
  292. * 功能描述 导出无头部标题行Excel
  293. *
  294. * @param title 表格sheet标题
  295. * @param dataset 数据集合
  296. * @param out 输出流
  297. * @param version 2003 或者 2007,不传时默认生成2003版本
  298. * @return void
  299. * @author
  300. * @date 2018-8-2
  301. */
  302. public void exportExcel(String title, Collection<T> dataset, OutputStream out, String version) {
  303. if (StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())) {
  304. exportExcel2003(title, null, dataset, out, "yyyy-MM-dd hh:mm:ss");
  305. } else {
  306. exportExcel2007(title, null, dataset, out, "yyyy-MM-dd hh:mm:ss");
  307. }
  308. }
  309. /**
  310. * 功能描述 导出带有头部标题行的Excel
  311. *
  312. * @param title 表格sheet标题
  313. * @param headers 头部标题集合
  314. * @param dataset 数据集合
  315. * @param out 输出流
  316. * @param version 2003 或者 2007,不传时默认生成2003版本
  317. * @return void
  318. * @author
  319. * @date 2018-8-2
  320. */
  321. public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String version) {
  322. if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) {
  323. exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd hh:mm:ss");
  324. } else {
  325. exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd hh:mm:ss");
  326. }
  327. }
  328. /**
  329. * 功能描述
  330. * 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中
  331. * 此版本生成2007以上版本的文件 (文件后缀:xlsx)
  332. *
  333. * @param title 表格sheet标题名
  334. * @param headers 表格头部标题集合
  335. * @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
  336. * JavaBean属性的数据类型有基本数据类型及String,Date
  337. * @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
  338. * @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
  339. * @return void
  340. * @author
  341. * @date 2018-8-2
  342. */
  343. @SuppressWarnings({"unchecked", "rawtypes"})
  344. public void exportExcel2007(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
  345. // 声明一个工作薄
  346. XSSFWorkbook workbook = new XSSFWorkbook();
  347. // 生成一个表格
  348. XSSFSheet sheet = workbook.createSheet(title);
  349. // 设置表格默认列宽度为20个字节
  350. sheet.setDefaultColumnWidth(20);
  351. // 生成一个样式
  352. XSSFCellStyle style = workbook.createCellStyle();
  353. // 设置这些样式(表头)
  354. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  355. style.setFillForegroundColor(new XSSFColor(Color.GRAY));
  356. style.setBorderBottom(BorderStyle.THIN);
  357. style.setBorderLeft(BorderStyle.THIN);
  358. style.setBorderRight(BorderStyle.THIN);
  359. style.setBorderTop(BorderStyle.THIN);
  360. style.setAlignment(HorizontalAlignment.CENTER);
  361. // 生成一个字体
  362. XSSFFont font = workbook.createFont();
  363. font.setBold(true);
  364. font.setFontName("宋体");
  365. font.setColor(new XSSFColor(Color.BLACK));
  366. font.setFontHeightInPoints((short) 11);
  367. // 把字体应用到当前的样式
  368. style.setFont(font);
  369. // 生成并设置另一个样式(内容)
  370. XSSFCellStyle style2 = workbook.createCellStyle();
  371. style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  372. style2.setFillForegroundColor(new XSSFColor(Color.WHITE));
  373. style2.setBorderBottom(BorderStyle.THIN);
  374. style2.setBorderLeft(BorderStyle.THIN);
  375. style2.setBorderRight(BorderStyle.THIN);
  376. style2.setBorderTop(BorderStyle.THIN);
  377. style2.setAlignment(HorizontalAlignment.CENTER);
  378. style2.setVerticalAlignment(VerticalAlignment.CENTER);
  379. // 生成另一个字体
  380. XSSFFont font2 = workbook.createFont();
  381. font2.setBold(false);
  382. font2.setFontName("宋体");
  383. font2.setColor(new XSSFColor(Color.BLACK));
  384. font2.setFontHeightInPoints((short) 11);
  385. // 把字体应用到当前的样式
  386. style2.setFont(font2);
  387. // 产生表格标题行
  388. XSSFRow row = sheet.createRow(0);
  389. XSSFCell cellHeader;
  390. for (int i = 0; i < headers.length; i++) {
  391. cellHeader = row.createCell(i);
  392. cellHeader.setCellStyle(style);
  393. cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
  394. }
  395. // 遍历集合数据,产生数据行
  396. Iterator<T> it = dataset.iterator();
  397. int index = 0;
  398. T t;
  399. Field[] fields;
  400. Field field;
  401. XSSFRichTextString richString;
  402. Pattern p = Pattern.compile("^//d+(//.//d+)?$");
  403. Matcher matcher;
  404. String fieldName;
  405. String getMethodName;
  406. XSSFCell cell;
  407. Class tCls;
  408. Method getMethod;
  409. Object value;
  410. String textValue;
  411. SimpleDateFormat sdf = new SimpleDateFormat(pattern);
  412. while (it.hasNext()) {
  413. index++;
  414. row = sheet.createRow(index);
  415. t = (T) it.next();
  416. // 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值
  417. //如果实体类里有serialVersionUID,请将serialVersionUID放在所有属性之后声明,否则会出现第一列为空
  418. fields = t.getClass().getDeclaredFields();
  419. for (int i = 0; i < fields.length; i++) {
  420. field = fields[i];
  421. fieldName = field.getName();
  422. if ("serialVersionUID".equals(fieldName)) {
  423. continue;
  424. }
  425. cell = row.createCell(i);
  426. cell.setCellStyle(style2);
  427. getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
  428. try {
  429. tCls = t.getClass();
  430. getMethod = tCls.getMethod(getMethodName, new Class[]{});
  431. value = getMethod.invoke(t, new Object[]{});
  432. // 判断值的类型后进行强制类型转换
  433. textValue = null;
  434. if (value instanceof Integer) {
  435. cell.setCellValue((Integer) value);
  436. } else if (value instanceof Float) {
  437. textValue = String.valueOf((Float) value);
  438. cell.setCellValue(textValue);
  439. } else if (value instanceof Double) {
  440. textValue = String.valueOf((Double) value);
  441. cell.setCellValue(textValue);
  442. } else if (value instanceof Long) {
  443. cell.setCellValue((Long) value);
  444. }
  445. if (value instanceof Boolean) {
  446. textValue = "是";
  447. if (!(Boolean) value) {
  448. textValue = "否";
  449. }
  450. } else if (value instanceof Date) {
  451. textValue = sdf.format((Date) value);
  452. } else {
  453. // 其它数据类型都当作字符串简单处理
  454. if (value != null) {
  455. textValue = value.toString();
  456. }
  457. }
  458. if (textValue != null) {
  459. matcher = p.matcher(textValue);
  460. if (matcher.matches()) {
  461. // 是数字当作double处理
  462. cell.setCellValue(Double.parseDouble(textValue));
  463. } else {
  464. richString = new XSSFRichTextString(textValue);
  465. cell.setCellValue(richString);
  466. }
  467. }
  468. } catch (NoSuchMethodException e) {
  469. e.printStackTrace();
  470. } catch (IllegalAccessException e) {
  471. e.printStackTrace();
  472. } catch (InvocationTargetException e) {
  473. e.printStackTrace();
  474. } catch (SecurityException e) {
  475. e.printStackTrace();
  476. } catch (IllegalArgumentException e) {
  477. e.printStackTrace();
  478. } finally {
  479. // 清理资源
  480. }
  481. }
  482. }
  483. try {
  484. workbook.write(out);
  485. workbook.close();
  486. } catch (IOException e) {
  487. e.printStackTrace();
  488. }
  489. }
  490. /**
  491. * 功能描述 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中 <br>
  492. * 此方法生成2003版本的excel,文件名后缀:xls
  493. *
  494. * @param title 表格sheet标题名
  495. * @param headers 表格头部标题集合
  496. * @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
  497. * JavaBean属性的数据类型有基本数据类型及String,Date
  498. * @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
  499. * @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
  500. * @return void
  501. * @author
  502. * @date 2018-8-2
  503. */
  504. @SuppressWarnings({"unchecked", "rawtypes", "resource"})
  505. public void exportExcel2003(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
  506. // 声明一个工作薄
  507. HSSFWorkbook workbook = new HSSFWorkbook();
  508. // 生成一个表格
  509. HSSFSheet sheet = workbook.createSheet(title);
  510. // 设置表格默认列宽度为15个字节
  511. sheet.setDefaultColumnWidth(20);
  512. // 生成一个样式
  513. HSSFCellStyle style = workbook.createCellStyle();
  514. // 设置这些样式
  515. style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
  516. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  517. style.setBorderBottom(BorderStyle.THIN);
  518. style.setBorderLeft(BorderStyle.THIN);
  519. style.setBorderRight(BorderStyle.THIN);
  520. style.setBorderTop(BorderStyle.THIN);
  521. style.setAlignment(HorizontalAlignment.CENTER);
  522. // 生成一个字体
  523. HSSFFont font = workbook.createFont();
  524. font.setBold(true);
  525. font.setFontName("宋体");
  526. font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
  527. font.setFontHeightInPoints((short) 11);
  528. // 把字体应用到当前的样式
  529. style.setFont(font);
  530. // 生成并设置另一个样式
  531. HSSFCellStyle style2 = workbook.createCellStyle();
  532. style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
  533. style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  534. style2.setBorderBottom(BorderStyle.THIN);
  535. style2.setBorderLeft(BorderStyle.THIN);
  536. style2.setBorderRight(BorderStyle.THIN);
  537. style2.setBorderTop(BorderStyle.THIN);
  538. style2.setAlignment(HorizontalAlignment.CENTER);
  539. style2.setVerticalAlignment(VerticalAlignment.CENTER);
  540. // 生成另一个字体
  541. HSSFFont font2 = workbook.createFont();
  542. font2.setBold(false);
  543. // 把字体应用到当前的样式
  544. style2.setFont(font2);
  545. // 产生表格标题行
  546. HSSFRow row = sheet.createRow(0);
  547. HSSFCell cellHeader;
  548. for (int i = 0; i < headers.length; i++) {
  549. cellHeader = row.createCell(i);
  550. cellHeader.setCellStyle(style);
  551. cellHeader.setCellValue(new HSSFRichTextString(headers[i]));
  552. }
  553. // 遍历集合数据,产生数据行
  554. Iterator<T> it = dataset.iterator();
  555. int index = 0;
  556. T t;
  557. Field[] fields;
  558. Field field;
  559. HSSFRichTextString richString;
  560. Pattern p = Pattern.compile("^//d+(//.//d+)?$");
  561. Matcher matcher;
  562. String fieldName;
  563. String getMethodName;
  564. HSSFCell cell;
  565. Class tCls;
  566. Method getMethod;
  567. Object value;
  568. String textValue;
  569. SimpleDateFormat sdf = new SimpleDateFormat(pattern);
  570. while (it.hasNext()) {
  571. index++;
  572. row = sheet.createRow(index);
  573. t = (T) it.next();
  574. // 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值
  575. //如果实体类里有serialVersionUID,请将serialVersionUID放在所有属性之后声明,否则会出现第一列为空
  576. fields = t.getClass().getDeclaredFields();
  577. for (int i = 0; i < fields.length; i++) {
  578. field = fields[i];
  579. fieldName = field.getName();
  580. if ("serialVersionUID".equals(fieldName)) {
  581. continue;
  582. }
  583. cell = row.createCell(i);
  584. cell.setCellStyle(style2);
  585. getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()
  586. + fieldName.substring(1);
  587. try {
  588. tCls = t.getClass();
  589. getMethod = tCls.getMethod(getMethodName, new Class[]{});
  590. value = getMethod.invoke(t, new Object[]{});
  591. // 判断值的类型后进行强制类型转换
  592. textValue = null;
  593. if (value instanceof Integer) {
  594. cell.setCellValue((Integer) value);
  595. } else if (value instanceof Float) {
  596. textValue = String.valueOf((Float) value);
  597. cell.setCellValue(textValue);
  598. } else if (value instanceof Double) {
  599. textValue = String.valueOf((Double) value);
  600. cell.setCellValue(textValue);
  601. } else if (value instanceof Long) {
  602. cell.setCellValue((Long) value);
  603. }
  604. if (value instanceof Boolean) {
  605. textValue = "是";
  606. if (!(Boolean) value) {
  607. textValue = "否";
  608. }
  609. } else if (value instanceof Date) {
  610. textValue = sdf.format((Date) value);
  611. } else {
  612. // 其它数据类型都当作字符串简单处理
  613. if (value != null) {
  614. textValue = value.toString();
  615. }
  616. }
  617. if (textValue != null) {
  618. matcher = p.matcher(textValue);
  619. if (matcher.matches()) {
  620. // 是数字当作double处理
  621. cell.setCellValue(Double.parseDouble(textValue));
  622. } else {
  623. richString = new HSSFRichTextString(textValue);
  624. cell.setCellValue(richString);
  625. }
  626. }
  627. } catch (SecurityException e) {
  628. e.printStackTrace();
  629. } catch (NoSuchMethodException e) {
  630. e.printStackTrace();
  631. } catch (IllegalArgumentException e) {
  632. e.printStackTrace();
  633. } catch (IllegalAccessException e) {
  634. e.printStackTrace();
  635. } catch (InvocationTargetException e) {
  636. e.printStackTrace();
  637. } finally {
  638. // 清理资源
  639. }
  640. }
  641. }
  642. try {
  643. workbook.write(out);
  644. } catch (IOException e) {
  645. e.printStackTrace();
  646. }
  647. }
  648. /**
  649. * 导出Excel
  650. *
  651. * @param titles 数据头
  652. * @param valueList 数据体
  653. * @param fileName Excel文件名
  654. * @param sheetName 首个sheet页名
  655. * @param response
  656. */
  657. @SuppressWarnings({ "resource", "unused" })
  658. public void listToExcel(String[] titles, List<List<Object>> valueList, String fileName, String sheetName, HttpServletResponse response) {
  659. try {
  660. response.setContentType("application/x-download");
  661. fileName = URLEncoder.encode(fileName, "UTF-8");
  662. response.addHeader("Content-Disposition", "attachement;filename=" + fileName);
  663. HSSFWorkbook workbook = new HSSFWorkbook();
  664. HSSFCellStyle cellStyle = workbook.createCellStyle();
  665. HSSFDataFormat format = workbook.createDataFormat();
  666. HSSFSheet sheet = workbook.createSheet();
  667. if (sheetName != null && sheetName.trim().length() >0 ) {
  668. workbook.setSheetName(0, sheetName);
  669. }
  670. HSSFRow row = sheet.createRow(0);
  671. HSSFCell cell;
  672. Set<String> set = new HashSet<>();
  673. // 构建表头
  674. for (int i = 0; i < titles.length; i++) {
  675. String title = titles[i];
  676. // cell = row.createCell[i];
  677. cell = row.createCell(i);
  678. cell.setCellType(CellType.STRING);
  679. cell.setCellValue(title);
  680. }
  681. // 构建表格
  682. for (int j = 0; j < valueList.size(); j++) {
  683. List<Object> values = valueList.get(j);
  684. row = sheet.createRow(j + 1);
  685. for (int m = 0; m < values.size(); m++) {
  686. cell = row.createCell(m);
  687. cell.setCellType(CellType.STRING);
  688. if (values.get(m) != null) {
  689. cell.setCellValue(values.get(m).toString());
  690. } else {
  691. cell.setCellValue("");
  692. }
  693. }
  694. }
  695. OutputStream out = response.getOutputStream();
  696. workbook.write(out);
  697. out.flush();
  698. out.close();
  699. } catch (Exception e) {
  700. e.printStackTrace();
  701. }
  702. }
  703. /**
  704. * 获取Excel单元格的值
  705. *
  706. * @param cell
  707. * @return
  708. */
  709. public static String getCellValue(Cell cell) {
  710. String value = "";
  711. if (cell != null) {
  712. switch (cell.getCellTypeEnum()) {
  713. case NUMERIC: //数字
  714. value = cell.getNumericCellValue() + "";
  715. if (HSSFDateUtil.isCellDateFormatted(cell)) {
  716. Date date = cell.getDateCellValue();
  717. if (date != null) {
  718. value = new SimpleDateFormat("yyyy/MM/dd").format(date);//日期格式化
  719. } else {
  720. value = "";
  721. }
  722. } else {
  723. //在解析cell的时候,数字类型默认是double的,但是想要的的整数的类型,需要格式化,很重要
  724. value = new DecimalFormat("0").format(cell.getNumericCellValue());
  725. }
  726. break;
  727. case STRING://字符串
  728. value = cell.getStringCellValue();
  729. break;
  730. case BOOLEAN://boolean类型
  731. value = cell.getBooleanCellValue() + "";
  732. break;
  733. case BLANK://空值
  734. value = "";
  735. break;
  736. case ERROR://错误类型
  737. value = "非法字符";
  738. break;
  739. default:
  740. value = "未知类型";
  741. }
  742. }
  743. return value.trim();
  744. }
  745. public static Date getCellDate(Cell cell) {
  746. Date date=new Date();
  747. if (cell != null) {
  748. switch (cell.getCellTypeEnum()) {
  749. case NUMERIC: //数字
  750. date = cell.getDateCellValue();
  751. break;
  752. case STRING://字符串
  753. date = DateUtils.StringToDate(cell.getStringCellValue(), AFTConstants.YYYYMMDD);
  754. default:
  755. date = new Date();
  756. }
  757. }
  758. return date;
  759. }
  760. public static Workbook getWorkBook(MultipartFile file) {
  761. //创建Workbook工作薄对象,表示整个excel
  762. Workbook workbook = null;
  763. try {
  764. //获取excel文件的io流
  765. InputStream is = file.getInputStream();
  766. //如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook
  767. if(file.getOriginalFilename().matches("^.+\\.(?i)(xls)$"))workbook = new HSSFWorkbook(is);
  768. if(file.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$"))workbook = new XSSFWorkbook(is);
  769. } catch (IOException e) {
  770. e.getLocalizedMessage();
  771. }
  772. return workbook;
  773. }
  774. }