CipherIS
asked on
C# Import Excel Data into a Generic List
I've been tasked to write an app that gets data from MS Excel. I wrote below and have modified it a bit for the different spreadsheets. Because of this I thought of making it Generic.
Issue I have is that the Model could contain 1 field or 2 fields, etc.
Is there a way to write this so it is generic?
Issue I have is that the Model could contain 1 field or 2 fields, etc.
Is there a way to write this so it is generic?
public interface IExcelReader<T>
{
List<T> GetExcelData(string filename, string worksheet, string rowstart, string rowend);
}
public class ExcelParser<T> : IExcelReader<T> where T : new()
{
private Microsoft.Office.Interop.Excel.Workbook _excelbook = null;
private Microsoft.Office.Interop.Excel.Application _excelapp = null;
private Microsoft.Office.Interop.Excel.Worksheet _excelsheet = null;
private string _worksheet = string.Empty;
private string _filename = string.Empty;
private string _path = string.Empty;
private string _rowstart = string.Empty;
private string _rowend = string.Empty;
List<T> result = new List<T>();
private List<ComponetsFullModel> _manufpartno = new List<ComponetsFullModel>();
public List<T> GetExcelData(string filename, string worksheet, string rowstart, string rowend)
{
_worksheet = worksheet;
Uri uri = new Uri(filename);
if (uri.IsFile)
{
_filename = System.IO.Path.GetFileName(uri.LocalPath);
_path = System.IO.Path.GetDirectoryName(uri.LocalPath);
ParseData();
}
return result;
}
private void ParseData()
{
_excelapp = new Application();
_excelapp.Visible = true;
_excelbook = _excelapp.Workbooks.Open(_path + @"\" + _filename);
try
{
_excelsheet = (Worksheet)_excelbook.Sheets[_worksheet];
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
_excelbook.Close(false);
_excelapp.Quit();
return;
}
_excelsheet.Select(Type.Missing);
int lastRow = _excelsheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
for (int index = 2; index <= lastRow; index++)
{
System.Array excelvalues = (System.Array)_excelsheet.get_Range(_rowstart + index.ToString(),
_rowend + index.ToString()).Cells.Value;
if (excelvalues != null)
{
var check = excelvalues.OfType<object>().Select(o => o.ToString()).ToList();
if (check.Count > 0)
{
var values = new List<string>();
foreach (object o in excelvalues)
{
string addtovalue = string.Empty;
if (o == null)
addtovalue = string.Empty;
else
addtovalue = o.ToString();
values.Add(addtovalue);
};
if (values.Count > 0)
{
//_manufpartno.Add(new ManufacturerPartNoModel()
//{
// Manufacturer = values[0],
// PartNo = values[1]
//});
}
}
}
}
_excelbook.Close(false);
_excelapp.Quit();
}
}
Thanks
ASKER
The issue is that I don't want to specify the model as below
_manufpartno.Add(new ManufacturerPartNoModel()
{
Manufacturer = values[0],
PartNo = values[1] != null ? values[1] : "" // Consume value iff it is non null OR as per the expected criteria
});
I want to populate T and return it to the ManufacturerPartNoModel from the calling class.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
For example:
Open in new window