public void Can_read_worksheet_from_excel_workbook()
{
string excelFile = "MyWorkbook.xls";
MemoryStream excelFileStream = new MemoryStream();
DataSet workbook = createWorkbook();
MockRepository mocks = new MockRepository();
IFileSystem fileSystem = mocks.CreateMock <IFileSystem>();
IExcelWorkbookReader workbookReader = mocks.CreateMock <IExcelWorkbookReader>();
Expect.Call(fileSystem.ReadIntoFileStream(excelFile)).Return(excelFileStream);
Expect.Call(workbookReader.GetWorkbookData(excelFileStream)).Return(workbook);
mocks.ReplayAll();
IExcelWorksheetReader worksheetReader = new ExcelWorksheetReader(fileSystem, workbookReader);
DataTable myWorksheet = worksheetReader.GetWorksheet(excelFile, "MyWorksheet");
Assert.That(myWorksheet, Is.Not.Null);
Assert.That(myWorksheet.Columns.Count, Is.EqualTo(2));
Assert.That(myWorksheet.Columns[0].ColumnName, Is.EqualTo("First Column"));
Assert.That(myWorksheet.Columns[1].ColumnName, Is.EqualTo("Second Column"));
Assert.That(myWorksheet.Rows.Count, Is.EqualTo(2));
Assert.That(myWorksheet.Rows[0]["First Column"], Is.EqualTo("Row 1 - First Column Value"));
Assert.That(myWorksheet.Rows[0]["Second Column"], Is.EqualTo("Row 1 - Second Column Value"));
Assert.That(myWorksheet.Rows[1]["First Column"], Is.EqualTo("Row 2 - First Column Value"));
Assert.That(myWorksheet.Rows[1]["Second Column"], Is.EqualTo("Row 2 - Second Column Value"));
mocks.VerifyAll();
}
public void Handles_scenario_where_worksheet_has_no_rows()
{
string excelFile = "MyWorkbook.xls";
MemoryStream excelFileStream = new MemoryStream();
DataSet workbook = new DataSet();
workbook.Tables.Add("MyWorksheet");
workbook.Tables["MyWorksheet"].Columns.Add();
DataRow columnHeaderRow = workbook.Tables["MyWorksheet"].NewRow();
columnHeaderRow[0] = "First Column";
workbook.Tables["MyWorksheet"].Rows.Add(columnHeaderRow);
MockRepository mocks = new MockRepository();
IFileSystem fileSystem = mocks.CreateMock <IFileSystem>();
IExcelWorkbookReader workbookReader = mocks.CreateMock <IExcelWorkbookReader>();
Expect.Call(fileSystem.ReadIntoFileStream(excelFile)).Return(excelFileStream);
Expect.Call(workbookReader.GetWorkbookData(excelFileStream)).Return(workbook);
mocks.ReplayAll();
IExcelWorksheetReader worksheetReader = new ExcelWorksheetReader(fileSystem, workbookReader);
worksheetReader.GetWorksheet(excelFile, "MyWorksheet");
mocks.VerifyAll();
}
public void Does_not_continue_reading_after_a_blank_row_is_found()
{
string excelFile = "MyWorkbook.xls";
MemoryStream excelFileStream = new MemoryStream();
DataSet workbook = new DataSet();
workbook.Tables.Add(new DataTable("MyWorksheet"));
workbook.Tables["MyWorksheet"].Columns.Add();
DataRow columnHeaders = workbook.Tables["MyWorksheet"].NewRow();
columnHeaders[0] = "First Column";
workbook.Tables["MyWorksheet"].Rows.Add(columnHeaders);
DataRow firstRow = workbook.Tables["MyWorksheet"].NewRow();
firstRow[0] = "Row 1 - First Column Value";
workbook.Tables["MyWorksheet"].Rows.Add(firstRow);
DataRow secondRow = workbook.Tables["MyWorksheet"].NewRow();
secondRow[0] = "Row 2 - First Column Value";
workbook.Tables["MyWorksheet"].Rows.Add(secondRow);
DataRow thirdRow = workbook.Tables["MyWorksheet"].NewRow();
workbook.Tables["MyWorksheet"].Rows.Add(thirdRow);
DataRow fourthRow = workbook.Tables["MyWorksheet"].NewRow();
fourthRow[0] = "Row 4 - First Column Value";
workbook.Tables["MyWorksheet"].Rows.Add(fourthRow);
MockRepository mocks = new MockRepository();
IFileSystem fileSystem = mocks.CreateMock <IFileSystem>();
IExcelWorkbookReader workbookReader = mocks.CreateMock <IExcelWorkbookReader>();
Expect.Call(fileSystem.ReadIntoFileStream(excelFile)).Return(excelFileStream);
Expect.Call(workbookReader.GetWorkbookData(excelFileStream)).Return(workbook);
mocks.ReplayAll();
IExcelWorksheetReader worksheetReader = new ExcelWorksheetReader(fileSystem, workbookReader);
DataTable myWorksheet = worksheetReader.GetWorksheet(excelFile, "MyWorksheet");
Assert.That(myWorksheet, Is.Not.Null);
Assert.That(myWorksheet.Columns.Count, Is.EqualTo(1));
Assert.That(myWorksheet.Columns[0].ColumnName, Is.EqualTo("First Column"));
Assert.That(myWorksheet.Rows.Count, Is.EqualTo(2));
Assert.That(myWorksheet.Rows[0]["First Column"], Is.EqualTo("Row 1 - First Column Value"));
Assert.That(myWorksheet.Rows[1]["First Column"], Is.EqualTo("Row 2 - First Column Value"));
mocks.VerifyAll();
}
public DataTable GetWorksheet(string filePath, string worksheetName)
{
DataTable table;
using (Stream fileStream = _fileSystem.ReadIntoFileStream(filePath))
{
DataTableCollection workbookData = _excelWorkbookReader.GetWorkbookData(fileStream).Tables;
if (workbookData.IndexOf(worksheetName) > -1)
{
table = workbookData[worksheetName];
modifyColumnNames(table);
}
else
{
string message = string.Format("The workbook is missing worksheet named '{0}'", worksheetName);
throw new ApplicationException(message);
}
}
return(table);
}
public void Handles_scenario_where_invalid_worksheet_requested()
{
string excelFile = "MyWorkbook.xls";
MemoryStream excelFileStream = new MemoryStream();
DataSet workbook = createWorkbook();
MockRepository mocks = new MockRepository();
IFileSystem fileSystem = mocks.CreateMock <IFileSystem>();
IExcelWorkbookReader workbookReader = mocks.CreateMock <IExcelWorkbookReader>();
Expect.Call(fileSystem.ReadIntoFileStream(excelFile)).Return(excelFileStream);
Expect.Call(workbookReader.GetWorkbookData(excelFileStream)).Return(workbook);
mocks.ReplayAll();
IExcelWorksheetReader worksheetReader = new ExcelWorksheetReader(fileSystem, workbookReader);
worksheetReader.GetWorksheet(excelFile, "MyOtherWorksheet");
mocks.VerifyAll();
}
