/**
* 导出数据
* @param col 数据列头
* @param list 数据集
* @return
*/
public static HSSFWorkbook exportData(String col[],List<Map<String, Object>> list){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet1");
sheet.setDefaultRowHeight((short)30); //设置默认行高
HSSFRow hsffRow = null;
//创建第一行
hsffRow = sheet.createRow(0);
int i = 0;
for(String column_name : col){
HSSFCell cell = hsffRow.createCell(i);
cell.setCellValue(new HSSFRichTextString(column_name));
cell.setCellStyle(getStyle(wb,16,true));
sheet.autoSizeColumn((short)i);
i++;
}
//插入的行数
int insertrow = 1;
for(Map map : list){
hsffRow = sheet.createRow(insertrow);
Iterator ite = map.entrySet().iterator();
int j = 0;
while(ite.hasNext()){
Map.Entry<Object, Object> entry = (Entry<Object, Object>) ite.next();
Object key = entry.getKey();
Object value = entry.getValue();
HSSFCell cell = hsffRow.createCell((short) j);
cell.setCellStyle(getStyle(wb,12,false));
cell.setCellValue(value == null ? "" : value.toString());
j ++ ;
}
insertrow ++;
}
return wb;
}
//获取表格样式
public static HSSFCellStyle getStyle(HSSFWorkbook wb,int fontSize,boolean isFontBold){
//标题字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) fontSize);// 字体大小
if(isFontBold){
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
}
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
style.setWrapText(true); //自动换行
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft((short) 1);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderRight((short) 1);
style.setBorderTop((short) 1);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色.
style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色.
return style;
}
应用:
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
String stuCol[] = {"姓名","性别","民族","出生日期","文化程度","政治面貌"};
HSSFWorkbook wb = exportData(stuCol,list);
if(request.getHeader("user-agent").indexOf("MSIE") != -1) {
reportTitle = java.net.URLEncoder.encode(reportTitle,"utf-8") + ".xls";
} else {
reportTitle = new String(reportTitle.getBytes("utf-8"),"iso-8859-1") + ".xls";
}
ServletOutputStream out = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+ reportTitle);
wb.write(out);
out.close();