如何从存储在SharePoint文档库中的Excel工作簿中获取值?

经历过类似的事情之后,我可以告诉你,从文档库中的Excel文件中获取值并不是那么糟糕。最后,我编写了一个自定义工作流操作(在SharePoint Designer工作流中使用),从Excel文件中读取值进行处理。我最终选择了 NPOI

使用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...
}

您也可以轻松地将其放入控制台应用程序中。

下面是我正在使用的代码的快照。

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