//创建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);
}
}
}