Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

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?
    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();
        }
    }

Open in new window

Thanks
Avatar of Shahan Ayyub
Shahan Ayyub
Flag of Pakistan image

May be you can prepare a list of known number of elements and consume iff valid value is available at that index.

For example:

             
var values = new List<string>(2);  // Enter capacity here, your model "ManufacturerPartNoModel" has two properties
var i = 0;
foreach (object o in excelvalues)
{
       string addtovalue = string.Empty;
       if (o == null)
              addtovalue = string.Empty;
       else
             addtovalue = o.ToString();

       values[i] = addtovalue;
       i += 1;
};

if (values.Count > 0)
{
       _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
       });
}

Open in new window

Avatar of CipherIS

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
       });

Open in new window

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 TRIAL
Members 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.