public void exprotExcel(String type, PageData pd, HttpServletResponse response) throws Exception {
//创建excel表格
HSSFWorkbook wb = new HSSFWorkbook();//创建webbook对象
HSSFSheet sheet = wb.createSheet(“sheet1”); //创建sheet
HSSFRow headerRow = sheet.createRow((short) 0);//在sheet第1行中添加表头
HSSFCellStyle style = getExcelStyle(wb);
List listProduct = null; //品种
List listInductry = null;//产品
String title = null;//标题
String[] titleList = null;//表头数组
String[] titleColumnList = null;//字段数据
int cellNumber = 0;//表头长度
//根据类型判断获取数据
if (type.equals(“1”)) {
// 获取所有的品种列表
listProduct = (List) dao.findForList(“ProductMapper.exportAll”, pd);//品种数据
//给listProduct中leixing赋值为2,表示是品种数据
for(PageData temp : listProduct){
temp.put(“LEIXING”,”2”);
}
// 获取所有的品类列表
listInductry =(List)dao.findForList(“IndustryMapper.exportAll”, pd);//品类数据
//给listInductry中leixing赋值为1,表示是品类数据
for(PageData temp : listInductry){
temp.put(“LEIXING”,”1”);
}
listProduct.addAll(listInductry);//合并两个表格
titleList = new String[]{“编码”, “名称”, “所属编号”, “类型”, “规格”, “单位”, “级别”, “备注”};
titleColumnList = new String[]{ “BIANMA”, “MINGCHENG”, “SSHBH”, “LEIXING”, “GUIGE”, “DANWEI”, “JIBIE”,”BEIZHU”};
cellNumber = titleList.length;
title = “品种列表”;//标题
}
//遍历表头
HSSFCell headerCell = null;//创建表头单元格
for (int i = 0; i < cellNumber; i++) {
headerCell = headerRow.createCell((short) i); //单元格titleList[i].
headerCell.setCellValue(titleList[i]);//给单元格赋值
headerCell.setCellStyle(style);//单元格样式
sheet.setColumnWidth((short) i, (short) 4766);//设置单元格宽度
}
//循环遍历数据,存入excel中
for (int i = 0; i < listProduct.size();i++) {
PageData single = listProduct.get(i);
HSSFRow dataRow = sheet.createRow((short) i + 1);//创建行
HSSFCell contentCell = null;//创建单元格
for (int j = 0; j < cellNumber ; j++) {
String TABLE_COLUMN = titleColumnList[j];//定义表字段
String VALUE = "";//定义表字段的值
//用containsKey()判断map中是否存在key,并且判断key的值是否为空
if(single.containsKey(TABLE_COLUMN) && single.getString(TABLE_COLUMN) != null &&!single.getString(TABLE_COLUMN).equals("")){
VALUE = single.getString(TABLE_COLUMN);
}
contentCell = dataRow.createCell((short)j);//单元格
contentCell.setCellValue(VALUE);//单元格赋值
}
contentCell.setCellStyle(style);//行添加样式
}
//浏览器下载
OutputStream os=null;
try {
os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename=" + new String((title+".xls").getBytes("UTF-8"), "ISO8859-1"));
response.setContentType("text/html;charset=UTF-8");
response.setContentType("application/x-excel");
wb.write(os);
os.close();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static HSSFCellStyle getExcelStyle(HSSFWorkbook wb) {
HSSFCellStyle style = wb.createCellStyle();
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 10);
font.setFontName(“宋体”);
style.setFont(font);
style.setWrapText(true);
return style;
}