导出功能请求controller的方法:
/**
参数NewOnlineDevice :存放前台按条件导出需要的条件
/
@RequestMapping("/exlNewOnlineDeviceListOut")
public void exlNewOnlineDeviceListOut(NewOnlineDevice newOnlineDevice,HttpServletResponse response,HttpServletRequest request,HttpSession session){
String fileName = "设备在线率统计表.xls"; //表名称
//设置字符集乱码的方法,下面有
fileName = ExportExcel.processFileName(request, fileName);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+fileName );
response.setCharacterEncoding("utf-8");
//===========获取需要导出的数据列表===========
List<NewOnlineDevice> list= new ArrayList<NewOnlineDevice>();
list = customOnlineService.getNewOnlineDeviceListExp(newOnlineDevice);
//==========导出操作============
//表头数组,也即是excel需要显示的列
String[] headers = {"运维单位","设备用途","在线数","挂起数","报修数","总数","在线率","创建时间"};
ExportExcel<NewOnlineDevice> exPortExcel = new ExportExcel<NewOnlineDevice>();
try {
OutputStream out = new BufferedOutputStream(response.getOutputStream());
//给单元格赋值的方法,下面有
exPortExcel.exportNewOnlineDeviceExcel(headers, list, out);
} catch (Exception e) {
e.printStackTrace();
log.error("###YwAssessController.exlYwAssessDetailOut###" + e.getMessage());
}
}
/**
* 设置字符集乱码的方法
* @param request
* @param fileNames
* @return
*/
public static String processFileName(HttpServletRequest request, String fileNames) {
String codedfilename = null;
try {
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE") || null != agent
&& -1 != agent.indexOf("Trident")) {// ie
String name = java.net.URLEncoder.encode(fileNames, "UTF8");
codedfilename = name;
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等
codedfilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
}
} catch (Exception e) {
e.printStackTrace();
}
return codedfilename;
}
//给单元格赋值
public void exportNewOnlineDeviceExcel(String[] headers,
List dList, OutputStream out){
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("sheet1");
sheet.setDefaultColumnWidth((short) 20);
DecimalFormat df = new DecimalFormat("#.00");
//将数据写入Execl
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
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 = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 生成另一个样式
HSSFCellStyle style1 = workbook.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font1 = workbook.createFont();
font1.setFontName("宋体");
font1.setFontHeightInPoints((short) 10);
// 把字体应用到当前的样式
style1.setFont(font1);
HSSFRow firstRow = sheet.createRow(0);
//设置标题
for(int i = 0;i<headers.length;i++){
String jString = headers[i];
HSSFCell cell = firstRow.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(jString);
}
for (int i = 1; i <= dList.size(); i++)
{
HSSFRow row = sheet.createRow((int)i);//创建一行
NewOnlineDevice newOnlineDevice = (NewOnlineDevice) dList.get(i-1);
HSSFCell unit_name = row.createCell((int)0);//创建列
if(newOnlineDevice.getUnitName()!=null){
unit_name.setCellType(HSSFCell.CELL_TYPE_STRING);
unit_name.setCellValue(newOnlineDevice.getUnitName());
unit_name.setCellStyle(style1);
}
HSSFCell devicePorpuse = row.createCell((int)1);
//String device_status_flag= null;
if(newOnlineDevice.getDevicePorpuse()!=null){
devicePorpuse.setCellType(HSSFCell.CELL_TYPE_STRING);
devicePorpuse.setCellValue(newOnlineDevice.getDevicePorpuse());
devicePorpuse.setCellStyle(style1);
}
HSSFCell online = row.createCell((int)2);
if(newOnlineDevice.getOnline()!=null){
online.setCellType(HSSFCell.CELL_TYPE_STRING);
online.setCellValue(newOnlineDevice.getOnline());
online.setCellStyle(style1);
}
HSSFCell pending = row.createCell((int)3);
if(newOnlineDevice.getPending()!=null){
pending.setCellType(HSSFCell.CELL_TYPE_STRING);
pending.setCellValue(newOnlineDevice.getPending());
pending.setCellStyle(style1);
}
HSSFCell repair = row.createCell((int)4);
if(newOnlineDevice.getRepair()!=null){
repair.setCellType(HSSFCell.CELL_TYPE_STRING);
repair.setCellValue(newOnlineDevice.getRepair());
repair.setCellStyle(style1);
}
HSSFCell total = row.createCell((int)5);
if(newOnlineDevice.getTotal()!=null){
total.setCellType(HSSFCell.CELL_TYPE_STRING);
total.setCellValue(newOnlineDevice.getTotal());
total.setCellStyle(style1);
}
HSSFCell rate = row.createCell((int)6);
rate.setCellType(HSSFCell.CELL_TYPE_STRING);
if(newOnlineDevice.getOnline()==0){rate.setCellValue("0.00%");}
else{
double on=newOnlineDevice.getOnline();
double to=newOnlineDevice.getTotal();
rate.setCellValue(((on/to)*100)+"%");
}
rate.setCellStyle(style1);
HSSFCell creat_time = row.createCell((int)7);
if(newOnlineDevice.getCreatTime()!=null){
creat_time.setCellType(HSSFCell.CELL_TYPE_STRING);
creat_time.setCellValue(newOnlineDevice.getCreatTime().substring(0, 10));
creat_time.setCellStyle(style1);
}
}
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}