Package jxl.write

Examples of jxl.write.WritableSheet


    AbstractJExcelView excelView = new UnixSafeAbstractJExcelView() {
      @Override
      protected void buildExcelDocument(Map<String, Object> model, WritableWorkbook wb,
          HttpServletRequest request, HttpServletResponse response) throws Exception {
        WritableSheet sheet = wb.getSheet("Sheet1");
        // test all possible permutation of row or column not existing
        sheet.addCell(new Label(2, 4, "Test Value"));
        sheet.addCell(new Label(2, 3, "Test Value"));
        sheet.addCell(new Label(3, 4, "Test Value"));
        sheet.addCell(new Label(2, 4, "Test Value"));
      }
    };

    excelView.setApplicationContext(webAppCtx);
    excelView.setUrl("template");
    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    Sheet sheet = wb.getSheet("Sheet1");
    Cell cell = sheet.getCell(0, 0);
    assertEquals("Test Template American English", cell.getContents());
  }
View Full Code Here


    AbstractJExcelView excelView = new UnixSafeAbstractJExcelView() {
      @Override
      protected void buildExcelDocument(Map<String, Object> model, WritableWorkbook wb,
          HttpServletRequest request, HttpServletResponse response) throws Exception {
        WritableSheet sheet = wb.getSheet("Sheet1");
        // test all possible permutation of row or column not existing
        sheet.addCell(new Label(2, 4, "Test Value"));
        sheet.addCell(new Label(2, 3, "Test Value"));
        sheet.addCell(new Label(3, 4, "Test Value"));
        sheet.addCell(new Label(2, 4, "Test Value"));
      }
    };

    excelView.setApplicationContext(webAppCtx);
    excelView.setUrl("template");
    excelView.render(new HashMap<String, Object>(), request, response);

    Workbook wb = Workbook.getWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
    Sheet sheet = wb.getSheet("Sheet1");
    Cell cell = sheet.getCell(0, 0);
    assertEquals("Test Template auf Deutsch", cell.getContents());
  }
View Full Code Here

   * 数据填充 将ExcelData填入excel模板
   *
   * @return ByteArrayOutputStream
   */
  public ByteArrayOutputStream fill(HttpServletRequest request) {
    WritableSheet wSheet = null;
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
          InputStream is = request.getSession().getServletContext().getResourceAsStream(getExcelTemplate().getTemplatePath());
      Workbook wb = Workbook.getWorkbook(is);
      WritableWorkbook wwb = Workbook.createWorkbook(bos, wb);
View Full Code Here

      relFile = new File(fileDir + File.separator + CapDate.getCurrentDate("yyyyMMdd") + "_ruleExport.xls");
      relFile.createNewFile();
      // 2. 產生 workbook
      workbook = Workbook.createWorkbook(relFile);
      WritableSheet sheet1 = workbook.createSheet("Tables", 0);
      WritableFont myFont = new WritableFont(WritableFont.createFont("Arial"), 12);
      WritableFont boldWhiteFnt =
          new WritableFont(WritableFont.createFont("Arial"), 12, WritableFont.BOLD);
      boldWhiteFnt.setColour(Colour.WHITE);
      WritableFont blackFnt =
          new WritableFont(WritableFont.createFont("Arial"), 12, WritableFont.NO_BOLD);
      blackFnt.setColour(Colour.BLACK);
      // 第1行先放空白資料,並設定欄寬--->還沒設定
      //DecisionTable起始先告區(黑底白字)
      WritableCellFormat headCellFmt = new WritableCellFormat();
      //自動換行
      headCellFmt.setWrap(false);
      //水平靠左
      headCellFmt.setAlignment(Alignment.LEFT);
      //垂直置中
      headCellFmt.setVerticalAlignment(VerticalAlignment.TOP);
      //設字形
      headCellFmt.setFont(boldWhiteFnt);
      //設欄位框(細)線
//      headCellFmt.setBorder(Border.ALL,BorderLineStyle.THIN, Colour.BLACK);
      //設定顏色
      headCellFmt.setBackground(Colour.GRAY_80);
     
      //DecisionTable內容橘底黑字
      WritableCellFormat setCellFmt = new WritableCellFormat();
      //自動換行
      setCellFmt.setWrap(true);
      //水平靠左
      setCellFmt.setAlignment(Alignment.LEFT);
      //垂直置中
      setCellFmt.setVerticalAlignment(VerticalAlignment.TOP);
      //設字形
      setCellFmt.setFont(myFont);
      //設欄位框(細)線
      setCellFmt.setBorder(Border.ALL,BorderLineStyle.THIN, Colour.BLACK);
      //設定顏色(粉橘色)
      setCellFmt.setBackground(Colour.TAN);
     
      //DecisionTable內容白底黑字
      WritableCellFormat bodyCellFmt = new WritableCellFormat();
      //自動換行
      bodyCellFmt.setWrap(false);
      //水平靠左
      bodyCellFmt.setAlignment(Alignment.LEFT);
      //垂直置中
      bodyCellFmt.setVerticalAlignment(VerticalAlignment.TOP);
      //設字形
      bodyCellFmt.setFont(myFont);
      //設欄位框(細)線
      bodyCellFmt.setBorder(Border.ALL,BorderLineStyle.THIN, Colour.BLACK);
      //設定顏色
//      bodyCellFmt.setBackground(Colour.BLACK);
     

      //DecisionTable內容藍底黑字
      WritableCellFormat blueCellFmt = new WritableCellFormat();
      //自動換行
      blueCellFmt.setWrap(false);
      //水平靠左
      blueCellFmt.setAlignment(Alignment.LEFT);
      //垂直置中
      blueCellFmt.setVerticalAlignment(VerticalAlignment.TOP);
      //設字形
      blueCellFmt.setFont(myFont);
      //設欄位框(細)線
      blueCellFmt.setBorder(Border.ALL,BorderLineStyle.THIN, Colour.BLACK);
      blueCellFmt.setBackground(Colour.LIGHT_TURQUOISE);

      //DecisionTable內容黃底黑字
      WritableCellFormat yellowCellFmt = new WritableCellFormat();
      //自動換行
      yellowCellFmt.setWrap(false);
      //水平靠左
      yellowCellFmt.setAlignment(Alignment.LEFT);
      //垂直置中
      yellowCellFmt.setVerticalAlignment(VerticalAlignment.TOP);
      //設字形
      yellowCellFmt.setFont(myFont);
      //設欄位框(細)線
      yellowCellFmt.setBorder(Border.ALL,BorderLineStyle.THIN, Colour.BLACK);
      yellowCellFmt.setBackground(Colour.YELLOW);

      //DecisionTable內容綠底黑字
      WritableCellFormat greenCellFmt = new WritableCellFormat();
      //自動換行
      greenCellFmt.setWrap(false);
      //水平靠左
      greenCellFmt.setAlignment(Alignment.LEFT);
      //垂直置中
      greenCellFmt.setVerticalAlignment(VerticalAlignment.TOP);
      //設字形
      greenCellFmt.setFont(myFont);
      //設欄位框(細)線
      greenCellFmt.setBorder(Border.ALL,BorderLineStyle.THIN, Colour.BLACK);
      greenCellFmt.setBackground(Colour.LIGHT_GREEN);
     
      //設列高
//      int row = 0;
//      sheet1.setRowView(row,450);
      //將第row行的欄寬設為100
//      sheet1.setColumnView(row, 100);
      // 合併儲存格(x,y, x1,y2)
//      sheet1.mergeCells(0, row, 11, row);
//      sheet1.addCell(new Label(0, 0, "", bodyCellFmt));
//      sheet1.setColumnView(0, 26);
     
      for(int i = 0; i < 65535; i++){
        sheet1.setRowView(i+1,450);
        sheet1.setColumnView(i, 23);
      }
      sheet1.setRowView(12,900);
     
      //DecisionTable開頭宣告-參數
      NumberFormat nf = new DecimalFormat("#.00");
      sheet1.addCell(new Label(1, 2, "RuleSet", headCellFmt));
      sheet1.addCell(new Label(1, 3, "Import", headCellFmt));
      sheet1.addCell(new Label(1, 4, "EscapeQuotes", headCellFmt));
      sheet1.addCell(new Label(1, 5, "Variables", headCellFmt));
      sheet1.addCell(new Label(1, 6, "Functions", headCellFmt));
      sheet1.addCell(new Label(1, 7, "Notes", headCellFmt));
      sheet1.addCell(new Label(1, 9, "RuleTable "+ rlItm.getDivRlNm(), headCellFmt));

      //DecisionTable開頭宣告-值
      sheet1.addCell(new Label(2, 2, DroolsConstants.PACKAGE_NAME, headCellFmt));
      sheet1.addCell(new Label(2, 3, DroolsConstants.IMPORT_CLASS, headCellFmt));
      sheet1.addCell(new Label(2, 4, "false", headCellFmt));
      sheet1.addCell(new Label(2, 5, DroolsConstants.VARIABLES_NAME, headCellFmt));
      //設定均量化參數
      sheet1.addCell(new Label(2, 6, "", headCellFmt));
      sheet1.addCell(new Label(2, 7, "This is decision table for "+rlItm.getDivRlNm()
          , headCellFmt));
      sheet1.addCell(new Label(2, 9, "", headCellFmt));
      //excel合併儲存格(著色)
      for(int i = 0;i <6; i++){       
        sheet1.mergeCells(2, 2+i, 7, 2+i);
      }
      //合併儲存格
      sheet1.mergeCells(2, 9, 7, 9);
      /*以下為條件判斷,分案設定內容*/
      //這一行要先決定開多少個condition和action
      //查出所有condition detail
      int conditionMaxCount = 0;
      //記錄有多少database table & column會用來做判斷
      List<String> tableNms = new ArrayList<String>();
      Map<String, DivCtDtl> colNms = new LinkedHashMap<String, DivCtDtl>();
      //排序的條件明細把它放到一個總集合,之後要填條件內容值($param)
      List<DivCtItm> ctItmList = new ArrayList<DivCtItm>();
      for(DivRlDtl rlDtl : rlDtls){
        DivCtItm ctItm = conditionMntService.findByDivCtItmNo(rlDtl.getDivCtNo());
        if(ctItm.getDivCtDtls()!=null && ctItm.getDivCtDtls().size()>conditionMaxCount){
          conditionMaxCount = ctItm.getDivCtDtls().size();
        }
        //排序的條件明細
        List<DivCtDtl> ctDtls = conditionMntService.findCtDtlsByCtNoAndSort(ctItm.getDivCtNo());
        //算出有幾種因子(因子和條件為1to1)
        for(DivCtDtl ctDtl : ctDtls){
          if(ctDtl.getDivFtDtl()!=null && ctDtl.getDivFtDtl().getDivFtItm()!=null){
            DivFtItm ftItm = ctDtl.getDivFtDtl().getDivFtItm();
            String tableNm = ftItm.getTableNm();
            String colNm = ftItm.getColumnNm();
            //每一種因子只會出現在CONDITION欄位一次
            if(!tableNms.contains(tableNm)){
              tableNms.add(tableNm);
            }
            if(!colNms.containsKey(colNm)){
              colNms.put(colNm, ctDtl);
            }
          }
        }
        ctItmList.add(ctItm);
      }//算完rule detail的內容

      //開始寫RuleTable第二行(抬頭)
      int i = 0, j = 0 ;
      Map<String, Integer> colSortNo = new HashMap<String, Integer>();
      for(String key : colNms.keySet()){
        DivCtDtl ctDtl = colNms.get(key);
        //CONDITOIN宣告欄位
        sheet1.addCell(new Label(1+j, 10, DroolsConstants.CONDITION_COL, setCellFmt));
        //資料型別宣告
        sheet1.addCell(new Label(1+j, 11, "beanClz:"+tableNms.get(0), setCellFmt));
        //這一行要擺 物件.屬性 + "比較運算元" + param/$1,$2
        String operand = "";
        if(ctDtl.getDivFtDtl()!=null){
          DivFtDtl ftDtl = ctDtl.getDivFtDtl();
          if(!CapString.isEmpty(ftDtl.getRange1()) && !CapString.isEmpty(ftDtl.getRange2())){
            operand = "$1<"+key+"&&"+key+"<=$2";
          }else{
            operand = key+"==$param";
          }
        }
        sheet1.addCell(new Label(1+j, 12, operand, setCellFmt));
        sheet1.addCell(new Label(1+j, 13, key, blueCellFmt));
        colSortNo.put(key, 1+j);
        i++;
        j++;
      }
      //Bean 宣告合併欄位
      sheet1.mergeCells(1, 11, j, 11);
     
      //宣告完CONDITION欄位,在宣告ACTIVITION-GROUP&NO-LOOP&PRIORITY
      int actGpColNo = 1+j;
      sheet1.addCell(new Label(actGpColNo, 10, DroolsConstants.ACTIVATION_GROUP, setCellFmt));
      sheet1.addCell(new Label(actGpColNo, 11, "", setCellFmt));
      sheet1.addCell(new Label(actGpColNo, 12, "", setCellFmt));
      sheet1.addCell(new Label(actGpColNo, 13, "activation group", blueCellFmt));
      int noLpColNo = actGpColNo+1;
      sheet1.addCell(new Label(noLpColNo, 10, DroolsConstants.NO_LOOP, setCellFmt));
      sheet1.addCell(new Label(noLpColNo, 11, "", setCellFmt));
      sheet1.addCell(new Label(noLpColNo, 12, "", setCellFmt));
      sheet1.addCell(new Label(noLpColNo, 13, "no loop", blueCellFmt));
      int priColNo = noLpColNo+1;
      sheet1.addCell(new Label(priColNo, 10, DroolsConstants.PRIORITY, setCellFmt));
      sheet1.addCell(new Label(priColNo, 11, "", setCellFmt));
      sheet1.addCell(new Label(priColNo, 12, "", setCellFmt));
      sheet1.addCell(new Label(priColNo, 13, "priority", blueCellFmt));
     
      //最後宣告ACTION
      int actColNo1 = priColNo+1;
      sheet1.addCell(new Label(actColNo1, 10, DroolsConstants.ACTION_COL, setCellFmt));
      sheet1.addCell(new Label(actColNo1, 11, "", setCellFmt));
      sheet1.addCell(new Label(actColNo1, 12, "beanClz.setGrpUppId(\"$param\");", setCellFmt));
      sheet1.addCell(new Label(actColNo1, 13, "", yellowCellFmt));

      int actColNo2 = actColNo1+1;
      sheet1.addCell(new Label(actColNo2, 10, DroolsConstants.ACTION_COL, setCellFmt));
      sheet1.addCell(new Label(actColNo2, 11, "", setCellFmt));
      sheet1.addCell(new Label(actColNo2, 12, "beanClz.setDocStatus(\"$param\");", setCellFmt));
      sheet1.addCell(new Label(actColNo2, 13, "", yellowCellFmt));
     
      int actColNo3 = actColNo2+1;
      sheet1.addCell(new Label(actColNo3, 10, DroolsConstants.ACTION_COL, setCellFmt));
      sheet1.addCell(new Label(actColNo3, 11, "", setCellFmt));
      sheet1.addCell(new Label(actColNo3, 12, "System.out.println(\"$param\"+ \" CASEINFO IS "
          + "\" +beanClz.getCasNo()+\" ** 分組結果 = "
          + "\" +beanClz.getGrpUppId() );", setCellFmt));
      sheet1.addCell(new Label(actColNo3, 13, "", yellowCellFmt));
      sheet1.addCell(new Label(0, 13, "Base rules", greenCellFmt));

      /*
       * 將總明細資料一筆一筆拆開來,將condition-->factor的值域填到Excel
       */
      if(!ctItmList.isEmpty()){
        int rowNo = 14, count = 0;
        for(DivCtItm ctItm : ctItmList){
          if(ctItm.getDivCtDtls()!=null){
            //CtItm list對應到自己的CtDtl,所以一筆CtItm就是一行Rule Row
            for(DivCtDtl ctDtl : ctItm.getDivCtDtls()){
              ctItm = ctDtl.getDivCtItm();
              //設定Action動作
              sheet1.addCell(new Label(actColNo1, rowNo, ctItm.getDivCtAction(), bodyCellFmt));
              sheet1.addCell(new Label(actColNo2, rowNo, ctItm.getDivCtSetting(), bodyCellFmt));
              sheet1.addCell(new Label(actColNo3, rowNo, "Test Condition::"+ctItm.getDivCtNm(), bodyCellFmt));

              DivFtDtl ftDtl = ctDtl.getDivFtDtl();
              String colNm = ftDtl.getDivFtItm().getColumnNm();
              //取得要放到第幾個cell
              Integer colNo = colSortNo.get(colNm);
              String rangeVal = "";
              if(!CapString.isEmpty(ftDtl.getRange1())){
                rangeVal = ftDtl.getRange1();
              }
              if(!CapString.isEmpty(ftDtl.getRange2())){
                rangeVal += ","+ftDtl.getRange2();
              }
              //設定行row名稱(rule name)
              sheet1.addCell(new Label(0, rowNo, ctDtl.getDivCtItm().getDivCtNm(), bodyCellFmt));
              //放入值域
              sheet1.addCell(new Label(colNo, rowNo, rangeVal, bodyCellFmt));
            }
            sheet1.addCell(new Label(actGpColNo, rowNo, "group", bodyCellFmt));
            sheet1.addCell(new Label(noLpColNo, rowNo, "true", bodyCellFmt));
            sheet1.addCell(new Label(priColNo, rowNo, String.valueOf(ctItmList.size()-count), bodyCellFmt));
            rowNo++;
            count++;
          }
        }
      }
View Full Code Here

        wbSettings.setLocale(new Locale("en", "EN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("PerformanceBenchmark", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        createLabel(excelSheet, testSummatyField);
        createContent(excelSheet, map);

        workbook.write();
        workbook.close();
View Full Code Here

      HttpServletResponse response) throws Exception {

    @SuppressWarnings("unchecked")
    Map<String, String> revenueData = (Map<String, String>) model
        .get("revenueData");
    WritableSheet sheet = workbook.createSheet("Revenue Report", 0);

    sheet.addCell(new Label(0, 0, "Month"));
    sheet.addCell(new Label(1, 0, "Revenue"));

    int rowNum = 1;
    for (Map.Entry<String, String> entry : revenueData.entrySet()) {
      // create the row data
      sheet.addCell(new Label(0, rowNum, entry.getKey()));
      sheet.addCell(new Label(1, rowNum, entry.getValue()));
      rowNum++;
    }
  }
View Full Code Here

TOP

Related Classes of jxl.write.WritableSheet

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.