// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
sheet.setColumnWidth((short) 0,(short)7*256);
sheet.setColumnWidth((short) 1,(short)47*256);
sheet.setColumnWidth((short) 2,(short)12*256);
sheet.setColumnWidth((short) 3,(short)12.43*256);
sheet.setColumnWidth((short) 4,(short)22*256);
sheet.addMergedRegion(new Region(0,(short)0,0,(short)4));
sheet.addMergedRegion(new Region(1,(short)0,1,(short)4));
sheet.addMergedRegion(new Region(2,(short)0,2,(short)4));
//打印设置
// HSSFPrintSetup ps = sheet.getPrintSetup();
// ps.setLandscape(false); //打印方向,true:横向,false:纵向
// ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张
sheet.setMargin(HSSFSheet.BottomMargin, 0.4); //页边距(下)
sheet.setMargin(HSSFSheet.LeftMargin, 0.4); //页边距(左)
sheet.setMargin(HSSFSheet.RightMargin, 0.4); //页边距(右)
sheet.setMargin(HSSFSheet.TopMargin, 0.4); //页边距(上)
sheet.getPrintSetup().setFooterMargin(0.32);
sheet.getPrintSetup().setHeaderMargin(0.32);
//单元格样式1
HSSFCellStyle style1 = wb.createCellStyle();
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
//style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont font=wb.createFont();
font.setFontName("SimHei");
font.setFontHeightInPoints((short)20); //字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体增粗
style1.setFont(font);
//单元格样式2
HSSFCellStyle style2 = wb.createCellStyle();
style2.setWrapText(true);//设置自动换行
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)18);
style2.setFont(font);
//单元格样式3
HSSFCellStyle style3 = wb.createCellStyle();
style3.setWrapText(true);//设置自动换行
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style3.setFont(font);
//单元格样式4
HSSFCellStyle style4 = wb.createCellStyle();
style4.setWrapText(true);//设置自动换行
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style4.setWrapText(true);
style4.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style4.setBorderTop(HSSFCellStyle.BORDER_THIN);
style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)16);
style4.setFont(font);
//单元格样式5
HSSFCellStyle style5 = wb.createCellStyle();
style5.setWrapText(true);//设置自动换行
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style5.setAlignment(HSSFCellStyle.ALIGN_LEFT);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)18);
style5.setFont(font);
//设置单元格样式6为第二行和第三行特别设置
HSSFCellStyle style6 = wb.createCellStyle();
style6.setWrapText(true);//设置自动换行
style6.setAlignment(HSSFCellStyle.ALIGN_LEFT);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)16);
style6.setFont(font);
HSSFRow row = sheet.createRow((int) 0);
row.setHeight((short)1200);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(result.get("subject").toString());
cell.setCellStyle(style1);
row = sheet.createRow((int) 1);
row.setHeight((short)1200);
cell = row.createCell((short) 0);
cell.setCellValue("定于"+result.get("hysj")+",在"+result.get("hydd")+"召开"+result.get("type")+"。");
cell.setCellStyle(style6);
row = sheet.createRow((int) 2);
row.setHeight((short)1200);
cell = row.createCell((short) 0);
cell.setCellValue("出席人员:"+result.get("cxry"));
cell.setCellStyle(style6);
row = sheet.createRow((int) 3);
cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell.setCellStyle(style3);
cell = row.createCell((short) 1);
cell.setCellValue("议题");
cell.setCellStyle(style3);
cell = row.createCell((short) 2);
cell.setCellValue("汇报部门");
cell.setCellStyle(style3);
cell = row.createCell((short) 3);
cell.setCellValue("汇报人");
cell.setCellStyle(style3);
cell = row.createCell((short) 4);
cell.setCellValue("相关部门");
cell.setCellStyle(style3);
int num=4;
for(Map<String,Object> _map:list){
row = sheet.createRow((int) num);
cell = row.createCell((short) 0);
cell.setCellValue(_map.get("row").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 1);
cell.setCellValue(_map.get("subject").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 2);
cell.setCellValue(_map.get("xzdw").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 3);
cell.setCellValue(_map.get("hbr").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 4);
cell.setCellValue(_map.get("xgbm").toString());
cell.setCellStyle(style4);
num++;
}
CellRangeAddress region1=new CellRangeAddress((short)num, (short)num,(short)3, (short)4);
sheet.addMergedRegion(region1);
row = sheet.createRow((int) num);
row.setHeight((short)1000);
cell = row.createCell(3);
cell.setCellValue(result.get("adddept").toString());
cell.setCellStyle(style2);
num++;
CellRangeAddress region2=new CellRangeAddress((short)num, (short)num,(short)3, (short)4);
sheet.addMergedRegion(region2);
row = sheet.createRow((int) num);
row.setHeight((short)1000);
cell = row.createCell((short) 3);
cell.setCellValue(result.get("addtime").toString());
cell.setCellStyle(style2);
OutputStream os = null;
BeanCtx.getResponse().reset(); // 清空输出流
os = BeanCtx.getResponse().getOutputStream(); // 取得输出流
BeanCtx.getResponse().setHeader("Content-disposition", "attachment; filename="
+ new String((result.get("subject")+".xls").getBytes("gb2312"), "ISO-8859-1")); // 设定输出文件头
BeanCtx.getResponse().setContentType("application/msexcel"); // 定义输出类型
os.flush();
wb.write(os);
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
sheet.setColumnWidth((short) 0,(short)7*256);
sheet.setColumnWidth((short) 1,(short)47*256);
sheet.setColumnWidth((short) 2,(short)12*256);
sheet.setColumnWidth((short) 3,(short)12.43*256);
sheet.setColumnWidth((short) 4,(short)22*256);
sheet.addMergedRegion(new Region(0,(short)0,0,(short)4));
sheet.addMergedRegion(new Region(1,(short)0,1,(short)4));
sheet.addMergedRegion(new Region(2,(short)0,2,(short)4));
//打印设置
// HSSFPrintSetup ps = sheet.getPrintSetup();
// ps.setLandscape(false); //打印方向,true:横向,false:纵向
// ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张
sheet.setMargin(HSSFSheet.BottomMargin, 0.4); //页边距(下)
sheet.setMargin(HSSFSheet.LeftMargin, 0.4); //页边距(左)
sheet.setMargin(HSSFSheet.RightMargin, 0.4); //页边距(右)
sheet.setMargin(HSSFSheet.TopMargin, 0.4); //页边距(上)
sheet.getPrintSetup().setFooterMargin(0.32);
sheet.getPrintSetup().setHeaderMargin(0.32);
//单元格样式1
HSSFCellStyle style1 = wb.createCellStyle();
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
//style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont font=wb.createFont();
font.setFontName("SimHei");
font.setFontHeightInPoints((short)20); //字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体增粗
style1.setFont(font);
//单元格样式2
HSSFCellStyle style2 = wb.createCellStyle();
style2.setWrapText(true);//设置自动换行
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)18);
style2.setFont(font);
//单元格样式3
HSSFCellStyle style3 = wb.createCellStyle();
style3.setWrapText(true);//设置自动换行
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style3.setFont(font);
//单元格样式4
HSSFCellStyle style4 = wb.createCellStyle();
style4.setWrapText(true);//设置自动换行
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style4.setWrapText(true);
style4.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style4.setBorderTop(HSSFCellStyle.BORDER_THIN);
style4.setBorderRight(HSSFCellStyle.BORDER_THIN);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)16);
style4.setFont(font);
//单元格样式5
HSSFCellStyle style5 = wb.createCellStyle();
style5.setWrapText(true);//设置自动换行
style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style5.setAlignment(HSSFCellStyle.ALIGN_LEFT);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)18);
style5.setFont(font);
//设置单元格样式6为第二行和第三行特别设置
HSSFCellStyle style6 = wb.createCellStyle();
style6.setWrapText(true);//设置自动换行
style6.setAlignment(HSSFCellStyle.ALIGN_LEFT);
font=wb.createFont();
font.setFontName("FangSong");
font.setFontHeightInPoints((short)16);
style6.setFont(font);
HSSFRow row = sheet.createRow((int) 0);
row.setHeight((short)1200);
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue(result.get("subject").toString());
cell.setCellStyle(style1);
row = sheet.createRow((int) 1);
row.setHeight((short)1200);
cell = row.createCell((short) 0);
cell.setCellValue("定于"+result.get("hysj")+",在"+result.get("hydd")+"召开"+result.get("type")+"。");
cell.setCellStyle(style6);
row = sheet.createRow((int) 2);
row.setHeight((short)1200);
cell = row.createCell((short) 0);
cell.setCellValue("出席人员:"+result.get("cxry"));
cell.setCellStyle(style6);
row = sheet.createRow((int) 3);
cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell.setCellStyle(style3);
cell = row.createCell((short) 1);
cell.setCellValue("议题");
cell.setCellStyle(style3);
cell = row.createCell((short) 2);
cell.setCellValue("汇报部门");
cell.setCellStyle(style3);
cell = row.createCell((short) 3);
cell.setCellValue("汇报人");
cell.setCellStyle(style3);
cell = row.createCell((short) 4);
cell.setCellValue("相关部门");
cell.setCellStyle(style3);
int num=4;
for(Map<String,Object> _map:list){
row = sheet.createRow((int) num);
cell = row.createCell((short) 0);
cell.setCellValue(_map.get("row").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 1);
cell.setCellValue(_map.get("subject").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 2);
cell.setCellValue(_map.get("xzdw").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 3);
cell.setCellValue(_map.get("hbr").toString());
cell.setCellStyle(style4);
cell = row.createCell((short) 4);
cell.setCellValue(_map.get("xgbm").toString());
cell.setCellStyle(style4);
num++;
}
CellRangeAddress region1=new CellRangeAddress((short)num, (short)num,(short)3, (short)4);
sheet.addMergedRegion(region1);
row = sheet.createRow((int) num);
row.setHeight((short)1000);
cell = row.createCell(3);
cell.setCellValue(result.get("adddept").toString());
cell.setCellStyle(style2);
num++;
CellRangeAddress region2=new CellRangeAddress((short)num, (short)num,(short)3, (short)4);
sheet.addMergedRegion(region2);
row = sheet.createRow((int) num);
row.setHeight((short)1000);
cell = row.createCell((short) 3);
cell.setCellValue(result.get("addtime").toString());
cell.setCellStyle(style2);
OutputStream os = null;
BeanCtx.getResponse().reset(); // 清空输出流
os = BeanCtx.getResponse().getOutputStream(); // 取得输出流
BeanCtx.getResponse().setHeader("Content-disposition", "attachment; filename="
+ new String((result.get("subject")+".xls").getBytes("gb2312"), "ISO-8859-1")); // 设定输出文件头
BeanCtx.getResponse().setContentType("application/msexcel"); // 定义输出类型
os.flush();
wb.write(os);