Get all the values from excel file by using linqtoexcel

Question

I'm using linqtoexcel in my asp.net mvc 4 project to read an excel file & get all the values from there. But I'm only getting the values of last row. Here are my codes,

Controller

    public ActionResult ExcelRead()
    {
        string pathToExcelFile = ""
        + @"C:\MyFolder\ProjectFolder\sample.xlsx";

        string sheetName = "Sheet1";

        var excelFile = new ExcelQueryFactory(pathToExcelFile);
        var getData = from a in excelFile.Worksheet(sheetName) select a;

        foreach (var a in getData)
        {
            string getInfo = "Name: "+ a["Name"] +"; Amount: "+ a["Amount"] +">>   ";
            ViewBag.excelRead = getInfo;
        }
        return View();
    }

View

@ViewBag.excelRead

How can I get the values from all the rows? Need this help badly! Thanks.

here on foreach loop the Viewbag.excelRead is assigning the current row..thats why you are getting only last row in view..you should assign this to a list and call the list in the view — Sachu, May 25 '15 at 06:02
Thanks. Can you give me an example by code? I'm kinda noob in asp.net. — Shihan Khan, May 25 '15 at 06:04

Siva Senthil · Accepted Answer · 2015-05-25T13:31:26.490

6

Try this (expanding on @Sachu's comment to the question) -

public ActionResult ExcelRead()
{
    string pathToExcelFile = ""
    + @"C:\MyFolder\ProjectFolder\sample.xlsx";

    string sheetName = "Sheet1";

    var excelFile = new ExcelQueryFactory(pathToExcelFile);
    var getData = from a in excelFile.Worksheet(sheetName) select a;
    string getInfo = String.Empty;

    foreach (var a in getData)
    {
        getInfo += "Name: "+ a["Name"] +"; Amount: "+ a["Amount"] +">>   ";

    }
    ViewBag.excelRead = getInfo;
    return View();
}

edited May 25 '15 at 13:31

answered May 25 '15 at 06:42

Siva Senthil

610
6
22

I'm getting `System.Collections.Generic.List1[System.String]` as `ViewBag.excelRead` value in view. – Shihan Khan May 25 '15 at 07:07
@SinOscuras, I have edited the code, check this one. I have removed the List since, you are probably binding to a HTML div instead of looping on the result. You can do this way as well for brevity. – Siva Senthil May 25 '15 at 13:32
@Sin Oscuras, If you require line break between two rows of "Name..." then do add
at the end of string. – Siva Senthil May 25 '15 at 13:33
Very nice workaround. Tnx a lot for your patience. This worked fine! :) – Shihan Khan May 25 '15 at 14:50
what is sheet name? – Zeeshan Ahmad Khalil Sep 28 '20 at 14:24

Sachu · Answer 2 · 2015-05-25T07:38:50.600

1

Make the getDate as .ToList()

var getData = (from a in excelFile.Worksheet(sheetName) select a);
List<string> getInfo = new List<string>();

foreach (var a in getData)
{
    getInfo.Add("Name: "+ a["Name"] +"; Amount: "+ a["Amount"] +">>   ");

}
ViewBag.excelRead = getInfo;
return View();

Then pass this to the view and do a foreach loop with @ViewBag.excelRead

    foreach (var data in @ViewBag.excelRead)
    {
    .....
    }

Hope this helps

edited May 25 '15 at 07:38

answered May 25 '15 at 06:09

Sachu

7,555
7
55
94

I'm getting `Compilation error` in view for `@model List` – Shihan Khan May 25 '15 at 06:19
`Compiler Error Message: CS0246: The type or namespace name 'getData' could not be found (are you missing a using directive or an assembly reference?)` – Shihan Khan May 25 '15 at 06:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78664/discussion-between-sachu-and-sin-oscuras). – Sachu May 25 '15 at 07:19

Get all the values from excel file by using linqtoexcel

2 Answers2

Linked