Excel导入和导出
一、前期准备
1、首先导入主要的依赖
<dependencies>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.7.0</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.7.0</version>
</dependency>
<!-- 文件上传 -->
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpmime</artifactId>
<version>4.5.13</version>
</dependency>
<!-- Json -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.78</version>
</dependency>
<!-- POI 包 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
</dependencies>
2、编写swagger配置类
这里需要注意SpringBoot的版本我的版本为2.1.3.RELEASE
@Configuration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket docket() {
return new Docket(DocumentationType.SWAGGER_2)
.groupName("默认模块")
.globalOperationParameters(Collections.emptyList()).apiInfo(getApiInfo())
.enable(true).select()
.apis(RequestHandlerSelectors.basePackage("com.excel.controller")).build();
}
private ApiInfo getApiInfo() {
String title = "Excel导入导出示例（基于 ExcelUtils 2.0 版本）";
String description = "Excel导入导出示例";
String version = "v2.0";
Contact contact = new Contact("", "", "");
String license = "ExcelUtils 2.0 版本";
String licenseUrl = "";
ApiInfo apiInfo = new ApiInfo(title, description, version, "", contact, license, licenseUrl,
Collections.emptyList());
return apiInfo;
}
}
}
3、导入ExcelUtils工具类
/**
* @author ysl
*/
@SuppressWarnings("unused")
public class ExcelUtils {
private static <E> List<E> getList(List<E> list) {
return list == null ? Collections.emptyList() : list;
}
/**
* 判断集合是否为空
*
* @param c 集合对象
* @return true-为空，false-不为空
*/
private static <E> boolean isEmpty(Collection<E> c) {
return c == null || c.isEmpty();
}
/**
* 判断字符串是否为空
*
* @param c 集合对象
* @return true-为空，false-不为空
*/
private static boolean isEmpty(String c) {
return c == null || c.trim().isEmpty();
}
private static <K, V> boolean isEmpty(Map<K, V> map) {
return map == null || map.isEmpty();
}
private static <K, V> boolean notEmpty(Map<K, V> map) {
return !isEmpty(map);
}
private static boolean notEmpty(String c) {
return !isEmpty(c);
}
/**
* 导出文件到本地
*
* @param file 本地excel文件，如（C:\excel\用户表.xlsx）
* @param list 导出数据列表
* @param c 泛型类
* @param <T> 泛型
*/
public static <T> void export(File file, List<T> list, Class<T> c, List<ExportCellMerge> mergers) {
checkExportFile(file);
ExportWorkBook myWorkBook = getExportWorkBook(file.getName(), list, c, mergers);
Workbook xssfWorkbook = getExportXSSFWorkbook(myWorkBook);
export(xssfWorkbook, file);
}
/**
* 导出文件到响应
*
* @param response 响应对象
* @param fileName 导出文件名称（不带尾缀）
* @param list 导出数据列表
* @param c 泛型类
* @param <T> 泛型
*/
public static <T> void export(HttpServletResponse response, String fileName, List<T> list, Class<T> c) {
ExportWorkBook myWorkBook = getExportWorkBook(fileName, list, c, null);
Workbook xssfWorkbook = getExportXSSFWorkbook(myWorkBook);
export(response, xssfWorkbook, fileName);
}
/**
* 导出文件到响应
*
* @param response 响应对象
* @param fileName 导出文件名称（不带尾缀）
* @param list 导出数据列表
* @param c 泛型类
* @param <T> 泛型
*/
public static <T> void export(HttpServletResponse response, String fileName, List<T> list, Class<T> c, List<ExportCellMerge> mergers) {
ExportWorkBook myWorkBook = getExportWorkBook(fileName, list, c, mergers);
myWorkBook.setName(fileName);
export(response, fileName, myWorkBook);
}
public static <T> void export(HttpServletResponse response, String fileName, List<ExportSheet> sheets) {
ExportWorkBook myWorkBook = new ExportWorkBook();
myWorkBook.setName(fileName);
myWorkBook.setSheets(sheets);
export(response, fileName, myWorkBook);
}
private static void export(HttpServletResponse response, String fileName, ExportWorkBook myWorkBook) {
Workbook xssfWorkbook = getExportXSSFWorkbook(myWorkBook);
export(response, xssfWorkbook, fileName);
}
/**
* 将工作簿导出到本地文件
*
* @param workbook POI工作簿对象
* @param file 本地文件对象
*/
private static void export(Workbook workbook, File file) {
FileOutputStream fos;
try {
fos = new FileOutputStream(file);
ByteArrayOutputStream ops = new ByteArrayOutputStream();
workbook.write(ops);
fos.write(ops.toByteArray());
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 将工作簿导出到响应
*
* @param response 响应流对象
* @param workbook POI工作簿对象
* @param fileName 文件名称
*/
private static void export(HttpServletResponse response, Workbook workbook, String fileName) {
try {
if (isEmpty(fileName)) {
fileName = System.currentTimeMillis() + "";
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(StandardCharsets.UTF_8.displayName());
String name = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1) + ExcelType.XLSX.val;
response.addHeader("Content-Disposition", "attachment;filename=" + name);
ServletOutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取导出表格对象
*
* @param fileName 文件全名
* @param list 导出对象集合
* @param c 泛型类
* @param <T> 泛型
* @return ExportWorkBook
*/
private static <T> ExportWorkBook getExportWorkBook(String fileName, List<T> list, Class<T> c, List<ExportCellMerge> merges) {
// ExportSheet
List<ExportSheet> mySheets = new ArrayList<>();
ExportSheet mySheet = ExportSheetFactory.createExportSheet(list, c, merges);
if (isEmpty(mySheet.getName())) {
mySheet.setName(getFileName(fileName));
}
mySheets.add(mySheet);
// ExportWorkBook
ExportWorkBook myBook = new ExportWorkBook();
myBook.setName(fileName);
myBook.setSheets(mySheets);
return myBook;
}
/**
* 将工作簿导出到本地文件
*
* @param myWorkBook 导出工作簿对象
* @return POI导出工作簿对象
*/
private static Workbook getExportXSSFWorkbook(ExportWorkBook myWorkBook) {
Workbook workbook = new XSSFWorkbook();
List<ExportSheet> mySheets = myWorkBook.getSheets();
for (ExportSheet mySheet : mySheets) {
// 默认样式
CellStyle headerStyle = getDefaultHeaderStyle(workbook);
CellStyle dataStyle = getDefaultDataStyle(workbook);
// 自定义样式
setCellStyle(headerStyle, mySheet.getHeaderStyle());
setCellStyle(dataStyle, mySheet.getDataStyle());
// Sheet：数据
Sheet sheet = workbook.createSheet(mySheet.getName());
// Sheet：样式
setSheetValue(sheet, mySheet, headerStyle, dataStyle);
// Sheet：批注
setSheetComment(sheet, mySheet);
// Sheet：冻结表头
setFreezeHeader(sheet, mySheet);
// Sheet：水印
setWatermark(workbook, sheet, mySheet);
// Sheet：设置宽高
setColumnWidth(sheet, mySheet);
// Sheet：合并单元格
mergeSheetCell(sheet, mySheet);
}
return workbook;
}
private static void mergeSheetCell(Sheet sheet, ExportSheet mySheet) {
if (mySheet.getMerges() != null && !mySheet.getMerges().isEmpty()) {
for (ExportCellMerge merge : mySheet.getMerges()) {
sheet.addMergedRegion(new CellRangeAddress(merge.getY1(), merge.getY2(), merge.getX1(), merge.getX2()));
}
}
}
private static void setColumnWidth(Sheet sheet, ExportSheet mySheet) {
// 全局列宽（默认15个字符）
int globalWidth = mySheet.getColumnWidth();
if (globalWidth <= 0) {
globalWidth = 15;
}
// 指定列宽
Map<Integer, Integer> columnIndexWidthMap = mySheet.getColumnIndexWidthMap();
if (notEmpty(columnIndexWidthMap)) {
for (Map.Entry<Integer, Integer> entry : columnIndexWidthMap.entrySet()) {
int columnIndex = entry.getKey();
int columnWidth = entry.getValue();
if (columnWidth <= 0) {
columnWidth = globalWidth;
}
sheet.setColumnWidth(columnIndex, columnWidth * 256);
}
}
}
private static void setWatermark(Workbook workbook, Sheet sheet, ExportSheet mySheet) {
ExportWatermark watermark = mySheet.getWatermark();
if (watermark == null) {
return;
}
int type = watermark.getType();
String src = watermark.getSrc();
if (!WatermarkType.containCode(type) || type == WatermarkType.NONE.code || ExcelUtils.isEmpty(src)) {
return;
}
byte[] imageBytes = getWaterMarkImageBytes(type, src);
if (imageBytes == null) {
return;
}
XSSFSheet xssfSheet = (XSSFSheet) sheet;
InputStream inputStream = new ByteArrayInputStream(imageBytes);
int pictureIndex = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
XSSFPictureData watermarkPictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIndex);
PackagePartName ppn = watermarkPictureData.getPackagePart().getPartName();
PackageRelationship packageRelationship = xssfSheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, XSSFRelation.IMAGES.getRelation(), null);
CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
CTSheetBackgroundPicture backgroundPicture = ctWorksheet.addNewPicture();
System.out.println(packageRelationship.getId());
backgroundPicture.setId(packageRelationship.getId());
}
private static byte[] getWaterMarkImageBytes(int type, String src) {
try {
if (type == WatermarkType.TEXT.code) {
// 文本文字
return getImageBytesFromText(src);
}
if (type == WatermarkType.FILE.code) {
// 本地图片
return getImageBytesFromFile(src);
}
if (type == WatermarkType.URL.code) {
// 网络图片
return getImageBytesFromUrl(src);
}
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static byte[] getImageBytesFromFile(String imageFilePath) throws IOException {
File file = new File(imageFilePath);
if (!file.exists()) {
throw new RuntimeException(String.format("水印图片%s不存在", imageFilePath));
}
// 文件限制为10MB下的图片
int allowMaxMb = 10;
int allowMaxByte = 10 * 1024 * 1024;
if (file.length() > allowMaxByte) {
throw new RuntimeException(String.format("水印图片%s不能超过%sMB", imageFilePath, allowMaxMb));
}
// 水印图片限制为 png,jpg,jpeg
List<String> allowTypes = Arrays.asList(".png", ".jpg", ".jpeg");
boolean isMatchType = false;
String fileName = file.getName();
for (String allowType : allowTypes) {
if (fileName.endsWith(allowType)) {
isMatchType = true;
break;
}
}
if (!isMatchType) {
throw new RuntimeException(String.format("水印图片%s格式不正确，请填写%s格式的图片", imageFilePath, allowTypes));
}
// 获取图片文件二进制流
return FileUtils.readFileToByteArray(file);
}
private static byte[] getImageBytesFromText(String text) throws IOException {
int width = 400;
int height = 300;
// 创建画板
BufferedImage bufferedImage = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
// 创建画笔
Graphics2D g2d = bufferedImage.createGraphics();
// 绘制透明背景
bufferedImage = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = bufferedImage.createGraphics();
// 设置绘制颜色和旋转角度
g2d.setColor(new Color(129, 12, 187, 80));
g2d.setStroke(new BasicStroke(1));
Font font = new Font(null, Font.BOLD, 40);
g2d.setFont(font);
g2d.rotate(-0.5, (double) bufferedImage.getWidth() / 2, (double) bufferedImage.getHeight() / 2);
// 获取文本边界和绘制位置
FontRenderContext context = g2d.getFontRenderContext();
Rectangle2D bounds = font.getStringBounds(text, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 绘制文本
g2d.drawString(text, (int) x, (int) baseY);
// 保存绘制结果，并释放资源
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
g2d.dispose();
// 将 BufferedImage 转换为字节数组
ByteArrayOutputStream os = new ByteArrayOutputStream();
ImageIO.write(bufferedImage, "png", os);
return os.toByteArray();
}
private static byte[] getImageBytesFromUrl(String imageUrl) throws IOException {
URL url = new URL(imageUrl);
try (InputStream is = url.openStream();
ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
byte[] buffer = new byte[1024];
int bytesRead;
while ((bytesRead = is.read(buffer)) != -1) {
bos.write(buffer, 0, bytesRead);
}
return bos.toByteArray();
}
}
private static void setFreezeHeader(Sheet sheet, ExportSheet mySheet) {
if (!mySheet.freezeHeader) {
return;
}
int headerRows = 0;
List<ExportRow> rows = mySheet.getRows();
for (ExportRow row : rows) {
if (!row.isHeader()) {
break;
}
headerRows++;
}
sheet.createFreezePane(0, headerRows);
}
private static CellStyle getDefaultHeaderStyle(Workbook workbook) {
CellStyle headerStyle = workbook.createCellStyle();
// 默认表头内容对齐为：居中显示
headerStyle.setAlignment(HorizontalAlignment.CENTER);
// 默认表头背景为：淡灰色（填充方式为全填充）
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
return headerStyle;
}
private static CellStyle getDefaultDataStyle(Workbook workbook) {
CellStyle dataStyle = workbook.createCellStyle();
// 默认数据内容对齐为：居左显示
dataStyle.setAlignment(HorizontalAlignment.CENTER);
return dataStyle;
}
/**
* 自定义样式转化为表格样式
*
* @param style POI样式
* @param myStyle 自定义样式
*/
private static void setCellStyle(CellStyle style, ExportCellStyle myStyle) {
if (myStyle == null) {
return;
}
if (myStyle.getBackgroundColor() > 0) {
style.setFillBackgroundColor(myStyle.getBackgroundColor());
}
}
/**
* 设置批注
*
* @param poiSheet POI页Sheet对象
* @param mySheet 导出Sheet对象
*/
private static void setSheetComment(Sheet poiSheet, ExportSheet mySheet) {
if (isEmpty(mySheet.getComments())) {
return;
}
Drawing<?> drawing = poiSheet.createDrawingPatriarch();
for (ExportComment myComment : mySheet.getComments()) {
int x = myComment.getX();
int y = myComment.getY();
String commentStr = myComment.getComment();
XSSFClientAnchor xssfClientAnchor = new XSSFClientAnchor(0, 0, 0, 0, x, y, x + 2, y + 6);
xssfClientAnchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
Comment comment = drawing.createCellComment(xssfClientAnchor);
comment.setString(new XSSFRichTextString(commentStr));
poiSheet.getRow(y).getCell(x).setCellComment(comment);
}
}
/**
* 设置Sheet数据
*
* @param sheet POI页Sheet对象
* @param mySheet 导出Sheet对象
*/
private static void setSheetValue(Sheet sheet, ExportSheet mySheet, CellStyle headerStyle, CellStyle dataStyle) {
List<ExportRow> myRows = getList(mySheet.getRows());
for (int j = 0; j < myRows.size(); j++) {
ExportRow myRow = myRows.get(j);
List<ExportCell> myCells = getList(myRow.getCells());
Row row = sheet.createRow(j);
CellStyle tempStyle = myRow.isHeader() ? headerStyle : dataStyle;
for (int k = 0; k < myCells.size(); k++) {
ExportCell myCell = myCells.get(k);
Cell cell = row.createCell(k);
setCellValue(cell, myCell);
cell.setCellStyle(tempStyle);
}
}
}
/**
* 设置单元格数据
*
* @param cell POI单元格对象
* @param myCell 导出单元格对象
*/
private static void setCellValue(Cell cell, ExportCell myCell) {
// 无值情况，返回空字符串
Object o = myCell.getValue();
if (Objects.isNull(o) || isEmpty(o.toString())) {
cell.setCellValue("");
return;
}
// 字符串类型
if (o instanceof String) {
cell.setCellValue((String) o);
return;
}
// Integer类型
if (o instanceof Integer) {
cell.setCellValue((Integer) o);
return;
}
// Long类型
if (o instanceof Long) {
// 数字类型默认超过10位，excel默认显示科学计数法，因此长度超过10位，则显示为文本格式
int length = o.toString().length();
if (length > 10) {
cell.setCellValue(o.toString());
} else {
cell.setCellValue((Long) o);
}
return;
}
// Double
if (o instanceof Double || o instanceof Float || o instanceof BigDecimal) {
// 去除科学计数法显示
BigDecimal b = new BigDecimal(o.toString());
String s = b.toPlainString();
int length = s.length();
if (length > 10) {
cell.setCellValue(s);
} else {
cell.setCellValue(b.doubleValue());
}
return;
}
// 日期格式
if (o instanceof Date) {
Date date = (Date) o;
String formatValue;
String dateFormat = myCell.getDateFormat();
if (isEmpty(dateFormat)) {
formatValue = new SimpleDateFormat(dateFormat).format("yyyy-MM-dd HH:mm:ss");
} else {
if ("ts".equals(dateFormat)) {
formatValue = String.valueOf(date.getTime());
} else if ("tms".equals(dateFormat)) {
formatValue = String.valueOf(date.getTime() / 1000);
} else {
formatValue = new SimpleDateFormat(dateFormat).format(date);
}
}
cell.setCellValue(formatValue);
return;
}
// 其他格式
cell.setCellValue(o.toString());
}
/**
* 获取文件名称（不带文件尾缀）
*
* @param fileFullName 文件名称（带尾缀）
* @return 文件名称（不带尾缀）
*/
private static String getFileName(String fileFullName) {
int lastDotIndex = fileFullName.lastIndexOf(".");
if (lastDotIndex != -1) {
return fileFullName.substring(0, lastDotIndex);
}
return fileFullName;
}
/**
* 检测导出文件是否OK
*
* @param file 到处在excel文件
*/
public static void checkExportFile(File file) {
if (Objects.isNull(file)) {
throw new RuntimeException("文件不能为空");
}
if (file.exists()) {
return;
}
String name = file.getName();
if (!name.endsWith(ExcelType.XLSX.val)) {
throw new RuntimeException("导出的excel文件请为xlsx类型的文件");
}
File parentFile = file.getParentFile();
if (Objects.nonNull(parentFile) && !parentFile.exists()) {
boolean mkdirs = parentFile.mkdirs();
if (!mkdirs) {
throw new RuntimeException("父级文件夹创建失败");
}
}
try {
boolean createNewFile = file.createNewFile();
if (!createNewFile) {
throw new RuntimeException("文件创建失败");
}
} catch (IOException e) {
throw new RuntimeException("文件创建失败");
}
}
/**
* 读取excel文件，返回文件第一个sheet页
*
* @param file excel文件
* @return 第一个sheet页
*/
public static Sheet readSheet(File file) {
return readManySheet(file).get(0);
}
/**
* 读取excel文件，返回文件第一个sheet页
*
* @param file excel文件
* @return 第一个sheet页
*/
public static Sheet readSheet(MultipartFile file) {
return readManySheet(file).get(0);
}
/**
* 读取excel文件，返回所有sheet页
*
* @param file excel文件
* @return 所有sheet页
*/
public static List<Sheet> readManySheet(File file) {
Workbook workbook = getWorkBook(file);
return getSheets(workbook);
}
/**
* 读取excel文件，返回所有sheet页
*
* @param file excel文件
* @return 所有sheet页
*/
public static List<Sheet> readManySheet(MultipartFile file) {
Workbook workbook = getWorkBook(file);
return getSheets(workbook);
}
/**
* 读取exel文件，返回第一个 sheet 页数据，以JSONArray格式返回（JSONArray中每个元素为每行数据）
*
* @param file excel文件
* @param containKey 是否需要包含键
* （1）当为true时，JSONArray中每个元素为JSONObject对象，其中键为表头(默认第一行为表头)，值为单元格数据，
* 例如：[{"k1":"v1","k2":"v2"},{"k1":"v3","k2":"v4"}]
* （2）当为false时，JSONArray中每个元素为每行单元格数据，位置和文件打开时的视图一一对应
* 例如：[["k1","k2"],["v1","v2"],["v3","v4"]]
* @return JSONArray对象
*/
public static JSONArray read(File file, boolean containKey) {
Sheet sheet = readSheet(file);
return pares(sheet, containKey);
}
/**
* 读取exel文件，返回第一个 sheet 页数据，以JSONArray格式返回（JSONArray中每个元素为每行数据）
*
* @param file excel文件
* @param containKey 是否需要包含键
* （1）当为true时，JSONArray中每个元素为JSONObject对象，其中键为表头(默认第一行为表头)，值为单元格数据，
* 例如：[{"k1":"v1","k2":"v2"},{"k1":"v3","k2":"v4"}]
* （2）当为false时，JSONArray中每个元素为每行单元格数据，位置和文件打开时的视图一一对应
* 例如：[["k1","k2"],["v1","v2"],["v3","v4"]]
* @return JSONArray对象
*/
public static JSONArray read(MultipartFile file, boolean containKey) {
Sheet sheet = readSheet(file);
return pares(sheet, containKey);
}
/**
* 读取excel文件，返回第一个sheet页数据，以List<Java对象>格式返回（默认第一行为表头）
*
* @param file excel文件
* @param <T> 泛型对象
* @return List<Java对象>
*/
public static <T> List<T> read(File file, Class<T> c) {
Sheet sheet = readSheet(file);
return pares(sheet, c);
}
/**
* 读取excel文件，返回第一个sheet页数据，以List<Java对象>格式返回（默认第一行为表头）
*
* @param file excel文件
* @param <T> 泛型对象
* @return List<Java对象>
*/
public static <T> List<T> read(MultipartFile file, Class<T> c) {
Sheet sheet = readSheet(file);
return pares(sheet, c);
}
/**
* 解析sheet数据，并返回Java对象
*
* @param sheet sheet对象
* @param <T> 泛型对象
* @return java对象
*/
public static <T> List<T> pares(Sheet sheet, Class<T> c) {
if (Objects.isNull(sheet) || Objects.isNull(c) || sheet.getLastRowNum() < 0) {
return Collections.emptyList();
}
// 取得该类所有打了@ExcelImport注解的属性
List<ImportClassField> importFields = getImportFields(c);
if (isEmpty(importFields)) {
return Collections.emptyList();
}
// 如果有columnIndex属性，则优先按columnIndex进行解析（这个解析不需要表头）
if (hasColumnIndex(importFields)) {
return pares(sheet, c, importFields, 0);
}
// 存在表头的话，先补充其他字段的表头下标，再进行解析（因为最终都是按列进行解析的）
for (Cell cell : sheet.getRow(0)) {
String value = getCellStringValue(cell);
if (!isEmpty(value)) {
for (ImportClassField field : importFields) {
if (field.getColumnIndex() < 0 && value.equals(field.getColumnName())) {
field.setColumnIndex(cell.getColumnIndex());
}
}
}
}
return pares(sheet, c, importFields, 1);
}
/**
* 解析sheet数据，并返回Java对象
*
* @param sheet sheet对象
* @param importFields 本次需要解析的列
* @param rowStartIndex 开始解析的行数
* @param <T> 泛型对象
* @return Java对象
*/
private static <T> List<T> pares(Sheet sheet, Class<T> c, List<ImportClassField> importFields, int rowStartIndex) {
// 属性按表头索引进行区分
Map<Integer, ImportClassField> indexFieldMap = new LinkedHashMap<>();
for (ImportClassField field : importFields) {
if (field.getColumnIndex() >= 0) {
indexFieldMap.put(field.getColumnIndex(), field);
}
}
// 开始解析行下标不可大于sheet最大行下标
int lastRowNum = sheet.getLastRowNum();
if (rowStartIndex > lastRowNum) {
return Collections.emptyList();
}
// 开始解析
List<T> ts = new ArrayList<>();
for (int i = rowStartIndex; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
// 判断是否为空行，非空行才进行数据解析
if (!isBlankRow(row)) {
try {
T t = getBean(c, row, indexFieldMap);
ts.add(t);
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
}
return ts;
}
/**
* 行数据转化为java对象
*
* @param c java对象类
* @param row 行数据对象
* @param indexFieldMap 关联的属性
* @return java对象
*/
private static <T> T getBean(Class<T> c, Row row, Map<Integer, ImportClassField> indexFieldMap) throws Exception {
T t = c.newInstance();
for (Map.Entry<Integer, ImportClassField> entry : indexFieldMap.entrySet()) {
int columnIndex = entry.getKey();
ImportClassField field = entry.getValue();
Cell cell = row.getCell(columnIndex);
setFieldValue(t, cell, field);
}
return t;
}
/**
* 对象属性赋值
*
* @param t java对象
* @param cell 单元格对象
*/
private static <T> void setFieldValue(T t, Cell cell, ImportClassField importClassField) {
String value = getCellStringValue(cell);
if (isEmpty(value)) {
return;
}
// 映射取值
LinkedHashMap<String, String> kvMap = importClassField.getKvMap();
if (!kvMap.isEmpty()) {
for (Map.Entry<String, String> entry : kvMap.entrySet()) {
if (entry.getValue().equals(value)) {
value = entry.getKey();
break;
}
}
}
Field field = importClassField.getField();
Class<?> type = field.getType();
try {
// 按照使用频率优先级赋值判断（包含: String + 8大基本数据类型 + Date + BigDecimal）
if (type == String.class) {
field.set(t, value);
} else if (type == int.class || type == Integer.class) {
field.set(t, Integer.parseInt(value));
} else if (type == long.class || type == Long.class) {
field.set(t, Long.parseLong(value));
} else if (type == Date.class) {
String dateFormat = importClassField.getDateFormat();
Date date = getDate(cell, dateFormat);
field.set(t, date);
} else if (type == double.class || type == Double.class) {
field.set(t, Double.parseDouble(value));
} else if (type == boolean.class || type == Boolean.class) {
field.set(t, Boolean.parseBoolean(value));
} else if (type == BigDecimal.class) {
field.set(t, new BigDecimal(value));
} else if (type == float.class || type == Float.class) {
field.set(t, Float.parseFloat(value));
} else if (type == short.class || type == Short.class) {
field.set(t, Short.parseShort(value));
} else if (type == char.class || type == Character.class) {
field.set(t, value.charAt(0));
} else if (type == byte.class || type == Byte.class) {
field.set(t, Byte.parseByte(value));
}
// 其他数据类型根据需要自行添加处理
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/**
* 读取时间类型
*
* @return 时间
*/
private static Date getDate(Cell cell, String dateFormat) {
CellType cellType = cell.getCellType();
// 如果单元格自己能够识别这是一个date类型数据，则忽略用户格式样式，直接返回
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
// 获取表格数据
String value = getCellStringValue(cell);
// 如果是纯数字，则为时间戳
if (Pattern.matches("\\d+", value)) {
long timestamp = Long.parseLong(value);
// 长度大于10的时候，则为毫秒级时间戳，毫秒级时间戳需要去掉最后三位
if (value.length() > 10) {
timestamp = timestamp / 1000;
}
return new Date(timestamp);
}
// 下面按照时间格式进行格式化（如果用户填写了时间格式，则优先以用户格式进行校验）
Date date = null;
if (!isEmpty(dateFormat)) {
date = parseDate(value, dateFormat);
}
// 尝试时间是否为yyyy-MM-dd HH:mm:ss格式
if (Objects.isNull(date)) {
date = parseDate(value, "yyyy-MM-dd HH:mm:ss");
}
// 尝试时间是否为yyyy-MM-dd格式
if (Objects.isNull(date)) {
date = parseDate(value, "yyyy-MM-dd");
}
// 如果还想进行其他时间解析尝试，可以在此自行添加
return date;
}
/**
* 将字符串格式日期转化为Date时间
*
* @param dateStr 字符串日期
* @param formatStyle 格式化样式
* @return Date时间
*/
private static Date parseDate(String dateStr, String formatStyle) {
try {
return new SimpleDateFormat(formatStyle).parse(dateStr);
} catch (ParseException e) {
return null;
}
}
/**
* 判断是否为空行
*
* @param row 当前行数据
* @return true-是空行，false-不是空行
*/
private static boolean isBlankRow(Row row) {
for (Cell cell : row) {
if (!isEmpty(getCellStringValue(cell))) {
return false;
}
}
return true;
}
/**
* 判断本次导入的对象中是否存在按索性进行解析的
*
* @param importFields 本次导入对象的属性
* @return true-存在；false-不存在
*/
private static boolean hasColumnIndex(List<ImportClassField> importFields) {
for (ImportClassField field : importFields) {
if (field.getColumnIndex() >= 0) {
return true;
}
}
return false;
}
/**
* 获取指定类中所有打了ExcelImport注解的属性（包括父类、祖类）
*
* @param c 指定类
* @return 该类所有打了ExcelImport注解的属性（包括父类、祖类）
*/
private static List<ImportClassField> getImportFields(Class<?> c) {
List<ImportClassField> excelImportClassFields = new ArrayList<>();
List<Field> allClassFields = getAllFields(c);
for (Field field : allClassFields) {
ExcelImport excelImport = field.getAnnotation(ExcelImport.class);
if (Objects.nonNull(excelImport)) {
field.setAccessible(true);
ImportClassField importField = new ImportClassField();
importField.setClassFieldName(field.getName());
String name = excelImport.name();
if (ExcelUtils.isEmpty(name)) {
name = excelImport.value();
}
importField.setColumnName(name);
importField.setColumnIndex(excelImport.columnIndex());
importField.setDateFormat(excelImport.dateFormat());
LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
KV[] kvs = excelImport.kvs();
for (KV kv : kvs) {
kvMap.put(kv.k(), kv.v());
}
importField.setKvMap(kvMap);
importField.setField(field);
excelImportClassFields.add(importField);
}
}
return excelImportClassFields;
}
/**
* 获取一个类的所有属性（包括这个类的父类属性，祖类属性）
*
* @param c 所在类
* @return 类的所有属性
*/
private static List<Field> getAllFields(Class<?> c) {
List<Field> allFields = new ArrayList<>();
while (Objects.nonNull(c) && c != Object.class) {
allFields.addAll(Arrays.asList(c.getDeclaredFields()));
c = c.getSuperclass();
}
return allFields;
}
/**
* 解析sheet数据，并返回JSONArray对象
*
* @param sheet sheet对象
* @param containKey 是否需要包含键
* （1）当为true时，JSONArray中每个元素为JSONObject对象，其中键为表头(默认第一行为表头)，值为单元格数据，
* 例如：[{"k1":"v1","k2":"v2"},{"k1":"v3","k2":"v4"}]
* （2）当为false时，JSONArray中每个元素为每行单元格数据，位置和文件打开时的视图一一对应
* 例如：[["k1","k2"],["v1","v2"],["v3","v4"]]
* @return JSONArray对象
*/
public static JSONArray pares(Sheet sheet, boolean containKey) {
if (Objects.isNull(sheet) || sheet.getLastRowNum() < 0) {
return new JSONArray();
}
return containKey ? paresJsonArrayWithKey(sheet) : paresJsonArrayNoKey(sheet);
}
/**
* 解析sheet数据，并返回JSONArray对象，其中键为表头(默认第一行为表头)，值为单元格数据，
* 例如：[{"k1":"v1","k2":"v2"},{"k1":"v3","k2":"v4"}]
*
* @param sheet sheet对象
* @return 包含键的JSONArray对象
*/
private static JSONArray paresJsonArrayWithKey(Sheet sheet) {
// 第一行默认为表头，如果表头无有效数据，则不解析
JSONArray array = new JSONArray();
Row header = sheet.getRow(0);
List<Integer> noEmptyHeaderIndexes = getNoEmptyHeaderIndexes(header);
if (isEmpty(noEmptyHeaderIndexes)) {
return array;
}
// 如果只有一行数据时，则默认该行为表头行，无数据行
int lastRowIndex = sheet.getLastRowNum();
if (lastRowIndex == 0) {
JSONObject rowObj = getOrderlyJsonObject();
for (Integer headerIndex : noEmptyHeaderIndexes) {
String k = getCellStringValue(header.getCell(headerIndex));
rowObj.put(k, "");
}
array.add(rowObj);
return array;
}
// 存在多行数据时，则第一行为表头，其他行为数据
for (int i = 1; i < lastRowIndex; i++) {
Row row = sheet.getRow(i);
JSONObject rowObj = getOrderlyJsonObject();
for (Integer headerIndex : noEmptyHeaderIndexes) {
String k = getCellStringValue(header.getCell(headerIndex));
String v = getCellStringValue(row.getCell(headerIndex));
rowObj.put(k, v);
}
array.add(rowObj);
}
return array;
}
/**
* 获取有序的JSONObject对象
*
* @return 有序的JSONObject对象
*/
private static JSONObject getOrderlyJsonObject() {
return new JSONObject(new LinkedHashMap<>());
}
/**
* 解析sheet数据，并返回JSONArray对象
*
* @param sheet sheet对象
* @return 包含键的JSONArray对象
*/
private static JSONArray paresJsonArrayNoKey(Sheet sheet) {
JSONArray array = new JSONArray();
for (Row row : sheet) {
JSONArray rowArray = new JSONArray();
for (Cell cell : row) {
rowArray.add(getCellStringValue(cell));
}
array.add(rowArray);
}
return array;
}
/**
* 获取表头不为空的下标集合
*
* @param header 表头行
* @return 表头不为空的下标集合
*/
private static List<Integer> getNoEmptyHeaderIndexes(Row header) {
List<Integer> noEmptyHeaderIndexes = new ArrayList<>();
for (int i = 0; i < header.getLastCellNum(); i++) {
Cell cell = header.getCell(i);
String cellValue = getCellStringValue(cell);
if (!isEmpty(cellValue)) {
noEmptyHeaderIndexes.add(i);
}
}
return noEmptyHeaderIndexes;
}
/**
* 获取单元格数据
*
* @param cell 单元格对象
* @return 单元格值（以字符串形式返回，如果是时间，则以时间戳格式返回）
*/
private static String getCellStringValue(Cell cell) {
if (Objects.isNull(cell) || Objects.isNull(cell.getCellType())) {
return "";
}
CellType cellType = cell.getCellType();
switch (cellType) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return getCellDateString(cell);
} else {
return new DecimalFormat("0.####################").format(cell.getNumericCellValue());
}
case STRING:
return cell.getRichStringCellValue().getString();
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case BLANK:
return "";
case ERROR:
return FormulaError.forInt(cell.getErrorCellValue()).getString();
default:
return new DataFormatter().formatCellValue(cell);
}
}
/**
* 日期单元格解析（格式统一按照国人的习惯显示，即 yyyy-MM-dd HH:mm:ss）
*
* @param cell 单元格对象
* @return 字符串时间
*/
private static String getCellDateString(Cell cell) {
String dataFormatString = cell.getCellStyle().getDataFormatString();
Date date = cell.getDateCellValue();
String formatStr;
if (dataFormatString.contains("y") && dataFormatString.contains("d")) {
if (dataFormatString.contains("h")) {
formatStr = "yyyy-MM-dd HH:mm:ss";
} else {
formatStr = "yyyy-MM-dd";
}
} else {
formatStr = dataFormatString;
}
return new SimpleDateFormat(formatStr).format(date);
}
private static Workbook getWorkBook(File file) {
try {
if (Objects.isNull(file) || !file.exists()) {
throw new RuntimeException("文件不能为空");
}
String fileName = file.getName();
if (fileName.endsWith(ExcelType.XLSX.val)) {
return new XSSFWorkbook(file);
} else if (fileName.endsWith(ExcelType.XLS.val)) {
return new HSSFWorkbook(Files.newInputStream(file.toPath()));
} else {
throw new RuntimeException("文件格式错误");
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
private static Workbook getWorkBook(MultipartFile file) {
try {
if (Objects.isNull(file) || file.isEmpty()) {
throw new RuntimeException("文件不能为空");
}
String fileName = file.getOriginalFilename();
assert fileName != null;
if (fileName.endsWith(ExcelType.XLSX.val)) {
return new XSSFWorkbook(file.getInputStream());
} else if (fileName.endsWith(ExcelType.XLS.val)) {
return new HSSFWorkbook(file.getInputStream());
} else {
throw new RuntimeException("文件格式错误");
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
private static List<Sheet> getSheets(Workbook workbook) {
int maxSheet = workbook.getNumberOfSheets();
List<Sheet> sheets = new ArrayList<>();
for (int i = 0; i < maxSheet; i++) {
sheets.add(workbook.getSheetAt(i));
}
return sheets;
}
/**
* excel文件类型
*/
enum ExcelType {
// excel 2007 +
XLSX(".xlsx"),
// excel 2007 -
XLS(".xls");
public final String val;
ExcelType(String val) {
this.val = val;
}
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelImport {
@AliasFor(value = "name")
String value() default "";
@AliasFor(value = "value")
String name() default "";
// 键值对集合
KV[] kvs() default {};
/**
* 时间样式
* 当该值无值时，则智能判断（智能判定可能会出错，建议指定格式）
* 当该值为固定值 ts 时（TimestampSecond），则表示表格中数据为秒级时间戳
* 当该值为固定值 tms 时（TimestampMillisecond），则表示表格中数据为毫秒级时间戳
* 当该值为其他值时，则表示表格中数据格式
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
// 列索引（如果有该值大于0，则以该值解析为准，优先级高于value字段）
int columnIndex() default -1;
}
@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelExport {
@AliasFor(value = "name")
String value() default "";
@AliasFor(value = "value")
String name() default "";
// 键值对集合
KV[] kvs() default {};
/**
* 时间样式
* 当该值无值时，则智能判断（智能判定可能会出错，建议指定格式）
* 当该值为固定值 ts 时（TimestampSecond），则表示表格中数据为秒级时间戳
* 当该值为固定值 tms 时（TimestampMillisecond），则表示表格中数据为毫秒级时间戳
* 当该值为其他值时，则表示表格中数据格式
*/
String dateFormat() default "yyyy-MM-dd HH:mm:ss";
// 列索引（列排序，默认 -1 不指定，默认按属性位置顺序排列，当设置为 0 时，则为第 1 列）
int columnIndex() default -1;
// 批注，如果要换行请用 \r\n 代替
String comment() default "";
// 行宽
int columnWidth() default 0;
}
@Target(value = {ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelSheet {
// sheet名称
@AliasFor(value = "name")
String value() default "";
@AliasFor(value = "value")
String name() default "";
// 是否冻结表头所在行
boolean freezeHeader() default false;
// 水印
Watermark watermark() default @Watermark();
// 列宽
int columnWidth() default 15;
// 是否忽略表头
boolean ignoreHeader() default false;
}
public @interface Watermark {
/**
* 1-文本文字水印;
* 2-本地图片水印;
* 3-网络图片水印；
* 更多查看 {@link WatermarkType#code}
*/
int type() default 0;
/**
* 当type=1时：src为文字内容，如：xxx科技有效公司
* 当type=2时：src为本地文件路径，如：D:\img\icon.png
* 当type=3时：src为网络图片水印，如：https://profile-avatar.csdnimg.cn/624a0ef43e224cb2bf5ffbcca1211e51_sunnyzyq.jpg
*/
String src() default "";
}
public enum WatermarkType {
NONE(0, "无水印"),
TEXT(1, "文本文字水印"),
FILE(2, "本地图片水印"),
URL(3, "网络图片水印");
public final int code;
public final String name;
WatermarkType(int code, String name) {
this.code = code;
this.name = name;
}
public int getCode() {
return code;
}
public String getName() {
return name;
}
public static boolean containCode(int code) {
return NONE.code == code || TEXT.code == code || FILE.code == code || URL.code == code;
}
}
@Retention(RetentionPolicy.RUNTIME)
public @interface KV {
// 枚举键
String k();
// 枚举值
String v();
}
/**
* 导入注解属性参数
*/
@Data
static class ImportClassField {
// field 原属性
private Field field;
// 字段名称
private String classFieldName;
// 列名称
private String columnName;
// 列索引
private int columnIndex = -1;
// 时间格式
private String dateFormat;
// 键值对
private LinkedHashMap<String, String> kvMap;
// 列宽
private int columnWidth;
}
/**
* 导出注解属性参数
*/
@Data
static class ExportClassField {
// 字段名称
private String classFieldName;
// excel表头名称
private String columnName;
// exel列索引
private int columnIndex = -1;
// 列宽
private int columnWidth;
// 时间格式
private String dateFormat;
// 批注
private String comment;
// 键值对
private LinkedHashMap<String, String> kvMap;
// field 原属性
private Field field;
}
@Data
public static class ExportWorkBook {
// 文件名称（不用带文件尾缀）
private String name;
// 页数据
private List<ExportSheet> sheets;
}
/**
* 导出模板
*/
@Data
public static class ExportSheet {
// 页名称
private String name;
// 表头样式
private ExportCellStyle headerStyle;
// 数据样式
private ExportCellStyle dataStyle;
// 数据
private List<ExportRow> rows;
// 批注
private List<ExportComment> comments;
// 水印
private ExportWatermark watermark;
// 是否冻结表头所在行
private boolean freezeHeader;
// 全局列宽
private int columnWidth;
// 是否忽略表头
private boolean ignoreHeader;
// 指定列宽
private Map<Integer, Integer> columnIndexWidthMap;
// 页其他设置
private List<ExportCellMerge> merges;
}
public static class ExportSheetFactory {
public static <T> ExportSheet createExportSheet(List<T> list, Class<T> c, List<ExportCellMerge> merges) {
// 类名注解
ExcelSheet excelSheet = c.getAnnotation(ExcelSheet.class);
String name = "";
boolean freezeHeader = false;
ExportWatermark watermark = null;
int columnWidth = 0;
boolean ignoreHeader = false;
if (excelSheet != null) {
name = excelSheet.name();
if (ExcelUtils.isEmpty(name)) {
name = excelSheet.value();
}
freezeHeader = excelSheet.freezeHeader();
ignoreHeader = excelSheet.ignoreHeader();
Watermark wmk = excelSheet.watermark();
watermark = new ExportWatermark();
watermark.setType(wmk.type());
watermark.setSrc(wmk.src());
columnWidth = excelSheet.columnWidth();
}
// 属性注解
List<ExportClassField> exportFields = filterExportFields(c);
Map<Integer, Integer> columnIndexWidthMap = getSheetColumnIndexWidthMap(exportFields);
List<ExportRow> rows = getSheetRows(exportFields, list, ignoreHeader);
List<ExportComment> comments = getSheetComments(exportFields);
// 构建导出Sheet对象
ExportSheet mySheet = new ExportSheet();
mySheet.setIgnoreHeader(ignoreHeader);
mySheet.setName(name);
mySheet.setHeaderStyle(new ExportCellStyle());
mySheet.setDataStyle(new ExportCellStyle());
mySheet.setRows(rows);
mySheet.setComments(comments);
mySheet.setWatermark(watermark);
mySheet.setFreezeHeader(freezeHeader);
mySheet.setColumnWidth(columnWidth);
mySheet.setColumnIndexWidthMap(columnIndexWidthMap);
mySheet.setMerges(merges);
return mySheet;
}
private static Map<Integer, Integer> getSheetColumnIndexWidthMap(List<ExportClassField> exportFields) {
Map<Integer, Integer> columnIndexWidthMap = new HashMap<>();
for (int i = 0; i < exportFields.size(); i++) {
columnIndexWidthMap.put(i, exportFields.get(i).getColumnWidth());
}
return columnIndexWidthMap;
}
private static List<ExportClassField> filterExportFields(Class<?> c) {
List<ExportClassField> excelImportClassFields = new ArrayList<>();
List<Field> allClassFields = ExcelUtils.getAllFields(c);
for (Field field : allClassFields) {
ExcelExport ex = field.getAnnotation(ExcelExport.class);
if (Objects.nonNull(ex)) {
field.setAccessible(true);
ExportClassField exportField = new ExportClassField();
exportField.setClassFieldName(field.getName());
String name = ex.name();
if (ExcelUtils.isEmpty(name)) {
name = ex.value();
}
exportField.setColumnName(name);
exportField.setColumnIndex(ex.columnIndex());
exportField.setColumnWidth(ex.columnWidth());
exportField.setDateFormat(ex.dateFormat());
exportField.setComment(ex.comment());
LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
KV[] kvs = ex.kvs();
for (KV kv : kvs) {
kvMap.put(kv.k(), kv.v());
}
exportField.setKvMap(kvMap);
exportField.setField(field);
excelImportClassFields.add(exportField);
}
}
// 按 columnIndex 排序（小的在前，大的在后）
excelImportClassFields.sort(Comparator.comparingInt(ExportClassField::getColumnIndex));
return excelImportClassFields;
}
private static <T> List<ExportRow> getSheetRows(List<ExportClassField> exportFields, List<T> list, boolean ignoreHeader) {
List<ExportRow> rows = new ArrayList<>();
if (!ignoreHeader) {
rows.addAll(initHeaderRows(exportFields));
}
rows.addAll(initDataRows(exportFields, list));
return rows;
}
private static List<ExportRow> initHeaderRows(List<ExportClassField> exportFields) {
List<ExportCell> headerCells = new ArrayList<>();
for (ExportClassField cf : exportFields) {
ExportCell cell = new ExportCell();
cell.setValue(cf.getColumnName());
headerCells.add(cell);
}
ExportRow headerRow = new ExportRow();
headerRow.setHeader(true);
headerRow.setCells(headerCells);
List<ExportRow> headerRows = new ArrayList<>();
headerRows.add(headerRow);
return headerRows;
}
private static <T> List<ExportRow> initDataRows(List<ExportClassField> exportFields, List<T> list) {
List<ExportRow> dataRows = new ArrayList<>();
for (T t : list) {
List<ExportCell> dataCells = new ArrayList<>();
for (ExportClassField cf : exportFields) {
ExportCell cell = new ExportCell();
cell.setDateFormat(cf.getDateFormat());
try {
Object o = cf.getField().get(t);
// 判断该属性是否有映射，如果有映射，则转化为映射值
if (!cf.getKvMap().isEmpty() && o != null) {
System.out.println("cf.getKvMap() = " + cf.getKvMap());
System.out.println("o = " + o);
o = cf.getKvMap().get(o.toString());
}
cell.setValue(o);
} catch (IllegalAccessException e) {
throw new RuntimeException("获取属性值失败: " + cf.getField().getName(), e);
}
dataCells.add(cell);
}
ExportRow dataRow = new ExportRow();
dataRow.setHeader(false);
dataRow.setCells(dataCells);
dataRows.add(dataRow);
}
return dataRows;
}
private static List<ExportComment> getSheetComments(List<ExportClassField> exportFields) {
List<ExportComment> comments = new ArrayList<>();
for (int i = 0; i < exportFields.size(); i++) {
ExportClassField cf = exportFields.get(i);
if (!isEmpty(cf.getComment())) {
ExportComment comment = new ExportComment();
comment.setX(i);
comment.setY(0);
comment.setComment(cf.getComment());
comments.add(comment);
}
}
return comments;
}
}
@Data
public static class ExportRow {
private boolean header;
private List<ExportCell> cells;
public ExportRow appendCells(Object... values) {
for (Object value : values) {
appendCell(value, null);
}
return this;
}
public ExportRow appendCell(Object value) {
appendCell(value, null);
return this;
}
public ExportRow appendCell(Object value, String dateFormat) {
if (cells == null) {
cells = new ArrayList<>();
}
ExportCell cell = new ExportCell();
cell.setValue(value);
cell.setDateFormat(dateFormat);
cells.add(cell);
return this;
}
}
@Data
public static class ExportCellStyle {
// 背景颜色
private short backgroundColor;
}
@Data
static
public class ExportCell {
private Object value;
private String dateFormat;
}
@Data
public static class ExportComment {
// 横坐标
private int x;
// 纵坐标
private int y;
// 备注
private String comment;
}
@Data
public static class ExportWatermark {
/**
* 1-文本文字水印;
* 2-本地图片水印;
* 3-网络图片水印；
* 更多查看 {@link WatermarkType#code}
*/
private int type;
/**
* 当type=1时：src为文字内容，如：xxx科技有效公司
* 当type=2时：src为本地文件路径，如：D:\img\icon.png
* 当type=3时：src为网络图片水印，如：https://profile-avatar.csdnimg.cn/624a0ef43e224cb2bf5ffbcca1211e51_sunnyzyq.jpg
*/
private String src;
}
@Data
public static class ExportCellMerge {
private int x1;
private int y1;
private int x2;
private int y2;
public ExportCellMerge(int x1, int y1, int x2, int y2) {
this.x1 = x1;
this.y1 = y1;
this.x2 = x2;
this.y2 = y2;
}
}
public static String formatMergerString(int x1, int y1, int x2, int y2) {
return String.format("%s,%s,%s,%s", x1, y1, x2, y2);
}
}
用熟这个工具类后异常简单，下面我用一个万能公式导出作为演示
二、导出编写
4、编写实体类entity
/**
* @Author Ysl
* @Date 2024/4/28
* @name Myselflean
**/
@Data
@ExcelUtils.ExcelSheet(name = "用户表", watermark = @ExcelUtils.Watermark(type = 3, src ="https://profileavatar.csdnimg.cn/37f95edca8614683923fa0b9455058f6_qq_53438100.jpg!1"))
public class User {
@ExcelUtils.ExcelExport(name = "序号")
private int id;
@ExcelUtils.ExcelExport(name = "姓名")
private String name;
@ExcelUtils.ExcelExport(name = "性别")
private int sex;
@ExcelUtils.ExcelExport(name = "身份证号")
private int id_card;
@ExcelUtils.ExcelExport(name = "电话")
private int phone;
@ExcelUtils.ExcelExport(name = "会员类型")
private String v_type;
@ExcelUtils.ExcelExport(name = "开始时间")
private Date start_time;
@ExcelUtils.ExcelExport(name = "结束时间")
private Date end_time;
}
5、编写Controller
/**
* @Author Ysl
* @Date 2024/4/29
* @name Myselflean
**/
@RestController
public class ExportController {
@GetMapping("/export/ysl")
@ApiOperation(value = "万能导入", produces = "application/octet-stream")
public void export(HttpServletResponse response){
ExcelUtils.ExportRow rows1 = new ExcelUtils.ExportRow();
//首先判断是否为表头，然后将数据放入对应的行
rows1.setHeader(true);
rows1.appendCells("序号","姓名","性别","身份证号","电话","会员类型","开始时间","结束时间");
ExcelUtils.ExportRow rows2 = new ExcelUtils.ExportRow();
//首先判断是否为表头，然后将数据放入对应的行
rows2.setHeader(false);
rows2.appendCells(1,"王二","男","130432200109160256","15834569852","普通会员")
.appendCell(new Date(),"yyyy-MM-dd")
.appendCell(new Date(),"yyyy-MM-dd");
//合并1，2列
List<ExcelUtils.ExportRow> list = Arrays.asList(rows1,rows2);
//设置批注
ExcelUtils.ExportComment comment = new ExcelUtils.ExportComment();
comment.setX(5);
comment.setY(0);
comment.setComment("普通会员; \n vip会员;\n 超级会员;" );
//合并批注
List<ExcelUtils.ExportComment> comments = Arrays.asList(comment);
//设置水印
ExcelUtils.ExportWatermark watermark = new ExcelUtils.ExportWatermark();
watermark.setType(1);
watermark.setSrc("ysl测试");
//设置sheet页
ExcelUtils.ExportSheet sheet = new ExcelUtils.ExportSheet();
sheet.setWatermark(watermark);
sheet.setRows(list);
sheet.setComments(comments);
sheet.setName("ysl测试");
List<ExcelUtils.ExportSheet> sheets = Arrays.asList(sheet);
ExcelUtils.export(response,"100",sheets);
}
}
5.1、结果：
三、导入编写
和导出同样的实体类
1、前端代码
注意自己配置跨域和路由、根据自己电脑修改样式
<template>
<div style="width: 1000px;margin: auto;margin-top: 200px;border: 1px solid #ebeef5">
<div style="display: flex;justify-content: right">
<el-upload
class="upload-demo"
action="http://localhost:8080/import"
:on-preview="handlePreview"
:on-remove="handleRemove"
:before-remove="beforeRemove"
multiple
:limit="3"
:on-exceed="handleExceed"
:file-list="fileList">
<el-button size="small" type="primary">点击上传</el-button>
<div slot="tip" class="el-upload__tip">上传文件不超过500kb</div>
</el-upload>
</div>
<el-table
:data="tableData"
border
style="width: 100%">
<el-table-column
prop="id"
label="序号"
width="180">
</el-table-column>
<el-table-column
prop="name"
label="姓名"
width="180">
</el-table-column>
<el-table-column
prop="sex"
label="性别">
</el-table-column>
<el-table-column
prop="id_card"
label="身份证号">
</el-table-column>
<el-table-column
prop="phone"
label="电话">
</el-table-column>
<el-table-column
prop="v_type"
label="会员类别">
</el-table-column>
<el-table-column
prop="start_time"
label="开始时间">
</el-table-column>
<el-table-column
prop="end_time"
label="结束时间">
</el-table-column>
</el-table>
</div>
</template>
<script>
export default {
name: "Login",
data() {
return {
tableData: [],
fileList: [],
};
},
methods: {
handleRemove(file, fileList) {
console.log(file, fileList);
},
handlePreview(file) {
console.log(file)
this.tableData = file.response;
console.log(this.tableData)
},
handleExceed(files, fileList) {
this.$message.warning(`当前限制选择 3 个文件，本次选择了 ${files.length} 个文件，共选择了 ${files.length + fileList.length} 个文件`);
},
beforeRemove(file, fileList) {
return this.$confirm(`确定移除 ${file.name}？`);
}
}
}
</script>
<style scoped>
</style>
2、后端代码Controller
/**
* @Author Ysl
* @Date 2024/4/28
* @name Myselflean
**/
@RestController
@Api(tags = {"Excel导入示例"})
public class ExcelImportController {
@ApiOperation(value = "按对象导入（基础）", produces = "application/octet-stream")
@PostMapping("/import")
public JSONArray importExcel(@RequestPart("file") MultipartFile file){
JSONArray users = ExcelUtils.read(file, true);
for (Object user : users) {
String userString = JSON.toJSONString(user);
try {
User userObject = JSON.parseObject(userString, User.class);
System.out.println(userObject);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
return users;
}
}
3、导入的excel为
4、导入成功后
