Exception while import from excel using openXML

Question

I would like to deserialize all the data from the excel file to list.

I am using this code

class ExcelImport
{
    Workbook workBook;
    SharedStringTable sharedStrings;
    IEnumerable<Sheet> workSheets;
    WorksheetPart custSheet;
    WorksheetPart orderSheet;
    string FilePath;
    ExcelStorage provider;
    Stiker[] ans;
    List<Stiker> StikerList;

    public ExcelImport(string fp)
    {
        FilePath = fp;


    }




    public List<Stiker> dothejob()
    {
        using (SpreadsheetDocument document =
    SpreadsheetDocument.Open(FilePath, true))
        {
            StikerList= new List<Stiker>();
            workBook = document.WorkbookPart.Workbook;
            workSheets = workBook.Descendants<Sheet>();
            sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
            StikerList = Stiker.LoadStiker(custSheet.Worksheet, sharedStrings);
            return StikerList;
        }
    }

But from some reson I get exception in the line:sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

that "Object reference not set to an instance of an object.".

After the above saggestion found that the

if (sharedStringTablePart == null)
{
// report a problem
}

rerurn null

Any idea?

Jeremy McGee · Accepted Answer · 2011-10-12 14:20:18Z

1

One of the properties in the source line will be "null" and have no value.

You'll want to either use a debugger to figure this out (set a breakpoint on the line and hover the mouse over each property), or break down the line into separate statements. Something like:

var workBookPart = document.WorkbookPart;

if (workBookPart == null)
{
    // do something to report a problem
}

var sharedStringTablePart = workBookPart.SharedStringTablePart;
if (sharedStringTablePart == null)
{
    // report a problem
}

sharedStrings = sharedStringTablePart.SharedStringTable;

This way your code can determine at run-time if there's an issue: this kind of "defensive" idea is usually a good idea when working with data created by some system other than your own.

answered Oct 12, 2011 at 14:20

Jeremy McGee

25.3k10 gold badges65 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MoShe Over a year ago

the stange thing is that for one file its work and fro the other no

Jeremy McGee Over a year ago

So it sounds like the two files have a different structure. See my updated answer for ideas on how to figure this out.

MoShe Over a year ago

hi the ar sharedStringTablePart = workBookPart.SharedStringTablePart; return null any idea why it happened? and why it happend only in some file?

Lapenkov Vladimir Over a year ago

the same thing in some excel files, others are ok. What SharedStringTable is pesponsible for?

Lapenkov Vladimir · Accepted Answer · 2015-08-11 13:28:30Z

1

After half a day parsing 2007, 2010, 2013 and convert some 2003->2007 and parsing them I got one method of parsing excel emitting SharedStringTable in some cases

 var link = document.WorkbookPart.SharedStringTablePart;
           Func<Cell, string> selector = (cell) => cell.InnerText;

          if (link != null)
           {
               SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
               selector = (cell) => cell.DataType == null ? cell.InnerText : cell.DataType == CellValues.SharedString ? sharedStringTable.ElementAt(Int32.Parse(cell.InnerText)).InnerText : cell.InnerText;

          }

         var values = wsPart.Worksheet.Descendants<Cell>().Select(cell =>selector(cell) ).ToArray();

edited Aug 11, 2015 at 13:28

answered Aug 11, 2015 at 13:21

Lapenkov Vladimir

3,2386 gold badges30 silver badges38 bronze badges

1 Comment

Lapenkov Vladimir Over a year ago

This works with either xlsx files (even converted from 2003) :) I hope to help ones like me killed by ms excel versionism

Collectives™ on Stack Overflow

Exception while import from excel using openXML

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related