locked
Need help, please! RRS feed

  • Question

  •         public string XLGetCellValue(string fileName, string sheetName, string addressName)
            {
                //  Return the value of the specified cell.
                const string documentRelationshipType =
                  "http://schemas.openxmlformats.org/officeDocument/" +
                  "2006/relationships/officeDocument";
                const string worksheetSchema =
                  "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
                const string sharedStringsRelationshipType =
                  "http://schemas.openxmlformats.org/officeDocument/" +
                  "2006/relationships/sharedStrings";
                const string sharedStringSchema =
                  "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    
                string cellValue = null;
    
                //  Retrieve the stream containing the requested
                //  worksheet's info:
                using (Package xlPackage = Package.Open(fileName, FileMode.Open, FileAccess.Read))
                {
                    PackagePart documentPart = null;
                    Uri documentUri = null;
    
                    //  Get the main document part (workbook.xml)
                    foreach (PackageRelationship relationship in xlPackage.GetRelationshipsByType(documentRelationshipType))
                    {
                        //  There should only be one document part in the package.
                        documentUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), relationship.TargetUri);
                        documentPart = xlPackage.GetPart(documentUri);
                        //  There should only be one instance, 
                        //  but get out no matter what.
                        break;
    
                    }
    
                    if (documentPart != null)
                    {
                        // Load the contents of the workbook.
                        XmlDocument doc = new XmlDocument();
                        doc.Load(documentPart.GetStream());
    
                        //  Create a namespace manager, so you can search.
                        //  Add a prefix (d) for the default namespace.
                        NameTable nt = new NameTable();
                        XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
                        nsManager.AddNamespace("d", worksheetSchema);
                        nsManager.AddNamespace("s", sharedStringSchema);
    
                        string searchString =
                          string.Format("//d:sheet[@name='{0}']", sheetName);
                        XmlNode sheetNode =
                          doc.SelectSingleNode(searchString, nsManager);
    
                        if (sheetNode != null)
                        {
                            //  Get the relId attribute:
                            XmlAttribute relationAttribute =
                              sheetNode.Attributes["r:id"];
    
                            if (relationAttribute != null)
                            {
                                string relId = relationAttribute.Value;
    
                                //  First, get the relation between the 
                                // document and the sheet.
                                PackageRelationship sheetRelation =
                                  documentPart.GetRelationship(relId);
                                Uri sheetUri = PackUriHelper.ResolvePartUri(
                                  documentUri, sheetRelation.TargetUri);
                                PackagePart sheetPart = xlPackage.GetPart(sheetUri);
    
                                //  Load the contents of the workbook.
                                XmlDocument sheetDoc = new XmlDocument(nt);
                                sheetDoc.Load(sheetPart.GetStream());
    
                                XmlNode cellNode = sheetDoc.SelectSingleNode(
                                string.Format("//d:sheetData/d:row/d:c[@r='{0}']", addressName),
                                nsManager);
    
                                if (cellNode != null)
                                {
                                    //  Retrieve the value. The value may be stored within 
                                    //  this element. If the "t" attribute contains "s", then
                                    //  the cell contains a shared string, and you must look 
                                    //  up the value individually.
                                    XmlAttribute typeAttr = cellNode.Attributes["t"];
                                    string cellType = string.Empty;
    
                                    if (typeAttr != null)
                                    {
                                        cellType = typeAttr.Value;
    
                                    }
    
                                    XmlNode valueNode = cellNode.SelectSingleNode("d:v", nsManager);
    
                                    if (valueNode != null)
                                    {
                                        cellValue = valueNode.InnerText;
    
                                    }
    
                                    //  Check the cell type. At this point, this code only checks
                                    //  for booleans and strings individually.
                                    if (cellType == "b")
                                    {
                                        if (cellValue == "1")
                                        {
                                            cellValue = "TRUE";
                                        }
    
                                        else
                                        {
                                            cellValue = "FALSE";
                                        }
                                    }
    
                                    else if (cellType == "s")
                                    {
                                        //  Go retrieve the actual string from the associated string file.
                                        foreach (System.IO.Packaging.PackageRelationship
                                          stringRelationship in
                                          documentPart.GetRelationshipsByType(sharedStringsRelationshipType))
                                        {
                                            //  There should only be one shared string reference, 
                                            // so you exit this loop immediately.
                                            Uri sharedStringsUri = PackUriHelper.ResolvePartUri(
                                              documentUri, stringRelationship.TargetUri);
                                            PackagePart stringPart = xlPackage.GetPart(sharedStringsUri);
    
                                            if (stringPart != null)
                                            {
                                                //  Load the contents of the shared strings.
                                                XmlDocument stringDoc = new XmlDocument(nt);
                                                stringDoc.Load(stringPart.GetStream());
    
                                                //  Add the string schema to the namespace manager:
                                                nsManager.AddNamespace("s", sharedStringSchema);
    
                                                int requestedString = Convert.ToInt32(cellValue);
                                                string strSearch = string.Format(
                                                  "//s:sst/s:si[{0}]", requestedString + 1);
                                                XmlNode stringNode =
                                                  stringDoc.SelectSingleNode(strSearch, nsManager);
    
                                                if (stringNode != null)
                                                {
                                                    cellValue = stringNode.InnerText;
    
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    return cellValue;
    
                }
            }
    Okay, so above is the code I have used to pull data from an Excel Cell.  I am trying to parse the contents of an Excel file and then dump said contents into an array that I can use later (that code isn't included).  The problem is, when I go to use the method I created, I get a null reference exception.  Any help would be appreciated!

    • Moved by spacewrangler Tuesday, July 28, 2009 3:53 AM (From:Building Development and Diagnostic Tools for .Net)
    Wednesday, July 22, 2009 9:54 PM

Answers

  • Usually Visual Studio don't just says 'Exception' - it try to open message box (in Debug build) with full (!) description and even member/function that throws this exception. There's another option (usually on the bottom) - there's what we call 'Call Stack' - this is a list of function that was executed and eventually cause the exception.

     You can get a lot of information from Visual Studio about exception and where it occurs. If you want to do it manually - you can write try/catch blocks around whole function.

    • Marked as answer by WaffleMan Friday, April 2, 2010 3:25 PM
    Thursday, August 13, 2009 9:56 AM