ExcelUtil.java 4.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150
  1. package com.ruoyi.common.utils.poi;
  2. import cn.hutool.core.util.IdUtil;
  3. import com.alibaba.excel.EasyExcel;
  4. import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
  5. import com.ruoyi.common.utils.DictUtils;
  6. import com.ruoyi.common.utils.StringUtils;
  7. import com.ruoyi.common.utils.file.FileUtils;
  8. import javax.servlet.ServletOutputStream;
  9. import javax.servlet.http.HttpServletResponse;
  10. import java.io.IOException;
  11. import java.io.InputStream;
  12. import java.net.URLEncoder;
  13. import java.nio.charset.StandardCharsets;
  14. import java.util.List;
  15. /**
  16. * Excel相关处理
  17. *
  18. * @author ruoyi
  19. */
  20. public class ExcelUtil {
  21. /**
  22. * 对excel表单默认第一个索引名转换成list(EasyExcel)
  23. *
  24. * @param is 输入流
  25. * @return 转换后集合
  26. */
  27. public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
  28. return EasyExcel.read(is).autoCloseStream(false).sheet().doReadSync();
  29. }
  30. /**
  31. * 对list数据源将其里面的数据导入到excel表单(EasyExcel)
  32. *
  33. * @param list 导出数据集合
  34. * @param sheetName 工作表的名称
  35. * @return 结果
  36. */
  37. public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) {
  38. try {
  39. String filename = encodingFilename(sheetName);
  40. response.reset();
  41. response.addHeader("Access-Control-Allow-Origin", "*");
  42. response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
  43. FileUtils.setAttachmentResponseHeader(response, URLEncoder.encode(filename, StandardCharsets.UTF_8.toString()));
  44. response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
  45. ServletOutputStream os = response.getOutputStream();
  46. EasyExcel.write(os, clazz)
  47. .autoCloseStream(false)
  48. // 自动适配
  49. .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
  50. .sheet(sheetName).doWrite(list);
  51. } catch (IOException e) {
  52. throw new RuntimeException("导出Excel异常");
  53. }
  54. }
  55. /**
  56. * 解析导出值 0=男,1=女,2=未知
  57. *
  58. * @param propertyValue 参数值
  59. * @param converterExp 翻译注解
  60. * @param separator 分隔符
  61. * @return 解析后值
  62. */
  63. public static String convertByExp(String propertyValue, String converterExp, String separator) {
  64. StringBuilder propertyString = new StringBuilder();
  65. String[] convertSource = converterExp.split(",");
  66. for (String item : convertSource) {
  67. String[] itemArray = item.split("=");
  68. if (StringUtils.containsAny(separator, propertyValue)) {
  69. for (String value : propertyValue.split(separator)) {
  70. if (itemArray[0].equals(value)) {
  71. propertyString.append(itemArray[1] + separator);
  72. break;
  73. }
  74. }
  75. } else {
  76. if (itemArray[0].equals(propertyValue)) {
  77. return itemArray[1];
  78. }
  79. }
  80. }
  81. return StringUtils.stripEnd(propertyString.toString(), separator);
  82. }
  83. /**
  84. * 反向解析值 男=0,女=1,未知=2
  85. *
  86. * @param propertyValue 参数值
  87. * @param converterExp 翻译注解
  88. * @param separator 分隔符
  89. * @return 解析后值
  90. */
  91. public static String reverseByExp(String propertyValue, String converterExp, String separator) {
  92. StringBuilder propertyString = new StringBuilder();
  93. String[] convertSource = converterExp.split(",");
  94. for (String item : convertSource) {
  95. String[] itemArray = item.split("=");
  96. if (StringUtils.containsAny(separator, propertyValue)) {
  97. for (String value : propertyValue.split(separator)) {
  98. if (itemArray[1].equals(value)) {
  99. propertyString.append(itemArray[0] + separator);
  100. break;
  101. }
  102. }
  103. } else {
  104. if (itemArray[1].equals(propertyValue)) {
  105. return itemArray[0];
  106. }
  107. }
  108. }
  109. return StringUtils.stripEnd(propertyString.toString(), separator);
  110. }
  111. /**
  112. * 解析字典值
  113. *
  114. * @param dictValue 字典值
  115. * @param dictType 字典类型
  116. * @param separator 分隔符
  117. * @return 字典标签
  118. */
  119. public static String convertDictByExp(String dictValue, String dictType, String separator) {
  120. return DictUtils.getDictLabel(dictType, dictValue, separator);
  121. }
  122. /**
  123. * 反向解析值字典值
  124. *
  125. * @param dictLabel 字典标签
  126. * @param dictType 字典类型
  127. * @param separator 分隔符
  128. * @return 字典值
  129. */
  130. public static String reverseDictByExp(String dictLabel, String dictType, String separator) {
  131. return DictUtils.getDictValue(dictType, dictLabel, separator);
  132. }
  133. /**
  134. * 编码文件名
  135. */
  136. public static String encodingFilename(String filename) {
  137. return IdUtil.fastSimpleUUID() + "_" + filename + ".xlsx";
  138. }
  139. }