导出excel=下载+生成excel

//创建excel

/**

* @return

*/

public  void createExcel(){  

String  dormBuildCode = "01";

//得到想要转成excel的数据

List<HealthRecordVO> healthRecordVOs =this.healthRecordService.searchHealthRecordVOsBydormBuildCode(dormBuildCode);

//设置工作表的格式  

workbook  =new HSSFWorkbook();

//创建一个可写入的工作表 

this.createSheet("sheet1");

//------------------------设置表头

//宿舍 辅导员 班级 地面 门窗 床铺 物品摆放 电线 总分 记录人 检查日期

List<String> headerList = newArrayList<String>();

headerList.add("宿舍");

headerList.add("辅导员");

headerList.add("班级");

headerList.add("地面");

headerList.add("门窗");

headerList.add("床铺");

headerList.add("物品摆放");

headerList.add("电线");

headerList.add("总分");

headerList.add("记录人");

headerList.add("检查日期");

try {

//添加表头

this.addHeader(headerList, true);

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

HealthRecordVO healthRecordVO = healthRecordVOs.get(i);

//宿舍 辅导员 班级 地面 门窗 床铺 物品摆放 电线 总分 记录人 检查日期

List dataList = new ArrayList();

dataList.add(healthRecordVO.getDormitory()); //宿舍

dataList.add(healthRecordVO.getInstructor()); //辅导员

dataList.add(healthRecordVO.getClasses()); //班级

dataList.add(healthRecordVO.getFloor()); //地面

dataList.add(healthRecordVO.getDoorAndWindow()); //门窗

dataList.add(healthRecordVO.getBed()); //床铺

dataList.add(healthRecordVO.getGoods()); //物品摆放

dataList.add(healthRecordVO.getWire()); //电线

dataList.add(healthRecordVO.getScore()); //总分

dataList.add(healthRecordVO.getHouseparent()); //记录人

dataList.add(healthRecordVO.getCheckOfDate()); //检查日期

//添加一行

this.addRow(dataList, i+1);

}

//下载

HttpServletResponse response =ServletActionContext.getResponse();

//设置MIME类型

response.setContentType("application/excel");

response.setCharacterEncoding("GBK");

//提示文件保存的文件名

//为了在浏览器中下载时，提示中文的下载文件名，需要转换为ISO-8859-1编码的字符串

String fileName =dormBuildCode+"号宿舍楼卫生检查表.xls";

fileName = newString(fileName.getBytes("GBK"),"ISO-8859-1");

response.setHeader("Content-Disposition","attachment;filename="+fileName);

ServletOutputStream outStream =response.getOutputStream();

workbook.write(outStream);

outStream.flush();

} catch (Exception e) {

e.printStackTrace();

}

   }

   

    //创建一个可写入的工作表 

protected void createSheet(String sheetName) {

   //Workbook的createSheet 是工作表的名称 

sheet = workbook.createSheet(sheetName);    

//

format = workbook.createDataFormat();

//

hdRow = sheet.createRow(0);

//设置默认行数

sheet.setDefaultRowHeightInPoints(5);

//设置默认列数

sheet.setDefaultColumnWidth( (short) 5  );

   

}

/**

    * 添加表头

    * @param rowvalues

    * @param isFilter

    * @throws Exception

    */

protected void addHeader(List rowvalues, boolean isFilter)throws Exception {

int listlength = rowvalues.size();

// 设置字体

HSSFFont workFont = workbook.createFont();

workFont.setFontName("黑体"); //设置字体类型

workFont.setFontHeightInPoints((short) 14); //设置字体大小字号

workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置

// 表头样式及背景色

HSSFCellStyle hdStyle = workbook.createCellStyle();

hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

hdStyle.setBottomBorderColor(HSSFColor.BLACK.index);

hdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

hdStyle.setLeftBorderColor(HSSFColor.BLACK.index);

hdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

hdStyle.setRightBorderColor(HSSFColor.BLACK.index);

hdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

hdStyle.setTopBorderColor(HSSFColor.BLACK.index);

hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中

//hdStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);//填充颜色黄色

hdStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//填充颜色灰色

hdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

//对字体设置 样式

hdStyle.setFont(workFont);

//设置表头

String[] title = new String[rowvalues.size()];

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

title[i] = (String) rowvalues.get(i);

}

//创建 一行

HSSFRow dtRow = sheet.createRow((1));

if (isFilter == true) {

//设置表头的 样式+数值

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

HSSFCell cell1 = hdRow.createCell((short) i);

HSSFRichTextString value = newHSSFRichTextString(title[i]);

cell1.setCellValue(value);

cell1.setCellStyle(hdStyle);

}

} else {

//设置表头的 数值

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

HSSFCell cell2 = dtRow.createCell((short) i);

HSSFRichTextString value2 = newHSSFRichTextString(title[i]);

cell2.setCellValue(value2);

}

}

}

/**

* 添加一行 此行要添加的数据list 和 第几行

*/

protected void addRow(List rowvalues ,int num) {

//创建第几行 s 从1开始

HSSFRow dtRow = sheet.createRow(num);

DataFormat format = workbook.createDataFormat();

//datable样式及背景色

// 设置字体

HSSFFont workFont = workbook.createFont();

workFont.setFontName("宋体"); //设置字体类型

workFont.setFontHeightInPoints((short) 11); //设置字体大小字号

workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //设置

// 表头样式及背景色

HSSFCellStyle dtStyle = workbook.createCellStyle();

dtStyle.setDataFormat(format.getFormat("text"));

dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);

dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);

dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

dtStyle.setRightBorderColor(HSSFColor.BLACK.index);

dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

dtStyle.setTopBorderColor(HSSFColor.BLACK.index);

dtStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中

dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);//填充颜色黄色

//dtStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

//dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

//日期样式

HSSFCellStyle dateStyle = workbook.createCellStyle();

dateStyle.setDataFormat(format.getFormat("yyyy-m-d"));

dateStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

dateStyle.setBottomBorderColor(HSSFColor.BLACK.index);

dateStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

dateStyle.setLeftBorderColor(HSSFColor.BLACK.index);

dateStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

dateStyle.setRightBorderColor(HSSFColor.BLACK.index);

dateStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

dateStyle.setTopBorderColor(HSSFColor.BLACK.index);

dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中

dateStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);//填充颜色黄色

for (int j = 0; j < rowvalues.size(); j++) {

String flag = "";

Object cell_data = rowvalues.get(j);

HSSFCell cell = dtRow.createCell(j);

// 正文格式

if (cell_data instanceof String) {

flag = "string";

cell.setCellValue((String)cell_data);

}

else if (cell_data instanceof Double) {

cell.setCellValue((Double) cell_data);

} 

else if (cell_data instanceof Integer) {

cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));

}

else if (cell_data instanceof Date) {

flag = "date";

cell.setCellValue((Date) cell_data);

} 

else if (cell_data instanceof Boolean) {

cell.setCellValue((Boolean) cell_data);

}

else if (cell_data instanceof Float) {

cell.setCellValue((Float) cell_data);

}

//设置此单元格的样式

if(flag==""||flag.equals("string")){

cell.setCellStyle(dtStyle);

}else if(flag.equals("date")){

cell.setCellStyle(dateStyle);

}

}

}