Get a List that is created reading an Excel File C#

Question

In my Web API Project I have a method that reads an excel file, and set some values to a list, this part works, but when I want to get that list through a HTTP Get method only returns an empty list like this [{ },{ },{ }] and something I have to say is that those { } are exactly the number of users I have on my .xlsx file. I don't know if the problem is by returning that list or that I coded an incorrect Get Method.

HTTP get Method

[HttpGet]
    public List<User> GetUsersList()
    {
        List<User> userList = new List<User>();

        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\Username\UploadFile\Base.xlsx");
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;
        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;
        string code = "";
        string rolename = "";
        int count = 2;
        for (int i = 3; i <= rowCount; i++)
        {
            count += 1;
            for (int j = 1; j <= colCount; j++)
            {
                if (count == i & j == 1)
                {
                    code = xlRange.Cells[i, j].Value2.ToString();
                }
                else
                {
                    if (count == i & j == 2)
                    {
                        rolename = xlRange.Cells[i, j].Value2.ToString();
                        User user = new User(code, rolename);
                        userList.Add(user);
                    }
                }

            }
        }


        return userList;
    }

Here I create the User Class

public class User
    {
        private string _code;
        private string _rolename;

        public User(string code, string rolename)
        {
            _code = code;
            _rolename = rolename;
        }
    }

You need to give us more detail as to what you mean by "it doesn't work". — PaulF, Nov 15 '17 at 16:01
your user Class has no public properties, so nothing is serialized also — Boo, Nov 15 '17 at 16:04
Your real problem is that `_code` and `_rolename` are private, and so are not serialized. Mark them as public instead. — dbc, Nov 16 '17 at 07:53

score 1 · Answer 1 · answered Nov 15 '17 at 17:31

As someone who also tried to use the Microsoft Excel Interoperability libraries on a server, I advise you not to use it.

One of the biggest problems when using it is the fact that it needs the right version of MS Excel to be installed on the server.

There are a lot of other libraries, free and paid, that you can use to read Excel files. If you want a suggestion, use ClosedXML

https://github.com/ClosedXML/ClosedXML.

It had enough documentation to get you started. And when you are posting a question please post the error you are getting too. Otherwise the community wouldn't be able to help you.

Get a List that is created reading an Excel File C#

1 Answers1