java实现poi导出_java实现使用poi导出excel

1.首先下载用的jar包

poi-4.1.2.jar

commons-math3-3.6.1.jar

根据系统下载对应的版本

2.java后端

/**

* 获得excel表头信息

*/

private static HSSFSheet getHssfSheet (HSSFSheet sheet,String tableHeader[],short cellNumber,HSSFFont font,HSSFCellStyle style)

{

HSSFHeader header = sheet.getHeader();

header.setCenter("");

HSSFRow row = sheet.createRow(0);

row.setHeight((short)400);

//sheet的头

for(int k = 0;k < cellNumber;k++){

HSSFCell cell = row.createCell((short) k);//创建第0行第k列

cell.setCellValue(tableHeader[k]);//设置第0行第k列的值

sheet.setColumnWidth((short)k,(short)7000);//设置列的宽度

font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.

font.setFontHeight((short)350); //设置单元字体高度

style.setFont(font);//设置字体风格

cell.setCellStyle(style);

}

return  sheet;

}

然后在controller里写

//查询要导出的信息

List ckRegistInfos = bdShouYeService.selectGaiByCkRegistInfo(bdShouYe);

//表头

String tableHeader[]={"测试1","测试2","测试3","测试4"};

就是excel的这样

//表的列数

short cellNumber = (short) tableHeader.length;

//创建一个excel

HSSFWorkbook workbook = new HSSFWorkbook();

//设置表头类型

HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//居中

//设置字体

HSSFFont font = workbook.createFont();

//创建一个sheet

HSSFSheet sheet = workbook.createSheet("1");

HSSFSheet sheet2 = workbook.createSheet("2");

就对应excel

//将表头写进excel

sheet = getHssfSheet(sheet, tableHeader, cellNumber, font, style);

sheet2 = getHssfSheet(sheet2, tableHeader, cellNumber, font, style);

//插入数据

for (int i = 0; i < ckRegistInfos.size(); i++) {

//得到当前具体信息

CkRegistInfo ckRegistInfo = ckRegistInfos.get(i);

//获取下一行

HSSFRow rows = sheet.createRow((short)(i+1));

rows.setHeight((short)400);

//测试1

HSSFCell cell1 = rows.createCell((short)0);

cell1 .setCellValue(ckRegistInfo.getName());

cell1 .setCellStyle(style);

//测试2

HSSFCell cell2= rows.createCell((short)1);

cell2.setCellValue(ckRegistInfo.getCardId());

cell2.setCellStyle(style);

//测试3

HSSFCell cell3= rows.createCell((short)2);

cell3.setCellValue(ckRegistInfo.getSex());

cell3.setCellStyle(style);

//测试4

HSSFCell cell4= rows.createCell((short)3);

cell4.setCellValue(ckRegistInfo.getRegistTime());

cell4.setCellStyle(style);

｝

//写入流，返回信息

String fileName ="体检缺项信息";

try{

BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());

response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes(),"ISO-8859-1"));//filename是下载的xls的名

workbook.write(out);

out.flush();

out.close();

workbook.close();

} catch (Exception e) {

e.printStackTrace();

}

3.前端

function Excel()

{

var url = url ;

var xhr = new XMLHttpRequest();

xhr.open('post', url, true);

xhr.responseType = 'blob';

xhr.setRequestHeader('Content-Type', 'application/json;charset=utf-8');

xhr.onload = function (e) {

if (this.status == 200) {

var blob = this.response;

var file_name='信息登记.xls';

// for IE

if (window.navigator && window.navigator.msSaveOrOpenBlob) {

document.getElementById('selectGaiExcel').disabled=false;

window.navigator.msSaveOrOpenBlob(blob, file_name);

}

// for Non-IE (chrome, firefox etc.)

else {

document.getElementById('selectGaiExcel').disabled=false;

var a = document.createElement('a');

document.body.appendChild(a);

a.style = 'display: none';

var url = window.URL.createObjectURL(blob);

a.href =  url;

a.download = file_name;

a.click();

a.remove();

window.URL.revokeObjectURL(url);

}

}

}

xhr.send(JSON.stringify({

"beginHappenTime" : beginHappenTime,

"endHappenTime":endHappenTime

}));

}