vxe-grid 服务器端下载excel，通过spring boot + easyexcel导出excel生成文件，返回给前端文件url, 然后下载.
easyexcel生成文件，vxe-grid下载文件。
·
前端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;
}
}
}
}
}
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
3
0- 0
已为社区贡献10条内容
所有评论(0)