java自定义模板导出excel
1、前端请求编码
// 自定义按钮导出数据
function exportData() {
var options = $('#exampleTable').bootstrapTable('getOptions');
//通过table的#id 得到每页多少条
var pageSize = options.pageSize;
//通过table的#id 得到当前第几页
var pageNumber = options.pageNumber;
var condition = {
'name': $('#name').val(),
};
var param = {
size: pageSize, // 如果设置了分页,每页数据条数
current: pageNumber, // 如果设置了分布,首页页码
condition: JSON.stringify(condition)
};
var ee = encodeParam(param);
var rr = encodeURI(ee);
//定义url //自己的访问路径prefix
var url = prefix + "/exportExcel?" + rr;
var xhr = new XMLHttpRequest();
xhr.open('GET', url, true); // 也可以使用POST方式,根据接口
xhr.responseType = "blob"; // 返回类型blob
// 定义请求完成的处理函数,请求前也可以增加加载框/禁用下载按钮逻辑
xhr.onload = function () {
// 请求完成
if (this.status === 200) {
// 返回200
var blob = this.response;
var reader = new FileReader();
reader.readAsDataURL(blob); // 转换为base64,可以直接放入a表情href
reader.onload = function (e) {
// 转换完成,创建一个a标签用于下载
var a = document.createElement('a');
var nowDate = moment().format('YYYYMMDDHHmmssSSS');
a.download = '文件' + nowDate + '.xls';
a.href = e.target.result;
$("body").append(a); // 修复firefox中无法触发click
a.click();
$(a).remove();
}
}
};
// 发送ajax请求
xhr.send()
};
function encodeParam(json) {
var tmps = [];
for (var key in json) {
tmps.push(key + '=' + json[key]);
}
return tmps.join('&');
}
2、后端处理代码
/**
* 导出excel
*/
@ResponseBody
@GetMapping(value = "/exportExcel")
public void exportExcel(@RequestParam Map<String, Object> _params, HttpServletResponse response) {
Map<String, Object> params = new HashMap<>();
_params.forEach((k, v) -> {
params.put(k.trim(), v);
});
List<pojo> list = xxxSevice.list(params );
if (CollectionUtils.isNotEmpty(list)) {
//excel标题
String[] title = {"",""};
//说明
String[] explain = {"",""};
String fileName = "xxx.xlsx";
//sheet名
String sheetName = "xxx";
int total = list.size();
String[][] content = new String[list.size()][title.length];
List<pojo> pojoList = JSON.parseArray(list.toString(), pojo.class);
for (int i = 0; i < total; i++) {
Pojo pojo= pojoList .get(i);
//
content[i][0] = "";
//
content[i][1] = "";
//
content[i][2] = StringUtils.isBlank(pojoList .getName()) ? "" : pojoList .getName();
}
//创建模板
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(sheetName);
//第一行
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellValue("ds");
HSSFCell cell_01 = row0.createCell(1);
cell_01.setCellValue("8.5.0");
//第二行
HSSFRow row01 = sheet.createRow(1);
HSSFCell cell_10 = row01.createCell(0);
cell_10.setCellValue("表格");
HSSFCell cell_11 = row01.createCell(1);
cell_11.setCellValue("题目");
//合并单元格
HSSFCell cell_20 = row0.createCell(2);
cell_20.setCellValue("内容");
CellRangeAddress region = new CellRangeAddress(0, 1, 2, 13);
sheet.addMergedRegion(region);
//声明列对象
HSSFCell cell = null;
HSSFRow row02 = sheet.createRow(2);
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row02.createCell(i);
cell.setCellValue(title[i]);
}
HSSFRow row03 = sheet.createRow(3);
//字段说明
for (int i = 0; i < explain.length; i++) {
cell = row03.createCell(i);
cell.setCellValue(explain[i]);
}
//创建内容
for (int i = 0; i < content.length; i++) {
row03 = sheet.createRow(i + 4);
for (int j = 0; j < content[i].length; j++) {
//将内容按顺序赋给对应的列对象
row03.createCell(j).setCellValue(content[i][j]);
}
}
//创建HSSFWorkbook
HSSFRow row3 = sheet.getRow(2);
HSSFFont font = workbook.createFont();
font.setBold(true);
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (int i = 0, c = title.length; i < c; i++) {
sheet.autoSizeColumn(i, true);
row3.getCell(i).setCellStyle(style);
}
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
} catch (Exception e) {
log.error("excel写出异常" + e.getMessage());
}
}
}
private void setResponseHeader(HttpServletResponse response, String fileName) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename="+ fileName);
} catch (Exception ex) {
log.info("发送响应流方法异常,原因=【{}】", ex.getMessage());
}
}
拙见按需参考!