jsp:<td style="vertical-align:top;"><a class="btn btn-mini btn-light" style="margin-bottom: 5px;" οnclick="toExcel();" title="导出到EXCEL">
<i id="nav-search-icon" class="icon-download-alt"></i></a></td>
java:
/*/
* 导出excel
*/
@RequestMapping(value="/doexcel")
public ModelAndView doexcel(){
ModelAndView mv=new ModelAndView();
PageData pd=new PageData();
pd=this.getPageData();
try {
Map< String , Object > map=new HashMap<>();
List<String > titles=new ArrayList<String>();
//获得表的标题
titles.add("姓名");
titles.add("学号");
titles.add("入学年份");
titles.add("毕业年份");
titles.add("性别");
titles.add("院");
titles.add("系(部)");
titles.add("专业");
titles.add("班级");
map.put("titles", titles);
//获得学生数据
List<PageData> student=studentService.getStudentAll(pd);
List<PageData > varList=new ArrayList<PageData>();
for(PageData list : student){
PageData var=new PageData();
//把相应的数据放入相应的标题里
var.put("var1", list.getString("name"));
var.put("var2", list.getString("studentId"));
/* var.put("var3", list.getString("name"));
var.put("var4", list.getString("name"));*/
var.put("var5", list.getString("sex"));
/*var.put("var6", list.getString("collegeId"));*/
var.put("var7", list.getString("collegeName"));
var.put("var8", list.getString("majorName"));
var.put("var9", list.getString("className"));
varList.add(var);
}
map.put("varList", varList);
//ObjectsExcelView是写好的工具类
ObjectsExcelView erv = new ObjectsExcelView();
mv = new ModelAndView(erv,map);
} catch (Exception e) {
// TODO: handle exception
logger.error(e.toString(), e);
}
return mv;
}
//工具类
public class ObjectsExcelView extends AbstractExcelView{
private String[] sheetNames;
public String[] getSheetNames() {
return sheetNames;
}
public void setSheetNames(String[] sheetNames) {
this.sheetNames = sheetNames;
}
@Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
HSSFSheet sheet;
HSSFCell cell;
sheet = workbook.createSheet("sheet1");
List<String> titles = (List<String>)model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
PageData vpd = varList.get(i);
for(int j=0;j<len;j++){
String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,varstr);
}
}
}
}