vxe-grid 服务器端下载excel，通过spring boot + easyexcel导出excel生成文件，返回给前端文件url, 然后下载.

花花鱼

已于 2024-05-16 21:35:57 修改

阅读量227

点赞数 3

分类专栏： Spring Boot Vue3 vxe-grid 文章标签： spring boot excel 后端

于 2024-05-16 21:23:15 首次发布

本文链接：https://blog.csdn.net/jwbabc/article/details/138975211

版权

Vue3 同时被 3 个专栏收录

18 篇文章 0 订阅

订阅专栏

Spring Boot

9 篇文章 0 订阅

订阅专栏

vxe-grid

2 篇文章 0 订阅

订阅专栏

前端vxe-grid配制

exportConfig: {
      remote: true,
      filename: '物资-' + dayjs(new Date()).format('YYYY-MM-DD'),
      sheetName: 'Sheet1',
      // 默认选中类型
      type: 'xlsx',
      // 局部自定义类型
      types: ['xlsx'],
      // 自定义数据量列表
      modes: ['current', 'selected', 'all'],
      exportMethod({ options }) {
        const $grid = tableRef.value;
        if ($grid) {
          const proxyInfo = $grid.getProxyInfo();
          // 传给服务端的参数
          const body = {
            filename: options.filename,
            sheetName: options.sheetName,
            isHeader: options.isHeader,
            original: options.original,
            mode: options.mode,
            pager: proxyInfo ? proxyInfo.pager : null,
            ids: options.mode === 'selected' ? options.data.map((item) => item.id) : [],
            fields: options.columns.map((column) => {
              return {
                field: column.field,
                title: column.title,
              };
            }),
          };
          // 开始服务端导出(ExportWzExcel:是前端生成excel的方法）
          return ExportWzExcel(body).then((data) => {
            if (data.message) {
              VXETable.modal.message({ content: '导出成功，开始下载', status: 'success' });
              // 读取路径，请求文件 data.message：返回的是服务端生成的文件https://开头的url
              fetch(`${data.message}`).then((response) => {
                response.blob().then((blob) => {
                  // 开始下载
                  VXETable.saveFile({ filename: body.filename, type: 'xlsx', content: blob });
                });
              });
            }
          });
        }
        return Promise.resolve();
      },
    },

remote: true,表示从服务端生成。如果是remote:false的话，是客户端直接导出，但是对于导出所有的数据，就是个问题，而且数据量非常大的时候还是要考虑从服务端生成。

/**
	 * 导出产品
	 * 
	 * @param wz
	 * @param req
	 * @return
	 */
	@RequestMapping(path = "/exportWzExcel", method = RequestMethod.POST)
	public ResultSuccessOpt ExportWzExcel(@RequestBody ExportExcel exportExcel, HttpServletRequest req) {
		System.out.println(exportExcel.toString());
		if (exportExcel.getMode().equals("all")) {
			exportExcel.getPager().setPageSize(10000);
		}
		Wz wz = new Wz();
		wz.setPage(exportExcel.getPager().getCurrentPage());
		wz.setPageSize(exportExcel.getPager().getPageSize());
        // mybatis plus 获取的分页数据
		Page<Wz> wzPage = wzService.getWzList(wz);
		List<Wz> records = wzPage.getRecords();
		String fileUrl = "";
		String rootPath = req.getSession().getServletContext().getRealPath("/");
		String tplDirname = "excel";
		String outputFilePath = rootPath + "/upload" + File.separator + tplDirname;
		File folder = new File(outputFilePath);
		if (!folder.exists()) {
			folder.mkdirs();
		}
		String filename = File.separator + System.currentTimeMillis() + ".xlsx";
		fileUrl = fileSvrUrl + File.separator + tplDirname + filename;
		// 生成的excel路径
		String outputFilename = outputFilePath + filename;

		List<List<String>> headers = new ArrayList<>();
		List<String> columns = new ArrayList<>();
		for (ExportFields field : exportExcel.getFields()) {
			List<String> headerList = new ArrayList<String>();
			headerList.add(field.getTitle());
			headers.add(headerList);
			columns.add(field.getField());
		}

		ExcelCellWidthStyleStrategy widthStyleStrategy = new ExcelCellWidthStyleStrategy();
		// 设置单元格样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(StyleUtils.getHeadStyle(), StyleUtils.getContentStyle());
		EasyExcel.write(outputFilename, Wz.class).head(headers).includeColumnFieldNames(columns)
				.sheet(exportExcel.getSheetName())
				.registerWriteHandler(horizontalCellStyleStrategy)
				.registerWriteHandler(widthStyleStrategy).doWrite(dataList(records,columns));

		ResultSuccessOpt entiry = new ResultSuccessOpt();
		int row = 1;
		if (row > 0) {
			entiry.setCode(0);
			entiry.setMessage(fileUrl); //文件url
			entiry.setType(ResourcesInfo.OPT_TYPE);
			entiry.setResult(0);
		} else {
			entiry.setCode(-1);
			entiry.setMessage(ResourcesInfo.OPT_ERROR);
			entiry.setType(ResourcesInfo.OPT_TYPE);
			entiry.setResult(-1);
		}
		return entiry;
	}

/**
	 * 根据传入的字段获取对应的get方法，如name,对应的getName方法
	 * 
	 * @param fieldName 字段名
	 * @param person    对象
	 * @return
	 */
	private static Object getFieldValue(String fieldName, Object person) {
		try {
			String firstLetter = fieldName.substring(0, 1).toUpperCase();
			String getter = "get" + firstLetter + fieldName.substring(1);
			Method method = person.getClass().getMethod(getter);
			return method.invoke(person);
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 设置表格信息
	 * 
	 * @param dataList 查询出的数据
	 * @param fileList 需要显示的字段
	 * @return
	 */
	private static List<List<Object>> dataList(List<Wz> dataList, List<String> fileList) {
		List<List<Object>> list = new ArrayList<>();
		for (Object person : dataList) {
			List<Object> data = new ArrayList<>();
			for (String fieldName : fileList) {
				/** 通过反射根据需要显示的字段，获取对应的属性值 */
				data.add(getFieldValue(fieldName, person));
			}
			list.add(data);
		}
		return list;
	}

StyleUitls.class

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class StyleUtils {

	/**
	 * 标题样式
	 * 
	 * @return
	 */
	public static WriteCellStyle getHeadStyle() {
		// 头的策略
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		// 字体
		WriteFont headWriteFont = new WriteFont();
		headWriteFont.setFontName("宋体");// 设置字体名字
		headWriteFont.setFontHeightInPoints((short) 11);// 设置字体大小
		headWriteFont.setBold(true);// 字体加粗
		headWriteCellStyle.setWriteFont(headWriteFont); // 在样式用应用设置的字体;

		// 样式
		headWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 设置底边框;
		headWriteCellStyle.setBottomBorderColor((short) 0);// 设置底边框颜色;
		headWriteCellStyle.setBorderLeft(BorderStyle.THIN); // 设置左边框;
		headWriteCellStyle.setLeftBorderColor((short) 0);// 设置左边框颜色;
		headWriteCellStyle.setBorderRight(BorderStyle.THIN);// 设置右边框;
		headWriteCellStyle.setRightBorderColor((short) 0);// 设置右边框颜色;
		headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 设置顶边框;
		headWriteCellStyle.setTopBorderColor((short) 0); // 设置顶边框颜色;

		headWriteCellStyle.setWrapped(true); // 设置自动换行;

		headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 设置水平对齐的样式为居中对齐;
		headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直对齐的样式为居中对齐;
		headWriteCellStyle.setShrinkToFit(true);// 设置文本收缩至合适

		return headWriteCellStyle;
	}

	/**
	 * 内容样式
	 * 
	 * @return
	 */
	public static WriteCellStyle getContentStyle() {
		// 内容的策略
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

		// 背景绿色
		// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
		// FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

		// 设置字体
		WriteFont contentWriteFont = new WriteFont();
		contentWriteFont.setFontHeightInPoints((short) 11);// 设置字体大小
		contentWriteFont.setFontName("宋体"); // 设置字体名字
		contentWriteCellStyle.setWriteFont(contentWriteFont);// 在样式用应用设置的字体;

		// 设置样式;
		contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 设置底边框;
		contentWriteCellStyle.setBottomBorderColor((short) 0);// 设置底边框颜色;
		contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); // 设置左边框;
		contentWriteCellStyle.setLeftBorderColor((short) 0);// 设置左边框颜色;
		contentWriteCellStyle.setBorderRight(BorderStyle.THIN);// 设置右边框;
		contentWriteCellStyle.setRightBorderColor((short) 0);// 设置右边框颜色;
		contentWriteCellStyle.setBorderTop(BorderStyle.THIN);// 设置顶边框;
		contentWriteCellStyle.setTopBorderColor((short) 0); /// 设置顶边框颜色;

		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		contentWriteCellStyle.setWrapped(true); // 设置自动换行;

		contentWriteCellStyle.setShrinkToFit(true);// 设置文本收缩至合适

		return contentWriteCellStyle;
	}

}

ExcelCellWidthStyleStrategy.class

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
 
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class ExcelCellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    // 可以根据这里的最大宽度，按自己需要进行调整,搭配单元格样式实现类中的，自动换行，效果更好
    private static final int MAX_COLUMN_WIDTH = 50;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
	@Override
	protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
			Head head, Integer relativeRowIndex, Boolean isHead) {
		 boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
	        if (needSetWidth) {
	            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
	            if (maxColumnWidthMap == null) {
	                maxColumnWidthMap = new HashMap(16);
	                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
	            }
	 
	            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
	            if (columnWidth >= 0) {
	                if (columnWidth > MAX_COLUMN_WIDTH) {
	                    columnWidth = MAX_COLUMN_WIDTH;
	                }
	 
	                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
	                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
	                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
	                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
	                }
	 
	            }
	        }
	}
 
	private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
 
}

参考：easyExcel 导出文件时，设置单元格样式，自适应列宽_writecellstyle设置宽度-CSDN博客