我们在使用ajax请求去向服务端下载文件时,我们通常从服务端拿到的只是一个字符串,jquery自动的将文件中的内容解析为字符串传给我们,此时,我们便不能再使用jquery的ajax请求来下载文件
前端代码:
<script src="../js/axios.min.js"></script>
$("#carExport").click(function() {
axios.post(basePath + '/excel/car', JSON.stringify(carSearchObject), {
headers: {
'Content-Type': 'application/json; charset=utf-8'
},
responseType: 'blob'
})
.then(function(response) {
var blob = new Blob([response.data])
var downloadElement = document.createElement('a');
var href = window.URL.createObjectURL(blob); //创建下载的链接
downloadElement.href = href;
downloadElement.download = '车辆查询.xlsx'; //下载后文件名
document.body.appendChild(downloadElement);
downloadElement.click(); //点击下载
document.body.removeChild(downloadElement); //下载完成移除元素
window.URL.revokeObjectURL(href); //释放掉blob对象
console.log(response);
})
.catch(function(error) {
console.log(error);
});
})
后端代码:
ExcelController.java
package com.resafety.port.controller;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import com.resafety.util.ExcelUtilX;
import com.resafety.vo.CarSearchData;
import com.resafety.vo.CarSearchVo;
import com.resafety.vo.FwSearchData;
import com.resafety.vo.FwSearchVo;
import com.resafety.vo.JsySearchData;
import com.resafety.vo.JsySearchVo;
@Controller
@RequestMapping("/excel")
public class ExcelController {
@RequestMapping(value = "/jsy", method = RequestMethod.POST)
@ResponseBody
public void jsyExcel(@RequestBody JsySearchVo jsySearchVo, HttpServletRequest request,
HttpServletResponse response) {
System.out.println("驾驶员信息:" + jsySearchVo.toString());
// excel文件名
String fileName = jsySearchVo.getJsySearchName() + ".xls";
// sheet名
String sheetName = jsySearchVo.getJsySearchName();
// 实际数据
List<Map<String, Object>> realData = new ArrayList<Map<String, Object>>();
// 页面返回数据
List<JsySearchData> jsySearchData = jsySearchVo.getJsySearchData();
for (JsySearchData temp : jsySearchData) {
realData.add(beanToMap(temp));
}
Workbook workbook = ExcelUtilX.getHSSFWorkbook(sheetName, getJsyTitle(), realData, null);
// 响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@RequestMapping(value = "/fw", method = RequestMethod.POST)
@ResponseBody
public void jsyExcel(@RequestBody FwSearchVo fwSearchVo, HttpServletRequest request, HttpServletResponse response) {
System.out.println("服务信息:" + fwSearchVo.toString());
// excel文件名
String fileName = fwSearchVo.getFwSearchName() + ".xls";
// sheet名
String sheetName = fwSearchVo.getFwSearchName();
// 实际数据
List<Map<String, Object>> realData = new ArrayList<Map<String, Object>>();
// 页面返回数据
List<FwSearchData> fwSearchData = fwSearchVo.getFwSearchData();
for (FwSearchData temp : fwSearchData) {
realData.add(beanToMap(temp));
}
Workbook workbook = ExcelUtilX.getHSSFWorkbook(sheetName, getFwTitle(), realData, null);
// 响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@RequestMapping(value = "/car", method = RequestMethod.POST)
@ResponseBody
public void jsyExcel(@RequestBody CarSearchVo carSearchVo, HttpServletRequest request,
HttpServletResponse response) {
System.out.println("车辆信息:" + carSearchVo.toString());
// excel文件名
String fileName = carSearchVo.getCarSearchName() + ".xls";
// sheet名
String sheetName = carSearchVo.getCarSearchName();
// 实际数据
List<Map<String, Object>> realData = new ArrayList<Map<String, Object>>();
// 页面返回数据
List<CarSearchData> carSearchData = carSearchVo.getCarSearchData();
for (CarSearchData temp : carSearchData) {
realData.add(beanToMap(temp));
}
Workbook workbook = ExcelUtilX.getHSSFWorkbook(sheetName, getCarTitle(), realData, null);
// 响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
@RequestMapping(value = "/export")
@ResponseBody
public void export2(HttpServletRequest request, HttpServletResponse response) throws Exception {
// 模拟获取数据
List<Map<String, String>> list = tempData();
// excel标题
String[] title = { "序号", "线路", "年份", "年日均满载率(%)" };
// 内容
String[][] content = new String[list.size()][title.length];
// excel文件名
String fileName = "线路客流满载率.xls";
// sheet名
String sheetName = "线路客流满载率";
for (int i = 0; i < list.size(); i++) {
Map<String, String> obj = list.get(i);
content[i][0] = obj.get("index");
content[i][1] = obj.get("road");
content[i][2] = obj.get("year");
content[i][3] = obj.get("bfb");
}
// 创建HSSFWorkbook
Workbook wb = ExcelUtilX.getHSSFWorkbook(sheetName, title, content, null);
// 响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 发送响应流方法
private void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
private List<Map<String, String>> tempData() {
List<Map<String, String>> temp = new ArrayList<Map<String, String>>();
int year = 2015;
int bfb = 98;
for (int i = 1; i <= 6; i++) {
Map<String, String> tempMap = new HashMap<String, String>();
year++;
bfb = bfb - ((int) (Math.random() * (5 - 1) + 1));
tempMap.put("index", i + "");
tempMap.put("road", "线路" + i);
tempMap.put("year", year + "年");
tempMap.put("bfb", bfb + "%");
temp.add(tempMap);
}
return temp;
}
private static Map<String, Object> beanToMap(Object bean) {
if (null == bean)
return null;
try {
Map<String, Object> map = BeanUtils.describe(bean);
// 移除key=class
map.remove("class");
return map;
} catch (Exception e) {
System.out.println("JavaBean-->Map转换失败:" + e.getMessage());
e.printStackTrace();
return null;
}
}
/***
* 保证Excel的列正确
*
* @return
*/
private List<ExcelUtilX.TitileBean> getJsyTitle() {
String[] titleid = { "id", "name", "sex", "company", "cyzgzh", "cyzgzlb", "cyzgzyxrq", "badRecordCount",
"negativeCount", "complainCount", "breakLowCount", "status" };
String[] titleName = { "序号", "姓名", "性别", "所属企业", "从业资格证号", "从业资格类别", "资格证有效日期", "不良记录次数", "差评次数", "投诉次数",
"违章次数", "从业状态" };
List<ExcelUtilX.TitileBean> titileBeans = new ArrayList<ExcelUtilX.TitileBean>();
for (int i = 0; i < titleName.length; i++) {
ExcelUtilX.TitileBean bean = new ExcelUtilX.TitileBean();
bean.setId(titleid[i]);
bean.setName(titleName[i]);
titileBeans.add(bean);
}
return titileBeans;
}
private List<ExcelUtilX.TitileBean> getFwTitle() {
String[] titleid = { "id", "plateNumber", "company", "complainTime", "complainMsg" };
String[] titleName = { "序号", "车牌号", "所属企业", "投诉时间", "投诉内容" };
List<ExcelUtilX.TitileBean> titileBeans = new ArrayList<ExcelUtilX.TitileBean>();
for (int i = 0; i < titleName.length; i++) {
ExcelUtilX.TitileBean bean = new ExcelUtilX.TitileBean();
bean.setId(titleid[i]);
bean.setName(titleName[i]);
titileBeans.add(bean);
}
return titileBeans;
}
private List<ExcelUtilX.TitileBean> getCarTitle() {
String[] titleid = { "id", "plateNumber", "company", "yszh", "operateTime", "carType", "haveInsurance" };
String[] titleName = { "序号", "车牌号", "所属企业", "运输证号", "投入运营时间", "车辆类型", "有无保险" };
List<ExcelUtilX.TitileBean> titileBeans = new ArrayList<ExcelUtilX.TitileBean>();
for (int i = 0; i < titleName.length; i++) {
ExcelUtilX.TitileBean bean = new ExcelUtilX.TitileBean();
bean.setId(titleid[i]);
bean.setName(titleName[i]);
titileBeans.add(bean);
}
return titileBeans;
}
}
ExcelUtilX.java
package com.resafety.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author 谢辉
* @Classname ExcelUtilX
* @Description TODO
* @Date 2020/12/9 18:34
*/
public class ExcelUtilX {
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param data 内容
* @param wb Workbook对象: 如果是.xls文件的请传入HSSFWorkbook对象类型
* 如果是.xlsx文件的请传入XSSFWorkbook对象类型 默认使用HSSFWorkbook对象类型
* @return
*/
public static Workbook getHSSFWorkbook(String sheetName, String[] title, String[][] data, Workbook wb) {
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if (wb == null) {
// 默认使用兼容.xls文件的
wb = new HSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
Row row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
// style.setVerticalAlignment(VerticalAlignment.CENTER);
// 声明列对象
Cell cell = null;
// 创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
// 创建内容
for (int i = 0; i < data.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < data[i].length; j++) {
// 将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(data[i][j]);
}
}
return wb;
}
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param data 内容
* @param wb Workbook对象: 如果是.xls文件的请传入HSSFWorkbook对象类型
* 如果是.xlsx文件的请传入XSSFWorkbook对象类型 默认使用HSSFWorkbook对象类型
* @return
*/
public static Workbook getHSSFWorkbook(String sheetName, List<ExcelUtilX.TitileBean> title,
List<Map<String, Object>> data, Workbook wb) {
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
if (title == null || title.size() <= 0) {
throw new IllegalArgumentException("传入的title参数有问题");
}
if (data == null) {
data = new ArrayList<Map<String, Object>>();
}
if (data.size() > 0) {
Map<String, Object> map = data.get(0);
if (map.size() != title.size()) {
throw new IllegalArgumentException("传入的title列数和实际数据列数不一致!");
}
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
Row row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
// style.setVerticalAlignment(VerticalAlignment.CENTER);
// 声明列对象
Cell cell = null;
int titleLength = title.size();
// 创建标题
for (int i = 0; i < titleLength; i++) {
cell = row.createCell(i);
cell.setCellValue(title.get(i).getName());
cell.setCellStyle(style);
}
// 创建内容
for (int i = 0; i < data.size(); i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < titleLength; j++) {
// 将内容按顺序赋给对应的列对象
Cell tempCell = row.createCell(j);
// 这里为了代码清晰,每一步都写出来
Map<String, Object> map = data.get(i);
Object objectValue = map.get(title.get(j).getId());
tempCell.setCellValue(objectValue.toString());
// tempCell.setCellStyle(style);
}
}
return wb;
}
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param data 内容
* @param wb Workbook对象: 如果是.xls文件的请传入HSSFWorkbook对象类型
* 如果是.xlsx文件的请传入XSSFWorkbook对象类型 默认使用HSSFWorkbook对象类型
* @return
*/
public static Workbook getHSSFWorkbook(String sheetName, List<LinkedHashMap<String, Object>> data, Workbook wb) {
// 创建title
List<String> title = new ArrayList<String>();
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
if (data == null) {
data = new ArrayList<LinkedHashMap<String, Object>>();
}
if (data.size() > 0) {
// 因为LinkedHashMap保存了插入时的顺序
LinkedHashMap<String, Object> linkedHashMap = data.get(0);
Set<Entry<String, Object>> entrySet = linkedHashMap.entrySet();
for (Entry<String, Object> entry : entrySet) {
// 保存title,使用key值作为title
title.add(entry.getKey());
}
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
Row row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
CellStyle style = wb.createCellStyle();
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
// style.setVerticalAlignment(VerticalAlignment.CENTER);
// 声明列对象
Cell cell = null;
int titleLength = title.size();
// 创建标题
for (int i = 0; i < titleLength; i++) {
cell = row.createCell(i);
cell.setCellValue(title.get(i));
cell.setCellStyle(style);
}
// 创建内容
for (int i = 0; i < data.size(); i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < titleLength; j++) {
// 将内容按顺序赋给对应的列对象
Cell tempCell = row.createCell(j);
// 这里为了代码清晰,每一步都写出来
Map<String, Object> map = data.get(i);
Object objectValue = map.get(title.get(j));
tempCell.setCellValue(objectValue.toString());
// tempCell.setCellStyle(style);
}
}
return wb;
}
/**
* 读取Excel数据,简单的行列数据(数据表格式),不适用复杂的数据
*
* @param excelPath 文件路径
* @return 表格数据
* @throws IOException
*/
public static List<Map<String, Object>> readExcel(String excelPath) throws IOException {
Workbook wb = null;
InputStream is = new FileInputStream(excelPath);
int indexPoint = excelPath.lastIndexOf(".");
String suffixName = "";
if (indexPoint != -1) {
suffixName = excelPath.substring(indexPoint + 1);
System.out.println("后缀名是:" + suffixName);
}
// 根据文件后缀(xls/xlsx)进行判断
if ("xls".equalsIgnoreCase(suffixName)) {
wb = new HSSFWorkbook(is);
} else if ("xlsx".equalsIgnoreCase(suffixName)) {
wb = new XSSFWorkbook(is);
} else {
throw new IllegalArgumentException("文件类型错误!");
}
List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
List<String> keys = new ArrayList<String>();
// 遍历sheet页
for (int numSheet = 0, sheetLength = wb.getNumberOfSheets(); numSheet < sheetLength; numSheet++) {
Sheet hssfSheet = wb.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 开始遍历行
if (hssfSheet.getLastRowNum() > 0) {
// 使用第一行作为key
Row row = hssfSheet.getRow(0);
for (int i = row.getFirstCellNum(), keyNum = row.getLastCellNum(); i < keyNum; i++) {
keys.add(row.getCell(i).getStringCellValue());
}
// 遍历数据行(除去第一行,下标从1开始)
for (int j = hssfSheet.getFirstRowNum() + 1, k = hssfSheet.getLastRowNum(); j <= k; j++) {
Map<String, Object> map = new LinkedHashMap<String, Object>();
// 遍历每个列
for (int index = hssfSheet.getRow(j).getFirstCellNum(), cellLength = hssfSheet.getRow(j)
.getLastCellNum(); index < cellLength; index++) {
map.put(keys.get(index), getValue(hssfSheet.getRow(j).getCell(index)));
}
// 存放数据
data.add(map);
}
} // 行结束
} // sheet页for结束
return data;
}
private static String getValue(Cell hssfCell) {
if (hssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellTypeEnum() == CellType.NUMERIC) {
// 返回数值类型的值
// return String.valueOf(hssfCell.getNumericCellValue());
return numOfImport(hssfCell);
} else if (hssfCell.getCellTypeEnum() == CellType.BLANK) {
// 返回空值
return "";
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 数字转换字符串
*
* @param cell
* @return
*/
private static String numOfImport(Cell cell) {
String value = cell.toString();
if (cell.getCellTypeEnum() == CellType.STRING) {// 字符串类型
return value;
} else {
String[] str = value.split("\\.");
if (str.length > 1) {
String str1 = str[1];
int m = Integer.parseInt(str1);
if (m == 0) {
return str[0];
} else {
return value;
}
} else {
return value;
}
}
}
public static class TitileBean {
private String id;
private String name;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "TitileBean [id=" + id + ", name=" + name + "]";
}
}
}
vo:不仔细展示了,就是前端数据的封装,注意尽量不要使用内部类,是个坑,@RequestBody封装不上
代码待完善,暂时功能是测通了。