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?

score 2 · Accepted Answer · answered Jan 07 '21 at 08:47

To read text value of cell, i have to check datatype SharedString, DateTime, and convert value:

int i = 0;
foreach (Cell c in r.Elements<Cell>())
{
    var value = c.CellValue.Text;
    if (c.DataType != null && c.DataType == CellValues.SharedString)
    {
        var stringId = Convert.ToInt32(c.InnerText); 
        value=workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(stringId).InnerText;
    }
    if (i == 3&& value!="")
    {
        value = DateTime.FromOADate(double.Parse(value)).ToString ("yyyy/MM/dd");
    }
    dt.Rows[dt.Rows.Count - 1][i] = value;
    i++;
}

score 0 · Answer 2 · answered Jan 07 '21 at 04:54

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();
                    }

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

2 Answers2