NewExcelUtil.java 29 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057
  1. package com.goafanti.common.utils.excel;
  2. import java.io.ByteArrayInputStream;
  3. import java.io.ByteArrayOutputStream;
  4. import java.io.File;
  5. import java.io.FileInputStream;
  6. import java.io.FileOutputStream;
  7. import java.io.IOException;
  8. import java.io.InputStream;
  9. import java.io.OutputStream;
  10. import java.lang.reflect.Field;
  11. import java.math.BigDecimal;
  12. import java.net.URL;
  13. import java.net.URLConnection;
  14. import java.text.DecimalFormat;
  15. import java.util.ArrayList;
  16. import java.util.Arrays;
  17. import java.util.Comparator;
  18. import java.util.Date;
  19. import java.util.HashMap;
  20. import java.util.List;
  21. import java.util.Map;
  22. import java.util.Set;
  23. import java.util.UUID;
  24. import java.util.stream.Collectors;
  25. import javax.servlet.http.HttpServletResponse;
  26. import org.apache.poi.ss.usermodel.BorderStyle;
  27. import org.apache.poi.ss.usermodel.Cell;
  28. import org.apache.poi.ss.usermodel.CellStyle;
  29. import org.apache.poi.ss.usermodel.CellType;
  30. import org.apache.poi.ss.usermodel.ClientAnchor;
  31. import org.apache.poi.ss.usermodel.DataValidation;
  32. import org.apache.poi.ss.usermodel.DataValidationConstraint;
  33. import org.apache.poi.ss.usermodel.DataValidationHelper;
  34. import org.apache.poi.ss.usermodel.DateUtil;
  35. import org.apache.poi.ss.usermodel.Drawing;
  36. import org.apache.poi.ss.usermodel.FillPatternType;
  37. import org.apache.poi.ss.usermodel.Font;
  38. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  39. import org.apache.poi.ss.usermodel.IndexedColors;
  40. import org.apache.poi.ss.usermodel.Row;
  41. import org.apache.poi.ss.usermodel.Sheet;
  42. import org.apache.poi.ss.usermodel.VerticalAlignment;
  43. import org.apache.poi.ss.usermodel.Workbook;
  44. import org.apache.poi.ss.util.CellRangeAddress;
  45. import org.apache.poi.ss.util.CellRangeAddressList;
  46. import org.apache.poi.util.IOUtils;
  47. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  48. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  49. import org.apache.poi.xssf.usermodel.XSSFDataValidation;
  50. import org.slf4j.Logger;
  51. import org.slf4j.LoggerFactory;
  52. import com.goafanti.common.bo.Result;
  53. import com.goafanti.common.constant.AFTConstants;
  54. import com.goafanti.common.error.BusinessException;
  55. import com.goafanti.common.utils.DateUtils;
  56. import com.goafanti.common.utils.StringUtils;
  57. import com.goafanti.common.utils.excel.Excel.ColumnType;
  58. /**
  59. * Excel相关处理
  60. *
  61. *
  62. */
  63. public class NewExcelUtil<T> {
  64. private static final Logger log = LoggerFactory.getLogger(NewExcelUtil.class);
  65. /**
  66. * Excel sheet最大行数,默认65536
  67. */
  68. public static final int sheetSize = 65536;
  69. public String downloadPath = "";
  70. /**
  71. * 工作表名称
  72. */
  73. private String sheetName;
  74. /**
  75. * 工作薄对象
  76. */
  77. private Workbook wb;
  78. /**
  79. * 工作表对象
  80. */
  81. private Sheet sheet;
  82. /**
  83. * 样式列表
  84. */
  85. private Map<String, CellStyle> styles;
  86. /**
  87. * 导入导出数据列表
  88. */
  89. private List<T> list;
  90. /**
  91. * 注解列表
  92. */
  93. private List<Object[]> fields;
  94. /**
  95. * 最大高度
  96. */
  97. private short maxHeight;
  98. /**
  99. * 统计列表
  100. */
  101. private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
  102. /**
  103. * 数字格式
  104. */
  105. private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
  106. /**
  107. * 实体对象
  108. */
  109. public Class<T> clazz;
  110. public NewExcelUtil(Class<T> clazz) {
  111. this.clazz = clazz;
  112. }
  113. public void init(List<T> list, String sheetName) {
  114. if (list == null) {
  115. list = new ArrayList<T>();
  116. }
  117. this.list = list;
  118. this.sheetName = sheetName;
  119. createExcelField();
  120. createWorkbook();
  121. }
  122. /**
  123. * 对list数据源将其里面的数据导入到excel表单
  124. *
  125. * @param list 导出数据集合
  126. * @param sheetName 工作表的名称
  127. * @return 结果
  128. */
  129. public Result exportExcel(List<T> list, String sheetName) {
  130. this.init(list, sheetName);
  131. return exportExcel();
  132. }
  133. /**
  134. * 直接导出CXCEL表
  135. * @param list 导出数据集合
  136. * @param sheetName 工作表的名称
  137. * @param response
  138. */
  139. public Result exportExcel(List<T> list, String sheetName, HttpServletResponse response) {
  140. this.init(list, sheetName);
  141. exportExcel(response);
  142. return new Result().data(1);
  143. }
  144. /**
  145. * 导出CXCEL表到指定目录
  146. * @param list 导出数据集合
  147. * @param sheetName 工作表的名称
  148. * @param response
  149. */
  150. public Result exportExcel(List<T> list, String sheetName, String uploadPath) {
  151. this.init(list, sheetName);
  152. this.downloadPath=uploadPath;
  153. return exportExcel();
  154. }
  155. private void exportExcel(HttpServletResponse response) {
  156. try {
  157. OutputStream out = response.getOutputStream();
  158. // 取出一共有多少个sheet.
  159. double sheetNo = Math.ceil(list.size() / sheetSize);
  160. String dateFilename =DateFilename(sheetName);
  161. for (int index = 0; index <= sheetNo; index++) {
  162. createSheet(sheetNo, index);
  163. // 产生一行
  164. Row title = sheet.createRow(0);
  165. Cell cell = title.createCell(0);
  166. cell.setCellValue(sheetName);
  167. CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
  168. sheet.addMergedRegion(region);
  169. cell.setCellStyle(styles.get("total"));
  170. Row row = sheet.createRow(1);
  171. int column = 0;
  172. // 写入各个字段的列头名称
  173. for (Object[] os : fields) {
  174. Excel excel = (Excel) os[1];
  175. this.createCell(excel, row, column++);
  176. }
  177. fillExcelData(index, row);
  178. addStatisticsRow();
  179. }
  180. response.addHeader("Content-Disposition", "attachment;filename=" + new String(dateFilename.getBytes(),"iso-8859-1"));
  181. response.setContentType("application/octet-stream;charset=utf-8");
  182. wb.write(out);
  183. out.flush();
  184. out.close();
  185. } catch (Exception e) {
  186. log.error("导出Excel异常{}", e.getMessage());
  187. throw new BusinessException("导出Excel失败,请联系网站管理员!");
  188. } finally {
  189. if (wb != null) {
  190. try {
  191. wb.close();
  192. } catch (IOException e1) {
  193. e1.printStackTrace();
  194. }
  195. }
  196. }
  197. }
  198. private String DateFilename(String sheetName) {
  199. sheetName = sheetName + "_" + DateUtils.formatDate(new Date(), AFTConstants.YYYYMMDDHHMMSS)+".xlsx";
  200. return sheetName;
  201. }
  202. /**
  203. * 对list数据源将其里面的数据导入到excel表单
  204. *
  205. * @param sheetName 工作表的名称
  206. * @return 结果
  207. */
  208. public Result importTemplateExcel(String sheetName) {
  209. this.init(null, sheetName);
  210. return exportExcel();
  211. }
  212. /**
  213. * 对list数据源将其里面的数据导入到excel表单
  214. *
  215. * @return 结果
  216. */
  217. public Result exportExcel() {
  218. OutputStream out = null;
  219. try {
  220. // 取出一共有多少个sheet.
  221. double sheetNo = Math.ceil(list.size() / sheetSize);
  222. for (int index = 0; index <= sheetNo; index++) {
  223. createSheet(sheetNo, index);
  224. // 产生一行
  225. Row title = sheet.createRow(0);
  226. Cell cell = title.createCell(0);
  227. cell.setCellValue(sheetName);
  228. CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
  229. sheet.addMergedRegion(region);
  230. cell.setCellStyle(styles.get("total"));
  231. Row row = sheet.createRow(1);
  232. // 产生一行
  233. int column = 0;
  234. // 写入各个字段的列头名称
  235. for (Object[] os : fields) {
  236. Excel excel = (Excel) os[1];
  237. this.createCell(excel, row, column++);
  238. }
  239. fillExcelData(index, row);
  240. addStatisticsRow();
  241. }
  242. String filename = encodingFilename(sheetName);
  243. out = new FileOutputStream(getAbsoluteFile(filename));
  244. wb.write(out);
  245. return new Result().data(filename);
  246. } catch (Exception e) {
  247. log.error("导出Excel异常{}", e.getMessage());
  248. throw new BusinessException("导出Excel失败,请联系网站管理员!");
  249. } finally {
  250. if (wb != null) {
  251. try {
  252. wb.close();
  253. } catch (IOException e1) {
  254. e1.printStackTrace();
  255. }
  256. }
  257. if (out != null) {
  258. try {
  259. out.close();
  260. } catch (IOException e1) {
  261. e1.printStackTrace();
  262. }
  263. }
  264. }
  265. }
  266. /**
  267. * 填充excel数据
  268. *
  269. * @param index 序号
  270. * @param row 单元格行
  271. */
  272. public void fillExcelData(int index, Row row) {
  273. int startNo = index * sheetSize;
  274. int endNo = Math.min(startNo + sheetSize, list.size());
  275. for (int i = startNo; i < endNo; i++) {
  276. row = sheet.createRow(i + 2 - startNo);
  277. // 得到导出对象.
  278. T vo = (T) list.get(i);
  279. int column = 0;
  280. for (Object[] os : fields) {
  281. Field field = (Field) os[0];
  282. Excel excel = (Excel) os[1];
  283. // 设置实体类私有属性可访问
  284. field.setAccessible(true);
  285. this.addCell(excel, row, vo, field, column++);
  286. }
  287. }
  288. }
  289. /**
  290. * 创建表格样式
  291. *
  292. * @param wb 工作薄对象
  293. * @return 样式列表
  294. */
  295. private Map<String, CellStyle> createStyles(Workbook wb) {
  296. // 写入各条记录,每条记录对应excel表中的一行
  297. Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  298. //
  299. CellStyle style = wb.createCellStyle();
  300. style.setAlignment(HorizontalAlignment.CENTER);
  301. style.setVerticalAlignment(VerticalAlignment.CENTER);
  302. style.setBorderRight(BorderStyle.THIN);
  303. style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  304. style.setBorderLeft(BorderStyle.THIN);
  305. style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  306. style.setBorderTop(BorderStyle.THIN);
  307. style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  308. style.setBorderBottom(BorderStyle.THIN);
  309. style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  310. Font dataFont = wb.createFont();
  311. dataFont.setFontName("Arial");
  312. dataFont.setFontHeightInPoints((short) 10);
  313. style.setFont(dataFont);
  314. styles.put("data", style);
  315. style = wb.createCellStyle();
  316. style.cloneStyleFrom(styles.get("data"));
  317. style.setAlignment(HorizontalAlignment.CENTER);
  318. style.setVerticalAlignment(VerticalAlignment.CENTER);
  319. style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  320. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  321. Font headerFont = wb.createFont();
  322. headerFont.setFontName("Arial");
  323. headerFont.setFontHeightInPoints((short) 10);
  324. headerFont.setBold(true);
  325. headerFont.setColor(IndexedColors.WHITE.getIndex());
  326. style.setFont(headerFont);
  327. styles.put("header", style);
  328. style = wb.createCellStyle();
  329. style.cloneStyleFrom(styles.get("data"));
  330. style.setAlignment(HorizontalAlignment.CENTER);
  331. style.setVerticalAlignment(VerticalAlignment.CENTER);
  332. style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
  333. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  334. Font totalFont = wb.createFont();
  335. totalFont.setFontName("Arial");
  336. totalFont.setFontHeightInPoints((short) 10);
  337. totalFont.setColor(IndexedColors.WHITE.getIndex());
  338. style.setFont(totalFont);
  339. styles.put("total", style);
  340. style = wb.createCellStyle();
  341. style.cloneStyleFrom(styles.get("data"));
  342. style.setAlignment(HorizontalAlignment.LEFT);
  343. styles.put("data1", style);
  344. style = wb.createCellStyle();
  345. style.cloneStyleFrom(styles.get("data"));
  346. style.setAlignment(HorizontalAlignment.CENTER);
  347. styles.put("data2", style);
  348. style = wb.createCellStyle();
  349. style.cloneStyleFrom(styles.get("data"));
  350. style.setAlignment(HorizontalAlignment.RIGHT);
  351. styles.put("data3", style);
  352. return styles;
  353. }
  354. /**
  355. * 创建单元格
  356. */
  357. public Cell createCell(Excel attr, Row row, int column) {
  358. // 创建列
  359. Cell cell = row.createCell(column);
  360. // 写入列信息
  361. cell.setCellValue(attr.name());
  362. setDataValidation(attr, row, column);
  363. cell.setCellStyle(styles.get("header"));
  364. return cell;
  365. }
  366. /**
  367. * 创建单元格
  368. */
  369. public Cell createTotal(String str, Row row, int column) {
  370. // 创建列
  371. Cell cell = row.createCell(column);
  372. // 写入列信息
  373. cell.setCellValue(str);
  374. sheet.setColumnWidth(column, (int) ((14 + 0.72) * 256));
  375. cell.setCellStyle(styles.get("header"));
  376. return cell;
  377. }
  378. /**
  379. * 设置单元格信息
  380. *
  381. * @param value 单元格值
  382. * @param attr 注解相关
  383. * @param cell 单元格信息
  384. */
  385. public void setCellVo(Object value, Excel attr, Cell cell) {
  386. if (ColumnType.STRING == attr.cellType()) {
  387. cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
  388. } else if (ColumnType.NUMERIC == attr.cellType()) {
  389. cell.setCellValue(StringUtils.contains(toStr(value), ".") ? toDouble(value) : toInt(value));
  390. } else if (ColumnType.IMAGE == attr.cellType()) {
  391. ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(),
  392. cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
  393. String imagePath = toStr(value);
  394. if (StringUtils.isNotEmpty(imagePath)) {
  395. byte[] data = getImage(imagePath);
  396. getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
  397. cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
  398. }
  399. }
  400. }
  401. /**
  402. * 获取画布
  403. */
  404. public static Drawing<?> getDrawingPatriarch(Sheet sheet) {
  405. if (sheet.getDrawingPatriarch() == null) {
  406. sheet.createDrawingPatriarch();
  407. }
  408. return sheet.getDrawingPatriarch();
  409. }
  410. /**
  411. * 获取图片类型,设置图片插入类型
  412. */
  413. public int getImageType(byte[] value) {
  414. String type = getFileExtendName(value);
  415. if ("JPG".equalsIgnoreCase(type)) {
  416. return Workbook.PICTURE_TYPE_JPEG;
  417. } else if ("PNG".equalsIgnoreCase(type)) {
  418. return Workbook.PICTURE_TYPE_PNG;
  419. }
  420. return Workbook.PICTURE_TYPE_JPEG;
  421. }
  422. /**
  423. * 创建表格样式
  424. */
  425. public void setDataValidation(Excel attr, Row row, int column) {
  426. if (attr.name().indexOf("注:") >= 0) {
  427. sheet.setColumnWidth(column, 6000);
  428. } else {
  429. // 设置列宽
  430. sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
  431. }
  432. // 如果设置了提示信息则鼠标放上去提示.
  433. if (StringUtils.isNotEmpty(attr.prompt())) {
  434. // 这里默认设了2-101列提示.
  435. setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
  436. }
  437. // 如果设置了combo属性则本列只能选择不能输入
  438. if (attr.combo().length > 0) {
  439. // 这里默认设了2-101列只能选择不能输入.
  440. setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
  441. }
  442. }
  443. /**
  444. * 添加单元格
  445. */
  446. @SuppressWarnings("deprecation")
  447. public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
  448. Cell cell = null;
  449. try {
  450. // 设置行高
  451. row.setHeight(maxHeight);
  452. // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  453. if (attr.isExport()) {
  454. // 创建cell
  455. cell = row.createCell(column);
  456. int align = attr.align().value();
  457. cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
  458. // 用于读取对象中的属性
  459. Object value = getTargetValue(vo, field, attr);
  460. String dateFormat = attr.dateFormat();
  461. String readConverterExp = attr.readConverterExp();
  462. String separator = attr.separator();
  463. if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
  464. cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
  465. } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
  466. cell.setCellValue(convertByExp(toStr(value), readConverterExp, separator));
  467. } else if (value instanceof BigDecimal && -1 != attr.scale()) {
  468. cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
  469. } else{
  470. // 设置列类型
  471. setCellVo(value, attr, cell);
  472. }
  473. addStatisticsData(column, toStr(value), attr);
  474. }
  475. } catch (Exception e) {
  476. log.error("导出Excel失败{}", e);
  477. }
  478. return cell;
  479. }
  480. /**
  481. * 设置 POI XSSFSheet 单元格提示
  482. *
  483. * @param sheet 表单
  484. * @param promptTitle 提示标题
  485. * @param promptContent 提示内容
  486. * @param firstRow 开始行
  487. * @param endRow 结束行
  488. * @param firstCol 开始列
  489. * @param endCol 结束列
  490. */
  491. public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
  492. int firstCol, int endCol) {
  493. DataValidationHelper helper = sheet.getDataValidationHelper();
  494. DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
  495. CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  496. DataValidation dataValidation = helper.createValidation(constraint, regions);
  497. dataValidation.createPromptBox(promptTitle, promptContent);
  498. dataValidation.setShowPromptBox(true);
  499. sheet.addValidationData(dataValidation);
  500. }
  501. /**
  502. * 设置某些列的值只能输入预制的数据,显示下拉框.
  503. *
  504. * @param sheet 要设置的sheet.
  505. * @param textlist 下拉框显示的内容
  506. * @param firstRow 开始行
  507. * @param endRow 结束行
  508. * @param firstCol 开始列
  509. * @param endCol 结束列
  510. * @return 设置好的sheet.
  511. */
  512. public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
  513. DataValidationHelper helper = sheet.getDataValidationHelper();
  514. // 加载下拉列表内容
  515. DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
  516. // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
  517. CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  518. // 数据有效性对象
  519. DataValidation dataValidation = helper.createValidation(constraint, regions);
  520. // 处理Excel兼容性问题
  521. if (dataValidation instanceof XSSFDataValidation) {
  522. dataValidation.setSuppressDropDownArrow(true);
  523. dataValidation.setShowErrorBox(true);
  524. } else {
  525. dataValidation.setSuppressDropDownArrow(false);
  526. }
  527. sheet.addValidationData(dataValidation);
  528. }
  529. /**
  530. * 解析导出值 0=男,1=女,2=未知
  531. *
  532. * @param propertyValue 参数值
  533. * @param converterExp 翻译注解
  534. * @param separator 分隔符
  535. * @return 解析后值
  536. */
  537. public static String convertByExp(String propertyValue, String converterExp, String separator) {
  538. StringBuilder propertyString = new StringBuilder();
  539. String[] convertSource = converterExp.split(",");
  540. for (String item : convertSource) {
  541. String[] itemArray = item.split("=");
  542. if (StringUtils.containsAny(separator, propertyValue)) {
  543. for (String value : propertyValue.split(separator)) {
  544. if (itemArray[0].equals(value)) {
  545. propertyString.append(itemArray[1] + separator);
  546. break;
  547. }
  548. }
  549. } else {
  550. if (itemArray[0].equals(propertyValue)) {
  551. return itemArray[1];
  552. }
  553. }
  554. }
  555. return StringUtils.stripEnd(propertyString.toString(), separator);
  556. }
  557. /**
  558. * 反向解析值 男=0,女=1,未知=2
  559. *
  560. * @param propertyValue 参数值
  561. * @param converterExp 翻译注解
  562. * @param separator 分隔符
  563. * @return 解析后值
  564. */
  565. public static String reverseByExp(String propertyValue, String converterExp, String separator) {
  566. StringBuilder propertyString = new StringBuilder();
  567. String[] convertSource = converterExp.split(",");
  568. for (String item : convertSource) {
  569. String[] itemArray = item.split("=");
  570. if (StringUtils.containsAny(separator, propertyValue)) {
  571. for (String value : propertyValue.split(separator)) {
  572. if (itemArray[1].equals(value)) {
  573. propertyString.append(itemArray[0] + separator);
  574. break;
  575. }
  576. }
  577. } else {
  578. if (itemArray[1].equals(propertyValue)) {
  579. return itemArray[0];
  580. }
  581. }
  582. }
  583. return StringUtils.stripEnd(propertyString.toString(), separator);
  584. }
  585. /**
  586. * 合计统计信息
  587. */
  588. private void addStatisticsData(Integer index, String text, Excel entity) {
  589. if (entity != null && entity.isStatistics()) {
  590. Double temp = 0D;
  591. if (!statistics.containsKey(index)) {
  592. statistics.put(index, temp);
  593. }
  594. try {
  595. temp = Double.valueOf(text);
  596. } catch (NumberFormatException e) {
  597. }
  598. statistics.put(index, statistics.get(index) + temp);
  599. }
  600. }
  601. /**
  602. * 创建统计行
  603. */
  604. public void addStatisticsRow() {
  605. if (statistics.size() > 0) {
  606. Cell cell = null;
  607. Row row = sheet.createRow(sheet.getLastRowNum() + 1);
  608. Set<Integer> keys = statistics.keySet();
  609. cell = row.createCell(0);
  610. cell.setCellStyle(styles.get("total"));
  611. cell.setCellValue("合计");
  612. for (Integer key : keys) {
  613. cell = row.createCell(key);
  614. cell.setCellStyle(styles.get("total"));
  615. cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
  616. }
  617. statistics.clear();
  618. }
  619. }
  620. /**
  621. * 编码文件名
  622. */
  623. public String encodingFilename(String filename) {
  624. filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
  625. return filename;
  626. }
  627. /**
  628. * 获取下载路径
  629. *
  630. * @param filename 文件名称
  631. */
  632. public String getAbsoluteFile(String filename) {
  633. String downloadPath = this.downloadPath+"/" + filename;
  634. File desc = new File(downloadPath);
  635. if (!desc.getParentFile().exists()) {
  636. desc.getParentFile().mkdirs();
  637. }
  638. log.debug(downloadPath);
  639. return downloadPath;
  640. }
  641. /**
  642. * 获取bean中的属性值
  643. *
  644. * @param vo 实体对象
  645. * @param field 字段
  646. * @param excel 注解
  647. * @return 最终的属性值
  648. * @throws Exception
  649. */
  650. private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
  651. Object o = field.get(vo);
  652. if (StringUtils.isNotEmpty(excel.targetAttr())) {
  653. String target = excel.targetAttr();
  654. if (target.indexOf(".") > -1) {
  655. String[] targets = target.split("[.]");
  656. for (String name : targets) {
  657. o = getValue(o, name);
  658. }
  659. } else {
  660. o = getValue(o, target);
  661. }
  662. }
  663. return o;
  664. }
  665. /**
  666. * 以类的属性的get方法方法形式获取值
  667. *
  668. * @param o
  669. * @param name
  670. * @return value
  671. * @throws Exception
  672. */
  673. private Object getValue(Object o, String name) throws Exception {
  674. if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name)) {
  675. Class<?> clazz = o.getClass();
  676. Field field = clazz.getDeclaredField(name);
  677. field.setAccessible(true);
  678. o = field.get(o);
  679. }
  680. return o;
  681. }
  682. /**
  683. * 得到所有定义字段
  684. */
  685. private void createExcelField() {
  686. this.fields = new ArrayList<Object[]>();
  687. List<Field> tempFields = new ArrayList<>();
  688. tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  689. tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  690. for (Field field : tempFields) {
  691. // 单注解
  692. if (field.isAnnotationPresent(Excel.class)) {
  693. putToField(field, field.getAnnotation(Excel.class));
  694. }
  695. // 多注解
  696. if (field.isAnnotationPresent(Excels.class)) {
  697. Excels attrs = field.getAnnotation(Excels.class);
  698. Excel[] excels = attrs.value();
  699. for (Excel excel : excels) {
  700. putToField(field, excel);
  701. }
  702. }
  703. }
  704. this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort()))
  705. .collect(Collectors.toList());
  706. this.maxHeight = getRowHeight();
  707. }
  708. /**
  709. * 根据注解获取最大行高
  710. */
  711. public short getRowHeight() {
  712. double maxHeight = 0;
  713. for (Object[] os : this.fields) {
  714. Excel excel = (Excel) os[1];
  715. maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
  716. }
  717. return (short) (maxHeight * 20);
  718. }
  719. /**
  720. * 放到字段集合中
  721. */
  722. private void putToField(Field field, Excel attr) {
  723. if (attr != null) {
  724. this.fields.add(new Object[] { field, attr });
  725. }
  726. }
  727. /**
  728. * 创建一个工作簿
  729. */
  730. public void createWorkbook() {
  731. this.wb = new SXSSFWorkbook(500);
  732. }
  733. /**
  734. * 创建工作表
  735. *
  736. * @param sheetNo sheet数量
  737. * @param index 序号
  738. */
  739. public void createSheet(double sheetNo, int index) {
  740. this.sheet = wb.createSheet();
  741. this.styles = createStyles(wb);
  742. // 设置工作表的名称.
  743. if (sheetNo == 0) {
  744. wb.setSheetName(index, sheetName);
  745. } else {
  746. wb.setSheetName(index, sheetName + index);
  747. }
  748. }
  749. /**
  750. * 获取单元格值
  751. *
  752. * @param row 获取的行
  753. * @param column 获取单元格列号
  754. * @return 单元格值
  755. */
  756. public Object getCellValue(Row row, int column) {
  757. if (row == null) {
  758. return row;
  759. }
  760. Object val = "";
  761. try {
  762. Cell cell = row.getCell(column);
  763. if (StringUtils.isNotNull(cell)) {
  764. if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
  765. val = cell.getNumericCellValue();
  766. if (DateUtil.isCellDateFormatted(cell)) {
  767. val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
  768. } else {
  769. if ((Double) val % 1 != 0) {
  770. val = new BigDecimal(val.toString());
  771. } else {
  772. val = new DecimalFormat("0").format(val);
  773. }
  774. }
  775. } else if (cell.getCellTypeEnum() == CellType.STRING) {
  776. val = cell.getStringCellValue();
  777. } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
  778. val = cell.getBooleanCellValue();
  779. } else if (cell.getCellTypeEnum() == CellType.ERROR) {
  780. val = cell.getErrorCellValue();
  781. }
  782. }
  783. } catch (Exception e) {
  784. return val;
  785. }
  786. return val;
  787. }
  788. /**
  789. * 转换为字符串<br>
  790. * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
  791. * 转换失败不会报错
  792. *
  793. * @param value 被转换的值
  794. * @return 结果
  795. */
  796. public static String toStr(Object value) {
  797. return toStr(value, null);
  798. }
  799. /**
  800. * 转换为字符串<br>
  801. * 如果给定的值为null,或者转换失败,返回默认值<br>
  802. * 转换失败不会报错
  803. *
  804. * @param value 被转换的值
  805. * @param defaultValue 转换错误时的默认值
  806. * @return 结果
  807. */
  808. public static String toStr(Object value, String defaultValue) {
  809. if (null == value) {
  810. return defaultValue;
  811. }
  812. if (value instanceof String) {
  813. return (String) value;
  814. }
  815. return value.toString();
  816. }
  817. /**
  818. * 转换为double<br>
  819. * 如果给定的值为空,或者转换失败,返回默认值<code>null</code><br>
  820. * 转换失败不会报错
  821. *
  822. * @param value 被转换的值
  823. * @return 结果
  824. */
  825. public static Double toDouble(Object value) {
  826. return toDouble(value, null);
  827. }
  828. /**
  829. * 转换为double<br>
  830. * 如果给定的值为空,或者转换失败,返回默认值<br>
  831. * 转换失败不会报错
  832. *
  833. * @param value 被转换的值
  834. * @param defaultValue 转换错误时的默认值
  835. * @return 结果
  836. */
  837. public static Double toDouble(Object value, Double defaultValue) {
  838. if (value == null) {
  839. return defaultValue;
  840. }
  841. if (value instanceof Double) {
  842. return (Double) value;
  843. }
  844. if (value instanceof Number) {
  845. return ((Number) value).doubleValue();
  846. }
  847. final String valueStr = toStr(value, null);
  848. if (StringUtils.isEmpty(valueStr)) {
  849. return defaultValue;
  850. }
  851. try {
  852. // 支持科学计数法
  853. return new BigDecimal(valueStr.trim()).doubleValue();
  854. } catch (Exception e) {
  855. return defaultValue;
  856. }
  857. }
  858. /**
  859. * 转换为int<br>
  860. * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
  861. * 转换失败不会报错
  862. *
  863. * @param value 被转换的值
  864. * @return 结果
  865. */
  866. public static Integer toInt(Object value) {
  867. return toInt(value, null);
  868. }
  869. /**
  870. * 转换为int<br>
  871. * 如果给定的值为空,或者转换失败,返回默认值<br>
  872. * 转换失败不会报错
  873. *
  874. * @param value 被转换的值
  875. * @param defaultValue 转换错误时的默认值
  876. * @return 结果
  877. */
  878. public static Integer toInt(Object value, Integer defaultValue) {
  879. if (value == null) {
  880. return defaultValue;
  881. }
  882. if (value instanceof Integer) {
  883. return (Integer) value;
  884. }
  885. if (value instanceof Number) {
  886. return ((Number) value).intValue();
  887. }
  888. final String valueStr = toStr(value, null);
  889. if (StringUtils.isEmpty(valueStr)) {
  890. return defaultValue;
  891. }
  892. try {
  893. return Integer.parseInt(valueStr.trim());
  894. } catch (Exception e) {
  895. return defaultValue;
  896. }
  897. }
  898. /**
  899. * 获取文件类型
  900. *
  901. * @param photoByte 文件字节码
  902. * @return 后缀(不含".")
  903. */
  904. public static String getFileExtendName(byte[] photoByte) {
  905. String strFileExtendName = "JPG";
  906. if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
  907. && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97)) {
  908. strFileExtendName = "GIF";
  909. } else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70)) {
  910. strFileExtendName = "JPG";
  911. } else if ((photoByte[0] == 66) && (photoByte[1] == 77)) {
  912. strFileExtendName = "BMP";
  913. } else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71)) {
  914. strFileExtendName = "PNG";
  915. }
  916. return strFileExtendName;
  917. }
  918. public static byte[] getImage(String imagePath) {
  919. InputStream is = getFile(imagePath);
  920. try {
  921. return IOUtils.toByteArray(is);
  922. } catch (Exception e) {
  923. log.error("图片加载异常 {}", e);
  924. return null;
  925. } finally {
  926. IOUtils.closeQuietly(is);
  927. }
  928. }
  929. public static InputStream getFile(String imagePath) {
  930. try {
  931. byte[] result = readFile(imagePath);
  932. result = Arrays.copyOf(result, result.length);
  933. return new ByteArrayInputStream(result);
  934. } catch (Exception e) {
  935. log.error("获取图片异常 {}", e);
  936. }
  937. return null;
  938. }
  939. /**
  940. * 读取文件为字节数据
  941. *
  942. * @param key 地址
  943. * @return 字节数据
  944. */
  945. public static byte[] readFile(String url) {
  946. InputStream in = null;
  947. ByteArrayOutputStream baos = null;
  948. try {
  949. if (url.startsWith("http")) {
  950. // 网络地址
  951. URL urlObj = new URL(url);
  952. URLConnection urlConnection = urlObj.openConnection();
  953. urlConnection.setConnectTimeout(30 * 1000);
  954. urlConnection.setReadTimeout(60 * 1000);
  955. urlConnection.setDoInput(true);
  956. in = urlConnection.getInputStream();
  957. } else {
  958. // 本机地址(需修改)
  959. String localPath = "127.0.0.1";
  960. String downloadPath = localPath + StringUtils.substringAfter(url, "/profile");
  961. in = new FileInputStream(downloadPath);
  962. }
  963. return IOUtils.toByteArray(in);
  964. } catch (Exception e) {
  965. log.error("获取文件路径异常 {}", e);
  966. return null;
  967. } finally {
  968. IOUtils.closeQuietly(baos);
  969. }
  970. }
  971. }