java导出excel代码
代码
/**
* 系统告警导出excel
* @param response
* @param filename
* @param dateType
* @param startTime
* @param endTime
* @param entId
* @param region
* @param industry
* @param energyConsume
*/
@RequestMapping(value = "/alarm/export")
public String exportData(HttpServletResponse response,
@RequestParam(required = false, defaultValue = "0") Integer userType,
@RequestParam(required = true) long entId,
@RequestParam(required = false, defaultValue = "") String content,
@RequestParam(required = false, defaultValue = "4") int disponseflag,
@RequestParam(required = false, defaultValue = "0") int alarmLevel,
@RequestParam(required = false, defaultValue = "") String alarmTime ){
response.setContentType("application/vnd.ms-excel");
String fileName = "系统告警统计" + DateTime.now().toString("yyyyMMddHHmmss") + ".xls";
try {
response.setHeader("Content-disposition", "attachment;filename=\""
+ new String(fileName.getBytes("gb2312"), "ISO8859-1") + "\"");
String queryConditaion = null;
if (!"".equals(content)) {
queryConditaion = "%" + content + "%";
}
List<BoilerAlarmRecord> listAlarm = null;
if (userType == EnumBoilerUserType.BOILER_USER_PLAT.getValue()) {
listAlarm = globalBoilerFacade.getExportAlarms(queryConditaion,disponseflag,alarmLevel,alarmTime);
} else if (userType == EnumBoilerUserType.BOILER_USER_ENT.getValue()) {
listAlarm = globalBoilerFacade.getExportVestedEntAlarms(queryConditaion, entId,disponseflag,alarmLevel,alarmTime);
} else if (userType == EnumBoilerUserType.BOILER_USER_FAC.getValue()) {
listAlarm = globalBoilerFacade.getExportFactoryEntAlarms(queryConditaion, entId,disponseflag,alarmLevel,alarmTime);
} else {
listAlarm = Lists.newArrayList();
}
int total = listAlarm.size();
OutputStream ouputStream = response.getOutputStream();
WritableWorkbook book = Workbook.createWorkbook(ouputStream);
WritableSheet sheet1 = book.createSheet("系统告警统计报表"+ DateTime.now().toString("yyyyMMddHHmmss"), 0);
//表格内容样式
WritableFont body = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat bodyStyle = new WritableCellFormat(body);
bodyStyle.setAlignment(Alignment.CENTRE);//内容水平居中
bodyStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
//表头标题样式
WritableFont titleHead= new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD);
WritableCellFormat titleHeadStyle = new WritableCellFormat(titleHead);
titleHeadStyle.setAlignment(Alignment.CENTRE);//内容水平居中
titleHeadStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
titleHeadStyle.setBackground(Colour.GRAY_25);//设置背景颜色为灰色
//筛选条件样式
WritableFont select= new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
WritableCellFormat selectStyle = new WritableCellFormat(select);
selectStyle.setAlignment(Alignment.CENTRE);//内容水平居中
selectStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
selectStyle.setBackground(Colour.YELLOW);//设置背景颜色为黄色
Label label;
//筛选条件
String selectContent = "";
if(!StringUtils.isEmpty(content)){
selectContent += "告警对象:"+content;
}
if(disponseflag != 4){
selectContent += " 状态:"+(disponseflag == BoilerCatalogConstants.ALARM_STATE_VERIFIER ?"已审核":(disponseflag == BoilerCatalogConstants.ALARM_STATE_REJECT ?"驳回":(disponseflag == BoilerCatalogConstants.ALARM_STATE_UNVERIFIER ?"未审核":"未确认")));
}
if(alarmLevel != 0){
selectContent += " 告警优先级:"+(alarmLevel == 3?"三级告警":(alarmLevel == 2?"二级告警":"一级告警"));
}
if(!StringUtils.isEmpty(alarmTime)){
selectContent += " 告警时间:"+alarmTime;
}
selectContent += " 共计:"+total+" 条记录";
sheet1.mergeCells(0, 0, 11, 0);//添加合并单元格,第一个参数是起始列,第二个参数是起始行,第三个参数是终止列,第四个参数是终止行
sheet1.setRowView(0, 800);
label = new Label(0,0,selectContent,selectStyle);
sheet1.addCell(label);
//表头标题
String[] title = {"告警时间","告警内容","告警对象","锅炉所属企业","锅炉制造企业","告警优先级","告警次数","状态","确认人","确认信息","确认时间","审核人"};
for(int i=0;i<title.length;i++){
label = new Label(i,1,title[i],titleHeadStyle);
sheet1.addCell(label);
sheet1.setRowView(1, 600);
}
//表格内容
Object[] obj = null;
for(int i = 0; i < listAlarm.size(); i++){
sheet1.setRowView(i+2, 400);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
obj = new Object[12];
obj[0] = sdf.format(listAlarm.get(i).getAlarmTime());
sheet1.setColumnView(0, 24);
String alarmContent = "";
if(listAlarm.get(i).getAlarmContent() != null){
alarmContent = listAlarm.get(i).getAlarmContent();
}
obj[1] = alarmContent;
sheet1.setColumnView(1, 26);
String objName = "";
if(listAlarm.get(i).getObjName() != null){
objName = listAlarm.get(i).getObjName();
}
obj[2] = objName;
sheet1.setColumnView(2, 30);
String vestName = "";
if(listAlarm.get(i).getVestedEntName() != null){
vestName = listAlarm.get(i).getVestedEntName();
}
obj[3] = vestName;
sheet1.setColumnView(3, 30);
String factoryName = "";
if(listAlarm.get(i).getFactoryName() != null){
factoryName = listAlarm.get(i).getFactoryName();
}
obj[4] = factoryName;
sheet1.setColumnView(4, 30);
String alarmStatus = "一级告警";
if( listAlarm.get(i).getAlarmLevel() == 2){
alarmStatus = "二级告警";
}else if( listAlarm.get(i).getAlarmLevel() == 3){
alarmStatus = "三级告警";
}
obj[5] = alarmStatus;
sheet1.setColumnView(5, 15);
obj[6] = listAlarm.get(i).getAlarmNum();
sheet1.setColumnView(6, 15);
String status = "未确认";
if(listAlarm.get(i).getDisposeFlag() == BoilerCatalogConstants.ALARM_STATE_UNVERIFIER){
status = "未审核";
}else if(listAlarm.get(i).getDisposeFlag() == BoilerCatalogConstants.ALARM_STATE_REJECT){
status = "驳回";
}else if(listAlarm.get(i).getDisposeFlag() == BoilerCatalogConstants.ALARM_STATE_VERIFIER){
status = "审核";
}
obj[7] = status;
sheet1.setColumnView(7, 15);
String disposeName = "";
if(listAlarm.get(i).getDisponseUserName() != null){
disposeName = listAlarm.get(i).getDisponseUserName();
}
obj[8] = disposeName;
sheet1.setColumnView(8, 15);
String info = "";
if(listAlarm.get(i).getDisponseInfo() != null){
info = listAlarm.get(i).getDisponseInfo();
}
obj[9] = info;
sheet1.setColumnView(9, 30);
String disTime = "";
if(listAlarm.get(i).getDisposeTime() != null){
disTime = sdf.format(listAlarm.get(i).getDisposeTime());
}
obj[10] = disTime;
sheet1.setColumnView(10, 24);
String verifierName = "";
if(listAlarm.get(i).getVerifierUserName() != null){
verifierName = listAlarm.get(i).getVerifierUserName();
}
sheet1.setColumnView(11, 15);
obj[11] = verifierName;
for (int j = 0; j < obj.length; j++) {
sheet1.addCell(new Label(j,i+2,obj[j].toString(),bodyStyle));
}
}
book.write();
book.close();
}
catch (Exception e) {
e.printStackTrace();
}
return null;
}