Read excel file data in MVC

Question

I've did some searching, but haven't really found any good guides on how to do this.

I want to be able to upload an excel file, but I don't want to store it on the server. I want to read it from the computer of the user and go over the values. Afterwards I will write the data away into the database using EF.

To ask a clear question: How do I read data from an excel file on the computer of a user?

Upload it to the server, read it, upload to database and delete the file from server. That's all you can do. You can't directly read a file in user's machine with server side code. — Sriram Sakthivel, Apr 06 '16 at 11:11
MVC is a server-side technology to render a WebUI and expose calls into a domain. If you really want to load an excel sheet JUST into a web client and never send it to the server until you've processed it, that's just going to be JavaScript. The only way MVC enters into it, is once you have the data in the format you want on the client to post it back to the server. — StuperUser, Apr 06 '16 at 11:34
Why not just have a specific format and [read it from the .xls on the server using Oledb](http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp)? What editing do you need on the client that they can't do in excel? — StuperUser, Apr 06 '16 at 12:22
`NPOI` is your friend here. It is a library ported from Java and is free. http://stackoverflow.com/questions/5855813/npoi-how-to-read-file-using-npoi gives a simple example. you can download NPOI from [here](https://npoi.codeplex.com/) — Amila, Apr 06 '16 at 12:32
was randomly googling for "client javascript excel", and I found this https://github.com/faisalman/simple-excel-js , maybe you can try that. Good luck :-) — Thariq Nugrohotomo, Apr 06 '16 at 12:45

score 0 · Answer 1 · answered Nov 13 '18 at 10:34

I would say that the easiest way is to let the user upload the excel-file to your MVC-Controller and use DocumentFormat.OpenXml to read the file and then save it to the database or do whatever you want with the data. Assuming we are talking abount XLSX-files and not XLS-files.

DocumentFormat.OpenXml can be found on NuGet: https://www.nuget.org/packages/DocumentFormat.OpenXml/

score -1 · Answer 2 · answered Apr 06 '16 at 11:14

-1

Your friend is Microsoft.Office.Interop.Excel! It is a great library which will do just that. Just add the reference for it and Microsoft Office 14.0 Object Library.

There's tons of guide for it online

Good luck

answered Apr 06 '16 at 11:14

Nonagon

397
1
5
21

Well i believe you would have to make a project that is a .exe program and run the information through it that way – Nonagon Apr 06 '16 at 11:16
2

This is a bad idea - Microsoft themselves do not recommend using Excel interop on servers: https://support.microsoft.com/en-us/kb/257757. Your best bet is to use a 3rd party solution like Telerik. – rhughes Apr 06 '16 at 11:47
@rhughes really? interesting! thanks for that, i will read up on telerik – Nonagon Apr 06 '16 at 14:41

score -1 · Answer 3 · answered Nov 13 '18 at 10:16

You can try something like this:

public string excelParsing(string fullpath)
{
    string data = "";
    //Create COM Objects. Create a COM object for everything that is referenced
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fullpath);
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;

    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;

    //iterate over the rows and columns and print to the console as it appears in the file
    //excel is not zero based!!
    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
             //either collect data cell by cell or DO you job like insert to DB 
            if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                data += xlRange.Cells[i, j].Value2.ToString();
        }
    }

    return data;
}

http://sforsuresh.in/how-to-read-an-excel-file-using-asp-net-mvc4/

Read excel file data in MVC

3 Answers3