ExcelUtil.java 47 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400
  1. package com.ruoyi.common.utils.poi;
  2. import java.io.File;
  3. import java.io.FileOutputStream;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.io.OutputStream;
  7. import java.lang.reflect.Field;
  8. import java.lang.reflect.Method;
  9. import java.math.BigDecimal;
  10. import java.text.DecimalFormat;
  11. import java.util.ArrayList;
  12. import java.util.Arrays;
  13. import java.util.Comparator;
  14. import java.util.Date;
  15. import java.util.HashMap;
  16. import java.util.List;
  17. import java.util.Map;
  18. import java.util.Set;
  19. import java.util.UUID;
  20. import java.util.stream.Collectors;
  21. import javax.servlet.http.HttpServletResponse;
  22. import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
  23. import org.apache.poi.hssf.usermodel.HSSFPicture;
  24. import org.apache.poi.hssf.usermodel.HSSFPictureData;
  25. import org.apache.poi.hssf.usermodel.HSSFShape;
  26. import org.apache.poi.hssf.usermodel.HSSFSheet;
  27. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  28. import org.apache.poi.ooxml.POIXMLDocumentPart;
  29. import org.apache.poi.ss.usermodel.BorderStyle;
  30. import org.apache.poi.ss.usermodel.Cell;
  31. import org.apache.poi.ss.usermodel.CellStyle;
  32. import org.apache.poi.ss.usermodel.CellType;
  33. import org.apache.poi.ss.usermodel.ClientAnchor;
  34. import org.apache.poi.ss.usermodel.DataValidation;
  35. import org.apache.poi.ss.usermodel.DataValidationConstraint;
  36. import org.apache.poi.ss.usermodel.DataValidationHelper;
  37. import org.apache.poi.ss.usermodel.DateUtil;
  38. import org.apache.poi.ss.usermodel.Drawing;
  39. import org.apache.poi.ss.usermodel.FillPatternType;
  40. import org.apache.poi.ss.usermodel.Font;
  41. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  42. import org.apache.poi.ss.usermodel.IndexedColors;
  43. import org.apache.poi.ss.usermodel.PictureData;
  44. import org.apache.poi.ss.usermodel.Row;
  45. import org.apache.poi.ss.usermodel.Sheet;
  46. import org.apache.poi.ss.usermodel.VerticalAlignment;
  47. import org.apache.poi.ss.usermodel.Workbook;
  48. import org.apache.poi.ss.usermodel.WorkbookFactory;
  49. import org.apache.poi.ss.util.CellRangeAddress;
  50. import org.apache.poi.ss.util.CellRangeAddressList;
  51. import org.apache.poi.util.IOUtils;
  52. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  53. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  54. import org.apache.poi.xssf.usermodel.XSSFDataValidation;
  55. import org.apache.poi.xssf.usermodel.XSSFDrawing;
  56. import org.apache.poi.xssf.usermodel.XSSFPicture;
  57. import org.apache.poi.xssf.usermodel.XSSFShape;
  58. import org.apache.poi.xssf.usermodel.XSSFSheet;
  59. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  60. import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
  61. import org.slf4j.Logger;
  62. import org.slf4j.LoggerFactory;
  63. import com.ruoyi.common.annotation.Excel;
  64. import com.ruoyi.common.annotation.Excel.ColumnType;
  65. import com.ruoyi.common.annotation.Excel.Type;
  66. import com.ruoyi.common.annotation.Excels;
  67. import com.ruoyi.common.config.RuoYiConfig;
  68. import com.ruoyi.common.core.domain.AjaxResult;
  69. import com.ruoyi.common.core.text.Convert;
  70. import com.ruoyi.common.exception.UtilException;
  71. import com.ruoyi.common.utils.DateUtils;
  72. import com.ruoyi.common.utils.DictUtils;
  73. import com.ruoyi.common.utils.StringUtils;
  74. import com.ruoyi.common.utils.file.FileTypeUtils;
  75. import com.ruoyi.common.utils.file.FileUtils;
  76. import com.ruoyi.common.utils.file.ImageUtils;
  77. import com.ruoyi.common.utils.reflect.ReflectUtils;
  78. /**
  79. * Excel相关处理
  80. *
  81. * @author ruoyi
  82. */
  83. public class ExcelUtil<T>
  84. {
  85. private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
  86. public static final String[] FORMULA_STR = { "=", "-", "+", "@" };
  87. /**
  88. * Excel sheet最大行数,默认65536
  89. */
  90. public static final int sheetSize = 65536;
  91. /**
  92. * 工作表名称
  93. */
  94. private String sheetName;
  95. /**
  96. * 导出类型(EXPORT:导出数据;IMPORT:导入模板)
  97. */
  98. private Type type;
  99. /**
  100. * 工作薄对象
  101. */
  102. private Workbook wb;
  103. /**
  104. * 工作表对象
  105. */
  106. private Sheet sheet;
  107. /**
  108. * 样式列表
  109. */
  110. private Map<String, CellStyle> styles;
  111. /**
  112. * 导入导出数据列表
  113. */
  114. private List<T> list;
  115. /**
  116. * 注解列表
  117. */
  118. private List<Object[]> fields;
  119. /**
  120. * 当前行号
  121. */
  122. private int rownum;
  123. /**
  124. * 标题
  125. */
  126. private String title;
  127. /**
  128. * 最大高度
  129. */
  130. private short maxHeight;
  131. /**
  132. * 统计列表
  133. */
  134. private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
  135. /**
  136. * 数字格式
  137. */
  138. private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
  139. /**
  140. * 实体对象
  141. */
  142. public Class<T> clazz;
  143. public ExcelUtil(Class<T> clazz)
  144. {
  145. this.clazz = clazz;
  146. }
  147. public void init(List<T> list, String sheetName, String title, Type type)
  148. {
  149. if (list == null)
  150. {
  151. list = new ArrayList<T>();
  152. }
  153. this.list = list;
  154. this.sheetName = sheetName;
  155. this.type = type;
  156. this.title = title;
  157. createExcelField();
  158. createWorkbook();
  159. createTitle();
  160. }
  161. /**
  162. * 创建excel第一行标题
  163. */
  164. public void createTitle()
  165. {
  166. if (StringUtils.isNotEmpty(title))
  167. {
  168. Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
  169. titleRow.setHeightInPoints(30);
  170. Cell titleCell = titleRow.createCell(0);
  171. titleCell.setCellStyle(styles.get("title"));
  172. titleCell.setCellValue(title);
  173. sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(),
  174. this.fields.size() - 1));
  175. }
  176. }
  177. /**
  178. * 对excel表单默认第一个索引名转换成list
  179. *
  180. * @param is 输入流
  181. * @return 转换后集合
  182. */
  183. public List<T> importExcel(InputStream is) throws Exception
  184. {
  185. return importExcel(is, 0);
  186. }
  187. /**
  188. * 对excel表单默认第一个索引名转换成list
  189. *
  190. * @param is 输入流
  191. * @param titleNum 标题占用行数
  192. * @return 转换后集合
  193. */
  194. public List<T> importExcel(InputStream is, int titleNum) throws Exception
  195. {
  196. return importExcel(StringUtils.EMPTY, is, titleNum);
  197. }
  198. /**
  199. * 对excel表单指定表格索引名转换成list
  200. *
  201. * @param sheetName 表格索引名
  202. * @param titleNum 标题占用行数
  203. * @param is 输入流
  204. * @return 转换后集合
  205. */
  206. public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception
  207. {
  208. this.type = Type.IMPORT;
  209. this.wb = WorkbookFactory.create(is);
  210. List<T> list = new ArrayList<T>();
  211. // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
  212. Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
  213. if (sheet == null)
  214. {
  215. throw new IOException("文件sheet不存在");
  216. }
  217. boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
  218. Map<String, PictureData> pictures;
  219. if (isXSSFWorkbook)
  220. {
  221. pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
  222. }
  223. else
  224. {
  225. pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
  226. }
  227. // 获取最后一个非空行的行下标,比如总行数为n,则返回的为n-1
  228. int rows = sheet.getLastRowNum();
  229. if (rows > 0)
  230. {
  231. // 定义一个map用于存放excel列的序号和field.
  232. Map<String, Integer> cellMap = new HashMap<String, Integer>();
  233. // 获取表头
  234. Row heard = sheet.getRow(titleNum);
  235. for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
  236. {
  237. Cell cell = heard.getCell(i);
  238. if (StringUtils.isNotNull(cell))
  239. {
  240. String value = this.getCellValue(heard, i).toString();
  241. cellMap.put(value, i);
  242. }
  243. else
  244. {
  245. cellMap.put(null, i);
  246. }
  247. }
  248. // 有数据时才处理 得到类的所有field.
  249. List<Object[]> fields = this.getFields();
  250. Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
  251. for (Object[] objects : fields)
  252. {
  253. Excel attr = (Excel) objects[1];
  254. Integer column = cellMap.get(attr.name());
  255. if (column != null)
  256. {
  257. fieldsMap.put(column, objects);
  258. }
  259. }
  260. for (int i = titleNum + 1; i <= rows; i++)
  261. {
  262. // 从第2行开始取数据,默认第一行是表头.
  263. Row row = sheet.getRow(i);
  264. // 判断当前行是否是空行
  265. if (isRowEmpty(row))
  266. {
  267. continue;
  268. }
  269. T entity = null;
  270. for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet())
  271. {
  272. Object val = this.getCellValue(row, entry.getKey());
  273. // 如果不存在实例则新建.
  274. entity = (entity == null ? clazz.newInstance() : entity);
  275. // 从map中得到对应列的field.
  276. Field field = (Field) entry.getValue()[0];
  277. Excel attr = (Excel) entry.getValue()[1];
  278. // 取得类型,并根据对象类型设置值.
  279. Class<?> fieldType = field.getType();
  280. if (String.class == fieldType)
  281. {
  282. String s = Convert.toStr(val);
  283. if (StringUtils.endsWith(s, ".0"))
  284. {
  285. val = StringUtils.substringBefore(s, ".0");
  286. }
  287. else
  288. {
  289. String dateFormat = field.getAnnotation(Excel.class).dateFormat();
  290. if (StringUtils.isNotEmpty(dateFormat))
  291. {
  292. val = DateUtils.parseDateToStr(dateFormat, (Date) val);
  293. }
  294. else
  295. {
  296. val = Convert.toStr(val);
  297. }
  298. }
  299. }
  300. else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
  301. {
  302. val = Convert.toInt(val);
  303. }
  304. else if (Long.TYPE == fieldType || Long.class == fieldType)
  305. {
  306. val = Convert.toLong(val);
  307. }
  308. else if (Double.TYPE == fieldType || Double.class == fieldType)
  309. {
  310. val = Convert.toDouble(val);
  311. }
  312. else if (Float.TYPE == fieldType || Float.class == fieldType)
  313. {
  314. val = Convert.toFloat(val);
  315. }
  316. else if (BigDecimal.class == fieldType)
  317. {
  318. val = Convert.toBigDecimal(val);
  319. }
  320. else if (Date.class == fieldType)
  321. {
  322. if (val instanceof String)
  323. {
  324. val = DateUtils.parseDate(val);
  325. }
  326. else if (val instanceof Double)
  327. {
  328. val = DateUtil.getJavaDate((Double) val);
  329. }
  330. }
  331. else if (Boolean.TYPE == fieldType || Boolean.class == fieldType)
  332. {
  333. val = Convert.toBool(val, false);
  334. }
  335. if (StringUtils.isNotNull(fieldType))
  336. {
  337. String propertyName = field.getName();
  338. if (StringUtils.isNotEmpty(attr.targetAttr()))
  339. {
  340. propertyName = field.getName() + "." + attr.targetAttr();
  341. }
  342. else if (StringUtils.isNotEmpty(attr.readConverterExp()))
  343. {
  344. val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
  345. }
  346. else if (StringUtils.isNotEmpty(attr.dictType()))
  347. {
  348. val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
  349. }
  350. else if (!attr.handler().equals(ExcelHandlerAdapter.class))
  351. {
  352. val = dataFormatHandlerAdapter(val, attr);
  353. }
  354. else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))
  355. {
  356. PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
  357. if (image == null)
  358. {
  359. val = "";
  360. }
  361. else
  362. {
  363. byte[] data = image.getData();
  364. val = FileUtils.writeImportBytes(data);
  365. }
  366. }
  367. ReflectUtils.invokeSetter(entity, propertyName, val);
  368. }
  369. }
  370. list.add(entity);
  371. }
  372. }
  373. return list;
  374. }
  375. /**
  376. * 对list数据源将其里面的数据导入到excel表单
  377. *
  378. * @param list 导出数据集合
  379. * @param sheetName 工作表的名称
  380. * @return 结果
  381. */
  382. public AjaxResult exportExcel(List<T> list, String sheetName)
  383. {
  384. return exportExcel(list, sheetName, StringUtils.EMPTY);
  385. }
  386. /**
  387. * 对list数据源将其里面的数据导入到excel表单
  388. *
  389. * @param list 导出数据集合
  390. * @param sheetName 工作表的名称
  391. * @param title 标题
  392. * @return 结果
  393. */
  394. public AjaxResult exportExcel(List<T> list, String sheetName, String title)
  395. {
  396. this.init(list, sheetName, title, Type.EXPORT);
  397. return exportExcel();
  398. }
  399. /**
  400. * 对list数据源将其里面的数据导入到excel表单
  401. *
  402. * @param response 返回数据
  403. * @param list 导出数据集合
  404. * @param sheetName 工作表的名称
  405. * @return 结果
  406. * @throws IOException
  407. */
  408. public void exportExcel(HttpServletResponse response, List<T> list, String sheetName)
  409. {
  410. exportExcel(response, list, sheetName, StringUtils.EMPTY);
  411. }
  412. /**
  413. * 对list数据源将其里面的数据导入到excel表单
  414. *
  415. * @param response 返回数据
  416. * @param list 导出数据集合
  417. * @param sheetName 工作表的名称
  418. * @param title 标题
  419. * @return 结果
  420. * @throws IOException
  421. */
  422. public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, String title)
  423. {
  424. response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  425. response.setCharacterEncoding("utf-8");
  426. this.init(list, sheetName, title, Type.EXPORT);
  427. exportExcel(response);
  428. }
  429. /**
  430. * 对list数据源将其里面的数据导入到excel表单
  431. *
  432. * @param sheetName 工作表的名称
  433. * @return 结果
  434. */
  435. public AjaxResult importTemplateExcel(String sheetName)
  436. {
  437. return importTemplateExcel(sheetName, StringUtils.EMPTY);
  438. }
  439. /**
  440. * 对list数据源将其里面的数据导入到excel表单
  441. *
  442. * @param sheetName 工作表的名称
  443. * @param title 标题
  444. * @return 结果
  445. */
  446. public AjaxResult importTemplateExcel(String sheetName, String title)
  447. {
  448. this.init(null, sheetName, title, Type.IMPORT);
  449. return exportExcel();
  450. }
  451. /**
  452. * 对list数据源将其里面的数据导入到excel表单
  453. *
  454. * @param sheetName 工作表的名称
  455. * @return 结果
  456. */
  457. public void importTemplateExcel(HttpServletResponse response, String sheetName)
  458. {
  459. importTemplateExcel(response, sheetName, StringUtils.EMPTY);
  460. }
  461. /**
  462. * 对list数据源将其里面的数据导入到excel表单
  463. *
  464. * @param sheetName 工作表的名称
  465. * @param title 标题
  466. * @return 结果
  467. */
  468. public void importTemplateExcel(HttpServletResponse response, String sheetName, String title)
  469. {
  470. response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  471. response.setCharacterEncoding("utf-8");
  472. this.init(null, sheetName, title, Type.IMPORT);
  473. exportExcel(response);
  474. }
  475. /**
  476. * 对list数据源将其里面的数据导入到excel表单
  477. *
  478. * @return 结果
  479. */
  480. public void exportExcel(HttpServletResponse response)
  481. {
  482. try
  483. {
  484. writeSheet();
  485. wb.write(response.getOutputStream());
  486. }
  487. catch (Exception e)
  488. {
  489. log.error("导出Excel异常{}", e.getMessage());
  490. }
  491. finally
  492. {
  493. IOUtils.closeQuietly(wb);
  494. }
  495. }
  496. /**
  497. * 对list数据源将其里面的数据导入到excel表单
  498. *
  499. * @return 结果
  500. */
  501. public AjaxResult exportExcel()
  502. {
  503. OutputStream out = null;
  504. try
  505. {
  506. writeSheet();
  507. String filename = encodingFilename(sheetName);
  508. out = new FileOutputStream(getAbsoluteFile(filename));
  509. wb.write(out);
  510. return AjaxResult.success(filename);
  511. }
  512. catch (Exception e)
  513. {
  514. log.error("导出Excel异常{}", e.getMessage());
  515. throw new UtilException("导出Excel失败,请联系网站管理员!");
  516. }
  517. finally
  518. {
  519. IOUtils.closeQuietly(wb);
  520. IOUtils.closeQuietly(out);
  521. }
  522. }
  523. /**
  524. * 创建写入数据到Sheet
  525. */
  526. public void writeSheet()
  527. {
  528. // 取出一共有多少个sheet.
  529. int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));
  530. for (int index = 0; index < sheetNo; index++)
  531. {
  532. createSheet(sheetNo, index);
  533. // 产生一行
  534. Row row = sheet.createRow(rownum);
  535. int column = 0;
  536. // 写入各个字段的列头名称
  537. for (Object[] os : fields)
  538. {
  539. Excel excel = (Excel) os[1];
  540. this.createCell(excel, row, column++);
  541. }
  542. if (Type.EXPORT.equals(type))
  543. {
  544. fillExcelData(index, row);
  545. addStatisticsRow();
  546. }
  547. }
  548. }
  549. /**
  550. * 填充excel数据
  551. *
  552. * @param index 序号
  553. * @param row 单元格行
  554. */
  555. public void fillExcelData(int index, Row row)
  556. {
  557. int startNo = index * sheetSize;
  558. int endNo = Math.min(startNo + sheetSize, list.size());
  559. for (int i = startNo; i < endNo; i++)
  560. {
  561. row = sheet.createRow(i + 1 + rownum - startNo);
  562. // 得到导出对象.
  563. T vo = (T) list.get(i);
  564. int column = 0;
  565. for (Object[] os : fields)
  566. {
  567. Field field = (Field) os[0];
  568. Excel excel = (Excel) os[1];
  569. this.addCell(excel, row, vo, field, column++);
  570. }
  571. }
  572. }
  573. /**
  574. * 创建表格样式
  575. *
  576. * @param wb 工作薄对象
  577. * @return 样式列表
  578. */
  579. private Map<String, CellStyle> createStyles(Workbook wb)
  580. {
  581. // 写入各条记录,每条记录对应excel表中的一行
  582. Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  583. CellStyle style = wb.createCellStyle();
  584. style.setAlignment(HorizontalAlignment.CENTER);
  585. style.setVerticalAlignment(VerticalAlignment.CENTER);
  586. Font titleFont = wb.createFont();
  587. titleFont.setFontName("Arial");
  588. titleFont.setFontHeightInPoints((short) 16);
  589. titleFont.setBold(true);
  590. style.setFont(titleFont);
  591. styles.put("title", style);
  592. style = wb.createCellStyle();
  593. style.setAlignment(HorizontalAlignment.CENTER);
  594. style.setVerticalAlignment(VerticalAlignment.CENTER);
  595. style.setBorderRight(BorderStyle.THIN);
  596. style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  597. style.setBorderLeft(BorderStyle.THIN);
  598. style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  599. style.setBorderTop(BorderStyle.THIN);
  600. style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  601. style.setBorderBottom(BorderStyle.THIN);
  602. style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  603. Font dataFont = wb.createFont();
  604. dataFont.setFontName("Arial");
  605. dataFont.setFontHeightInPoints((short) 10);
  606. style.setFont(dataFont);
  607. styles.put("data", style);
  608. style = wb.createCellStyle();
  609. style.cloneStyleFrom(styles.get("data"));
  610. style.setAlignment(HorizontalAlignment.CENTER);
  611. style.setVerticalAlignment(VerticalAlignment.CENTER);
  612. style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
  613. style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  614. Font headerFont = wb.createFont();
  615. headerFont.setFontName("Arial");
  616. headerFont.setFontHeightInPoints((short) 10);
  617. headerFont.setBold(true);
  618. headerFont.setColor(IndexedColors.WHITE.getIndex());
  619. style.setFont(headerFont);
  620. styles.put("header", style);
  621. style = wb.createCellStyle();
  622. style.setAlignment(HorizontalAlignment.CENTER);
  623. style.setVerticalAlignment(VerticalAlignment.CENTER);
  624. Font totalFont = wb.createFont();
  625. totalFont.setFontName("Arial");
  626. totalFont.setFontHeightInPoints((short) 10);
  627. style.setFont(totalFont);
  628. styles.put("total", style);
  629. style = wb.createCellStyle();
  630. style.cloneStyleFrom(styles.get("data"));
  631. style.setAlignment(HorizontalAlignment.LEFT);
  632. styles.put("data1", style);
  633. style = wb.createCellStyle();
  634. style.cloneStyleFrom(styles.get("data"));
  635. style.setAlignment(HorizontalAlignment.CENTER);
  636. styles.put("data2", style);
  637. style = wb.createCellStyle();
  638. style.cloneStyleFrom(styles.get("data"));
  639. style.setAlignment(HorizontalAlignment.RIGHT);
  640. styles.put("data3", style);
  641. return styles;
  642. }
  643. /**
  644. * 创建单元格
  645. */
  646. public Cell createCell(Excel attr, Row row, int column)
  647. {
  648. // 创建列
  649. Cell cell = row.createCell(column);
  650. // 写入列信息
  651. cell.setCellValue(attr.name());
  652. setDataValidation(attr, row, column);
  653. cell.setCellStyle(styles.get("header"));
  654. return cell;
  655. }
  656. /**
  657. * 设置单元格信息
  658. *
  659. * @param value 单元格值
  660. * @param attr 注解相关
  661. * @param cell 单元格信息
  662. */
  663. public void setCellVo(Object value, Excel attr, Cell cell)
  664. {
  665. if (ColumnType.STRING == attr.cellType())
  666. {
  667. String cellValue = Convert.toStr(value);
  668. // 对于任何以表达式触发字符 =-+@开头的单元格,直接使用tab字符作为前缀,防止CSV注入。
  669. if (StringUtils.containsAny(cellValue, FORMULA_STR))
  670. {
  671. cellValue = StringUtils.replaceEach(cellValue, FORMULA_STR, new String[] { "\t=", "\t-", "\t+", "\t@" });
  672. }
  673. cell.setCellValue(StringUtils.isNull(cellValue) ? attr.defaultValue() : cellValue + attr.suffix());
  674. }
  675. else if (ColumnType.NUMERIC == attr.cellType())
  676. {
  677. if (StringUtils.isNotNull(value))
  678. {
  679. cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
  680. }
  681. }
  682. else if (ColumnType.IMAGE == attr.cellType())
  683. {
  684. ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
  685. String imagePath = Convert.toStr(value);
  686. if (StringUtils.isNotEmpty(imagePath))
  687. {
  688. byte[] data = ImageUtils.getImage(imagePath);
  689. getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
  690. cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
  691. }
  692. }
  693. }
  694. /**
  695. * 获取画布
  696. */
  697. public static Drawing<?> getDrawingPatriarch(Sheet sheet)
  698. {
  699. if (sheet.getDrawingPatriarch() == null)
  700. {
  701. sheet.createDrawingPatriarch();
  702. }
  703. return sheet.getDrawingPatriarch();
  704. }
  705. /**
  706. * 获取图片类型,设置图片插入类型
  707. */
  708. public int getImageType(byte[] value)
  709. {
  710. String type = FileTypeUtils.getFileExtendName(value);
  711. if ("JPG".equalsIgnoreCase(type))
  712. {
  713. return Workbook.PICTURE_TYPE_JPEG;
  714. }
  715. else if ("PNG".equalsIgnoreCase(type))
  716. {
  717. return Workbook.PICTURE_TYPE_PNG;
  718. }
  719. return Workbook.PICTURE_TYPE_JPEG;
  720. }
  721. /**
  722. * 创建表格样式
  723. */
  724. public void setDataValidation(Excel attr, Row row, int column)
  725. {
  726. if (attr.name().indexOf("注:") >= 0)
  727. {
  728. sheet.setColumnWidth(column, 6000);
  729. }
  730. else
  731. {
  732. // 设置列宽
  733. sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
  734. }
  735. // 如果设置了提示信息则鼠标放上去提示.
  736. if (StringUtils.isNotEmpty(attr.prompt()))
  737. {
  738. // 这里默认设了2-101列提示.
  739. setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
  740. }
  741. // 如果设置了combo属性则本列只能选择不能输入
  742. if (attr.combo().length > 0)
  743. {
  744. // 这里默认设了2-101列只能选择不能输入.
  745. setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
  746. }
  747. }
  748. /**
  749. * 添加单元格
  750. */
  751. public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
  752. {
  753. Cell cell = null;
  754. try
  755. {
  756. // 设置行高
  757. row.setHeight(maxHeight);
  758. // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
  759. if (attr.isExport())
  760. {
  761. // 创建cell
  762. cell = row.createCell(column);
  763. int align = attr.align().value();
  764. cell.setCellStyle(styles.get("data" + (align >= 1 && align <= 3 ? align : "")));
  765. // 用于读取对象中的属性
  766. Object value = getTargetValue(vo, field, attr);
  767. String dateFormat = attr.dateFormat();
  768. String readConverterExp = attr.readConverterExp();
  769. String separator = attr.separator();
  770. String dictType = attr.dictType();
  771. if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
  772. {
  773. cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
  774. }
  775. else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
  776. {
  777. cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
  778. }
  779. else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))
  780. {
  781. cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));
  782. }
  783. else if (value instanceof BigDecimal && -1 != attr.scale())
  784. {
  785. cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
  786. }
  787. else if (!attr.handler().equals(ExcelHandlerAdapter.class))
  788. {
  789. cell.setCellValue(dataFormatHandlerAdapter(value, attr));
  790. }
  791. else
  792. {
  793. // 设置列类型
  794. setCellVo(value, attr, cell);
  795. }
  796. addStatisticsData(column, Convert.toStr(value), attr);
  797. }
  798. }
  799. catch (Exception e)
  800. {
  801. log.error("导出Excel失败{}", e);
  802. }
  803. return cell;
  804. }
  805. /**
  806. * 设置 POI XSSFSheet 单元格提示
  807. *
  808. * @param sheet 表单
  809. * @param promptTitle 提示标题
  810. * @param promptContent 提示内容
  811. * @param firstRow 开始行
  812. * @param endRow 结束行
  813. * @param firstCol 开始列
  814. * @param endCol 结束列
  815. */
  816. public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
  817. int firstCol, int endCol)
  818. {
  819. DataValidationHelper helper = sheet.getDataValidationHelper();
  820. DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
  821. CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  822. DataValidation dataValidation = helper.createValidation(constraint, regions);
  823. dataValidation.createPromptBox(promptTitle, promptContent);
  824. dataValidation.setShowPromptBox(true);
  825. sheet.addValidationData(dataValidation);
  826. }
  827. /**
  828. * 设置某些列的值只能输入预制的数据,显示下拉框.
  829. *
  830. * @param sheet 要设置的sheet.
  831. * @param textlist 下拉框显示的内容
  832. * @param firstRow 开始行
  833. * @param endRow 结束行
  834. * @param firstCol 开始列
  835. * @param endCol 结束列
  836. * @return 设置好的sheet.
  837. */
  838. public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
  839. {
  840. DataValidationHelper helper = sheet.getDataValidationHelper();
  841. // 加载下拉列表内容
  842. DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
  843. // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
  844. CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
  845. // 数据有效性对象
  846. DataValidation dataValidation = helper.createValidation(constraint, regions);
  847. // 处理Excel兼容性问题
  848. if (dataValidation instanceof XSSFDataValidation)
  849. {
  850. dataValidation.setSuppressDropDownArrow(true);
  851. dataValidation.setShowErrorBox(true);
  852. }
  853. else
  854. {
  855. dataValidation.setSuppressDropDownArrow(false);
  856. }
  857. sheet.addValidationData(dataValidation);
  858. }
  859. /**
  860. * 解析导出值 0=男,1=女,2=未知
  861. *
  862. * @param propertyValue 参数值
  863. * @param converterExp 翻译注解
  864. * @param separator 分隔符
  865. * @return 解析后值
  866. */
  867. public static String convertByExp(String propertyValue, String converterExp, String separator)
  868. {
  869. StringBuilder propertyString = new StringBuilder();
  870. String[] convertSource = converterExp.split(",");
  871. for (String item : convertSource)
  872. {
  873. String[] itemArray = item.split("=");
  874. if (StringUtils.containsAny(separator, propertyValue))
  875. {
  876. for (String value : propertyValue.split(separator))
  877. {
  878. if (itemArray[0].equals(value))
  879. {
  880. propertyString.append(itemArray[1] + separator);
  881. break;
  882. }
  883. }
  884. }
  885. else
  886. {
  887. if (itemArray[0].equals(propertyValue))
  888. {
  889. return itemArray[1];
  890. }
  891. }
  892. }
  893. return StringUtils.stripEnd(propertyString.toString(), separator);
  894. }
  895. /**
  896. * 反向解析值 男=0,女=1,未知=2
  897. *
  898. * @param propertyValue 参数值
  899. * @param converterExp 翻译注解
  900. * @param separator 分隔符
  901. * @return 解析后值
  902. */
  903. public static String reverseByExp(String propertyValue, String converterExp, String separator)
  904. {
  905. StringBuilder propertyString = new StringBuilder();
  906. String[] convertSource = converterExp.split(",");
  907. for (String item : convertSource)
  908. {
  909. String[] itemArray = item.split("=");
  910. if (StringUtils.containsAny(separator, propertyValue))
  911. {
  912. for (String value : propertyValue.split(separator))
  913. {
  914. if (itemArray[1].equals(value))
  915. {
  916. propertyString.append(itemArray[0] + separator);
  917. break;
  918. }
  919. }
  920. }
  921. else
  922. {
  923. if (itemArray[1].equals(propertyValue))
  924. {
  925. return itemArray[0];
  926. }
  927. }
  928. }
  929. return StringUtils.stripEnd(propertyString.toString(), separator);
  930. }
  931. /**
  932. * 解析字典值
  933. *
  934. * @param dictValue 字典值
  935. * @param dictType 字典类型
  936. * @param separator 分隔符
  937. * @return 字典标签
  938. */
  939. public static String convertDictByExp(String dictValue, String dictType, String separator)
  940. {
  941. return DictUtils.getDictLabel(dictType, dictValue, separator);
  942. }
  943. /**
  944. * 反向解析值字典值
  945. *
  946. * @param dictLabel 字典标签
  947. * @param dictType 字典类型
  948. * @param separator 分隔符
  949. * @return 字典值
  950. */
  951. public static String reverseDictByExp(String dictLabel, String dictType, String separator)
  952. {
  953. return DictUtils.getDictValue(dictType, dictLabel, separator);
  954. }
  955. /**
  956. * 数据处理器
  957. *
  958. * @param value 数据值
  959. * @param excel 数据注解
  960. * @return
  961. */
  962. public String dataFormatHandlerAdapter(Object value, Excel excel)
  963. {
  964. try
  965. {
  966. Object instance = excel.handler().newInstance();
  967. Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });
  968. value = formatMethod.invoke(instance, value, excel.args());
  969. }
  970. catch (Exception e)
  971. {
  972. log.error("不能格式化数据 " + excel.handler(), e.getMessage());
  973. }
  974. return Convert.toStr(value);
  975. }
  976. /**
  977. * 合计统计信息
  978. */
  979. private void addStatisticsData(Integer index, String text, Excel entity)
  980. {
  981. if (entity != null && entity.isStatistics())
  982. {
  983. Double temp = 0D;
  984. if (!statistics.containsKey(index))
  985. {
  986. statistics.put(index, temp);
  987. }
  988. try
  989. {
  990. temp = Double.valueOf(text);
  991. }
  992. catch (NumberFormatException e)
  993. {
  994. }
  995. statistics.put(index, statistics.get(index) + temp);
  996. }
  997. }
  998. /**
  999. * 创建统计行
  1000. */
  1001. public void addStatisticsRow()
  1002. {
  1003. if (statistics.size() > 0)
  1004. {
  1005. Row row = sheet.createRow(sheet.getLastRowNum() + 1);
  1006. Set<Integer> keys = statistics.keySet();
  1007. Cell cell = row.createCell(0);
  1008. cell.setCellStyle(styles.get("total"));
  1009. cell.setCellValue("合计");
  1010. for (Integer key : keys)
  1011. {
  1012. cell = row.createCell(key);
  1013. cell.setCellStyle(styles.get("total"));
  1014. cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
  1015. }
  1016. statistics.clear();
  1017. }
  1018. }
  1019. /**
  1020. * 编码文件名
  1021. */
  1022. public String encodingFilename(String filename)
  1023. {
  1024. filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
  1025. return filename;
  1026. }
  1027. /**
  1028. * 获取下载路径
  1029. *
  1030. * @param filename 文件名称
  1031. */
  1032. public String getAbsoluteFile(String filename)
  1033. {
  1034. String downloadPath = RuoYiConfig.getDownloadPath() + filename;
  1035. File desc = new File(downloadPath);
  1036. if (!desc.getParentFile().exists())
  1037. {
  1038. desc.getParentFile().mkdirs();
  1039. }
  1040. return downloadPath;
  1041. }
  1042. /**
  1043. * 获取bean中的属性值
  1044. *
  1045. * @param vo 实体对象
  1046. * @param field 字段
  1047. * @param excel 注解
  1048. * @return 最终的属性值
  1049. * @throws Exception
  1050. */
  1051. private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
  1052. {
  1053. Object o = field.get(vo);
  1054. if (StringUtils.isNotEmpty(excel.targetAttr()))
  1055. {
  1056. String target = excel.targetAttr();
  1057. if (target.indexOf(".") > -1)
  1058. {
  1059. String[] targets = target.split("[.]");
  1060. for (String name : targets)
  1061. {
  1062. o = getValue(o, name);
  1063. }
  1064. }
  1065. else
  1066. {
  1067. o = getValue(o, target);
  1068. }
  1069. }
  1070. return o;
  1071. }
  1072. /**
  1073. * 以类的属性的get方法方法形式获取值
  1074. *
  1075. * @param o
  1076. * @param name
  1077. * @return value
  1078. * @throws Exception
  1079. */
  1080. private Object getValue(Object o, String name) throws Exception
  1081. {
  1082. if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name))
  1083. {
  1084. Class<?> clazz = o.getClass();
  1085. Field field = clazz.getDeclaredField(name);
  1086. field.setAccessible(true);
  1087. o = field.get(o);
  1088. }
  1089. return o;
  1090. }
  1091. /**
  1092. * 得到所有定义字段
  1093. */
  1094. private void createExcelField()
  1095. {
  1096. this.fields = getFields();
  1097. this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
  1098. this.maxHeight = getRowHeight();
  1099. }
  1100. /**
  1101. * 获取字段注解信息
  1102. */
  1103. public List<Object[]> getFields()
  1104. {
  1105. List<Object[]> fields = new ArrayList<Object[]>();
  1106. List<Field> tempFields = new ArrayList<>();
  1107. tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
  1108. tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
  1109. for (Field field : tempFields)
  1110. {
  1111. // 单注解
  1112. if (field.isAnnotationPresent(Excel.class))
  1113. {
  1114. Excel attr = field.getAnnotation(Excel.class);
  1115. if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  1116. {
  1117. field.setAccessible(true);
  1118. fields.add(new Object[] { field, attr });
  1119. }
  1120. }
  1121. // 多注解
  1122. if (field.isAnnotationPresent(Excels.class))
  1123. {
  1124. Excels attrs = field.getAnnotation(Excels.class);
  1125. Excel[] excels = attrs.value();
  1126. for (Excel attr : excels)
  1127. {
  1128. if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
  1129. {
  1130. field.setAccessible(true);
  1131. fields.add(new Object[] { field, attr });
  1132. }
  1133. }
  1134. }
  1135. }
  1136. return fields;
  1137. }
  1138. /**
  1139. * 根据注解获取最大行高
  1140. */
  1141. public short getRowHeight()
  1142. {
  1143. double maxHeight = 0;
  1144. for (Object[] os : this.fields)
  1145. {
  1146. Excel excel = (Excel) os[1];
  1147. maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
  1148. }
  1149. return (short) (maxHeight * 20);
  1150. }
  1151. /**
  1152. * 创建一个工作簿
  1153. */
  1154. public void createWorkbook()
  1155. {
  1156. this.wb = new SXSSFWorkbook(500);
  1157. this.sheet = wb.createSheet();
  1158. wb.setSheetName(0, sheetName);
  1159. this.styles = createStyles(wb);
  1160. }
  1161. /**
  1162. * 创建工作表
  1163. *
  1164. * @param sheetNo sheet数量
  1165. * @param index 序号
  1166. */
  1167. public void createSheet(int sheetNo, int index)
  1168. {
  1169. // 设置工作表的名称.
  1170. if (sheetNo > 1 && index > 0)
  1171. {
  1172. this.sheet = wb.createSheet();
  1173. this.createTitle();
  1174. wb.setSheetName(index, sheetName + index);
  1175. }
  1176. }
  1177. /**
  1178. * 获取单元格值
  1179. *
  1180. * @param row 获取的行
  1181. * @param column 获取单元格列号
  1182. * @return 单元格值
  1183. */
  1184. public Object getCellValue(Row row, int column)
  1185. {
  1186. if (row == null)
  1187. {
  1188. return row;
  1189. }
  1190. Object val = "";
  1191. try
  1192. {
  1193. Cell cell = row.getCell(column);
  1194. if (StringUtils.isNotNull(cell))
  1195. {
  1196. if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA)
  1197. {
  1198. val = cell.getNumericCellValue();
  1199. if (DateUtil.isCellDateFormatted(cell))
  1200. {
  1201. val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
  1202. }
  1203. else
  1204. {
  1205. if ((Double) val % 1 != 0)
  1206. {
  1207. val = new BigDecimal(val.toString());
  1208. }
  1209. else
  1210. {
  1211. val = new DecimalFormat("0").format(val);
  1212. }
  1213. }
  1214. }
  1215. else if (cell.getCellType() == CellType.STRING)
  1216. {
  1217. val = cell.getStringCellValue();
  1218. }
  1219. else if (cell.getCellType() == CellType.BOOLEAN)
  1220. {
  1221. val = cell.getBooleanCellValue();
  1222. }
  1223. else if (cell.getCellType() == CellType.ERROR)
  1224. {
  1225. val = cell.getErrorCellValue();
  1226. }
  1227. }
  1228. }
  1229. catch (Exception e)
  1230. {
  1231. return val;
  1232. }
  1233. return val;
  1234. }
  1235. /**
  1236. * 判断是否是空行
  1237. *
  1238. * @param row 判断的行
  1239. * @return
  1240. */
  1241. private boolean isRowEmpty(Row row)
  1242. {
  1243. if (row == null)
  1244. {
  1245. return true;
  1246. }
  1247. for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
  1248. {
  1249. Cell cell = row.getCell(i);
  1250. if (cell != null && cell.getCellType() != CellType.BLANK)
  1251. {
  1252. return false;
  1253. }
  1254. }
  1255. return true;
  1256. }
  1257. /**
  1258. * 获取Excel2003图片
  1259. *
  1260. * @param sheet 当前sheet对象
  1261. * @param workbook 工作簿对象
  1262. * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
  1263. */
  1264. public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
  1265. {
  1266. Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
  1267. List<HSSFPictureData> pictures = workbook.getAllPictures();
  1268. if (!pictures.isEmpty())
  1269. {
  1270. for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
  1271. {
  1272. HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
  1273. if (shape instanceof HSSFPicture)
  1274. {
  1275. HSSFPicture pic = (HSSFPicture) shape;
  1276. int pictureIndex = pic.getPictureIndex() - 1;
  1277. HSSFPictureData picData = pictures.get(pictureIndex);
  1278. String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
  1279. sheetIndexPicMap.put(picIndex, picData);
  1280. }
  1281. }
  1282. return sheetIndexPicMap;
  1283. }
  1284. else
  1285. {
  1286. return sheetIndexPicMap;
  1287. }
  1288. }
  1289. /**
  1290. * 获取Excel2007图片
  1291. *
  1292. * @param sheet 当前sheet对象
  1293. * @param workbook 工作簿对象
  1294. * @return Map key:图片单元格索引(1_1)String,value:图片流PictureData
  1295. */
  1296. public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
  1297. {
  1298. Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
  1299. for (POIXMLDocumentPart dr : sheet.getRelations())
  1300. {
  1301. if (dr instanceof XSSFDrawing)
  1302. {
  1303. XSSFDrawing drawing = (XSSFDrawing) dr;
  1304. List<XSSFShape> shapes = drawing.getShapes();
  1305. for (XSSFShape shape : shapes)
  1306. {
  1307. if (shape instanceof XSSFPicture)
  1308. {
  1309. XSSFPicture pic = (XSSFPicture) shape;
  1310. XSSFClientAnchor anchor = pic.getPreferredSize();
  1311. CTMarker ctMarker = anchor.getFrom();
  1312. String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
  1313. sheetIndexPicMap.put(picIndex, pic.getPictureData());
  1314. }
  1315. }
  1316. }
  1317. }
  1318. return sheetIndexPicMap;
  1319. }
  1320. }