一,pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2,在src/main目录下创建webapp目录,新建excel/dwxx/dwxz.xlsx模板
三,导出方法,此处可对列赋值等进行封装
@PostMapping(value = "/download")
public void download(HttpServletResponse response, HttpServletRequest request,@RequestParam Map<String, Object> params) throws IOException {
//queryData = request.getParameter("queryData");
//request.get
log.info("单位险种增减信息开始导出。。。。。");
//System.out.println(params);
XSSFWorkbook wb = null;
try {
// excel模板路径
String basePath = request.getSession().getServletContext().getRealPath("/");
//InputStream asStream = DwxzzjController.class.
// getClassLoader().getResourceAsStream("excel/dwxx/dwxz.xlsx");
//URL resource = DwxzzjController.class.getResource("/");
String excel = basePath + "/excel/dwxx/dwxz.xlsx";
//File fi = new File("D:\\bp\\dwxz.xlsx");
File fi = new File(excel);
InputStream asStream = new FileInputStream(fi);
// 读取excel模板
wb = new XSSFWorkbook(asStream);
// 读取了模板内所有sheet内容
XSSFSheet sheet = wb.getSheetAt(0);
// 在相应的单元格进行赋值
int rowIndex = 3;
for (int i=0;i<=10;i++) {
XSSFRow row = sheet.getRow(rowIndex);
if (null == row) {
row = sheet.createRow(rowIndex);
}
XSSFCell cell0 = row.getCell(0);
if (null == cell0) {
cell0 = row.createCell(0);
}
cell0.setCellValue(i);// 标识
XSSFCell cell1 = row.getCell(1);
if (null == cell1) {
cell1 = row.createCell(1);
}
cell1.setCellValue("rr"+i);// 用户名
XSSFCell cell2 = row.getCell(2);
if (null == cell2) {
cell2 = row.createCell(2);
}
cell2.setCellValue("dd"+i);// 头像
XSSFCell cell3 = row.getCell(3);
if (null == cell3) {
cell3 = row.createCell(3);
}
cell3.setCellValue("ee"+i);// 性别
XSSFCell cell4 = row.getCell(4);
if (null == cell4) {
cell4 = row.createCell(4);
}
cell4.setCellValue("ff"+i);// 手机
XSSFCell cell5 = row.getCell(5);
if (null == cell5) {
cell5 = row.createCell(5);
}
cell5.setCellValue("ff"+i);// 手机
XSSFCell cell6 = row.getCell(6);
if (null == cell6) {
cell6 = row.createCell(6);
}
cell6.setCellValue("ff"+i);// 手机
XSSFCell cell7 = row.getCell(7);
if (null == cell7) {
cell7 = row.createCell(7);
}
cell7.setCellValue("ff"+i);// 手机
XSSFCell cell8 = row.getCell(8);
if (null == cell8) {
cell8 = row.createCell(8);
}
cell8.setCellValue("ff"+i);// 手机
rowIndex++;
}
String fileName = "单位险种增减信息";
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
ServletOutputStream sout = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(sout);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
log.info(e.getMessage());
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
} catch (Exception e) {
log.info(e.getMessage());
}
}
四前端方法
$("#export-btn").on('click',
function() {
// var data= JSON.stringify($('#cbdw').bootstrapTable('getData',false));
// var param = data;
// var param = {"name":"list"};
var data = getData();
postExcelFile(data,"/dwxzzj/download");
});
function getData(){
console.log($('#dataForm').serializeJSON());
console.log(JSON.stringify($('#dataForm').serializeJSON()));
return $('#dataForm').serializeJSON();
}
// 添加列表导出方法
function postExcelFile(params, url) { //params是post请求需要的参数,url是请求url地址
var form = document.createElement("form");
alert(params);
form.style.display = 'none';
form.action = url;
form.method = "post";
document.body.appendChild(form);//设置请求格式
for (var key in params) {
//alert(params[key])
if(typeof params[key] == 'object') {
//for(let i = 0; i < params[key].length; i++) {
var input = document.createElement("input");
input.type = "hidden";
input.name = key;
//console.log(key);
input.value = params[key][i];
form.appendChild(input);
}
else{
var input = document.createElement("input");
input.type = "hidden";
input.name = key;
input.value = params[key];
form.appendChild(input);
}
}// 请求内容整理,按实际情况进行处理
form.submit();
form.remove();
}