poi导出excel 损坏_使用POI导出Excel例子与常见问题

代码

function toExcel(){

$("#formExcel").submit();

}

@RequestMapping(value="/toExcel",method=RequestMethod.POST)

public String toExcel(HttpServletResponse response, String fileName, String tableName, String type, String where){

if (StringUtils.isNotEmpty(fileName) && StringUtils.isNotEmpty(tableName) && StringUtils.isNotEmpty(type)) {

inteQueryService.toExcel(response,fileName,tableName,type,where);

return null;

}

return null;

}

public static void toExcel(HttpServletResponse response,List> lists,String name,String[] strArr){

List[] totals = splitList(lists,65000);

// 创建工作簿

HSSFWorkbook wb = new HSSFWorkbook();

// 由工作簿创建工作表

for (int j = 0; j < totals.length; j++) {

HSSFSheet sheet = wb.createSheet("sheet"+(j+1));

// 在工作表中创建行

HSSFRow row = sheet.createRow(0);

// 创建单元格,设置每个单元格的字段名

HSSFCell cell = null;

for (int i = 0; i < strArr.length; i++) {

cell = row.createCell(i);

cell.setCellValue(strArr[i]);

}

List> list = totals[j];

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

Iterator iter = list.get(i).entrySet().iterator();

row = sheet.createRow(i+1);

cell = row.createCell(0);

cell.setCellValue(i+1);

int index = 1;

while (iter.hasNext()) {

Map.Entry entry = (Map.Entry) iter.next();

//Object key = entry.getKey();

Object val = entry.getValue();

cell = row.createCell(index);

cell.setCellValue(val.toString());

index++;

}

}

}

ServletOutputStream sos = null;

try {

String fileName = name+".xls";

response.setContentType("application/vnk.ms-excel");

response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

response.setContentType("application/msexcel;charset=utf-8");

response.resetBuffer();

sos = response.getOutputStream();

wb.write(sos);

sos.flush();

} catch (IOException e) {

logger.error("导出excel工具类报错",e);

} finally {

try {

sos.close();

} catch (IOException e) {

logger.error("导出excel工具类报错",e);

}

}

}

public static List[] splitList(List list, Integer pageSize) {

if(pageSize == null){

pageSize = 300;

}

int total = list.size();

//总页数

int pageCount = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;

List[] result = new List[pageCount];

for(int i = 0; i < pageCount; i++) {

int start = i * pageSize;

//最后一条可能超出总数

int end = start + pageSize > total ? total : start + pageSize;

List subList = list.subList(start, end);

result[i] = subList;

}

return result;

}

常见问题

前台一定要使用表单的方式进行提交，ajax是不行的，因为ajax只能接受 xml、 json、html等类似字符串的返回，不能接受流作为返回值。否则程序不报错，就是不弹下载框。