![getdata excel getdata excel](http://blog.vlbteched.com/wp-content/uploads/2018/04/Excel-Get-Data-Image-3.png)
Select this if you want to connect using Windows authentication.ĭatabase Select this if you want to connect using SQL Server authentication. Select the authentication mode to connect to the SQL Server database. For more information, see Import Data from Database using Native Database Query. If you want to import data using a native database query, specify your query in the SQL Statement box. Optionally, you can specify a Database Name as well.
![getdata excel getdata excel](https://analysistabs.com/wp/wp-content/uploads/2013/02/Reading-data-from-Range.png)
In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. I do think its is fine that you give them the option to specify isFirstRowAsColumnNames, that could be very handy.Select Data > Get Data > From Database > From SQL Server Database. If it wasn't for the bool isFirstRowAsColumnNames I would suggest you actually go forward with the whole OOP thing, and have the workbook load when you construct it, and take advantage of OO design by actually having internal data besides just the path. Return from DataRow row in GetExcelWorkSheet(path, workSheetName, isFirstRowAsColumnNames).Rows select row Īs mentioned in the comments, you are not accounting for all Excel filetypes. Private static IEnumerable GetData(string path, string workSheetName, bool isFirstRowAsColumnNames = true) Throw new WorksheetDoesNotExistException(string.Format("The worksheet does not exist, has an incorrect name, or does not have any data in the worksheet", workSheetName)) Private DataTable GetExcelWorkSheet(string workSheetName, bool isFirstRowAsColumnNames)ĭataSet dataSet = GetExcelDataAsDataSet(isFirstRowAsColumnNames) ĭataTable workSheet = dataSet.Tables Return GetExcelDataReader(isFirstRowAsColumnNames).AsDataSet() Private DataSet GetExcelDataAsDataSet(bool isFirstRowAsColumnNames) Throw new FileToBeProcessedIsNotInTheCorrectFormatException("The file to be processed is not an Excel file") ĭataReader.IsFirstRowAsColumnNames = isFirstRowAsColumnNames Throw exception for things you cannot correct Using (FileStream fileStream = File.Open(path, FileMode.Open, FileAccess.Read))ĭataReader = ExcelReaderFactory.CreateBinaryReader(fileStream) ĭataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream) Private IExcelDataReader GetExcelDataReader(bool isFirstRowAsColumnNames) The ExcelData class: public class ExcelData LivingArea = dataRow.ToString()īasically, I figured that I needed a class on top of the ExcelData class, because it seemed somewhat higher level. Return dataRows.Select(dataRow => new Recipient() Var dataRows = excelData.GetData(worksheetName, isFirstRowAsColumnNames) Public IEnumerable GetData(bool isFirstRowAsColumnNames = true) Public ReadInData(string path, string worksheetName) The ReadInData class: public class ReadInData
GETDATA EXCEL CODE
In this code I create a new ReadInData class, to which I pass the path, the name of the file and the name of the Excel sheet that I want to read.Ĭoncern here: is it okay to pass those things within that file?
![getdata excel getdata excel](https://i.ytimg.com/vi/S1jUHP_CDPo/maxresdefault.jpg)
IEnumerable recipients = readInData.GetData() Ĭatch (WorksheetDoesNotExistException ex)Ĭatch (FileToBeProcessedIsNotInTheCorrectFormatException ex) The entry point of my code: class Program Passing of the isFirstRowAsColumnNames parameterīecause this is code for a company, I changed the names of a couple of classes, so I know they aren't the best of names.Should I encapsulate my ExcelData class within ReadInData, or keep it like it is?.The overall construction of the code and code quality.On top of that class I made a ReadInData class, which will get the specific columns I want from the Excel sheet. I've implemented a low-level class called ExcelData which uses the ExcelDataReader and does things like figuring out if it is an ".xls" of ".xslx" file (or maybe something completely unrelated!) etc. I want to import an Excel file, and read the rows of certain columns.