private void buildHeader() {
   if (bean.getReportLayout().getPageHeaderBand().getColumnCount() == 0) {
     return;
   }
   try {
     printPageHeaderBand();
     Header header = xlsSheet.getHeader();
     header.setCenter(headerS.toString());
   } catch (QueryException ex) {
     ex.printStackTrace();
   } finally {
     headerS = new StringBuilder();
   }
 }
  // Define A method for Creating Excel File
  public String createExcel(
      String titleString,
      String sheetString,
      String subTitleString,
      List data,
      String[] headerTitles,
      String titleKey,
      String filePath) {

    String file = null;
    Workbook wb;
    int col = 0;
    try {
      // check Header Title
      if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;

      wb = (Workbook) new HSSFWorkbook();
      // Hear we are getting whole property
      List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
      Map<String, CellStyle> styles = createStyles(wb);
      Sheet sheet = wb.createSheet(sheetString);
      PrintSetup printSetup = sheet.getPrintSetup();
      printSetup.setLandscape(true);
      sheet.setFitToPage(true);
      sheet.setHorizontallyCenter(true);

      Header header = sheet.getHeader();
      header.setCenter("Center Header");
      header.setLeft("Left Header");
      header.setRight("Right Footer");
      Footer footer = sheet.getFooter();
      footer.setCenter("center footer");
      footer.setLeft("left footer");
      footer.setRight("right footer");

      // Title Row....
      Row titleRow = sheet.createRow(0);
      titleRow.setHeightInPoints(20);
      Cell titleCell = titleRow.createCell(0);
      titleCell.setCellValue(titleString);
      titleCell.setCellStyle(styles.get("title"));
      sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));

      // Sub Title Row.....
      // System.out.println("Sub Title String >>>>>>"+subTitleString);
      Row headerRow = null;
      if (subTitleString != "") {
        Row subTitleRow = sheet.createRow(1);
        subTitleRow.setHeightInPoints(18);
        Cell subTitleCell = subTitleRow.createCell(0);
        subTitleCell.setCellValue(subTitleString);
        subTitleCell.setCellStyle(styles.get("subTitle"));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col - 1));
        headerRow = sheet.createRow(2);
        headerRow.setHeightInPoints(15);
        //
      } else {
        headerRow = sheet.createRow(1);
        headerRow.setHeightInPoints(15);
      }
      Cell headerCell = null;
      if (headerTitles != null) {
        for (ConfigurationUtilBean cell : titleMap) {
          int titleIndex = 0;
          for (int i = 0; i < headerTitles.length; i++) {
            if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
              headerCell = headerRow.createCell(titleIndex);
              headerCell.setCellValue(cell.getValue());
              headerCell.setCellStyle(styles.get("header"));
            }
            titleIndex++;
          }
        }
      }
      Row dataRow = null;
      Cell dataCell = null;

      int rowIndex = 2;
      /* List Iteration text */
      try {
        if (data != null && data.size() > 0) {
          for (Iterator it = data.iterator(); it.hasNext(); ) {
            Object[] obdata = (Object[]) it.next();
            dataRow = sheet.createRow(rowIndex);
            for (int cellIndex = 0; cellIndex < headerTitles.length; cellIndex++) {
              dataCell = dataRow.createCell(cellIndex);

              if (obdata[cellIndex] != null && !obdata[cellIndex].toString().equalsIgnoreCase("")) {

                dataCell.setCellValue(obdata[cellIndex].toString());
              } else {
                dataCell.setCellValue("NA");
              }
            }

            rowIndex++;
          }
        }
      } catch (Exception e) {
        // TODO: handle exception
      }

      for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
        sheet.autoSizeColumn(titleIndex); // adjust width of the column

      file =
          filePath
              + File.separator
              + "OpportunityReportDetail_"
              + DateUtil.getCurrentDateIndianFormat()
              + (DateUtil.getCurrentTimeHourMin()).replaceAll(":", "-")
              + ".xls";

      if (wb instanceof XSSFWorkbook) file += "x";
      FileOutputStream out = new FileOutputStream(file);
      wb.write(out);
      out.close();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {

    }
    return file;
  }
  // Define A method for Creating Excel File
  public String createExcelformate(
      String titleString,
      String sheetString,
      String[] headerTitles,
      String titleKey,
      String filePath) {

    String file = null;
    Workbook wb;
    int col = 0;
    try {
      // check Header Title
      if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length;

      wb = (Workbook) new HSSFWorkbook();
      // Hear we are getting whole property
      List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey);
      Map<String, CellStyle> styles = createStyles(wb);
      Sheet sheet = wb.createSheet(sheetString);
      PrintSetup printSetup = sheet.getPrintSetup();
      printSetup.setLandscape(true);
      sheet.setFitToPage(true);
      sheet.setHorizontallyCenter(true);

      Header header = sheet.getHeader();
      header.setCenter("Center Header");
      header.setLeft("Left Header");
      header.setRight("Right Footer");
      Footer footer = sheet.getFooter();
      footer.setCenter("center footer");
      footer.setLeft("left footer");
      footer.setRight("right footer");

      // Title Row....
      Row titleRow = sheet.createRow(0);
      titleRow.setHeightInPoints(20);
      Cell titleCell = titleRow.createCell(0);
      titleCell.setCellValue(titleString);
      titleCell.setCellStyle(styles.get("title"));
      sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1));

      //
      Row headerRow = sheet.createRow(1);
      headerRow.setHeightInPoints(15);
      Cell headerCell = null;
      if (headerTitles != null) {
        for (ConfigurationUtilBean cell : titleMap) {
          int titleIndex = 0;
          for (int i = 0; i < headerTitles.length; i++) {
            if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) {
              headerCell = headerRow.createCell(titleIndex);
              headerCell.setCellValue(cell.getValue());
              headerCell.setCellStyle(styles.get("header"));
            }
            titleIndex++;
          }
        }
      }

      for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++)
        sheet.autoSizeColumn(titleIndex); // adjust width of the column

      file =
          filePath
              + File.separator
              + "ContactReport"
              + DateUtil.getCurrentDateIndianFormat()
              + (DateUtil.getCurrentTime()).replaceAll(":", "-")
              + ".xls";

      if (wb instanceof XSSFWorkbook) file += "x";
      FileOutputStream out = new FileOutputStream(file);
      wb.write(out);
      out.close();

    } catch (Exception e) {
      e.printStackTrace();
    } finally {

    }
    return file;
  }
Example #4
0
File: Test1.java Project: naily/iph
  @Test
  public void createex() {
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillPattern(CellStyle.NO_FILL);

    Sheet sheet = wb.createSheet("数据日志");
    Sheet sheet2 = wb.createSheet("second sheet");
    String safeName =
        WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
    Sheet sheet3 = wb.createSheet(safeName);

    Header header = sheet.getHeader();
    header.setCenter("Center Header");
    header.setLeft("Left Header");
    header.setRight(
        HSSFHeader.font("Stencil-Normal", "Italic")
            + HSSFHeader.fontSize((short) 16)
            + "Right w/ Stencil-Normal Italic font and size 16");

    sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
    sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
    sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
    sheet.autoSizeColumn(2); // 调整第四列宽度

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);
    cell.setCellValue("ID");
    cell.setCellStyle(cellStyle);

    // Or do it on one line.
    row.createCell(1).setCellValue("表名");
    row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
    row.createCell(3).setCellValue("操作日期");
    row.createCell(4).setCellValue("操作者");

    List<Log> list = this.logList();
    Row rw;
    for (int i = 2; i < list.size(); i++) {
      Log log = list.get(i);
      rw = sheet.createRow((short) i);

      rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
      rw.createCell(1).setCellValue(log.getDataTable());
      rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
      rw.createCell(3)
          .setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
      rw.createCell(4).setCellValue(log.getAdminId());
    }

    String bp = this.getClass().getResource("/").toString();
    try {
      System.out.println(bp);
      File f = new File("workbook.xls");
      FileOutputStream fileOut = new FileOutputStream(f);
      wb.write(fileOut);
      fileOut.close();
    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }