poi导出excel,显示无法打开文件,急！！
poi导出excel,显示无法打开文件,在指定文件目录下可以完美打开,网络传输则显示
poi版本
控制层代码：
public void exportModel(@RequestBody Map<String,Object> map, HttpServletRequest request, HttpServletResponse response){
//查询数据
List partNumber1 = (List) map.get("partNumber");
if (partNumber1.size() == 0){
map.remove("partNumber");
}
int total = pimCatentryMaterialService.queryModelTotal(map);
List<ModelImageCustomEntity> modelLists = null;
if (total>0) {
modelLists = pimCatentryMaterialService.queryModelImage(map);
}else {
modelLists = new ArrayList<>();
}
if (!modelLists.isEmpty()){
//按照款号分组
Map<String, List<ModelImageCustomEntity>> groupBy = modelLists.stream().collect(Collectors.groupingBy(ModelImageCustomEntity::getPartNumber));
//遍历各个款号的数据,去除不是model的数据,如果遍历后不为空改变url
Set<String> strings = groupBy.keySet();
List<String> partNumberList = new ArrayList<>(strings);
for (String partNumber : partNumberList) {
List<ModelImageCustomEntity> modelList = groupBy.get(partNumber);
if (!modelList.isEmpty()){
List<ModelImageCustomEntity> modelList1 = new ArrayList<>();
for (ModelImageCustomEntity modelImageCustomEntity : modelList) {
if (modelImageCustomEntity.getPosition().equals("model")){
modelList1.add(modelImageCustomEntity);
}
}
//如果为空设置默认需要的值,没有url
if (modelList1.isEmpty()){
ModelImageCustomEntity modelImageCustomEntity = modelList.get(0);
modelImageCustomEntity.setMaterialUrl(null);
modelList1.add(modelImageCustomEntity);
}
//重新设置url
for (ModelImageCustomEntity modelImageCustomEntity : modelList1) {
if (modelImageCustomEntity.getMaterialUrl()!=null){
String materialUrl = modelImageCustomEntity.getMaterialUrl();
String temp = materialUrl.replaceAll(OSSFactory.ossAccessDoMain, OSSFactory.accessDoMain);
modelImageCustomEntity.setMaterialUrl(temp);
}
}
//重新赋值款号下数组
groupBy.put(partNumber,modelList1);
}
}
//导出excel
XSSFWorkbook workbook = new XSSFWorkbook();
// 字体
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 20);// 字号
XSSFCellStyle styleTitle = workbook.createCellStyle();
styleTitle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
styleTitle.setFont(font);
XSSFCellStyle cellStyle = workbook.createCellStyle();
// 创建sheet页
Sheet sheet = workbook.createSheet();
Row row;
Cell cell;
row = sheet.createRow(0);
Cell cell1 = row.createCell(0);
cell1.setCellStyle(styleTitle);
cell1.setCellValue("款号");
Cell cell2 = row.createCell( 1);
cell2.setCellStyle(styleTitle);
cell2.setCellValue("模特图");
//设置头
Set<String> parts = groupBy.keySet();
List<String> part = new ArrayList<>(parts);
int rows = 1;
int start = 1;
CreationHelper createHelper = workbook.getCreationHelper();
for(int i = 0; i < part.size(); i++) {
start = rows;
//款号
List<ModelImageCustomEntity> n = groupBy.get(part.get(i));
//图片
for (int j = 0;j<n.size();j++){
row = sheet.createRow(rows);
//如果遍历到最后一个,放入款号
if (j ==0){
Cell cell3 = row.createCell(0);
cell3.setCellValue(part.get(i));
}
String materialUrl = n.get(j).getMaterialUrl();
if (materialUrl != null) {
XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress(materialUrl);
Cell cell4 = row.createCell(1);
cell4.setHyperlink(link);// 设置超链接
cell4.setCellValue(n.get(j).getMaterialUrl());
if (j != 0) {
Cell cell3 = row.createCell(0);
cell3.setCellValue("");
}
}else {
Cell cell4 = row.createCell(1);
cell4.setCellValue("");
}
rows++;
}
if (start != rows-1) {
CellRangeAddress region = new CellRangeAddress(start,rows-1,0,0);
sheet.addMergedRegion(region);
}
sheet.setColumnWidth(0,100*40);
sheet.setColumnWidth(1,100*256);
}
try {
String fileName = "模特图片信息.xlsx";
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName,"UTF-8") );//要保存的文件名
ServletOutputStream os = response.getOutputStream();
//----------------
File file = new File("E:\\model\\demox1.xlsx");
if (!file.exists()){
file.createNewFile();
}
FileOutputStream fout = null;
fout = new FileOutputStream(file);
workbook.write(fout);
fout.close();
//---------------
workbook.write(os);
os.flush();
os.close();
workbook.close();
logger.info("导出完成！");
}catch (IOException e){
e.printStackTrace();
}
}
}
前端request请求：
import axios from 'axios'
import { MessageBox, Message } from 'element-ui'
import store from '@/store'
import { getToken } from '@/utils/auth'
// create an axios instance
const service = axios.create({
baseURL: process.env.BASE_API, // url = base url + request url
// withCredentials: true, // send cookies when cross-domain requests
timeout: 600000 // request timeout
})
// request interceptor
service.interceptors.request.use(
config => {
// do something before request is sent
if(store.getters.token) {
// let each request carry token
// ['X-Token'] is a custom headers key
// please modify it according to the actual situation
config.headers['X-Token'] = getToken()
}
return config
},
error => {
// do something with request error
console.log(error) // for debug
return Promise.reject(error)
}
)
// response interceptor
service.interceptors.response.use(
/**
* If you want to get http information such as headers or status
* Please return response => response
*/
/**
* Determine the request status by custom code
* Here is just an example
* You can also judge the status by HTTP Status Code
*/
response => {
debugger
const res = response
if(response.headers.sessionstatus != undefined) {
var usernamenotfound = JSON.parse(response.headers.sessionstatus);
if(usernamenotfound.code === '401') {
Message({
message: '登录失效，请重新登录！' || 'Error',
type: 'error',
duration: 5 * 1000
})
setTimeout(function() {
location.reload();
}, 1000);
return Promise.reject(new Error(res.message || 'Error'))
} else if(usernamenotfound.code === '500') {
Message({
message: '无权限操作!' || 'Error',
type: 'error',
duration: 5 * 1000
})
return Promise.reject(new Error(res.message || 'Error'))
} else if(usernamenotfound.code === '501') {
Message({
message: '权限发生改变，请重新登录!' || 'Error',
type: 'error',
duration: 5 * 1000
})
return Promise.reject(new Error(res.message || 'Error'))
}
} else {
if(res.meta && !res.meta.success) {
var msg = res.meta != undefined ? res.meta.message : res.message
if(msg != 'error') {
Message({
message: msg || 'Error',
type: 'error',
duration: 5 * 1000
})
}
return Promise.reject(new Error(msg || 'Error'))
} else if(res.success && !res.success) {
var msg = res.meta != undefined ? res.meta.message : res.message
Message({
message: msg || 'Error',
type: 'error',
duration: 5 * 1000
})
} else {
return res;
}
}
},
error => {
if(error.response.headers.sessionstatus != undefined) {
var sessionstatus = JSON.parse(error.response.headers.sessionstatus);
if(sessionstatus.code === '401') {
Message({
message: '登录失效，请重新登录！' || 'Error',
type: 'error',
duration: 5 * 1000
})
setTimeout(function() {
location.reload();
}, 1000);
return Promise.reject(error)
} else if(sessionstatus.code === '500') {
Message({
message: '无权限操作!' || 'Error',
type: 'error',
duration: 5 * 1000
})
return Promise.reject(error)
} else if(usernamenotfound.code === '501') {
Message({
message: '权限发生改变，请重新登录!' || 'Error',
type: 'error',
duration: 5 * 1000
})
return Promise.reject(error)
}
} else if(error.response && error.response.status && error.response.status == 500) {
Message({
message: '服务器连接繁忙！' || 'Error',
type: 'error',
duration: 5 * 1000
})
return Promise.reject(error)
}
console.log('err' + error) // for debug
Message({
message: error.message,
type: 'error',
duration: 5 * 1000
})
return Promise.reject(error)
}
)
export default service
前端js方法
export function exportModel(data) {
return requestExcel({
url: process.env.BASE_PIM_API + '/pimCatentryMaterial/exportModel',
method: 'post',
responseType: 'blob',
data
})
}
前端方法：
downExcel(){
let partNumber;
if (this.textareaData){
partNumber = this.textareaData.trim().split(/\s+/)
}else {
partNumber = []
}
this.listQuery.partNumber=partNumber
debugger
exportModel(this.listQuery).then(res=>{
debugger
const link = document.createElement('a');
//根据客户需求选择excel后缀格式 以下二选一
// let blob = new Blob([res.data], {type: 'application/vnd.ms-excel'}); //xls
let blob = new Blob([res.data], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'}); //xlsx
link.style.display = 'none';
//设置连接
link.href = URL.createObjectURL(blob);
link.download = '模特信息';
document.body.appendChild(link);
//模拟点击事件
link.click();
URL.revokeObjectURL(link.href);// 释放url
document.body.removeChild(link) // 释放标签
})
},
可以先接口确认下是前端的问题还是后端问题
用office Excel打开看下 内容是不是乱码
检查一下网络导出的文件大小和直接生成的大小一致不一致
一定要加** responseType: 'blob'**
你解决了吗 我也遇到同样的问题了