首页 > 代码库 > poi导出excel

poi导出excel

2024-07-17 14:41:37   230人阅读

/**
  *
 * <p>方法名称: exportUtil|描述: 导出指定格式的数据</p>
 * @param projPlanList
 * @return
  */
 public String exportUtil(List projPlanList){
  try{
   HSSFWorkbook workbook = new HSSFWorkbook();
   HSSFSheet sheet = workbook.createSheet();
   int columnum = 0;
//   sheet.setColumnWidth((short)columnum++, (short)35.7*180);
   sheet.setColumnWidth((short)columnum++, (short)35.7*260);
   sheet.setColumnWidth((short)columnum++, (short)35.7*180);
   sheet.setColumnWidth((short)columnum++, (short)35.7*90);
   sheet.setColumnWidth((short)columnum++, (short)35.7*90);
   sheet.setColumnWidth((short)columnum++, (short)35.7*90);
   sheet.setColumnWidth((short)columnum++, (short)35.7*60);
   sheet.setColumnWidth((short)columnum++, (short)35.7*60);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   sheet.setColumnWidth((short)columnum++, (short)35.7*120);
   Map<String,HSSFCellStyle> styleMap = new HashMap<String,HSSFCellStyle>();
   //标题样式
   HSSFCellStyle titleStyle = workbook.createCellStyle();
   titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    //设置垂直居中 
   HSSFFont titleFont = workbook.createFont();
   titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
   titleStyle.setFont(titleFont);
   styleMap.put("titleStyle", titleStyle);
   //内容样式
   HSSFCellStyle contentStyle = workbook.createCellStyle();
   contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    //设置垂直居中 
   styleMap.put("contentStyle", contentStyle);

   
   HSSFRow row = null;
   //报表头
   row = sheet.createRow(0);
   HSSFCell cell = row.createCell((short) 0);
   cell.setCellType(HSSFCell.CELL_TYPE_STRING);
   cell.setCellValue("项目综合信息统计表");
   HSSFCellStyle titlebStyle = workbook.createCellStyle();
   titlebStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    //设置垂直居中 
   HSSFFont titlebFont = workbook.createFont();
   titlebFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
   titlebFont.setFontHeightInPoints((short) 12);
   titlebStyle.setFont(titlebFont);
   styleMap.put("titleStyle", titleStyle);
   cell.setCellStyle(titlebStyle);
   
   //报表时间
   row = sheet.createRow(1);
   cell = row.createCell((short) 0);
   cell.setCellType(HSSFCell.CELL_TYPE_STRING);
   cell.setCellValue("时间："+DateUtil.formatYYYY_MM_DD(new Date()));
   GkjrProPlan gkjrProPlan=new GkjrProPlan("平台公司","出资平台", "项目名称","部门", "所属板块",  "项目类型", "币种", "期限", "投资期结束日期", "最晚回收本息日期", "承诺出资额（万元）", "承诺未出资（万元）", "原币（万元）", "人民币（万元）", "原币（万元）", "人民币（万元）", "原币（万元）", "人民币（万元）", "出资计划时间", "出资计划金额（万元）", "回收计划时间", "本金回收（万元）", "利息收入（万元）", "已出资时间", "已出资金额（万元）", "已回收时间","已回收金额（万元）");
   //生成标题
   row = sheet.createRow(2);
   makeExcelRow(gkjrProPlan, sheet, row, 0, 0, true, styleMap);
   //内容起始行数
   int rownum = 3;
   //查询项目所关联的数据
   int cellSize=0;
   if(projPlanList!=null&&projPlanList.size()>0){
    for(int i = 0; i < projPlanList.size(); i++){
     Map projMap=(Map)projPlanList.get(i);
     GkjrProPlan gl=createProPlanData(projMap);
     Object size=projMap.get("size");
     if(size!=null){
      cellSize=(Integer)size;
     }else{
      cellSize=0;
     }
     makeExcelRow(gl, sheet, sheet.createRow(rownum), rownum, cellSize, false, styleMap);
     rownum++;
    }
   }
   // 将workbook中的数据输出
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   byte[] data = http://www.mamicode.com/baos.toByteArray();
   //queryTpye 可以queryType 查询类型 命名不同名字
   String excelName = "项目综合信息统计表.xls";
   if (data != null) {
    HttpServletResponse response = WebUtil.getResponse();
    response.reset();
    response.setContentType("application/x-download;charset=UTF-8");
    response.setHeader("Content-Disposition",
      "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8"));
    OutputStream out = response.getOutputStream();
    out.write(data);
    out.flush();
    out.close();

   }
   
  }catch (Exception e){
   e.printStackTrace();
  }
 
  return "";
 }

 

/**
  *
 * <p>方法名称: transParamType|描述:设置double的样式 </p>
 * @param flag
 * @param cell
 * @param param
  */
 public void transParamType(boolean flag,HSSFCell cell,String param){
  if(flag){
   cell.setCellType(HSSFCell.CELL_TYPE_STRING);
   cell.setCellValue(transStringData(param));
  }else{
   if(StringUtil.isStringEmpty(param)||param== "&nbsp"){
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    cell.setCellValue(transStringData(param));
   }else{
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellValue(transZDoubleData(param));
   }
  }
 }
 
 public String transStringData(Object obj){
  if(obj==null){
   return "";
  }else{
   return obj.toString();
  }
 }
 public String transDateData(Object obj){
  
  if(obj==null||StringUtil.isStringEmpty(obj.toString())){
   return "";
  }else{
   return DateUtil.formatYYYY_MM_DD((Timestamp)obj);
  }
 }
 public double transZDoubleData(Object obj){
  return  Double.parseDouble(obj.toString());
 }
 
 /**
  *
 * <p>方法名称: transOutDoubleData|描述: 转换出资金额</p>
 * @param obj
 * @return
  */
 public String transOutDoubleData(Object obj){
  if(obj==null||StringUtil.isStringEmpty(obj.toString())){
   return "";
  }else{
   double empAmount= Double.parseDouble(obj.toString());
   BigDecimal amount=new BigDecimal(empAmount);
   return "-"+amount.divide(new BigDecimal(10000)).toString();
  }
 }
 
 public String transDoubleData(Object obj){
  if(obj==null||StringUtil.isStringEmpty(obj.toString())){
   return "";
  }else{
   double empAmount= Double.parseDouble(obj.toString());
   BigDecimal amount=new BigDecimal(empAmount);
   return amount.divide(new BigDecimal(10000)).toString();
  }
 }
 
 public BigDecimal sumAmount(BigDecimal sumValue,Object obj){
  if(obj==null||StringUtil.isStringEmpty(obj.toString())){
   sumValue=http://www.mamicode.com/sumValue;
  }else{
   double empAmount= Double.parseDouble(obj.toString());
   sumValue=http://www.mamicode.com/sumValue.add(new BigDecimal(empAmount));
  }
  return sumValue;
 }
 
 /**
  *
 * <p>方法名称: makeExcelRow|描述: </p>
 * @param gkjrProPlan
 * @param sheet
 * @param row
 * @param rownum
 * @param size
 * @param flag true:标题,flase:内容
 * @param styleMap
  */
 public void makeExcelRow(GkjrProPlan gkjrProPlan,HSSFSheet sheet,HSSFRow row,int rownum,int size,boolean flag,Map<String,HSSFCellStyle> styleMap){
  HSSFCell cell = null;
  int j = 0;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getPltName());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
//  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
//  cell.setCellValue(gkjrProPlan.getPltNameAndCode());
//  cell.setCellStyle(selectStyle(styleMap, flag));
//  if(size!=0){
//   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
//  }
//  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getProName());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getOrgName());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getPlateName());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getProjectTypeName());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getCurrency());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getTerm());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getInvestEndTime());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getLastRecycelTime());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getPermitAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getUnPermitAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getOriginalCurrency());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getCnyCommitAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getOriginalFlowCurrency());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getCnyCommitFlowAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getUnOriginalFlowCurrency());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getUnCnyCommitFlowAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  if(size!=0){
   sheet.addMergedRegion(new Region(rownum-size+1,(short)j,rownum,(short)j));
  }
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getFmFuOutFundTime());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getFmFuOutFund());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getFmFuInFundTime());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getFmFuInFundAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getFmFuInFundAmountInterest());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getFmFuOutFundFlowTime());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getFmFuOutFundFlowAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格类型
  cell.setCellValue(gkjrProPlan.getFmFuInFundFlowTime());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
  cell = row.createCell((short) j);
  transParamType(flag,cell,gkjrProPlan.getFmFuInFundFlowAmount());
  cell.setCellStyle(selectStyle(styleMap, flag));
  j++;
 }
 /**
  *
 * <p>方法名称: selectStyle|描述: 选则单元格样式</p>
 * @param styleMap
 * @param flag
 * @return
  */
 public HSSFCellStyle selectStyle(Map<String,HSSFCellStyle> styleMap, boolean flag) {
  if(flag){
   return styleMap.get("titleStyle");
  }else {
   return styleMap.get("contentStyle");
  }
 }
 


联系
我们