/*
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;
}
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()));
}
/** 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())));
}
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()));
}
/**
* 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())));
}
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())));
}
}
}
/**
* 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());
}
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);
}
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());
}
/** 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())));
}
/** 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()));
}
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);
}
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());
}
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);
}
