public void ReadTest()
{
// header + 5 row
using (var stream = new FileStream("files/Valid.xlsx", FileMode.Open, FileAccess.Read))
{
var workbook = new XSSFWorkbook(stream);
var worksheet = workbook.GetSheetAt(0);
var rowReader = new ExcelRowReader(worksheet, 1, 1, 4);
var row = rowReader.Read();
CollectionAssert.AreEqual(new[] { "ID", "NAME", "PRICE", "REMARKS" }, row.ToArray());
row = rowReader.Read();
Assert.AreEqual(4, row.Count);
row = rowReader.Read();
Assert.AreEqual(4, row.Count);
row = rowReader.Read();
Assert.AreEqual(4, row.Count);
row = rowReader.Read();
Assert.AreEqual(4, row.Count);
row = rowReader.Read();
Assert.AreEqual(4, row.Count);
row = rowReader.Read();
Assert.IsNull(row);
}
}
public void WhenFieldIsNumber_ReturnInvariantLocaleNumber()
{
using (var dataReader = new MockExcelDataReader("Sheet1:2.5"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Equal("2.5", x.Fields[0]));
}
}
public void GivenSingleRowSingleSheetInput_WhenFieldIsDate_ReturnIsoDate()
{
using (var dataReader = new MockExcelDataReader("Sheet1:2012-12-31"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Equal("2012-12-31", x.Fields[0]));
}
}
public void GivenSingleRowSingleSheetInput_ThenSheetReturnsSheetName()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello,World"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Equal("Sheet1", x.Sheet));
}
}
public void GivenSingleRowSingleSheetInput_WhenSheetDoesntMatch_ThenReturnsEmptyEnumerable()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello,World"))
using (var reader = new ExcelRowReader(dataReader, new Regex[] { new Regex("NotSheet1") }))
{
Assert.Empty(reader);
}
}
public void WhenFieldIsUnsupportedDataType_ThrowUnsupportedDataTypeException()
{
using (var dataReader = new MockExcelDataReader("Sheet1:[object]"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Throws <UnsupportedDataTypeException>(() => reader.First());
}
}
public void GivenTwoRowsWithDifferentSheets_ThenSheetReturnsSheetName()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello,World\nSheet2:Foo"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Equal("Sheet1", x.Sheet)
, x => Assert.Equal("Sheet2", x.Sheet));
}
}
public void GivenSingleRowSingleSheetInput_WhenSheetMatchesExpression_ThenReturnSingleIRow()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello,World"))
using (var reader = new ExcelRowReader(dataReader, new Regex[] { new Regex("Sheet1") }))
{
Assert.Collection(reader, x => Assert.Collection(x.Fields, f => Assert.Equal("Hello", f),
f => Assert.Equal("World", f)));
}
}
public void GivenSingleSheetSingleRows_ThenGetSourceFieldReturnsCorrectExcelReference()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello,World"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Collection(x.Fields, f => Assert.Equal("Sheet1!A1", x.GetSourceField(0)),
f => Assert.Equal("Sheet1!B1", x.GetSourceField(1))));
}
}
public void WhenReadingMultipleSheets_ThenRowNumberResetsToOneAtTheStartOfEachSheet()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello\nSheet2:World"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Equal("Sheet1!1", x.GetSourceRow()),
x => Assert.Equal("Sheet2!1", x.GetSourceRow()));
}
}
public void WhenReadingSingleSheetMultipleRows_ThenRowNumberIncreasesMonotonically()
{
using (var dataReader = new MockExcelDataReader("Sheet1:Hello\nSheet1:World"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Equal("Sheet1!1", x.GetSourceRow()),
x => Assert.Equal("Sheet1!2", x.GetSourceRow()));
}
}
public void WhenFieldIsBoolean_ReturnTrueOrFalse()
{
using (var dataReader = new MockExcelDataReader("Sheet1:TRUE,FALSE"))
using (var reader = new ExcelRowReader(dataReader, new Regex[0]))
{
Assert.Collection(reader, x => Assert.Collection(x.Fields,
f => Assert.Equal("True", f),
f => Assert.Equal("False", f)));
}
}
public void ReadRow(ExcelRowReaderHandler handler)
{
SharedStringTable sst = null;
SharedStringTablePart sstpart = this.document.WorkbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (sstpart != null)
{
sst = sstpart.SharedStringTable;
}
int rowNo = 0;
using (OpenXmlReader reader = OpenXmlReader.Create(this.part))
{
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
rowNo++;
ExcelRowReader row = new ExcelRowReader(reader, sst, rowNo);
if (handler(row) == false)
{
break;
}
do
{
if (reader.ElementType == typeof(Row))
{
break;
}
}while (reader.ReadNextSibling());
}
}
}
}
public ExcelCellReader(ExcelRowReader row, Cell cell)
{
this.row = row;
this.cell = cell;
}
