public void Issue_EmptyCells_5320_Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/TestEmptyCells_5320.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
excelReader.Close();
string val1 = new DateTime(2009, 1, 31).ToShortDateString();
string val2Unparsed = workSheets[0].Rows[4].Values[1].ToString();
Assert.IsFalse(string.IsNullOrEmpty(val2Unparsed));
string val2 = DateTime.Parse(val2Unparsed).ToShortDateString();
Assert.AreEqual(val1, val2);
val1 = new DateTime(2009, 2, 28).ToShortDateString();
val2Unparsed = workSheets[0].Rows[4].Values[7].ToString();
Assert.IsFalse(string.IsNullOrEmpty(val2Unparsed));
val2 = DateTime.Parse(val2Unparsed).ToShortDateString();
Assert.AreEqual(val1, val2);
}
public void DoublePrecisionTest()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/TestDoublePrecision.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
double excelPI = 3.1415926535897900;
Assert.AreEqual(+excelPI, ParseDouble(workSheets[0].Rows[2].Values[1].ToString()), 1e-14);
Assert.AreEqual(-excelPI, ParseDouble(workSheets[0].Rows[3].Values[1].ToString()), 1e-14);
Assert.AreEqual(+excelPI * 1.0e-300, ParseDouble(workSheets[0].Rows[4].Values[1].ToString()), 3e-315);
Assert.AreEqual(-excelPI * 1.0e-300, ParseDouble(workSheets[0].Rows[5].Values[1].ToString()), 3e-315);
Assert.AreEqual(+excelPI * 1.0e300, ParseDouble(workSheets[0].Rows[6].Values[1].ToString()), 3e+285);
Assert.AreEqual(-excelPI * 1.0e300, ParseDouble(workSheets[0].Rows[7].Values[1].ToString()), 3e+287);
Assert.AreEqual(+excelPI * 1.0e15, ParseDouble(workSheets[0].Rows[8].Values[1].ToString()), 3e+1);
Assert.AreEqual(-excelPI * 1.0e15, ParseDouble(workSheets[0].Rows[9].Values[1].ToString()), 3e+1);
excelReader.Close();
}
public void Test_num_double_date_bool_string()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/Test_num_double_date_bool_string.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(30, workSheets[0].Rows.Count);
Assert.AreEqual(6, workSheets[0].Columns.Count);
Assert.AreEqual(1, int.Parse(workSheets[0].Rows[0].Values[0].ToString()));
Assert.AreEqual(1346269, int.Parse(workSheets[0].Rows[29].Values[0].ToString()));
//double + Formula
Assert.AreEqual(1.02, double.Parse(workSheets[0].Rows[0].Values[1].ToString()));
Assert.AreEqual(4.08, double.Parse(workSheets[0].Rows[2].Values[1].ToString()));
Assert.AreEqual(547608330.24, double.Parse(workSheets[0].Rows[29].Values[1].ToString()));
//Date + Formula
Assert.AreEqual(new DateTime(2009, 5, 11).ToShortDateString(), DateTime.Parse(workSheets[0].Rows[0].Values[2].ToString()).ToShortDateString());
Assert.AreEqual(new DateTime(2009, 11, 30).ToShortDateString(), DateTime.Parse(workSheets[0].Rows[29].Values[2].ToString()).ToShortDateString());
//Custom Date Time + Formula
Assert.AreEqual(new DateTime(2009, 5, 7).ToShortDateString(), DateTime.Parse(workSheets[0].Rows[0].Values[5].ToString()).ToShortDateString());
Assert.AreEqual(new DateTime(2009, 5, 8, 11, 1, 2), DateTime.Parse(workSheets[0].Rows[1].Values[5].ToString()));
//DBNull value (dont have dbnull when not using datatable
Assert.IsNull(workSheets[0].Rows[1].Values[4]);
excelReader.Close();
}
public IWorkBook AsWorkBook(bool convertOaDateTime)
{
if (!IsValid)
{
return(null);
}
if (IsClosed)
{
return(_WorkbookData);
}
_ConvertOaDate = convertOaDateTime;
_WorkbookData = WorkBookFactory.CreateWorkBook();
for (var index = 0; index < ResultsCount; index++)
{
var table = ReadWholeWorkSheet(_Sheets[index], _WorkbookData);
if (null != table)
{
_WorkbookData.WorkSheets.Add(table);
}
}
_File.Close();
IsClosed = true;
return(_WorkbookData);
}
public void MultiSheetTest()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/TestMultiSheet.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(3, workSheets.Count);
Assert.AreEqual(4, workSheets.FirstOrDefault(ws => ws.Name == "Sheet1").Columns.Count);
Assert.AreEqual(12, workSheets.FirstOrDefault(ws => ws.Name == "Sheet1").Rows.Count);
Assert.AreEqual(4, workSheets.FirstOrDefault(ws => ws.Name == "Sheet2").Columns.Count);
Assert.AreEqual(12, workSheets.FirstOrDefault(ws => ws.Name == "Sheet2").Rows.Count);
Assert.AreEqual(2, workSheets.FirstOrDefault(ws => ws.Name == "Sheet3").Columns.Count);
Assert.AreEqual(5, workSheets.FirstOrDefault(ws => ws.Name == "Sheet3").Rows.Count);
var wks2 = workSheets.FirstOrDefault(ws => ws.Name == "Sheet2");
Assert.AreEqual("1", wks2.Rows[11].Values[0].ToString());
var wks1 = workSheets.FirstOrDefault(ws => ws.Name == "Sheet1");
Assert.AreEqual("2", wks1.Rows[11].Values[3].ToString());
var wks3 = workSheets.FirstOrDefault(ws => ws.Name == "Sheet3");
Assert.AreEqual("3", wks3.Rows[4].Values[1].ToString());
excelReader.Close();
}
public void Issue_Encoding_1520_Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/Test_Encoding_Formula_Date_1520.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
string val1 = "Simon Hodgetts";
string val2 = workSheets[0].Rows[2].Values[0].ToString();
Assert.AreEqual(val1, val2);
val1 = "John test";
val2 = workSheets[0].Rows[1].Values[0].ToString();
Assert.AreEqual(val1, val2);
//librement réutilisable
val1 = "librement réutilisable";
val2 = workSheets[0].Rows[7].Values[0].ToString();
Assert.AreEqual(val1, val2);
val2 = workSheets[0].Rows[8].Values[0].ToString();
Assert.AreEqual(val1, val2);
excelReader.Close();
}
public void Test_Toyota()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/AMAXX.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual("TOYOTA", workSheets[0].Rows[7].Values[5]);
}
public void UnicodeCharsTest()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/TestUnicodeChars.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(3, workSheets[0].Rows.Count);
Assert.AreEqual(8, workSheets[0].Columns.Count);
//missing
excelReader.Close();
}
private string GetInfo(IWorkBook workBook)
{
StringBuilder sb = new StringBuilder();
foreach (var sheet in workBook.Worksheets)
{
sb.AppendLine("sheet : " + sheet.Name);
foreach (var rowMap in sheet.GetAllRows())
{
sb.AppendLine("row " + rowMap.Key + ": " + GetRow(rowMap.Value));
}
}
return(sb.ToString());
}
public void Test_Genes()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/Genes.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(101526.9, double.Parse(workSheets[0].Rows[5].Values[6].ToString()));
Assert.AreEqual(38713.79, double.Parse(workSheets[0].Rows[24].Values[7].ToString()));
Assert.AreEqual("101526.9", workSheets[0].Rows[5].Values[6]);
Assert.AreEqual("38713.79", workSheets[0].Rows[24].Values[7]);
}
public void Dimension10x10Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(GetTestWorkBook("Resources/Test10x10.xls"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(10, workSheets[0].Rows.Count);
Assert.AreEqual(10, workSheets[0].Columns.Count);
Assert.AreEqual("10x10", workSheets[0].Rows[1].Values[0]);
Assert.AreEqual("10x27", workSheets[0].Rows[9].Values[9]);
excelReader.Close();
}
public void AsDataSet_Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/TestChess.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.IsTrue(excelWorkBook != null);
Assert.AreEqual(1, workSheets.Count);
Assert.AreEqual(4, workSheets[0].Rows.Count);
Assert.AreEqual(6, workSheets[0].Columns.Count);
excelReader.Close();
}
public void Dimension255x10Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/Test255x10.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(10, workSheets[0].Rows.Count);
Assert.AreEqual(255, workSheets[0].Columns.Count);
Assert.AreEqual("1", workSheets[0].Rows[9].Values[254].ToString());
Assert.AreEqual("one", workSheets[0].Rows[1].Values[1].ToString());
excelReader.Close();
}
private static IWorkBook TryOpenByAllWay(Stream stream)
{
IWorkBook result = null;
int index = m_Funcs.Length - 1;
while (index >= 0)
{
result = TryOpen(stream, index);
if (result != null)
{
break;
}
index--;
}
return(result);
}
public static IWorkBook Open(Stream stream)
{
IWorkBook result = null;
try
{
result = TryOpenByAllWay(stream);
}
finally
{
if (stream.CanRead)
{
stream.Close();
}
}
return(result);
}
private static IWorkBook TryOpen(Stream stream, int index)
{
IWorkBook result = null;
try
{
result = m_Funcs[index](stream);
}
catch (NotSupportedException ex)
{
if (index < 0)
{
throw ex;
}
}
return(result);
}
public void ChessTest()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/TestChess.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(4, workSheets[0].Rows.Count);
Assert.AreEqual(6, workSheets[0].Columns.Count);
Assert.AreEqual("1", workSheets[0].Rows[3].Values[5].ToString());
Assert.AreEqual("1", workSheets[0].Rows[2].Values[0].ToString());
excelReader.Close();
}
public void Issue_Decimal_1109_Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/Test_Decimal_1109.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
Assert.AreEqual(Double.Parse("3.14159"), Double.Parse(workSheets[0].Rows[0].Values[0].ToString()));
double val1 = -7080.61;
double val2 = Double.Parse(workSheets[0].Rows[0].Values[1].ToString());
Assert.AreEqual(val1, val2);
excelReader.Close();
}
public void Issue_Date_and_Time_1468_Test()
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(GetTestWorkBook("Resources/Test_Encoding_Formula_Date_1520.xlsx"));
excelReader.WorkBookFactory = new ExcelWorkBookFactory();
IWorkBook excelWorkBook = excelReader.AsWorkBook();
List <IWorkSheet> workSheets = (List <IWorkSheet>)excelWorkBook.WorkSheets;
string val1 = new DateTime(2009, 05, 01).ToShortDateString();
string val2 = DateTime.Parse(workSheets[0].Rows[1].Values[1].ToString()).ToShortDateString();
Assert.AreEqual(val1, val2);
val1 = DateTime.Parse("11:00:00").ToShortTimeString();
val2 = DateTime.Parse(workSheets[0].Rows[2].Values[4].ToString()).ToShortTimeString();
Assert.AreEqual(val1, val2);
excelReader.Close();
}
private IWorkSheet ReadWholeWorkSheet(XlsWorksheet sheet, IWorkBook workBook)
{
XlsBiffIndex idx;
if (!ReadWorkSheetGlobals(sheet, out idx)) return null;
var workSheet = workBook.CreateWorkSheet();
workSheet.Name = sheet.Name;
var triggerCreateColumns = true;
_DatabaseCellAddresses = idx.DbCellAddresses;
for (var index = 0; index < _DatabaseCellAddresses.Length; index++)
{
if (Depth == _MaxRowIndex) break;
// init reading data
_CellOffset = FindFirstDataCellOffset((int)_DatabaseCellAddresses[index]);
//DataTable columns
if (triggerCreateColumns)
{
if (IsFirstRowAsColumnNames && ReadWorkSheetRow())
{
for (var i = 0; i < FieldCount; i++)
{
if (_CellsValues[i] != null && _CellsValues[i].ToString().Length > 0)
{
var column = workSheet.CreateDataColumn();
column.ColumnName = _CellsValues[i].ToString();
workSheet.Columns.Add(column);
}
else
{
var column = workSheet.CreateDataColumn();
column.ColumnName = String.Concat(Column, i);
workSheet.Columns.Add(column);
}
}
}
else
{
for (var i = 0; i < FieldCount; i++)
{
workSheet.Columns.Add(workSheet.CreateDataColumn());
}
}
triggerCreateColumns = false;
//table.BeginLoadData();
}
while (ReadWorkSheetRow())
{
var dataRow = workSheet.CreateDataRow();
dataRow.Values = _CellsValues;
workSheet.Rows.Add(dataRow);
}
if (Depth > 0)
{
var dataRow = workSheet.CreateDataRow();
dataRow.Values = _CellsValues;
workSheet.Rows.Add(dataRow);
}
}
//table.EndLoadData();
return workSheet;
}
private IWorkSheet ReadWholeWorkSheet(XlsWorksheet sheet, IWorkBook workBook)
{
XlsBiffIndex idx;
if (!ReadWorkSheetGlobals(sheet, out idx))
{
return(null);
}
var workSheet = workBook.CreateWorkSheet();
workSheet.Name = sheet.Name;
var triggerCreateColumns = true;
_DatabaseCellAddresses = idx.DbCellAddresses;
for (var index = 0; index < _DatabaseCellAddresses.Length; index++)
{
if (Depth == _MaxRowIndex)
{
break;
}
// init reading data
_CellOffset = FindFirstDataCellOffset((int)_DatabaseCellAddresses[index]);
//DataTable columns
if (triggerCreateColumns)
{
if (IsFirstRowAsColumnNames && ReadWorkSheetRow())
{
for (var i = 0; i < FieldCount; i++)
{
if (_CellsValues[i] != null && _CellsValues[i].ToString().Length > 0)
{
var column = workSheet.CreateDataColumn();
column.ColumnName = _CellsValues[i].ToString();
workSheet.Columns.Add(column);
}
else
{
var column = workSheet.CreateDataColumn();
column.ColumnName = String.Concat(Column, i);
workSheet.Columns.Add(column);
}
}
}
else
{
for (var i = 0; i < FieldCount; i++)
{
workSheet.Columns.Add(workSheet.CreateDataColumn());
}
}
triggerCreateColumns = false;
//table.BeginLoadData();
}
while (ReadWorkSheetRow())
{
var dataRow = workSheet.CreateDataRow();
dataRow.Values = _CellsValues;
workSheet.Rows.Add(dataRow);
}
if (Depth > 0)
{
var dataRow = workSheet.CreateDataRow();
dataRow.Values = _CellsValues;
workSheet.Rows.Add(dataRow);
}
}
//table.EndLoadData();
return(workSheet);
}
public IWorkBook AsWorkBook(bool convertOaDateTime)
{
if (!IsValid) return null;
if (IsClosed) return _WorkbookData;
_ConvertOaDate = convertOaDateTime;
_WorkbookData = WorkBookFactory.CreateWorkBook();
for (var index = 0; index < ResultsCount; index++)
{
var table = ReadWholeWorkSheet(_Sheets[index], _WorkbookData);
if (null != table)
_WorkbookData.WorkSheets.Add(table);
}
_File.Close();
IsClosed = true;
return _WorkbookData;
}