0

For some reason ExcelReaderFactory.CreateOpenXmlReader() throws NullReferenceException while trying to read .xlsx files created by code. For document creating I use OpenXml library. Here is the way of creating .xlsx I used in my code:

public void CreateExcelFile(string filePath, string[][] rowsData)
{
    using (SpreadsheetDocument spreedDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = spreedDocument.WorkbookPart;
        if (workbookPart == null)
        {
            workbookPart = spreedDocument.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();
        }
        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        var sheetData = new SheetData();
        worksheetPart.Worksheet = new Worksheet(sheetData);
        if (workbookPart.Workbook.Sheets == null)
        {
            workbookPart.Workbook.AppendChild(new Sheets());
        }
        var sheet = new Sheet()
        {
            Id = workbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "Sheet1"
        };
        var excelRowIndex = 1;
        foreach (var row in rowsData)
        {
            var excelRow = new Row {RowIndex = (UInt32) excelRowIndex};
            foreach (var value in row)
            {
                excelRow.AppendChild(AddCellWithText(value));
            }
            sheetData.AppendChild(excelRow);
            excelRowIndex++;
        }
        workbookPart.Workbook.Sheets.Append(sheet);
        workbookPart.Workbook.Save();
    }
}

public static Cell AddCellWithText(string text)
{
    var cell = new Cell {DataType = CellValues.InlineString};
    var inlineString = new InlineString();
    var cellText = new Text {Text = text};
    inlineString.AppendChild(cellText);
    cell.AppendChild(inlineString);
    return cell;
}

The issue happens only for .xlsx files generated by code. For documents created in Excel everything work fine. So I think that there are some issues in the files generating. Any ideas?

Update: This is the cocde where the exception happens:

static DataSet ReadExcelFile(string filePath, bool isFirstRowColumnNames = false) 
{ 
    DataSet result; 
    using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) 
    { 
         using (var excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs))
         // ...
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • So `SpreadsheetDocument.Create` throws the exception? – Tim Schmelter Sep 18 '18 at 14:57
  • No. .xlsx file seems to be fine. I can open them with Excel. But further in my code I'm trying to get the DataSet from .xlsx file. And it fails on ExcelReaderFactory.CreateOpenXmlReader(Stream stream) with NullReferenceException. – Aleksey Ermalinsky Sep 18 '18 at 15:00
  • That line isnt even in this code – Dave Sep 18 '18 at 15:13
  • @AlekseyErmalinsky: there's an edit button :) – Tim Schmelter Sep 18 '18 at 15:18
  • @AlekseyErmalinsky: You're welcome. But how is the first code related to the second, where you call `ReadExcelFile`? Have you used the debugger, is the path valid that is passed to `ReadExcelFile`? Is `CreateOpenXmlReader` really throwing the exception or is your code throwing it afterwards? Show the stacktrace of the error. In general your question is a duplicate of this: https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it – Tim Schmelter Sep 18 '18 at 15:22
  • @TimSchmelter I use ReadExcelFile to create DataSet objects for further comparing of .xlsx files. So in case of the files, generated directly in Excel - everything is fine. But when I change them with files, generated by the code in the first part, I get this exception. – Aleksey Ermalinsky Sep 18 '18 at 15:25
  • @TimSchmelter I'm sure that everything is fine with Path. The Stream length is more than 8000 – Aleksey Ermalinsky Sep 18 '18 at 15:27

0 Answers0