NewExcelUtil.java 29 KB

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