private static void DataTableBulkCopySample()
{
var dt = new DataTable();
using (var reader = new ExcelDataReader(@"test.xlsx"))
dt.Load(reader);
Console.WriteLine("Read DataTable done: " + dt.Rows.Count);
DataHelper.CreateTableIfNotExists(ConnectionString, TableName, dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray());
Console.WriteLine("Create table done.");
using (var bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = TableName;
foreach (DataColumn dc in dt.Columns)
bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
bulkCopy.WriteToServer(dt);
}
Console.WriteLine("Copy data to database done (DataTable).");
}
public void CanReadExcel()
{
IDataTable table;
using (var excel = new ExcelDataReader(GetFileStream("testdata.xlsx"), true))
{
table = LightweightDataTable.FromDataReader(excel);
}
VerifyDataTable(table);
}
private static void DataReaderBulkCopySample()
{
using (var reader = new ExcelDataReader(@"test.xlsx"))
{
var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetName(i)).ToArray();
DataHelper.CreateTableIfNotExists(ConnectionString, TableName, cols);
Console.WriteLine("Create table done.");
using (var bulkCopy = new SqlBulkCopy(ConnectionString))
{
// MSDN: When EnableStreaming is true, SqlBulkCopy reads from an IDataReader object using SequentialAccess,
// optimizing memory usage by using the IDataReader streaming capabilities
bulkCopy.EnableStreaming = true;
bulkCopy.DestinationTableName = TableName;
foreach (var col in cols)
bulkCopy.ColumnMappings.Add(col, col);
bulkCopy.WriteToServer(reader);
}
Console.WriteLine("Copy data to database done (DataReader).");
}
}
static void Main(string[] args)
{
var dt = new DataTable();
using (var reader = new ExcelDataReader(@"data.xlsx"))
dt.Load(reader);
Console.WriteLine("done: " + dt.Rows.Count);
Console.ReadKey();
}
public static List <int> GetIdCityByExcel(this HttpPostedFileBase file, string fileLocation)
{
//Arquivo valido para tratamento
if (file.FileName.Contains(".csv") || file.FileName.Contains(".xlsx"))
{
string columName = "City";
var names = columName.Split(';');
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
var dt = new DataTable();
file.SaveAs(fileLocation);
//Caso for csv
if (fileLocation.Contains(".csv"))
{
dt = DataTableHandle.GetDataTabletFromCsvFile(fileLocation);
}
if (fileLocation.Contains(".xlsx"))
{
using (var reader = new ExcelDataReader(fileLocation))
dt.Load(reader);
}
var lista = new List <int>();
var cidades = Business.City.GetCities().ToList();
foreach (var s in dt.AsEnumerable())
{
int data;
try
{
data = Convert.ToInt32(s[names[0].Trim()].ToString());
if (!cidades.Any(w => w.idCity == data))
{
continue;
}
}
catch
{
//supressed
continue;
}
lista.Add(data);
}
//Atualiza as ordens
return(lista);
}
return(null);
}
public void OpenPathHdrDefaultSheetTest()
{
var dt = new DataTable();
using (var reader = new ExcelDataReader(@"test.xlsx"))
dt.Load(reader);
Assert.AreEqual(7, dt.Columns.Count);
Assert.AreEqual(5, dt.Rows.Count);
}
public void OpenPathNoHdrSheetNameTest()
{
var dt = new DataTable();
using (var reader = new ExcelDataReader(@"test.xlsx", "Second", false))
dt.Load(reader);
Assert.AreEqual(7, dt.Columns.Count);
Assert.AreEqual(6, dt.Rows.Count);
}
public void Read_ShouldReturnFailedResultWithExpectedMessageWhenFileNotFound()
{
var path = TestUtils.BuildPathFor(@"DataReadersTestCases\NotExistedFile.xlsx");
var reader = new ExcelDataReader();
var result = reader.Read(path);
result.Data.Should().BeEmpty();
result.ErrorMessage.Should().ContainEquivalentOf("Could not find file");
}
public void Read_ShouldReturnFailedResultWithExpectedMessageWhenValuesInWrongFormat()
{
var path = TestUtils.BuildPathFor(@"DataReadersTestCases\ExcelDataReaderTestCase2.xlsx");
var reader = new ExcelDataReader();
var result = reader.Read(path);
result.Data.Should().BeEmpty();
result.ErrorMessage.Should().ContainEquivalentOf("string was not in a correct format");
}
public void OpenPathEmptyRowsTest()
{
var dt = new DataTable();
using (var reader = new ExcelDataReader(@"NoDataReturned.xlsx", "ContractIBNR"))
dt.Load(reader);
Assert.AreEqual(5, dt.Columns.Count);
Assert.AreEqual(1, dt.Rows.Count);
}
public void DataReaderTest()
{
using (var reader = new ExcelDataReader(@"test.xlsx"))
{
Assert.AreEqual(0, reader.Depth);
Assert.AreEqual(-1, reader.RecordsAffected);
Assert.AreEqual(7, reader.FieldCount);
Assert.AreEqual(5, reader.GetOrdinal("ColDecimal"));
Assert.AreEqual(-1, reader.GetOrdinal("InvalidColName"));
}
}
public void CanReadExcel()
{
IDataTable table;
using (var excel = new ExcelDataReader(GetFileStream("testdata.xlsx"), true))
{
table = LightweightDataTable.FromDataReader(excel);
}
VerifyDataTable(table);
}
protected override void ReadAll()
{
do
{
int rowNr = 0;
while (ExcelDataReader.Read())
{
if (ExcelDataReader.VisibleState != "visible")
{
continue;
}
if (HasSheetName && ExcelDataReader.Name != SheetName)
{
continue;
}
rowNr++;
if (HasRange && rowNr > Range.EndRowIfSet)
{
break;
}
if (HasRange && rowNr < Range.StartRow)
{
continue;
}
try
{
TOutput row = ParseDataRow();
if (row == null && IgnoreBlankRows)
{
continue;
}
else if (row == null && !IgnoreBlankRows)
{
break;
}
else if (row != null)
{
Buffer.SendAsync(row).Wait();
LogProgress();
}
}
catch (Exception e)
{
if (!ErrorHandler.HasErrorBuffer)
{
throw e;
}
ErrorHandler.Send(e, $"File: {Uri} -- Sheet: {SheetName ?? ""} -- Row: {rowNr}");
}
}
} while (ExcelDataReader.NextResult());
}
private TOutput ParseDataRow()
{
TOutput row = (TOutput)Activator.CreateInstance(typeof(TOutput));
bool emptyRow = true;
for (int col = 0, colNrInRange = -1; col < ExcelDataReader.FieldCount; col++)
{
if (HasRange && col > Range.EndColumnIfSet)
{
break;
}
if (HasRange && (col + 1) < Range.StartColumn)
{
continue;
}
colNrInRange++;
emptyRow &= ExcelDataReader.IsDBNull(col);
object value = ExcelDataReader.GetValue(col);
if (TypeInfo.IsDynamic)
{
var r = row as IDictionary <string, Object>;
if (HasHeaderData)
{
r.Add(HeaderColumns[colNrInRange], value);
}
else
{
r.Add("Column" + (colNrInRange + 1), value);
}
}
else
{
PropertyInfo propInfo = null;
if (HasHeaderData && TypeInfo.ExcelColumnName2PropertyIndex.ContainsKey(HeaderColumns[colNrInRange]))
{
propInfo = TypeInfo.Properties[TypeInfo.ExcelColumnName2PropertyIndex[HeaderColumns[colNrInRange]]];
}
else if (TypeInfo.ExcelIndex2PropertyIndex.ContainsKey(colNrInRange))
{
propInfo = TypeInfo.Properties[TypeInfo.ExcelIndex2PropertyIndex[colNrInRange]];
}
propInfo?.TrySetValue(row, TypeInfo.CastPropertyValue(propInfo, value?.ToString()));
}
}
if (emptyRow)
{
return(default(TOutput));
}
else
{
return(row);
}
}
public void test_read_excel_should_report_missing_column_error()
{
ExcelDataReader <InvoiceData> processor = new ExcelDataReader <InvoiceData>();
processor.Source = this.getFilePath("invoice_missing_column.xlsx");
var process_result = processor.ReadData();
Assert.IsFalse(process_result);
Assert.AreEqual(0, processor.Data.Count());
Assert.AreEqual(1, processor.Errors.Count);
Assert.AreEqual("column Circuit Name is not found", processor.Errors[0].GetError("Circuit Name").Message);
}
public void OpenStreamEmptySheetTest()
{
var dt = new DataTable();
using (var sr = File.OpenRead(@"test.xlsx"))
{
using (var reader = new ExcelDataReader(sr, "Third", false))
dt.Load(reader);
}
Assert.AreEqual(0, dt.Columns.Count);
Assert.AreEqual(0, dt.Rows.Count);
}
protected bool IsRowEmpty(ExcelDataReader reader, decimal count, IEnumerable <int> excludedColumns)
{
var itemArray = new object[reader.FieldCount];
reader.GetValues(itemArray);
var emptyRowFound = itemArray.Where((r, d) => excludedColumns.Contains(d)).All(v => v == DBNull.Value);
if (emptyRowFound)
{
}
return(emptyRowFound);
}
public void OpenStreamHdrSheetIndexTest()
{
var dt = new DataTable();
using (var sr = File.OpenRead(@"test.xlsx"))
{
using (var reader = new ExcelDataReader(sr, 1, false))
dt.Load(reader);
}
Assert.AreEqual(7, dt.Columns.Count);
Assert.AreEqual(6, dt.Rows.Count);
}
private DataTable ReadFile(string _path)
{
DataTable dtShifts = new DataTable();
try
{
using (var reader = new ExcelDataReader(_path))
dtShifts.Load(reader);
}
catch (Exception ex)
{
}
return(dtShifts);
}
static void Main(string[] args)
{
Console.WriteLine("Type path of excel file:\n");
string path = @Console.ReadLine();
var dataReader = new ExcelDataReader(path);
var dictionary = dataReader.getDataEntry();
foreach (KeyValuePair <string, DataEntry> pair in dictionary)
{
Console.WriteLine("{0} {1}", pair.Key, pair.Value.Hours + " " + pair.Value.Price);
}
Console.ReadLine();
}
private void MainForm_Shown(object sender, EventArgs e)
{
Thread t = new Thread(() =>
{
using (var openFileDialog = new OpenFileDialog())
{
openFileDialog.CheckFileExists = true;
openFileDialog.Multiselect = false;
openFileDialog.Filter = @"Excel Files|*.xls;*.xlsx";
openFileDialog.Title = "Select Excel File With Data";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
var fileName = openFileDialog.FileName;
//todo add Unity Container
using (IDataReader excelDataReader = new ExcelDataReader())
{
if (excelDataReader is INotifyChanges)
{
((INotifyChanges)excelDataReader).SendNotification += SendNotification;
}
excelDataReader.ReaderDetails = fileName;
var response = excelDataReader.ReadData();
if (response.Success)
{
WritePlayers(response.ReturnValue as IEnumerable <PlayersGroup>);
SendNotification(null, "Work Completed.");
}
else
{
foreach (var exception in response.Errors)
{
Logger.Error(exception);
}
SendNotification(null,
string.Format(Resources.SplashForm_Errors_Log, response.Errors.Count));
}
}
}
SystemSounds.Beep.Play();
Thread.Sleep(500);
MainForm.Finished = true;
}
});
t.IsBackground = true;
t.SetApartmentState(ApartmentState.STA);
t.Start();
}
public static DataSet Parse(string fileName)
{
// string cons = "Provider=Microsoft.ACE.OLEDB.12.0; " + "data source='" + fileName + "';" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ";
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
try
{
var spreadsheet = new ExcelDataReader(fs);
fs.Close();
return(spreadsheet.WorkbookData);
}
catch (Exception ex)
{
fs.Close();
fs.Dispose();
throw ex;
}
//foreach (var sheetName in GetExcelSheetNames(cons))
//{
// using (OleDbConnection con = new OleDbConnection(cons))
// {
// try
// {
// var dataTable = new DataTable();
// string query = string.Format("SELECT * FROM [{0}]", sheetName);
// con.Open();
// OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
// adapter.Fill(dataTable);
// data.Tables.Add(dataTable);
// con.Close();
// con.Dispose();
// }
// catch (Exception ex)
// {
// con.Close();
// con.Dispose();
// throw ex;
// }
// }
//}
}
public void GetField()
{
//var filePath = @"D:\Users\Scott\Source\github\cadru\tests\Cadru.Data.Tests\Plan Assignment DELETE File 20170615.xlsx";
var filePath = @"Adjustments Table Upload 20170616.xlsx";
using (var stream = File.OpenRead(filePath))
{
using (var reader = new ExcelDataReader(stream))
{
reader.FirstRowAsHeader = true;
reader.Read();
var sheetName = reader.CurrentSheetName;
reader.Read();
var empty = this.IsRowEmpty(reader, 0, Enumerable.Empty <int>());
var fiscalYear = reader.Field <int>(0);
var empId = reader.Field <string>(1);
var startDate = reader.Field <DateTime>(2);
var status = reader.Field <int?>(6);
var monthlyRate = reader.Field <decimal?>(7);
var orgUnit = reader.Field <string>(10);
var s1 = reader.ToDelimitedString();
var s2 = reader.ToDelimitedString(true);
var action = "I";
var index = -1;
if (reader.FieldNames.Contains("ImportAction"))
{
index = reader.GetOrdinal("ImportAction");
}
else if (reader.FieldNames.Contains("Action"))
{
index = reader.GetOrdinal("Action");
}
if (index != -1)
{
action = reader.GetString(index) ?? "I";
}
reader.Close();
}
}
}
private void ParseHeader()
{
for (int col = 0, colNrInRange = -1; col < ExcelDataReader.FieldCount; col++)
{
if (HasRange && col > Range.EndColumnIfSet)
{
break;
}
if (HasRange && (col + 1) < Range.StartColumn)
{
continue;
}
colNrInRange++;
string value = Convert.ToString(ExcelDataReader.GetValue(col));
HeaderColumns.Add(value);
}
IsHeaderRead = true;
}
private static IEnumerable <TestCaseData> GetData(string path, string sheetName)
{
var testcases = ExcelDataReader.New().FromFileSystem(path).AddSheet(sheetName).GetTestCases(delegate(string sheet, System.Data.DataRow row, int rowNum)
{
var testDataArgs = new Dictionary <string, string>();
foreach (System.Data.DataColumn column in row.Table.Columns)
{
testDataArgs[column.ColumnName] = Convert.ToString(row[column]);
}
string testName = sheet + " - " + row.ItemArray[0];
return(new TestCaseData(testDataArgs).SetName(testName));
});
foreach (TestCaseData testCaseData in testcases)
{
yield return(testCaseData);
}
}
public DataSet ProcessFileForStaging(string FilePath, string ConnectionStr, string StagingTableName, string StoredProcedure, int SqlCommandTimeOut = 30, List <SqlBulkCopyColumnMapping> ColumnMappings = null, string[] IgnoreColumns = null)
{
DataTable dt = new DataTable();
if (Path.GetExtension(FilePath).ToLower() == ".csv")
{
dt = ParseCSVFile(FilePath);
}
else
{
using (ExcelDataReader r = new ExcelDataReader(FilePath))
{
dt.Load(r);
}
}
return(ProcessStagingTableFile(dt, ConnectionStr, StagingTableName, StoredProcedure, SqlCommandTimeOut, ColumnMappings, IgnoreColumns));
}
public void test_read_excel_should_report_data_errors()
{
ExcelDataReader <InvoiceData> processor = new ExcelDataReader <InvoiceData>();
processor.Source = this.getFilePath("invoice_invalid_data.xlsx");
var process_result = processor.ReadData();
Assert.IsFalse(process_result);
Assert.AreEqual(4, processor.Data.Count());
Assert.AreEqual(1, processor.Errors.Count);
var error = processor.Errors[0];
Assert.AreEqual(3, error.Count);
Assert.AreEqual("unable to convert 'today' to datetime value", error.GetError("Invoice From").Message);
Assert.AreEqual("unable to convert 'tomorrow' to datetime value", error.GetError("Invoice To").Message);
Assert.AreEqual("unable to convert '300 Million' to number value", error.GetError("Termination Charge").Message);
}
private bool ValidateAPFile(string fpath)
{
bool valid = true;
try
{
List <DispositionRow> rows = ExcelDataReader.GetResearchRows(fpath);
int zeroRecords = rows.Count(r => r.InterviewRecordID == 0);
valid = (zeroRecords == 0);
} catch (Exception e)
{
valid = false;
}
return(valid);
// return true;
}
private bool ValidateMDFile(string fpath)
{
bool valid = true;
try
{
List <ModificationRow> rows = ExcelDataReader.GetModificationRows(fpath);
int zeroRecords = rows.Count(r => r.InterviewRecordID == 0);
valid = (zeroRecords != 0);
}
catch
{
valid = false;
}
return(valid);
}
public static DataTable GenerateTableFromExcel(string pathToExcel)
{
//var path = @"s:\OneClickDb\PLC1.xlsx";
var dt = new DataTable();
try
{
using (var reader = new ExcelDataReader(pathToExcel))
{
dt.Load(reader);
return(dt);
}
}
catch (Exception e)
{
Console.WriteLine(e);
return(null);
}
}
public void test_read_excel_should_success()
{
ExcelDataReader <InvoiceData> processor = new ExcelDataReader <InvoiceData>();
processor.Source = this.getFilePath("invoice.xlsx");
var process_result = processor.ReadData();
Assert.IsTrue(process_result);
Assert.AreEqual(5, processor.Data.Count());
Assert.AreEqual(5, processor.TotalRecords);
var first_row = processor.Data.First();
Assert.AreEqual("Circuit #1", first_row.CircuitName);
Assert.AreEqual("CNY", first_row.CurrencyCode);
Assert.AreEqual("2019-08-01 00:00:00", first_row.BillingPeriod.ToString("yyyy-MM-dd HH:mm:ss"));
Assert.AreEqual(360.33, first_row.TerminationCharge);
Assert.AreEqual(4000.56m, first_row.AccessInstallationCharge);
Assert.AreEqual(872.18m, Math.Round(first_row.VAT, 2));
}
private bool ValidateQBFile(string fpath)
{
bool valid = true;
try
{
var checks = ExcelDataReader.GetQuickbooksChecks(fpath);
var clr = checks.Count(c => c.Clr.Equals("Unknown"));
valid = clr == 0;
}
catch (Exception e)
{
valid = false;
}
return(valid);
// return true;
}
private void ProcessFile(FileItem item, bool alreadyOpened = false) =>
this.MainWindow.Waiting(async(statusBar, callback) =>
{
if (!alreadyOpened)
{
sourceList.Add(item);
}
if (FileContent.IsOpened(item))
{
callback();
return;
}
ExcelDataReader reader = container.Get <ExcelDataReader>().Setup(r =>
{
r.Initialize(item.Location, msg => this.MainWindow.Output(msg));
});
ExcelData data = await reader.Read();
FileContent.Add(item, data);
callback();
});
public object Get(string dob, string forceUpdate, string callback)
{
ExcelDataReader e = new ExcelDataReader();
DateTime validDOB;
DateTime.TryParse(dob, out validDOB);
string spPath = ConfigurationManager.AppSettings["PathToSP"];
if (validDOB.Year < 2)
return ConvertToHttpResponse("bad date of birth. dob= " + dob + ", validDOB= " + validDOB.ToShortDateString());
string spDate = Convert.ToDateTime(dob).ToString("yyyy.MM");
string spDateToday = DateTime.Now.ToString("yyyy.MM");
string pathToFile = System.AppDomain.CurrentDomain.BaseDirectory + @"Data\ie_data.xls";
var client = new WebClient();
if (!File.Exists(pathToFile)){
client.DownloadFile(spPath, pathToFile);
}
DateTime fileCreatedDate = File.GetLastWriteTime(pathToFile);
try
{
//Download the xls file on web
if (((DateTime.Now - fileCreatedDate).TotalDays > 28) || forceUpdate == "true")
{
File.Delete(pathToFile);
client.DownloadFile(spPath, pathToFile);
}
}
catch (Exception)
{
//email error
}
// Read file
DataSet spData = e.convertExelDataDataSet("/Data/ie_data.xls");
DataSet stampData = e.convertExelDataDataSet("/Data/stamps.xlsx");
//Querys
DataRow sp_dob = spData.Tables[0].Rows.Cast<DataRow>()
.Where(row => row["Column1"].ToString() == spDate)
.FirstOrDefault();
DataRow sp_today = spData.Tables[0].Rows.Cast<DataRow>()
.Where(row => row["Column1"] != DBNull.Value)
.Last();
DataRow stamp_dob = stampData.Tables[0].Rows.Cast<DataRow>()
.Where(row => Convert.ToInt32(row["Column2"]) >= validDOB.Year - 2)
.FirstOrDefault();
DataRow stamp_today = stampData.Tables[0].Rows.Cast<DataRow>()
.Where(row => row["Column2"] != DBNull.Value )
.Last();
//load object
Dictionary<string, string> payload = new Dictionary<string, string>();
payload.Add("dob", dob);
payload.Add("dobSP", (sp_dob != null) ? sp_dob["Column2"].ToString() : "");
payload.Add("latestSP", (sp_today != null) ? sp_today["Column2"].ToString() : "");
payload.Add("dobStampPrice", stamp_dob["Column3"].ToString());
payload.Add("latestStampPrice", stamp_today["Column3"].ToString());
payload.Add("dobStampImage", stamp_dob["Column4"].ToString());
payload.Add("latestStampImage", stamp_today["Column4"].ToString());
payload.Add("dataUpdated", fileCreatedDate.ToString());
//return ConvertToHttpResponse(JsonConvert.SerializeObject(payload));
string serialized = JsonConvert.SerializeObject(payload);
return (String.IsNullOrEmpty(callback)) ? serialized : callback + "(" + serialized + ")";
}
public string Get()
{
ExcelDataReader e = new ExcelDataReader();
DataSet data = e.convertExelDataDataSet("/Data/ie_data.xls");
return JsonConvert.SerializeObject(data);
}
public static IDataTable FromFile(
string fileName,
Stream stream,
long contentLength,
CsvConfiguration configuration,
int skip = 0,
int take = int.MaxValue)
{
Guard.ArgumentNotEmpty(() => fileName);
Guard.ArgumentNotNull(() => stream);
Guard.ArgumentNotNull(() => configuration);
if (contentLength == 0)
{
throw Error.Argument("fileName", "The posted file '{0}' does not contain any data.".FormatInvariant(fileName));
}
IDataReader dataReader = null;
try
{
var fileExt = System.IO.Path.GetExtension(fileName).ToLowerInvariant();
switch (fileExt)
{
case ".xlsx":
dataReader = new ExcelDataReader(stream, true); // TODO: let the user specify if excel file has headers
break;
default:
dataReader = new CsvDataReader(new StreamReader(stream), configuration);
break;
}
var table = LightweightDataTable.FromDataReader(dataReader, skip, take);
if (table.Columns.Count == 0 || table.Rows.Count == 0)
{
throw Error.InvalidOperation("The posted file '{0}' does not contain any columns or data rows.".FormatInvariant(fileName));
}
return table;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dataReader != null)
{
if (!dataReader.IsClosed)
{
dataReader.Dispose();
}
dataReader = null;
}
}
}
private DataTable ReadPaymentsFromFile(Mapper mapper)
{
string fileName = Path.GetFileName(txtExcelFilename.Text);
DataTable dt = new DataTable();
// check for Excel office version -- old versions
if (fileName.ToLower().EndsWith(".xls"))
{
FileStream stream = new FileStream(txtExcelFilename.Text, FileMode.Open);
ExcelDataReader excelReader
= new ExcelDataReader(stream);
DataSet ds = excelReader.GetDataSet();
dt = ds.Tables[0];
//this stores the mapped column names to the column names in Excel
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
if (mapper.ContainsKey(ds.Tables[0].Rows[0][i].ToString()))
{
dt.Columns[i].ColumnName = (string)ds.Tables[0].Rows[0][i];
}
}
stream.Close();
}
else
{
// Office 2007 file parser
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + txtExcelFilename.Text + ";" +
"Extended Properties=\"Excel 12.0;HDR=YES;\"";
// if you don't want to show the header row (first row)
// use 'HDR=NO' in the string
string oleSql = "SELECT * FROM [{0}]";
DataSet ds = new DataSet();
using (OleDbConnection oleCnn = new OleDbConnection(connectionString))
{
oleCnn.Open();
DataTable schema = oleCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheet1Name = schema.Rows[0]["TABLE_NAME"].ToString();
oleSql = String.Format(oleSql, sheet1Name);
using (OleDbDataAdapter oleDA = new OleDbDataAdapter(oleSql, oleCnn))
{
oleDA.Fill(ds);
}
}
dt = ds.Tables[0];
}
return dt;
}
public void ExcelReaderThrowsIfNotInitialized()
{
using (var excel = new ExcelDataReader(GetFileStream("testdata.xlsx"), true))
{
ExceptionAssert.Throws<InvalidOperationException>(() => { var value = excel["Name"]; });
}
}
