import
static
org.junit.Assert.assertEquals;
import
java.io.IOException;
import
java.net.URISyntaxException;
import
java.nio.file.Paths;
import
org.apache.poi.ss.usermodel.Row;
import
org.apache.poi.ss.usermodel.Sheet;
import
org.apache.poi.ss.usermodel.Workbook;
import
org.apache.poi.xssf.usermodel.XSSFWorkbook;
import
org.junit.Before;
import
org.junit.Test;
public
class
ExcelCellFormatterSampleUnitTest {
private
static
final
String EXCEL_FILE_NAME =
"ExcelCellFormatterSample.xlsx"
;
private
static
final
int
STRING_CELL_INDEX =
0
;
private
static
final
int
BOOLEAN_CELL_INDEX =
1
;
private
static
final
int
RAW_NUMERIC_CELL_INDEX =
2
;
private
static
final
int
FORMATTED_NUMERIC_CELL_INDEX =
3
;
private
static
final
int
FORMULA_CELL_INDEX =
4
;
private
String fileLocation;
@Before
public
void
setup()
throws
IOException, URISyntaxException {
fileLocation = Paths.get(ClassLoader.getSystemResource(EXCEL_FILE_NAME).toURI()).toString();
}
@Test
public
void
checkingForStringValue()
throws
IOException {
Workbook workbook =
new
XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(
0
);
Row row = sheet.getRow(
0
);
SampleExcelCellFormatter formatter =
new
SampleExcelCellFormatter();
assertEquals(
"Working with Excel"
, formatter.getCellStringValue(row.getCell(STRING_CELL_INDEX)));
workbook.close();
}
@Test
public
void
checkingForBooleanValue()
throws
IOException {
Workbook workbook =
new
XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(
0
);
Row row = sheet.getRow(
0
);
SampleExcelCellFormatter formatter =
new
SampleExcelCellFormatter();
assertEquals(
"TRUE"
, formatter.getCellStringValue(row.getCell(BOOLEAN_CELL_INDEX)));
workbook.close();
}
@Test
public
void
checkingForNumericValue()
throws
IOException {
Workbook workbook =
new
XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(
0
);
Row row = sheet.getRow(
0
);
SampleExcelCellFormatter formatter =
new
SampleExcelCellFormatter();
assertEquals(
"100.234"
, formatter.getCellStringValue(row.getCell(RAW_NUMERIC_CELL_INDEX)));
assertEquals(
"100.23"
, formatter.getCellStringValue(row.getCell(FORMATTED_NUMERIC_CELL_INDEX)));
workbook.close();
}
@Test
public
void
checkingForCellContainingFormula()
throws
IOException {
Workbook workbook =
new
XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(
0
);
Row row = sheet.getRow(
0
);
SampleExcelCellFormatter formatter =
new
SampleExcelCellFormatter();
assertEquals(
"SUM(100+200)"
, formatter.getCellStringValue(row.getCell(FORMULA_CELL_INDEX)));
workbook.close();
}
@Test
public
void
checkingForCellCalculatingFormula()
throws
IOException {
Workbook workbook =
new
XSSFWorkbook(fileLocation);
Sheet sheet = workbook.getSheetAt(
0
);
Row row = sheet.getRow(
0
);
SampleExcelCellFormatter formatter =
new
SampleExcelCellFormatter();
assertEquals(
"300"
, formatter.getCellStringValueWithFormula(row.getCell(FORMULA_CELL_INDEX), workbook));
workbook.close();
}
}