public void putHeaderDefault(
     WritableCellFormat cellFormat, WritableSheet sheet, Object value, int row, int column)
     throws RowsExceededException, WriteException {
   sheet.setColumnView(column, 20);
   Label label = new Label(column, row, "" + value, cellFormat);
   sheet.addCell(label);
 }
  public static void addRowTop(WritableSheet ws) throws Exception {

    WritableFont Bwf =
        new WritableFont(
            WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体:Arial,大小为18号,粗体,非斜体
    Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色
    // 创建单元格格式:
    jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf);
    CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐
    CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐
    CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色--也可以设置其他颜色)
    CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线
    CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线

    List<Label> labelList = new ArrayList<Label>();
    labelList.add(new Label(0, 0, "商品名称", CwcfF)); //
    labelList.add(new Label(1, 0, "单位", CwcfF)); //
    labelList.add(new Label(2, 0, "销售数量", CwcfF)); //
    labelList.add(new Label(3, 0, "销售成本", CwcfF));
    labelList.add(new Label(4, 0, "销售金额", CwcfF));
    labelList.add(new Label(5, 0, "利润", CwcfF));
    labelList.add(new Label(6, 0, "利润率", CwcfF));
    for (int j = 0; j < labelList.size(); j++) {
      ws.addCell(labelList.get(j));
    }
    for (int i = 0; i < ws.getColumns(); i++) {
      Cell cell = ws.getCell(i, 0);
      ws.setColumnView(i, cell.getContents().length() * 4);
    }
    //		ws.setRowView(0, 80*4);
  }
  public String exportReaderBorrowedExcel(String rootDir, ReaderView readerView) {
    List<Reader> readers = new ArrayList<Reader>();
    WritableWorkbook ww;
    String fileName = "upload" + File.separator + "readerBorrowedsStatics.xls";
    File file = new File(rootDir + fileName);
    try {
      readers = readerDao.selectReaders(readerView);
      ww = Workbook.createWorkbook(file);
      WritableSheet ws = ww.createSheet("读者借阅信息统计", 0);
      ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "读者借阅信息统计", ExcelStyle.getHeaderStyle());
      ExcelOperate.addLabelToSheet(ws, 0, 1, "借阅证号", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 1, 1, "条形码", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 2, 1, "读者姓名", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 3, 1, "姓别", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 4, 1, "当前借阅数量", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 5, 1, "累计借阅数量", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 6, 1, "读者单位", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 7, 1, "读者类别", ExcelStyle.getTitleStyle());

      int count = 2;
      for (Reader reader : readers) {
        ExcelOperate.addLabelToSheet(
            ws, 0, count, reader.getCardNo(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 1, count, reader.getBarCode(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 2, count, reader.getReaderName(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(ws, 3, count, reader.getSex(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 4, count, reader.getBorrowedQuantiy(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 5, count, reader.getTotalBQuantity(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 6, count, reader.getReaderUnits().getUnitName(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 7, count, reader.getReaderType().getReaderCateName(), ExcelStyle.getContentStyle());
        count++;
      }

      for (int i = 0; i < 8; i++) {
        ws.setColumnView(i, 16);
      }
      ws.setRowView(0, 20);
      ww.write();
      ww.close();
      System.out.println("写入excel成功!");
    } catch (Exception e) {
      System.out.println("写入excel失败!");
      e.printStackTrace();
    }
    return fileName;
  }
Beispiel #4
0
 /** @param ws @MethodName : setColumnAutoSize @Description : 设置工作表自动列宽和首行加粗 */
 private static void setColumnAutoSize(WritableSheet ws, int extraWith) {
   // 获取本列的最宽单元格的宽度
   for (int i = 0; i < ws.getColumns(); i++) {
     int colWith = 0;
     for (int j = 0; j < ws.getRows(); j++) {
       String content = ws.getCell(i, j).getContents().toString();
       int cellWith = content.length();
       if (colWith < cellWith) {
         colWith = cellWith;
       }
     }
     // 设置单元格的宽度为最宽宽度+额外宽度
     ws.setColumnView(i, colWith + extraWith);
   }
 }
Beispiel #5
0
  public void executeLast(Hashtable _tagLibrary, Hashtable _beanLibrary) {
    try {
      bean _sysPdfCC = (bean) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_CurrentCELL);
      bean _sysPdfCR = (bean) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_CurrentROW);

      int X = 0;
      try {
        X = ((Integer) _sysPdfCC.getContent()).intValue();
      } catch (Exception e) {
      }

      int Y = 0;
      try {
        Y = ((Integer) _sysPdfCR.getContent()).intValue();
      } catch (Exception e) {
      }

      WritableSheet document =
          (WritableSheet)
              (((report_element_base) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_Document))
                  .getContent());

      ((java.util.Vector)
              (((report_element_base) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_Canvas))
                  .getContent()))
          .add(this.getCellC(document.getWritableCell(X, Y), X, Y));

      try {
        if (!internal_style.getWIDTH().equals(""))
          document.setColumnView(X, new Integer(internal_style.getWIDTH()).intValue());
      } catch (Exception e) {
      }
      if (!internal_style.getHEIGHT().equals("") && parent != null && parent instanceof table_row)
        ((table_row) parent).setHEIGHT(internal_style.getHEIGHT());

      X++;

      _sysPdfCC.setContent(new Integer(X));
      _beanLibrary.put(_sysPdfCC.getName() + ":" + _sysPdfCC.getID(), _sysPdfCC);

      if (_tagLibrary.get(getName() + ":" + getID()) == null)
        _tagLibrary.remove(getName() + ":" + getID() + "_ids_" + this.motore.hashCode());
      else _tagLibrary.remove(getName() + ":" + getID());

    } catch (Exception e) {
      setError(e, iStub.log_WARN);
    }
  }
  public String exportExcel(String rootDir, SysLogView sysLogView) {
    List<SysLog> sysLogs = new ArrayList<SysLog>();
    WritableWorkbook ww;
    String fileName = "upload" + File.separator + "sysLogs.xls";
    File file = new File(rootDir + fileName);
    try {
      sysLogs = sysLogDao.selectSysLogs(sysLogView);
      ww = Workbook.createWorkbook(file);
      WritableSheet ws = ww.createSheet("日志信息", 0);
      ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "日志信息", ExcelStyle.getHeaderStyle());
      ExcelOperate.addLabelToSheet(ws, 0, 1, "操作类型", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 1, 1, "事件", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 2, 1, "操作人", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 3, 1, "日期", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 4, 1, "ip地址", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 5, 1, "业务描述", ExcelStyle.getTitleStyle());

      int count = 2;
      for (SysLog sysLog : sysLogs) {
        ExcelOperate.addLabelToSheet(
            ws, 0, count, sysLog.getOperateType(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 1, count, sysLog.getFuncName(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 2, count, sysLog.getOperator(), ExcelStyle.getContentStyle());
        String operateDate = DateFormatUtil.format(sysLog.getOperateDate(), "yyyy-MM-dd");
        ExcelOperate.addLabelToSheet(
            ws, 3, count, DateFormatUtil.convertToDate(operateDate), ExcelStyle.getDateStyle());
        ExcelOperate.addLabelToSheet(
            ws, 4, count, sysLog.getOperateIPAddress(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 5, count, sysLog.getOperateDescription(), ExcelStyle.getContentStyle());
        count++;
      }

      for (int i = 0; i < 6; i++) {
        ws.setColumnView(i, 16);
      }
      ws.setRowView(0, 20);
      ww.write();
      ww.close();
      System.out.println("写入excel成功!");
    } catch (Exception e) {
      System.out.println("写入excel失败!");
      e.printStackTrace();
    }
    return fileName;
  }
Beispiel #7
0
  /**
   *
   *
   * <DL>
   *   <DT>メソッド記述:
   *   <DD><br>
   * </DL>
   *
   * @param MCtlListVo , String
   * @return int
   * @throws BizException
   */
  public boolean exportEXCEL(String strFilePath) throws BizException {
    try {
      List<MCtlVo> lstMCtlVo = null;

      /** データある場合、データ取得 */
      lstMCtlVo = dao.exportEXCEL();

      if (lstMCtlVo == null || lstMCtlVo.size() == 0) {
        return false;
      }
      WritableWorkbook workbook = Workbook.createWorkbook(new File(strFilePath));
      WritableSheet s1 = workbook.createSheet("M_CTL", 0);

      FontName fontName = WritableFont.createFont("MS ゴシック");
      WritableFont fontFormat = new WritableFont(fontName, 12);

      // header format (color, padding, border)
      WritableCellFormat headFormat = new WritableCellFormat(fontFormat);
      headFormat.setAlignment(Alignment.CENTRE);
      headFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
      headFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      headFormat.setBackground(Colour.VERY_LIGHT_YELLOW);

      // left format
      WritableCellFormat leftCellFormat = new WritableCellFormat(fontFormat);
      leftCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      leftCellFormat.setAlignment(Alignment.LEFT);

      // center format
      WritableCellFormat centerCellFormat = new WritableCellFormat(fontFormat);
      centerCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      centerCellFormat.setAlignment(Alignment.CENTRE);

      // right format
      WritableCellFormat rightCellFormat = new WritableCellFormat(fontFormat);
      rightCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      rightCellFormat.setAlignment(Alignment.RIGHT);

      final String[] excelHeader = {
        "USERID",
        "KEY",
        "NAME",
        "DATA",
        "HELP",
        "入力桁数",
        "入力桁数(小数桁)",
        "入力属性地",
        "メンテフラグ",
        "コントロールフラグ",
        "変更可否フラグ",
        "登録ユーザー名",
        "登録PC名",
        "登録日付",
        "登録時刻",
        "最終更新ユーザー名",
        "最終更新PC名",
        "最終更新日付",
        "最終更新時刻"
      };

      int columnCount = excelHeader.length;
      for (int i = 0; i < columnCount; i++) {
        Label lbHeader = new Label(i, 0, excelHeader[i], headFormat);
        s1.addCell(lbHeader);
        if (i == 2 || i == 3) s1.setColumnView(i, 60);
        else if (i == 4) s1.setColumnView(i, 150);
        else s1.setColumnView(i, 20);
      }
      for (int i = 0; i < lstMCtlVo.size(); i++) {
        int column = 0;
        MCtlVo mCtlVo = (MCtlVo) lstMCtlVo.get(i);

        s1.addCell(new Label(column++, i + 1, mCtlVo.getUserid(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCKey(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCName(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCData(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCHelp(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCBm(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCDecbm(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCAttr(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getMtnFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCntFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getUpdFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getAddUserView(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getAddPc(), leftCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getDateWithSplitYobi(mCtlVo.getAddDate()),
                centerCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getTimeWithSplit(mCtlVo.getAddTime()),
                centerCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupUserView(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupPc(), leftCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getDateWithSplitYobi(mCtlVo.getLastupDate()),
                centerCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getTimeWithSplit(mCtlVo.getLastupTime()),
                centerCellFormat));
      }
      workbook.write();
      workbook.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return true;
  }
 /**
  * 生成一张盘点表格
  *
  * @return
  * @throws Exception
  */
 public String createCheckingExcel() throws Exception {
   if (date == null || date.trim().equals("")) {
     date = DateTool.getInstance().DateToPattern1(new Date());
   }
   warehouses = this.warehouseService.findByKeyword("");
   if (warehouse != null && warehouse.getId() != 0) {
     warehouse = this.warehouseService.get(Warehouse.class, warehouse.getId());
     stockCheckingList =
         this.stockCheckingService.findCheckingListByDateAndWarehouse(date, warehouse.getId());
   } else {
     stockCheckingList = this.stockCheckingService.findCheckingListByDate(date);
   }
   ByteOutputStream outputStream = new ByteOutputStream();
   WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
   WritableSheet sheet = workbook.createSheet("盘点记录表", 0);
   int row = 0;
   {
     Label label01 = new Label(0, row, "仓库");
     sheet.addCell(label01);
     if (warehouse.getId() != 0) {
       Label label02 = new Label(1, row, warehouse.getWnickname());
       sheet.addCell(label02);
     } else {
       Label label02 = new Label(1, row, "全部");
       sheet.addCell(label02);
     }
   }
   row++;
   {
     Label label01 = new Label(0, row, "行");
     Label label02 = new Label(1, row, "产品编码*");
     Label label03 = new Label(2, row, "产品型号*");
     Label label04 = new Label(3, row, "盘点前数量");
     Label label05 = new Label(4, row, "盘点数量");
     Label label06 = new Label(5, row, "差额");
     Label label07 = new Label(6, row, "盘点人");
     Label label08 = new Label(7, row, "时间");
     sheet.addCell(label01);
     sheet.addCell(label02);
     sheet.addCell(label03);
     sheet.addCell(label04);
     sheet.addCell(label05);
     sheet.addCell(label06);
     sheet.addCell(label07);
     sheet.addCell(label08);
   }
   row++;
   for (int i = 0; i < stockCheckingList.size(); i++) {
     StockChecking checking = stockCheckingList.get(i);
     Label label01 = new Label(0, row, "" + (i + 1));
     Label label02 = new Label(1, row, checking.getProductInfo().getBarcode());
     Label label03 = new Label(2, row, checking.getProductInfo().getPdesc());
     jxl.write.Number label04 = new jxl.write.Number(3, row, checking.getQuantity_before());
     jxl.write.Number label05 = new jxl.write.Number(4, row, checking.getQuantity_after());
     jxl.write.Number label06 =
         new jxl.write.Number(
             5, row, checking.getQuantity_after() - checking.getQuantity_before());
     Label label07 = new Label(6, row, checking.getOperator());
     Label label08 = new Label(7, row, checking.getUpdatetime());
     sheet.addCell(label01);
     sheet.addCell(label02);
     sheet.addCell(label03);
     sheet.addCell(label04);
     sheet.addCell(label05);
     sheet.addCell(label06);
     sheet.addCell(label07);
     sheet.addCell(label08);
     row++;
   }
   for (int i = 0; i < sheet.getColumns(); i++) {
     sheet.setColumnView(i, 30);
   }
   for (int i = 0; i < sheet.getRows(); i++) {
     sheet.setRowView(i, 300);
   }
   workbook.write();
   workbook.close();
   response.reset();
   response.setContentType("application/vn.ms-xls");
   response.setCharacterEncoding("utf-8");
   this.inputStream = new ByteArrayInputStream(outputStream.getBytes()); // 关键,以 inputstream 输出
   return SUCCESS;
 }
  public void ExportExcel(HttpServletResponse response, String filename, List<MemberLevel> items) {
    boolean b = false;
    OutputStream os = null;
    WritableWorkbook wbook = null;

    try {
      os = response.getOutputStream(); // 取得输出流
      response.reset(); // 清空输出流
      response.setHeader(
          "Content-disposition",
          "attachment; filename="
              + java.net.URLEncoder.encode(filename, "UTF-8")
              + ".xls"); // 设定输出文件头
      response.setContentType("application/msexcel"); // 定义输出类型

      wbook = Workbook.createWorkbook(os); // 建立excel文件
      String tmptitle = "会员级别"; // 标题
      WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称

      // 设置excel标题
      WritableFont wfont =
          new WritableFont(
              WritableFont.ARIAL,
              16,
              WritableFont.BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              Colour.BLACK);
      WritableCellFormat wcfFC = new WritableCellFormat(wfont);
      wcfFC.setBackground(Colour.AQUA);
      wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
      wfont =
          new jxl.write.WritableFont(
              WritableFont.ARIAL,
              15,
              WritableFont.BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              Colour.BLACK);
      wcfFC = new WritableCellFormat(wfont);

      int r = 0;
      wsheet.setColumnView(r, 10); // 设置列的宽度
      wsheet.setColumnView(r + 1, 13); // 设置列的宽度
      wsheet.setColumnView(r + 2, 12); // 设置列的宽度
      wsheet.setColumnView(r + 3, 12); // 设置列的宽度
      wsheet.setColumnView(r + 4, 15); // 设置列的宽度
      wsheet.setColumnView(r + 5, 15); // 设置列的宽度
      wsheet.setColumnView(r + 6, 12); // 设置列的宽度
      wsheet.setColumnView(r + 7, 12); // 设置列的宽度
      wsheet.setColumnView(r + 8, 12); // 设置列的宽度
      wsheet.setColumnView(r + 9, 12); // 设置列的宽度
      wsheet.setColumnView(r + 10, 12); // 设置列的宽度
      wsheet.setColumnView(r + 11, 12); // 设置列的宽度
      wsheet.setColumnView(r + 12, 12); // 设置列的宽度

      int f = 1; // from this row write data
      r = 0;
      // 开始生成主体内容  会员号  姓名  联系电话  会员等级  降级期?  总积分  产品积分  变更时间  前一  后一  后二  后三  后四  后五
      wsheet.addCell(new Label(r, f, "姓名"));
      wsheet.addCell(new Label(r + 1, f, "会员号"));
      // wsheet.addCell(new Label(r+2, f, "性别"));
      wsheet.addCell(new Label(r + 2, f, "联系电话"));
      wsheet.addCell(new Label(r + 3, f, "会员等级"));
      wsheet.addCell(new Label(r + 4, f, "总积分"));
      wsheet.addCell(new Label(r + 5, f, "产品积分"));
      wsheet.addCell(new Label(r + 6, f, "变更时间"));
      wsheet.addCell(new Label(r + 7, f, "前一"));
      wsheet.addCell(new Label(r + 8, f, "后一"));
      wsheet.addCell(new Label(r + 9, f, "后二"));
      wsheet.addCell(new Label(r + 10, f, "后三"));
      wsheet.addCell(new Label(r + 11, f, "后四"));
      wsheet.addCell(new Label(r + 12, f, "后五"));

      f++;
      r = 0;
      int row = 0;
      for (MemberLevel item : items) {

        wsheet.addCell(new Label(r, row + f, item.getRealName())); // 数据库的城市名字段
        wsheet.addCell(new Label(r + 1, row + f, item.getMemberId())); // 数据库的城市名字段
        wsheet.addCell(new Label(r + 2, row + f, item.getPhoneNo())); // 数据库的城市名字段
        wsheet.addCell(new Label(r + 3, row + f, item.getLevelName()));
        wsheet.addCell(new Label(r + 4, row + f, item.getTotalScore() + ""));
        wsheet.addCell(new Label(r + 5, row + f, item.getSnScore() + ""));
        wsheet.addCell(new Label(r + 6, row + f, item.getUpdateTime()));
        wsheet.addCell(new Label(r + 7, row + f, item.getScore1() + ""));
        wsheet.addCell(new Label(r + 8, row + f, item.getScore2() + ""));
        wsheet.addCell(new Label(r + 9, row + f, item.getScore3() + ""));
        wsheet.addCell(new Label(r + 10, row + f, item.getScore4() + ""));
        wsheet.addCell(new Label(r + 11, row + f, item.getScore5() + ""));
        wsheet.addCell(new Label(r + 12, row + f, item.getScore6() + ""));
        row++;
      }
      // 主体内容生成结束
      wbook.write(); // 写入文件
      wbook.close();
      os.close(); // 关闭流
      b = true;
    } catch (java.net.SocketException exc) {
      exc.printStackTrace();
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      try {
        if (wbook != null) wbook.close();
        if (os != null) os.close(); // 关闭流
      } catch (Exception ex) {
      }
    }
  }
  public static void export(
      String field, String value, String start, String end, TaodianApi api, OutputStream os) {
    try {
      WritableWorkbook wbook = Workbook.createWorkbook(os);
      WritableSheet wsheet = wbook.createSheet("访问详情", 0);

      WritableFont wfont =
          new WritableFont(
              WritableFont.ARIAL,
              16,
              WritableFont.BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              Colour.BLACK);
      WritableCellFormat wcfFC = new WritableCellFormat(wfont);
      wcfFC.setBackground(Colour.AQUA);
      // wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
      wfont =
          new jxl.write.WritableFont(
              WritableFont.ARIAL,
              14,
              WritableFont.BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              Colour.BLACK);
      wcfFC = new WritableCellFormat(wfont);

      int row = 0;

      wsheet.addCell(new Label(0, row, "短网址"));
      wsheet.setColumnView(0, 10);

      wsheet.addCell(new Label(1, row, "商家"));
      wsheet.setColumnView(1, 12);

      wsheet.addCell(new Label(2, row, "商品"));
      wsheet.setColumnView(2, 12);

      wsheet.addCell(new Label(3, row, "推广者"));
      wsheet.setColumnView(3, 6);

      wsheet.addCell(new Label(4, row, "点击单价"));
      wsheet.setColumnView(4, 7);

      wsheet.addCell(new Label(5, row, "访问IP"));
      wsheet.setColumnView(5, 14);

      wsheet.addCell(new Label(6, row, "访问设备"));
      wsheet.setColumnView(6, 8);

      wsheet.addCell(new Label(7, row, "浏览器"));
      wsheet.setColumnView(7, 8);

      wsheet.addCell(new Label(8, row, "访问时间"));
      wsheet.setColumnView(8, 20);

      wsheet.addCell(new Label(9, row, "访问者ID"));
      wsheet.setColumnView(9, 12);

      wsheet.addCell(new Label(10, row, "访问来源"));
      wsheet.setColumnView(10, 80);

      wsheet.addCell(new Label(11, row, "Agent"));
      wsheet.setColumnView(11, 80);

      DataIterator data = new DataIterator(field, value, start, end, api);

      row++;
      while (data.hasNext()) {
        writeRow(wsheet, data.next(), row++);
      }

      wbook.write(); // 写入文件
      wbook.close();

    } catch (Exception e) {
      log.error(e, e);
    }
  }
  public void createEmployeesFile(List<Employee> list, OutputStream os) throws Exception {
    WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
    WritableSheet wsheet = wbook.createSheet("Sheet1", 0); // 工作表名称

    // 设置公司名
    WritableFont companyfont =
        new WritableFont(
            WritableFont.createFont("宋体"),
            18,
            WritableFont.BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat companyFormat = new WritableCellFormat(companyfont);
    companyFormat.setAlignment(jxl.format.Alignment.CENTRE);
    companyFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    Label excelCompany = new Label(0, 0, "桐庐富伟针织有限公司员工花名册", companyFormat);
    wsheet.addCell(excelCompany);
    wsheet.mergeCells(0, 0, 13, 0);
    wsheet.setRowView(0, 800);

    // 设置Excel字体
    WritableFont wfont =
        new WritableFont(
            WritableFont.createFont("宋体"),
            10,
            WritableFont.BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat titleFormat = new WritableCellFormat(wfont);
    titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
    titleFormat.setBorder(
        jxl.format.Border.ALL,
        jxl.format.BorderLineStyle.THIN,
        jxl.format.Colour.BLACK); // BorderLineStyle边框

    WritableFont wfont2 =
        new WritableFont(
            WritableFont.createFont("宋体"),
            10,
            WritableFont.NO_BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat titleFormat2 = new WritableCellFormat(wfont2);
    titleFormat2.setAlignment(jxl.format.Alignment.CENTRE);
    titleFormat2.setBorder(
        jxl.format.Border.ALL,
        jxl.format.BorderLineStyle.THIN,
        jxl.format.Colour.BLACK); // BorderLineStyle边框

    String[] title = {
      "编号", "姓名", "性别", "入厂日期", "身份证号码", "联系方式", "岗位", "部门", "家庭住址", "现居住地", "合同期限", "用工形式", "离职时间",
      "时薪"
    };
    // 设置Excel表头
    int col = 0;
    int merge_col = 0;
    int columnBestWidth[] = new int[title.length + 1]; // 保存最佳列宽数据的数组
    for (int i = 0; i < title.length; i++, col++) {
      columnBestWidth[col] = title[i].getBytes().length;
      Label excelTitle = new Label(col, 1, title[i], titleFormat);

      if (title[i].equals("合同期限")) {
        merge_col = col;
        col++;
        columnBestWidth[col] = title[i].getBytes().length;
      }
      wsheet.addCell(excelTitle);
    }

    wsheet.setRowView(1, 400);
    wsheet.mergeCells(merge_col, 1, merge_col + 1, 1);

    int c = 2; // 用于循环时Excel的行号 		

    for (Employee employee : list) {
      wsheet.setRowView(c, 400);
      Label content1 = new Label(0, c, employee.getNumber(), titleFormat2);
      Label content2 = new Label(1, c, employee.getName(), titleFormat2);
      Label content3 = new Label(2, c, employee.getSex(), titleFormat2);
      Label content4 =
          new Label(3, c, DateTool.formatDateYMD(employee.getEnter_at()), titleFormat2);
      Label content5 = new Label(4, c, employee.getId_card(), titleFormat2);
      Label content6 = new Label(5, c, employee.getTel(), titleFormat2);
      Label content7 = new Label(6, c, employee.getJob(), titleFormat2);
      Label content8 =
          new Label(7, c, SystemCache.getDepartmentName(employee.getDepartmentId()), titleFormat2);
      Label content9 = new Label(8, c, employee.getAddress_home(), titleFormat2);
      Label content10 = new Label(9, c, employee.getAddress(), titleFormat2);
      Label content11 =
          new Label(10, c, DateTool.formatDateYMD(employee.getAgreement_at()), titleFormat2);
      Label content12 =
          new Label(11, c, DateTool.formatDateYMD(employee.getAgreement_end()), titleFormat2);
      Label content13 = new Label(12, c, employee.getEmployee_type(), titleFormat2);
      Label content14 =
          new Label(13, c, DateTool.formatDateYMD(employee.getLeave_at(), "/"), titleFormat2);
      Label content15 =
          new Label(
              14,
              c,
              employee.getHour_salary() == null ? "" : String.valueOf(employee.getHour_salary()),
              titleFormat2);

      wsheet.addCell(content1);
      wsheet.addCell(content2);
      wsheet.addCell(content3);
      wsheet.addCell(content4);
      wsheet.addCell(content5);
      wsheet.addCell(content6);
      wsheet.addCell(content7);
      wsheet.addCell(content8);
      wsheet.addCell(content9);
      wsheet.addCell(content10);
      wsheet.addCell(content11);
      wsheet.addCell(content12);
      wsheet.addCell(content13);
      wsheet.addCell(content14);
      wsheet.addCell(content15);

      int width1 = content1.getContents().getBytes().length;
      int width2 = content2.getContents().getBytes().length;
      int width3 = content3.getContents().getBytes().length;
      int width4 = content4.getContents().getBytes().length;
      int width5 = content5.getContents().getBytes().length;
      int width6 = content6.getContents().getBytes().length;
      int width7 = content7.getContents().getBytes().length;
      int width8 = content8.getContents().getBytes().length;
      int width9 = content9.getContents().getBytes().length;
      int width10 = content10.getContents().getBytes().length;
      int width11 = content11.getContents().getBytes().length;
      int width12 = content12.getContents().getBytes().length;
      int width13 = content13.getContents().getBytes().length;
      int width14 = content14.getContents().getBytes().length;
      int width15 = content15.getContents().getBytes().length;
      if (columnBestWidth[0] < width1) {
        columnBestWidth[0] = width1;
      }
      if (columnBestWidth[1] < width2) {
        columnBestWidth[1] = width2;
      }
      if (columnBestWidth[2] < width3) {
        columnBestWidth[2] = width3;
      }
      if (columnBestWidth[3] < width4) {
        columnBestWidth[3] = width4;
      }
      if (columnBestWidth[4] < width5) {
        columnBestWidth[4] = width5;
      }
      if (columnBestWidth[5] < width6) {
        columnBestWidth[5] = width6;
      }
      if (columnBestWidth[6] < width7) {
        columnBestWidth[6] = width7;
      }
      if (columnBestWidth[7] < width8) {
        columnBestWidth[7] = width8;
      }
      if (columnBestWidth[8] < width9) {
        columnBestWidth[8] = width9;
      }
      if (columnBestWidth[9] < width10) {
        columnBestWidth[9] = width10;
      }
      if (columnBestWidth[10] < width11) {
        columnBestWidth[10] = width11;
      }
      if (columnBestWidth[11] < width12) {
        columnBestWidth[11] = width12;
      }
      if (columnBestWidth[12] < width13) {
        columnBestWidth[12] = width13;
      }
      if (columnBestWidth[13] < width14) {
        columnBestWidth[13] = width14;
      }
      if (columnBestWidth[14] < width15) {
        columnBestWidth[14] = width15;
      }
      c++;
    }
    for (int p = 0; p < columnBestWidth.length; ++p) {
      wsheet.setColumnView(p, columnBestWidth[p] + 1);
    }
    wbook.write(); // 写入文件
    wbook.close();
    os.close();
  }
Beispiel #12
0
  /**
   * Создание шапки с датами и марками топлива
   *
   * @param column - номер первой колонки
   * @param row - номер первой строки
   * @param sheet - лист, на котором будет записываться
   * @throws RowsExceededException
   * @throws WriteException
   */
  private int cape(int column, int row, WritableSheet sheet)
      throws RowsExceededException, WriteException {

    /**
     * формат записи даты<br>
     * Пример 26.04.2013 г.
     */
    SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");

    /** Список надписей под результатом */
    String[] result = {"Изменение цен %", "Изменение цен руб."};

    /*
     * Поле "Дата"
     */
    sheet.addCell(new Label(column, row, "Дата", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column, row, column, row + 1);
    sheet.setColumnView(column, 15);

    /** Поле "Марка бензина" */
    sheet.addCell(new Label(column + 1, row, "Марка бензина", font.tahoma9ptBoldMedion));
    sheet.mergeCells(column + 1, row, column + 1, row + 1);
    sheet.setColumnView(column + 1, 10);

    /** первая строчка под наименование поставщика */
    sheet.setRowView(row, 900);
    /** вторая строчка под пояснение выбранного значения */
    sheet.setRowView(row + 1, 2000);

    /** Запись даты и марки топлива */
    for (int i = 0; i < time.length; i++) {
      /** дата */
      sheet.addCell(
          new Label(
              column,
              row + 2 + i * 7,
              formatter.format(new Date(time[i])) + " г.",
              font.tahoma9ptBoldMedion));
      /** склеивание со строчками марки топлива */
      sheet.mergeCells(column, row + 2 + i * 7, column, row + 2 + i * 7 + 6);

      /** запись марки топлива */
      for (int p = 0; p < label_toplivo.length; p++) {
        sheet.addCell(
            new Label(column + 1, row + 2 + i * 7 + p, label_toplivo[p], font.tahomaLabelToplivo));
      }
    }

    // для удобства
    int nextRow = row + 2 + 7 * time.length;

    /** Запись результата */
    for (String res : result) {
      /** Титульник */
      sheet.addCell(new Label(column, nextRow, res, font.tahoma9ptBoldMedion));
      sheet.mergeCells(column, nextRow, column + 1, nextRow);

      /** марка топлива */
      for (int p = 0; p < label_toplivo.length; p++) {
        sheet.addCell(
            new Label(column, nextRow + 1 + p, label_toplivo[p], font.tahoma9ptBoldMedion));
        sheet.mergeCells(column, nextRow + 1 + p, column + 1, nextRow + 1 + p);
      }

      /** смещение к след пункту, без потери номера строки */
      nextRow += label_toplivo.length + 1;
    }

    return column + 2;
  }
  public void doFinish(IProgressMonitor monitor) {
    monitor.beginTask("Exporting ", rowCount);

    try {
      switch (ComboFile) {
        case 0:
          WritableWorkbook myWorkbook = null;
          String str = null;
          try {
            str = command.Command_Int("SELECT COUNT(*) FROM " + sCollection);
          } catch (Exception e) {
            e.printStackTrace();
          }
          if (Integer.parseInt(str) > 50000) {
            int j = 0;
            int k = 0;
            int m = 0;
            while (k * 50000 < Integer.parseInt(str)) {
              if (Integer.parseInt(str) > 100000)
                myWorkbook = Workbook.createWorkbook(new File(sFullPath + "(" + m + ")" + ".xls"));
              else {
                myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls"));
              }
              int l = 0;
              for (; k * 50000 < Integer.parseInt(str); k++) {
                myWorkbook.createSheet("Sheet" + k, k);
                WritableSheet mySheet = myWorkbook.getSheet(l);
                String sImsi = "";
                for (int i = 0; i < oColumns.size(); i++) {
                  sImsi = oColumns.get(i);
                  WritableCellFormat ColumnFormat = new WritableCellFormat();
                  ColumnFormat.setAlignment(Alignment.CENTRE);
                  ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
                  ColumnFormat.setBackground(Colour.GRAY_25);
                  mySheet.setColumnView(i, (sImsi.trim().length() * 2));
                  jxl.write.Label oColumnLabel = null;
                  oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat);
                  mySheet.addCell(oColumnLabel);
                }
                sQuery = "selectattr " + sAttr + "'limit " + 50000 + "offset " + (j) * 50000 + "'";
                ArrayList<String> oExcel = command.Command_Excel(sQuery);
                int nTotal = oExcel.size() / oColumns.size();
                int nColumn = oColumns.size();
                int nCount = 0;
                for (int nRow = 1; nRow <= nTotal; nRow++) {
                  for (int nCol = 0; nCol < nColumn; nCol++) {
                    jxl.write.Label numberLabels = null;
                    numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++));
                    mySheet.addCell(numberLabels);
                  }
                  monitor.worked(1);
                }
                j++;
                l++;
                if (l == 2) {
                  k++;
                  break;
                }
              }
              m++;
              myWorkbook.write();
              myWorkbook.close();
            }
          } else if (Integer.parseInt(str) < 50000) {
            myWorkbook = Workbook.createWorkbook(new File(sFullPath + ".xls"));
            WritableSheet mySheet = myWorkbook.createSheet(sCollection.replaceAll("/", "_"), 0);

            String sImsi = "";
            for (int i = 0; i < oColumns.size(); i++) {
              sImsi = oColumns.get(i);

              WritableCellFormat ColumnFormat = new WritableCellFormat();
              ColumnFormat.setAlignment(Alignment.CENTRE);
              ColumnFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
              ColumnFormat.setBackground(Colour.GRAY_25);
              mySheet.setColumnView(i, (sImsi.trim().length() * 2));
              jxl.write.Label oColumnLabel = null;

              oColumnLabel = new jxl.write.Label(i, 0, sImsi, ColumnFormat);
              mySheet.addCell(oColumnLabel);
            }

            ArrayList<String> oExcel = command.Command_Excel(sQuery);

            int nTotal = oExcel.size() / oColumns.size();
            int nColumn = oColumns.size();

            int nCount = 0;
            for (int nRow = 1; nRow <= nTotal; nRow++) {
              for (int nCol = 0; nCol < nColumn; nCol++) {
                jxl.write.Label numberLabels = null;
                numberLabels = new jxl.write.Label(nCol, nRow, oExcel.get(nCount++));
                mySheet.addCell(numberLabels);
              }
              monitor.worked(1);
            }
            myWorkbook.write();
            myWorkbook.close();
          }
          final int sheet = lblsheet;
          final int File;
          if ((lblsheet / 2) == 0) {
            File = 1;
          } else {
            File = (lblsheet / 2);
          }
          if (istrue == true) {
            getShell()
                .getDisplay()
                .syncExec(
                    new Runnable() {
                      public void run() {
                        MessageDialog.openInformation(
                            getShell(),
                            "AMGA_Mangaer",
                            "Success : Files have been saved["
                                + sFullPath
                                + (page2.getComboFile() == 0 ? ".xls" : ".txt")
                                + "]"
                                + "\r\n"
                                + "File : "
                                + File
                                + " Sheet : "
                                + sheet);
                      }
                    });
          }

          break;
        case 1:
          // Txt
          String sDelimited = "";
          switch (ComboString) {
            case 0:
              sDelimited = "\t";
              break;
            case 1:
              sDelimited = " ";
              break;
            case 2:
              sDelimited = ",";
              break;
            default:
              break;
          }

          String sTitle = "";

          if (p1btnselection == true) {

            sQuery = "selectattr " + sAttr + " ' '";
            for (int i = 0; i < oColumns.size(); i++) {
              sTitle = sTitle + oColumns.get(i) + sDelimited;
            }
          } else {
            sQuery = p1_1text;
            String Query = sQuery;
            ArrayList<String> ooColumn = new ArrayList<String>();
            int nQuery = 0;
            // selectattr
            for (int i = 0; i < Query.length(); i++) {
              if (Query.charAt(i) == '\'') {
                nQuery = i;
                break;
              }
            }
            StringTokenizer st1 = new StringTokenizer(Query.substring(0, nQuery), " ");
            while (st1.hasMoreElements()) {
              String sTemp = (String) st1.nextElement();
              if (sTemp.trim().charAt(0) == '/') {
                ooColumn.add(sTemp);
              }
            }

            for (int i = 0; i < ooColumn.size(); i++) {
              sTitle = sTitle + ooColumn.get(i).substring(page1.str.length() + 1) + sDelimited;
            }

            nColumnCount = ooColumn.size();
          }
          ArrayList<String> oTxt = null;
          try {
            oTxt = command.Command_Txt(sQuery, sDelimited, nColumnCount);
          } catch (Exception e) {
            final String str1 = e.getLocalizedMessage();
            getShell()
                .getDisplay()
                .syncExec(
                    new Runnable() {
                      public void run() {
                        MessageDialog.openError(getShell(), "AMGA_Mangaer", str1);
                      }
                    });
            istrue = false;
          }
          oTxt.add(0, sTitle);
          BufferedWriter out = new BufferedWriter(new FileWriter(sFullPath + ".txt"));
          for (int i = 0; i < oTxt.size(); i++) {
            out.write(oTxt.get(i));
            out.newLine();
            monitor.worked(1);
          }
          out.close();
          if (istrue == true) {
            getShell()
                .getDisplay()
                .syncExec(
                    new Runnable() {
                      public void run() {
                        MessageDialog.openInformation(
                            getShell(),
                            "AMGA_Mangaer",
                            "Success : Files have been saved["
                                + sFullPath
                                + (page2.getComboFile() == 0 ? ".xls" : ".txt")
                                + "]");
                      }
                    });
          }
          break;
        default:
          break;
      }
    } catch (Exception e) {
      final String str = e.getLocalizedMessage();
      getShell()
          .getDisplay()
          .syncExec(
              new Runnable() {
                public void run() {
                  MessageDialog.openError(getShell(), "AMGA_Mangaer", str);
                }
              });
    }
  }
  public String exportExcel(String rootDir, ReaderView readerView) {
    List<Reader> readers = new ArrayList<Reader>();
    WritableWorkbook ww;
    String fileName = "upload" + File.separator + "readers.xls";
    File file = new File(rootDir + fileName);
    try {
      readers = readerDao.selectReaders(readerView);
      ww = Workbook.createWorkbook(file);
      WritableSheet ws = ww.createSheet("读者信息", 0);
      ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "读者信息", ExcelStyle.getHeaderStyle());
      ExcelOperate.addLabelToSheet(ws, 0, 1, "借阅证号", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 1, 1, "密码", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 2, 1, "条形码", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 3, 1, "读者姓名", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 4, 1, "出生日期", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 5, 1, "姓别", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 6, 1, "邮箱", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 7, 1, "联系电话", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 8, 1, "余额", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 9, 1, "拼音", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 10, 1, "办证日期", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 11, 1, "有效日期", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 12, 1, "当前借阅数量", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 13, 1, "累计借阅数量", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 14, 1, "读者描述", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 15, 1, "读者单位", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 16, 1, "读者类别", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 17, 1, "证件类别", ExcelStyle.getTitleStyle());
      ExcelOperate.addLabelToSheet(ws, 18, 1, "借阅证状态", ExcelStyle.getTitleStyle());

      int count = 2;
      for (Reader reader : readers) {
        ExcelOperate.addLabelToSheet(
            ws, 0, count, reader.getCardNo(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 1, count, reader.getPassword(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 2, count, reader.getBarCode(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 3, count, reader.getReaderName(), ExcelStyle.getContentStyle());

        String birthday = DateFormatUtil.format(reader.getBirthday(), "yyyy-MM-dd");
        ExcelOperate.addLabelToSheet(
            ws, 4, count, DateFormatUtil.convertToDate(birthday), ExcelStyle.getDateStyle());
        //				ExcelOperate.addLabelToSheet(ws, 4, count,reader.getBirthday(),
        // ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(ws, 5, count, reader.getSex(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(ws, 6, count, reader.getEmail(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 7, count, reader.getContactTel(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 8, count, reader.getLeftMoney(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(ws, 9, count, reader.getSpell(), ExcelStyle.getContentStyle());

        String entyDate = DateFormatUtil.format(reader.getEntyDate(), "yyyy-MM-dd");
        ExcelOperate.addLabelToSheet(
            ws, 10, count, DateFormatUtil.convertToDate(entyDate), ExcelStyle.getDateStyle());
        //				ExcelOperate.addLabelToSheet(ws, 10, count,reader.getEntyDate(),
        // ExcelStyle.getContentStyle());

        String effectiveDate = DateFormatUtil.format(reader.getEffectiveDate(), "yyyy-MM-dd");
        ExcelOperate.addLabelToSheet(
            ws, 11, count, DateFormatUtil.convertToDate(effectiveDate), ExcelStyle.getDateStyle());
        //				ExcelOperate.addLabelToSheet(ws, 11,
        // count,reader.getEffectiveDate(),ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 12, count, reader.getBorrowedQuantiy(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 13, count, reader.getTotalBQuantity(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 14, count, reader.getReaderDesc(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 15, count, reader.getReaderUnits().getUnitName(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws,
            16,
            count,
            reader.getReaderType().getReaderCateName(),
            ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 17, count, reader.getCertificate().getItemName(), ExcelStyle.getContentStyle());
        ExcelOperate.addLabelToSheet(
            ws, 18, count, reader.getCardState().getItemName(), ExcelStyle.getContentStyle());
        count++;
      }

      for (int i = 0; i < 20; i++) {
        ws.setColumnView(i, 16);
      }
      ws.setRowView(0, 20);
      ww.write();
      ww.close();
      System.out.println("写入excel成功!");
    } catch (Exception e) {
      System.out.println("写入excel失败!");
      e.printStackTrace();
    }
    return fileName;
  }
Beispiel #15
0
  public static void main(String args[]) {
    try {
      // 打开文件
      WritableWorkbook book = Workbook.createWorkbook(new File("/home/uc/local/sandy/测试.xls"));
      // 生成名为“第一页”的工作表,参数0表示这是第一页
      WritableSheet sheetOne = book.createSheet("第一页", 0);

      /** 定义单元格样式 */
      WritableFont wf_title =
          new WritableFont(
              WritableFont.ARIAL,
              11,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
      WritableFont wf_head =
          new WritableFont(
              WritableFont.ARIAL,
              11,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
      WritableFont wf_table =
          new WritableFont(
              WritableFont.ARIAL,
              11,
              WritableFont.NO_BOLD,
              false,
              UnderlineStyle.NO_UNDERLINE,
              jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色

      WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义
      wcf_title.setBackground(jxl.format.Colour.WHITE); // 设置单元格的背景颜色
      wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
      wcf_title.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框

      WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义
      wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色
      wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
      wcf_title1.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框

      WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); // 单元格定义
      wcf_title2.setBackground(jxl.format.Colour.YELLOW2); // 设置单元格的背景颜色
      wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
      wcf_title2.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框

      WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head);
      wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);
      wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_head1.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      WritableCellFormat wcf_head2 = new WritableCellFormat(wf_head);
      wcf_head2.setBackground(jxl.format.Colour.YELLOW2);
      wcf_head2.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_head2.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table);
      wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN);
      wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_table1.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      WritableCellFormat wcf_table2 = new WritableCellFormat(wf_table);
      wcf_table2.setBackground(jxl.format.Colour.YELLOW2);
      wcf_table2.setAlignment(jxl.format.Alignment.CENTRE);
      wcf_table2.setBorder(
          jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);

      sheetOne.setColumnView(0, 15); // 设置列的宽度
      sheetOne.setColumnView(1, 15); // 设置列的宽度
      sheetOne.setColumnView(2, 15); // 设置列的宽度
      sheetOne.setColumnView(3, 15); // 设置列的宽度
      sheetOne.setColumnView(4, 15); // 设置列的宽度
      sheetOne.setColumnView(5, 15); // 设置列的宽度
      sheetOne.setColumnView(6, 15); // 设置列的宽度
      sheetOne.setColumnView(7, 15); // 设置列的宽度
      sheetOne.setColumnView(8, 15); // 设置列的宽度
      sheetOne.setColumnView(9, 15); // 设置列的宽度
      sheetOne.setColumnView(10, 15); // 设置列的宽度
      sheetOne.setColumnView(11, 15); // 设置列的宽度
      sheetOne.setColumnView(12, 15); // 设置列的宽度
      sheetOne.setColumnView(13, 15); // 设置列的宽度

      // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
      // 以及单元格内容为test
      Label title = new Label(0, 0, "统计", wcf_title);
      Label titleOne = new Label(0, 1, "统计1", wcf_title1);
      Label titleTwo = new Label(2, 1, "统计2", wcf_title2);

      Label column1 = new Label(0, 2, "姓名", wcf_head1);
      Label column2 = new Label(1, 2, "所选课程", wcf_head1);

      Label column3 = new Label(2, 2, "姓名", wcf_head2);
      Label column4 = new Label(3, 2, "所选课程", wcf_head2);

      // 或者WritableCell cell =  new jxl.write.Number(column, row, value, wcf)
      // 将定义好的单元格添加到工作表中
      sheetOne.addCell(title);
      sheetOne.addCell(titleOne);
      sheetOne.addCell(titleTwo);

      sheetOne.addCell(column1);
      sheetOne.addCell(column2);
      sheetOne.addCell(column3);
      sheetOne.addCell(column4);

      // 合: 第1列第1行  到 第13列第1行
      sheetOne.mergeCells(0, 0, 3, 0);
      sheetOne.mergeCells(0, 1, 1, 1);
      sheetOne.mergeCells(2, 1, 3, 1);

      /*动态数据   */
      Label content1 = new Label(0, 3, "张三", wcf_table1);
      Label content2 = new Label(0, 4, "张三", wcf_table1);
      Label content3 = new Label(0, 5, "张三", wcf_table1);
      Label kecheg1 = new Label(1, 3, "语文", wcf_table1);
      Label kecheg2 = new Label(1, 4, "数学", wcf_table1);
      Label kecheg3 = new Label(1, 5, "英语", wcf_table1);

      sheetOne.addCell(content1);
      sheetOne.addCell(content2);
      sheetOne.addCell(content3);
      sheetOne.addCell(kecheg1);
      sheetOne.addCell(kecheg2);
      sheetOne.addCell(kecheg3);

      sheetOne.mergeCells(0, 3, 0, 2 + 3);

      Label content11 = new Label(2, 3, "李四", wcf_table2);
      Label content22 = new Label(2, 4, "李四", wcf_table2);
      Label content33 = new Label(2, 5, "李四", wcf_table2);
      Label kecheg11 = new Label(3, 3, "语文", wcf_table2);
      Label kecheg22 = new Label(3, 4, "数学", wcf_table2);
      Label kecheg33 = new Label(3, 5, "英语", wcf_table2);

      sheetOne.addCell(content11);
      sheetOne.addCell(content22);
      sheetOne.addCell(content33);
      sheetOne.addCell(kecheg11);
      sheetOne.addCell(kecheg22);
      sheetOne.addCell(kecheg33);

      sheetOne.mergeCells(2, 3, 2, 2 + 3);

      // 写入数据并关闭文件
      book.write();
      book.close();
    } catch (Exception e) {
      System.out.println(e);
    }
  }
Beispiel #16
0
  public static Map<String, Object> exportCommunicationEvent(
      DispatchContext dctx, Map<String, ?> context) {
    Delegator delegator = dctx.getDelegator();

    String outpath = (String) context.get("outpath"); // mandatory
    List<String> results = FastList.newInstance();
    String commEventId = null;

    if (UtilValidate.isNotEmpty(outpath)) {
      File outdir = new File(outpath);
      if (!outdir.exists()) {
        outdir.mkdir();
      }
      if (outdir.isDirectory() && outdir.canWrite()) {

        String entityName = "CommunicationEvent";
        long numberWritten = 0;
        EntityListIterator values = null;
        WritableWorkbook workbook = null;

        List conditionExprs = FastList.newInstance();
        conditionExprs.add(
            EntityCondition.makeCondition(
                "communicationEventTypeId", EntityOperator.EQUALS, "EMAIL_COMMUNICATION"));
        conditionExprs.add(
            EntityCondition.makeCondition(
                "contactMechTypeId", EntityOperator.EQUALS, "EMAIL_ADDRESS"));
        List orExprs = FastList.newInstance();
        String cntctUsSbjStr = "%Contact us%".toUpperCase();
        String reqCatSbjStr = "%Request Catalog%".toUpperCase();
        orExprs.add(
            EntityCondition.makeCondition(
                EntityFunction.UPPER_FIELD("subject"), EntityOperator.LIKE, cntctUsSbjStr));
        orExprs.add(
            EntityCondition.makeCondition(
                EntityFunction.UPPER_FIELD("subject"), EntityOperator.LIKE, reqCatSbjStr));
        conditionExprs.add(EntityCondition.makeCondition(orExprs, EntityOperator.OR));
        EntityConditionList whereEntityCondition =
            EntityCondition.makeCondition(conditionExprs, EntityOperator.AND);

        try {
          boolean beganTx = TransactionUtil.begin();
          try {
            values =
                delegator.find(
                    entityName,
                    whereEntityCondition,
                    null,
                    null,
                    UtilMisc.toList("communicationEventId"),
                    null);
          } catch (Exception entityEx) {
            results.add("error while process retrieving from db");
          }

          GenericValue value = (GenericValue) values.next();
          if (value != null) {
            PrintWriter writer =
                new PrintWriter(
                    new BufferedWriter(
                        new OutputStreamWriter(
                            new FileOutputStream(new File(outdir, entityName + ".xml")), "UTF-8")));
            writer.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
            writer.println("<entity-engine-xml>");

            do {
              value.writeXmlText(writer, "");
              numberWritten++;
              if (numberWritten % 500 == 0) {
                TransactionUtil.commit(beganTx);
                beganTx = TransactionUtil.begin();
              }
            } while ((value = (GenericValue) values.next()) != null);
            writer.println("</entity-engine-xml>");
            writer.close();
          } else {
            results.add("No record found");
          }
          values.close();
          TransactionUtil.commit(beganTx);

          File file = new File(outdir, entityName + ".xls");
          WorkbookSettings wbSettings = new WorkbookSettings();
          wbSettings.setLocale(new Locale("en", "EN"));
          workbook = Workbook.createWorkbook(file, wbSettings);
          workbook.createSheet(entityName, 0);
          WritableSheet excelSheet = workbook.getSheet(0);
          int row = 0;

          WritableFont headerFont = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
          WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
          CellView cv = new CellView();
          cv.setAutosize(true);

          Label label = new Label(0, row, "ID", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(0, cv);
          label = new Label(1, row, "Date", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(1, cv);
          label = new Label(2, row, "Subject", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(2, cv);
          label = new Label(3, row, "Email Address", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(3, cv);
          label = new Label(4, row, "First Name", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(4, cv);
          label = new Label(5, row, "Last Name", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(5, cv);
          label = new Label(6, row, "Contact Email Address", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(6, cv);
          label = new Label(7, row, "Reason For Contact ", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(7, cv);
          label = new Label(8, row, "Address1", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(8, cv);
          label = new Label(9, row, "Address2", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(9, cv);
          label = new Label(10, row, "City State Zip", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(10, cv);
          label = new Label(11, row, "Phone", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(11, cv);
          label = new Label(12, row, "Order #", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(12, cv);
          label = new Label(13, row, "Comment", headerFormat);
          excelSheet.addCell(label);
          excelSheet.setColumnView(13, cv);
          // label  = new Label(12, row, "Email Content Body", headerFormat);
          // excelSheet.addCell(label);
          // excelSheet.setColumnView(12, cv);
          Debug.logInfo("wrote header", module);
          row++;

          WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
          WritableCellFormat times = new WritableCellFormat(times10pt);

          String XmlFilePath = new File(outdir, entityName + ".xml").getAbsolutePath();
          List<Map<Object, Object>> listMaps = getListMapsFromXmlFile(XmlFilePath);
          for (Map entityDataRow : listMaps) {
            String fname = "",
                lname = "",
                contactEmail = "",
                reasonOfContact = "",
                emailContentAddress = "",
                address1 = "",
                address2 = "",
                cityStateZip = "",
                phone = "",
                comment = "",
                orderNum = "";
            commEventId = entityDataRow.get("communicationEventId").toString();
            contactEmail = entityDataRow.get("fromString").toString();
            // Debug.logInfo("Extracting column value for commEventId:" + commEventId, module);
            label = new Label(0, row, entityDataRow.get("communicationEventId").toString(), times);
            excelSheet.addCell(label);

            label = new Label(1, row, entityDataRow.get("createdTxStamp").toString(), times);
            excelSheet.addCell(label);

            label = new Label(2, row, entityDataRow.get("subject").toString(), times);
            excelSheet.addCell(label);

            label = new Label(3, row, entityDataRow.get("toString").toString(), times);
            excelSheet.addCell(label);

            String emailContent = entityDataRow.get("content").toString();
            String emailContentBody =
                emailContent.substring(
                    emailContent.indexOf("<body>") + "<body>".length(),
                    emailContent.indexOf("</body>"));
            emailContentBody =
                emailContentBody
                    .replaceAll("&#58;()", "")
                    .replaceAll("&nbsp;", " ")
                    .replaceAll("&#64;()", "@")
                    .replaceAll(",", " ")
                    .replaceAll(";", " ");

            try {
              if (emailContentBody.indexOf("First Name:") > -1) {
                int idxStart =
                    emailContentBody.indexOf("First Name:") + "First Name:</td>".length();
                fname =
                    emailContentBody.substring(
                        idxStart, emailContentBody.indexOf("</td>", idxStart));
                fname = fname.substring(fname.lastIndexOf(">") + 1);
                fname.trim();

              } else {
                if (emailContentBody.indexOf(">First Name") > -1) {
                  int idxStart = emailContentBody.indexOf(">First Name") + ">First Name".length();
                  int idxEnd =
                      emailContentBody.indexOf("</p>", emailContentBody.indexOf(">First Name"));
                  fname = emailContentBody.substring(idxStart, idxEnd);
                  if (fname.indexOf("<span class=\"data\">") > -1) {
                    fname = fname.substring(fname.indexOf("\">") + 2, fname.indexOf("</span>"));
                  } else {
                    if (fname.indexOf("\"infoValue\">") > -1) {
                      idxStart = fname.indexOf("\"infoValue\">") + "\"infoValue\">".length();
                      fname = fname.substring(idxStart, fname.indexOf("</div>", idxStart));

                    } else if (fname.indexOf(">") > -1) {
                      fname = fname.substring(fname.lastIndexOf(">") + 1);
                    }
                  }
                  fname.trim();
                }
              }
              if (emailContentBody.indexOf("Last Name:") > -1) {
                int idxStart = emailContentBody.indexOf("Last Name:") + "Last Name:</td>".length();
                lname =
                    emailContentBody.substring(
                        idxStart, emailContentBody.indexOf("</td>", idxStart));
                lname = lname.substring(lname.lastIndexOf(">") + 1);
                lname.trim();

              } else {
                if (emailContentBody.indexOf(">Last Name") > -1) {
                  int idxStart = emailContentBody.indexOf(">Last Name") + ">Last Name".length();
                  int idxEnd =
                      emailContentBody.indexOf("</p>", emailContentBody.indexOf(">Last Name"));
                  lname = emailContentBody.substring(idxStart, idxEnd);
                  if (lname.indexOf("<span class=\"data\">") > -1) {
                    lname = lname.substring(lname.indexOf("\">") + 2, lname.indexOf("</span>"));
                  } else {
                    if (lname.indexOf("\"infoValue\">") > -1) {
                      idxStart = lname.indexOf("\"infoValue\">") + "\"infoValue\">".length();
                      lname = lname.substring(idxStart, lname.indexOf("</div>", idxStart));

                    } else if (lname.indexOf(">") > -1) {
                      lname = lname.substring(lname.lastIndexOf(">") + 1);
                    }
                  }
                  lname.trim();
                }
              }
              if (UtilValidate.isEmpty(contactEmail)) {
                if (emailContentBody.indexOf(">Email Address") > -1) {
                  contactEmail =
                      emailContentBody.substring(
                          emailContentBody.indexOf(">Email Address") + ">Email Address".length(),
                          emailContentBody.indexOf(
                              "</p>", emailContentBody.indexOf(">Email Address")));
                  if (contactEmail.indexOf(">") > -1) {
                    contactEmail = contactEmail.substring(contactEmail.lastIndexOf(">") + 1);
                  }
                }
              }
              if (emailContentBody.indexOf("Reason For Contact:") > -1) {
                int idxStart =
                    emailContentBody.indexOf("Reason For Contact:")
                        + "Reason For Contact:</td>".length();
                reasonOfContact =
                    emailContentBody.substring(
                        idxStart, emailContentBody.indexOf("</td>", idxStart));
                reasonOfContact = reasonOfContact.substring(reasonOfContact.lastIndexOf(">") + 1);
                reasonOfContact.trim();

              } else {
                if (emailContentBody.indexOf(">Reason For Contact") > -1) {
                  int idxStart =
                      emailContentBody.indexOf(">Reason For Contact")
                          + ">Reason For Contact".length();
                  int idxEnd =
                      emailContentBody.indexOf(
                          "</p>", emailContentBody.indexOf(">Reason For Contact"));
                  reasonOfContact = emailContentBody.substring(idxStart, idxEnd);
                  if (reasonOfContact.indexOf("<span class=\"data\">") > -1) {
                    reasonOfContact =
                        reasonOfContact.substring(
                            reasonOfContact.indexOf("\">") + 2, reasonOfContact.indexOf("</span>"));
                  } else {
                    if (reasonOfContact.indexOf("\"infoValue\">") > -1) {
                      idxStart =
                          reasonOfContact.indexOf("\"infoValue\">") + "\"infoValue\">".length();
                      reasonOfContact =
                          reasonOfContact.substring(
                              idxStart, reasonOfContact.indexOf("</div>", idxStart));

                    } else if (reasonOfContact.indexOf(">") > -1) {
                      reasonOfContact =
                          reasonOfContact.substring(reasonOfContact.lastIndexOf(">") + 1);
                    }
                  }
                  reasonOfContact.trim();
                }
              }

              if (emailContentBody.indexOf(">Address") > -1) {
                emailContentAddress =
                    emailContentBody.substring(
                        emailContentBody.indexOf(">Address") + ">Address".length(),
                        emailContentBody.indexOf(">Contact Phone"));
              }
              if (emailContentAddress.indexOf("<p>") > -1) {
                address1 =
                    emailContentAddress.substring(
                        emailContentAddress.indexOf("<p>") + "<p>".length(),
                        emailContentAddress.indexOf("</p>", emailContentAddress.indexOf("<p>")));
                emailContentAddress =
                    emailContentAddress.replaceFirst(
                        emailContentAddress.substring(
                            emailContentAddress.indexOf("<p>"),
                            emailContentAddress.indexOf(
                                "</p>", emailContentAddress.indexOf("<p>"))),
                        "");
                if (address1.indexOf(">") > -1) {
                  address1 = address1.substring(address1.lastIndexOf(">") + 1);
                }
              }
              if (emailContentAddress.indexOf("<p>") > -1) {
                address2 =
                    emailContentAddress.substring(
                        emailContentAddress.indexOf("<p>") + "<p>".length(),
                        emailContentAddress.indexOf("</p>", emailContentAddress.indexOf("<p>")));
                emailContentAddress =
                    emailContentAddress.replaceFirst(
                        emailContentAddress.substring(
                            emailContentAddress.indexOf("<p>"),
                            emailContentAddress.indexOf(
                                "</p>", emailContentAddress.indexOf("<p>"))),
                        "");
                if (address2.indexOf(">") > -1) {
                  address2 = address2.substring(address2.lastIndexOf(">") + 1);
                }
              }
              if (emailContentAddress.indexOf("<p>") > -1) {
                cityStateZip =
                    emailContentAddress.substring(
                        emailContentAddress.indexOf("<p>") + "<p>".length(),
                        emailContentAddress.indexOf("</p>", emailContentAddress.indexOf("<p>")));
                if (cityStateZip.indexOf(">") > -1) {
                  cityStateZip = cityStateZip.substring(cityStateZip.lastIndexOf(">") + 1);
                }
              }

              if (emailContentBody.indexOf("Contact Phone:") > -1) {
                int idxStart =
                    emailContentBody.indexOf("Contact Phone:") + "Contact Phone:</td>".length();
                phone =
                    emailContentBody.substring(
                        idxStart, emailContentBody.indexOf("</td>", idxStart));
                phone = phone.substring(phone.lastIndexOf(">") + 1);
                phone.trim();

              } else {
                if (emailContentBody.indexOf(">Contact Phone") > -1) {
                  int idxStart =
                      emailContentBody.indexOf(">Contact Phone") + ">Contact Phone".length();
                  int idxEnd =
                      emailContentBody.indexOf("</p>", emailContentBody.indexOf(">Contact Phone"));
                  phone = emailContentBody.substring(idxStart, idxEnd);
                  if (phone.indexOf("<span class=\"data\">") > -1) {
                    phone = phone.substring(phone.indexOf("\">") + 2, phone.indexOf("</span>"));
                  } else {
                    if (phone.indexOf("\"infoValue\">") > -1) {
                      idxStart = phone.indexOf("\"infoValue\">") + "\"infoValue\">".length();
                      phone = phone.substring(idxStart, phone.indexOf("</div>", idxStart));

                    } else if (phone.indexOf(">") > -1) {
                      phone = phone.substring(phone.lastIndexOf(">") + 1);
                    }
                  }
                  phone.trim();
                }
              }
              if (emailContentBody.indexOf("Order #:") > -1) {
                int idxStart = emailContentBody.indexOf("Order #:") + "Order #:</td>".length();
                orderNum =
                    emailContentBody.substring(
                        idxStart, emailContentBody.indexOf("</td>", idxStart));
                orderNum = orderNum.substring(orderNum.lastIndexOf(">") + 1);
                orderNum.trim();

              } else {
                if (emailContentBody.indexOf(">Order #") > -1) {
                  int idxStart = emailContentBody.indexOf(">Order #") + ">Order #".length();
                  int idxEnd =
                      emailContentBody.indexOf("</p>", emailContentBody.indexOf(">Order #"));
                  orderNum = emailContentBody.substring(idxStart, idxEnd);
                  if (orderNum.indexOf("<span class=\"data\">") > -1) {
                    orderNum =
                        orderNum.substring(
                            orderNum.indexOf("\">") + 2, orderNum.indexOf("</span>"));
                  } else {
                    if (orderNum.indexOf("\"infoValue\">") > -1) {
                      idxStart = orderNum.indexOf("\"infoValue\">") + "\"infoValue\">".length();
                      orderNum = orderNum.substring(idxStart, orderNum.indexOf("</div>", idxStart));

                    } else if (orderNum.indexOf(">") > -1) {
                      orderNum = orderNum.substring(orderNum.lastIndexOf(">") + 1);
                    }
                  }
                  orderNum.trim();
                }
              }

              if (emailContentBody.indexOf("Comment:") > -1) {
                int idxStart = emailContentBody.indexOf("Comment:") + "Comment:</td>".length();
                comment =
                    emailContentBody.substring(
                        idxStart, emailContentBody.indexOf("</td>", idxStart));
                comment = comment.substring(comment.lastIndexOf(">") + 1);
                comment.trim();

              } else {
                if (emailContentBody.indexOf(">Comment") > -1) {
                  int idxStart = emailContentBody.indexOf(">Comment") + ">Comment".length();
                  int idxEnd = emailContentBody.indexOf("</p>", idxStart);
                  comment = emailContentBody.substring(idxStart, idxEnd);
                  if (comment.indexOf("<span class=\"data\">") > -1) {
                    comment =
                        comment.substring(comment.indexOf("\">") + 2, comment.indexOf("</span>"));
                  } else {
                    if (comment.indexOf("\"infoValue\">") > -1) {
                      idxStart = comment.indexOf("\"infoValue\">") + "\"infoValue\">".length();
                      comment = comment.substring(idxStart, comment.indexOf("</div>", idxStart));

                    } else if (comment.indexOf(">") > -1) {
                      comment = comment.substring(comment.lastIndexOf(">") + 1);
                    }
                  }
                }
              }

            } catch (IndexOutOfBoundsException iobex) {
              Debug.logError(
                  iobex, "Error extracting column value for commEventId:" + commEventId, module);
              return ServiceUtil.returnError(
                  "Error extracting column value for commEventId:" + commEventId);

            } catch (Exception ex) {
              Debug.logError(ex, "Error commEventId:" + commEventId, module);
              return ServiceUtil.returnError(
                  "Error extracting column value for commEventId:" + commEventId);
            }
            label = new Label(4, row, fname.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(5, row, lname.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(6, row, contactEmail.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(7, row, reasonOfContact.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(8, row, address1.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(9, row, address2.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(10, row, cityStateZip.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(11, row, phone.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(12, row, orderNum.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            label = new Label(13, row, comment.replaceAll("\n", "").trim(), times);
            excelSheet.addCell(label);

            // label  = new Label(12, row, emailContentBody, times);
            // excelSheet.addCell(label);
            row++;
          }
          Debug.logInfo("Finished loop", module);

          workbook.write();
          workbook.close();
          new File(outdir, entityName + ".xml").delete();
        } catch (Exception ex) {
          if (values != null) {
            try {
              values.close();
            } catch (Exception exc) {
              // Debug.warning();
            }
          }
          if (workbook != null) {
            try {
              workbook.close();
            } catch (Exception exc) {
              // Debug.warning();
            }
          }
        }
      } else {
        results.add("Path not found or no write access.");
        return ServiceUtil.returnError("Path not found or no write access.");
      }
    } else {
      results.add("No path specified, doing nothing.");
      return ServiceUtil.returnError("No path specified, doing nothing.");
    }
    // send the notification
    Debug.logInfo("Finished job", module);
    Map<String, Object> resp = UtilMisc.<String, Object>toMap("results", results);
    return resp;
  }