public void exportBusServiceCard(List<Map> l,Map<String, Object> params){
String []tableHeader={"序号","受理号","照片","姓名","从业类别","培训日期","初次领证日期","有效期","考试合格证号"};
short cellNumber=(short)tableHeader.length; //表的列数
HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个excel
HSSFCell cell = null; //Excel的列
HSSFRow row = null; //Excel的行
HSSFCellStyle style = workbook.createCellStyle(); //设置表头的类型
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
HSSFCellStyle style1 = workbook.createCellStyle(); //设置数据类型
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle style2 = workbook.createCellStyle(); //设置数据类型
style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
HSSFCellStyle style3 = workbook.createCellStyle(); //设置数据类型
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
HSSFFont font1 = workbook.createFont();
HSSFCellStyle stylegreen = workbook.createCellStyle(); //设置表头的类型
stylegreen.setAlignment(HSSFCellStyle.ALIGN_CENTER);
stylegreen.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
stylegreen.setWrapText(true);//设置字体
HSSFFont green = workbook.createFont();
HSSFCellStyle styleblue = workbook.createCellStyle(); //设置表头的类型
styleblue.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleblue.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleblue.setWrapText(true);//设置字体
HSSFFont blue = workbook.createFont();
HSSFCellStyle stylered = workbook.createCellStyle(); //设置表头的类型
stylered.setAlignment(HSSFCellStyle.ALIGN_CENTER);
stylered.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
stylered.setWrapText(true);//设置字体
HSSFFont red = workbook.createFont();
green.setColor(HSSFColor.GREEN.index);
blue.setColor(HSSFColor.BLUE.index);
red.setColor(HSSFColor.RED.index);
stylegreen.setFont(green);
styleblue.setFont(blue);
stylered.setFont(red);
HSSFSheet sheet = workbook.createSheet("sheet1"); //创建一个sheet
HSSFHeader header = sheet.getHeader();
String zpPath = "f:\Bus_Photo\";
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
String time = df.format(new Date());
try {
if(l.size() < 1 ) {
/*header.setCenter("没有查到人员");*/
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));//合并单元格
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue("没有查到结果");
font1.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
/*font1.setFontHeight((short)400); //设置单元字体高度*/
font1.setFontHeightInPoints((short)18);
style3.setFont(font1);
cell.setCellStyle(style3);
}else{
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));//合并单元格
row = sheet.createRow(0);
cell = row.createCell(0);
cell.setCellValue(time.substring(0, 4)+"年"+time.substring(5, 7)+"月"+time.substring(8)+"日公交服务卡");
font1.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
/*font1.setFontHeight((short)400); //设置单元字体高度*/
font1.setFontHeightInPoints((short)18);
style3.setFont(font1);
cell.setCellStyle(style3);
row = sheet.createRow(1);
row.setHeight((short)400);
for(int k = 0;k < cellNumber;k++) {
cell = row.createCell(k);//创建第0行第k列
cell.setCellValue(tableHeader[k]);//设置第0行第k列的值
font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
font.setFontHeight((short)250); //设置单元字体高度
style1.setFont(font);//设置字体风格
cell.setCellStyle(style1);
}
for(int i = 0 ;i < l.size() ;i++) {
Map t = l.get(i);
//设置各列的宽度
sheet.setColumnWidth(0,1500);
sheet.setColumnWidth(1,3000);
sheet.setColumnWidth(2,3660);
sheet.setColumnWidth(3,3000);
sheet.setColumnWidth(4,3000);
sheet.setColumnWidth(5,3000);
sheet.setColumnWidth(6,4000);
sheet.setColumnWidth(7,3000);
sheet.setColumnWidth(8,4000);
//数据行
row = sheet.createRow((short) (i + 2));
row.setHeight((short)2000);
//第一列 序号
cell = row.createCell(0);
cell.setCellValue(i+1);
cell.setCellStyle(style);
//第二列受理号
if(t.get("SLH") != null){
cell = row.createCell(1);
cell.setCellValue(t.get("SLH").toString());
cell.setCellStyle(style);
}
//第三列照片
if(t.get("IDENTIFICATION") != null) {
String sfzh = t.get("IDENTIFICATION").toString();
String sfzh18 = null;
if (sfzh.length() == 15) {
sfzh18 = IDCardUtil.trans15to18(sfzh);
} else if (sfzh.length() == 18) {
sfzh18 = sfzh;
}
String year = null;
String month = null;
if (sfzh18 != null) {
year = IDCardUtil.getYearPart(sfzh18);
month = IDCardUtil.getMonthPart(sfzh18);
}
String zp = zpPath + year + "\" + month + "\" + sfzh18 + ".jpg";
File picture = new File(zp);
if(!picture.exists()){
cell = row.createCell(2);
cell.setCellValue(t.get("IDENTIFICATION").toString());
cell.setCellStyle(style);
}else{
BufferedImage sourceImg = ImageIO.read(new FileInputStream(picture));
ByteArrayOutputStream bout = new ByteArrayOutputStream();
ImageIO.write(sourceImg,"PNG",bout);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
(short) 1, (i+2), (short) 2, (i+3));
patriarch.createPicture(anchor, workbook.addPicture(bout
.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}
}
//第四列 姓名
if(t.get("FULLNAME") != null){
cell = row.createCell(3);
cell.setCellValue(t.get("FULLNAME").toString());
cell.setCellStyle(style);
}
//第五列从业类别
if(t.get("GENRENAME") != null){
cell = row.createCell(4);
cell.setCellValue(t.get("GENRENAME").toString());
cell.setCellStyle(style);
}
//第六列培训日期
if(t.get("EXAMDATE") != null) {
cell = row.createCell(5);
cell.setCellValue(t.get("EXAMDATE").toString());
cell.setCellStyle(style);
}
//第七列初领证日期
if(t.get("INITIALDATE") != null) {
cell = row.createCell(6);
cell.setCellValue(t.get("INITIALDATE").toString());
cell.setCellStyle(style);
}
//第八列 有效期
if(t.get("VALIDDATE") != null) {
cell = row.createCell(7);
cell.setCellValue(t.get("VALIDDATE").toString());
cell.setCellStyle(style);
}
//第9列合格证号
if(t.get("SERIALNUMBER") != null) {
cell = row.createCell(8);
cell.setCellValue(t.get("SERIALNUMBER").toString());
cell.setCellStyle(style);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
HttpServletResponse response = null;//创建一个HttpServletResponse对象
OutputStream os = null;//创建一个输出流对象
try {
response = ServletActionContext.getResponse();//初始化HttpServletResponse对象
os = response.getOutputStream();//
String headerStr ="公交服务卡人员 ";
headerStr =new String(headerStr.getBytes("gb2312"), "ISO8859-1");
response.setHeader("Content-disposition","attachment; filename="+ headerStr+".xls");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");//设置类型
response.setHeader("Pragma","No-cache");//设置头
response.setHeader("Cache-Control","no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期头
workbook.write(os);
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}