Excel导入和导出

一、前期准备

1、首先导入主要的依赖

java"><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

java">@Configuration
@EnableSwagger2
public class SwaggerConfig {@Beanpublic 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工具类

java">/*** @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) {// ExportSheetList<ExportSheet> mySheets = new ArrayList<>();ExportSheet mySheet = ExportSheetFactory.createExportSheet(list, c, merges);if (isEmpty(mySheet.getName())) {mySheet.setName(getFileName(fileName));}mySheets.add(mySheet);// ExportWorkBookExportWorkBook 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,jpegList<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;}// Doubleif (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();}/*** 导入注解属性参数*/@Datastatic 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;}/*** 导出注解属性参数*/@Datastatic 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;}@Datapublic static class ExportWorkBook {// 文件名称（不用带文件尾缀）private String name;// 页数据private List<ExportSheet> sheets;}/*** 导出模板*/@Datapublic 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;}}@Datapublic 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;}}@Datapublic static class ExportCellStyle {// 背景颜色private short backgroundColor;}@Datastaticpublic class ExportCell {private Object value;private String dateFormat;}@Datapublic static class ExportComment {// 横坐标private int x;// 纵坐标private int y;// 备注private String comment;}@Datapublic 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;}@Datapublic 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

java">/*** @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

java">/*** @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-uploadclass="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"borderstyle="width: 100%"><el-table-columnprop="id"label="序号"width="180"></el-table-column><el-table-columnprop="name"label="姓名"width="180"></el-table-column><el-table-columnprop="sex"label="性别"></el-table-column><el-table-columnprop="id_card"label="身份证号"></el-table-column><el-table-columnprop="phone"label="电话"></el-table-column><el-table-columnprop="v_type"label="会员类别"></el-table-column><el-table-columnprop="start_time"label="开始时间"></el-table-column><el-table-columnprop="end_time"label="结束时间"></el-table-column></el-table></div>
</template><script>javascript">
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

java">/*** @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;}
}

excel_1939">3、导入的excel为

4、导入成功后