sample code for POI

build.gradle

apply plugin: 'java'

repositories {
    mavenCentral()
}

dependencies {
    compile 'org.apache.poi:poi:3.15'
    compile 'org.apache.poi:poi-ooxml:3.15'

    testCompile 'junit:junit:4.+'
}

processTestResources{
    exclude '**/~$*.xlsx'
}

ColumnNum.java

package sample;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ColumnNum {
    int value();
}

DateUtils.java

package sample;

import java.util.Calendar;
import java.util.Date;

public class DateUtils {
    public static Date getDate(int year, int month, int mday) {
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.MILLISECOND, 0);
        cal.set(year, month-1, mday, 0, 0, 0);

        return cal.getTime();
    }

    public static Date getDate(long timeInMills) {
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.MILLISECOND, 0);
        cal.set(1900, 1-1, 0, 0, 0, 0);
        long zero = cal.getTimeInMillis();

        cal.setTimeInMillis(zero + timeInMills);

        return cal.getTime();
    }
    public static Date getDate(double day) {
        return getDate((long)(day*1000 * 24*60*60));
    }
}

Excel.java

package sample;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.eval.NotImplementedException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Excel {
    static String default_directory = System.getProperty("java.io.tmpdir");

    static CellValue getFormulaValue(Cell cell) {
        assert cell.getCellTypeEnum() == CellType.FORMULA;

        org.apache.poi.ss.usermodel.Workbook book = cell.getSheet().getWorkbook();
        CreationHelper helper = book.getCreationHelper();
        FormulaEvaluator evaluator = helper.createFormulaEvaluator();
        try {
            CellValue value = evaluator.evaluate(cell);
            return value;
        }
        catch (FormulaParseException e) {
            return new CellValue("#NAME?");
        }
        catch (NotImplementedException e) {
            return new CellValue("#VALUE!");
        }
    }

    static String getStringCellValue(CellValue cellValue) {
        switch (cellValue.getCellTypeEnum()) {
            case _NONE:
                return cellValue.getStringValue();
            case BLANK:
                return cellValue.getStringValue();
            case BOOLEAN:
                return cellValue.getStringValue();
            case ERROR:
                return cellValue.formatAsString();
            case FORMULA:
                return cellValue.getStringValue();
            case NUMERIC:
                return cellValue.getStringValue();
            case STRING:
                return cellValue.getStringValue();
            default:
                throw new NotImplementedException("not implemented");
        }
    }

    public static Workbook createWorkbook() {
        return new Workbook();
    }

    public static Workbook openWorkbook(String path) throws IOException, InvalidFormatException {
        return new Workbook(path);
    }

    public static class Workbook {
        String excelPath;
        org.apache.poi.ss.usermodel.Workbook book;

        // Books

        public Workbook() {
            excelPath = PathUtils.path(default_directory, "Book1.xlsx");
            this.book = new org.apache.poi.xssf.usermodel.XSSFWorkbook();
            createSheetIfNone();
        }

        public Workbook(String path) throws IOException, InvalidFormatException {
            excelPath = PathUtils.path(path);
            this.book = org.apache.poi.ss.usermodel.WorkbookFactory.create(new File(path));

            addSheets();
        }

        public String getName() {
            return PathUtils.basename(excelPath);
        }

        public String getPath() {
            return excelPath;
        }

        public void save() throws IOException {
            save(excelPath);
        }

        public void save(String path) throws IOException {
            try (FileOutputStream out = new FileOutputStream(path)) {
                createSheetIfNone();
                book.write(out);
            };
        }

        // recalculate
        public void evaluateAllFormulaCells() {
            try {
                XSSFFormulaEvaluator.evaluateAllFormulaCells(book);
            }
            catch (FormulaParseException e) {
                // through
            }
            catch (NotImplementedException e) {
                // through
            }
        }

        // Sheets

        TreeMap<Integer,Worksheet> indexToWorksheetMap = new TreeMap<Integer,Worksheet>();
        HashMap<String,Integer> sheetNameToIndexMap = new HashMap<String,Integer>();

        public List<Worksheet> getSheets() {
            return new ArrayList<Worksheet>(indexToWorksheetMap.values());
        }

        void createSheetIfNone() {
            if (book.getNumberOfSheets() == 0) {
                addSheet();
            }
        }

        void addSheets() {
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
                Sheet sheet = book.getSheetAt(i);
                addSheet(sheet, new Worksheet(sheet));
            }
        }

        public Worksheet addSheet() {
            int num = book.getNumberOfSheets() + 1;
            String name = "Sheet" + num;
            return addSheet(name);
        }

        int searchDuplicateSheet(String name) {
            Pattern pat = Pattern.compile("^" + Pattern.quote(name) + " \\((\\d+)\\)$");

            int maxnum = 0;
            for (Worksheet worksheet: getSheets()) {
                String sheetName = worksheet.getName();
                Matcher mat = pat.matcher(sheetName);
                if (mat.find()) {
                    int num = Integer.parseInt(mat.group(1));
                    if (maxnum < num) {
                        maxnum = num;
                    }
                }
            }
            return maxnum;
        }

        public Worksheet addSheet(String name) throws IllegalArgumentException {
            Sheet sheet;
            IllegalArgumentException save = null;
            for (int retry = 0;; retry++) {
                try {
                    sheet = book.createSheet(name);
                    break;
                } catch (IllegalArgumentException e) {
                    if (save != null) {
                        throw save;
                    }

                    save = e;

                    // name (n)
                    int num = searchDuplicateSheet(name);

                    if (num > 0) {
                        name = name + " (" + (num + 1) + ")";
                    } else {
                        name = name + " (" + 2 + ")";
                    }
                    continue;
                }
            }

            Worksheet worksheet = new Worksheet(sheet);

            addSheet(sheet, worksheet);

            return worksheet;
        }

        void addSheet(org.apache.poi.ss.usermodel.Sheet sheet, Worksheet worksheet) {
            int index = book.getSheetIndex(sheet);
            String name = book.getSheetName(index);

            indexToWorksheetMap.put(index, worksheet);
            sheetNameToIndexMap.put(name, index);
        }

        public Worksheet getSheet(String name) {
            if (sheetNameToIndexMap.containsKey(name)) {
                return indexToWorksheetMap.get(sheetNameToIndexMap.get(name));
            }

            org.apache.poi.ss.usermodel.Sheet sheet = book.getSheet(name);
            Worksheet worksheet = new Worksheet(sheet);

            return worksheet;
        }

        public Worksheet getSheet(int index) {
            if (indexToWorksheetMap.containsKey(index)) {
                return indexToWorksheetMap.get(index);
            }
            String name = book.getSheetName(index);
            return getSheet(name);
        }

        public class Worksheet {
            org.apache.poi.ss.usermodel.Sheet sheet;
            Map<Integer,Row> rows = new HashMap<Integer,Row>();

            public Worksheet(org.apache.poi.ss.usermodel.Sheet sheet) {
                this.sheet = sheet;
                sheet.setForceFormulaRecalculation(true);

                Workbook.this.addSheet(sheet, this);
            }

            public String getName() {
                return sheet.getSheetName();
            }

            public Workbook getParent() {
                return Workbook.this;
            }

            // Row
            public Range getRange(int row, int col) {
                return new Range(row, col);
            }

            // Range class

            public class Range {
                int rownum;
                int colnum;

                public Range(int rownum, int colnum) {
                    this.rownum = rownum;
                    this.colnum = colnum;
                }

                public Range move(int rownum, int colnum) {
                    this.rownum += rownum;
                    this.colnum += colnum;

                    return this;
                }

                Row getRow(int rownum) {
                    Row row = Worksheet.this.sheet.getRow(rownum);
                    if (row == null) {
                        row = Worksheet.this.sheet.createRow(rownum);
                    }
                    return row;
                }

                Cell getCell(int rownum, int colnum) {
                    Row row = getRow(rownum);
                    Cell cell = row.getCell(colnum);
                    if (cell == null) {
                        cell = row.createCell(colnum);
                    }
                    return cell;
                }

                public String getAddress() {
                    return getCell(rownum, colnum).getAddress().toString();
                }

                // getXXXValue

                Object getJavaTypeValue(Class<?> clazz) {
                    switch (clazz.getName()) {
                        case "byte":
                        case "java.lang.Byte":
                            return (byte)this.getLongValue();
                        case "char":
                        case "java.lang.Char":
                            return (char)this.getLongValue();
                        case "int":
                        case "java.lang.Integer":
                            return (int)this.getLongValue();
                        case "long":
                        case "java.lang.Long":
                            return this.getLongValue();
                        case "float":
                        case "java.lang.Float":
                            return (float)this.getDoubleValue();
                        case "double":
                        case "java.lang.Double":
                            return this.getDoubleValue();
                        case "boolean":
                        case "java.lang.Boolean":
                            return this.getBooleanValue();
                        case "java.lang.String":
                            return this.getStringValue();
                        case "java.math.BigDecimal":
                            return java.math.BigDecimal.valueOf(this.getDoubleValue());
                        case "java.util.Date":
                        case "java.sql.Date":
                            return this.getDateValue();
                        default:
                            throw new IllegalArgumentException("unknown java type " + clazz.getName());
                    }
                }

                public String getValue() {
                    return getStringValue();
                }
                public String getStringValue() {
                    CellType type = getCell(rownum, colnum).getCellTypeEnum();
                    switch (type) {
                        case _NONE:
                        case BLANK:
                            return "";
                        case BOOLEAN:
                            boolean bool = getBooleanValue();
                            return bool ? "TRUE" : "FALSE";
                        case ERROR:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case FORMULA:
                        {
                            CellValue cellValue = getFormulaValue(getCell(rownum, colnum));
                            return getStringCellValue(cellValue);
                        }
                        case NUMERIC:
                            double num = getDoubleValue();
                            if (num % 1.0 == 0.0) {
                                return String.valueOf(getLongValue());
                            }
                            else {
                                return String.valueOf(num);
                            }
                        case STRING:
                            return getCell(rownum, colnum).getStringCellValue();
                        default:
                            throw new IllegalStateException("unknown type : " + type.name());
                    }
                }
                public Date getDateValue() {
                    CellType type = getCell(rownum, colnum).getCellTypeEnum();
                    Calendar col = Calendar.getInstance();
                    long zero = -2209107600000L;  // 1989-12-31 00:00:00

                    switch (type) {
                        case _NONE:
                            throw new NotImplementedException("not implemented");
                        case BLANK:
                            col.setTimeInMillis(zero + ((long)(1*1000)) * (24*60*60));
                            return col.getTime();
                        case BOOLEAN: {
                            double num = getDoubleValue();
                            col.setTimeInMillis(zero + ((long) (num * 1000)) * (24 * 60 * 60));
                            return col.getTime();
                        }
                        case ERROR:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case FORMULA:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case NUMERIC:
                            return getCell(rownum, colnum).getDateCellValue();
                        case STRING:
                            String str = getStringValue();
                            double num;
                            try {
                                num = Double.parseDouble(str);
                            }
                            catch (NumberFormatException e) {
                                num = 0.0;
                            }
                            col.setTimeInMillis(zero + ((long) (num * 1000)) * (24 * 60 * 60));
                            return col.getTime();
                        default:
                            throw new IllegalStateException("unknown type : " + type.name());
                    }
                }
                public boolean getBooleanValue() {
                    CellType type = getCell(rownum, colnum).getCellTypeEnum();
                    switch (type) {
                        case _NONE:
                            throw new NotImplementedException("not implemented");
                        case BLANK:
                            return false;
                        case BOOLEAN:
                            return getCell(rownum, colnum).getBooleanCellValue();
                        case ERROR:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case FORMULA:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case NUMERIC:
                            double num = getDoubleValue();
                            if (num == 0.0) {
                                return false;
                            }
                            else {
                                return true;
                            }
                        case STRING:
                            String str = getCell(rownum, colnum).getStringCellValue();
                            if ("".equals(str)) {
                                return false;
                            }
                            else if ("FALSE".equalsIgnoreCase(str)) {
                                return false;
                            }
                            else {
                                return true;
                            }
                        default:
                            throw new IllegalStateException("unknown type : " + type.name());
                    }
                }

                public double getDoubleValue() {
                    CellType type = getCell(rownum, colnum).getCellTypeEnum();
                    switch (type) {
                        case _NONE:
                            throw new NotImplementedException("not implemented");
                        case BLANK:
                            return 0.0;
                        case BOOLEAN: {
                            boolean bool = getCell(rownum, colnum).getBooleanCellValue();
                            if (bool) {
                                return 1.0;
                            }
                            else {
                                return 0.0;
                            }
                        }
                        case ERROR:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case FORMULA:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case NUMERIC:
                            return getCell(rownum, colnum).getNumericCellValue();
                        case STRING:
                            String str = getStringValue();
                            double num;
                            try {
                                num = Double.parseDouble(str);
                            }
                            catch (NumberFormatException e) {
                                num = 0.0;
                            }

                            return num;
                        default:
                            throw new IllegalStateException("unknown type : " + type.name());
                    }
                }

                public long getLongValue() {
                    CellType type = getCell(rownum, colnum).getCellTypeEnum();
                    switch (type) {
                        case _NONE:
                            throw new NotImplementedException("not implemented");
                        case BLANK:
                            return 0L;
                        case BOOLEAN: {
                            double num = getDoubleValue();
                            return Math.round(num);
                        }
                        case ERROR:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case FORMULA:
                            // TODO
                            throw new NotImplementedException("not implemented");
                        case NUMERIC:
                            return Math.round(getDoubleValue());
                        case STRING:
                            String str = getStringValue();
                            double num;
                            try {
                                num = Double.parseDouble(str);
                            }
                            catch (NumberFormatException e) {
                                num = 0.0;
                            }

                            return Math.round(num);
                        default:
                            throw new IllegalStateException("unknown type : " + type.name());
                    }
                }

                // setXXXValue

                // Formula
                public String setFormula(String formula) {
                    if (formula.startsWith("=")) {
                        formula = formula.substring(1);
                    }
                    try {
                        getCell(rownum, colnum).setCellFormula(formula);
                    }
                    catch (FormulaParseException e) {
                        return "#NAME?";
                    }
                    return getStringValue();
                }

                // String
                public String setValue(String str) {
                    getCell(rownum, colnum).setCellValue(str);
                    return getStringValue();
                }

                // Date
                public Date setValue(Date date) {
                    getCell(rownum, colnum).setCellValue(date);
                    return getDateValue();
                }

                // Boolean
                public boolean setValue(boolean bool) {
                    getCell(rownum, colnum).setCellValue(bool);
                    return getBooleanValue();
                }

                // Double
                public double setValue(double num) {
                    getCell(rownum, colnum).setCellValue((double)num);
                    return getDoubleValue();
                }

                // Int
                public int setValue(int num) {
                    getCell(rownum, colnum).setCellValue((double)num);
                    return (int)getLongValue();
                }

                // Long
                public long setValue(long num) {
                    getCell(rownum, colnum).setCellValue((double)num);
                    return getLongValue();
                }

                public Range offset(int rownum, int colnum) {
                    return new Range(this.rownum + rownum, this.colnum + colnum);
                }
                public <T> void readRows(Class<T> clazz, RowsReaderCallback<T> callback) throws IllegalAccessException, InstantiationException {
                    HashMap<String,Field> fieldNameMap = new HashMap<String,Field>();
                    HashMap<Integer,Field> fieldColumnMap = new HashMap<Integer,Field>();

                    for (Field f: clazz.getDeclaredFields()) {
                        fieldNameMap.put(f.getName().toLowerCase(), f);
                        for (Annotation a: f.getDeclaredAnnotations()) {
                            if (a instanceof ColumnNum) {
                                ColumnNum cn = (ColumnNum)a;
                                fieldColumnMap.put(cn.value() - 1, f);
                            }
                        }
                    }

                    // Decide field by header name
                    int maxItems = fieldNameMap.size();
                    int maxCol = 0;
                    for (int col = 0, items = 0; items < maxItems; maxCol = ++col) {
                        if (fieldColumnMap.containsKey(col)) {
                            // This field has the ColumnNum annotation
                            items++;
                            continue;
                        }

                        Range r = this.offset(0, col);
                        String cellValue = r.getStringValue();
                        if (cellValue.equals("")) {
                            continue;
                        }

                        Field f = fieldNameMap.get(cellValue.toLowerCase());
                        if (f == null) {
                            throw new IllegalArgumentException(String.format("%s has no field %s", clazz.getName(), cellValue));
                        }
                        fieldColumnMap.put(col, f);
                        items++;
                    }

                    this.move(1, 0);
                    for (;;) {
                        if ("".equals(this.offset(0,0).getStringValue())) {
                            break;
                        }

                        T bean = clazz.newInstance();

                        for (int col = 0; col < maxCol; col++) {
                            Field f = fieldColumnMap.get(col);
                            if (f == null) {
                                continue;
                            }

                            Range r = this.offset(0, col);
                            Object cellValue = r.getJavaTypeValue(f.getType());
                            f.set(bean, cellValue);
                        }
                        callback.read(bean);
                        this.move(1, 0);
                    }
                }
            }
        }
    }
}

ExcelTest.java

package sample;

import org.junit.Before;
import org.junit.Test;
import sample.Excel.Workbook;
import sample.Excel.Workbook.Worksheet;
import sample.Excel.Workbook.Worksheet.Range;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertThat;

public class ExcelTest {

    String tmpdir = PathUtils.getTmpdir();
    Workbook workbook;

    @Before
    public void setUp() {
        String path = PathUtils.path(tmpdir, "Book1.xlsx");
        PathUtils.removeFile(path);
        assertThat(PathUtils.exists(path), is(false));

        workbook = Excel.createWorkbook();
    }

    @Test
    public void testTouchFile() throws Exception {
        String path = PathUtils.path(tmpdir,"testtest.test");

        PathUtils.touchFile(path);
        assertThat(PathUtils.path(path), is(PathUtils.path(tmpdir, "testtest.test")));
        assertThat(PathUtils.exists(path), is(true));
        assertThat(PathUtils.exists(path + 2), is(false));

        PathUtils.removeFile(path);
        assertThat(PathUtils.exists(path), is(false));

        String projectDir = PathUtils.getCurrentDirectory();
        String classDir = PathUtils.path("/", projectDir, "build/classes/test/");
        String resourceDir = PathUtils.path("/", projectDir, "build/resources/test/");
        assertThat(ResourceUtils.getResourcePath("/"), is(classDir));
        assertThat(ResourceUtils.getResourcePath("/sample.xlsx"), is(PathUtils.path(resourceDir, "sample.xlsx")));
    }

    @Test
    public void testOpenWorkBook() throws Exception {
        assertThat(workbook.getName(), is("Book1.xlsx"));
        assertThat(workbook.getPath(), is("C:/tmp/sys/Book1.xlsx"));
    }

    @Test
    public void testSave() throws Exception {
        assertThat(PathUtils.exists(workbook.getPath()), is(false));
        workbook.save();
        assertThat(PathUtils.exists(workbook.getPath()), is(true));
    }

    @Test
    public void testSaveWithPath() throws Exception {
        String oldpath = workbook.getPath();
        String newpath = "c:/tmp/testtmp.xlsx";
        PathUtils.removeFile(newpath);

        assertThat(PathUtils.exists(oldpath), is(false));
        assertThat(PathUtils.exists(newpath), is(false));

        workbook.save(newpath);

        // The newpath is saved, but the path of workbook is not changed
        assertThat(workbook.getPath(), is(oldpath));

        assertThat(PathUtils.exists(oldpath), is(false));
        assertThat(PathUtils.exists(newpath), is(true));
    }

    @Test
    public void testGetSheet() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        assertThat(sheet.getName(), is("Sheet1"));
    }

    @Test
    public void testAddSheet() throws Exception {
        assertThat(workbook.getSheets().size(), is(1));
        workbook.addSheet();
        assertThat(workbook.getSheets().size(), is(2));

        List<Worksheet> sheets = workbook.getSheets();
        assertThat(sheets.get(0).getName(), is("Sheet1"));
        assertThat(sheets.get(1).getName(), is("Sheet2"));
    }

    @Test
    public void testAddSheetWithName() throws Exception {
        assertThat(workbook.getSheets().size(), is(1));
        Worksheet sheet1 = workbook.getSheet(0);
        Worksheet sheet2 = workbook.addSheet("test1");
        Worksheet sheet3 = workbook.addSheet("test1");
        Worksheet sheet4 = workbook.addSheet("test1");

        assertThat(workbook.getSheets().size(), is(4));

        assertThat(sheet1.getName(), is("Sheet1"));
        assertThat(sheet2.getName(), is("test1"));
        assertThat(sheet3.getName(), is("test1 (2)"));
        assertThat(sheet4.getName(), is("test1 (3)"));
    }

    @Test(expected = IllegalArgumentException.class)
    public void testAddSheetWithInvalidName() throws Exception {
        assertThat(workbook.getSheets().size(), is(1));
        workbook.addSheet("test1:");
    }

    @Test
    public void testGetValue() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);
        assertThat(range.getAddress(), is("A1"));

        assertThat(range.getValue(), is(""));
        assertThat(range.getStringValue(), is(""));
        assertThat(range.getDateValue(), is(DateUtils.getDate(1900,1,1)));
        assertThat(range.getBooleanValue(), is(false));
        assertThat(range.getDoubleValue(), is(0.0));
        assertThat(range.getLongValue(), is(0L));
    }

    @Test
    public void testSetValueString1() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue("10.5"), is("10.5"));

        assertThat(range.getValue(), is("10.5"));
        assertThat(range.getStringValue(), is("10.5"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(10.5)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(10.5));
        assertThat(range.getLongValue(), is(11L));
    }

    @Test
    public void testSetValueString2() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue("abc"), is("abc"));

        assertThat(range.getValue(), is("abc"));
        assertThat(range.getStringValue(), is("abc"));
        // error = 1989-12-31 0:0:0
        assertThat(range.getDateValue(), is(DateUtils.getDate(0.0)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(0.0));
        assertThat(range.getLongValue(), is(0L));
    }

    @Test
    public void testSetValueString3() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue("10a"), is("10a"));

        assertThat(range.getValue(), is("10a"));
        assertThat(range.getStringValue(), is("10a"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(0.0)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(0.0));
        assertThat(range.getLongValue(), is(0L));
    }

    @Test
    public void testSetValueString4() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue("1970/1/1"), is("1970/1/1"));

        assertThat(range.getValue(), is("1970/1/1"));
        assertThat(range.getStringValue(), is("1970/1/1"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(1899,12,31))); // do not evaluate format of getDate
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(0.0));
        assertThat(range.getLongValue(), is(0L));
    }

    @Test
    public void testSetValueDate1() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(DateUtils.getDate(1.0)), is(DateUtils.getDate(1.0)));

        assertThat(range.getValue(), is("1"));
        assertThat(range.getStringValue(), is("1"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(1.0)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(1.0));
        assertThat(range.getLongValue(), is(1L));
    }

    @Test
    public void testSetValueBoolean1() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(false), is(false));

        assertThat(range.getValue(), is("FALSE"));
        assertThat(range.getStringValue(), is("FALSE"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(0.0)));
        assertThat(range.getBooleanValue(), is(false));
        assertThat(range.getDoubleValue(), is(0.0));
        assertThat(range.getLongValue(), is(0L));
    }

    @Test
    public void testSetValueBoolean2() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(true), is(true));

        assertThat(range.getValue(), is("TRUE"));
        assertThat(range.getStringValue(), is("TRUE"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(1.0)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(1.0));
        assertThat(range.getLongValue(), is(1L));
    }

    @Test
    public void testSetValueDouble1() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(10.0), is(10.0));

        assertThat(range.getValue(), is("10"));
        assertThat(range.getStringValue(), is("10"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(10.0)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(10.0));
        assertThat(range.getLongValue(), is(10L));
    }


    @Test
    public void testSetValueDouble2() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(10.4), is(10.4));

        assertThat(range.getValue(), is("10.4"));
        assertThat(range.getStringValue(), is("10.4"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(10.4)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(10.4));
        assertThat(range.getLongValue(), is(10L));
    }

    @Test
    public void testSetValueDouble3() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(10.5), is(10.5));

        assertThat(range.getValue(), is("10.5"));
        assertThat(range.getStringValue(), is("10.5"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(10.5)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(10.5));
        assertThat(range.getLongValue(), is(11L));
    }

    @Test
    public void testSetValueInt() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(10), is(10));

        assertThat(range.getValue(), is("10"));
        assertThat(range.getStringValue(), is("10"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(1900,1,10)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(10.0));
        assertThat(range.getLongValue(), is(10L));
    }

    @Test
    public void testSetValueLong() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.setValue(10L), is(10L));

        assertThat(range.getValue(), is("10"));
        assertThat(range.getStringValue(), is("10"));
        assertThat(range.getDateValue(), is(DateUtils.getDate(1900,1,10)));
        assertThat(range.getBooleanValue(), is(true));
        assertThat(range.getDoubleValue(), is(10.0));
        assertThat(range.getLongValue(), is(10L));
    }

    @Test
    public void testGetValueFromSavedFile() throws Exception {
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);
        assertThat(range.getAddress(), is("A1"));

        assertThat(range.setValue("0"), is("0"));
        range.move(1, 0);
        assertThat(range.setValue("1"), is("1"));
        range.move(1, 0);
        assertThat(range.setValue("1.0"), is("1.0"));
        range.move(1, 0);
        assertThat(range.setValue("abc"), is("abc"));
        range.move(1, 0);

        workbook.save("c:/tmp/test1.xlsx");

        workbook = Excel.openWorkbook("c:/tmp/test1.xlsx");
        sheet = workbook.getSheet(0);
        range = sheet.getRange(0, 0);

        assertThat(range.getStringValue(), is("0"));
        range.move(1, 0);
        assertThat(range.getStringValue(), is("1"));
        range.move(1, 0);
        assertThat(range.getStringValue(), is("1.0"));
        range.move(1, 0);
        assertThat(range.getStringValue(), is("abc"));
        range.move(1, 0);
    }

    @Test
    public void testGetValueFromErrorFormula() throws Exception {
        String path = ResourceUtils.getResourcePath("/errorFormula.xlsx");

        workbook = Excel.openWorkbook(path);
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(0, 0);

        assertThat(range.getStringValue(), is("#DIV/0!"));
        range.move(1, 0);
        assertThat(range.getStringValue(), is("#NAME?"));
        range.move(1, 0);
        assertThat(range.getStringValue(), is("#VALUE!"));
        range.move(1, 0);
        assertThat(range.getStringValue(), is(""));
        range.move(1, 0);
    }

    @Test
    public void testGetValueFromSavedFile2() throws Exception {
        Worksheet sheet1 = workbook.getSheet(0);
        Range range1 = sheet1.getRange(0, 0);
        assertThat(range1.getAddress(), is("A1"));

        assertThat(range1.setFormula("=1/0"), is("#DIV/0!"));
        range1.move(1, 0);
        assertThat(range1.setValue("=T(aaa)"), is("=T(aaa)"));
        range1.move(1, 0);
        assertThat(range1.setFormula("=DATEVALUE(\"abc\")"), is("#VALUE!"));
        range1.move(1, 0);
        assertThat(range1.setFormula(""), is("#NAME?"));
        range1.move(1, 0);

        workbook.save("c:/tmp/test3.xlsx");

        workbook = Excel.openWorkbook("c:/tmp/test3.xlsx");
        Worksheet sheet2 = workbook.getSheet(0);
        Range range2 = sheet2.getRange(0, 0);

        assertThat(range2.getStringValue(), is("#DIV/0!"));
        range2.move(1, 0);
        assertThat(range2.getStringValue(), is("=T(aaa)"));
        range2.move(1, 0);
        assertThat(range2.getStringValue(), is("#VALUE!"));
        range2.move(1, 0);
        assertThat(range2.getStringValue(), is(""));
        range2.move(1, 0);
    }

    @Test
    public void testReadRow() throws Exception {
        String path = ResourceUtils.getResourcePath("/sampleRow.xlsx");

        workbook = Excel.openWorkbook(path);
        Worksheet sheet = workbook.getSheet(0);
        Range range = sheet.getRange(5, 3);

        final List<SampleBean> expectBeans = new ArrayList<SampleBean>() {
            {
                add(new SampleBean());
                add(new SampleBean());
            }
        };
        expectBeans.get(0).name = "username1";
        expectBeans.get(0).address = "ＸＸ県";
        expectBeans.get(0).age = 10;
        expectBeans.get(0).createdDate = DateUtils.getDate(2016,11,27);

        expectBeans.get(1).name = "username2";
        expectBeans.get(1).address = "ＸＸ県○○市";
        expectBeans.get(1).age = 20;
        expectBeans.get(1).createdDate = DateUtils.getDate(2016,11,28);

        assertThat(range.getAddress(), is("D6"));
        range.readRows(SampleBean.class, new RowsReaderCallback<SampleBean>() {
            int i = 0;
            @Override
            public void read(SampleBean bean) {
                assertThat(bean, is(expectBeans.get(i)));
                i++;
            }
        });
        assertThat(range.getAddress(), is("D9"));
    }
}

PathUtils.java

package sample;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.regex.Pattern;

public class PathUtils {
    public static boolean exists(String path) {
        File file = new File(path);
        return file.exists();
    }

    public static String path(String... paths) {
        StringBuilder sb = new StringBuilder();
        for (String path: paths) {
            path = path.replace('\\', '/'); // convert to UNIX path
            if (sb.length() > 0 && sb.charAt(sb.length()-1) != '/') {
                sb.append("/");
            }
            sb.append(path);
        }

        return sb.toString();
    }

    public static String path(File file) {
        return path(file.getPath().toString());
    }

    public static String basename(String path) {
        return new File(path).getName();
    }

    public static String dirname(String path) {
        return path(new File(path).getParent());
    }

    public static void removeFile(String path) {
        File file = new File(path);
        file.delete();
    }

    public static void touchFile(String path) throws IOException {
        File file = new File(path);
        file.createNewFile();
    }

    public static String getTmpdir() {
        return path(System.getProperty("java.io.tmpdir"));
    }

    public static String getCurrentDirectory() {
        return path(System.getProperty("user.dir"));
//        return path(new File(".").getAbsoluteFile().getParent());
    }
}

ResourceUtils.java

package sample;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.regex.Pattern;

public class ResourceUtils {
    private static String path(String path) {
        return path.replace('\\', '/'); // convert to UNIX path
    }

    public static List<String> findResources(String pattern) {
        return findResources(Pattern.compile(pattern));
    }

    public static List<String> findResources(Pattern pattern) {
        List<String> list = new ArrayList<String>();
        Enumeration<URL> urls = null;
        try {
            urls = ResourceUtils.class.getClassLoader().getResources("");
        } catch (IOException e) {
            return list;
        }

        while (urls.hasMoreElements()) {
            findResourcesRecursive(list, urls.nextElement().getPath(), pattern);
        }
        return list;
    }

    private static void findResourcesRecursive(List<String> list, String path, Pattern pattern) {
        File file = new File(path);
        if (file.isDirectory()) {
            for (File file2: file.listFiles()) {
                findResourcesRecursive(list, file2.getPath(), pattern);
            }
        }
        else {
            if (pattern.matcher(file.getName()).find()) {
                list.add(ResourceUtils.path(path));
            }
        }
    }

    public static String getResourcePath(String path) {
        URL url = ResourceUtils.class.getClass().getResource(path);
        return ResourceUtils.path(url.getPath());
    }
}

ResourceUtilsTest.java

package sample;

import org.junit.Test;

import java.io.IOException;
import java.net.URL;
import java.util.Enumeration;
import java.util.List;

import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.nullValue;
import static org.junit.Assert.*;

public class ResourceUtilsTest {
    @Test
    public void testGetResources() throws Exception {
        Enumeration<URL> urls;

        urls = this.getClass().getClassLoader().getResources("");
        assertThat(urls.hasMoreElements(), is(true));
        assertThat(urls.nextElement().toString(), is("file:/C:/workspace/poi/build/classes/test/"));
        assertThat(urls.nextElement().toString(), is("file:/C:/workspace/poi/build/classes/main/"));
        assertThat(urls.nextElement().toString(), is("file:/C:/workspace/poi/build/resources/test/"));
        assertThat(urls.nextElement().toString(), is("file:/C:/workspace/poi/build/resources/main/"));
        assertThat(urls.hasMoreElements(), is(false));

        urls = this.getClass().getClassLoader().getResources("foobar");

        assertThat(urls.hasMoreElements(), is(false));

        urls = this.getClass().getClassLoader().getResources("sample.xlsx");

        assertThat(urls.hasMoreElements(), is(true));
    }

    @Test
    public void testClassGetResources() throws Exception {
        URL url;
        url = this.getClass().getResource("/");
        assertThat(url.toString(), is("file:/C:/workspace/poi/build/classes/test/"));

        url = this.getClass().getResource("sample.xlsx");

        assertThat(url, is(nullValue()));

        url = this.getClass().getResource("/sample.xlsx");
        assertThat(url.toString(), is("file:/C:/workspace/poi/build/resources/test/sample.xlsx"));
    }

    @Test
    public void testClassLoaderGetResources() throws Exception {
        URL url;
        url = this.getClass().getClassLoader().getResource("/");
        assertThat(url, is(nullValue()));

        url = this.getClass().getClassLoader().getResource("sample.xlsx");

        assertThat(url.toString(), is("file:/C:/workspace/poi/build/resources/test/sample.xlsx"));

        url = this.getClass().getResource("/sample.xlsx");
        assertThat(url.toString(), is("file:/C:/workspace/poi/build/resources/test/sample.xlsx"));
    }

    @Test
    public void testFindResources() throws Exception {
        List<String> list = ResourceUtils.findResources("sample.*\\.xlsx");
        assertThat(list.size(), is(3));
        assertThat(list.get(0), is("C:/workspace/poi/build/resources/test/sample.xlsx"));
        assertThat(list.get(1), is("C:/workspace/poi/build/resources/test/sampleRow.xlsx"));
        assertThat(list.get(2), is("C:/workspace/poi/build/resources/main/sample2.xlsx"));
    }
}

RowsReaderCallback.java

package sample;

public interface RowsReaderCallback<T> {
    void read(T bean);
}

SampleBean.java

package sample;

import java.util.Date;

public class SampleBean {
    String name;
    String address;
    Integer age;
    Date createdDate;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;

        SampleBean that = (SampleBean) o;

        if (name != null ? !name.equals(that.name) : that.name != null) return false;
        if (address != null ? !address.equals(that.address) : that.address != null) return false;
        if (age != null ? !age.equals(that.age) : that.age != null) return false;
        return createdDate != null ? createdDate.equals(that.createdDate) : that.createdDate == null;

    }

    @Override
    public int hashCode() {
        int result = name != null ? name.hashCode() : 0;
        result = 31 * result + (address != null ? address.hashCode() : 0);
        result = 31 * result + (age != null ? age.hashCode() : 0);
        result = 31 * result + (createdDate != null ? createdDate.hashCode() : 0);
        return result;
    }

    @Override
    public String toString() {
        return "SampleBean{" +
                "name='" + name + '\'' +
                ", address='" + address + '\'' +
                ", age=" + age +
                ", createdDate=" + createdDate +
                '}';
    }
}