c# - Why can't get cell value of excel file by OpenXML?

Question

I must read excel file as sample:

My code reading file:

        SPWeb web = SPContext.Current.Web;          
        SPFile file = web.GetFile(path);
        Stream dataStream = file.OpenBinaryStream();
        // Open the document .
        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(dataStream, true))
        {
            WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Sheet1");

            if (worksheetPart != null)
            {
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                DataTable dt = new DataTable();
                dt.Columns.Add("Id");
                dt.Columns.Add("Title");
                dt.Columns.Add("Gender");
                dt.Columns.Add("Birthday");

                foreach (Row r in sheetData.Elements<Row>())
                {
                    if (r.RowIndex.Value > 1)
                    {
                        dt.Rows.Add();
                        int i = 0;
                        foreach (Cell c in r.Elements<Cell>())
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = c.CellValue.Text;
                            i++;
                        }
                    }
                    
                }                  
                
                GridView1.DataSource = dt;
                GridView1.DataBind();                  

               
            }
        }

Gridview Result only get correct first columns:

Why can't get text value of cell by OpenXML?

Answer 1

You can do this by Open XML with the DataTable

//Converting Datatable to excel bytes Memory Stream

                dte=DataTable();    //Your DataTable

                XLWorkbook wb = new XLWorkbook();
                wb.Worksheets.Add(dte, SheetName);

                var Excelbytes = new byte[0];
                using (var ms = new MemoryStream())
                {
                    wb.SaveAs(ms);
                    Excelbytes = ms.ToArray();    
                }

                MemoryStream memorystream = new MemoryStream();

//converting Memory stream to excel

        using (MemoryStream memStream = new MemoryStream())
                    {
                        memStream.Write(byteArray, 0, byteArray.Length);
                        XLWorkbook wb = new XLWorkbook(memStream, XLEventTracking.Disabled);
          
                        string myName = Server.UrlEncode(FileName + ".xlsx");
                        MemoryStream stream = (MemoryStream)GetStream(wb);

                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition", "attachment; filename=" + myName);
                        Response.ContentType = "application/vnd.ms-excel";
                        Response.BinaryWrite(stream.ToArray());
                        Response.End();
                    }