How to get a value out of an Excel workbook stored in a SharePoint document library?

Question

I have some data that's currently stored in an Excel workbook. It makes sense for the data to be in Excel (in that it's easy to manage, easy to extend, do calcs, etc.) but some of the data there is required by an automated process, so from that point of view it would be more convenient if it were in a database.

To give the information more visibility, workflow, etc. I'm thinking of moving it to SharePoint. Actually turning it into a SharePoint form would be tedious & time-consuming, and then the flexibility/convenience would be lost; instead, I'm thinking of simply storing the current Excel file within a SharePoint library.

My problem then would be: how can the automated process extract the values it needs from the Excel workbook that now lives within the SharePoint library? Is this something that Excel Services can be used for? Or is there another/better way? And even if it can be done, is it a sensible thing to do?

Answer 1

Having gone through something similar, I can tell you it actually isn't that bad getting values out of an Excel file in a document library. I ended up writing a custom workflow action (used within a SharePoint Designer workflow) that reads values out of the Excel file for processing. I ended up choosing NPOI to handle all of the Excel operations.

Using NPOI, you can do something like this:

// get the document in the document library
SPList myList = web.Lists[listGuid];
SPListItem myItem = myList.GetItemById(ListItem);
SPFile file = myItem.File;

using (Stream stream = file.OpenBinaryStream())
{
    HSSFWorkbook workbook = new HSSFWorkbook(stream);
    HSSFSheet sheet = workbook.GetSheet("Sheet1");
    CellReference c = new CellReference("A1");
    HSSFRow row = sheet.GetRow(c.Row);
    HSSFCell cell = row.GetCell(c.Col);
    string cellValue = cell.StringCellValue;

    // etc...
}

You could easily put this in a console application as well.