public void GitIssue_29_ReadSheetStatesReadsCorrectly()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xTest_Excel_Dataset")))
{
Assert.AreEqual("hidden", excelReader.VisibleState);
excelReader.NextResult();
Assert.AreEqual("visible", excelReader.VisibleState);
excelReader.NextResult();
Assert.AreEqual("veryhidden", excelReader.VisibleState);
}
}
public void Test_Issue_11601_ReadSheetnames()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xTest_Excel_Dataset")))
{
Assert.AreEqual("test.csv", excelReader.Name);
excelReader.NextResult();
Assert.AreEqual("Sheet2", excelReader.Name);
excelReader.NextResult();
Assert.AreEqual("Sheet3", excelReader.Name);
}
}
public void GitIssue_29_ReadSheetStatesReadsCorrectly()
{
IExcelDataReader excelReader =
ExcelReaderFactory.CreateBinaryReader(Helper.GetTestWorkbook("Test_Excel_Dataset"));
Assert.AreEqual("hidden", excelReader.VisibleState);
excelReader.NextResult();
Assert.AreEqual("visible", excelReader.VisibleState);
excelReader.NextResult();
Assert.AreEqual("veryhidden", excelReader.VisibleState);
}
public void Test_Issue_11601_ReadSheetnames()
{
IExcelDataReader excelReader =
ExcelReaderFactory.CreateBinaryReader(Helper.GetTestWorkbook("Test_Excel_Dataset"));
Assert.AreEqual("test.csv", excelReader.Name);
excelReader.NextResult();
Assert.AreEqual("Sheet2", excelReader.Name);
excelReader.NextResult();
Assert.AreEqual("Sheet3", excelReader.Name);
}
public void UsingExcelDataReader()
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (FileStream xlsFile = File.Open("D:\\repository\\SDET\\javaSDET\\ExcelFiles\\excelDriven.xlsx", FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader sheet = ExcelReaderFactory.CreateReader(xlsFile))
{
do
{
while (sheet.Read()) //reads Each ROW
// At end of Rows will Return False
// and End While loop.
{
for (int column = 0; column < sheet.FieldCount; column++)
{
//Console.WriteLine(reader.GetString(column));//Will blow up if the value is decimal etc.
Console.WriteLine(sheet.GetValue(column));//Get Value returns object
}
}
} while (sheet.NextResult()); //Move to NEXT SHEET
// When ALL the ROWS in sheet are done
// and End While loop.
}
}
}
public static void SelectSheet(IExcelDataReader reader, int sheetNumber)
{
for (var i = 1; i < sheetNumber; i++)
{
reader.NextResult();
}
}
public void Issue_11479_BlankSheet()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(Configuration.GetTestWorkbook("xIssue_11479_BlankSheet")))
{
// DataSet result = excelReader.AsDataSet(true);
excelReader.Read();
Assert.AreEqual(5, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
}
}
static Dictionary <string, List <string[]> > ReadExcelData(TextAsset excelAsset, bool readExtraSheet)
{
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(new MemoryStream(excelAsset.bytes));
Dictionary <string, List <string[]> > result = new Dictionary <string, List <string[]> >();
do
{
List <string[]> properties = new List <string[]>();
while (reader.Read())
{
string[] row = new string[reader.FieldCount];
for (int i = 0; i < row.Length; i++)
{
string data = reader.GetString(i);
row[i] = data == null ? "" : data;
}
if (row[0] != "")
{
properties.Add(row);
}
}
result.Add(reader.Name, properties);
} while (readExtraSheet && reader.NextResult());
return(result);
}
public void Issue_12271_NextResultSet()
{
Excel.Log.Log.InitializeWith <Log4NetLog>();
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xTest_LotsOfSheets"));
//excelReader.IsFirstRowAsColumnNames = true;
do
{
excelReader.Read();
if (excelReader.FieldCount == 0)
{
continue;
}
//ignore sheets beginning with $e
if (excelReader.Name.StartsWith("$e", StringComparison.InvariantCultureIgnoreCase))
{
continue;
}
Assert.AreEqual("StaffName", excelReader.GetString(0));
} while (excelReader.NextResult());
excelReader.Close();
}
private void EnsureCorrectSheetSelected()
{
if (string.IsNullOrWhiteSpace(_sheetName))
{
return;
}
bool isSheetFound;
while (true)
{
isSheetFound = _excelDataReader.Name == _sheetName;
if (isSheetFound)
{
break;
}
if (!_excelDataReader.NextResult())
{
break;
}
}
if (!isSheetFound)
{
throw new InvalidOperationException($"The sheet '{_sheetName}' cannot be found.");
}
}
public List <ExcelModel> ReadFile()
{
_excelReader = ExcelReaderFactory.CreateReader(_stream);
var data = new List <ExcelModel>();
while (_excelReader.Read())
{
if (_excelReader.VisibleState == "hidden")
{
_excelReader.NextResult();
continue;
}
else
{
data.Add(new ExcelModel
{
Column1 = ReadField(0),
Column2 = ReadField(1),
Column3 = ReadField(2),
Column4 = ReadField(3),
Column5 = ReadField(4),
Column6 = ReadField(5),
Column7 = ReadField(6),
Column8 = ReadField(7),
Column9 = ReadField(8),
//Column10 = ReadField(9),
});
}
}
if (ContainsHeaders)
{
data.RemoveRange(0, 1);
}
return(data);
}
public void Issue11479BlankSheet()
{
using (IExcelDataReader excelReader = OpenReader("Test_Issue_11479_BlankSheet"))
{
// DataSet result = excelReader.AsDataSet(true);
excelReader.Read();
Assert.AreEqual(5, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
}
}
public void readReport(string path)
{
bool secondLine = false;
FileStream fs = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
do
{
while (excelReader.Read())
{
if (secondLine == true)
{
if (excelReader.GetString(1) != null)
{
string prsDetails = excelReader.GetString(1);
DateTime date = excelReader.GetDateTime(7);
string detection = excelReader.GetString(10);
string icdLine = excelReader.GetString(11);
compoundInfo temporary = new compoundInfo(prsDetails, icdLine, date, detection);
if (findDuplicatesPatients(temporary) == false)
{
patients.Add(temporary);
ConsoleOutputPatINF(temporary);
}
}
}
secondLine = true;
}
} while (excelReader.NextResult());
fs.Close();
}
/// <summary>
/// Gets the Excel data from the specified file
/// </summary>
/// <returns>The excel data set or null if file is invalid.</returns>
/// <param name="filePath">File path.</param>
private DataSet GetExcelDataSet(string filePath)
{
// Get the excel data reader with the excel data
IExcelDataReader excelReader = GetExcelDataReaderForFile(filePath);
if (excelReader == null)
{
return(null);
}
// Get the data from the excel file
DataSet data = new DataSet();
do
{
// Get the DataTable from the current spreadsheet
DataTable table = GetExcelSheetData(excelReader);
if (table != null)
{
// Add the table to the data set
data.Tables.Add(table);
}
}while(excelReader.NextResult()); // Read the next sheet
return(data);
}
/// <summary>
/// Gets a list of worksheets within the Excel workbook.
/// </summary>
/// <returns>A list of sheets name.</returns>
public List <string> GetSheets()
{
List <string> sheetNames = new List <string>();
try
{
FileInfo excelFile = new FileInfo(Path);
IExcelDataReader excelReader = GetExcelDataReader(excelFile);
while (excelReader.Read())
{
if (!sheetNames.Contains(excelReader.Name))
{
sheetNames.Add(excelReader.Name);
}
excelReader.NextResult();
}
excelReader.Dispose();
}
catch
{ }
return(sheetNames);
}
public List <string> GetWorkseetsList()
{
var result = new List <string>();
try
{
do
{
result.Add(_reader.Name);
} while (_reader.NextResult());
}
catch (Exception ex)
{
Log.Warning(ex, "Failed to get worksheet list");
}
return(result);
}
public void Issue_11479_BlankSheet()
{
IExcelDataReader excelReader =
ExcelReaderFactory.CreateOpenXmlReader(Helper.GetTestWorkbook("xIssue_11479_BlankSheet"));
//DataSet result = excelReader.AsDataSet(true);
excelReader.Read();
Assert.AreEqual(5, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.Close();
}
/// <summary>
/// Converts all sheets to a DataSet
/// </summary>
/// <param name="self">The IExcelDataReader instance</param>
/// <param name="configuration">An optional configuration object to modify the behavior of the conversion</param>
/// <returns>A dataset with all workbook contents</returns>
public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfiguration configuration = null)
{
if (configuration == null)
{
configuration = new ExcelDataSetConfiguration();
}
self.Reset();
var tableIndex = -1;
var result = new DataSet();
do
{
tableIndex++;
if (configuration.FilterSheet != null && !configuration.FilterSheet(self, tableIndex))
{
continue;
}
var tableConfiguration = configuration.ConfigureDataTable != null
? configuration.ConfigureDataTable(self)
: null;
var validatorConfiguration = configuration.ConfigureDataValidator != null
? configuration.ConfigureDataValidator(self)
: null;
if (tableConfiguration == null)
{
tableConfiguration = new ExcelDataTableConfiguration();
}
if (validatorConfiguration == null)
{
validatorConfiguration = new ExcelDataValidatorConfiguration();
}
DataTable errorTable, targetTable;
var table = AsDataTable(self, tableConfiguration, validatorConfiguration, out errorTable, out targetTable);
result.Tables.Add(table);
result.Tables.Add(errorTable);
result.Tables.Add(targetTable);
}while (self.NextResult());
result.AcceptChanges();
if (configuration.UseColumnDataType)
{
FixDataTypes(result);
}
self.Reset();
return(result);
}
private IExcelDataReader SwitchToWorksheet(IExcelDataReader excelDataReader, int index)
{
var current = 0;
while (current < index)
{
excelDataReader.NextResult();
current++;
}
return excelDataReader;
}
private static void MoveToSheet(IExcelDataReader xlsReader, Func <string, bool> namePred)
{
xlsReader.Reset();
while (!namePred(xlsReader.Name))
{
if (!xlsReader.NextResult())
{
throw new Exception("Cannot find sheet");
}
}
}
public List <Hold> Read(string filePath)
{
List <Hold> hold = new List <Hold>();
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
do
{
bool isFirst = true;
while (excelReader.Read())
{
if (excelReader.Name.ToLowerInvariant().Trim() == "hold")
{
if (isFirst) // get column index
{
Console.WriteLine(excelReader.Name);
isFirst = false;
}
else
{
Hold h = new Hold();
h.Name = SafeGetString(excelReader, HoldIndex.Name);
h.WeekDay = SafeGetString(excelReader, HoldIndex.WeekDay);
h.Time = SafeGetString(excelReader, HoldIndex.Time);
h.Place = SafeGetString(excelReader, HoldIndex.Place);
h.Age = SafeGetString(excelReader, HoldIndex.Age);
h.Description = SafeGetString(excelReader, HoldIndex.Description, " Beskrivelse mangler");
h.Responsible = SafeGetString(excelReader, HoldIndex.Responsible);
h.Assistent = SafeGetString(excelReader, HoldIndex.Assistente);
h.HalfSeason = SafeGetString(excelReader, HoldIndex.HalfSeason);
h.StartDate = SafeGetString(excelReader, HoldIndex.StartDate);
h.Price = SafeGetString(excelReader, HoldIndex.Price, "Ikke angivet");
h.Comments = SafeGetString(excelReader, HoldIndex.Comments);
h.Number = SafeGetString(excelReader, HoldIndex.Number);
h.Min = SafeGetString(excelReader, HoldIndex.Min);
h.Max = SafeGetString(excelReader, HoldIndex.Max);
h.Waiting = SafeGetString(excelReader, HoldIndex.Waiting);
h.Image = SafeGetString(excelReader, HoldIndex.Image, "");
h.Status = SafeGetString(excelReader, HoldIndex.Status, "");
hold.Add(h);
}
}
}
} while (excelReader.NextResult());
}
}
return(hold);
}
public T ReadAllWorksheets <T>(IExcelDataReader reader, FluentConfig config = null) where T : class, new()
{
var dataFromExcel = new T();
var sheetProcessors = new List <SheetProcessingData>();
List <TablePropertyData> tables = null;
if (config != null)
{
tables = config.Tables;
}
else
{
tables = GetTableProperties(typeof(T));
}
foreach (var table in tables)
{
sheetProcessors.Add(new SheetProcessingData(table, CreateSetPropertyAction(typeof(T), table.PropertyName))); //table.ExcelSheetName
}
var hasNextResult = false;
var readerWasReset = false;
do
{
readerWasReset = false;
var worksheetName = reader.Name;
var currentSheetTables = sheetProcessors.Where(m => m.TablePropertyData.ExcelSheetName.Equals(worksheetName, StringComparison.OrdinalIgnoreCase) && m.Processed == false).ToList();
if (currentSheetTables.Count > 0)
{
var processor = currentSheetTables.First();
var result = ProcessTable(reader, processor.TablePropertyData);
processor.PropertySetter(dataFromExcel, result);
processor.Processed = true;
// handling cases (experimental), where multiple tables (classes) are bound to one excel sheet
if (currentSheetTables.Count > 1)
{
reader.Reset(); // ToDo: this will kill performance on large files. Reading should be refactored to be able to read one sheet to different tables simultaneously...
readerWasReset = true;
}
}
if (!readerWasReset)
{
hasNextResult = reader.NextResult();
}
} while (hasNextResult);
return(dataFromExcel);
}
public Trade[] ReadCSV(IExcelDataReader reader)
{
List <Trade> result = new List <Trade>();
do
{
while (reader.Read() && reader != null)
{
try
{
var x = reader;
DateTime time = DateTime.Parse(reader.GetString(0));
var i = 0;
var price = Convert.ToDouble(reader.GetString(1));
var test = reader.GetString(2);
var volume = Convert.ToInt32(reader.GetString(2).Replace(",", ""));
var value = Convert.ToInt32(reader.GetString(3).Replace(",", ""));
string reasonString = reader.GetString(4);
Reason reason;
switch (reasonString)
{
case "ASK":
reason = Reason.ASK;
break;
case "BID":
reason = Reason.BID;
break;
case "MATCH":
reason = Reason.MATCH;
break;
default:
reason = Reason.ASK;
break;
}
if (time != null || price != 0 || volume != 0 || value != 0)
{
result.Add(new Trade(time: time, price: price, value: value, volume: volume, reason: reason));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
continue; // Try to read the next row
}
}
} while (reader.NextResult());
return(result.ToArray());
}
public static List <ExcelStateObj> ReadExcel(string path)
{
MemoryStream memory = new MemoryStream(LoadFilebytes(path));
List <ExcelStateObj> list = new List <ExcelStateObj>();
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(memory);
do
{
// sheet name
//Debug.Log(excelReader.Name);
List <RowStateObj> rowList = new List <RowStateObj>();
int rowCount = 0;
int colCount = 0;
while (excelReader.Read())
{
bool isAdd = false;
RowStateObj row = new RowStateObj(excelReader.FieldCount);
colCount = Mathf.Max(excelReader.FieldCount, colCount);
for (int i = 0; i < colCount; i++)
{
CellStateObj cell = new CellStateObj();
row.Cells[i] = cell;
if (i < excelReader.FieldCount)
{
string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
cell.Value = value;
if (!string.IsNullOrEmpty(value))
{
isAdd = true;
}
}
}
if (isAdd)
{
rowCount++;
rowList.Add(row);
}
}
ExcelStateObj excelStateObj = new ExcelStateObj(rowCount, colCount);
excelStateObj.ExcelName = excelReader.Name;
excelStateObj.Rows = rowList.ToArray();
list.Add(excelStateObj);
}while(excelReader.NextResult());
//DataSet result = excelReader.AsDataSet();
excelReader.Close();
excelReader.Dispose();
memory.Close();
memory.Dispose();
return(list);
}
private static void GotoSheetNumber(IExcelDataReader reader, int sheetNumber)
{
var currentSheet = 1;
while (currentSheet < reader.ResultsCount)
{
if (currentSheet == sheetNumber)
{
return;
}
reader.NextResult();
currentSheet++;
}
}
private DataSet GetExcelDataSet(int recordLimit)
{
DataSet dataSet = new DataSet();
FileInfo excelFile = new FileInfo(Path);
IExcelDataReader excelReader = GetExcelDataReader(excelFile);
while (excelReader.Read() && dataSet.Tables.Count == 0)
{
if (excelReader.Name == SheetName)
{
dataSet = excelReader.AsDataSet();
if (firstRowHeaders && dataSet.Tables[SheetName].Rows.Count >= 1)
{
for (int columnIndex = 0; columnIndex < dataSet.Tables[SheetName].Columns.Count; columnIndex++)
{
string columnName = dataSet.Tables[SheetName].Rows[0][columnIndex].ToString();
if (!dataSet.Tables[SheetName].Columns.Contains(columnName))
{
dataSet.Tables[SheetName].Columns[columnIndex].ColumnName = columnName;
dataSet.Tables[SheetName].Columns[columnIndex].Caption = columnName;
}
else
{
throw new ApplicationException(string.Format("Please remove the duplicate '{0}' column from the {1} worksheet", columnName, sheetName));
}
}
dataSet.Tables[SheetName].Rows[0].Delete();
dataSet.AcceptChanges();
}
for (int rowIndex = 0; rowIndex < dataSet.Tables[SheetName].Rows.Count && recordLimit != ALL_RECORDS_LIMIT; rowIndex++)
{
if (rowIndex >= recordLimit)
{
dataSet.Tables[SheetName].Rows[rowIndex].Delete();
dataSet.AcceptChanges();
}
}
}
excelReader.NextResult();
}
excelReader.Dispose();
return(dataSet);
}
public List <string> GetTableName(string ExcelName, string Path)
{
ExcelNameData = ExcelName;
FileStream stream = File.Open(Path + "" + ExcelName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
List <string> TableName = new List <string>();
do
{
TableName.Add(excelReader.Name);
} while (excelReader.NextResult());
stream.Close();
return(TableName);
}
private void ButtonLoad_Click(object sender, EventArgs e)
{
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xls;*.xlsx", ValidateNames = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
try
{
MessageBox.Show(ofd.FileName);
fileStream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read); //Dosya Kullanımdaysa hata veriyor..
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
isFileLoaded = true;
LabelFileName.Text = ofd.SafeFileName;
path = ofd.FileName.Replace(ofd.SafeFileName, "");
IExcelDataReader reader = ExcelReaderFactory.CreateReader(fileStream);
result = reader.AsDataSet();
cellTypes = new Dictionary <int, Type[, ]>();
for (int workSheetIndex = 0; workSheetIndex < reader.ResultsCount; workSheetIndex++)
{
Type[,] types = new Type[reader.RowCount, reader.FieldCount];
int rowIndex = 0;
while (reader.Read())
{
for (int columnIndex = 0; columnIndex <= reader.FieldCount - 1; columnIndex++)
{
types[rowIndex, columnIndex] = reader.GetFieldType(columnIndex);
}
rowIndex++;
}
cellTypes.Add(workSheetIndex, types);
reader.NextResult();
}
reader.Close();
reader.Dispose();
reader = null;
}
}
}
private static void SelectSheet(IExcelDataReader reader, string sheetName)
{
var currentSheet = 1;
while (reader.Name != sheetName)
{
reader.NextResult();
currentSheet++;
if (currentSheet == reader.ResultsCount)
{
throw new Exception($"Sheet {sheetName} not found");
}
}
}
public Trade[] ReadXlsx(IExcelDataReader reader)
{
List <Trade> result = new List <Trade>();
do
{
while (reader.Read())
{
try
{
var time = reader.GetValue(0);
var price = reader.GetValue(1);
var volume = reader.GetValue(2);
var value = reader.GetValue(3);
var reasonString = reader.GetValue(4);
if (time != null && price != null && volume != null && value != null && reasonString != null)
{
Reason reason;
switch (reasonString)
{
case "ASK":
reason = Reason.ASK;
break;
case "BID":
reason = Reason.BID;
break;
case "MATCH":
reason = Reason.MATCH;
break;
default:
reason = Reason.ASK;
break;
}
result.Add(new Trade(time: Convert.ToDateTime(time), price: Convert.ToInt32(price), volume: Convert.ToInt32(volume), value: Convert.ToInt32(value), reason: reason));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
continue; // Try to read the next row
}
}
} while (reader.NextResult());
return(result.ToArray());
}
/// <summary>
/// Changes to using the passed in sheet. Note that changing to a new sheet automatically resets the
/// internal row counter used by GetRecords.
/// </summary>
/// <param name="sheet">Sheet to change to (0 to TotalSheets - 1)</param>
/// <returns>True on success, false if the sheet is out of range</returns>
public bool ChangeSheet(
int sheet)
{
if (sheet >= _reader.ResultsCount)
{
return(false);
}
_reader.Reset();
for (var i = 0; i < sheet; i++)
{
_reader.NextResult();
}
_row = 0;
return(true);
}
public static void DoOpenOfficeTest(IExcelDataReader excelReader)
{
Assert.IsTrue(excelReader.IsValid);
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual("column a", excelReader.GetString(0));
Assert.AreEqual(" column b", excelReader.GetString(1));
Assert.AreEqual(" column b", excelReader.GetString(2));
Assert.IsNull(excelReader.GetString(3));
Assert.AreEqual("column e", excelReader.GetString(4));
Assert.AreEqual(" column b", excelReader.GetString(5));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(2, excelReader.GetInt32(0));
Assert.AreEqual("b", excelReader.GetString(1));
Assert.AreEqual("c", excelReader.GetString(2));
Assert.AreEqual("d", excelReader.GetString(3));
Assert.AreEqual(" e ", excelReader.GetString(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(3, excelReader.GetInt32(0));
Assert.AreEqual(2, excelReader.GetInt32(1));
Assert.AreEqual(3, excelReader.GetInt32(2));
Assert.AreEqual(4, excelReader.GetInt32(3));
Assert.AreEqual(5, excelReader.GetInt32(4));
excelReader.Read();
Assert.AreEqual(6, excelReader.FieldCount);
Assert.AreEqual(4, excelReader.GetInt32(0));
Assert.AreEqual(new DateTime(2012, 10, 13), excelReader.GetDateTime(1));
Assert.AreEqual(new DateTime(2012, 10, 14), excelReader.GetDateTime(2));
Assert.AreEqual(new DateTime(2012, 10, 15), excelReader.GetDateTime(3));
Assert.AreEqual(new DateTime(2012, 10, 16), excelReader.GetDateTime(4));
for (int i = 4; i < 34; i++)
{
excelReader.Read();
Assert.AreEqual(i + 1, excelReader.GetInt32(0));
Assert.AreEqual(i + 2, excelReader.GetInt32(1));
Assert.AreEqual(i + 3, excelReader.GetInt32(2));
Assert.AreEqual(i + 4, excelReader.GetInt32(3));
Assert.AreEqual(i + 5, excelReader.GetInt32(4));
}
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
excelReader.NextResult();
excelReader.Read();
Assert.AreEqual(0, excelReader.FieldCount);
//test dataset
DataSet result = excelReader.AsDataSet(true);
Assert.AreEqual(1, result.Tables.Count);
Assert.AreEqual(6, result.Tables[0].Columns.Count);
Assert.AreEqual(33, result.Tables[0].Rows.Count);
Assert.AreEqual("column a", result.Tables[0].Columns[0].ColumnName);
Assert.AreEqual(" column b", result.Tables[0].Columns[1].ColumnName);
Assert.AreEqual(" column b_1", result.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("Column3", result.Tables[0].Columns[3].ColumnName);
Assert.AreEqual("column e", result.Tables[0].Columns[4].ColumnName);
Assert.AreEqual(" column b_2", result.Tables[0].Columns[5].ColumnName);
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[0][0]));
Assert.AreEqual("b", result.Tables[0].Rows[0][1]);
Assert.AreEqual("c", result.Tables[0].Rows[0][2]);
Assert.AreEqual("d", result.Tables[0].Rows[0][3]);
Assert.AreEqual(" e ", result.Tables[0].Rows[0][4]);
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][0]));
Assert.AreEqual(2, Convert.ToInt32(result.Tables[0].Rows[1][1]));
Assert.AreEqual(3, Convert.ToInt32(result.Tables[0].Rows[1][2]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[1][3]));
Assert.AreEqual(5, Convert.ToInt32(result.Tables[0].Rows[1][4]));
Assert.AreEqual(4, Convert.ToInt32(result.Tables[0].Rows[2][0]));
Assert.AreEqual(new DateTime(2012, 10, 13), result.Tables[0].Rows[2][1]);
Assert.AreEqual(new DateTime(2012, 10, 14), result.Tables[0].Rows[2][2]);
Assert.AreEqual(new DateTime(2012, 10, 15), result.Tables[0].Rows[2][3]);
Assert.AreEqual(new DateTime(2012, 10, 16), result.Tables[0].Rows[2][4]);
for (int i = 4; i < 33; i++)
{
Assert.AreEqual(i + 2, Convert.ToInt32(result.Tables[0].Rows[i][0]));
Assert.AreEqual(i + 3, Convert.ToInt32(result.Tables[0].Rows[i][1]));
Assert.AreEqual(i + 4, Convert.ToInt32(result.Tables[0].Rows[i][2]));
Assert.AreEqual(i + 5, Convert.ToInt32(result.Tables[0].Rows[i][3]));
Assert.AreEqual(i + 6, Convert.ToInt32(result.Tables[0].Rows[i][4]));
}
excelReader.Close();
}