Excel文件导出

jsp页面

 

//导Excel
 function makeExcel(){
     var startDate=$('#startDate').val();
     var endDate=$('#endDate').val();
    
     if('' == startDate || null == startDate){
         alert('开始日期不能为空！');
         $('#startDate').focus();
         return false ;
     }
    
     if('' == endDate || null == endDate){
         alert('终止日期不能为空！');
         $('#endDate').focus();
         return false ;
     }

  if(confirm("确认导出Excel吗?")){
   window.location.href="<%=basePath%>jsp/pmas/custReportAction!makeExcelYhsdgc.action?startDate="+startDate+"&endDate="+endDate;   
  }
 }

 

------------------------------------------------------

 

Java代码

public void makeExcelYhsdgc(){
  HttpServletResponse response = ServletActionContext.getResponse();
  Map<String, String> param = new HashMap<String, String>();
  param.put("startDate", startDate);
  param.put("endDate", endDate);
  
  List resList = new ArrayList();
  
  String filename = "10kV及以上用户受电工程信息明细表.xls";
  try {
   filename = URLEncoder.encode(filename, "UTF-8");
  } catch (UnsupportedEncodingException e1) {
   logger.error("不支持UTF-8编码", e1);
  }

  response.addHeader("Content-Disposition", "attachment;filename="+ filename);
  
  response.setContentType("application/msexcel;charset=UTF-8");// 定义输出类型

  resList = icustReportService.queryYhsdgc(param);
  ServletOutputStream sos = null;
  ByteArrayOutputStream out = null;
  try {
   sos = response.getOutputStream();

   Workbook wb = makeYhsdgcWorkbook(resList);
   out = new ByteArrayOutputStream();
   wb.write(out);
   byte[] buf = out.toByteArray();
   sos.write(buf);
   sos.flush();
  } catch (IOException e) {
   logger.error("Excel文件导出异常", e);
  } finally {
   try {
    if (sos != null) {
     sos.close();
    }
    if (out != null) {
     out.close();
    }
   } catch (IOException e) {
    logger.error("Excel文件导出异常", e);
   }
  }
 }

--------   makeYhsdgcWorkbook(resList) 方法

private Workbook makeYhsdgcWorkbook(List<CustMonthInfo> list) throws IOException {

  Workbook wb = new HSSFWorkbook();
  
  Sheet sheet = wb.createSheet("10kV及以上用户受电工程信息明细表");

  CellStyle styleTitle = wb.createCellStyle();
  styleTitle.setBorderBottom(CellStyle.BORDER_THIN);
  styleTitle.setBorderTop(CellStyle.BORDER_THIN);
  styleTitle.setBorderLeft(CellStyle.BORDER_THIN);
  styleTitle.setBorderRight(CellStyle.BORDER_THIN);
  styleTitle.setAlignment(CellStyle.ALIGN_CENTER);
  styleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  Font fontTitle = wb.createFont();
  fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
  fontTitle.setFontHeightInPoints((short) 16);
  styleTitle.setFont(fontTitle);

  // Title
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);

  cell.setCellValue("10kV及以上用户受电工程信息明细表");
  cell.setCellStyle(styleTitle);

  CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, 22);
  ExcelUtil.addMergedRegionStyle(wb, sheet, rangeAddress, styleTitle);

  // 表头
  CellStyle style = wb.createCellStyle();
  style.setBorderBottom(CellStyle.BORDER_THIN);
  style.setBorderTop(CellStyle.BORDER_THIN);
  style.setBorderLeft(CellStyle.BORDER_THIN);
  style.setBorderRight(CellStyle.BORDER_THIN);
  style.setAlignment(CellStyle.ALIGN_CENTER);
  style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  Font font = wb.createFont();
  font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
  fontTitle.setFontHeightInPoints((short) 12);
  style.setFont(font);
  row = sheet.createRow(1);
  
  // 序号
  cell = row.createCell(0);
  cell.setCellValue("序号");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 0,0), style);
  sheet.setColumnWidth((short) 0, (short) 1800);
  // 申请编号
  cell = row.createCell(1);
  cell.setCellValue("申请编号");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 1,1), style);
  sheet.setColumnWidth((short) 1, (short) 4000);
  // 户名
  cell = row.createCell(2);
  cell.setCellValue("户名");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 2,2), style);
  sheet.setColumnWidth((short) 2, (short) 10000);
  // 地址
  cell = row.createCell(3);
  cell.setCellValue("地址");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 3,3), style);
  sheet.setColumnWidth((short) 3, (short) 10000);
  // 用电联系人
  cell = row.createCell(4);
  cell.setCellValue("用电联系人");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 4,4), style);
  sheet.setColumnWidth((short) 4, (short) 4000);
  // 联系电话
  cell = row.createCell(5);
  cell.setCellValue("联系电话");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 5,5), style);
  sheet.setColumnWidth((short) 5, (short) 7000);
  // 供电容量
  cell = row.createCell(6);
  cell.setCellValue("供电容量");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 6,6), style);
  sheet.setColumnWidth((short) 6, (short) 4000);
  // 供电电压
  cell = row.createCell(7);
  cell.setCellValue("供电电压");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 7,7), style);
  sheet.setColumnWidth((short) 7, (short) 4000);
  // 申请时间
  cell = row.createCell(8);
  cell.setCellValue("申请时间");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1, 8,8), style);
  sheet.setColumnWidth((short) 7, (short) 5000);
  //接电时间
  cell = row.createCell(9);
  cell.setCellValue("接电时间");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,9, 9), style);
  sheet.setColumnWidth((short) 7, (short) 4000);
  // 设计单位
  cell = row.createCell(10);
  cell.setCellValue("设计单位");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,10, 10), style);
  sheet.setColumnWidth((short) 7, (short) 10000);
  // 施工单位
  cell = row.createCell(11);
  cell.setCellValue("施工单位");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,11, 11), style);
  sheet.setColumnWidth((short) 7, (short) 10000);
  // 高压设备生产厂家
  cell = row.createCell(12);
  cell.setCellValue("高压设备生产厂家");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,12, 12), style);
  sheet.setColumnWidth((short) 7, (short) 10000);
  // 有无供电方关联企业
  cell = row.createCell(13);
  cell.setCellValue("有无供电方关联企业");
  cell.setCellStyle(style);
  ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 1,13, 13), style);
  sheet.setColumnWidth((short) 7, (short) 3000);

  // 明细数据
  for (int i = 0; i < list.size(); i++) {

   CustMonthInfo custMonthInfo =(CustMonthInfo) list.get(i);

   row = sheet.createRow(i+3);
   // 序号
   cell = row.createCell(0);
   cell.setCellValue(""+(i+1));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 0, 0), style);
   
   // 申请编号
   cell = row.createCell(1);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getAppid()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 1, 1), style);
   // 户名
   cell = row.createCell(2);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getCustname()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 2, 2), style);
   // 地址
   cell = row.createCell(3);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getAddress()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 3, 3), style);
   // 用电联系人
   cell = row.createCell(4);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getContactname()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 4, 4), style);
   // 联系电话
   cell = row.createCell(5);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getContactphone()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 5, 5), style);
   // 供电容量
   cell = row.createCell(6);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getCapa()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 6, 6), style);
   // 供电电压
   cell = row.createCell(7);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getVoltage()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 7, 7), style);
   //申请时间
   cell = row.createCell(8);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getApptime()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 8, 8), style);
   //接电时间
   cell = row.createCell(9);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getRealtime()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 9, 9), style);
   //设计单位
   cell = row.createCell(10);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getDesignname()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 10, 10), style);
   //施工单位
   cell = row.createCell(11);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getFidname()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 11, 11), style);
   //高压设备生产厂家
   cell = row.createCell(12);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getFactory()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 12, 12), style);
   //有无供电方关联企业
   cell = row.createCell(13);
   cell.setCellValue(ExcelUtil.getNotNullString(custMonthInfo.getGlqy()));
   cell.setCellStyle(style);
   ExcelUtil.addMergedRegionStyle(wb, sheet, new CellRangeAddress(1, 2, 13, 13), style);

  }
  return wb;
 }