1

I am trying to read excel file from "Shared Documents" of SharePoint. The function i used to read excel file from C Drive using openXML It works fine but it doesn't work with SharePoint URL.

// Retrieve the value of a cell, given a file name, sheet name, 
    // and address name.
    public static string GetCellValue(string fileName,
        string sheetName,
        string addressName)
    {
        string value = null;

        // Open the spreadsheet document for read-only access.
        using (SpreadsheetDocument document = 
            SpreadsheetDocument.Open(fileName, false))
        {
            // Retrieve a reference to the workbook part.
            WorkbookPart wbPart = document.WorkbookPart;

SpreadsheetDocument.Open(fileName : says URI Format Not Supported.

filename is: "http:/mysharepoint/Shared Documents/file.xlsx"

3 Answers 3

1

Now the scenario is you are trying to access the spreadsheet which is in the SharePoint Document Library.

Assuming that you have path of the SpreadSheet in the document library.

For an example the path of the spread sheet is

http://TestSharePoint.com/sites/test/Shared%20Documents/TestExcel.xls

Please check the following code:


HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create("http://TestSharePoint.com/sites/test/Shared%20Documents/TestExcel.xls");
myReq.Credentials = new NetworkCredential("USERID", "PASSWORD", "DOMAIN");
WebResponse myResp = myReq.GetResponse();

byte[] b = null;
using (Stream stream = myResp.GetResponseStream())
using (MemoryStream ms = new MemoryStream())
{
    int count = 0;
    do
    {
    byte[] buf = new byte[1024];
    count = stream.Read(buf, 0, 1024);
    ms.Write(buf, 0, count);
    } while (stream.CanRead && count > 0);
    b = ms.ToArray();
    var document = SpreadsheetDocument.Open(ms, false);
    //Your Code Here

}

Enjoy!!!

0

You're going to need to get access to the stream through the SharePoint API and then call the (stream, boolean) signature of the Open method instead of what you're doing. The SharePoint folders don't reside on a physical drive so OpenXML's not going to know what to do with a URI string.

http://msdn.microsoft.com/en-us/library/office/cc562185.aspx

SPFile file = GetTheFileFromTheDocLibrary(); //I'll leave this part up to you
SPFileStream fileStream = file.OpenBinaryStream();
using(SpreadsheetDocument document = SpreadsheetDocument.Open(fileStream, false)
{
    //insert code here
}
3
  • what is SPFileStream
    – Sage Shah
    Jun 6, 2013 at 19:57
  • It's just a version of a file stream that you can call directly from the SharePoint API that doesn't require you to use System.IO and the like. Also, SharePoint knows how to work with these a little bit easier, relatively speaking. msdn.microsoft.com/en-us/library/… Jun 6, 2013 at 20:24
  • when i use SPFileStream VS underlines it and there is no option for Resolve. Whate Namespace or reference i should i use. My Application is MVC - Razor - C#
    – Sage Shah
    Jun 6, 2013 at 20:40
0

My working code:

                          SPFile file = item.File;
                            if (file.Exists)
                            {
                                byte[] byteArray = file.OpenBinary();
                                using (MemoryStream memStr = new MemoryStream())
                                {
                                    memStr.Write(byteArray, 0, (int)byteArray.Length);
                                    using (SpreadsheetDocument document = SpreadsheetDocument.Open(memStr, false))
                                    {
                                        SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

                                        string cellValue = null;

                                        foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
                                        {
                                            foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
                                            {
                                                if (sheetData.HasChildren)
                                                {
                                                    foreach (Row row in sheetData.Elements<Row>())
                                                    {
                                                        foreach (Cell cell in row.Elements<Cell>())
                                                        {
                                                            cellValue = cell.InnerText;

                                                            if (cell.DataType == CellValues.SharedString)
                                                            {
                                                                cellValue = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault().SharedStringTable.ElementAt(int.Parse(cell.CellValue.Text)).InnerText;
                                                                if (cellValue.IndexOf("Test", 0, StringComparison.CurrentCultureIgnoreCase) != -1)
                                                                {
                                                                    Console.WriteLine(file.ToString());
                                                                }
                                                            }
                                                            else
                                                            {
                                                                Console.WriteLine("cell val 2: " + cellValue);
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.