这些是我再实现该功能时进行的探索
this.$refs.crud.$refs
一共16个字段,上面的数组中第一个是序号字段,以及最后的操作字段省去后就是我们所需的字段了就是14个,之后就可以传回后台
这个地址是下载地址,自己确定就行了
剩下的就是后端处理的代码了
工具类
package com.yyb.live.unit;
import com.aliyun.oss.OSSClient;
import com.google.common.base.Strings;
import com.ztzb.common.util.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
public class ExportUtils {
private static Logger logger = LoggerFactory.getLogger(ExportUtils.class);
private String certExport = "certificate/export/";
//表头
private String title;
//各个列的表头
private String[] heardList;
//各个列的元素key值
private String[] heardKey;
//需要填充的数据信息
private List<Map> data;
//字体大小
private int fontSize = 14;
//行高
private int rowHeight = 30;
//列宽
private int columWidth = 200;
//工作表
private String sheetName = "sheet1";
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String[] getHeardList() {
return heardList;
}
public void setHeardList(String[] heardList) {
this.heardList = heardList;
}
public String[] getHeardKey() {
return heardKey;
}
public void setHeardKey(String[] heardKey) {
this.heardKey = heardKey;
}
public List<Map> getData() {
return data;
}
public void setData(List<Map> data) {
this.data = data;
}
public int getFontSize() {
return fontSize;
}
public void setFontSize(int fontSize) {
this.fontSize = fontSize;
}
public int getRowHeight() {
return rowHeight;
}
public void setRowHeight(int rowHeight) {
this.rowHeight = rowHeight;
}
public int getColumWidth() {
return columWidth;
}
public void setColumWidth(int columWidth) {
this.columWidth = columWidth;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
/**
* 开始导出数据信息
*
*/
public String exportExport(String endPoint,String accessKeyId,String accessKeySecret,String bucketName,HttpServletRequest request, HttpServletResponse response) throws IOException {
//检查参数配置信息
checkConfig();
//创建工作簿
HSSFWorkbook wb = new HSSFWorkbook();
//创建工作表
HSSFSheet wbSheet = wb.createSheet(this.sheetName);
//设置默认行宽
wbSheet.setDefaultColumnWidth(20);
// 标题样式(加粗,垂直居中)
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
HSSFFont fontStyle = wb.createFont();
fontStyle.setBold(true); //加粗
fontStyle.setFontHeightInPoints((short)16); //设置标题字体大小
cellStyle.setFont(fontStyle);
//在第0行创建rows (表标题)
HSSFRow title = wbSheet.createRow((int) 0);
title.setHeightInPoints(30);//行高
HSSFCell cellValue = title.createCell(0);
cellValue.setCellValue(this.title);
cellValue.setCellStyle(cellStyle);
wbSheet.addMergedRegion(new CellRangeAddress(0,0,0,(this.heardList.length-1)));
//设置表头样式,表头居中
HSSFCellStyle style = wb.createCellStyle();
//设置单元格样式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) this.fontSize);
style.setFont(font);
//在第1行创建rows
HSSFRow row = wbSheet.createRow((int) 1);
//设置列头元素
HSSFCell cellHead = null;
for (int i = 0; i < heardList.length; i++) {
cellHead = row.createCell(i);
cellHead.setCellValue(heardList[i]);
cellHead.setCellStyle(style);
}
//设置每格数据的样式 (字体红色)
HSSFCellStyle cellParamStyle = wb.createCellStyle();
HSSFFont ParamFontStyle = wb.createFont();
cellParamStyle.setAlignment(HorizontalAlignment.CENTER);
cellParamStyle.setVerticalAlignment(VerticalAlignment.CENTER);
ParamFontStyle.setColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex()); //设置字体颜色 (红色)
ParamFontStyle.setFontHeightInPoints((short) this.fontSize);
cellParamStyle.setFont(ParamFontStyle);
//设置每格数据的样式2(字体蓝色)
HSSFCellStyle cellParamStyle2 = wb.createCellStyle();
cellParamStyle2.setAlignment(HorizontalAlignment.CENTER);
cellParamStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont ParamFontStyle2 = wb.createFont();
ParamFontStyle2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex()); //设置字体颜色 (蓝色)
ParamFontStyle2.setFontHeightInPoints((short) this.fontSize);
cellParamStyle2.setFont(ParamFontStyle2);
//开始写入实体数据信息
int a = 2;
for (int i = 0; i < data.size(); i++) {
HSSFRow roww = wbSheet.createRow((int) a);
Map map = data.get(i);
HSSFCell cell = null;
for (int j = 0; j < heardKey.length; j++) {
cell = roww.createCell(j);
cell.setCellStyle(style);
Object valueObject = map.get(heardKey[j]);
String value = null;
if (valueObject == null) {
valueObject = "";
}
if (valueObject instanceof String) {
//取出的数据是字符串直接赋值
value = (String) map.get(heardKey[j]);
} else if (valueObject instanceof Integer) {
//取出的数据是Integer
value = String.valueOf(((Integer) (valueObject)).floatValue());
} else if (valueObject instanceof BigDecimal) {
//取出的数据是BigDecimal
value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
} else {
value = valueObject.toString();
}
//设置单个单元格的字体颜色
if(heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")){
if((Long)map.get("ddNum")!=null){
if((Long)map.get("sjNum")==null){
cell.setCellStyle(cellParamStyle);
} else if((Long) map.get("ddNum") != (Long) map.get("sjNum")){
if ((Long) map.get("ddNum") > (Long) map.get("sjNum")) {
cell.setCellStyle(cellParamStyle);
}
if ((Long) map.get("ddNum") < (Long) map.get("sjNum")) {
cell.setCellStyle(cellParamStyle2);
}
}else {
cell.setCellStyle(style);
}
}
}
cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value);
}
a++;
}
String fileName = null;
String pathUrl = null;
ByteArrayOutputStream bos = null;
ByteArrayInputStream bis = null;
//导出数据
try {
//也可存入本地
//FileOutputStream fileOutputStream = new FileOutputStream("D:\\test.pdf");
//fileOutputStream.write(baos.toByteArray());
fileName = certExport + DateUtils.getDate("yyyyMMddHHmmss")+".xls";
bos = new ByteArrayOutputStream();
wb.write(bos);
byte[] byteArray = bos.toByteArray();
bis = new ByteArrayInputStream(byteArray);
upload(endPoint,accessKeyId,accessKeySecret,bucketName,bis,fileName);
pathUrl = fileName;
} catch (Exception ex) {
ex.printStackTrace();
throw new IOException("导出Excel出现严重异常,异常信息:" + ex.getMessage());
} finally {
if(bos != null){
try{
bos.flush();;
bos.close();
bis.close();
}catch (IOException e){
logger.info("流关闭出现严重异常,异常信息:" + e.getMessage());
}
}
}
return pathUrl;
}
public void upload(String endPoint,String accessKeyId,String accessKeySecret,String bucketName,InputStream is, String path){
OSSClient client = new OSSClient(endPoint,accessKeyId, accessKeySecret);
try{
if(path.startsWith("/")){
path=path.substring(1);
}
client .putObject(bucketName, path, is);
} catch(RuntimeException e){
logger.error("oss上传文件流出错",e);
} finally {
client.shutdown();
}
}
/**
* 检查数据配置问题
*
* @throws IOException 抛出数据异常类
*/
protected void checkConfig() throws IOException {
if (heardKey == null || heardList.length == 0) {
throw new IOException("列名数组不能为空或者为NULL");
}
if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
throw new IOException("字体、宽度或者高度不能为负值");
}
if (Strings.isNullOrEmpty(sheetName)) {
throw new IOException("工作表表名不能为NULL");
}
}
}
service层:
List<Map> certificateInfo = liveCertificateMapper.exportCertificateInfo(liveCertificate);
String pathUrl = null;
if (certificateInfo != null && certificateInfo.size() > 0) {
String sheetTitle = "证书管理";
String[] title = exportLabels.split(","); //设置表格表头字段
String[] properties = exportProperty.split(","); // 查询对应的字段
try {
ExportUtils excelExport = new ExportUtils();
excelExport.setData(certificateInfo);
excelExport.setHeardKey(properties);
excelExport.setFontSize(14);
excelExport.setSheetName(sheetTitle);
excelExport.setTitle(sheetTitle);
excelExport.setHeardList(title);
pathUrl = excelExport.exportExport(endPoint,accessKeyId,accessKeySecret,bucketName,request, response);
} catch (IOException e) {
logger.info("导出失败");
}
前端传入表头和对应字段处理:
let exportColumns = this.$refs.crud.$refs.table.columns
for (let i = 1; i < exportColumns.length - 1; i++) {
if(i == 1){
//表头
this.exportLabels = exportColumns[i].label;
//对应字段
this.exportProperty = exportColumns[i].property;
}else{
this.exportLabels = this.exportLabels + "," + exportColumns[i].label;
this.exportProperty = this.exportProperty + "," + exportColumns[i].property;
}
}