Beispiel #1
0
  public void format() {

    wb = new XSSFWorkbook();

    Map styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 768);
    sheet.setColumnWidth(1, 768);
    sheet.setColumnWidth(2, 2816);
    sheet.setColumnWidth(3, 3584);
    sheet.setColumnWidth(4, 3584);
    sheet.setColumnWidth(5, 3584);
    sheet.setColumnWidth(6, 3584);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35F);
    for (int i = 1; i <= 7; i++)
      titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));

    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple");
  }
Beispiel #2
0
  private void printSetup(Sheet oldSheet, Sheet newSheet) {
    // print setup
    PrintSetup oldPS = oldSheet.getPrintSetup();
    PrintSetup newPS = newSheet.getPrintSetup();

    newPS.setPaperSize(oldPS.getPaperSize());
    newPS.setScale(oldPS.getScale());
    newPS.setPageStart(oldPS.getPageStart());
    newPS.setFitWidth(oldPS.getFitWidth());
    newPS.setFitHeight(oldPS.getFitHeight());
    newPS.setLeftToRight(oldPS.getLeftToRight());
    newPS.setLandscape(oldPS.getLandscape());
    newPS.setValidSettings(oldPS.getValidSettings());
    newPS.setNoColor(oldPS.getNoColor());
    newPS.setDraft(oldPS.getDraft());
    newPS.setNotes(oldPS.getNotes());
    newPS.setNoOrientation(oldPS.getNoOrientation());
    newPS.setUsePage(oldPS.getUsePage());
    newPS.setHResolution(oldPS.getHResolution());
    newPS.setVResolution(oldPS.getVResolution());
    newPS.setHeaderMargin(oldPS.getHeaderMargin());
    newPS.setFooterMargin(oldPS.getFooterMargin());
    newPS.setCopies(oldPS.getCopies());

    newSheet.setRightToLeft(oldSheet.isRightToLeft());
    newSheet.setHorizontallyCenter(oldSheet.getHorizontallyCenter());
    newSheet.setVerticallyCenter(oldSheet.getVerticallyCenter());
    newSheet.setAutobreaks(oldSheet.getAutobreaks());
    newSheet.setDisplayZeros(oldSheet.isDisplayZeros());
    newSheet.setFitToPage(oldSheet.getFitToPage());
    newSheet.setAutobreaks(oldSheet.getAutobreaks());
    newSheet.setPrintGridlines(oldSheet.isPrintGridlines());

    newSheet.getHeader().setCenter(oldSheet.getHeader().getCenter());
    newSheet.getHeader().setLeft(oldSheet.getHeader().getLeft());
    newSheet.getHeader().setRight(oldSheet.getHeader().getRight());

    newSheet.getFooter().setCenter(oldSheet.getFooter().getCenter());
    newSheet.getFooter().setLeft(oldSheet.getFooter().getLeft());
    newSheet.getFooter().setRight(oldSheet.getFooter().getRight());

    newSheet.setMargin(Sheet.LeftMargin, oldSheet.getMargin(Sheet.LeftMargin));
    newSheet.setMargin(Sheet.RightMargin, oldSheet.getMargin(Sheet.RightMargin));
    newSheet.setMargin(Sheet.HeaderMargin, oldSheet.getMargin(Sheet.HeaderMargin));
    newSheet.setMargin(Sheet.FooterMargin, oldSheet.getMargin(Sheet.FooterMargin));
    newSheet.setMargin(Sheet.BottomMargin, oldSheet.getMargin(Sheet.BottomMargin));
    newSheet.setMargin(Sheet.TopMargin, oldSheet.getMargin(Sheet.TopMargin));
  }
Beispiel #3
0
  public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
    else wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
      headerCell = headerRow.createCell(i);
      headerCell.setCellValue(titles[i]);
      headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
      Row row = sheet.createRow(rownum++);
      for (int j = 0; j < titles.length; j++) {
        Cell cell = row.createCell(j);
        if (j == 9) {
          // the 10th cell contains sum over week days, e.g. SUM(C3:I3)
          String ref = "C" + rownum + ":I" + rownum;
          cell.setCellFormula("SUM(" + ref + ")");
          cell.setCellStyle(styles.get("formula"));
        } else if (j == 11) {
          cell.setCellFormula("J" + rownum + "-K" + rownum);
          cell.setCellStyle(styles.get("formula"));
        } else {
          cell.setCellStyle(styles.get("cell"));
        }
      }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
      cell = sumRow.createCell(j);
      String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
      cell.setCellFormula("SUM(" + ref + ")");
      if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
      else cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
      Row row = sheet.getRow(2 + i);
      for (int j = 0; j < sample_data[i].length; j++) {
        if (sample_data[i][j] == null) continue;

        if (sample_data[i][j] instanceof String) {
          row.getCell(j).setCellValue((String) sample_data[i][j]);
        } else {
          row.getCell(j).setCellValue((Double) sample_data[i][j]);
        }
      }
    }

    // finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
      sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook) file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
  }
  // 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;
  }
  /**
   * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
   *
   * @param request servlet request
   * @param response servlet response
   * @throws ServletException if a servlet-specific error occurs
   * @throws IOException if an I/O error occurs
   */
  protected void processRequest(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    HttpSession ss = request.getSession();
    Account ac = (Account) ss.getAttribute("ac");
    int cId = Integer.parseInt((Long) ss.getAttribute("cId") + "");
    Course c = Course.getCourseByID(cId);

    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("scoresheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Score sheet of " + c.getName() + " course");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

    List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore");
    int rownum = 2;
    int cellcount = 1;
    Row sumRow = sheet.createRow(rownum);
    sumRow.setHeightInPoints(55);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellValue("Student name");
    cell.setCellStyle(styles.get("header"));
    int countback = listStudentScore.get(0).getListStudentScore().size();
    int maxScore = 0;
    for (int i = countback - 1; i >= 0; i--) {
      cell = sumRow.createCell(cellcount);
      UserScore u = listStudentScore.get(0).getListStudentScore().get(i);
      cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")");
      cell.setCellStyle(styles.get("header"));
      cellcount++;
      maxScore += u.getFull_mark();
    }
    cell = sumRow.createCell(cellcount);
    cell.setCellValue("Total (" + maxScore + ")");
    cell.setCellStyle(styles.get("header"));
    rownum++;

    for (Account account : listStudentScore) {
      sumRow = sheet.createRow(rownum);
      sumRow.setHeightInPoints(35);
      cell = sumRow.createCell(0);
      cell.setCellValue(account.getFirstname() + " " + account.getLastname());
      int j = 1;
      for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) {
        UserScore usc = (UserScore) account.getListStudentScore().get(i);
        cell = sumRow.createCell(j);
        Assignment a = null;
        if (usc.getAss_type().equalsIgnoreCase("web")) {
          a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id());
          String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a);
          if (status.equalsIgnoreCase("ontime")
              || status.equalsIgnoreCase("hurryup")
              || status.equalsIgnoreCase("late")) {
            cell.setCellValue(usc.getStof().getScore());
          } else {
            status = Assignment.calculateTime(a);
            if (status.equalsIgnoreCase("miss")) {
              cell.setCellValue(usc.getStof().getScore());
            } else {
              cell.setCellValue("-");
            }
          }

        } else if (usc.getAss_type().equalsIgnoreCase("file")) {
          a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id());
          String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a);
          if (status.equalsIgnoreCase("ontime")
              || status.equalsIgnoreCase("hurryup")
              || status.equalsIgnoreCase("late")) {
            cell.setCellValue(usc.getStf().getScore());
          } else {
            status = Assignment.calculateTime(a);
            if (status.equalsIgnoreCase("miss")) {
              cell.setCellValue(usc.getStf().getScore());
            } else {
              cell.setCellValue("-");
            }
          }
        }
        j++;
      }
      cell = sumRow.createCell(j);
      int lastcol = account.getListStudentScore().size();

      // calculate column
      int dv = lastcol / 26;
      String coltmp = "";
      for (int i = 0; i < dv; i++) {
        coltmp += "A";
      }
      coltmp += (char) ('A' + (lastcol - (dv * 26)));
      System.out.println(coltmp);
      //

      String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1);
      System.out.println(ref);
      cell.setCellFormula("SUM(" + ref + ")");
      rownum++;
    }

    // Write the output to a file
    String filename = "scoresheet_" + c.getName() + ".xlsx";
    String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename;
    //        String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();

    response.sendRedirect("file/scoresheet/" + filename);

    //
    //        Workbook wb = new XSSFWorkbook();
    //        Sheet sheet = wb.createSheet("scoresheet");
    //        PrintSetup printSetup = sheet.getPrintSetup();
    //        printSetup.setLandscape(true);
    //        sheet.setFitToPage(true);
    //        sheet.setHorizontallyCenter(true);
    //
    //        //title row
    //        Row titleRow = sheet.createRow(0);
    //        titleRow.setHeightInPoints(45);
    //        Cell titleCell = titleRow.createCell(0);
    //        titleCell.setCellValue("Score sheet of " + "...." + " course");
    //        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1"));
    //
    //        //row with totals below
    //        int rownum = 2;
    //        Row sumRow = sheet.createRow(rownum);
    //       sumRow.setHeightInPoints(35);
    //        Cell cell;
    //        cell = sumRow.createCell(0);
    //        cell.setCellValue("Name:");
    //
    //        for (int j = 1; j < 12; j++) {
    //            cell = sumRow.createCell(j);
    //            String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
    //            cell.setCellFormula("SUM(" + ref + ")");
    //        }
    //
    //        // Write the output to a file
    //        String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx";
    //        FileOutputStream out = new FileOutputStream(file);
    //        wb.write(out);
    //        out.close();
  }