From 7be17ea89a0f18e47bccd7560d4dacc82891eb82 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: 星期三, 11 八月 2021 10:13:34 +0800
Subject: [PATCH] Excel注解支持Image图片导入
---
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 246 +++++++++++++++++++++++++++++-----------
ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUtils.java | 97 ++++++++++++---
ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUploadUtils.java | 4
ruoyi-common/src/main/java/com/ruoyi/common/config/RuoYiConfig.java | 8 +
4 files changed, 264 insertions(+), 91 deletions(-)
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/config/RuoYiConfig.java b/ruoyi-common/src/main/java/com/ruoyi/common/config/RuoYiConfig.java
index c64ce71..843b919 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/config/RuoYiConfig.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/config/RuoYiConfig.java
@@ -91,6 +91,14 @@
}
/**
+ * 获取导入上传路径
+ */
+ public static String getImportPath()
+ {
+ return getProfile() + "/import";
+ }
+
+ /**
* 获取头像上传路径
*/
public static String getAvatarPath()
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUploadUtils.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUploadUtils.java
index 94a5d43..76b647a 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUploadUtils.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUploadUtils.java
@@ -127,7 +127,7 @@
return fileName;
}
- private static final File getAbsoluteFile(String uploadDir, String fileName) throws IOException
+ public static final File getAbsoluteFile(String uploadDir, String fileName) throws IOException
{
File desc = new File(uploadDir + File.separator + fileName);
@@ -141,7 +141,7 @@
return desc;
}
- private static final String getPathFileName(String uploadDir, String fileName) throws IOException
+ public static final String getPathFileName(String uploadDir, String fileName) throws IOException
{
int dirLastIndex = RuoYiConfig.getProfile().length() + 1;
String currentDir = StringUtils.substring(uploadDir, dirLastIndex);
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUtils.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUtils.java
index dfdf9e9..04ae9b1 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUtils.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/file/FileUtils.java
@@ -3,6 +3,7 @@
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
+import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
@@ -10,8 +11,12 @@
import java.nio.charset.StandardCharsets;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
+import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;
+import com.ruoyi.common.config.RuoYiConfig;
+import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
+import com.ruoyi.common.utils.uuid.IdUtils;
/**
* 文件处理工具类
@@ -53,29 +58,48 @@
}
finally
{
- if (os != null)
- {
- try
- {
- os.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
- }
- if (fis != null)
- {
- try
- {
- fis.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
- }
+ IOUtils.close(os);
+ IOUtils.close(fis);
}
+ }
+
+ /**
+ * 写数据到文件中
+ *
+ * @param data 数据
+ * @return 目标文件
+ * @throws IOException IO异常
+ */
+ public static String writeImportBytes(byte[] data) throws IOException
+ {
+ return writeBytes(data, RuoYiConfig.getImportPath());
+ }
+
+ /**
+ * 写数据到文件中
+ *
+ * @param data 数据
+ * @param uploadDir 目标文件
+ * @return 目标文件
+ * @throws IOException IO异常
+ */
+ public static String writeBytes(byte[] data, String uploadDir) throws IOException
+ {
+ FileOutputStream fos = null;
+ String pathName = "";
+ try
+ {
+ String extension = getFileExtendName(data);
+ pathName = DateUtils.datePath() + "/" + IdUtils.fastUUID() + "." + extension;
+ File file = FileUploadUtils.getAbsoluteFile(uploadDir, pathName);
+ fos = new FileOutputStream(file);
+ fos.write(data);
+ }
+ finally
+ {
+ IOUtils.close(fos);
+ }
+ return FileUploadUtils.getPathFileName(uploadDir, pathName);
}
/**
@@ -200,4 +224,33 @@
String encode = URLEncoder.encode(s, StandardCharsets.UTF_8.toString());
return encode.replaceAll("\\+", "%20");
}
+
+ /**
+ * 获取图像后缀
+ *
+ * @param photoByte 图像数据
+ * @return 后缀名
+ */
+ public static String getFileExtendName(byte[] photoByte)
+ {
+ String strFileExtendName = "jpg";
+ if ((photoByte[0] == 71) && (photoByte[1] == 73) && (photoByte[2] == 70) && (photoByte[3] == 56)
+ && ((photoByte[4] == 55) || (photoByte[4] == 57)) && (photoByte[5] == 97))
+ {
+ strFileExtendName = "gif";
+ }
+ else if ((photoByte[6] == 74) && (photoByte[7] == 70) && (photoByte[8] == 73) && (photoByte[9] == 70))
+ {
+ strFileExtendName = "jpg";
+ }
+ else if ((photoByte[0] == 66) && (photoByte[1] == 77))
+ {
+ strFileExtendName = "bmp";
+ }
+ else if ((photoByte[1] == 80) && (photoByte[2] == 78) && (photoByte[3] == 71))
+ {
+ strFileExtendName = "png";
+ }
+ return strFileExtendName;
+ }
}
diff --git a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
index f13b866..ccc7b20 100644
--- a/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
+++ b/ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java
@@ -18,6 +18,8 @@
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
+import javax.servlet.http.HttpServletResponse;
+import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
@@ -32,15 +34,23 @@
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
+import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
+import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
+import org.apache.poi.xssf.usermodel.XSSFDrawing;
+import org.apache.poi.xssf.usermodel.XSSFPicture;
+import org.apache.poi.xssf.usermodel.XSSFShape;
+import org.apache.poi.xssf.usermodel.XSSFSheet;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.ruoyi.common.annotation.Excel;
@@ -55,6 +65,7 @@
import com.ruoyi.common.utils.DictUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileTypeUtils;
+import com.ruoyi.common.utils.file.FileUtils;
import com.ruoyi.common.utils.file.ImageUtils;
import com.ruoyi.common.utils.reflect.ReflectUtils;
@@ -116,12 +127,12 @@
* 统计列表
*/
private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
-
+
/**
* 数字格式
*/
private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
-
+
/**
* 实体对象
*/
@@ -168,24 +179,15 @@
this.type = Type.IMPORT;
this.wb = WorkbookFactory.create(is);
List<T> list = new ArrayList<T>();
- Sheet sheet = null;
- if (StringUtils.isNotEmpty(sheetName))
- {
- // 如果指定sheet名,则取指定sheet中的内容.
- sheet = wb.getSheet(sheetName);
- }
- else
- {
- // 如果传入的sheet名不存在则默认指向第1个sheet.
- sheet = wb.getSheetAt(0);
- }
-
+ // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
+ Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
if (sheet == null)
{
throw new IOException("文件sheet不存在");
}
-
- int rows = sheet.getPhysicalNumberOfRows();
+ Map<String, PictureData> pictures = getSheetPictrues((XSSFSheet) sheet, (XSSFWorkbook) wb);
+ // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
+ int rows = sheet.getLastRowNum();
if (rows > 0)
{
@@ -225,11 +227,12 @@
}
}
}
- for (int i = 1; i < rows; i++)
+ for (int i = 1; i <= rows; i++)
{
// 从第2行开始取数据,默认第一行是表头.
Row row = sheet.getRow(i);
- if(row == null)
+ // 判断当前行是否是空行
+ if (isRowEmpty(row))
{
continue;
}
@@ -315,6 +318,20 @@
{
val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
}
+ else if (ColumnType.IMAGE == attr.cellType())
+ {
+ if (StringUtils.isNull(pictures))
+ {
+ val = "";
+ }
+ PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
+ if (image == null)
+ {
+ val = "";
+ }
+ byte[] data = image.getData();
+ val = FileUtils.writeImportBytes(data);
+ }
ReflectUtils.invokeSetter(entity, propertyName, val);
}
}
@@ -340,6 +357,23 @@
/**
* 对list数据源将其里面的数据导入到excel表单
*
+ * @param response 返回数据
+ * @param list 导出数据集合
+ * @param sheetName 工作表的名称
+ * @return 结果
+ * @throws IOException
+ */
+ public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException
+ {
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ this.init(list, sheetName, Type.EXPORT);
+ exportExcel(response.getOutputStream());
+ }
+
+ /**
+ * 对list数据源将其里面的数据导入到excel表单
+ *
* @param sheetName 工作表的名称
* @return 结果
*/
@@ -352,6 +386,43 @@
/**
* 对list数据源将其里面的数据导入到excel表单
*
+ * @param sheetName 工作表的名称
+ * @return 结果
+ */
+ public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException
+ {
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ this.init(null, sheetName, Type.IMPORT);
+ exportExcel(response.getOutputStream());
+ }
+
+ /**
+ * 对list数据源将其里面的数据导入到excel表单
+ *
+ * @return 结果
+ */
+ public void exportExcel(OutputStream out)
+ {
+ try
+ {
+ writeSheet();
+ wb.write(out);
+ }
+ catch (Exception e)
+ {
+ log.error("导出Excel异常{}", e.getMessage());
+ }
+ finally
+ {
+ IOUtils.closeQuietly(wb);
+ IOUtils.closeQuietly(out);
+ }
+ }
+
+ /**
+ * 对list数据源将其里面的数据导入到excel表单
+ *
* @return 结果
*/
public AjaxResult exportExcel()
@@ -359,27 +430,7 @@
OutputStream out = null;
try
{
- // 取出一共有多少个sheet.
- double sheetNo = Math.ceil(list.size() / sheetSize);
- for (int index = 0; index <= sheetNo; index++)
- {
- createSheet(sheetNo, index);
-
- // 产生一行
- Row row = sheet.createRow(0);
- int column = 0;
- // 写入各个字段的列头名称
- for (Object[] os : fields)
- {
- Excel excel = (Excel) os[1];
- this.createCell(excel, row, column++);
- }
- if (Type.EXPORT.equals(type))
- {
- fillExcelData(index, row);
- addStatisticsRow();
- }
- }
+ writeSheet();
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
@@ -392,27 +443,35 @@
}
finally
{
- if (wb != null)
+ IOUtils.closeQuietly(wb);
+ IOUtils.closeQuietly(out);
+ }
+ }
+
+ /**
+ * 创建写入数据到Sheet
+ */
+ public void writeSheet()
+ {
+ // 取出一共有多少个sheet.
+ double sheetNo = Math.ceil(list.size() / sheetSize);
+ for (int index = 0; index <= sheetNo; index++)
+ {
+ createSheet(sheetNo, index);
+
+ // 产生一行
+ Row row = sheet.createRow(0);
+ int column = 0;
+ // 写入各个字段的列头名称
+ for (Object[] os : fields)
{
- try
- {
- wb.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
+ Excel excel = (Excel) os[1];
+ this.createCell(excel, row, column++);
}
- if (out != null)
+ if (Type.EXPORT.equals(type))
{
- try
- {
- out.close();
- }
- catch (IOException e1)
- {
- e1.printStackTrace();
- }
+ fillExcelData(index, row);
+ addStatisticsRow();
}
}
}
@@ -484,7 +543,7 @@
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
-
+
style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
@@ -548,8 +607,7 @@
}
else if (ColumnType.IMAGE == attr.cellType())
{
- ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1),
- cell.getRow().getRowNum() + 1);
+ ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
String imagePath = Convert.toStr(value);
if (StringUtils.isNotEmpty(imagePath))
{
@@ -559,7 +617,7 @@
}
}
}
-
+
/**
* 获取画布
*/
@@ -802,7 +860,7 @@
}
return StringUtils.stripEnd(propertyString.toString(), separator);
}
-
+
/**
* 解析字典值
*
@@ -828,7 +886,7 @@
{
return DictUtils.getDictValue(dictType, dictLabel, separator);
}
-
+
/**
* 合计统计信息
*/
@@ -859,13 +917,12 @@
{
if (statistics.size() > 0)
{
- Cell cell = null;
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
Set<Integer> keys = statistics.keySet();
- cell = row.createCell(0);
+ Cell cell = row.createCell(0);
cell.setCellStyle(styles.get("total"));
cell.setCellValue("合计");
-
+
for (Integer key : keys)
{
cell = row.createCell(key);
@@ -983,7 +1040,7 @@
this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
this.maxHeight = getRowHeight();
}
-
+
/**
* 根据注解获取最大行高
*/
@@ -1097,4 +1154,59 @@
}
return val;
}
+
+ /**
+ * 判断是否是空行
+ *
+ * @param row 判断的行
+ * @return
+ */
+ private boolean isRowEmpty(Row row)
+ {
+ if (row == null)
+ {
+ return true;
+ }
+ for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
+ {
+ Cell cell = row.getCell(i);
+ if (cell != null && cell.getCellType() != CellType.BLANK)
+ {
+ return false;
+ }
+ }
+ return true;
+ }
+
+ /**
+ * 获取Excel图片
+ *
+ * @param sheet 当前sheet对象
+ * @param workbook 工作簿对象
+ * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
+ */
+ public static Map<String, PictureData> getSheetPictrues(XSSFSheet sheet, XSSFWorkbook workbook)
+ {
+ Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
+ for (POIXMLDocumentPart dr : sheet.getRelations())
+ {
+ if (dr instanceof XSSFDrawing)
+ {
+ XSSFDrawing drawing = (XSSFDrawing) dr;
+ List<XSSFShape> shapes = drawing.getShapes();
+ for (XSSFShape shape : shapes)
+ {
+ if (shape instanceof XSSFPicture)
+ {
+ XSSFPicture pic = (XSSFPicture) shape;
+ XSSFClientAnchor anchor = pic.getPreferredSize();
+ CTMarker ctMarker = anchor.getFrom();
+ String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
+ sheetIndexPicMap.put(picIndex, pic.getPictureData());
+ }
+ }
+ }
+ }
+ return sheetIndexPicMap;
+ }
}
\ No newline at end of file
--
Gitblit v1.9.2