package org.sep4j;

import junit.framework.Assert;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.ExpectedException;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * the integration test
 * 
 * @author chenjianjx
 * 
 */

public class SsioIntegrationTest {

	@Rule
	public ExpectedException expectedEx = ExpectedException.none();

	@Test
	public void saveIfNoErrorTest() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("fake", "Not Real");

		ITRecord record = new ITRecord();

		Collection<ITRecord> records = Arrays.asList(record);
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		List<DatumError> datumErrors = new ArrayList<DatumError>();

		// save it
		Ssio.saveIfNoDatumError(headerMap, records, outputStream, null, datumErrors);

		byte[] spreadsheet = outputStream.toByteArray();
		Assert.assertEquals(0, spreadsheet.length);
		Assert.assertEquals(1, datumErrors.size());

	}

	@Test
	public void saveTest_ValidAndInvalid() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("primInt", "Primitive Int");
		headerMap.put("fake", "Not Real");

		ITRecord record = new ITRecord();
		record.setPrimInt(123);

		Collection<ITRecord> records = Arrays.asList(record);
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		String datumErrPlaceholder = "!!ERROR!!";
		List<DatumError> datumErrors = new ArrayList<DatumError>();

		// save it
		Ssio.save(headerMap, records, outputStream, datumErrPlaceholder, datumErrors);
		byte[] spreadsheet = outputStream.toByteArray();

		// do a save for human eye check
		FileUtils.writeByteArrayToFile(createFile("saveTest_ValidAndInvalid"), spreadsheet);

		// then parse it
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		Cell cell00 = headerRow.getCell(0);
		Cell cell01 = headerRow.getCell(1);
		Cell cell10 = dataRow.getCell(0);
		Cell cell11 = dataRow.getCell(1);

		// size
		Assert.assertEquals(1, sheet.getLastRowNum());
		Assert.assertEquals(2, headerRow.getLastCellNum()); // note cell num is
															// 1-based
		Assert.assertEquals(2, dataRow.getLastCellNum());

		// types
		Assert.assertEquals(Cell.CELL_TYPE_STRING, cell00.getCellType());
		Assert.assertEquals(Cell.CELL_TYPE_STRING, cell01.getCellType());
		Assert.assertEquals(Cell.CELL_TYPE_STRING, cell10.getCellType());
		Assert.assertEquals(Cell.CELL_TYPE_STRING, cell11.getCellType());

		// texts
		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("!!ERROR!!", cell11.getStringCellValue());

		// errors
		DatumError datumError = datumErrors.get(0);
		Assert.assertEquals(1, datumErrors.size());
		Assert.assertEquals(0, datumError.getRecordIndex());
		Assert.assertEquals("fake", datumError.getPropName());
		Assert.assertTrue(datumError.getCause().getMessage().contains("no getter method"));

	}

	
	@Test
	public void saveAndAppendTest_File() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("primInt", "Primitive Int");
		headerMap.put("fake", "Not Real");
		headerMap.put("str", "Str");

		ITRecord record = new ITRecord();
		record.setPrimInt(123);
		record.setStr("first row string");

		Collection<ITRecord> records = Arrays.asList(record);
		File theFile = createFile("saveAndAppendTest_File");
		String datumErrPlaceholder = "!!ERROR!!";
		List<DatumError> datumErrors = new ArrayList<DatumError>();

		// save it
		Ssio.save(headerMap, records, theFile, datumErrPlaceholder, datumErrors);



		// then parse it
		byte[] spreadsheet = FileUtils.readFileToByteArray(theFile);
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		Cell cell00 = headerRow.getCell(0);
		Cell cell01 = headerRow.getCell(1);
		Cell cell02 = headerRow.getCell(2);
		Cell cell10 = dataRow.getCell(0);
		Cell cell11 = dataRow.getCell(1);
		Cell cell12 = dataRow.getCell(2);

		// texts
		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("Str", cell02.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("!!ERROR!!", cell11.getStringCellValue());
		Assert.assertEquals("first row string", cell12.getStringCellValue());

		// errors
		DatumError datumError = datumErrors.get(0);
		Assert.assertEquals(1, datumErrors.size());
		Assert.assertEquals(0, datumError.getRecordIndex());
		Assert.assertEquals("fake", datumError.getPropName());
		Assert.assertTrue(datumError.getCause().getMessage().contains("no getter method"));

		// now append 2 records
		ITRecord record2 = new ITRecord();
		record2.setPrimInt(456);
		record2.setStr("row2 string");

		ITRecord record3 = new ITRecord();
		record3.setPrimInt(789);
		record3.setStr("row3 string");

		// append them
		String datumErrPlaceholder2 = "!!APPENED-ROW-ERROR!!";
		List<DatumError> datumErrors2 = new ArrayList<>();
		Ssio.appendTo(headerMap, Arrays.asList(record2, record3), theFile, datumErrPlaceholder2, datumErrors2);
		// parse again
		spreadsheet = FileUtils.readFileToByteArray(theFile);
		workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions again ***/
		sheet = workbook.getSheetAt(0);
		headerRow = sheet.getRow(0);
		dataRow = sheet.getRow(1);

		Assert.assertEquals(3, sheet.getLastRowNum());

		// the original rows should not be changed
		cell00 = headerRow.getCell(0);
		cell01 = headerRow.getCell(1);
		cell02 = headerRow.getCell(2);
		cell10 = dataRow.getCell(0);
		cell11 = dataRow.getCell(1);
		cell12 = dataRow.getCell(2);

		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("Str", cell02.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("!!ERROR!!", cell11.getStringCellValue());
		Assert.assertEquals("first row string", cell12.getStringCellValue());

		// now the new row
		Row dataRow2 = sheet.getRow(2);
		Cell cell20 = dataRow2.getCell(0);
		Cell cell21 = dataRow2.getCell(1);
		Cell cell22 = dataRow2.getCell(2);

		Row dataRow3 = sheet.getRow(3);
		Cell cell30 = dataRow3.getCell(0);
		Cell cell31 = dataRow3.getCell(1);
		Cell cell32 = dataRow3.getCell(2);

		Assert.assertEquals("456", cell20.getStringCellValue());
		Assert.assertEquals("!!APPENED-ROW-ERROR!!", cell21.getStringCellValue());
		Assert.assertEquals("row2 string", cell22.getStringCellValue());

		Assert.assertEquals("789", cell30.getStringCellValue());
		Assert.assertEquals("!!APPENED-ROW-ERROR!!", cell31.getStringCellValue());
		Assert.assertEquals("row3 string", cell32.getStringCellValue());

		// new errors
		Assert.assertEquals(2, datumErrors2.size());

		DatumError firstAppendingError = datumErrors2.get(0);
		Assert.assertEquals(0, firstAppendingError.getRecordIndex());
		Assert.assertEquals("fake", firstAppendingError.getPropName());
		Assert.assertTrue(firstAppendingError.getCause().getMessage().contains("no getter method"));


		DatumError secondAppendingError = datumErrors2.get(1);
		Assert.assertEquals(1, secondAppendingError.getRecordIndex());
		Assert.assertEquals("fake", secondAppendingError.getPropName());
		Assert.assertTrue(secondAppendingError.getCause().getMessage().contains("no getter method"));

		//append again,  ignoring errors
		ITRecord record4 = new ITRecord();
		record4.setPrimInt(123456789);
		record4.setStr("row4 string");

		// append them
		Ssio.appendTo(headerMap, Arrays.asList(record4), theFile);
		// parse again
		spreadsheet = FileUtils.readFileToByteArray(theFile);
		workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions again ***/
		sheet = workbook.getSheetAt(0);
		Assert.assertEquals(4, sheet.getLastRowNum());


		Row dataRow4 = sheet.getRow(4);
		Cell cell40 = dataRow4.getCell(0);
		Cell cell41 = dataRow4.getCell(1);
		Cell cell42 = dataRow4.getCell(2);

		Assert.assertEquals("123456789", cell40.getStringCellValue());
		Assert.assertEquals("", cell41.getStringCellValue());
		Assert.assertEquals("row4 string", cell42.getStringCellValue());
	}


	@Test
	public void saveAndAppendMapsTest_File() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("primInt", "Primitive Int");
		headerMap.put("fake", "Not Real");
		headerMap.put("str", "Str");

		LinkedHashMap<String, Object> record = new LinkedHashMap<String, Object>();
		record.put("primInt", 123);
		record.put("str",  "first row string");

		Collection<Map<String, Object>> records = Arrays.asList(record);
		File theFile = createFile("saveAndAppendMapsTest_File");
		String datumErrPlaceholder = "!!ERROR!!";
		List<DatumError> datumErrors = new ArrayList<DatumError>();

		// save it
		Ssio.saveMaps(headerMap, records, theFile, datumErrPlaceholder, datumErrors);

		// then parse it
		byte[] spreadsheet = FileUtils.readFileToByteArray(theFile);
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		Cell cell00 = headerRow.getCell(0);
		Cell cell01 = headerRow.getCell(1);
		Cell cell02 = headerRow.getCell(2);
		Cell cell10 = dataRow.getCell(0);
		Cell cell11 = dataRow.getCell(1);
		Cell cell12 = dataRow.getCell(2);

		// texts
		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("Str", cell02.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("", cell11.getStringCellValue());
		Assert.assertEquals("first row string", cell12.getStringCellValue());

		// errors
		Assert.assertEquals(0, datumErrors.size());

		// now append 2 records
		LinkedHashMap<String, Object> record2 = new LinkedHashMap<String, Object>();
		record2.put("primInt", 456);
		record2.put("str",  "row2 string");

		LinkedHashMap<String, Object> record3 = new LinkedHashMap<String, Object>();
		record3.put("primInt",789);
		record3.put("str",  "row3 string");

		// append them
		String datumErrPlaceholder2 = "!!APPENED-ROW-ERROR!!";
		List<DatumError> datumErrors2 = new ArrayList<>();
		Ssio.appendMapsTo(headerMap, Arrays.asList(record2, record3), theFile, datumErrPlaceholder2, datumErrors2);
		// parse again
		spreadsheet = FileUtils.readFileToByteArray(theFile);
		workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions again ***/
		sheet = workbook.getSheetAt(0);
		headerRow = sheet.getRow(0);
		dataRow = sheet.getRow(1);

		Assert.assertEquals(3, sheet.getLastRowNum());

		// the original rows should not be changed
		cell00 = headerRow.getCell(0);
		cell01 = headerRow.getCell(1);
		cell02 = headerRow.getCell(2);
		cell10 = dataRow.getCell(0);
		cell11 = dataRow.getCell(1);
		cell12 = dataRow.getCell(2);

		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("Str", cell02.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("", cell11.getStringCellValue());
		Assert.assertEquals("first row string", cell12.getStringCellValue());

		// now the new row
		Row dataRow2 = sheet.getRow(2);
		Cell cell20 = dataRow2.getCell(0);
		Cell cell21 = dataRow2.getCell(1);
		Cell cell22 = dataRow2.getCell(2);

		Row dataRow3 = sheet.getRow(3);
		Cell cell30 = dataRow3.getCell(0);
		Cell cell31 = dataRow3.getCell(1);
		Cell cell32 = dataRow3.getCell(2);

		Assert.assertEquals("456", cell20.getStringCellValue());
		Assert.assertEquals("", cell21.getStringCellValue());
		Assert.assertEquals("row2 string", cell22.getStringCellValue());

		Assert.assertEquals("789", cell30.getStringCellValue());
		Assert.assertEquals("", cell31.getStringCellValue());
		Assert.assertEquals("row3 string", cell32.getStringCellValue());

		// new errors
		Assert.assertEquals(0, datumErrors2.size());
	}



	
	@Test
	public void saveTest_IgnoringErrors() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("fake", "Not Real");

		ITRecord record = new ITRecord();

		Collection<ITRecord> records = Arrays.asList(record);
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		// save it
		Ssio.save(headerMap, records, outputStream);
		byte[] spreadsheet = outputStream.toByteArray();

		// do a save for human eye check
		FileUtils.writeByteArrayToFile(createFile("saveTest_IngoringErrors"), spreadsheet);

		// then parse it
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		Cell cell00 = headerRow.getCell(0);
		Cell cell10 = dataRow.getCell(0);

		// size
		Assert.assertEquals(1, sheet.getLastRowNum());
		Assert.assertEquals(1, headerRow.getLastCellNum()); // note cell num is
															// 1-based
		Assert.assertEquals(1, dataRow.getLastCellNum());

		// types
		Assert.assertEquals(Cell.CELL_TYPE_STRING, cell00.getCellType());
		Assert.assertEquals(Cell.CELL_TYPE_STRING, cell10.getCellType());

		// texts
		Assert.assertEquals("Not Real", cell00.getStringCellValue());
		Assert.assertEquals("", cell10.getStringCellValue());

	}
	
	@Test
	public void saveTest_IgnoringErrors_File() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("primInt", "Primitive Int");
		headerMap.put("fake", "Not Real");

		ITRecord record = new ITRecord();
		record.setPrimInt(123);

		Collection<ITRecord> records = Arrays.asList(record);
		File outputFile = createFile("saveTest_IgnoringErrors_File");

		// save it
		Ssio.save(headerMap, records, outputFile);	
		

		 
		// then parse it
		byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		Cell cell00 = headerRow.getCell(0);
		Cell cell01 = headerRow.getCell(1);
		Cell cell10 = dataRow.getCell(0);
		Cell cell11 = dataRow.getCell(1);


		// texts
		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("", cell11.getStringCellValue());
		
	}


	@Test
	public void saveMapsTest_IgnoringErrors_File() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("primInt", "Primitive Int");
		headerMap.put("fake", "Not Real");

		Map<String, Object> record = new LinkedHashMap<>();
		record.put("primInt", 123);
		record.put("fake", "someValueAnyway");

		Collection<Map<String, Object>> records = Arrays.asList(record);
		File outputFile = createFile("saveMapsTest_IgnoringErrors_File");

		// save it
		Ssio.saveMaps(headerMap, records, outputFile);


		// then parse it
		byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		Cell cell00 = headerRow.getCell(0);
		Cell cell01 = headerRow.getCell(1);
		Cell cell10 = dataRow.getCell(0);
		Cell cell11 = dataRow.getCell(1);


		// texts
		Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
		Assert.assertEquals("Not Real", cell01.getStringCellValue());
		Assert.assertEquals("123", cell10.getStringCellValue());
		Assert.assertEquals("someValueAnyway", cell11.getStringCellValue());

	}
	
	
	@Test
	public void saveTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
		HeaderUtilsTestRecord record = new HeaderUtilsTestRecord();
		record.setPrimIntProp(1);
		record.setIntObjProp(100);
		record.setStrProp("some string");
		record.setDateProp("2000-01-01 00:00:00");		 
		record.setWriteOnlyProp("would not be saved");

		

		Collection<HeaderUtilsTestRecord> records = Arrays.asList(record);
		File outputFile = createFile("saveTest_UsingGeneratedHeader_File");
		// save it
		Ssio.save(HeaderUtilsTestRecord.class, records, outputFile);
		

		// then parse it
		byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);
		
		List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
		List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
		Map<String, Object> keyValueMap = new LinkedHashMap<>();
		for (int i = 0; i < headerCells.size(); i++) {
			keyValueMap.put(headerCells.get(i), dataCells.get(i));
		}
		
		Assert.assertEquals(6, keyValueMap.size());
		Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
		Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
		Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
		Assert.assertTrue(keyValueMap.containsKey("Date Prop"));
		Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop Str"));
		Assert.assertNull(keyValueMap.get("Read Only Prop"));
		  		
	}

	@Test
	public void saveMapsTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
		Map<String, Object> record = new LinkedHashMap<>();
		record.put("primIntProp", 1);
		record.put("intObjProp", 100);
		record.put("strProp", "some string");
		record.put("dateProp", "2000-01-01 00:00:00");


		Collection<Map<String, Object>> records = Arrays.asList(record);
		File outputFile = createFile("saveMapsTest_UsingGeneratedHeader_File");
		// save it
		Ssio.saveMaps(Arrays.asList("primIntProp", "intObjProp", "strProp", "dateProp"), records, outputFile);


		// then parse it
		byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);
		Row dataRow = sheet.getRow(1);

		List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
		List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
		Map<String, Object> keyValueMap = new LinkedHashMap<>();
		for (int i = 0; i < headerCells.size(); i++) {
			keyValueMap.put(headerCells.get(i), dataCells.get(i));
		}

		Assert.assertEquals(4, keyValueMap.size());
		Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
		Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
		Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
		Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop"));


	}


	private Object getStringOrDateValue(Cell cell) {
		if (cell == null) {
			return null;
		}

		if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
			return null;
		}

		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		}

		if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			return null;
		}

		if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
			return null;
		}

		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			} else {
				double v = cell.getNumericCellValue();
				return String.valueOf(v);
			}
		}

		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			String s = cell.getStringCellValue();
			return StringUtils.trimToNull(s);
		}
		return null;
	}

	private List<Cell> getAllCells(Row row) {
		List<Cell> cells = new ArrayList<>();
		Iterator<Cell> it = row.cellIterator();
		while (it.hasNext()) {
			Cell cell = it.next();
			cells.add(cell);
		}
		return cells;
	}
	

	@Test
	public void saveTest_HeadersOnly() throws InvalidFormatException, IOException {

		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		// save it
		Ssio.save(ITRecord.getHeaderMap(), null, outputStream);
		byte[] spreadsheet = outputStream.toByteArray();

		// do a save for human eye check
		FileUtils.writeByteArrayToFile(createFile("saveTest_HeadersOnly"), spreadsheet);

		// then parse it
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row headerRow = sheet.getRow(0);

		// size
		Assert.assertEquals(0, sheet.getLastRowNum());
		Assert.assertEquals(ITRecord.getHeaderMap().size(), headerRow.getLastCellNum());

	}

	@Test
	public void saveTest_BigNumber() throws InvalidFormatException, IOException {
		LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		headerMap.put("bigInteger", "Big Int");
		headerMap.put("bigDecimal", "Big Decimal");

		String bigIntegerStr = "" + Long.MAX_VALUE;
		String bigDecimalStr = Long.MAX_VALUE + "." + Long.MAX_VALUE;
		ITRecord record = new ITRecord();
		record.setBigInteger(new BigInteger(bigIntegerStr));
		record.setBigDecimal(new BigDecimal(bigDecimalStr));

		Collection<ITRecord> records = Arrays.asList(record);
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		List<DatumError> datumErrors = new ArrayList<DatumError>();

		// save it
		Ssio.save(headerMap, records, outputStream, null, datumErrors);
		byte[] spreadsheet = outputStream.toByteArray();

		// do a save for human eye check
		FileUtils.writeByteArrayToFile(createFile("saveTest_BigNumber"), spreadsheet);

		// then parse it
		Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

		/*** do assertions ***/
		Sheet sheet = workbook.getSheetAt(0);
		Row dataRow = sheet.getRow(1);

		Cell cell10 = dataRow.getCell(0);
		Cell cell11 = dataRow.getCell(1);

		// texts
		Assert.assertEquals(bigIntegerStr, cell10.getStringCellValue());
		Assert.assertEquals(bigDecimalStr, cell11.getStringCellValue());

		// errors
		Assert.assertEquals(0, datumErrors.size());

	}

	@Test(expected = InvalidHeaderRowException.class)
	public void parseTest_InvalidHeader() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-all-headers-wrong.xlsx"));
		Ssio.parse(ITRecord.getReverseHeaderMap(), in, null, ITRecord.class);
	}

	@Test
	public void parseTest_Excel97() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-excel97.xls"));
		List<ITRecord> list = Ssio.parse(ITRecord.getReverseHeaderMap(), in, null, ITRecord.class);
		Assert.assertEquals((short) 1, list.get(0).getPrimShort());
	}

	@Test
	public void parseTest_DataHalfCorrect() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-data-half-correct.xlsx"));
		List<CellError> cellErrors = new ArrayList<CellError>();
		List<ITRecord> records = Ssio.parse(ITRecord.getReverseHeaderMap(), in, cellErrors, ITRecord.class);

		ITRecord record = records.get(0);
		Assert.assertEquals(1, records.size());
		Assert.assertEquals(123, record.getPrimInt());
		Assert.assertNull(record.getStr());

		CellError error = cellErrors.get(0);
		Assert.assertEquals(1, cellErrors.size());
		Assert.assertEquals(2, error.getRowIndexOneBased());
		Assert.assertEquals(3, error.getColumnIndexOneBased());
		Assert.assertTrue(error.getCause().getMessage().contains("suitable setter"));
		Assert.assertTrue(error.getCause().getMessage().contains("abc"));
	}
	
	@Test
	public void parseTest_File() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-data-half-correct.xlsx"));
		File inputFile = createFile("parseTest_File");
		copyInputToFileAndClose(in, inputFile); 		
		List<CellError> cellErrors = new ArrayList<CellError>();
		List<ITRecord> records = Ssio.parse(ITRecord.getReverseHeaderMap(), inputFile, cellErrors, ITRecord.class);

		Assert.assertEquals(1, records.size());
		Assert.assertEquals(1, cellErrors.size());
	}	
	
	@Test
	public void parseTest_IgnoringErrors() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-data-half-correct.xlsx"));		 
		List<ITRecord> records = Ssio.parseIgnoringErrors(ITRecord.getReverseHeaderMap(), in, ITRecord.class);

		ITRecord record = records.get(0);
		Assert.assertEquals(1, records.size());
		Assert.assertEquals(123, record.getPrimInt());
	}
	
	@Test
	public void parseTest_UsingGeneratedReverseHeader_FileAsInput() throws InvalidFormatException, InvalidHeaderRowException, ParseException, IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-for-generated-reverse-header.xlsx"));		 
		File inputFile = createFile("parseTest_UsingGeneratedReverseHeader_FileAsInput");
		copyInputToFileAndClose(in, inputFile); 
		
		List<HeaderUtilsTestRecord> records = Ssio.parseIgnoringErrors(inputFile, HeaderUtilsTestRecord.class);

		HeaderUtilsTestRecord record = records.get(0);
		Assert.assertEquals(1, records.size());
		
		Assert.assertEquals(1, record.getPrimIntProp());
		Assert.assertEquals(Integer.valueOf(100), record.getIntObjProp());
		Assert.assertEquals(DateUtils.parseDate("2000-01-01 00:00:00", new String[] {"yyyy-MM-dd HH:mm:ss"}), record.getDateProp());		 
		Assert.assertNull(record.getReadOnlyProp());
		
		Field writedOnlyPropField = record.getClass().getDeclaredField("writeOnlyProp");
		writedOnlyPropField.setAccessible(true);
		
		Assert.assertEquals("write-only string", writedOnlyPropField.get(record));
 
	}
	
	@Test
	public void parseTest_IgnoringErrors_File() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-data-half-correct.xlsx"));	
		File inputFile = createFile("parseTest_IgnoringErrors_File_As_Input");
		copyInputToFileAndClose(in, inputFile); 
				
		List<ITRecord> records = Ssio.parseIgnoringErrors(ITRecord.getReverseHeaderMap(), inputFile, ITRecord.class);

		ITRecord record = records.get(0);
		Assert.assertEquals(1, records.size());
		Assert.assertEquals(123, record.getPrimInt());
	}


	@Test
	public void parseToMapsTest_IgnoringErrors_File() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-data-half-correct.xlsx"));
		File inputFile = createFile("parseTest_IgnoringErrors_File_As_Input");
		copyInputToFileAndClose(in, inputFile);

		List<Map<String, String>> records = Ssio.parseToMapsIgnoringErrors(ITRecord.getReverseHeaderMap(), inputFile);

		Map<String, String> record = records.get(0);
		Assert.assertEquals(1, records.size());
		Assert.assertEquals("123", record.get("primInt"));  // note it's String
		Assert.assertEquals("abc", record.get("primLong"));  //For map, it's not a data type error
		Assert.assertEquals("2010-11-12T13:14:15.000", record.get("date"));
	}

	@Test
	public void parseTest_AllStringCells() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-all-string-cells-input.xlsx"));
		List<CellError> cellErrors = new ArrayList<CellError>();
		List<ITRecord> records = Ssio.parse(ITRecord.getReverseHeaderMap(), in, cellErrors, ITRecord.class);

		// assertions
		ITRecord record = records.get(0);
		Assert.assertEquals(1, records.size());
		Assert.assertEquals(0, cellErrors.size());

		Assert.assertEquals(12, record.getPrimShort());
		Assert.assertEquals(2323, record.getPrimInt());
		Assert.assertEquals(1213l, record.getPrimLong());
		Assert.assertEquals(342.34f, record.getPrimFloat());
		Assert.assertEquals(0.34, record.getPrimDouble());
		Assert.assertEquals(true, record.isPrimBoolean());

		Assert.assertEquals(new Short("23"), record.getObjShort());
		Assert.assertEquals(new Integer(234), record.getObjInt());
		Assert.assertEquals(new Long(982), record.getObjLong());
		Assert.assertEquals(new Float(483.323f), record.getObjFloat());
		Assert.assertEquals(new Double(23903.234), record.getObjDouble());
		Assert.assertEquals(new Boolean(false), record.getObjBoolean());

		Assert.assertEquals(new BigInteger("123456789123456789"), record.getBigInteger());
		Assert.assertEquals(new BigDecimal("123456789.123456789"), record.getBigDecimal());
		Assert.assertEquals("abc", record.getStr());
		Assert.assertEquals("2014-11-29 16:18:47", record.getDateStr());

	}
	

	@Test
	public void parseTest_FreeTypeCells() throws InvalidFormatException, InvalidHeaderRowException {
		ByteArrayInputStream in = toByteArrayInputStreamAndClose(this.getClass().getResourceAsStream("/parse-test-all-free-type-input.xlsx"));
		List<CellError> cellErrors = new ArrayList<CellError>();
		List<ITRecord> records = Ssio.parse(ITRecord.getReverseHeaderMap(), in, cellErrors, ITRecord.class);

		// assertions
		ITRecord record = records.get(0);
		Assert.assertEquals(1, records.size());
		Assert.assertEquals(0, cellErrors.size());

		Assert.assertEquals(12, record.getPrimShort());
		Assert.assertEquals(2323, record.getPrimInt());
		Assert.assertEquals(1213l, record.getPrimLong());
		// //floats and doubles won't be accurate
		Assert.assertEquals(342, (int) record.getPrimFloat());
		Assert.assertEquals(0, (int) record.getPrimDouble());
		Assert.assertEquals(true, record.isPrimBoolean());

		Assert.assertEquals(new Short("23"), record.getObjShort());
		Assert.assertEquals(new Integer(234), record.getObjInt());
		Assert.assertEquals(new Long(982), record.getObjLong());
		// //floats and doubles won't be accurate
		Assert.assertEquals(483, record.getObjFloat().intValue());
		Assert.assertEquals(23903, record.getObjDouble().intValue());
		Assert.assertEquals(new Boolean(false), record.getObjBoolean());

		Assert.assertEquals(new BigInteger("123456789123456000"), record.getBigInteger());
		Assert.assertEquals(123456789, record.getBigDecimal().intValue());
		Assert.assertEquals("abc", record.getStr());
		Assert.assertEquals("2014-11-29 16:18:47", record.getDateStr());

	}

	// read an outside input stream as bytes and then close it, so as to avoid
	// try/finally snippet code in every parsing test
	// method
	private ByteArrayInputStream toByteArrayInputStreamAndClose(InputStream in) {
		try {
			byte[] bytes = IOUtils.toByteArray(in);
			return new ByteArrayInputStream(bytes);
		} catch (IOException e) {
			throw new IllegalStateException(e);
		} finally {
			IOUtils.closeQuietly(in);
		}

	}
	
	// copy an input stream to File and then close it, so as to avoid
	// try/finally snippet code in every parsing test
	// method
	private void copyInputToFileAndClose(InputStream in, File file) {
		FileOutputStream fileOutput = null;
		try {
			fileOutput = new FileOutputStream(file);
			IOUtils.copy(in, fileOutput);
		} catch (IOException e) {
			throw new IllegalStateException(e);
		} finally {
			IOUtils.closeQuietly(in);
			IOUtils.closeQuietly(fileOutput);
		}

	}

	private File createFile(String prefix) {
		File dir = new File(System.getProperty("java.io.tmpdir"), "/sep4j-it-test");
		dir.mkdirs();
		String filename = prefix + "-" + System.nanoTime() + ".xlsx";
		File file = new File(dir, filename);
		System.out.println("File created: " + file.getAbsolutePath());
		return file;
	}
	
 

	@SuppressWarnings("unused")
	private static class ITRecord {
		private static LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
		static {
			headerMap.put("primShort", "Primitive Short");
			headerMap.put("primInt", "Primitive Int");
			headerMap.put("primLong", "Primitive Long");
			headerMap.put("primFloat", "Primitive Float");
			headerMap.put("primDouble", "Primitive Double");
			headerMap.put("primBoolean", "Primitive Boolean");

			headerMap.put("objShort", "Object Short");
			headerMap.put("objInt", "Object Int");
			headerMap.put("objLong", "Object Long");
			headerMap.put("objFloat", "Object Float");
			headerMap.put("objDouble", "Object Double");
			headerMap.put("objBoolean", "Object Boolean");

			headerMap.put("bigInteger", "Big Integer");
			headerMap.put("bigDecimal", "Big Decimal");
			headerMap.put("str", "String");
			headerMap.put("dateStr", "Date String");
		}
		private short primShort;
		private int primInt;
		private long primLong;
		private float primFloat;
		private double primDouble;
		private boolean primBoolean;

		private Short objShort;
		private Integer objInt;
		private Long objLong;
		private Float objFloat;
		private Double objDouble;
		private Boolean objBoolean;

		private BigInteger bigInteger;
		private BigDecimal bigDecimal;

		private String str;
		private Date date;

		public static LinkedHashMap<String, String> getHeaderMap() {
			return new LinkedHashMap<String, String>(headerMap);
		}

		public static Map<String, String> getReverseHeaderMap() {
			LinkedHashMap<String, String> map = reverse(headerMap);
			map.remove("Date String");
			map.put("Date", "date");
			return map;
		}

		public Date getDate() {
			return date;
		}

		public void setDate(Date date) {
			this.date = date;
		}

		public String getDateStr() {
			if (date == null) {
				return null;
			}
			return DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
		}

		public void setDate(String s) {
			if (s == null) {
				return;
			}
			try {
				Date d = DateUtils.parseDate(s, new String[] { "yyyy-MM-dd HH:mm:ss" });
				this.setDate(d);
			} catch (ParseException e) {
				throw new IllegalArgumentException(e);
			}

		}

		public short getPrimShort() {
			return primShort;
		}

		public void setPrimShort(short primShort) {
			this.primShort = primShort;
		}

		public int getPrimInt() {
			return primInt;
		}

		public void setPrimInt(int primInt) {
			this.primInt = primInt;
		}

		public long getPrimLong() {
			return primLong;
		}

		public void setPrimLong(long primLong) {
			this.primLong = primLong;
		}

		public float getPrimFloat() {
			return primFloat;
		}

		public void setPrimFloat(float primFloat) {
			this.primFloat = primFloat;
		}

		public double getPrimDouble() {
			return primDouble;
		}

		public void setPrimDouble(double primDouble) {
			this.primDouble = primDouble;
		}

		public boolean isPrimBoolean() {
			return primBoolean;
		}

		public void setPrimBoolean(boolean primBoolean) {
			this.primBoolean = primBoolean;
		}

		public Short getObjShort() {
			return objShort;
		}

		public void setObjShort(Short objShort) {
			this.objShort = objShort;
		}

		public Integer getObjInt() {
			return objInt;
		}

		public void setObjInt(Integer objInt) {
			this.objInt = objInt;
		}

		public Long getObjLong() {
			return objLong;
		}

		public void setObjLong(Long objLong) {
			this.objLong = objLong;
		}

		public Float getObjFloat() {
			return objFloat;
		}

		public void setObjFloat(Float objFloat) {
			this.objFloat = objFloat;
		}

		public Double getObjDouble() {
			return objDouble;
		}

		public void setObjDouble(Double objDouble) {
			this.objDouble = objDouble;
		}

		public Boolean getObjBoolean() {
			return objBoolean;
		}

		public void setObjBoolean(Boolean objBoolean) {
			this.objBoolean = objBoolean;
		}

		public BigDecimal getBigDecimal() {
			return bigDecimal;
		}

		public void setBigDecimal(BigDecimal bigDecimal) {
			this.bigDecimal = bigDecimal;
		}

		public BigInteger getBigInteger() {
			return bigInteger;
		}

		public void setBigInteger(BigInteger bigInteger) {
			this.bigInteger = bigInteger;
		}

		public String getStr() {
			return str;
		}

		public void setStr(String str) {
			this.str = str;
		}

	}

	private static <K, V> LinkedHashMap<V, K> reverse(Map<K, V> origMap) {
		LinkedHashMap<V, K> newMap = new LinkedHashMap<V, K>();
		if (origMap == null) {
			origMap = new HashMap<K, V>();
		}
		for (Map.Entry<K, V> entry : origMap.entrySet())
			newMap.put(entry.getValue(), entry.getKey());
		return newMap;
	}

}