public void ProbeOpenXml(string name)
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook(name)))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelOpenXmlReader");
}
}
public void ProbeXLSX()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook("xTest10x10")))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelOpenXmlReader");
}
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook("xTestOpenXml")))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelOpenXmlReader");
}
}
/*
* static void ReadExcelFiles (DirectoryInfo dir)
* {
* foreach (var f in dir.GetFiles ("*.xlsx")) {
* var nombre = f.Name.ToLowerInvariant ();
* if ((nombre.Contains("riesgo") || nombre.Contains("problema")) && !nombre.Contains ("$") ) {
* Console.WriteLine ("Leyendo: " + f.FullName);
* using (var stream = f.OpenRead ()) {
*
* var reader = ExcelReaderFactory.CreateOpenXmlReader (stream);
* Dictionary<String, List<List<String>>> dic;
* if (f.Name.ToLowerInvariant ().Contains ("riesgo")) {
* dic = _riesgos;
* } else {
* dic = _problemas;
* }
* ProcessTable (f.FullName, reader, dic);
* }
* }
* }
* }
*/
static string GetResultTableName(IExcelDataReader reader, int i)
{
var fields = reader.GetType().GetRuntimeFields();
var workbookField = fields.FirstOrDefault(f => f.Name.ToLowerInvariant().Contains("workbook"));
var workBook = workbookField.GetValue(reader);
var workbookType = workBook.GetType();
var sheets = (IList)workbookType.GetProperty("Sheets").GetValue(workBook);
var sheet = sheets [i];
string name = (String)sheet.GetType().GetProperty("Name").GetValue(sheet);
//return "";
return(name);
}
public void ProbeXLS()
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook("Test10x10")))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelBinaryReader");
}
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook("TestUnicodeChars")))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelBinaryReader");
}
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook("biff3")))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelBinaryReader");
}
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(Configuration.GetTestWorkbook("as3xls_BIFF2")))
{
Assert.AreEqual(excelReader.GetType().Name, "ExcelBinaryReader");
}
}
public DataTable ConvertStreamToDataTable(System.IO.Stream stream, string filterColumnName)
{
DataTable dt = new DataTable();
try
{
// IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
excelReader.GetType();
if (excelReader.IsValid)
{
DataSet ds = new DataSet();
try
{
ds = excelReader.AsDataSet(true);
}
catch
{
throw;
}
DataSet dsNew = new DataSet();
dsNew = ds.Clone();
ds.Tables[0].AsEnumerable().Where(c => c[filterColumnName].ToString() != String.Empty).CopyToDataTable(dsNew.Tables[0], LoadOption.OverwriteChanges);
dsNew.Tables[0].AcceptChanges();
stream.Dispose();
dt = dsNew.Tables[0];
}
}
catch (Exception Ex)
{
stream.Dispose();
throw;
}
return(dt);
}
/*
static void ReadExcelFiles (DirectoryInfo dir)
{
foreach (var f in dir.GetFiles ("*.xlsx")) {
var nombre = f.Name.ToLowerInvariant ();
if ((nombre.Contains("riesgo") || nombre.Contains("problema")) && !nombre.Contains ("$") ) {
Console.WriteLine ("Leyendo: " + f.FullName);
using (var stream = f.OpenRead ()) {
var reader = ExcelReaderFactory.CreateOpenXmlReader (stream);
Dictionary<String, List<List<String>>> dic;
if (f.Name.ToLowerInvariant ().Contains ("riesgo")) {
dic = _riesgos;
} else {
dic = _problemas;
}
ProcessTable (f.FullName, reader, dic);
}
}
}
}
*/
static string GetResultTableName (IExcelDataReader reader, int i)
{
var fields = reader.GetType ().GetRuntimeFields ();
var workbookField = fields.FirstOrDefault(f => f.Name.ToLowerInvariant().Contains("workbook"));
var workBook = workbookField.GetValue (reader);
var workbookType = workBook.GetType ();
var sheets = (IList)workbookType.GetProperty ("Sheets").GetValue (workBook);
var sheet = sheets [i];
string name = (String)sheet.GetType ().GetProperty ("Name").GetValue (sheet);
//return "";
return name;
}