NewExcelUtil.java 28 KB

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