Example #1
2
    /*
            public static void copy(MultipartFile file,String savePath,String newname) throws Exception {
                try {
                    File targetFile = new File(savePath,newname);
                    if (!targetFile.exists()) {
                        //判断文件夹是否存在,不存在就创建
                        targetFile.mkdirs();
                    }

                    file.transferTo(targetFile);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            }
    */
    private static String getCellValue(HSSFCell cell) {
      DecimalFormat df = new DecimalFormat("#");
      String cellValue = null;
      if (cell == null) return null;
      switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
          if (HSSFDateUtil.isCellDateFormatted(cell)) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
            break;
          }
          cellValue = df.format(cell.getNumericCellValue());
          break;
        case HSSFCell.CELL_TYPE_STRING:
          cellValue = String.valueOf(cell.getStringCellValue());
          break;
        case HSSFCell.CELL_TYPE_FORMULA:
          cellValue = String.valueOf(cell.getCellFormula());
          break;
        case HSSFCell.CELL_TYPE_BLANK:
          cellValue = null;
          break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
          cellValue = String.valueOf(cell.getBooleanCellValue());
          break;
        case HSSFCell.CELL_TYPE_ERROR:
          cellValue = String.valueOf(cell.getErrorCellValue());
          break;
      }
      if (cellValue != null && cellValue.trim().length() <= 0) {
        cellValue = null;
      }
      return cellValue;
    }
Example #2
0
  public void testSheetFunctions() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("A");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellValue(1);
    c = r.createCell(1);
    c.setCellValue(2);

    s = wb.createSheet("B");
    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellFormula("AVERAGE(A!A1:B1)");
    c = r.createCell(1);
    c.setCellFormula("A!A1+A!B1");
    c = r.createCell(2);
    c.setCellFormula("A!$A$1+A!$B1");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);

    s = wb.getSheet("B");
    r = s.getRow(0);
    c = r.getCell(0);
    assertTrue(
        "expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(),
        ("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
    c = r.getCell(1);
    assertTrue(
        "expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
  }
Example #3
0
  /** Writes a function then tests to see if its correct */
  public void refAreaArrayFunctionTest(String function) {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    r = s.createRow(0);

    c = r.createCell(0);
    c.setCellFormula(function + "(A2:A4,B2:B4)");
    c = r.createCell(1);
    c.setCellFormula(function + "($A$2:$A4,B$2:B4)");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(0);

    assertTrue(
        "function =" + function + "(A2:A4,B2:B4)",
        ((function + "(A2:A4,B2:B4)").equals(c.getCellFormula())));

    c = r.getCell(1);
    assertTrue(
        "function =" + function + "($A$2:$A4,B$2:B4)",
        ((function + "($A$2:$A4,B$2:B4)").equals(c.getCellFormula())));
  }
Example #4
0
  public void testAbsRefs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r;
    HSSFCell c;

    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellFormula("A3+A2");
    c = r.createCell(1);
    c.setCellFormula("$A3+$A2");
    c = r.createCell(2);
    c.setCellFormula("A$3+A$2");
    c = r.createCell(3);
    c.setCellFormula("$A$3+$A$2");
    c = r.createCell(4);
    c.setCellFormula("SUM($A$3,$A$2)");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(0);
    assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
    c = r.getCell(1);
    assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
    c = r.getCell(2);
    assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
    c = r.getCell(3);
    assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula()));
    c = r.getCell(4);
    assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula()));
  }
Example #5
0
  /**
   * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we
   * expect (x operator y)
   */
  private static void binomialVerify(String operator, HSSFWorkbook wb) {
    HSSFSheet s = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.getRow(0);
    c = r.getCell(1);
    assertTrue(
        "minval Formula is as expected 1" + operator + "1 != " + c.getCellFormula(),
        (("1" + operator + "1").equals(c.getCellFormula())));

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.getRow(x);

      for (int y = 1; y < 256 && y > 0; y++) {

        c = r.getCell(y);

        assertTrue(
            "loop Formula is as expected " + x + operator + y + "!=" + c.getCellFormula(),
            (("" + x + operator + y).equals(c.getCellFormula())));
      }
    }

    // test our maximum values
    r = s.getRow(0);
    c = r.getCell(0);

    assertTrue(
        "maxval Formula is as expected",
        (("" + Short.MAX_VALUE + operator + Short.MAX_VALUE).equals(c.getCellFormula())));
  }
Example #6
0
  private static void floatVerify(String operator, HSSFWorkbook wb) {

    HSSFSheet s = wb.getSheetAt(0);

    // don't know how to check correct result .. for the moment, we just verify that the file can be
    // read.

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      HSSFRow r = s.getRow(x);

      for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) {

        HSSFCell c = r.getCell(y);
        assertTrue("got a formula", c.getCellFormula() != null);

        assertTrue(
            "loop Formula is as expected "
                + x
                + "."
                + y
                + operator
                + y
                + "."
                + x
                + "!="
                + c.getCellFormula(),
            (("" + x + "." + y + operator + y + "." + x).equals(c.getCellFormula())));
      }
    }
  }
Example #7
0
  /**
   * Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we
   * expect (x operator y)
   */
  private static void operationalRefVerify(String operator, HSSFWorkbook wb) {

    HSSFSheet s = wb.getSheetAt(0);
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.getRow(0);
    c = r.getCell(1);
    // get our minimum values
    assertTrue(
        "minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(),
        (("A2" + operator + "A3").equals(c.getCellFormula())));

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.getRow(x);

      for (int y = 1; y < 256 && y > 0; y++) {

        int refx1;
        int refy1;
        int refx2;
        int refy2;
        if (x + 50 < Short.MAX_VALUE) {
          refx1 = x + 50;
          refx2 = x + 46;
        } else {
          refx1 = x - 4;
          refx2 = x - 3;
        }

        if (y + 50 < 255) {
          refy1 = y + 50;
          refy2 = y + 49;
        } else {
          refy1 = y - 4;
          refy2 = y - 3;
        }

        c = r.getCell(y);
        CellReference cr = new CellReference(refx1, refy1, false, false);
        String ref = cr.formatAsString();
        ref = cr.formatAsString();
        cr = new CellReference(refx2, refy2, false, false);
        String ref2 = cr.formatAsString();

        assertTrue(
            "loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(),
            (("" + ref + operator + ref2).equals(c.getCellFormula())));
      }
    }

    // test our maximum values
    r = s.getRow(0);
    c = r.getCell(0);

    assertEquals("B1" + operator + "IV255", c.getCellFormula());
  }
Example #8
0
  public void testSumIf() {
    String function = "SUMIF(A1:A5,\">4000\",B1:B5)";

    HSSFWorkbook wb = openSample("sumifformula.xls");

    HSSFSheet s = wb.getSheetAt(0);
    HSSFRow r = s.getRow(0);
    HSSFCell c = r.getCell(2);
    assertEquals(function, c.getCellFormula());

    wb = new HSSFWorkbook();
    s = wb.createSheet();

    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellValue(1000);
    c = r.createCell(1);
    c.setCellValue(1);

    r = s.createRow(1);
    c = r.createCell(0);
    c.setCellValue(2000);
    c = r.createCell(1);
    c.setCellValue(2);

    r = s.createRow(2);
    c = r.createCell(0);
    c.setCellValue(3000);
    c = r.createCell(1);
    c.setCellValue(3);

    r = s.createRow(3);
    c = r.createCell(0);
    c.setCellValue(4000);
    c = r.createCell(1);
    c.setCellValue(4);

    r = s.createRow(4);
    c = r.createCell(0);
    c.setCellValue(5000);
    c = r.createCell(1);
    c.setCellValue(5);

    r = s.getRow(0);
    c = r.createCell(2);
    c.setCellFormula(function);

    HSSFTestDataSamples.writeOutAndReadBack(wb);
  }
Example #9
0
  public void testLogicalFormulas() {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("A");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("IF(A1<A2,B1,B2)");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(1);
    assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula());
  }
Example #10
0
  /** Add 1+1 -- WHoohoo! */
  public void testBasicAddIntegers() {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(1);
    c = r.createCell(1);
    c.setCellFormula(1 + "+" + 1);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(1);
    c = r.getCell(1);

    assertTrue("Formula is as expected", ("1+1".equals(c.getCellFormula())));
  }
Example #11
0
  /** tests order wrting out == order writing in for a given formula */
  private static void orderTest(String formula) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula(formula);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);

    // get our minimum values
    r = s.getRow(0);
    c = r.getCell(1);
    assertTrue("minval Formula is as expected", formula.equals(c.getCellFormula()));
  }
Example #12
0
  public void testSquareMacro() {
    HSSFWorkbook w = openSample("SquareMacro.xls");

    HSSFSheet s0 = w.getSheetAt(0);
    HSSFRow[] r = {s0.getRow(0), s0.getRow(1)};

    HSSFCell a1 = r[0].getCell(0);
    assertEquals("square(1)", a1.getCellFormula());
    assertEquals(1d, a1.getNumericCellValue(), 1e-9);

    HSSFCell a2 = r[1].getCell(0);
    assertEquals("square(2)", a2.getCellFormula());
    assertEquals(4d, a2.getNumericCellValue(), 1e-9);

    HSSFCell b1 = r[0].getCell(1);
    assertEquals("IF(TRUE,square(1))", b1.getCellFormula());
    assertEquals(1d, b1.getNumericCellValue(), 1e-9);

    HSSFCell b2 = r[1].getCell(1);
    assertEquals("IF(TRUE,square(2))", b2.getCellFormula());
    assertEquals(4d, b2.getNumericCellValue(), 1e-9);

    HSSFCell c1 = r[0].getCell(2);
    assertEquals("square(square(1))", c1.getCellFormula());
    assertEquals(1d, c1.getNumericCellValue(), 1e-9);

    HSSFCell c2 = r[1].getCell(2);
    assertEquals("square(square(2))", c2.getCellFormula());
    assertEquals(16d, c2.getNumericCellValue(), 1e-9);

    HSSFCell d1 = r[0].getCell(3);
    assertEquals("square(one())", d1.getCellFormula());
    assertEquals(1d, d1.getNumericCellValue(), 1e-9);

    HSSFCell d2 = r[1].getCell(3);
    assertEquals("square(two())", d2.getCellFormula());
    assertEquals(4d, d2.getNumericCellValue(), 1e-9);
  }
Example #13
0
  public void testStringFormulas() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("A");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("UPPER(\"abc\")");
    c = r.createCell(2);
    c.setCellFormula("LOWER(\"ABC\")");
    c = r.createCell(3);
    c.setCellFormula("CONCATENATE(\" my \",\" name \")");

    HSSFTestDataSamples.writeOutAndReadBack(wb);

    wb = openSample("StringFormulas.xls");
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(0);
    assertEquals("UPPER(\"xyz\")", c.getCellFormula());
  }
Example #14
0
  public void testIfFormulas() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("testSheet1");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellValue(1);
    c = r.createCell(2);
    c.setCellValue(2);
    c = r.createCell(3);
    c.setCellFormula("MAX(A1:B1)");
    c = r.createCell(4);
    c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(4);

    assertTrue(
        "expected: IF(A1=D1,\"A1\",\"B1\") got " + c.getCellFormula(),
        ("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula()));

    wb = openSample("IfFormulaTest.xls");
    s = wb.getSheetAt(0);
    r = s.getRow(3);
    c = r.getCell(0);
    assertTrue(
        "expected: IF(A3=A1,\"A1\",\"A2\") got " + c.getCellFormula(),
        ("IF(A3=A1,\"A1\",\"A2\")").equals(c.getCellFormula()));
    // c = r.getCell((short)1);
    // assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(),
    // ("A!A1+A!B1").equals(c.getCellFormula()));

    wb = new HSSFWorkbook();
    s = wb.createSheet("testSheet1");
    r = null;
    c = null;
    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellFormula("IF(1=1,0,1)");

    HSSFTestDataSamples.writeOutAndReadBack(wb);

    wb = new HSSFWorkbook();
    s = wb.createSheet("testSheet1");
    r = null;
    c = null;
    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellValue(1);

    c = r.createCell(1);
    c.setCellValue(3);

    HSSFCell formulaCell = r.createCell(3);

    r = s.createRow(1);
    c = r.createCell(0);
    c.setCellValue(3);

    c = r.createCell(1);
    c.setCellValue(7);

    formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");

    HSSFTestDataSamples.writeOutAndReadBack(wb);
  }