c# - 为什么无法通过 OpenXML 获取 excel 文件的单元格值？

Question

我必须读取 excel 文件作为示例：

我的代码阅读文件：

        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 仅获得正确的第一列：

为什么无法通过 OpenXML 获取单元格的文本值？

标签： c#openxmlopenxml-sdk

Answer 1

您可以通过使用 DataTable 的 Open XML 来执行此操作

//将Datatable转换为excel字节内存流

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

//将内存流转换为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();
                    }