NewExcelUtil.java 31 KB

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