前端:
哈哈哈哈哈33333_111111111eeeeeeeeeeeeeeeeeee_è¯å•å·ç _20200810-174122.xlsx
// 中文解码处理
function decodeUtf8(bytes) {
var encoded = "";
for (var i = 0; i < bytes.length; i++) {
encoded += bytes[i].toString(16);
}
return decodeURIComponent(encoded);
}
// 通过Url下载文件,并通过GET方式
function downloadUrlFileByGet(_url) {
var xhr = new XMLHttpRequest();
xhr.open('GET', _url, true); // 也可以使用POST方式,根据接口来看
xhr.responseType = "blob"; // 返回类型blob
// 定义请求完成的处理函数,请求前也可以增加加载框/禁用下载按钮逻辑
xhr.onload = function () {
console.log(this);
// 请求完成
if (this.status !== 200) {
return;
}
var blob = this.response;
console.log(blob);
var _type = blob.type;
console.log(_type);
if (_type === 'application/json') {
var reader2 = new FileReader();
reader2.readAsText(blob, 'utf-8');
reader2.onload = function (e) {
console.info(reader2.result);
var failData = JSON.parse(reader2.result);
top.showSwal("${_res.get('tips')}", failData.msg);
}
} else {
var fileName = xhr.getResponseHeader("content-disposition").split("filename=")[1];
console.log(fileName);
fileName = decodeUtf8(escape(fileName));
console.log(fileName);
var reader = new FileReader();
reader.readAsDataURL(blob); // 转换为base64,可以直接放入a表情href
reader.onload = function (e) {
// 转换完成,创建一个a标签用于下载
var a = document.createElement('a');
a.download = fileName;
a.href = e.target.result;
$("body").append(a); // 修复firefox中无法触发click
a.click();
$(a).remove();
}
}
};
// 发送ajax请求
xhr.send();
}
后端文件导出接口:
@GetMapping("/exportExcel")
public void exportExcel() {
String type = getPara("type");
// 根据导出文件类型设置分布式锁的key & 获取对应key的锁
String lockKey = tableRedisLockKey.replaceFirst("\\{tableName\\}", "tb_cdr_" + type);
String locker = smartRedisLockTemplate.lock(lockKey, 10, 300);
if (null == locker) {
log.error("CdrController exportExcel get lock fail");
renderJson(RestResponse.build(CodeEnum.EXECUTION_FAIL, "下载任务已存在,请稍后再试"));
return;
}
try {
QueryCdrParamVo queryCdrParamVo = new QueryCdrParamVo(this);
// 导出号码1 导出话单2变量和标签3费用报表
if (CdrExportEnum.CDE_TAG_AND_VAR.getType().equals(type)) {
queryCdrParamVo.setIsDelete(0);
cdrSer.exportCdrForVarAndTag(queryCdrParamVo, this, queryCdrParamVo.getTaskId());
} else if (CdrExportEnum.CDR_CHARGE.getType().equals(type)) {
cdrSer.exportCdrForCharge(queryCdrParamVo, this, queryCdrParamVo.getTaskId());
} else if (CdrExportEnum.CDR_LIST.getType().equals(type)) {
queryCdrParamVo.setIsDelete(0);
cdrSer.exportCdrListOfCallOut(queryCdrParamVo, this, queryCdrParamVo.getTaskId());
} else {
queryCdrParamVo.setIsDelete(0);
cdrSer.exportCdrForPhone(queryCdrParamVo, this, queryCdrParamVo.getTaskId());
}
} catch (Exception e) {
log.error("CdrController exportExcel exception : {}", e);
renderJson(RestResponse.build(CodeEnum.SYSTEM_ERROR, CodeEnum.SYSTEM_ERROR.getMsg()));
} finally {
if (null != lockKey && null != locker) {
smartRedisLockTemplate.unlock(lockKey, locker);
}
}
}
导出文件名:
private String spliceFileName(String prodId, String robotId, String fileNameMark, String taskTag) {
Record productRecord = productInfoSer.getRecordByProdIdAndRobotId(prodId, robotId);
String robotName = "";
if (null != productRecord) {
robotName = productRecord.getStr("robot_name");
}
List<Record> prodInfos = productInfoSer.queryRecordsByProdId(prodId);
String prodName = (null != prodInfos && prodInfos.size() > 0) ? prodInfos.get(0).getStr("product_name") : "";
String fileName;
try {
if (StringUtils.isBlank(robotName)) {
fileName = new String((prodName + "_" + fileNameMark).getBytes("utf-8"), "ISO-8859-1");
} else {
fileName = new String((prodName + "_" + robotName + "_" + fileNameMark).getBytes("utf-8"), "ISO-8859-1");
}
if (StringUtils.isBlank(taskTag)) {
fileName += DateUtil.getCurrDate(DateUtil.FORMAT_THREE) + ".xlsx";
} else {
fileName += taskTag + "_" + DateUtil.getCurrDate(DateUtil.FORMAT_THREE) + ".xlsx";
}
} catch (UnsupportedEncodingException e) {
log.error("CDRService spliceFileName e : {}", e);
fileName = DateUtil.getCurrDate(DateUtil.FORMAT_THREE) + ".xlsx";
}
return fileName;
}
excel工具类:
/**
* excel导出,支持500w以上数据导出
*/
public static void exportExcel(Controller controller, String[] headers, String[] columns, String fileName, List<Record> dataList) {
HttpServletResponse response = controller.getResponse();
response.setHeader("content-disposition", "attachment; filename=" + fileName);
response.setHeader("access-control-expose-headers","content-disposition");
response.setContentType("application/msexcel;charset=UTF-8");
response.setCharacterEncoding("utf-8");
OutputStream out = null;
try {
out = response.getOutputStream();
// 创建工作簿
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
// 数据格式化
DataFormat dataFormat = sxssfWorkbook.createDataFormat();
// 设置单元格格式为“文本”格式
CellStyle cellStyle = sxssfWorkbook.createCellStyle();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
// 创建工作表
SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet0");
// 判断header是否为空
if (null == headers || headers.length <= 0) {
// 写入文件
sxssfWorkbook.write(out);
return;
}
// 判断columns是否为空
if (null == columns || columns.length <= 0 || null == dataList || dataList.size() <= 0) {
// 设置head
SXSSFRow sheetRow = sheet.createRow(0);
for (int column = 0; column < headers.length; column++) {
sheetRow.createCell(column).setCellValue(headers[column]);
sheet.setDefaultColumnStyle(column, cellStyle);
}
// 写入文件
sxssfWorkbook.write(out);
return;
}
// 设置数据
int index = 0;
int sheetSize = 1000000;
int sheetCounts = ((dataList.size() / sheetSize) == 0 && dataList.size() > sheetSize) ? (dataList.size() / sheetSize) : (dataList.size() / sheetSize) + 1;
for (int sheetCount = 0; sheetCount < sheetCounts; sheetCount++) {
if (sheetCount > 0) {
sheet = sxssfWorkbook.createSheet("sheet" + sheetCount);
}
int tempSheetSize = 0;
if ((dataList.size() - sheetCount * sheetSize) > sheetSize) {
tempSheetSize = sheetSize;
} else {
tempSheetSize = (dataList.size() - sheetCount * sheetSize);
}
for (int row = 0; row <= tempSheetSize; row++) {
SXSSFRow sheetRow = sheet.createRow(row);
// 设置head
if (row == 0) {
for (int column = 0; column < headers.length; column++) {
sheetRow.createCell(column).setCellValue(headers[column]);
sheet.setDefaultColumnStyle(column, cellStyle);
}
} else { // 设置数据
for (int column = 0; column < headers.length; column++) {
Object record;
if (dataList.size() > index) {
record = dataList.get(index);
if (record instanceof Record) {
Record data = (Record) record;
String value = data.get(columns[column]) == null ? "" : String.valueOf(data.get(columns[column]).toString());
sheetRow.createCell(column).setCellValue(value);
} else if (record instanceof JSONObject) {
JSONObject data = (JSONObject) record;
sheetRow.createCell(column).setCellValue(
data.getString(columns[column]) == null ? "" : data.getString(columns[column]));
} else {
continue;
}
sheet.setDefaultColumnStyle(column, cellStyle);
}
}
index++;
}
}
}
// 写入文件
sxssfWorkbook.write(out);
} catch (Exception e) {
log.error("ExcelUtil exportExcel exception catch : ", e);
} finally {
try {
if (out != null) {
out.flush();
out.close();
}
controller.renderNull();
} catch (IOException e) {
log.error("ExcelUtil exportExcel exception finally catch : " + e);
}
}
}