NewExcelUtil.java 28 KB

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