excel导出

来源：互联网发布：中国网络为什么多喷子编辑：程序博客网时间：2024/06/15 19:05

@RequestMapping("/product/modelConfigContrastExport.do")
public void exportDimensionData(HttpServletRequest request,HttpServletResponse response){
  String excelName=null;
  try {
   excelName=java.net.URLEncoder.encode("产品-车型对比分析","UTF-8");
  } catch (UnsupportedEncodingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  String sheetName="产品-车型对比分析";
  response.setContentType("application/vnd.ms-excel;charset=utf-8");
  response.addHeader("Content-Disposition", "attachment;filename="+ excelName + ".xls");
  OutputStream out=null;
  try {
   out=response.getOutputStream();
   Workbook wb=modelConfigContrast.exportModelContrast(sheetName, request);
   if(null != wb){
    request.setAttribute(EXPORT_IS_SUCCESS, true);
          request.setAttribute(EXPORT_SIZE, (((HSSFWorkbook) wb).getBytes()).length);
          wb.write(out);
         }
   out.flush();
  } catch (IOException e) {
   request.setAttribute(EXPORT_IS_SUCCESS, false);
         request.setAttribute(EXPORT_SIZE, 0);
   e.printStackTrace();
  }finally{
   if (out != null) {
    try {
     out.close();
    } catch (IOException e) {
     e.printStackTrace();
    }
   }
  }

}

//写excel

@Override
public Workbook exportModelContrast(String sheetName,
   HttpServletRequest request) {
                //新建一个workbook
                HSSFWorkbook wb =new HSSFWorkbook();
                //得到样式
                CellStyle cellStyleHeadMsrp = mccExportUtil.getExeclHeadMsrpCellStyle((HSSFWorkbook)wb);
                //创建 sheet
                HSSFSheet sheet = wb.createSheet();
                //设置sheet名字
  wb.setSheetName(0, sheetName);

                //单元格数据填写
                HSSFRow row = sheet.createRow(rowIndex);
  row.setHeight((short)500);
  HSSFCell cell = row.createCell(colIndex);
  cell.setCellValue(Name);
  cell.setCellStyle(cellStyleHeadMsrp);
                //合并单元格
  rowIndex++;
  CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex+AdvantageMerge, colIndex, colIndex);
  sheet.addMergedRegion(range);
  row = sheet.createRow(rowIndex);
  cell = row.createCell(colIndex);
  cell.setCellValue("优势配置");
  cell.setCellStyle(mccExportUtil.getExeclHeadAdvantageCellStyle((HSSFWorkbook)wb));
                //返回workbook
                return wb;

}

样式设置

public static CellStyle Style(HSSFWorkbook wb){
  CellStyle contentStyle=wb.createCellStyle();
  Font contentFont= wb.createFont();
  contentFont.setFontName("Arial");
  contentFont.setFontHeightInPoints((short) 10);
  contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
  contentFont.setCharSet(Font.DEFAULT_CHARSET);
  contentFont.setColor(IndexedColors.BLACK.index);
  //
  contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
  contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  contentStyle.setFont(contentFont);
  contentStyle.setBorderTop(CellStyle.BORDER_NONE);
  contentStyle.setBorderBottom(CellStyle.BORDER_NONE);
  contentStyle.setBorderLeft(CellStyle.BORDER_NONE);
  contentStyle.setBorderRight(CellStyle.BORDER_NONE);
  //自定义颜色
  HSSFPalette customPalette = ((HSSFWorkbook) wb).getCustomPalette();
  customPalette.setColorAtIndex(HSSFColor.YELLOW.index, (byte) 242, (byte) 242, (byte) 242);
  contentStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
  contentStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
  //格式化数据
  HSSFDataFormat format= wb.createDataFormat();
  contentStyle.setDataFormat(format.getFormat("0.0%"));
  contentStyle.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
  return contentStyle;
}

设置去除导出后三角形标记

//格式化数据-去除导出excel后有三角形标志
public static void setCellValue(String str, Cell cell) {
  String reg = "^-?[0-9]+.?[0-9]*$";
  if (null != str && 0 != str.trim().length() && !"-".equals(str)) {
   if (str.contains(".") && !str.contains("%")) {
    if (str.matches(reg)) {
     str = str + "%";
    }
   }
   if (-1 == str.indexOf("%")) {
    if (str.matches("^-?\\d*$")) {
     cell.setCellValue(Integer.parseInt(str));
     } else {
     cell.setCellValue(str);
    }
   } else {
    String regx = "%+";
    Pattern p = Pattern.compile(regx);
    Matcher m = p.matcher(str);
    str = m.replaceAll("");
    cell.setCellValue(Double.parseDouble(str) / 100);
   }
  } else {
   cell.setCellValue(str);

  }
}

导出excel入模板
  @SuppressWarnings("unused")
public static HSSFWorkbook getHSSFWorkbook(HttpServletRequest request,boolean isLine)
{
  HSSFWorkbook wb=null;
  if(isLine){
   try {
    String path = request.getSession().getServletContext().getRealPath("/");
    wb = new HSSFWorkbook(new FileInputStream(new File(path+"exceTemplate/price/template_line_price_trend.xls")));
   } catch (FileNotFoundException e) {
    e.printStackTrace();
   } catch (IOException e) {
    e.printStackTrace();
   }

  }else{
   wb=new HSSFWorkbook();
  }

  return wb;
}

//设置背景为白底

sheet.setDisplayGridlines(false);

/*
* 用于导出维度分析excel的数据
* Title 标题
* row   行
* rowIndex 行数
* isLastRow 是否最后一行
* level 用来区分第几层每层表头不同样式 "1","2","3"
* array 数据
* format true 为百分比 false 为千分位
* haveTitle 是否有标题有true 没有 false
* style 样式数组
*/
public void DimensionByExcelData(String Title,HSSFRow row,int rowIndex,String[] array,String level,HSSFWorkbook wb,boolean isLastRow,boolean haveTitle,CellStyle style[]){

  boolean format=false;
  int colIndex = 1;
  HSSFCell cell=null;
  if(haveTitle){
   //表头
   if(isLastRow){
    if(level.equals("1")){
     colIndex = 1;
     cell = row.createCell(colIndex);
     cell.setCellValue(Title);
     cell.setCellStyle(style[0]);
       }else
    if(level.equals("2")){
     colIndex = 1;
     cell = row.createCell(colIndex);
     cell.setCellValue(Title);
     cell.setCellStyle(style[1]);
       }else
    if(level.equals("3")){
     colIndex = 1;
     cell = row.createCell(colIndex);
     cell.setCellValue(Title);
     cell.setCellStyle(style[2]);
    }
   }else{
    if(level.equals("1")){
     colIndex = 1;
     cell = row.createCell(colIndex);
     cell.setCellValue(Title);
     cell.setCellStyle(style[3]);
       }else
    if(level.equals("2")){
     colIndex = 1;
     cell = row.createCell(colIndex);
     cell.setCellValue(Title);
     cell.setCellStyle(style[4]);
       }else
    if(level.equals("3")){
     colIndex = 1;
     cell = row.createCell(colIndex);
     cell.setCellValue(Title);
     cell.setCellStyle(style[5]);
    }
   }
  }else
  if(Title.equals("1")){
   colIndex=1;
  }else{
   colIndex=0;
  }

  //数据
  for(int i =0;i<array.length;i++){
   cell=row.createCell(++colIndex);

   Style.setCellValue(array[i], cell);

   if(array[i].contains(".")){
    format = true;
   }else{
    format = false;
   }
   if(array[0].equals("Total")){
    if(format){
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclOddContentChartLastCellMinusStyle_Percent((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[6]);
//       }
      }else{
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclOddContentMinusLastCellStyleChartS((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[7]);
//       }
      }
   }else{
    if(isLastRow){
     if(rowIndex%2==0){
      if(format){
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclOddContentChartLastCellMinusStyle_Percent((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[8]);
//       }
      }else{
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclOddContentMinusLastCellStyleChartS((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[9]);
//       }
      }
     }else{
      if(format){
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclLastRowEvenContentCellMinusStyle_Percent((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[10]);
//       }
      }else{
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclEvenContentMinusLastCellStyleS((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[11]);
//       }
      }
     }
    }else{
     if(rowIndex%2==0){
      if(format){
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclOddContentChartCellMinusStyle_Percent((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[12]);
//       }
      }else{
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclOddContentCellMinusStyleChartS((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[13]);
//       }
      }
     }else{
      if(format){
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclEvenContentCellMinusStyle_Percent((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[14]);
//       }
      }else{
//       if(Double.valueOf(array[i])<0){
//        cell.setCellStyle(segmentStyle.getExeclEvenContentCellMinusStyleS((HSSFWorkbook)wb));
//       }else{
        cell.setCellStyle(style[15]);
//       }
      }
     }
    }
   }

}
}

0 0