ExcelUtils.java 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795
  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.customer.bo.CustomerExcelBo;
  20. import com.goafanti.order.bo.OrderReportBo;
  21. import org.apache.commons.lang3.StringUtils;
  22. import org.apache.poi.hssf.usermodel.*;
  23. import org.apache.poi.hssf.util.HSSFColor;
  24. import org.apache.poi.ss.formula.functions.T;
  25. import org.apache.poi.ss.usermodel.*;
  26. import org.apache.poi.xssf.usermodel.*;
  27. import javax.servlet.http.HttpServletResponse;
  28. import java.awt.Color;
  29. import java.io.OutputStream;
  30. import java.lang.reflect.Field;
  31. import java.lang.reflect.InvocationTargetException;
  32. import java.lang.reflect.Method;
  33. import java.net.URLEncoder;
  34. import java.util.*;
  35. import java.util.regex.Matcher;
  36. public class ExcelUtils {
  37. private Set<Integer> vacantRows = new HashSet<Integer>(); //数据不完整的
  38. private final String DEFAULT_MOBILE = "0000-0000000";
  39. DecimalFormat df = new DecimalFormat("0");
  40. @SuppressWarnings ("resource")
  41. public Set<CustomerExcelBo> parseExcel(String excelPath) throws IOException{
  42. InputStream excelFile = new FileInputStream(new File(excelPath));
  43. XSSFWorkbook wb = new XSSFWorkbook(excelFile);
  44. Set<CustomerExcelBo> result = new HashSet<CustomerExcelBo>();
  45. XSSFSheet sheet = wb.getSheetAt(0);
  46. Iterator<Row> rowIterator = sheet.iterator();
  47. Row currentRow = sheet.getRow(1); //表头
  48. Cell configCell = currentRow.getCell(1); //配置
  49. int totalNumber = 2;
  50. if(configCell.getCellTypeEnum() == CellType.NUMERIC){
  51. totalNumber = Integer.valueOf(df.format(configCell.getNumericCellValue()));
  52. }else if(configCell.getCellTypeEnum() == CellType.STRING){
  53. totalNumber = Integer.valueOf(configCell.getStringCellValue());
  54. }
  55. int rowNumber = 2;
  56. rowIterator.next();
  57. rowIterator.next();
  58. while(rowIterator.hasNext() && rowNumber < totalNumber){
  59. rowNumber++;
  60. currentRow = rowIterator.next();
  61. Iterator<Cell> cellIterator = currentRow.iterator();
  62. CustomerExcelBo bo = new CustomerExcelBo();
  63. bo.setCustomerType(checkCustomerType(cellIterator.next().getStringCellValue(), rowNumber));
  64. bo.setIdentifyName(checkOther(cellIterator.next().getStringCellValue(),rowNumber,64));
  65. bo.setFollowSituation(checkFollowSituation(cellIterator.next().getStringCellValue(), rowNumber));
  66. bo.setCustomerStatus(checkCustomerStatus(cellIterator.next().getStringCellValue(), rowNumber));
  67. // bo.setBusinessGlossoryId(checkCustomerIntention(cellIterator.next().getStringCellValue(), rowNumber));
  68. bo.setContacts(checkOther(cellIterator.next().getStringCellValue(),rowNumber,32));
  69. Cell phoneCell = cellIterator.next();
  70. if(phoneCell.getCellTypeEnum() == CellType.NUMERIC) {
  71. bo.setMobile(checkMobile(df.format(phoneCell.getNumericCellValue()), rowNumber));
  72. }else if(phoneCell.getCellTypeEnum() == CellType.STRING){
  73. bo.setMobile(checkMobile(phoneCell.getStringCellValue(), rowNumber));
  74. }
  75. bo.setRowNumber(rowNumber);
  76. result.add(bo);
  77. }
  78. return result;
  79. }
  80. private String checkCustomerType(String customerType,int rowNumber){
  81. if(StringUtils.isEmpty(customerType)) {
  82. vacantRows.add(rowNumber);
  83. return "";
  84. }
  85. if(customerType.equals("个人客户")) return "0";
  86. if(customerType.equals("单位客户")) return "1";
  87. vacantRows.add(rowNumber);
  88. return "";
  89. }
  90. private String checkFollowSituation(String followSituation,int rowNumber){
  91. if(StringUtils.isEmpty(followSituation)){
  92. vacantRows.add(rowNumber);
  93. return "";
  94. }
  95. if(followSituation.equals("已发项目介绍资料")) return "0";
  96. if(followSituation.equals("已约面谈")) return "1";
  97. if(followSituation.equals("已发合同计划书")) return "2";
  98. if(followSituation.equals("已报价")) return "3";
  99. if(followSituation.equals("已发合同")) return "4";
  100. if(followSituation.equals("已签合同")) return "5";
  101. if(followSituation.equals("面谈中")) return "6";
  102. if(followSituation.equals("已面签")) return "7";
  103. if(followSituation.equals("无进度")) return "8";
  104. vacantRows.add(rowNumber);
  105. return "";
  106. }
  107. private String checkCustomerStatus(String customerStatus,int rowNumber){
  108. if(StringUtils.isEmpty(customerStatus)) {
  109. vacantRows.add(rowNumber);
  110. return "";
  111. }
  112. if(customerStatus.equals("新客户")) return "0";
  113. if(customerStatus.equals("意向客户")) return "1";
  114. if(customerStatus.equals("重点客户")) return "2";
  115. if(customerStatus.equals("面谈客户")) return "3";
  116. if(customerStatus.equals("签单客户")) return "4";
  117. if(customerStatus.equals("被拒绝客户")) return "5";
  118. if(customerStatus.equals("停止跟进")) return "6";
  119. vacantRows.add(rowNumber);
  120. return "";
  121. }
  122. public boolean checkRepeat(CustomerExcelBo cin,List<CustomerExcelBo> cinList){
  123. boolean flag = false;
  124. return flag;
  125. }
  126. /*private String checkCustomerIntention(String companyIntention,int rowNumber){
  127. if(StringUtils.isEmpty(companyIntention)){
  128. vacantRows.add(rowNumber);
  129. return "";
  130. }
  131. if(companyIntention.equals("发明专利")) return "0";
  132. if(companyIntention.equals("实用型新型专利")) return "1";
  133. if(companyIntention.equals("外观专利")) return "2";
  134. if(companyIntention.equals("软件著作权")) return "3";
  135. if(companyIntention.equals("知识产权贯标")) return "4";
  136. if(companyIntention.equals("高企认定")) return "5";
  137. if(companyIntention.equals("技术成果")) return "6";
  138. if(companyIntention.equals("技术需求")) return "7";
  139. if(companyIntention.equals("专家咨询")) return "8";
  140. if(companyIntention.equals("团单合作")) return "9";
  141. if(companyIntention.equals("商标")) return "10";
  142. if(companyIntention.equals("系统集成")) return "11";
  143. vacantRows.add(rowNumber);
  144. return "";
  145. }*/
  146. private String checkOther(String cellValue,int rowNumber,int maxLength){
  147. if(StringUtils.isBlank(cellValue) || cellValue.length() > maxLength){
  148. vacantRows.add(rowNumber);
  149. return "";
  150. }else{
  151. return String.valueOf(cellValue).trim();
  152. }
  153. }
  154. public String checkMobile(String mobile, int rowNumber){
  155. mobile = mobile.trim();
  156. String mobileRegex = "^1[3|4|5|7|8][0-9]{9}$";
  157. String telRegex = "^\\d{3,4}-\\d{7,8}$";
  158. if(StringUtils.isBlank(mobile)) return "";
  159. if(DEFAULT_MOBILE.equals(mobile)) return mobile;
  160. if(!Pattern.matches(mobileRegex, mobile) && !Pattern.matches(telRegex, mobile)){
  161. vacantRows.add(rowNumber);
  162. return "";
  163. }
  164. return mobile;
  165. }
  166. public String getVacantRows(){
  167. if(vacantRows.size()>0)
  168. return StringUtils.join(vacantRows.toArray(),",")+" 行数据不全或格式有误;";
  169. else
  170. return "";
  171. }
  172. @SuppressWarnings("resource")
  173. public Set<OrderReportBo> parseOrderExcel(String excelPath) throws IOException{
  174. InputStream excelFile = new FileInputStream(new File(excelPath));
  175. XSSFWorkbook wb = new XSSFWorkbook(excelFile);
  176. Set<OrderReportBo> result = new HashSet<OrderReportBo>();
  177. XSSFSheet sheet = wb.getSheetAt(0);
  178. Iterator<Row> rowIterator = sheet.iterator();
  179. Row currentRow = sheet.getRow(1); //表头
  180. int totalNumber = sheet.getLastRowNum();
  181. int rowNumber = 1;
  182. rowIterator.next();
  183. while(rowIterator.hasNext() && rowNumber <= totalNumber){
  184. rowNumber++;
  185. currentRow = rowIterator.next();
  186. Iterator<Cell> cellIterator = currentRow.iterator();
  187. OrderReportBo bo = new OrderReportBo();
  188. bo.setSaleName(checkEmptyStr(cellIterator.next().getStringCellValue(),rowNumber));
  189. //电话
  190. Cell cell = cellIterator.next();
  191. if(cell.getCellTypeEnum() == CellType.NUMERIC) {
  192. bo.setMobile(checkMobile(df.format(cell.getNumericCellValue()), rowNumber));
  193. }else if(cell.getCellTypeEnum() == CellType.STRING){
  194. bo.setMobile(checkMobile(cell.getStringCellValue(), rowNumber));
  195. }
  196. //月份
  197. cell = cellIterator.next();
  198. if(cell.getCellTypeEnum() == CellType.NUMERIC){
  199. bo.setMonth(checkOrderMonth(df.format(cell.getNumericCellValue()),rowNumber));
  200. }else if(cell.getCellTypeEnum() == CellType.STRING){
  201. bo.setMonth(checkOrderMonth(cell.getStringCellValue(),rowNumber));
  202. }
  203. bo.setType(checkOrderType(cellIterator.next().getStringCellValue(),rowNumber));
  204. //数量
  205. cell = cellIterator.next();
  206. if(cell.getCellTypeEnum() == CellType.NUMERIC){
  207. bo.setProofCount(Float.valueOf(checkEmptyStr(df.format(cell.getNumericCellValue()),rowNumber)));
  208. }else if(cell.getCellTypeEnum() == CellType.STRING){
  209. bo.setProofCount(Float.valueOf(checkEmptyStr(cell.getStringCellValue(),rowNumber)));
  210. }
  211. //金额
  212. cell = cellIterator.next();
  213. if(cell.getCellTypeEnum() == CellType.NUMERIC){
  214. bo.setAmount(new BigDecimal(checkEmptyStr(df.format(cell.getNumericCellValue()),rowNumber)));
  215. }else if(cell.getCellTypeEnum() == CellType.STRING){
  216. bo.setAmount(new BigDecimal(checkEmptyStr(cell.getStringCellValue(),rowNumber)));
  217. }
  218. bo.setRowNumber(rowNumber);
  219. result.add(bo);
  220. }
  221. return result;
  222. }
  223. private Integer checkOrderType(String OrderType,int rowNumber){
  224. if(checkEmpty(OrderType, rowNumber)){
  225. return null;
  226. }
  227. if(OrderType.equals("认证项目")) return 0;
  228. if(OrderType.equals("科技项目")) return 1;
  229. vacantRows.add(rowNumber);
  230. return null;
  231. }
  232. private Integer checkOrderMonth(String month,int rowNumber){
  233. if(checkEmpty(month, rowNumber)){
  234. return null;
  235. }
  236. Integer m = null;
  237. if(month.length() == 2 || month.length() == 6){
  238. try {
  239. m = Integer.valueOf(month.substring(month.length()-2, month.length()));
  240. } catch (Exception e) {
  241. vacantRows.add(rowNumber);
  242. return null;
  243. }
  244. }else{
  245. if(month.length() == 1){
  246. try {
  247. m = Integer.valueOf(month);
  248. } catch (Exception e) {
  249. vacantRows.add(rowNumber);
  250. return null;
  251. }
  252. }else{
  253. vacantRows.add(rowNumber);
  254. }
  255. }
  256. if(m>0 && m<=12){
  257. Integer re = Integer.valueOf(month);
  258. if(re>m){
  259. return re;
  260. }else{
  261. String ym = new SimpleDateFormat("yyyy").format(new Date()) + (m>10?m:"0"+m);
  262. System.out.println(ym);
  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 Workbook getWorkBook(MultipartFile file) {
  746. //创建Workbook工作薄对象,表示整个excel
  747. Workbook workbook = null;
  748. try {
  749. //获取excel文件的io流
  750. InputStream is = file.getInputStream();
  751. //如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook
  752. if(file.getOriginalFilename().matches("^.+\\.(?i)(xls)$"))workbook = new HSSFWorkbook(is);
  753. if(file.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$"))workbook = new XSSFWorkbook(is);
  754. } catch (IOException e) {
  755. }
  756. return workbook;
  757. }
  758. }