From a5e38f6f9993ad34c3d0566762d71d1093ef88d4 Mon Sep 17 00:00:00 2001
From: RuoYi <yzz_ivy@163.com>
Date: 星期三, 08 九月 2021 09:28:23 +0800
Subject: [PATCH] 菜单管理支持配置路由参数
---
ruoyi-common/src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java | 303 ++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 233 insertions(+), 70 deletions(-)
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 dcba68e..54157f3 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,14 @@
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
+import javax.servlet.http.HttpServletResponse;
+import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
+import org.apache.poi.hssf.usermodel.HSSFPicture;
+import org.apache.poi.hssf.usermodel.HSSFPictureData;
+import org.apache.poi.hssf.usermodel.HSSFShape;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+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 +40,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;
@@ -50,11 +66,12 @@
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.text.Convert;
-import com.ruoyi.common.exception.CustomException;
+import com.ruoyi.common.exception.UtilException;
import com.ruoyi.common.utils.DateUtils;
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 +133,12 @@
* 统计列表
*/
private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
-
+
/**
* 数字格式
*/
private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
-
+
/**
* 实体对象
*/
@@ -168,24 +185,24 @@
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();
+ boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
+ Map<String, PictureData> pictures;
+ if (isXSSFWorkbook)
+ {
+ pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
+ }
+ else
+ {
+ pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
+ }
+ // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
+ int rows = sheet.getLastRowNum();
if (rows > 0)
{
@@ -225,10 +242,15 @@
}
}
}
- for (int i = 1; i < rows; i++)
+ for (int i = 1; i <= rows; i++)
{
// 从第2行开始取数据,默认第一行是表头.
Row row = sheet.getRow(i);
+ // 判断当前行是否是空行
+ if (isRowEmpty(row))
+ {
+ continue;
+ }
T entity = null;
for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet())
{
@@ -311,6 +333,16 @@
{
val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
}
+ else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))
+ {
+ 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);
}
}
@@ -336,6 +368,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 结果
*/
@@ -348,6 +397,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()
@@ -355,27 +441,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);
@@ -384,31 +450,39 @@
catch (Exception e)
{
log.error("导出Excel异常{}", e.getMessage());
- throw new CustomException("导出Excel失败，请联系网站管理员！");
+ throw new UtilException("导出Excel失败，请联系网站管理员！");
}
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();
}
}
}
@@ -480,7 +554,7 @@
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
-
+
style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
@@ -537,12 +611,14 @@
}
else if (ColumnType.NUMERIC == attr.cellType())
{
- cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
+ if (StringUtils.isNotNull(value))
+ {
+ cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
+ }
}
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))
{
@@ -552,7 +628,7 @@
}
}
}
-
+
/**
* 获取画布
*/
@@ -795,7 +871,7 @@
}
return StringUtils.stripEnd(propertyString.toString(), separator);
}
-
+
/**
* 解析字典值
*
@@ -821,7 +897,7 @@
{
return DictUtils.getDictValue(dictType, dictLabel, separator);
}
-
+
/**
* 合计统计信息
*/
@@ -852,13 +928,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);
@@ -976,7 +1051,7 @@
this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
this.maxHeight = getRowHeight();
}
-
+
/**
* 根据注解获取最大行高
*/
@@ -1090,4 +1165,92 @@
}
return val;
}
-}
\ No newline at end of file
+
+ /**
+ * 判断是否是空行
+ *
+ * @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;
+ }
+
+ /**
+ * 获取Excel2003图片
+ *
+ * @param sheet 当前sheet对象
+ * @param workbook 工作簿对象
+ * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
+ */
+ public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
+ {
+ Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
+ List<HSSFPictureData> pictures = workbook.getAllPictures();
+ if (!pictures.isEmpty())
+ {
+ for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
+ {
+ HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
+ if (shape instanceof HSSFPicture)
+ {
+ HSSFPicture pic = (HSSFPicture) shape;
+ int pictureIndex = pic.getPictureIndex() - 1;
+ HSSFPictureData picData = pictures.get(pictureIndex);
+ String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
+ sheetIndexPicMap.put(picIndex, picData);
+ }
+ }
+ return sheetIndexPicMap;
+ }
+ else
+ {
+ return sheetIndexPicMap;
+ }
+ }
+
+ /**
+ * 获取Excel2007图片
+ *
+ * @param sheet 当前sheet对象
+ * @param workbook 工作簿对象
+ * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
+ */
+ public static Map<String, PictureData> getSheetPictures07(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;
+ }
+}
--
Gitblit v1.9.2