package com.copote.businessManage.afbjDataAnalysis.expExcelServlet;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi2.hssf.usermodel.HSSFCell;
import org.apache.poi2.hssf.usermodel.HSSFCellStyle;
import org.apache.poi2.hssf.usermodel.HSSFFont;
import org.apache.poi2.hssf.usermodel.HSSFRichTextString;
import org.apache.poi2.hssf.usermodel.HSSFRow;
import org.apache.poi2.hssf.usermodel.HSSFSheet;
import org.apache.poi2.hssf.usermodel.HSSFWorkbook;
import org.apache.poi2.hssf.util.HSSFColor;
import org.apache.poi2.hssf.util.Region;
import com.copote.businessManage.afbjDataAnalysis.bean.AfbjDynamicTableBean;
import com.copote.businessManage.afbjDataAnalysis.dao.AfbjDataAnalysis;
/**
* 统计分析-报警记录 Excel导出
* */
public class ExpExcel_Bjjl extends HttpServlet {
private HashMap<String,String> condition = new HashMap<String,String>();
public ExpExcel_Bjjl() {//构造器
super();
}
public void destroy() {//销毁
super.destroy();
}
public void init() throws ServletException {//初始化
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {//doPost
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//设置返回类型为Excel
response.setContentType("application/vnd.ms-excel; charset=UTF-8");
//设置返回文件名为 myDocument.xls
response.setHeader("Content-Disposition", "filename=myDocument"+System.currentTimeMillis()+".xls");
System.out.println("filename=mydocument"+System.currentTimeMillis()+".xls");
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
//创建工作簿
HSSFWorkbook book = ExportExcelStatistics(response,request);
try{
ServletOutputStream out = response.getOutputStream();
book.write(out);
out.flush();
out.close();
response.flushBuffer();
}catch(Exception e){
e.printStackTrace();
}
}
private HSSFWorkbook ExportExcelStatistics(HttpServletResponse response,
HttpServletRequest request) {
String dwdh = request.getParameter("dwdh").toString();
String dwmc = request.getParameter("dwmc").toString();// 单位名称
String starttime = request.getParameter("starttime").toString();
String endtime = request.getParameter("endtime").toString();
condition.put("dwdh", dwdh);
condition.put("starttime", starttime);
condition.put("endtime", endtime);
//创建工作簿
HSSFWorkbook book = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = book.createSheet("sheet1");
//设置表格列的宽度
sheet.setColumnWidth((short) 0, (short) 2058);
sheet.setColumnWidth((short) 1, (short) 4868);
sheet.setColumnWidth((short) 2, (short) 2868);
sheet.setColumnWidth((short) 3, (short) 4268);
sheet.setColumnWidth((short) 4, (short) 4268);
sheet.setColumnWidth((short) 5, (short) 4868);
sheet.setColumnWidth((short) 6, (short) 4868);
sheet.setColumnWidth((short) 7, (short) 4268);
sheet.setColumnWidth((short) 8, (short) 6268);
sheet.setColumnWidth((short) 9, (short) 4268);
sheet.setColumnWidth((short) 10, (short) 4268);
//初始化title样式
HSSFCellStyle titleStyle = book.createCellStyle();
HSSFFont font = book.createFont();
font.setFontHeightInPoints((short) 20);
titleStyle.setFont(font);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直对齐方式
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平对齐方式
//初始化head样式
HSSFCellStyle headStyle_center = book.createCellStyle();
font = book.createFont();
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
headStyle_center.setFont(font);
headStyle_center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle headStyle_left = book.createCellStyle();
font = book.createFont();
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
headStyle_left.setFont(font);
headStyle_left.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFCellStyle headStyle_right = book.createCellStyle();
font = book.createFont();
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗
headStyle_right.setFont(font);
headStyle_right.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//初始化body样式
HSSFCellStyle bodyStyle_center = book.createCellStyle();
font = book.createFont();
font.setFontHeightInPoints((short) 12);
bodyStyle_center.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中对齐
bodyStyle_center.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中对齐
bodyStyle_center.setWrapText(true);//自动换行
bodyStyle_center.setBorderBottom(HSSFCellStyle.BORDER_THIN); //设置底线和颜色
bodyStyle_center.setBottomBorderColor(HSSFColor.BLACK.index);
bodyStyle_center.setBorderLeft(HSSFCellStyle.BORDER_THIN); //设置左边线和颜色
bodyStyle_center.setLeftBorderColor(HSSFColor.BLACK.index);
bodyStyle_center.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边线和颜色
bodyStyle_center.setRightBorderColor(HSSFColor.BLACK.index);
bodyStyle_center.setBorderTop(HSSFCellStyle.BORDER_THIN); //设置上面线和颜色
bodyStyle_center.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
HSSFCellStyle bodyStyle_left = book.createCellStyle();
font = book.createFont();
font.setFontHeightInPoints((short) 12);
bodyStyle_left.setFont(font);
bodyStyle_left.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平居左对齐
bodyStyle_left.setBorderBottom(HSSFCellStyle.BORDER_THIN);//垂直方向对齐方式
bodyStyle_left.setWrapText(true);// 自动换行
bodyStyle_left.setBottomBorderColor(HSSFColor.BLACK.index); //设置底线和颜色
bodyStyle_left.setBorderLeft(HSSFCellStyle.BORDER_THIN);
bodyStyle_left.setLeftBorderColor(HSSFColor.BLACK.index); //设置左边线和颜色
bodyStyle_left.setBorderRight(HSSFCellStyle.BORDER_THIN);
bodyStyle_left.setRightBorderColor(HSSFColor.BLACK.index); //设置右边线和颜色
bodyStyle_left.setBorderTop(HSSFCellStyle.BORDER_THIN);
bodyStyle_left.setTopBorderColor(HSSFColor.BLACK.index); //设置上面线和颜色
bodyStyle_left.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
//创建行对象
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 1000);//行高
//设置表头
HSSFCell cell = row.createCell((short) 0);
sheet.addMergedRegion(new Region(0,(short) 0,0,(short) 10));
cell.setCellStyle(titleStyle);
//表格名称
cell.setCellValue(new HSSFRichTextString("[" + dwmc + "]及直属下级单位各类报警总数 表"));
//设置表格的表头
row = sheet.createRow(1);
cell = row.createCell((short) 0);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("序号"));
cell = row.createCell((short) 1);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("单位名称"));
cell = row.createCell((short) 2);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("报警总数"));
cell = row.createCell((short) 3);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("紧急情况报警"));
cell = row.createCell((short) 4);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("情况异常报警"));
cell = row.createCell((short) 5);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("设备被破坏/故障"));
cell = row.createCell((short) 6);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("设备断线/断电"));
cell = row.createCell((short) 7);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("违规操作报警"));
cell = row.createCell((short) 8);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("设备异常及工作提醒"));
cell = row.createCell((short) 9);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("ATM环境异常"));
cell = row.createCell((short) 10);
cell.setCellStyle(headStyle_center);
cell.setCellValue(new HSSFRichTextString("系统检测报警"));
/**
* 获取数据
**/
AfbjDataAnalysis bj = new AfbjDataAnalysis();
List<AfbjDynamicTableBean> list = null;//获取数据
list = (List<AfbjDynamicTableBean>) bj.getExpBjjlList(condition,request.getSession());
int rowindex = 2;
for(int i=0;i<list.size();i++){
row = sheet.createRow(rowindex);
cell = row.createCell((short) 0);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(i + 1);
cell = row.createCell((short) 1);
cell.setCellStyle(bodyStyle_left);
cell.setCellValue(new HSSFRichTextString(list.get(i).getDwmc()));
cell = row.createCell((short) 2);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_0()));
cell = row.createCell((short) 3);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_1()));
cell = row.createCell((short) 4);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_2()));
cell = row.createCell((short) 5);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_3()));
cell = row.createCell((short) 6);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_4()));
cell = row.createCell((short) 7);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_5()));
cell = row.createCell((short) 8);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_6()));
cell = row.createCell((short) 9);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_7()));
cell = row.createCell((short) 10);
cell.setCellStyle(bodyStyle_center);
cell.setCellValue(new HSSFRichTextString(list.get(i).getCondition_8()));
rowindex++;
}
return book;
}
}