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

Question

I have some data that is currently stored in an Excel workbook. It makes sense that the data is in Excel (in that it is easy to manage, easy to expand, do calculations, etc.), but some of the data there is needed by an automated process, so from this point of view it would be more convenient if it was in the database.

To provide information more visibility, workflow, etc. I am considering moving it to SharePoint. In fact, turning it into a SharePoint form would be tedious and time-consuming, and then flexibility / convenience would be lost; instead, I'm going to just save the current Excel file in a SharePoint library.

My problem then would be: how does an automated process extract the desired values ​​from an Excel workbook that now lives in a SharePoint library? Is this something you can use for Excel Services? Or is there another / better way? And even if it can be done, is it wise to do it?

Answer 1

After going through something like this, I can say that in fact it is not so badly obtained from the Excel file in the document library. As a result, I wrote my own workflow action (used in the SharePoint Designer workflow) that reads values ​​from an Excel file for processing. I decided to choose NPOI to handle all Excel operations.

NPOI, - :

// 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...
}

.

Answer 2

, . . SQL , , , .

, .

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;
    }