ExcelUtil.java 46 KB

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