Java读取blob生成excel_java使用Poi实现从服务器导出excel(前端使用vue)

后端使用的是springboot，前端用的是vue+axios(坑点多)

话不多说直接上代码

Controller

@PostMapping(value = "/getExcel",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)

public void exportApi(HttpServletResponse response) throws IOException {

logDetailService.exportExcel(response);

}

就是一个简单的调用service层代码

下面是service。接口不写了，直接写impl

@Override

public void exportExcel(HttpServletResponse response, String createBy, String time) throws IOException {

//得到要导出的数据，存放到list中

ListlogList = logApiInfo();

XSSFWorkbook workbook = new XSSFWorkbook();

XSSFSheet sheet = workbook.createSheet("统计表");

createTitle(workbook,sheet);

//设置日期格式

XSSFCellStyle style = workbook.createCellStyle();

style.setDataFormat(new XSSFDataFormat().getFormat("m/d/yy h:mm"));

//新增数据行，并且设置单元格数据

int rowNum=1;

for (LogDetailInfo log :logList) {

XSSFRow row = sheet.createRow(rowNum);

row.createCell(0).setCellValue(log.getCreateBy());

row.createCell(1).setCellValue(log.getRemoteIp());

row.createCell(2).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(log.getCreateTime()));

row.createCell(3).setCellValue(log.getTitle());

XSSFCell cell = row.createCell(4);

cell.setCellStyle(style);

rowNum++;

}

String fileName = "导出表";

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("gbk"), "iso8859-1")+".xlsx");

OutputStream out = response.getOutputStream();

try {

workbook.write(out);

} catch (Exception e) {

e.printStackTrace();

} finally {

out.flush();

out.close();

}*/

}

使用到的两个方法

private void createTitle(XSSFWorkbook workbook, XSSFSheet sheet){

XSSFRow row = sheet.createRow(0);

//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度

sheet.setColumnWidth(0,30*256);

sheet.setColumnWidth(1,30*256);

sheet.setColumnWidth(2,30*256);

sheet.setColumnWidth(3,17*256);

//设置为居中加粗

XSSFCellStyle style = workbook.createCellStyle();

XSSFFont font = workbook.createFont();

font.setBold(true);

// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

style.setFont(font);

XSSFCell cell;

cell = row.createCell(0);

cell.setCellValue("操作人");

cell.setCellStyle(style);

cell = row.createCell(1);

cell.setCellValue("请求ip");

cell.setCellStyle(style);

cell = row.createCell(2);

cell.setCellValue("记录时间");

cell.setCellStyle(style);

cell = row.createCell(3);

cell.setCellValue("操作描述");

cell.setCellStyle(style);

}

//这个方法，大家改成自己获取要导出的数据的方法，我这里是从数据库直接拿的

public ListlogApiInfo() {

String beginTime=null;

String endTime=null;

if (timeString!=null){

String[] timeStrs = timeString.split(",");

beginTime = timeStrs[0];

endTime = timeStrs[1];

}

return baseMapper.selectLogApi();

}

这 里是后端，下面为Vue端

exportLog().then((res)=>{

const _res = res.data;

let blob = new Blob([_res], {type: 'application/vnd.ms-excel;charset=utf-8'});

let downloadElement = document.createElement("a");

let href = window.URL.createObjectURL(blob); //创建下载的链接

downloadElement.href = href;

// downloadElement.download = fileName; //下载后文件名x

downloadElement.download = "商城后台管理接口日志导出表.xlsx"; //下载后文件名

document.body.appendChild(downloadElement);

downloadElement.click(); //点击下载

document.body.removeChild(downloadElement); //下载完成移除元素

window.URL.revokeObjectURL(href); //释放掉blob对象

}, error => {

console.log(error);

});

这里最重要的是在写axios中写datatype为blob

/*

* 导出excel

* */

export const exportLog =()=>{

return request({

url:'/logdetail/getExcel',

method:'post',

headers: {

'Content-Type': 'application/x-www-form-urlencoded',

},

responseType: 'blob',

params:{

createBy,

time,

list,

}

})

}

出现什么问题，可以留言告我，