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

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?

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.

Yes, I am trying to extract a range of cells on several sheets within a workbook. I was able to use some of the code below in a console application and view the data within the command window. Now I need to dump the data to a SQL Table and was looking for some examples on how to accomplish this and make sure I am going down the correct coding path.

Here is a snapshot of the code I am using.

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
        using (SPSite site = new SPSite(SPContext.Current.Site.Url))
        {
            using (SPWeb web = site.RootWeb)
            {
                SPList docList = web.Lists[__ListId];
                SPListItem docItem = docList.GetItemById(__ListItem);
                SPFile docFile = docItem.File;

                using (Stream stream = docFile.OpenBinaryStream())
                {
                    HSSFWorkbook wb = new HSSFWorkbook(stream);

                    //loop through each sheet in file, ignoring the first sheet
                    for (int i = 1; i < 0; i++)
                    {
                        NPOI.SS.UserModel.Name name = wb.GetNameAt(i);
                        String sheet = wb.GetSheetName(i);

                        NPOI.SS.UserModel.Name nameRange = wb.CreateName();
                        nameRange.NameName = ("DispatchCells");
                        //start at a specific area on the sheet
                        nameRange.RefersToFormula = (sheet + "!$A$11:$AZ$100");

                    }

                    wb.Write(stream);

                }

            }
        }
        return ActivityExecutionStatus.Closed;
    }