Upload Excel file and extract data - asp.net mvc 3

Question

I am wondering how do I extract data out of a 2007 excel file? I am using asp.net mvc 3. My plan is to have a upload section that you choose a file and hit upload. I have no clue after that what kind of format it will be or what I need to do to extract the values out.

Thanks

Answer 1

The easiest way to read excel spread sheets IMO is to use a DataAdapter and an OleDB connection as shown in this code project sample. The good thing about this is it does not have any dependencies on COM or the MS office libraries.

Answer 2

Once you have the spreadsheet uploaded and you save it to a file on the web server it is quite easy to use LINQ to select the rows from the spreadsheet. Check this out for more info.

http://code.google.com/p/linqtoexcel/

Answer 3

For reading Excel files, I learned to love Koogra. It's an open source library that reads both xls and xlsx files, and is very easy to use.

http://sourceforge.net/projects/koogra/

Answer 4

I've used NPOI and it's quite simple to use:

Using Xlfile As FileStream = New FileStream(FileName, FileMode.Open, FileAccess.Read)
    Using XLBook As HSSFWorkbook = New HSSFWorkbook(Xlfile)
        Using XLSheet As NPOI.SS.UserModel.Sheet = XLBook.GetSheetAt(0)

            Dim CurrentRow As NPOI.HSSF.UserModel.HSSFRow
                        Dim CurrentCell As NPOI.SS.UserModel.Cell
                        Dim RowEnum As IEnumerator = XLSheet.GetRowEnumerator()

            While RowEnum.MoveNext
                          If (RowEnum.Current IsNot Nothing) Then
                              CurrentRow = TryCast(RowEnum.Current, NPOI.HSSF.UserModel.HSSFRow)
                    Select Case CurrentCell.CellType
                        Case NPOI.SS.UserModel.CellType.STRING
                            ' CurrentCell.StringCellValue
                        Case NPOI.SS.UserModel.CellType.NUMERIC
                            ' CurrentCell.NumericCellValue.ToString()
                    End Select
            End While
        End Using
    End Using
    Xlfile.Close()
End Using