public void testModifyArrayCells_mergeCells() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    assertEquals(0, sheet.getNumMergedRegions());

    // single-cell array formulas behave just like normal cells
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    sheet.addMergedRegion(CellRangeAddress.valueOf("B5:C6"));
    // we are still an array formula
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertTrue(scell.isPartOfArrayFormulaGroup());
    assertEquals(1, sheet.getNumMergedRegions());

    // we cannot merge cells included in an array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    CellRangeAddress cra = CellRangeAddress.valueOf("C1:C3");
    try {
      sheet.addMergedRegion(cra);
      fail("expected exception");
    } catch (IllegalStateException e) {
      String msg =
          "The range "
              + cra.formatAsString()
              + " intersects with a multi-cell array formula. You cannot merge cells of an array.";
      assertEquals(msg, e.getMessage());
    }
    // the number of merged regions remains the same
    assertEquals(1, sheet.getNumMergedRegions());
  }
  /** Set multi-cell array formula */
  public final void testSetArrayFormula_multiCell() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // multi-cell formula
    // rows 3-5 don't exist yet
    assertNull(sheet.getRow(3));
    assertNull(sheet.getRow(4));
    assertNull(sheet.getRow(5));

    CellRangeAddress range = CellRangeAddress.valueOf("C4:C6");
    Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range).getFlattenedCells();
    assertEquals(3, cells.length);

    // sheet.setArrayFormula creates rows and cells for the designated range
    assertSame(cells[0], sheet.getRow(3).getCell(2));
    assertSame(cells[1], sheet.getRow(4).getCell(2));
    assertSame(cells[2], sheet.getRow(5).getCell(2));

    for (Cell acell : cells) {
      assertTrue(acell.isPartOfArrayFormulaGroup());
      assertEquals(Cell.CELL_TYPE_FORMULA, acell.getCellType());
      assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula());
      // retrieve the range and check it is the same
      assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString());
    }
  }
  public void testModifyArrayCells_removeRow() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells
    CellRangeAddress cra = CellRangeAddress.valueOf("B5");
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());

    Row srow = scell.getRow();
    assertSame(srow, sheet.getRow(cra.getFirstRow()));
    sheet.removeRow(srow);
    assertNull(sheet.getRow(cra.getFirstRow()));

    // re-create the removed row and cell
    scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
    assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
    assertFalse(scell.isPartOfArrayFormulaGroup());

    // we cannot remove rows with cells included in a multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      int columnIndex = mcell.getColumnIndex();
      Row mrow = mcell.getRow();
      try {
        sheet.removeRow(mrow);
        fail("expected exception");
      } catch (IllegalStateException e) {
        String msg =
            "Row[rownum="
                + mrow.getRowNum()
                + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Row.removeCell leaves the row
      // in the state that it was in prior to the invocation
      assertSame(mrow, sheet.getRow(mrow.getRowNum()));
      assertSame(mcell, mrow.getCell(columnIndex));
      assertTrue(mcell.isPartOfArrayFormulaGroup());
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }
  }
  public void testModifyArrayCells_setCellFormula() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertTrue(scell.isPartOfArrayFormulaGroup());
    scell.setCellFormula("SUM(A4,A6)");
    // we are now a normal formula cell
    assertEquals("SUM(A4,A6)", scell.getCellFormula());
    assertFalse(scell.isPartOfArrayFormulaGroup());
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    // check that setting formula result works
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellValue(33.0);
    assertEquals(33.0, scell.getNumericCellValue());

    // multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      // we cannot set individual formulas for cells included in an array formula
      try {
        assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
        mcell.setCellFormula("A1+A2");
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Cell.setCellFormula leaves the cell
      // in the state that it was in prior to the invocation
      assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
      assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
  }
  public void testModifyArrayCells_shiftRows() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells - we can change the cell type
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
    sheet.shiftRows(0, 0, 1);
    sheet.shiftRows(0, 1, 1);

    // we cannot set individual formulas for cells included in an array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));

    try {
      sheet.shiftRows(0, 0, 1);
      fail("expected exception");
    } catch (IllegalStateException e) {
      String msg =
          "Row[rownum=0] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
      assertEquals(msg, e.getMessage());
    }
    /*
     TODO: enable shifting the whole array

    sheet.shiftRows(0, 2, 1);
    //the array C1:C3 is now C2:C4
    CellRangeAddress cra = CellRangeAddress.valueOf("C2:C4");
    for(Cell mcell : mrange){
        //TODO define equals and hashcode for CellRangeAddress
        assertEquals(cra.formatAsString(), mcell.getArrayFormulaRange().formatAsString());
        assertEquals("A2:A4*B2:B4", mcell.getCellFormula());
        assertTrue(mcell.isPartOfArrayFormulaGroup());
        assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }

    */
  }
  /** Test that when reading a workbook from input stream, array formulas are recognized */
  public final void testReadArrayFormula() {
    Cell[] cells;

    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet1 = workbook.createSheet();
    cells =
        sheet1
            .setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6"))
            .getFlattenedCells();
    assertEquals(3, cells.length);

    cells =
        sheet1
            .setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6"))
            .getFlattenedCells();
    assertEquals(3, cells.length);

    Sheet sheet2 = workbook.createSheet();
    cells =
        sheet2
            .setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4"))
            .getFlattenedCells();
    assertEquals(3, cells.length);

    workbook = _testDataProvider.writeOutAndReadBack(workbook);
    sheet1 = workbook.getSheetAt(0);
    for (int rownum = 3; rownum <= 5; rownum++) {
      Cell cell1 = sheet1.getRow(rownum).getCell(2);
      assertTrue(cell1.isPartOfArrayFormulaGroup());

      Cell cell2 = sheet1.getRow(rownum).getCell(0);
      assertTrue(cell2.isPartOfArrayFormulaGroup());
    }

    sheet2 = workbook.getSheetAt(1);
    for (int rownum = 1; rownum <= 3; rownum++) {
      Cell cell1 = sheet2.getRow(rownum).getCell(3);
      assertTrue(cell1.isPartOfArrayFormulaGroup());
    }
  }
  public void testModifyArrayCells_setCellType() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells -
    // changing cell type removes the array formula and associated cached result
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellType(Cell.CELL_TYPE_STRING);
    assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType());
    scell.setCellValue("string cell");
    assertEquals("string cell", scell.getStringCellValue());

    // once you create a multi-cell array formula, you cannot change the type of its cells
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      try {
        assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
        mcell.setCellType(Cell.CELL_TYPE_NUMERIC);
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Cell.setCellType leaves the cell
      // in the state that it was in prior to the invocation
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
      assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
  }
  public void testOneSeriePlot() throws Exception {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = new SheetBuilder(wb, plotData).build();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
    Chart chart = drawing.createChart(anchor);

    ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
    ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

    ScatterChartData scatterChartData = chart.getChartDataFactory().createScatterChartData();

    ChartDataSource<String> xs =
        DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
    ChartDataSource<Number> ys =
        DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
    ScatterChartSerie serie = scatterChartData.addSerie(xs, ys);

    assertNotNull(serie);
    assertEquals(1, scatterChartData.getSeries().size());
    assertTrue(scatterChartData.getSeries().contains(serie));

    chart.plot(scatterChartData, bottomAxis, leftAxis);
  }
  /** Test that we can set pre-calculated formula result for array formulas */
  public void testModifyArrayCells_setFormulaResult() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formula
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellValue(1.1);
    assertEquals(1.1, scell.getNumericCellValue());

    // multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
      assertEquals(0.0, mcell.getNumericCellValue());
      double fmlaResult = 1.2;
      mcell.setCellValue(fmlaResult);
      assertEquals(fmlaResult, mcell.getNumericCellValue());
    }
  }
  public final void testAutoCreateOtherCells() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet("Sheet1");

    Row row1 = sheet.createRow(0);
    Cell cellA1 = row1.createCell(0);
    Cell cellB1 = row1.createCell(1);
    String formula = "42";
    sheet.setArrayFormula(formula, CellRangeAddress.valueOf("A1:B2"));

    assertEquals(formula, cellA1.getCellFormula());
    assertEquals(formula, cellB1.getCellFormula());
    Row row2 = sheet.getRow(1);
    assertNotNull(row2);
    assertEquals(formula, row2.getCell(0).getCellFormula());
    assertEquals(formula, row2.getCell(1).getCellFormula());
  }
  private HSSFSheet createWorkSheet(HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.createSheet("TIH-Demurage");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    org.apache.poi.ss.usermodel.PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:C5")};

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet
        .createRow(1)
        .createCell(1)
        .setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
    return sheet;
  }
  @Test
  public void mergedCells() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();

    Row row = sheet.createRow(0);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));

    Cell cell0 = row.createCell(0);
    cell0.setCellValue("Apache Software Foundation");

    int defaulWidth = sheet.getColumnWidth(0);
    sheet.autoSizeColumn(0);
    // column is unchanged if merged regions are ignored (Excel like behavior)
    assertEquals(defaulWidth, sheet.getColumnWidth(0));

    sheet.autoSizeColumn(0, true);
    assertTrue(sheet.getColumnWidth(0) > defaulWidth);

    workbook.close();
  }
Example #13
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();
  }
  /**
   * 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();
  }