public DataTable GetDataFromCSVFile(Stream stream)
{
var userList = new List <UserModel>();
DataTable dt = new DataTable();
try
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true // To set First Row As Column Names
}
});
if (dataSet.Tables.Count > 0)
{
dt = dataSet.Tables[0];
}
}
}
catch (Exception ex)
{
throw;
}
return(dt);
}
public static List <string> GetDataList(string path)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
List <string> data = new List <string>();
using (var stream = File.Open(Constants.pdfFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
do
{
while (reader.Read()) //Each ROW
{
for (int column = 0; column < reader.FieldCount; column++)
{
if (reader.GetValue(column) != null)
{
//Console.WriteLine(reader.GetString(column));//Will blow up if the value is decimal etc.
data.Add(reader.GetValue(column).ToString());
}
}
}
} while (reader.NextResult()); //Move to NEXT SHEET
}
}
return(data);
}
public void UseExcelDataReader_1()
{
string outputName = @"C:\Users\RomanBushuev\YandexDisk\MarketData\MOEX\raw\2018.03.12\rates.csv";
string outputName_2 = @"C:\Users\RomanBushuev\YandexDisk\MarketData\MOEX\raw\2018.03.12\rates2.csv";
var intermediateResult = File.ReadAllLines(outputName, Encoding.GetEncoding("windows-1251")).Skip(2);
File.WriteAllLines(outputName_2, intermediateResult);
FileStream stream = File.Open(outputName_2, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateCsvReader(stream);
ExcelDataSetConfiguration configureation = new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
};
DataSet dataSet = excelReader.AsDataSet(configureation);
foreach (DataTable datatable in dataSet.Tables)
{
foreach (DataRow dataRow in datatable.Rows)
{
object var = dataRow["MATDATE"];
Console.WriteLine(dataRow["MATDATE"].GetType());
if (var != null)
{
Console.WriteLine(var.ToString());
}
}
}
}
public void CsvQuotesAndNewlines()
{
using (var strm = new MemoryStream())
{
using (var writer = new StreamWriter(strm, Encoding.UTF8))
{
writer.NewLine = "\n";
writer.WriteLine("a,b");
writer.WriteLine("1,\"ha ");
writer.WriteLine("\"\"ha\"\" ");
writer.WriteLine("ha\"");
writer.WriteLine("3,4");
writer.Flush();
using (var excelReader = ExcelReaderFactory.CreateCsvReader(strm))
{
var ds = excelReader.AsDataSet();
Assert.AreEqual("a", ds.Tables[0].Rows[0][0]);
Assert.AreEqual("b", ds.Tables[0].Rows[0][1]);
Assert.AreEqual("1", ds.Tables[0].Rows[1][0]);
Assert.AreEqual("ha \n\"ha\" \nha", ds.Tables[0].Rows[1][1]);
Assert.AreEqual("3", ds.Tables[0].Rows[2][0]);
Assert.AreEqual("4", ds.Tables[0].Rows[2][1]);
}
}
}
}
public static List <DataPoint> ParsePointData(string fileName, int routeNumber)
{
var resultPointList = new List <DataPoint>();
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
var counter = -1;
using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
do
{
while (reader.Read())
{
var valString = reader.GetValue(1).ToString();
if (valString == "elevation")
{
continue;
}
resultPointList.Add(new DataPoint(counter, double.Parse(valString.Replace(".", ","))));
counter++;
}
} while (reader.NextResult());
}
}
resultPointList.RemoveAt(0);
return(resultPointList);
}
void TestEmpty(string linebreak)
{
using (var strm = new MemoryStream())
{
using (var writer = new StreamWriter(strm, Encoding.UTF8))
{
writer.NewLine = linebreak;
writer.WriteLine("a,b,c");
writer.WriteLine("1,\"\",\"\"");
writer.WriteLine("2,3,4");
writer.Flush();
using (var excelReader = ExcelReaderFactory.CreateCsvReader(strm))
{
var ds = excelReader.AsDataSet();
Assert.AreEqual("a", ds.Tables[0].Rows[0][0]);
Assert.AreEqual("b", ds.Tables[0].Rows[0][1]);
Assert.AreEqual("c", ds.Tables[0].Rows[0][2]);
Assert.AreEqual("1", ds.Tables[0].Rows[1][0]);
Assert.AreEqual("", ds.Tables[0].Rows[1][1]);
Assert.AreEqual("", ds.Tables[0].Rows[1][2]);
Assert.AreEqual("2", ds.Tables[0].Rows[2][0]);
Assert.AreEqual("3", ds.Tables[0].Rows[2][1]);
Assert.AreEqual("4", ds.Tables[0].Rows[2][2]);
}
}
}
}
public static IExcelDataReader GetExcelReader(Stream stream, string ext)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
// We return the interface, so that
IExcelDataReader reader = null;
try
{
if (".xls".Equals(ext))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
if (".xlsx".Equals(ext))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (".csv".Equals(ext))
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
return(reader);
}
catch (Exception)
{
throw;
}
}
public IExcelDataReader getExcelReader()
{
FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = null;
try
{
if (_path.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (_path.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (_path.EndsWith(".csv"))
{
reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(1252),
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' }
});
}
return(reader);
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable ParseSpreadsheet(Stream stream, bool isExcel)
{
stream.Position = 0;
var reader = isExcel
? ExcelReaderFactory.CreateReader(stream)
: ExcelReaderFactory.CreateCsvReader(stream);
using (reader)
{
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = tableReader =>
new ExcelDataTableConfiguration {
UseHeaderRow = true
}
};
var result = reader.AsDataSet(config);
if (result.Tables.Count != 1)
{
throw new Exception(
$"Cannot handle an Excel File with {result.Tables.Count} sheets");
}
return(result.Tables[0]);
}
}
public DataRowCollection Leer(string FileName, byte[] Data)
{
string Ruta = WebConfigurationManager.AppSettings["RutaCargasMasivas"].ToString() + FileName;
File.WriteAllBytes(Ruta, Data);
var ds = new System.Data.DataSet();
var extension = Path.GetExtension(FileName).ToLower();
using (var stream = new FileStream(Ruta, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var sw = new Stopwatch();
sw.Start();
IExcelDataReader reader = null;
if (extension == ".xls")
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (extension == ".xlsx")
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (extension == ".csv")
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
if (reader == null)
{
return(null);
}
var openTiming = sw.ElapsedMilliseconds;
// reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
using (reader)
{
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
}
// toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
var tablenames = GetTablenames(ds.Tables);
return(ds.Tables[0].Rows);
// sheetCombo.DataSource = tablenames;
//if (tablenames.Count > 0)
// sheetCombo.SelectedIndex = 0;
// dataGridView1.DataSource = ds;
// dataGridView1.DataMember
}
}
public void GitIssue323DoubleClose()
{
using (var reader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("csv\\MOCK_DATA.csv")))
{
reader.Read();
reader.Close();
}
}
private static ExcelInfo CsvReader(FileStream stream)
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
ExcelInfo info = new ExcelInfo(reader.AsDataSet());
return(info);
}
}
public static DataSet ReadDataExcel(string filepath, bool use_header_row = true)
{
try
{
FileStream stream = File.Open(filepath, FileMode.Open, FileAccess.Read);
IExcelDataReader reader;
string extension = System.IO.Path.GetExtension(filepath).ToLower();
if (extension.Equals(".csv"))
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
else if (extension.Equals(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = use_header_row,
FilterRow = rowReader =>
{
var hasData = false;
for (var i = 0; i < rowReader.FieldCount; i++)
{
if (rowReader[i] == null || string.IsNullOrEmpty(rowReader[i].ToString()))
{
continue;
}
hasData = true;
break;
}
return(hasData);
},
EmptyColumnNamePrefix = "Col "
}
};
conf.UseColumnDataType = false;
var dataSet = reader.AsDataSet(conf);
reader.Close();
return(dataSet);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
public void GitIssue333EanQuotes()
{
using (var reader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("csv\\ean.txt")))
{
reader.Read();
Assert.AreEqual(2, reader.RowCount);
Assert.AreEqual(24, reader.FieldCount);
}
}
public double[] GetDoubleRows(int col)
{
using (var stream = File.Open(this.FilePath, FileMode.Open, FileAccess.Read))
{
var rowsData = new List <double>();
if (!this.IsCsv)
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
if (col >= reader.FieldCount)
{
return(null);
}
;
var fieldType = reader.GetFieldType(col);
rowsData.Add(fieldType == typeof(double) ? reader.GetDouble(col) : -1);
}
} while (reader.NextResult());
}
}
else
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
do
{
while (reader.Read())
{
if (col >= reader.FieldCount)
{
return(null);
}
;
if (double.TryParse(reader.GetString(col), out double result))
{
rowsData.Add(result);
}
else
{
rowsData.Add(-1);
}
}
} while (reader.NextResult());
}
}
return(rowsData.ToArray());
}
}
private void Button2Click(object sender, EventArgs e)
{
var extension = Path.GetExtension(textBox1.Text).ToLower();
using (var stream = new FileStream(textBox1.Text, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var sw = new Stopwatch();
sw.Start();
IExcelDataReader reader = null;
if (extension == ".xls")
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (extension == ".xlsx")
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (extension == ".csv")
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
if (reader == null)
{
return;
}
var openTiming = sw.ElapsedMilliseconds;
// reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
using (reader)
{
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = firstRowNamesCheckBox.Checked
}
});
}
toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
var tablenames = GetTablenames(ds.Tables);
sheetCombo.DataSource = tablenames;
if (tablenames.Count > 0)
{
sheetCombo.SelectedIndex = 0;
}
// dataGridView1.DataSource = ds;
// dataGridView1.DataMember
}
}
private void ReadDataFromExcel(string fileName)
{
using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream
, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding(936),
AutodetectSeparators = new char[] { ',', ';', '\t', '|', '#' }
}
))
{
// 2. Use the AsDataSet extension method
var result = reader.AsDataSet();
if (result.Tables.Count == 0)
{
return;
}
foreach (DataRow row in result.Tables[0].Rows)
{
int value;
if (!int.TryParse(row[0].ToString(), out value))
{
continue;
}
var company = new Company()
{
StockNum = row[0].ToString(),
CompanyName = row[1].ToString(),
StockName = row[3].ToString(),
//IPODate = DateTime.Parse(row[4].ToString()),
TotalEquity = decimal.Parse(row[5].ToString()),
CirculatingEquity = decimal.Parse(row[6].ToString())
};
var date = new DateTime(1980, 1, 1);
if (DateTime.TryParse(row[4].ToString(), out date))
{
}
var tmpDate = new DateTime(1980, 1, 1);
if (date < tmpDate)
{
date = tmpDate;
}
company.IPODate = date;
Console.WriteLine("Adding " + company.CompanyName);
SaveCompanyInfo(company);
}
}
}
}
/// <summary>
/// Reads only those files, that have supported extension (*.txt files are also interpreted as CSV files)
/// </summary>
/// <param name="filepath">A path to readed file</param>
/// <returns>True, if file was succesgully read, otherwise False</returns>
private bool RestrictedRead(string filepath)
{
string ext = filepath.Substring(filepath.LastIndexOf('.'));
if (ext == ".xls" || ext == ".xlsx")
{
using (var stream = File.Open(filepath, FileMode.Open, FileAccess.Read))
{
try
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
}
}while (reader.NextResult());
this.Set.Set = reader.AsDataSet(Config);
}
}
catch { return(false); }
return(true);
}
}
if (ext == ".csv" || ext == ".txt")
{
using (var stream = File.Open(filepath, FileMode.Open, FileAccess.Read))
{
try
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
do
{
while (reader.Read())
{
}
}while (reader.NextResult());
this.Set.Set = reader.AsDataSet();
}
}
catch { return(false); }
}
return(true);
}
return(false);
}
public void CsvDisposed()
{
// Verify the file stream is closed and disposed by the reader
{
var stream = Configuration.GetTestWorkbook("csv\\MOCK_DATA.csv");
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateCsvReader(stream))
{
var _ = excelReader.AsDataSet();
}
Assert.Throws <ObjectDisposedException>(() => stream.ReadByte());
}
}
public void CsvEscapedQuotes()
{
using (var excelReader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("escaped_quotes.csv")))
{
var ds = excelReader.AsDataSet();
Assert.AreEqual("a", ds.Tables[0].Rows[0][0]);
Assert.AreEqual("b", ds.Tables[0].Rows[0][1]);
Assert.AreEqual("1", ds.Tables[0].Rows[1][0]);
Assert.AreEqual("ha \"ha\" ha", ds.Tables[0].Rows[1][1]);
Assert.AreEqual("3", ds.Tables[0].Rows[2][0]);
Assert.AreEqual("4", ds.Tables[0].Rows[2][1]);
}
}
public void CsvWrongEncoding()
{
Assert.Throws(typeof(DecoderFallbackException), () =>
{
var configuration = new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.UTF8
};
using (var excelReader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("cp1252.csv"), configuration))
{
}
});
}
public void CsvWhitespaceNull()
{
using (var excelReader = ExcelReaderFactory.CreateCsvReader(Configuration.GetTestWorkbook("simple_whitespace_null.csv")))
{
var ds = excelReader.AsDataSet();
Assert.AreEqual("a", ds.Tables[0].Rows[0][0]); // ignore spaces
Assert.AreEqual("\0b\0", ds.Tables[0].Rows[0][1]);
Assert.AreEqual("c", ds.Tables[0].Rows[0][2]); // ignore tabs
Assert.AreEqual("1", ds.Tables[0].Rows[1][0]);
Assert.AreEqual("2", ds.Tables[0].Rows[1][1]);
Assert.AreEqual("3", ds.Tables[0].Rows[1][2]);
}
}
public ActionResult Upload(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (upload.FileName.EndsWith(".csv"))
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
// Older ExcelDataReader lib:
//reader.IsFirstRowAsColumnNames = true;
//DataSet result = reader.AsDataSet();
// ExcelDataReader lib v 3.6
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
reader.Close();
return(View(result.Tables[0]));
}
else
{
ModelState.AddModelError("File", "Please Upload Your file");
}
}
return(View());
}
public override (List <DataTable> data, DataTable errors) ParseData()
{
using (var stream = File.Open(location.FullName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
do
{
DataTable table = new DataTable();
bool headerRow = true;
int startRow_check = 1;
int BlankRow = 0;
while (reader.Read())
{
if (startRow_check < startingRow)
{
startRow_check++;
continue;
}
IDataRecord rowData = reader;
if (headerRow == true)
{
// make column headers for datatable
table = CreateTable(table, rowData);
headerRow = false;
continue;
}
// add blank or actual data rows
if (RowIsNull(rowData))
{
BlankRow++;
continue;
}
for (int i = 0; i < BlankRow; i++)
{
table.Rows.Add(table.NewRow());
}
BlankRow = 0;
table = AddToTable(table, rowData, location.Name, reader.Name);
}
tables.Add(table);
} while (reader.NextResult());
}
}
return(tables, errorTable);
}
public static DataSet openCsv(string filePath)
{
using (stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
using (reader = ExcelReaderFactory.CreateCsvReader(stream))
{ //return excel as tables
return(reader.AsDataSet());
}
}
}
}
private static DataTable ParseDataTableFromStream(Stream stream)
{
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = tableReader => new ExcelDataTableConfiguration {
UseHeaderRow = true
}
};
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
var dataSet = reader.AsDataSet(config);
return(dataSet.Tables[0]);
}
}
IExcelDataReader GetReader(string ext, Stream stream)
{
switch (ext)
{
case ".xls":
return(ExcelReaderFactory.CreateBinaryReader(stream));
case ".xlsm":
case ".xlsx":
return(ExcelReaderFactory.CreateOpenXmlReader(stream));
default:
return(ExcelReaderFactory.CreateCsvReader(stream));
}
}
private IExcelDataReader CreateReader(Stream stream)
{
try
{
// try to create default excel data reader
return(ExcelReaderFactory.CreateReader(stream));
}
catch (Exception ex)
{
Log.Info($"CiExcelParser.Parse: Unable to create excel data reader. Try to create CSV reader", ex);
// fallback action, if file not excel compatible then try to created scv reader, or raise an exception
return(ExcelReaderFactory.CreateCsvReader(stream));
}
}
private IExcelDataReader CreateDataReader(Stream stream, Enums.FileTypes fileType)
{
if (fileType == Enums.FileTypes.EXCEL)
{
return(ExcelReaderFactory.CreateReader(stream));
}
else if (fileType == Enums.FileTypes.CSV)
{
return(ExcelReaderFactory.CreateCsvReader(stream));
}
else
{
throw new InvalidOperationException("File Import Definition FileType value is invalid. " + fileType.ToString());
}
}
public string[] GetValueRows(int col)
{
using (var stream = File.Open(this.FilePath, FileMode.Open, FileAccess.Read))
{
var rowsData = new List <string>();
if (!this.IsCsv)
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
if (col >= reader.FieldCount)
{
return(null);
}
;
rowsData.Add(reader.GetValue(col)?.ToString());
}
} while (reader.NextResult());
}
}
else
{
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
do
{
while (reader.Read())
{
if (col >= reader.FieldCount)
{
return(null);
}
;
rowsData.Add(reader.GetValue(col)?.ToString());
}
} while (reader.NextResult());
}
}
return(rowsData.ToArray());
}
}