CellReference is null

[AlejandroFerrandiz]

Description

I have excel files that I'm trying to map but CellReference is null for each cell but, if I Open the file with Microsoft Office and save, after that if I try to map again CellReference is not null for each cell. I think is a bug in the library.

Information

• .NET Target: netstandard2.0
• DocumentFormat.OpenXml Version: 2.9.0

Expected

CellReference not null for all the case

[twsouthwick]

Can you give a sample of this? Could you provide the xml that you expect to see a CellREference and example SDK usage?

[AlejandroFerrandiz]

This is the file that CellReference is null for all the cell

abc.xlsx

[twsouthwick]

I'm not sure what to do with this. Can you provide repro steps? Ideally, like a unit test (that is described in the issue template), and without a file dependency (extract the specific xml snippet that is required if possible). At a minimum, I need a some code to reproduce that shows what you are seeing and a description of what you expect to see.

[AlejandroFerrandiz]

class Program
    {
        static void Main(string[] args)
        {
            var value = ExcelFileConverter.ToDataTable(@"C:\Users\alejandroferrandiz\Downloads\abc.xlsx");

            Console.ReadLine();
        }
    }

Check --> var cellReferent = cell.CellReference; /////////////// this is null instead A1, B1, C1, etc

public static class ExcelFileConverter
    {
        public static DataTable ToDataTable(string fileName, string sheetName = null)
        {
            using (var document = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbook = document.WorkbookPart;
                var sheets = workbook.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();

                Sheet sheet;
                if (string.IsNullOrEmpty(sheetName))
                    sheet = sheets.FirstOrDefault();
                else
                    sheet = sheets.FirstOrDefault(s => s.Name == sheetName);

                if (sheet == null)
                    throw new NullReferenceException("A workbook sheet could not be found.");

                var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
                var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                var rows = sheetData.Descendants<Row>();
                if (!rows.Any())
                    return null;

                var dt = new DataTable();
                foreach (Cell cell in rows.ElementAt(0))
                {
                    var cellReferent = cell.CellReference;  ///////////////  this is null instead A1, B1, C1, etc
                    ................
                }
                
                return dt;
            }            
        }
}

if I open the file abc.xlsx with Excel 2016 for example and save without change nothing and try to map again then var cellReferent = cell.CellReference; has the correct value A1 for example

[AlejandroFerrandiz]

Any update about this issue?

[rmboggs]

I'm not sure this is an issue with the library but with how the spreadsheet was created in the first place. I just unzipped the file and examined the worksheet xml file in the xls file you posted earlier and I didn't see any cell reference attributes list in any of the cell elements. Which is why the CellReference properties in the Cell objects are coming through as null. Do you know how the file was built in the first place?

As a side note, there was very little attributes in the file at all. Even the row elements didn't have the row index attributes set. I guess Excel is making assumptions if the information is missing.

Hope this helps.

[twsouthwick]

Thanks @rmboggs for taking a look. @AlejandroFerrandiz since we haven't heard from you in a while, I'm going to close the issue. If you still feel that this is an SDK issue, feel free to reopen to continue the discussion.

[arpana20singh]

I m also facing same issue? Any solution or help?

[S1r-Lanzelot]

Same issue

[Maracaipe611]

Same

[caglarsarikaya]

I have also same :/

[caglarsarikaya]

I solved my case, when I expor excel from the system, if I want to import it I got this ıssue, but If I create excel by writing down, there wasnt a problem, so I realize it, exporting method was not assignin cellreferences, excel should do it basically but didnt, so I just added a cellreference create method

 private string CreateCellReference(int column)
        {
            string result = string.Empty;
            //First is A
            //After Z, is AA
            //After ZZ, is AAA
            char firstRef = 'A';
            uint firstIndex = (uint)firstRef;
            int mod = 0;

            while (column > 0)
            {
                mod = (column - 1) % 26;
                result += (char)(firstIndex + mod);
                column = (column - mod) / 26;
            }

            return result;
        }

private Cell CreateCell(string colValue, int ColIndex, int rowIndex)
        {
            Cell cell = new Cell();
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(colValue);
            cell.CellReference =CreateCellReference(ColIndex + 1) + rowIndex; 
            return cell;
        }

a basic example for using

for (var i = 0; i< productsTable.Columns.Count; i++)
                {
                    var column = productsTable.Columns[i];
                    columns.Add(column.ColumnName);
                    Cell cell = CreateCell(column.ColumnName, i,1);
                    headerRow.AppendChild(cell);
                }

Created this solution from this
#1003 (comment)

[x-rune-x]

Hello, I'm getting what seems to be the same issue. When downloading an excel file and trying to access the cells using OpenXML, the CellReference for all the cells is set to none until I open and save the file manually.

Just wondering if there is a way to get around this as I don't have much control over how the file is created and don't think I can really apply caglarsarikaya's solution.

[Zabaa]

I m also facing same issue? Any solution or help?