NewExcelUtil.java 65 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000200120022003200420052006200720082009201020112012201320142015201620172018201920202021202220232024202520262027202820292030203120322033203420352036203720382039204020412042204320442045204620472048204920502051205220532054205520562057205820592060206120622063206420652066206720682069207020712072207320742075207620772078207920802081208220832084208520862087208820892090209120922093209420952096209720982099210021012102210321042105210621072108210921102111211221132114211521162117211821192120212121222123212421252126212721282129213021312132213321342135213621372138213921402141214221432144214521462147214821492150215121522153215421552156215721582159216021612162216321642165216621672168216921702171217221732174217521762177217821792180
  1. package com.goafanti.common.utils.excel;
  2. import com.goafanti.common.bo.Result;
  3. import com.goafanti.common.constant.AFTConstants;
  4. import com.goafanti.common.error.BusinessException;
  5. import com.goafanti.common.utils.DateUtils;
  6. import com.goafanti.common.utils.StringUtils;
  7. import com.goafanti.common.utils.excel.Excel.ColumnType;
  8. import com.goafanti.common.utils.excel.Excel.Type;
  9. import com.goafanti.techproject.bo.OutTaskDetailsBo;
  10. import com.goafanti.techproject.bo.TaskDetailsBo;
  11. import org.apache.poi.POIXMLDocumentPart;
  12. import org.apache.poi.hssf.usermodel.*;
  13. import org.apache.poi.ss.usermodel.*;
  14. import org.apache.poi.ss.util.CellRangeAddress;
  15. import org.apache.poi.ss.util.CellRangeAddressList;
  16. import org.apache.poi.util.IOUtils;
  17. import org.apache.poi.xssf.usermodel.*;
  18. import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
  19. import org.slf4j.Logger;
  20. import org.slf4j.LoggerFactory;
  21. import javax.servlet.http.HttpServletResponse;
  22. import java.io.*;
  23. import java.lang.reflect.Field;
  24. import java.lang.reflect.Method;
  25. import java.math.BigDecimal;
  26. import java.net.URL;
  27. import java.net.URLConnection;
  28. import java.nio.file.Files;
  29. import java.nio.file.Paths;
  30. import java.text.DecimalFormat;
  31. import java.time.LocalDate;
  32. import java.time.LocalDateTime;
  33. import java.util.*;
  34. import java.util.stream.Collectors;
  35. /**
  36. * Excel相关处理
  37. *
  38. *
  39. */
  40. public class NewExcelUtil<T> {
  41. private static final Logger log = LoggerFactory.getLogger(NewExcelUtil.class);
  42. /**
  43. * Excel sheet最大行数,默认65536
  44. */
  45. public static final int sheetSize = 65536;
  46. public String downloadPath = "";
  47. public String screen = "";
  48. /**
  49. * 工作表名称
  50. */
  51. private String sheetName;
  52. /**
  53. * 工作薄对象
  54. */
  55. private Workbook wb;
  56. /**
  57. * 导出类型(EXPORT:导出数据;IMPORT:导入模板)
  58. */
  59. private Type type;
  60. /**
  61. * 工作表对象
  62. */
  63. private Sheet sheet;
  64. /**
  65. * 样式列表
  66. */
  67. private Map<String, CellStyle> styles;
  68. /**
  69. * 导入导出数据列表
  70. */
  71. private List<T> list;
  72. /**
  73. * 注解列表
  74. */
  75. private List<Object[]> fields;
  76. /**
  77. * 标题
  78. */
  79. private String title;
  80. /**
  81. * 最大高度
  82. */
  83. private short maxHeight;
  84. /**
  85. * 统计列表
  86. */
  87. private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
  88. /**
  89. * 数字格式
  90. */
  91. private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
  92. /**
  93. * 实体对象
  94. */
  95. public Class<T> clazz;
  96. public NewExcelUtil(Class<T> clazz) {
  97. this.clazz = clazz;
  98. }
  99. public void init(List<T> list, String sheetName) {
  100. if (list == null) {
  101. list = new ArrayList<T>();
  102. }
  103. this.list = list;
  104. this.sheetName = sheetName;
  105. createExcelField();
  106. createWorkbook();
  107. }
  108. public void init(List<T> list, String sheetName, String title, Type type)
  109. {
  110. if (list == null)
  111. {
  112. list = new ArrayList<T>();
  113. }
  114. this.list = list;
  115. this.sheetName = sheetName;
  116. this.type = type;
  117. this.title = title;
  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 uploadPath 导出本地地址
  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. /**
  155. * 将数据合并按订单合并并导出CXCEL表到指定目录
  156. * @param list 导出数据集合
  157. * @param sheetName 工作表的名称
  158. * @param uploadPath 导出本地地址
  159. */
  160. public Result OrderMergeAndExportExcel(List<T> list, String sheetName, String uploadPath) {
  161. this.init(list, sheetName);
  162. this.downloadPath=uploadPath;
  163. return OrderMergeAndExportExcel();
  164. }
  165. private Result OrderMergeAndExportExcel() {
  166. OutputStream out = null;
  167. String filename="";
  168. try {
  169. // 取出一共有多少个sheet.
  170. int sheetNo = (list.size() / sheetSize);
  171. for (int index = 0; index <= sheetNo; index++) {
  172. createSheet(sheetNo, index);
  173. int line=0;
  174. // 产生一行
  175. Row title = sheet.createRow(line);
  176. Cell cell = title.createCell(0);
  177. cell.setCellValue(sheetName);
  178. CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
  179. sheet.addMergedRegion(region);
  180. cell.setCellStyle(styles.get("total"));
  181. if (StringUtils.isNotEmpty(screen)){
  182. log.debug(screen);
  183. Row r = sheet.createRow(++line);
  184. Cell c = r.createCell(0);
  185. c.setCellValue(screen);
  186. CellRangeAddress rg = new CellRangeAddress(line, line, 0, fields.size()-1);
  187. sheet.addMergedRegion(rg);
  188. c.setCellStyle(styles.get("header"));
  189. }
  190. Row row = sheet.createRow(++line);
  191. // 产生一行
  192. int column = 0;
  193. // 写入各个字段的列头名称
  194. for (Object[] os : fields) {
  195. Excel excel = (Excel) os[1];
  196. this.createCell(excel, row, column++);
  197. }
  198. filename = encodingFilename(sheetName);
  199. out = Files.newOutputStream(Paths.get(getAbsoluteFile(filename)));
  200. fillMergeExcelData(index,++line);
  201. //新增合并
  202. // mergeOrderNo();
  203. addStatisticsRow();
  204. }
  205. wb.write(out);
  206. return new Result().data(filename);
  207. } catch (Exception e) {
  208. e.printStackTrace();
  209. throw new BusinessException("导出Excel失败,请联系网站管理员!");
  210. } finally {
  211. if (wb != null) {
  212. try {
  213. wb.close();
  214. } catch (IOException e1) {
  215. e1.printStackTrace();
  216. }
  217. }
  218. if (out != null) {
  219. try {
  220. out.close();
  221. } catch (IOException e1) {
  222. e1.printStackTrace();
  223. }
  224. }
  225. }
  226. }
  227. private void fillMergeExcelData(int index, Integer line) throws Exception {
  228. int startNo = index * sheetSize;
  229. int endNo = Math.min(startNo + sheetSize, list.size());
  230. if(line==null)line=2;
  231. for (int i = startNo; i < endNo; i++) {
  232. int start=i + line - startNo;
  233. Row row = sheet.createRow(start);
  234. // 得到导出对象.
  235. T vo = (T) list.get(i);
  236. String orderNo = (String) getValue(vo, "orderNo");
  237. int x = i+1;
  238. int count =0;
  239. while (x<endNo){
  240. T t1 = list.get(x);
  241. String orderNo1 = (String) getValue(t1, "orderNo");
  242. if (orderNo.equals(orderNo1)){
  243. count++;
  244. x++;
  245. }else{
  246. break;
  247. }
  248. }
  249. if(count>0){
  250. int column = 0;
  251. for (Object[] os : fields) {
  252. Field field = (Field) os[0];
  253. Excel excel = (Excel) os[1];
  254. // 设置实体类私有属性可访问
  255. field.setAccessible(true);
  256. if (column<35){
  257. this.addOrderMegerCell(excel, row, vo, field, column);
  258. CellRangeAddress region = new CellRangeAddress(start, start+count, column, column);
  259. sheet.addMergedRegion(region);
  260. Row row1 = sheet.getRow(start);
  261. Cell cell1 = row1.getCell(column);
  262. CellStyle style = styles.get("data4");
  263. cell1.setCellStyle(style);
  264. column++;
  265. }else {
  266. this.addOrderMegerCell(excel, row, vo, field, column++);
  267. }
  268. }
  269. for (int j = 1; j <= count; j++) {
  270. T vo3 = (T) list.get(i+j);
  271. Row row3 = sheet.createRow(start+j);
  272. int column3 = 0;
  273. for (Object[] os : fields) {
  274. Field field = (Field) os[0];
  275. Excel excel = (Excel) os[1];
  276. // 设置实体类私有属性可访问
  277. field.setAccessible(true);
  278. if (column3>34){
  279. this.addOrderMegerCell(excel, row3, vo3, field, column3++);
  280. }else {
  281. column3++;
  282. }
  283. }
  284. }
  285. i=i+count;
  286. }else {
  287. int column = 0;
  288. for (Object[] os : fields) {
  289. Field field = (Field) os[0];
  290. Excel excel = (Excel) os[1];
  291. // 设置实体类私有属性可访问
  292. field.setAccessible(true);
  293. Cell cell = this.addCell(excel, row, vo, field, column++);
  294. cell.setCellStyle(styles.get("data4"));
  295. }
  296. }
  297. }
  298. }
  299. private void mergeOrderNo() throws Exception {
  300. int max = list.size();
  301. for (int i = 0; i < max; i++) {
  302. T t = list.get(i);
  303. String orderNo = (String) getValue(t, "orderNo");
  304. int count =0;
  305. if (i!=max-1){
  306. int x = i+1;
  307. while (x<max){
  308. T t1 = list.get(x);
  309. String orderNo1 = (String) getValue(t1, "orderNo");
  310. if (orderNo.equals(orderNo1)){
  311. count++;
  312. x++;
  313. }else{
  314. break;
  315. }
  316. }
  317. if (count>0&&orderNo.equals("1015315879283802112")){
  318. int rowNum = i+2;
  319. int rowNum1 = x+1;
  320. Row row = sheet.getRow(rowNum);
  321. Row row1 = sheet.getRow(rowNum1);
  322. System.out.println("orderNo="+orderNo+",count="+count+",rowNum="+rowNum+",rowNum1="+rowNum1);
  323. int index=0;
  324. for (Object[] e : fields) {
  325. Field field = (Field) e[0];
  326. Excel attr = (Excel) e[1];
  327. // 设置实体类私有属性可访问
  328. field.setAccessible(true);
  329. Object value = getTargetValue(t, field, attr);
  330. Cell cell = row.createCell(index);
  331. String dateFormat = attr.dateFormat();
  332. String readConverterExp = attr.readConverterExp();
  333. String separator = attr.separator();
  334. String enumsName = attr.enumsName();
  335. String methodName="getValueByCode";
  336. if (StringUtils.isNotBlank(enumsName)&& StringUtils.isNotNull(value)) {
  337. Class<?> clazz=Class.forName(enumsName);
  338. Method method=clazz.getDeclaredMethod(methodName,Integer.class);
  339. Object s=method.invoke(clazz,value);
  340. setCellVo(s.toString(), attr, cell);
  341. }else if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
  342. value=DateUtils.parseDateToStr(dateFormat, (Date) value);
  343. } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
  344. value=convertByExp(toStr(value), readConverterExp, separator);
  345. } else if (value instanceof BigDecimal && -1 != attr.scale()) {
  346. value=(((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString();
  347. } else{
  348. // 设置列类型
  349. setCellVo(value, attr, cell);
  350. }
  351. if (value==null)value="";
  352. cell.setCellValue(value.toString());
  353. CellRangeAddress region = new CellRangeAddress(rowNum, rowNum1, index, index);
  354. sheet.addMergedRegion(region);
  355. index++;
  356. }
  357. }
  358. }
  359. }
  360. // CellRangeAddress region = new CellRangeAddress(4, 5, 0, fields.size()-1);
  361. // sheet.addMergedRegion(region);
  362. }
  363. /**
  364. * 直接导出CXCEL表
  365. * @param list 导出数据集合
  366. * @param sheetName 工作表的名称
  367. * @param uploadPath 导出本地地址
  368. * @param screen 搜索条件
  369. */
  370. public Result exportExcel(List<T> list, String sheetName, String uploadPath,String screen) {
  371. this.init(list, sheetName);
  372. this.downloadPath=uploadPath;
  373. this.screen=screen;
  374. return exportExcel();
  375. }
  376. private void exportExcel(HttpServletResponse response) {
  377. try {
  378. OutputStream out = response.getOutputStream();
  379. // 取出一共有多少个sheet.
  380. int sheetNo = (int) Math.ceil(list.size() / sheetSize);
  381. String dateFilename =DateFilename(sheetName);
  382. for (int index = 0; index <= sheetNo; index++) {
  383. Integer line=0;
  384. createSheet(sheetNo, index);
  385. // 产生一行
  386. Row title = sheet.createRow(line);
  387. Cell cell = title.createCell(0);
  388. cell.setCellValue(sheetName);
  389. CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
  390. sheet.addMergedRegion(region);
  391. cell.setCellStyle(styles.get("total"));
  392. if (StringUtils.isNotEmpty(screen)){
  393. log.debug(screen);
  394. Row r = sheet.createRow(++line);
  395. Cell c = r.createCell(0);
  396. c.setCellValue(screen);
  397. CellRangeAddress rg = new CellRangeAddress(line, line, 0, fields.size()-1);
  398. sheet.addMergedRegion(rg);
  399. c.setCellStyle(styles.get("header"));
  400. }
  401. Row row = sheet.createRow(++line);
  402. int column = 0;
  403. // 写入各个字段的列头名称
  404. for (Object[] os : fields) {
  405. Excel excel = (Excel) os[1];
  406. this.createCell(excel, row, column++);
  407. }
  408. fillExcelData(index, row,++line);
  409. addStatisticsRow();
  410. }
  411. response.addHeader("Content-Disposition", "attachment;filename=" + new String(dateFilename.getBytes(),"iso-8859-1"));
  412. response.setContentType("application/octet-stream;charset=utf-8");
  413. wb.write(out);
  414. out.flush();
  415. out.close();
  416. } catch (Exception e) {
  417. e.printStackTrace();
  418. throw new BusinessException("导出Excel失败,请联系网站管理员!");
  419. } finally {
  420. if (wb != null) {
  421. try {
  422. wb.close();
  423. } catch (IOException e1) {
  424. e1.printStackTrace();
  425. }
  426. }
  427. }
  428. }
  429. private String DateFilename(String sheetName) {
  430. sheetName = sheetName + "_" + DateUtils.formatDate(new Date(), AFTConstants.YYYYMMDDHHMMSS)+".xlsx";
  431. return sheetName;
  432. }
  433. /**
  434. * 对list数据源将其里面的数据导入到excel表单
  435. *
  436. * @param sheetName 工作表的名称
  437. * @return 结果
  438. */
  439. public Result importTemplateExcel(String sheetName) {
  440. this.init(null, sheetName);
  441. return exportExcel();
  442. }
  443. /**
  444. * 对list数据源将其里面的数据导入到excel表单
  445. *
  446. * @return 结果
  447. */
  448. public Result exportExcel() {
  449. OutputStream out = null;
  450. String filename="";
  451. try {
  452. // 取出一共有多少个sheet.
  453. int sheetNo = (int) Math.ceil(list.size() / sheetSize);
  454. for (int index = 0; index <= sheetNo; index++) {
  455. createSheet(sheetNo, index);
  456. int line=0;
  457. // 产生一行
  458. Row title = sheet.createRow(line);
  459. Cell cell = title.createCell(0);
  460. cell.setCellValue(sheetName);
  461. CellRangeAddress region = new CellRangeAddress(0, 0, 0, fields.size()-1);
  462. sheet.addMergedRegion(region);
  463. cell.setCellStyle(styles.get("total"));
  464. if (StringUtils.isNotEmpty(screen)){
  465. log.debug(screen);
  466. Row r = sheet.createRow(++line);
  467. Cell c = r.createCell(0);
  468. c.setCellValue(screen);
  469. CellRangeAddress rg = new CellRangeAddress(line, line, 0, fields.size()-1);
  470. sheet.addMergedRegion(rg);
  471. c.setCellStyle(styles.get("header"));
  472. }
  473. Row row = sheet.createRow(++line);
  474. // 产生一行
  475. int column = 0;
  476. // 写入各个字段的列头名称
  477. for (Object[] os : fields) {
  478. Excel excel = (Excel) os[1];
  479. this.createCell(excel, row, column++);
  480. }
  481. filename = encodingFilename(sheetName);
  482. out = new FileOutputStream(getAbsoluteFile(filename));
  483. fillExcelData(index, row,++line);
  484. addStatisticsRow();
  485. }
  486. wb.write(out);
  487. return new Result().data(filename);
  488. } catch (Exception e) {
  489. e.printStackTrace();
  490. throw new BusinessException("导出Excel失败,请联系网站管理员!");
  491. } finally {
  492. if (wb != null) {
  493. try {
  494. wb.close();
  495. } catch (IOException e1) {
  496. e1.printStackTrace();
  497. }
  498. }
  499. if (out != null) {
  500. try {
  501. out.close();
  502. } catch (IOException e1) {
  503. e1.printStackTrace();
  504. }
  505. }
  506. }
  507. }
  508. /**
  509. * 填充excel数据
  510. *
  511. * @param index 序号
  512. * @param row 单元格行
  513. */
  514. public void fillExcelData(int index, Row row,Integer line) {
  515. int startNo = index * sheetSize;
  516. int endNo = Math.min(startNo + sheetSize, list.size());
  517. if(line==null)line=2;
  518. for (int i = startNo; i < endNo; i++) {
  519. row = sheet.createRow(i + line - startNo);
  520. // 得到导出对象.
  521. T vo = (T) list.get(i);
  522. int column = 0;
  523. for (Object[] os : fields) {
  524. Field field = (Field) os[0];
  525. Excel excel = (Excel) os[1];
  526. // 设置实体类私有属性可访问
  527. field.setAccessible(true);
  528. this.addCell(excel, row, vo, field, column++);
  529. }
  530. }
  531. }
  532. /**
  533. * 创建表格样式
  534. *
  535. * @param wb 工作薄对象
  536. * @return 样式列表
  537. */
  538. private Map<String, CellStyle> createStyles(Workbook wb) {
  539. // 写入各条记录,每条记录对应excel表中的一行
  540. Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  541. //
  542. CellStyle style = wb.createCellStyle();
  543. style.setAlignment(HorizontalAlignment.CENTER);
  544. style.setVerticalAlignment(VerticalAlignment.CENTER);
  545. style.setBorderRight(BorderStyle.THIN);
  546. style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  547. style.setBorderLeft(BorderStyle.THIN);
  548. style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  549. style.setBorderTop(BorderStyle.THIN);
  550. style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  551. style.setBorderBottom(BorderStyle.THIN);
  552. style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  553. Font dataFont = wb.createFont();
  554. dataFont.setFontName("Arial");
  555. dataFont.setFontHeightInPoints((short) 10);
  556. style.setFont(dataFont);
  557. styles.put("data", style);
  558. style = wb.createCellStyle();
  559. style.cloneStyleFrom(styles.get("data"));
  560. style.setAlignment(HorizontalAlignment.CENTER);
  561. style.setVerticalAlignment(VerticalAlignment.CENTER);
  562. style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  563. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  564. Font headerFont = wb.createFont();
  565. headerFont.setFontName("Arial");
  566. headerFont.setFontHeightInPoints((short) 10);
  567. headerFont.setBold(true);
  568. headerFont.setColor(IndexedColors.WHITE.getIndex());
  569. style.setFont(headerFont);
  570. styles.put("header", style);
  571. style = wb.createCellStyle();
  572. style.cloneStyleFrom(styles.get("data"));
  573. style.setAlignment(HorizontalAlignment.CENTER);
  574. style.setVerticalAlignment(VerticalAlignment.CENTER);
  575. style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
  576. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  577. Font totalFont = wb.createFont();
  578. totalFont.setFontName("Arial");
  579. totalFont.setFontHeightInPoints((short) 10);
  580. totalFont.setColor(IndexedColors.WHITE.getIndex());
  581. style.setFont(totalFont);
  582. styles.put("total", style);
  583. style = wb.createCellStyle();
  584. style.cloneStyleFrom(styles.get("data"));
  585. style.setAlignment(HorizontalAlignment.LEFT);
  586. styles.put("data1", style);
  587. style = wb.createCellStyle();
  588. style.cloneStyleFrom(styles.get("data"));
  589. style.setAlignment(HorizontalAlignment.CENTER);
  590. styles.put("data2", style);
  591. style = wb.createCellStyle();
  592. style.cloneStyleFrom(styles.get("data"));
  593. style.setAlignment(HorizontalAlignment.RIGHT);
  594. styles.put("data3", style);
  595. style = wb.createCellStyle();
  596. style.cloneStyleFrom(styles.get("data"));
  597. style.setAlignment(HorizontalAlignment.CENTER);
  598. style.setVerticalAlignment(VerticalAlignment.CENTER);
  599. style.setBorderRight(BorderStyle.THIN);
  600. style.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
  601. style.setBorderLeft(BorderStyle.THIN);
  602. style.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
  603. style.setBorderTop(BorderStyle.THIN);
  604. style.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
  605. style.setBorderBottom(BorderStyle.THIN);
  606. style.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
  607. styles.put("data4", style);
  608. return styles;
  609. }
  610. /**
  611. * 创建单元格
  612. */
  613. public Cell createCell(Excel attr, Row row, int column) {
  614. // 创建列
  615. Cell cell = row.createCell(column);
  616. // 写入列信息
  617. cell.setCellValue(attr.name());
  618. setDataValidation(attr, row, column);
  619. cell.setCellStyle(styles.get("header"));
  620. return cell;
  621. }
  622. /**
  623. * 创建单元格
  624. */
  625. public Cell createTotal(String str, Row row, int column) {
  626. // 创建列
  627. Cell cell = row.createCell(column);
  628. // 写入列信息
  629. cell.setCellValue(str);
  630. sheet.setColumnWidth(column, (int) ((14 + 0.72) * 256));
  631. cell.setCellStyle(styles.get("header"));
  632. return cell;
  633. }
  634. /**
  635. * 设置单元格信息
  636. *
  637. * @param value 单元格值
  638. * @param attr 注解相关
  639. * @param cell 单元格信息
  640. */
  641. public void setCellVo(Object value, Excel attr, Cell cell) {
  642. if (ColumnType.STRING == attr.cellType()) {
  643. if (attr.percentage()==1){
  644. if (value!=null){
  645. BigDecimal v=new BigDecimal(value.toString());
  646. v=v.multiply(new BigDecimal(100));
  647. if (attr.scale()>0){
  648. v=v.setScale(attr.scale(),BigDecimal.ROUND_HALF_EVEN);
  649. }
  650. value=v.stripTrailingZeros().toPlainString()+"%";
  651. }else {
  652. value="0%";
  653. }
  654. }
  655. cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
  656. } else if (ColumnType.NUMERIC == attr.cellType()) {
  657. cell.setCellValue(StringUtils.contains(toStr(value), ".") ? toDouble(value) : toInt(value));
  658. } else if (ColumnType.IMAGE == attr.cellType()) {
  659. ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(),
  660. cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
  661. String imagePath = toStr(value);
  662. if (StringUtils.isNotEmpty(imagePath)) {
  663. byte[] data = getImage(imagePath);
  664. getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
  665. cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
  666. }
  667. }
  668. }
  669. /**
  670. * 获取画布
  671. */
  672. public static Drawing<?> getDrawingPatriarch(Sheet sheet) {
  673. if (sheet.getDrawingPatriarch() == null) {
  674. sheet.createDrawingPatriarch();
  675. }
  676. return sheet.getDrawingPatriarch();
  677. }
  678. /**
  679. * 获取图片类型,设置图片插入类型
  680. */
  681. public int getImageType(byte[] value) {
  682. String type = getFileExtendName(value);
  683. if ("JPG".equalsIgnoreCase(type)) {
  684. return Workbook.PICTURE_TYPE_JPEG;
  685. } else if ("PNG".equalsIgnoreCase(type)) {
  686. return Workbook.PICTURE_TYPE_PNG;
  687. }
  688. return Workbook.PICTURE_TYPE_JPEG;
  689. }
  690. /**
  691. * 创建表格样式
  692. */
  693. public void setDataValidation(Excel attr, Row row, int column) {
  694. if (attr.name().indexOf("注:") >= 0) {
  695. sheet.setColumnWidth(column, 6000);
  696. } else {
  697. // 设置列宽
  698. sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
  699. }
  700. // 如果设置了提示信息则鼠标放上去提示.
  701. if (StringUtils.isNotEmpty(attr.prompt())) {
  702. // 这里默认设了2-101列提示.
  703. setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
  704. }
  705. // 如果设置了combo属性则本列只能选择不能输入
  706. if (attr.combo().length > 0) {
  707. // 这里默认设了2-101列只能选择不能输入.
  708. setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
  709. }
  710. }
  711. /**
  712. * 添加单元格
  713. */
  714. @SuppressWarnings("deprecation")
  715. public Cell addCell(Excel attr, Row row, T vo, Field field, int column) {
  716. Cell cell = null;
  717. try {
  718. // 设置行高
  719. row.setHeight(maxHeight);
  720. // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  721. if (attr.isExport()) {
  722. // 创建cell
  723. cell = row.createCell(column);
  724. int align = attr.align().value();
  725. cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
  726. // 用于读取对象中的属性
  727. Object value = getTargetValue(vo, field, attr);
  728. String dateFormat = attr.dateFormat();
  729. String readConverterExp = attr.readConverterExp();
  730. String separator = attr.separator();
  731. String enumsName = attr.enumsName();
  732. String methodName="getValueByCode";
  733. if (StringUtils.isNotBlank(enumsName)&& StringUtils.isNotNull(value)) {
  734. Class<?> clazz=Class.forName(enumsName);
  735. Object[] os=clazz.getEnumConstants();
  736. Method method=clazz.getDeclaredMethod(methodName,Integer.class);
  737. Object s=method.invoke(clazz,value);
  738. setCellVo(s.toString(), attr, cell);
  739. }else if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
  740. cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
  741. } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
  742. cell.setCellValue(convertByExp(toStr(value), readConverterExp, separator));
  743. } else if (value instanceof BigDecimal && -1 != attr.scale()) {
  744. cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
  745. } else{
  746. // 设置列类型
  747. setCellVo(value, attr, cell);
  748. }
  749. addStatisticsData(column, toStr(value), attr);
  750. }
  751. } catch (Exception e) {
  752. log.error("导出Excel失败{}", e);
  753. }
  754. return cell;
  755. }
  756. public Cell addOrderMegerCell(Excel attr, Row row, T vo, Field field, int column) {
  757. Cell cell = null;
  758. try {
  759. // 设置行高
  760. row.setHeight(maxHeight);
  761. // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  762. if (attr.isExport()) {
  763. // 创建cell
  764. cell = row.createCell(column);
  765. int align = attr.align().value();
  766. cell.setCellStyle(styles.get("data4"));
  767. // 用于读取对象中的属性
  768. Object value = getTargetValue(vo, field, attr);
  769. String dateFormat = attr.dateFormat();
  770. String readConverterExp = attr.readConverterExp();
  771. String separator = attr.separator();
  772. String enumsName = attr.enumsName();
  773. String methodName="getValueByCode";
  774. if (StringUtils.isNotBlank(enumsName)&& StringUtils.isNotNull(value)) {
  775. Class<?> clazz=Class.forName(enumsName);
  776. Object[] os=clazz.getEnumConstants();
  777. Method method=clazz.getDeclaredMethod(methodName,Integer.class);
  778. Object s=method.invoke(clazz,value);
  779. setCellVo(s.toString(), attr, cell);
  780. }else if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value)) {
  781. cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
  782. } else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value)) {
  783. cell.setCellValue(convertByExp(toStr(value), readConverterExp, separator));
  784. } else if (value instanceof BigDecimal && -1 != attr.scale()) {
  785. cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).stripTrailingZeros().toPlainString());
  786. } else{
  787. // 设置列类型
  788. setCellVo(value, attr, cell);
  789. }
  790. addStatisticsData(column, toStr(value), attr);
  791. }
  792. } catch (Exception e) {
  793. log.error("导出Excel失败{}", e);
  794. }
  795. return cell;
  796. }
  797. /**
  798. * 设置 POI XSSFSheet 单元格提示
  799. *
  800. * @param sheet 表单
  801. * @param promptTitle 提示标题
  802. * @param promptContent 提示内容
  803. * @param firstRow 开始行
  804. * @param endRow 结束行
  805. * @param firstCol 开始列
  806. * @param endCol 结束列
  807. */
  808. public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
  809. int firstCol, int endCol) {
  810. DataValidationHelper helper = sheet.getDataValidationHelper();
  811. DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
  812. CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  813. DataValidation dataValidation = helper.createValidation(constraint, regions);
  814. dataValidation.createPromptBox(promptTitle, promptContent);
  815. dataValidation.setShowPromptBox(true);
  816. sheet.addValidationData(dataValidation);
  817. }
  818. /**
  819. * 设置某些列的值只能输入预制的数据,显示下拉框.
  820. *
  821. * @param sheet 要设置的sheet.
  822. * @param textlist 下拉框显示的内容
  823. * @param firstRow 开始行
  824. * @param endRow 结束行
  825. * @param firstCol 开始列
  826. * @param endCol 结束列
  827. * @return 设置好的sheet.
  828. */
  829. public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
  830. DataValidationHelper helper = sheet.getDataValidationHelper();
  831. // 加载下拉列表内容
  832. DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
  833. // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
  834. CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  835. // 数据有效性对象
  836. DataValidation dataValidation = helper.createValidation(constraint, regions);
  837. // 处理Excel兼容性问题
  838. if (dataValidation instanceof XSSFDataValidation) {
  839. dataValidation.setSuppressDropDownArrow(true);
  840. dataValidation.setShowErrorBox(true);
  841. } else {
  842. dataValidation.setSuppressDropDownArrow(false);
  843. }
  844. sheet.addValidationData(dataValidation);
  845. }
  846. /**
  847. * 解析导出值 0=男,1=女,2=未知
  848. *
  849. * @param propertyValue 参数值
  850. * @param converterExp 翻译注解
  851. * @param separator 分隔符
  852. * @return 解析后值
  853. */
  854. public static String convertByExp(String propertyValue, String converterExp, String separator) {
  855. StringBuilder propertyString = new StringBuilder();
  856. String[] convertSource = converterExp.split(",");
  857. for (String item : convertSource) {
  858. String[] itemArray = item.split("=");
  859. if (StringUtils.containsAny(separator, propertyValue)) {
  860. for (String value : propertyValue.split(separator)) {
  861. if (itemArray[0].equals(value)) {
  862. propertyString.append(itemArray[1] + separator);
  863. break;
  864. }
  865. }
  866. } else {
  867. if (itemArray[0].equals(propertyValue)) {
  868. return itemArray[1];
  869. }
  870. }
  871. }
  872. return StringUtils.stripEnd(propertyString.toString(), separator);
  873. }
  874. /**
  875. * 反向解析值 男=0,女=1,未知=2
  876. *
  877. * @param propertyValue 参数值
  878. * @param converterExp 翻译注解
  879. * @param separator 分隔符
  880. * @return 解析后值
  881. */
  882. public static String reverseByExp(String propertyValue, String converterExp, String separator) {
  883. StringBuilder propertyString = new StringBuilder();
  884. String[] convertSource = converterExp.split(",");
  885. for (String item : convertSource) {
  886. String[] itemArray = item.split("=");
  887. if (StringUtils.containsAny(separator, propertyValue)) {
  888. for (String value : propertyValue.split(separator)) {
  889. if (itemArray[1].equals(value)) {
  890. propertyString.append(itemArray[0] + separator);
  891. break;
  892. }
  893. }
  894. } else {
  895. if (itemArray[1].equals(propertyValue)) {
  896. return itemArray[0];
  897. }
  898. }
  899. }
  900. return StringUtils.stripEnd(propertyString.toString(), separator);
  901. }
  902. /**
  903. * 合计统计信息
  904. */
  905. private void addStatisticsData(Integer index, String text, Excel entity) {
  906. if (entity != null && entity.isStatistics()) {
  907. Double temp = 0D;
  908. if (!statistics.containsKey(index)) {
  909. statistics.put(index, temp);
  910. }
  911. try {
  912. temp = Double.valueOf(text);
  913. } catch (NumberFormatException e) {
  914. }
  915. statistics.put(index, statistics.get(index) + temp);
  916. }
  917. }
  918. /**
  919. * 创建统计行
  920. */
  921. public void addStatisticsRow() {
  922. if (statistics.size() > 0) {
  923. Cell cell = null;
  924. Row row = sheet.createRow(sheet.getLastRowNum() + 1);
  925. Set<Integer> keys = statistics.keySet();
  926. cell = row.createCell(0);
  927. cell.setCellStyle(styles.get("total"));
  928. cell.setCellValue("合计");
  929. for (Integer key : keys) {
  930. cell = row.createCell(key);
  931. cell.setCellStyle(styles.get("total"));
  932. cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
  933. }
  934. statistics.clear();
  935. }
  936. }
  937. /**
  938. * 编码文件名
  939. */
  940. public String encodingFilename(String filename) {
  941. filename = filename + "_" +UUID.randomUUID().toString() + ".xlsx";
  942. return filename;
  943. }
  944. /**
  945. * 获取下载路径
  946. *
  947. * @param filename 文件名称
  948. */
  949. public String getAbsoluteFile(String filename) {
  950. String downloadPath = this.downloadPath+"/" + filename;
  951. File desc = new File(downloadPath);
  952. if (!desc.getParentFile().exists()) {
  953. desc.getParentFile().mkdirs();
  954. }
  955. log.debug(downloadPath);
  956. return downloadPath;
  957. }
  958. /**
  959. * 获取bean中的属性值
  960. *
  961. * @param vo 实体对象
  962. * @param field 字段
  963. * @param excel 注解
  964. * @return 最终的属性值
  965. * @throws Exception
  966. */
  967. private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
  968. Object o = field.get(vo);
  969. if (StringUtils.isNotEmpty(excel.targetAttr())) {
  970. String target = excel.targetAttr();
  971. if (target.indexOf(".") > -1) {
  972. String[] targets = target.split("[.]");
  973. for (String name : targets) {
  974. o = getValue(o, name);
  975. }
  976. } else {
  977. o = getValue(o, target);
  978. }
  979. }
  980. return o;
  981. }
  982. /**
  983. * 以类的属性的get方法方法形式获取值
  984. *
  985. * @param o
  986. * @param name
  987. * @return value
  988. * @throws Exception
  989. */
  990. private Object getValue(Object o, String name) throws Exception {
  991. if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name)) {
  992. Class<?> clazz = o.getClass();
  993. Field field = clazz.getDeclaredField(name);
  994. field.setAccessible(true);
  995. o = field.get(o);
  996. }
  997. return o;
  998. }
  999. /**
  1000. * 得到所有定义字段
  1001. */
  1002. private void createExcelField() {
  1003. this.fields = new ArrayList<Object[]>();
  1004. List<Field> tempFields = new ArrayList<>();
  1005. tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  1006. tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  1007. for (Field field : tempFields) {
  1008. // 单注解
  1009. if (field.isAnnotationPresent(Excel.class)) {
  1010. putToField(field, field.getAnnotation(Excel.class));
  1011. }
  1012. // 多注解
  1013. if (field.isAnnotationPresent(Excels.class)) {
  1014. Excels attrs = field.getAnnotation(Excels.class);
  1015. Excel[] excels = attrs.value();
  1016. for (Excel excel : excels) {
  1017. putToField(field, excel);
  1018. }
  1019. }
  1020. }
  1021. this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort()))
  1022. .collect(Collectors.toList());
  1023. this.maxHeight = getRowHeight();
  1024. }
  1025. /**
  1026. * 根据注解获取最大行高
  1027. */
  1028. public short getRowHeight() {
  1029. double maxHeight = 0;
  1030. for (Object[] os : this.fields) {
  1031. Excel excel = (Excel) os[1];
  1032. maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
  1033. }
  1034. return (short) (maxHeight * 20);
  1035. }
  1036. /**
  1037. * 放到字段集合中
  1038. */
  1039. private void putToField(Field field, Excel attr) {
  1040. if (attr != null) {
  1041. this.fields.add(new Object[] { field, attr });
  1042. }
  1043. }
  1044. /**
  1045. * 创建一个工作簿
  1046. */
  1047. public void createWorkbook() {
  1048. this.wb = new XSSFWorkbook();
  1049. }
  1050. /**
  1051. * 创建工作表
  1052. *
  1053. * @param sheetNo sheet数量
  1054. * @param index 序号
  1055. */
  1056. public void createSheet(int sheetNo, int index) {
  1057. this.sheet = wb.createSheet();
  1058. this.styles = createStyles(wb);
  1059. // 设置工作表的名称.
  1060. if (sheetNo == 0) {
  1061. wb.setSheetName(index, sheetName);
  1062. } else {
  1063. wb.setSheetName(index, sheetName + index);
  1064. }
  1065. }
  1066. /**
  1067. * 获取单元格值
  1068. *
  1069. * @param row 获取的行
  1070. * @param column 获取单元格列号
  1071. * @return 单元格值
  1072. */
  1073. public Object getCellValue(Row row, int column) {
  1074. if (row == null) {
  1075. return row;
  1076. }
  1077. Object val = "";
  1078. try {
  1079. Cell cell = row.getCell(column);
  1080. if (StringUtils.isNotNull(cell)) {
  1081. if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) {
  1082. val = cell.getNumericCellValue();
  1083. if (DateUtil.isCellDateFormatted(cell)) {
  1084. val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
  1085. } else {
  1086. if ((Double) val % 1 != 0) {
  1087. val = new BigDecimal(val.toString());
  1088. } else {
  1089. val = new DecimalFormat("0").format(val);
  1090. }
  1091. }
  1092. } else if (cell.getCellTypeEnum() == CellType.STRING) {
  1093. val = cell.getStringCellValue();
  1094. } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
  1095. val = cell.getBooleanCellValue();
  1096. } else if (cell.getCellTypeEnum() == CellType.ERROR) {
  1097. val = cell.getErrorCellValue();
  1098. }
  1099. }
  1100. } catch (Exception e) {
  1101. return val;
  1102. }
  1103. return val;
  1104. }
  1105. /**
  1106. * 转换为字符串<br>
  1107. * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
  1108. * 转换失败不会报错
  1109. *
  1110. * @param value 被转换的值
  1111. * @return 结果
  1112. */
  1113. public static String toStr(Object value) {
  1114. return toStr(value, null);
  1115. }
  1116. /**
  1117. * 转换为字符串<br>
  1118. * 如果给定的值为null,或者转换失败,返回默认值<br>
  1119. * 转换失败不会报错
  1120. *
  1121. * @param value 被转换的值
  1122. * @param defaultValue 转换错误时的默认值
  1123. * @return 结果
  1124. */
  1125. public static String toStr(Object value, String defaultValue) {
  1126. if (null == value) {
  1127. return defaultValue;
  1128. }
  1129. if (value instanceof String) {
  1130. return (String) value;
  1131. }
  1132. return value.toString();
  1133. }
  1134. /**
  1135. * 转换为double<br>
  1136. * 如果给定的值为空,或者转换失败,返回默认值<code>null</code><br>
  1137. * 转换失败不会报错
  1138. *
  1139. * @param value 被转换的值
  1140. * @return 结果
  1141. */
  1142. public static Double toDouble(Object value) {
  1143. return toDouble(value, null);
  1144. }
  1145. /**
  1146. * 转换为double<br>
  1147. * 如果给定的值为空,或者转换失败,返回默认值<br>
  1148. * 转换失败不会报错
  1149. *
  1150. * @param value 被转换的值
  1151. * @param defaultValue 转换错误时的默认值
  1152. * @return 结果
  1153. */
  1154. public static Double toDouble(Object value, Double defaultValue) {
  1155. if (value == null) {
  1156. return defaultValue;
  1157. }
  1158. if (value instanceof Double) {
  1159. return (Double) value;
  1160. }
  1161. if (value instanceof Number) {
  1162. return ((Number) value).doubleValue();
  1163. }
  1164. final String valueStr = toStr(value, null);
  1165. if (StringUtils.isEmpty(valueStr)) {
  1166. return defaultValue;
  1167. }
  1168. try {
  1169. // 支持科学计数法
  1170. return new BigDecimal(valueStr.trim()).doubleValue();
  1171. } catch (Exception e) {
  1172. return defaultValue;
  1173. }
  1174. }
  1175. /**
  1176. * 转换为int<br>
  1177. * 如果给定的值为<code>null</code>,或者转换失败,返回默认值<code>null</code><br>
  1178. * 转换失败不会报错
  1179. *
  1180. * @param value 被转换的值
  1181. * @return 结果
  1182. */
  1183. public static Integer toInt(Object value) {
  1184. return toInt(value, null);
  1185. }
  1186. /**
  1187. * 转换为int<br>
  1188. * 如果给定的值为空,或者转换失败,返回默认值<br>
  1189. * 转换失败不会报错
  1190. *
  1191. * @param value 被转换的值
  1192. * @param defaultValue 转换错误时的默认值
  1193. * @return 结果
  1194. */
  1195. public static Integer toInt(Object value, Integer defaultValue) {
  1196. if (value == null) {
  1197. return defaultValue;
  1198. }
  1199. if (value instanceof Integer) {
  1200. return (Integer) value;
  1201. }
  1202. if (value instanceof Number) {
  1203. return ((Number) value).intValue();
  1204. }
  1205. final String valueStr = toStr(value, null);
  1206. if (StringUtils.isEmpty(valueStr)) {
  1207. return defaultValue;
  1208. }
  1209. try {
  1210. return Integer.parseInt(valueStr.trim());
  1211. } catch (Exception e) {
  1212. return defaultValue;
  1213. }
  1214. }
  1215. /**
  1216. * 获取文件类型
  1217. *
  1218. * @param photoByte 文件字节码
  1219. * @return 后缀(不含".")
  1220. */
  1221. public static String getFileExtendName(byte[] photoByte) {
  1222. String strFileExtendName = "JPG";
  1223. if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
  1224. && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97)) {
  1225. strFileExtendName = "GIF";
  1226. } else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70)) {
  1227. strFileExtendName = "JPG";
  1228. } else if ((photoByte[0] == 66) && (photoByte[1] == 77)) {
  1229. strFileExtendName = "BMP";
  1230. } else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71)) {
  1231. strFileExtendName = "PNG";
  1232. }
  1233. return strFileExtendName;
  1234. }
  1235. public static byte[] getImage(String imagePath) {
  1236. InputStream is = getFile(imagePath);
  1237. try {
  1238. return IOUtils.toByteArray(is);
  1239. } catch (Exception e) {
  1240. log.error("图片加载异常 {}", e);
  1241. return null;
  1242. } finally {
  1243. IOUtils.closeQuietly(is);
  1244. }
  1245. }
  1246. public static InputStream getFile(String imagePath) {
  1247. try {
  1248. byte[] result = readFile(imagePath);
  1249. result = Arrays.copyOf(result, result.length);
  1250. return new ByteArrayInputStream(result);
  1251. } catch (Exception e) {
  1252. log.error("获取图片异常 {}", e);
  1253. }
  1254. return null;
  1255. }
  1256. /**
  1257. * 读取文件为字节数据
  1258. *
  1259. * @return 字节数据
  1260. */
  1261. public static byte[] readFile(String url) {
  1262. InputStream in = null;
  1263. ByteArrayOutputStream baos = null;
  1264. try {
  1265. if (url.startsWith("http")) {
  1266. // 网络地址
  1267. URL urlObj = new URL(url);
  1268. URLConnection urlConnection = urlObj.openConnection();
  1269. urlConnection.setConnectTimeout(30 * 1000);
  1270. urlConnection.setReadTimeout(60 * 1000);
  1271. urlConnection.setDoInput(true);
  1272. in = urlConnection.getInputStream();
  1273. } else {
  1274. // 本机地址(需修改)
  1275. String localPath = "127.0.0.1";
  1276. String downloadPath = localPath + StringUtils.substringAfter(url, "/profile");
  1277. in = new FileInputStream(downloadPath);
  1278. }
  1279. return IOUtils.toByteArray(in);
  1280. } catch (Exception e) {
  1281. log.error("获取文件路径异常 {}", e);
  1282. return null;
  1283. } finally {
  1284. IOUtils.closeQuietly(baos);
  1285. }
  1286. }
  1287. /**
  1288. * 对excel表单默认第一个索引名转换成list
  1289. *
  1290. * @param is 输入流
  1291. * @return 转换后集合
  1292. */
  1293. public List<T> importExcel(InputStream is) throws Exception
  1294. {
  1295. return importExcel(is, 0);
  1296. }
  1297. /**
  1298. * 对excel表单默认第一个索引名转换成list
  1299. *
  1300. * @param is 输入流
  1301. * @param titleNum 标题占用行数
  1302. * @return 转换后集合
  1303. */
  1304. public List<T> importExcel(InputStream is, int titleNum) throws Exception
  1305. {
  1306. return importExcel(StringUtils.EMPTY, is, titleNum);
  1307. }
  1308. //
  1309. public List<T> importExcelSet( InputStream is, int titleNum) throws Exception {
  1310. this.type = Excel.Type.IMPORT;
  1311. this.wb = WorkbookFactory.create(is);
  1312. List<T> list = new ArrayList<T>();
  1313. Iterator<Sheet> sheetIterator = wb.sheetIterator();
  1314. while (sheetIterator.hasNext()) {
  1315. Sheet sheet = sheetIterator.next();
  1316. if (sheet == null) {
  1317. throw new IOException("文件sheet不存在");
  1318. }
  1319. boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
  1320. Map<String, PictureData> pictures;
  1321. if (isXSSFWorkbook) {
  1322. pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
  1323. } else {
  1324. pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
  1325. }
  1326. // 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
  1327. int rows = sheet.getLastRowNum();
  1328. if (rows > 0) {
  1329. // 定义一个map用于存放excel列的序号和field.
  1330. Map<String, Integer> cellMap = new HashMap<String, Integer>();
  1331. // 获取表头
  1332. Row heard = sheet.getRow(titleNum);
  1333. for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
  1334. Cell cell = heard.getCell(i);
  1335. if (StringUtils.isNotNull(cell)) {
  1336. String value = this.getCellValue(heard, i).toString();
  1337. cellMap.put(value, i);
  1338. } else {
  1339. cellMap.put(null, i);
  1340. }
  1341. }
  1342. // 有数据时才处理 得到类的所有field.
  1343. List<Object[]> fields = this.getFields();
  1344. Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
  1345. for (Object[] objects : fields) {
  1346. Excel attr = (Excel) objects[1];
  1347. Integer column = cellMap.get(attr.name());
  1348. if (column != null) {
  1349. fieldsMap.put(column, objects);
  1350. }
  1351. }
  1352. int errorCount = 0;
  1353. try {
  1354. for (int i = titleNum + 1; i <= rows; i++) {
  1355. errorCount = i + 1;
  1356. // 从第2行开始取数据,默认第一行是表头.
  1357. Row row = sheet.getRow(i);
  1358. // 判断当前行是否是空行
  1359. if (isRowEmpty(row)) {
  1360. continue;
  1361. }
  1362. T entity = null;
  1363. for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {
  1364. Object val = this.getCellValue(row, entry.getKey());
  1365. // 如果不存在实例则新建.
  1366. entity = (entity == null ? clazz.newInstance() : entity);
  1367. // 从map中得到对应列的field.
  1368. Field field = (Field) entry.getValue()[0];
  1369. Excel attr = (Excel) entry.getValue()[1];
  1370. // 取得类型,并根据对象类型设置值.
  1371. Class<?> fieldType = field.getType();
  1372. if (String.class == fieldType) {
  1373. String s = Convert.toStr(val);
  1374. if (StringUtils.endsWith(s, ".0")) {
  1375. val = StringUtils.substringBefore(s, ".0");
  1376. } else {
  1377. String dateFormat = field.getAnnotation(Excel.class).dateFormat();
  1378. if (StringUtils.isNotEmpty(dateFormat)) {
  1379. val = parseDateToStr(dateFormat, val);
  1380. } else {
  1381. val = Convert.toStr(val);
  1382. }
  1383. }
  1384. } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
  1385. val = Convert.toInt(val);
  1386. } else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
  1387. val = Convert.toLong(val);
  1388. } else if (Double.TYPE == fieldType || Double.class == fieldType) {
  1389. val = Convert.toDouble(val);
  1390. } else if (Float.TYPE == fieldType || Float.class == fieldType) {
  1391. val = Convert.toFloat(val);
  1392. } else if (BigDecimal.class == fieldType) {
  1393. val = Convert.toBigDecimal(val);
  1394. } else if (Date.class == fieldType) {
  1395. if (val instanceof String) {
  1396. val = DateUtils.parseDate((String) val);
  1397. } else if (val instanceof Double) {
  1398. val = DateUtil.getJavaDate((Double) val);
  1399. }
  1400. } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
  1401. val = Convert.toBool(val, false);
  1402. }
  1403. if (StringUtils.isNotNull(fieldType)) {
  1404. String propertyName = field.getName();
  1405. if (StringUtils.isNotEmpty(attr.targetAttr())) {
  1406. propertyName = field.getName() + "." + attr.targetAttr();
  1407. } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
  1408. val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
  1409. } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
  1410. val = dataFormatHandlerAdapter(val, attr);
  1411. } else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty((CharSequence) pictures)) {
  1412. PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
  1413. if (image == null) {
  1414. val = "";
  1415. } else {
  1416. byte[] data = image.getData();
  1417. val = FileUtils.writeImportBytes(data);
  1418. }
  1419. }
  1420. ReflectUtils.invokeSetter(entity, propertyName, val);
  1421. }
  1422. }
  1423. list.add(entity);
  1424. }
  1425. } catch (Exception e) {
  1426. throw new BusinessException(String.format("表格第%s行内容错误,请检查修改后再上传。", errorCount));
  1427. }
  1428. }
  1429. }
  1430. return list;
  1431. }
  1432. /**
  1433. * 对excel表单指定表格索引名转换成list
  1434. *
  1435. * @param sheetName 表格索引名
  1436. * @param titleNum 标题占用行数
  1437. * @param is 输入流
  1438. * @return 转换后集合
  1439. */
  1440. public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception
  1441. {
  1442. this.type = Excel.Type.IMPORT;
  1443. this.wb = WorkbookFactory.create(is);
  1444. List<T> list = new ArrayList<T>();
  1445. // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
  1446. Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
  1447. if (sheet == null)
  1448. {
  1449. throw new IOException("文件sheet不存在");
  1450. }
  1451. boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
  1452. Map<String, PictureData> pictures;
  1453. if (isXSSFWorkbook)
  1454. {
  1455. pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
  1456. }
  1457. else
  1458. {
  1459. pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
  1460. }
  1461. // 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
  1462. int rows = sheet.getLastRowNum();
  1463. if (rows > 0){
  1464. // 定义一个map用于存放excel列的序号和field.
  1465. Map<String, Integer> cellMap = new HashMap<String, Integer>();
  1466. // 获取表头
  1467. Row heard = sheet.getRow(titleNum);
  1468. for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
  1469. {
  1470. Cell cell = heard.getCell(i);
  1471. if (StringUtils.isNotNull(cell))
  1472. {
  1473. String value = this.getCellValue(heard, i).toString();
  1474. cellMap.put(value, i);
  1475. }
  1476. else
  1477. {
  1478. cellMap.put(null, i);
  1479. }
  1480. }
  1481. // 有数据时才处理 得到类的所有field.
  1482. List<Object[]> fields = this.getFields();
  1483. Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
  1484. for (Object[] objects : fields)
  1485. {
  1486. Excel attr = (Excel) objects[1];
  1487. Integer column = cellMap.get(attr.name());
  1488. if (column != null)
  1489. {
  1490. fieldsMap.put(column, objects);
  1491. }
  1492. }
  1493. int errorCount=0;
  1494. try {
  1495. for (int i = titleNum + 1; i <= rows; i++){
  1496. errorCount=i+1;
  1497. // 从第2行开始取数据,默认第一行是表头.
  1498. Row row = sheet.getRow(i);
  1499. // 判断当前行是否是空行
  1500. if (isRowEmpty(row))
  1501. {
  1502. continue;
  1503. }
  1504. T entity = null;
  1505. for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet())
  1506. {
  1507. Object val = this.getCellValue(row, entry.getKey());
  1508. // 如果不存在实例则新建.
  1509. entity = (entity == null ? clazz.newInstance() : entity);
  1510. // 从map中得到对应列的field.
  1511. Field field = (Field) entry.getValue()[0];
  1512. Excel attr = (Excel) entry.getValue()[1];
  1513. // 取得类型,并根据对象类型设置值.
  1514. Class<?> fieldType = field.getType();
  1515. if (String.class == fieldType)
  1516. {
  1517. String s = Convert.toStr(val);
  1518. if (StringUtils.endsWith(s, ".0"))
  1519. {
  1520. val = StringUtils.substringBefore(s, ".0");
  1521. }
  1522. else
  1523. {
  1524. String dateFormat = field.getAnnotation(Excel.class).dateFormat();
  1525. if (StringUtils.isNotEmpty(dateFormat))
  1526. {
  1527. val = parseDateToStr(dateFormat, val);
  1528. }
  1529. else
  1530. {
  1531. val = Convert.toStr(val);
  1532. }
  1533. }
  1534. }
  1535. else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
  1536. {
  1537. val = Convert.toInt(val);
  1538. }
  1539. else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
  1540. {
  1541. val = Convert.toLong(val);
  1542. }
  1543. else if (Double.TYPE == fieldType || Double.class == fieldType)
  1544. {
  1545. val = Convert.toDouble(val);
  1546. }
  1547. else if (Float.TYPE == fieldType || Float.class == fieldType)
  1548. {
  1549. val = Convert.toFloat(val);
  1550. }
  1551. else if (BigDecimal.class == fieldType)
  1552. {
  1553. val = Convert.toBigDecimal(val);
  1554. }
  1555. else if (Date.class == fieldType)
  1556. {
  1557. if (val instanceof String)
  1558. {
  1559. val = DateUtils.parseDate((String) val);
  1560. }
  1561. else if (val instanceof Double)
  1562. {
  1563. val = DateUtil.getJavaDate((Double) val);
  1564. }
  1565. }
  1566. else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
  1567. {
  1568. val = Convert.toBool(val, false);
  1569. }
  1570. if (StringUtils.isNotNull(fieldType))
  1571. {
  1572. String propertyName = field.getName();
  1573. if (StringUtils.isNotEmpty(attr.targetAttr()))
  1574. {
  1575. propertyName = field.getName() + "." + attr.targetAttr();
  1576. }
  1577. else if (StringUtils.isNotEmpty(attr.readConverterExp()))
  1578. {
  1579. val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
  1580. }
  1581. else if (!attr.handler().equals(ExcelHandlerAdapter.class))
  1582. {
  1583. val = dataFormatHandlerAdapter(val, attr);
  1584. }
  1585. else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty((CharSequence) pictures))
  1586. {
  1587. PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
  1588. if (image == null)
  1589. {
  1590. val = "";
  1591. }
  1592. else
  1593. {
  1594. byte[] data = image.getData();
  1595. val = FileUtils.writeImportBytes(data);
  1596. }
  1597. }
  1598. ReflectUtils.invokeSetter(entity, propertyName, val);
  1599. }
  1600. }
  1601. list.add(entity);
  1602. }
  1603. }catch (Exception e){
  1604. throw new BusinessException(String.format("表格第%s行内容错误,请检查修改后再上传。",errorCount));
  1605. }
  1606. }
  1607. return list;
  1608. }
  1609. /**
  1610. * 判断是否是空行
  1611. *
  1612. * @param row 判断的行
  1613. * @return
  1614. */
  1615. private boolean isRowEmpty(Row row)
  1616. {
  1617. if (row == null)
  1618. {
  1619. return true;
  1620. }
  1621. for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
  1622. {
  1623. Cell cell = row.getCell(i);
  1624. if (cell != null && cell.getCellTypeEnum() != CellType.BLANK)
  1625. {
  1626. return false;
  1627. }
  1628. }
  1629. return true;
  1630. }
  1631. /**
  1632. * 格式化不同类型的日期对象
  1633. *
  1634. * @param dateFormat 日期格式
  1635. * @param val 被格式化的日期对象
  1636. * @return 格式化后的日期字符
  1637. */
  1638. public String parseDateToStr(String dateFormat, Object val)
  1639. {
  1640. if (val == null)
  1641. {
  1642. return "";
  1643. }
  1644. String str;
  1645. if (val instanceof Date)
  1646. {
  1647. str = DateUtils.parseDateToStr(dateFormat, (Date) val);
  1648. }
  1649. else if (val instanceof LocalDateTime)
  1650. {
  1651. str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate(LocalDate.from((LocalDateTime) val)));
  1652. }
  1653. else if (val instanceof LocalDate)
  1654. {
  1655. str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) val));
  1656. }
  1657. else
  1658. {
  1659. str = val.toString();
  1660. }
  1661. return str;
  1662. }
  1663. /**
  1664. * 获取Excel2003图片
  1665. *
  1666. * @param sheet 当前sheet对象
  1667. * @param workbook 工作簿对象
  1668. * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
  1669. */
  1670. public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
  1671. {
  1672. Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
  1673. List<HSSFPictureData> pictures = workbook.getAllPictures();
  1674. if (!pictures.isEmpty())
  1675. {
  1676. for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
  1677. {
  1678. HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
  1679. if (shape instanceof HSSFPicture)
  1680. {
  1681. HSSFPicture pic = (HSSFPicture) shape;
  1682. int pictureIndex = pic.getPictureIndex() - 1;
  1683. HSSFPictureData picData = pictures.get(pictureIndex);
  1684. String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
  1685. sheetIndexPicMap.put(picIndex, picData);
  1686. }
  1687. }
  1688. return sheetIndexPicMap;
  1689. }
  1690. else
  1691. {
  1692. return sheetIndexPicMap;
  1693. }
  1694. }
  1695. /**
  1696. * 获取Excel2007图片
  1697. *
  1698. * @param sheet 当前sheet对象
  1699. * @param workbook 工作簿对象
  1700. * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
  1701. */
  1702. public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
  1703. {
  1704. Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
  1705. for (POIXMLDocumentPart dr : sheet.getRelations())
  1706. {
  1707. if (dr instanceof XSSFDrawing)
  1708. {
  1709. XSSFDrawing drawing = (XSSFDrawing) dr;
  1710. List<XSSFShape> shapes = drawing.getShapes();
  1711. for (XSSFShape shape : shapes)
  1712. {
  1713. if (shape instanceof XSSFPicture)
  1714. {
  1715. XSSFPicture pic = (XSSFPicture) shape;
  1716. XSSFClientAnchor anchor = pic.getPreferredSize();
  1717. CTMarker ctMarker = anchor.getFrom();
  1718. String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
  1719. sheetIndexPicMap.put(picIndex, pic.getPictureData());
  1720. }
  1721. }
  1722. }
  1723. }
  1724. return sheetIndexPicMap;
  1725. }
  1726. /**
  1727. * 获取字段注解信息
  1728. */
  1729. public List<Object[]> getFields()
  1730. {
  1731. List<Object[]> fields = new ArrayList<Object[]>();
  1732. List<Field> tempFields = new ArrayList<>();
  1733. tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  1734. tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  1735. for (Field field : tempFields)
  1736. {
  1737. // 单注解
  1738. if (field.isAnnotationPresent(Excel.class))
  1739. {
  1740. Excel attr = field.getAnnotation(Excel.class);
  1741. if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  1742. {
  1743. field.setAccessible(true);
  1744. fields.add(new Object[] { field, attr });
  1745. }
  1746. }
  1747. // 多注解
  1748. if (field.isAnnotationPresent(Excels.class))
  1749. {
  1750. Excels attrs = field.getAnnotation(Excels.class);
  1751. Excel[] excels = attrs.value();
  1752. for (Excel attr : excels)
  1753. {
  1754. if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  1755. {
  1756. field.setAccessible(true);
  1757. fields.add(new Object[] { field, attr });
  1758. }
  1759. }
  1760. }
  1761. }
  1762. return fields;
  1763. }
  1764. /**
  1765. * 数据处理器
  1766. *
  1767. * @param value 数据值
  1768. * @param excel 数据注解
  1769. * @return
  1770. */
  1771. public String dataFormatHandlerAdapter(Object value, Excel excel)
  1772. {
  1773. try
  1774. {
  1775. Object instance = excel.handler().newInstance();
  1776. Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
  1777. value = formatMethod.invoke(instance, value, excel.args());
  1778. }
  1779. catch (Exception e)
  1780. {
  1781. log.error("不能格式化数据 " + excel.handler(), e.getMessage());
  1782. }
  1783. return Convert.toStr(value);
  1784. }
  1785. public Result exportTaskDetailsExcel(TaskDetailsBo byTid, String sheetName, String uploadPath) {
  1786. OutputStream out = null;
  1787. String filename="";
  1788. downloadPath=uploadPath;
  1789. uploadPath=uploadPath+"/"+"tmp/member_progress.xls";
  1790. try {
  1791. FileInputStream fileInputStream = new FileInputStream(uploadPath);
  1792. wb = new HSSFWorkbook(fileInputStream);
  1793. styles=createStyles(wb);
  1794. sheet = wb.getSheetAt(0);
  1795. Row row = sheet.createRow(6);
  1796. filename = encodingFilename(sheetName);
  1797. out = Files.newOutputStream(Paths.get(getAbsoluteFile(filename)));
  1798. addCell( row,byTid);
  1799. wb.write(out);
  1800. return new Result().data(filename);
  1801. } catch (Exception e) {
  1802. e.printStackTrace();
  1803. throw new BusinessException("导出Excel失败,请联系网站管理员!");
  1804. } finally {
  1805. if (wb != null) {
  1806. try {
  1807. wb.close();
  1808. } catch (IOException e1) {
  1809. e1.printStackTrace();
  1810. }
  1811. }
  1812. if (out != null) {
  1813. try {
  1814. out.close();
  1815. } catch (IOException e1) {
  1816. e1.printStackTrace();
  1817. }
  1818. }
  1819. }
  1820. }
  1821. private void addObjectCell(Row row, OutTaskDetailsBo byTid) {
  1822. Class<?> clazz = byTid.getClass();
  1823. int i = 0;
  1824. for (Field field :clazz.getDeclaredFields()){
  1825. field.setAccessible(true);
  1826. Cell cell = row.createCell(i);
  1827. cell.setCellStyle(styles.get("data4"));
  1828. try {
  1829. // System.out.println(String.format("name=%s,value=%s",field.getName(),field.get(byTid)));
  1830. Object o = field.get(byTid);
  1831. } catch (IllegalAccessException e) {
  1832. throw new RuntimeException(e);
  1833. }
  1834. }
  1835. }
  1836. private void addCell(Row row,TaskDetailsBo o) {
  1837. for (int i = 0; i < 70; i++) {
  1838. Cell cell = row.createCell(i);
  1839. cell.setCellStyle(styles.get("data4"));
  1840. if (i==0)cell.setCellValue(1);//序号
  1841. else if(i==1)cell.setCellValue(o.getUserName());//客户名称
  1842. else if(i==2)cell.setCellValue(o.getContractNo());//合同编号
  1843. else if(i==3)cell.setCellValue(o.getServiceProject());//服务内容
  1844. else if(i==4){//服务年限
  1845. cell.setCellValue(o.getServiceYear());
  1846. }
  1847. else if(i==5)cell.setCellValue(o.getMemberLvl());//会员级别
  1848. else if(i==6)cell.setCellValue(o.getTaskName()); //业务负责人
  1849. else if(i==7)cell.setCellValue(o.getProjectName()); //项目负责人
  1850. else if(i==8)cell.setCellValue(o.getTechnologyName()); //技术咨询师
  1851. else if(i==9)cell.setCellValue(o.getFinanceTaxationName()); //财税咨询师
  1852. else if(i==10)cell.setCellValue(getBigDecimal(o.getBeforeLastRdSalesAmount())); //上上年度销售收入
  1853. else if(i==11)cell.setCellValue(getBigDecimal(o.getBeforeLastRdBudget())); //上上年度研发费用
  1854. else if(i==12)cell.setCellValue(getBigDecimal(o.getLastRdSalesAmount())); //上年度销售收入
  1855. else if(i==13)cell.setCellValue(getBigDecimal(o.getRdSalesAmount())); //上年度研发费用
  1856. else if(i==14)cell.setCellValue(o.getWorkersNumber());//职工人数
  1857. else if(i==15)cell.setCellValue(o.getTechnologyNumber());//科技人员
  1858. else if(i==16)cell.setCellValue(o.getWorkersRate()+"%");//人员占比
  1859. else if(i==17)cell.setCellValue(o.getProjectApprovalNumber());//研发立项数
  1860. else if(i==18)cell.setCellValue(getBigDecimal(o.getRdBudget()));//年度研发预算总额
  1861. else if(i==19)cell.setCellValue(getBigDecimal(o.getRdSalesAmount()));//年度销售预计收入
  1862. else if(i==20)cell.setCellValue(o.getRdBudgetRate()+"%");//年度研发费用预算占比
  1863. else if(i==21)cell.setCellValue(o.getThreeYearsActualRdRate()+"%");//三年度研发费用占比
  1864. else if(i==22){//数据周期
  1865. if (o.getDataCycle()==null){
  1866. cell.setCellValue("");
  1867. }else {
  1868. switch (o.getDataCycle())
  1869. {
  1870. case 0:
  1871. cell.setCellValue("第一季度");
  1872. break;
  1873. case 1: cell.setCellValue("上半年"); break;
  1874. case 2: cell.setCellValue("前三季度"); break;
  1875. case 3: cell.setCellValue("全年"); break;
  1876. }
  1877. }
  1878. }else if(i==23){//已立项RD数
  1879. cell.setCellValue(o.getActualRdNumber());
  1880. }else if(i==24){//委外项目数
  1881. cell.setCellValue(o.getActualOutsourcingNumber());
  1882. }
  1883. else if(i==25){//合作项目数
  1884. cell.setCellValue(o.getActualCooperateNumber());
  1885. }
  1886. else if(i==26){//已归集研发费用总额
  1887. cell.setCellValue(o.getActualRdAmount().stripTrailingZeros().toPlainString());
  1888. }
  1889. else if(i==27){//研发预算完成度
  1890. cell.setCellValue(o.getRdCompletionProgress()+"%");
  1891. }
  1892. else if(i==28){//年度实际销售额
  1893. cell.setCellValue(getBigDecimal(o.getActualSalesAmount()));
  1894. }
  1895. else if(i==29){//年度研发费用占比
  1896. cell.setCellValue(o.getActualRdRate()+"%");
  1897. }
  1898. else if(i==30){//三年研发费用占比
  1899. cell.setCellValue(o.getThreeYearsActualRdRate()+"%");
  1900. }
  1901. else if(i==31){//火炬统计
  1902. addParam(cell,o.getTorchStatus(),o.getTorchOther());
  1903. }
  1904. else if(i==32){//统计年报
  1905. addParam(cell,o.getStatisticsStatus(),o.getStatisticsOther());
  1906. }
  1907. else if(i==33){//高企年报
  1908. addParam(cell,o.getChStatus(),o.getChOther());
  1909. }
  1910. else if(i==34){//汇算清缴-是否完成
  1911. addParam(cell,o.getFinalSettlementStatus(),o.getFinalSettlementOther());
  1912. }
  1913. else if(i==35){//汇算清缴-加扣RD数
  1914. cell.setCellValue(o.getReportFormsRdNumber());
  1915. }
  1916. else if(i==36){//汇算清缴-知识产权加扣费用
  1917. cell.setCellValue(getBigDecimal(o.getIntellectualPropertyAmount()));
  1918. }
  1919. else if(i==37){//汇算清缴-加扣研发费用金额
  1920. cell.setCellValue(getBigDecimal(o.getReportRdAmount()));
  1921. }
  1922. else if(i==38){//研发准备金备案
  1923. addParam(cell,o.getRdReservesStatus(),o.getRdReservesOther());
  1924. }
  1925. else if(i==39){//研发奖补申请-是否完成
  1926. addParam(cell,o.getRdAwardCompensationStatus(),o.getRdAwardCompensationOther());
  1927. }
  1928. else if(i==40){//研发奖补申请-申请金额
  1929. cell.setCellValue(getBigDecimal(o.getRdApplicationFee()));
  1930. }
  1931. else if(i==41){//研发奖补申请-公示金额
  1932. cell.setCellValue(getBigDecimal(o.getRdAnnouncementAmount()));
  1933. }
  1934. else if(i==42){//研发奖补申请-其他
  1935. cell.setCellValue(o.getRdAnnouncementOther());
  1936. }
  1937. else if(i==43){//加扣备查交付
  1938. addParam(cell,o.getAdDeliverStatus(),o.getAdDeliverOther());
  1939. }
  1940. else if(i==44){//加扣是否归档
  1941. if(o.getAdDeliverFinish()==null){
  1942. cell.setCellValue("");
  1943. }else if(o.getAdDeliverFinish()==1){
  1944. cell.setCellValue("是");
  1945. }else if(o.getAdDeliverFinish()==0){
  1946. cell.setCellValue("否");
  1947. }
  1948. }
  1949. else if(i==45){//高新备查交付
  1950. addParam(cell,o.getHtDeliverStatus(),o.getHtDeliverOther());
  1951. }
  1952. else if(i==46){//高新是否归档
  1953. if (o.getHtDeliverFinish()==null){
  1954. cell.setCellValue("");
  1955. }else if(o.getHtDeliverFinish()==1){
  1956. cell.setCellValue("是");
  1957. }else if(o.getHtDeliverFinish()==0){
  1958. cell.setCellValue("否");
  1959. }
  1960. }
  1961. else if(i==47){//召开上年度会议于本年度工作计划
  1962. addParam(cell,o.getCloseMeetingStatus(),o.getCloseMeetingOther());
  1963. }
  1964. else if(i==48){//阶段性汇报
  1965. StringBuilder res= new StringBuilder();
  1966. if (o.getPhasedType()==1){
  1967. res.append("月度- ");
  1968. }else if (o.getPhasedType()==2){
  1969. res.append("季度- ");
  1970. }
  1971. String[] split = o.getPhasedContent().split(",");
  1972. for (String s : split) {
  1973. res.append(s).append("月 ,");
  1974. }
  1975. if(res.toString().contains(",")){
  1976. cell.setCellValue(res.substring(0,res.length()-1));
  1977. }else {
  1978. cell.setCellValue("");
  1979. }
  1980. }
  1981. else if(i==49){//立项资料编制-完成数
  1982. cell.setCellValue(o.getProjectApprovalWriteNumber());
  1983. }else if(i==50){//立项资料编制-完成率
  1984. cell.setCellValue(o.getProjectApprovalWriteRate()+"%");
  1985. }else if(i==51){//加扣预计申报7月
  1986. addParam(cell,o.getAdPrepareJulyStatus(),o.getAdPrepareJulyOther());
  1987. }else if(i==52){//加扣预计申报10月
  1988. addParam(cell,o.getAdPrepareOctoberStatus(),o.getAdPrepareOctoberOther());
  1989. }else if(i==53){//知识产权-发明专利-申报数
  1990. cell.setCellValue(o.getIpInventionPatentDeclare());
  1991. }else if(i==54){//知识产权-发明专利-授权数
  1992. cell.setCellValue(o.getIpInventionPatentAuthorize());
  1993. }else if(i==55){///知识产权-发明专利-累计授权数
  1994. cell.setCellValue(o.getIpInventionPatentAuthorizeCount());
  1995. }else if(i==56){//实用新型-申报数
  1996. cell.setCellValue(o.getIpUtilityModelDeclare());
  1997. }else if(i==57){//实用新型-授权数
  1998. cell.setCellValue(o.getIpUtilityModelAuthorize());
  1999. }else if(i==58){//实用新型-累计授权数
  2000. cell.setCellValue(o.getIpUtilityModelAuthorizeCount());
  2001. }else if(i==59){//软著-申报数
  2002. cell.setCellValue(o.getIpSoftwareWorksDeclare());
  2003. }else if(i==60){//软著-授权数
  2004. cell.setCellValue(o.getIpSoftwareWorksAuthorize());
  2005. }else if(i==61){//软著-累计授权数
  2006. cell.setCellValue(o.getIpSoftwareWorksAuthorizeCount());
  2007. }else if(i==62){//软著-截止本年末可用于下次高新认定数量
  2008. cell.setCellValue(o.getIpHt());
  2009. }else if(i==63){//软著-I类数量
  2010. cell.setCellValue(o.getIpHtI());
  2011. }else if(i==64){//软著-II类数量
  2012. cell.setCellValue(o.getIpHtIi());
  2013. }else if(i==65){//公出指标-合同约定次数
  2014. cell.setCellValue(o.getPublicReleaseStipulation());
  2015. }else if(i==66){//公出指标-实际次数
  2016. cell.setCellValue(o.getPublicReleaseActual());
  2017. }else if(i==67){//培训指标(本年度)-合同约定次数
  2018. cell.setCellValue(o.getTrainingStipulation());
  2019. }else if(i==68){//培训指标(本年度)-实际次数
  2020. cell.setCellValue(o.getTrainingActual());
  2021. }else if(i==69){//培训指标(本年度)-其他
  2022. cell.setCellValue(o.getTrainingOther());
  2023. }else {
  2024. cell.setCellValue("");
  2025. }
  2026. }
  2027. }
  2028. private String getBigDecimal(BigDecimal bigDecimal) {
  2029. if (bigDecimal==null){
  2030. return "";
  2031. }else {
  2032. return bigDecimal.stripTrailingZeros().toPlainString();
  2033. }
  2034. }
  2035. private void addParam(Cell cell, Integer code, String desc) {
  2036. switch (code)
  2037. {
  2038. case 0:
  2039. String res="其他:"+desc;
  2040. cell.setCellValue(res);
  2041. break;
  2042. case 1: cell.setCellValue("待完成"); break;
  2043. case 2: cell.setCellValue("已完成"); break;
  2044. case 3: cell.setCellValue("合同无此项"); break;
  2045. }
  2046. }
  2047. }