/**
*
* @param headStr
* 表格内大title
* @param titleVec
* 字段名称
* @param titleWidthAry
* 没列宽度
* @param bodyAry
* 内容
* @param os
* 输出流
* @param sheetName
* EXCEL页标名称(sheet1)
* @throws Exception
*/
public static void excelOS(String headStr, Vector titleVec,
int[] titleWidthAry, String[][] bodyAry, OutputStream os,
String sheetName) throws Exception {
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
wb.setSheetName(0, sheetName);
sheet.getPrintSetup().setLandscape(true);// true:横向 false:纵向
HSSFFont font = wb.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints((short) 10);
HSSFFont titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 10);
titleFont.setFontName("楷体_GB2312");
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// titleFont.setColor(HSSFColor.BLUE.index);
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setBorderLeft((short) 1);
titleStyle.setBorderRight((short) 1);
titleStyle.setBorderTop((short) 1);
titleStyle.setBorderBottom((short) 1);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setWrapText(true);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setBorderLeft((short) 1);
style.setBorderRight((short) 1);
style.setBorderTop((short) 1);
style.setBorderBottom((short) 1);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
HSSFFont headFont = wb.createFont();
headFont.setFontHeightInPoints((short) 18);
headFont.setFontName("楷体_GB2312");
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
setColumnWidth(sheet, titleWidthAry);
// 第一行合并
sheet.addMergedRegion(new Region(0, (short) 0, 0,
(short) (titleWidthAry.length - 1)));// 合并单元格
HSSFRow headRowOne = sheet.createRow((short) 0);
HSSFCell headCellOne = headRowOne.createCell((short) 0);
// headCellOne.setEncoding(HSSFCell.ENCODING_UTF_16);
headCellOne.setCellStyle(headStyle);
headCellOne.setCellValue(headStr);
// 数据项描述
HSSFRow rowTitle = sheet.createRow(1);
for (int i = 0; i < titleVec.size(); i++) {
HSSFCell titleCell = rowTitle.createCell((short) i);
// titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue((String) titleVec.get(i));
titleCell.setCellStyle(titleStyle);
}
int listFlag = 2;
if (bodyAry != null) {
for (int i = 0; i < bodyAry.length; i++) {
HSSFRow row = sheet.createRow(listFlag);
int dataFlag = 0;
HSSFCell Contentcell = null;
for (int j = 0; j < bodyAry[i].length; j++) {
Contentcell = row.createCell(dataFlag);
Contentcell.setCellStyle(style);
// Contentcell.setEncoding(HSSFCell.ENCODING_UTF_16);
Contentcell.setCellValue(bodyAry[i][j]);
dataFlag++;
}
listFlag++;
}
}
os.flush();
wb.write(os);
} catch (Exception ex) {
ex.printStackTrace();
}
}
rp.setCharacterEncoding("UTF-8");
rp.setContentType("application/msexcel");
rp.setHeader("Content-Disposition", "attachment; filename=exportdev.xls");
String where = " ";
String items =r.getParameter("items");
String data ="["+java.net.URLDecoder.decode(r.getParameter("data"),"UTF-8") +"]";
if (!StringUtils.isEmpty(items)){
int len = Integer.parseInt(items);
String filed = "";
String sign = "";
String value = "";
String andor = "";
String text = "";
String date = "";
String type = "";
String filedwhere = "";
String signwhere = "";
// String valuewhere = "";
String andorwhere = "";
net.sf.json.JSONArray jdata = net.sf.json.JSONArray.fromObject(Function.PageToDb(data));
for (int i = 0; i < len; i++) {
Object obj = jdata.get(0);
net.sf.json.JSONObject jo = net.sf.json.JSONObject.fromObject(obj);
filed =jo.get("fieldsboxname"+(i+1)).toString();
sign = jo.get("signboxname"+(i+1)).toString();
text = jo.get("valuetextname"+(i+1)).toString();
type = jo.get("alarmtypename"+(i+1)).toString();
date = jo.get("alarmdatename"+(i+1)).toString();
andor = jo.get("andorboxname"+(i+1)).toString();
if ("1".equals(filed)) {
value = text;
filedwhere = "devname";
}else if ("2".equals(filed)) {
value = text;
filedwhere = "channelname";
}else if ("3".equals(filed)) {
value = type;
filedwhere = "operate_status";
}else if ("4".equals(filed)) {
value = type;
filedwhere = "channeltype";
}else if ("5".equals(filed)) {
value = type;
filedwhere = "alarmtype";
}else if ("6".equals(filed)) {
value = type;
filedwhere = "alarmlevelid";
}else if ("7".equals(filed)) {
value = date;
filedwhere = "alarmtime";
}else if ("8".equals(filed)) {
value = date;
filedwhere = "operatetime";
}else if ("9".equals(filed)) {
value = text;
filedwhere = "alarmuser";
}
if ("1".equals(sign)) {
signwhere = "=" + " '"+value+"'";
}else if ("2".equals(sign)) {
signwhere = "!="+ " '"+value+"'";
}else if ("3".equals(sign)) {
signwhere = ">"+ " '"+value+"'";
}else if ("4".equals(sign)) {
signwhere = "<"+ " '"+value+"'";
}else if ("5".equals(sign)) {
signwhere = "<="+ " '"+value+"'";
}else if ("6".equals(sign)) {
signwhere = ">="+ " '"+value +"'";
}else if ("7".equals(sign)) {
signwhere = "like"+ " '"+"%"+value+"%'";
}else if ("8".equals(sign)) {
signwhere = "like"+ " '"+value+"%'";
}else if ("9".equals(sign)) {
signwhere = "like"+ " '"+"%"+value+"'";
}
if ("1".equals(andor)) {
andorwhere = "and";
}else if ("2".equals(andor)) {
andorwhere = "or";
}
if (i+1 == len) {
where += " " + filedwhere + " " + signwhere + " ";
}else {
where += " " + filedwhere + " " + signwhere + " " + andorwhere + " ";
}
}
}else {
where = " 1=1 ";
}
List<AlarmLog> list = oaddl.loglistexprot(where);
String[][] contentarray;
int[] titleWidth;
Vector<String> titleVec = new Vector<String>();
titleVec.add("报警编号");//1
titleVec.add("报警时间");//2
titleVec.add("报警设备");//3
titleVec.add("报警通道");//4
titleVec.add("通道类型");//5
titleVec.add("报警类型");//6
titleVec.add("报警等级");//7
titleVec.add("报警内容");//8
titleVec.add("接警状态");//9
titleVec.add("接警时间");//10
titleVec.add("接警员");//11
titleVec.add("处理结果");//12
int[] titleWidthAry ={ 20, 20, 20, 20, 20, 20, 20,20, 20, 20, 20, 20 };
titleWidth=titleWidthAry;
contentarray = new String[list.size()][titleVec.size()];
for(int i = 0 ; i < list.size();i++){
contentarray[i][0] = list.get(i).getId();
contentarray[i][1] = list.get(i).getAlarmtime();
contentarray[i][2] = list.get(i).getDevname();
contentarray[i][3] = list.get(i).getChannelname();
contentarray[i][4] = Function.getChnlType(list.get(i).getChanneltype());
contentarray[i][5] = Function.getAlarmType(list.get(i).getAlarmtype());
if("1".equals(list.get(i).getAlarmlevelid())){
contentarray[i][6] = "紧急";
}else if("2".equals(list.get(i).getAlarmlevelid())){
contentarray[i][6] = "高级";
}else if("3".equals(list.get(i).getAlarmlevelid())){
contentarray[i][6] ="中级";
}else if("4".equals(list.get(i).getAlarmlevelid())){
contentarray[i][6] ="低级";
}
contentarray[i][7] = list.get(i).getAlarmdesc();
if( "0".equals(list.get(i).getOperate_status())){
contentarray[i][8]= "否";
}else if( "1".equals(list.get(i).getOperate_status())){
contentarray[i][8] = "是";
}
contentarray[i][9] = list.get(i).getOperatetime();
contentarray[i][10] = list.get(i).getAlarmuser();
contentarray[i][11] = list.get(i).getResultdesc();
}
String fileName = URLEncoder.encode("导出报警日志.xls", "UTF-8");
rp.setHeader("Content-Disposition", "attachment; filename=" + fileName);
Function.excelOS("报警日志导出" ,titleVec,titleWidth, contentarray, rp.getOutputStream(), "sheet1");