从Excel文件读取Open XML

Question

从Excel文件读取Open XML

34

我想将OpenXml SDK 2.5应用到我的项目中。我按照这个链接上的步骤执行了一切。

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO.Packaging;


static void Main(string[] args)
        {

            String fileName = @"C:\OPENXML\BigData.xlsx";
            // Comment one of the following lines to test the method separately.
            ReadExcelFileDOM(fileName);    // DOM
            //ReadExcelFileSAX(fileName);    // SAX
        }

        // The DOM approach.
        // Note that the code below works only for cells that contain numeric values.
        // 
        static void ReadExcelFileDOM(string fileName)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                string text;

                int rowCount= sheetData.Elements<Row>().Count();

                foreach (Row r in sheetData.Elements<Row>())
                {
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        text = c.CellValue.Text;
                        Console.Write(text + " ");
                    }
                }
                Console.WriteLine();
                Console.ReadKey();
            }
        }

但是我没有得到任何行。它没有进入循环。注意：我也在我的计算机上安装了 openXml sdk 2.5。

我发现下面的代码对于数值有效，但对于字符串值，则写入0、1、2...

 private static void Main(string[] args)
            {
                var filePath = @"C:/OPENXML/BigData.xlsx";
                using (var document = SpreadsheetDocument.Open(filePath, false))
                {
                    var workbookPart = document.WorkbookPart;
                    var workbook = workbookPart.Workbook;

                    var sheets = workbook.Descendants<Sheet>();
                    foreach (var sheet in sheets)
                    {
                        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                        var sharedStringPart = workbookPart.SharedStringTablePart;
                        //var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();

                        string text;
                        var rows = worksheetPart.Worksheet.Descendants<Row>();
                        foreach (var row in rows)
                        {
                            Console.WriteLine();
                            int count = row.Elements<Cell>().Count();

                            foreach (Cell c in row.Elements<Cell>())
                            {

                                text = c.CellValue.InnerText;

                                Console.Write(text + " ");

                            }
                        }
                    }
                }

                Console.ReadLine();
            }

- altandogan

4

之所以会出现0、1、2……这样的数字，是因为Excel使用了共享字符串表。请查看下面我的回答，了解如何基本地访问实际文本。 - shunty

4个回答

17

我和原帖作者有相同的问题，上面的答案对我没有用。

我认为问题出在这里：当你在Excel中创建一个文档（而不是通过编程），默认情况下会有3个工作表，Sheet1的行数据对应的是最后一个WorksheetParts元素，而不是第一个。

我通过在Visual Studio中为document.WorkbookPart.WorksheetParts设置监视器，展开结果，然后查看所有子元素，直到找到一个HasChildren = true的SheetData对象才发现了这一点。

尝试这个：

// open the document read-only
SpreadSheetDocument document = SpreadsheetDocument.Open(filePath, false);
SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;

foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
{
    foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
    {
        if (sheetData.HasChildren)
        {
            foreach (Row row in sheetData.Elements<Row>())
            {
                foreach (Cell cell in row.Elements<Cell>())
                {
                    cellValue = cell.InnerText;

                    if (cell.DataType == CellValues.SharedString)
                    {
                        Console.WriteLine("cell val: " + sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
                    }
                    else
                    {
                        Console.WriteLine("cell val: " + cellValue);
                    }
                }
            }
        }
    }
}
document.Close();

- vik

谢谢，很好的例子！ - Mikhail Zhuikov

8

读取大型Excel文件： openxml有两种读取Excel的方法：DOM和SAX。DOM方法消耗更多的RAM资源，因为它将整个xml内容（Excel文件）加载到内存中，但其具有强类型的方法。另一方面，SAX是基于事件的解析。了解更多

所以，如果您面临大型Excel文件，最好使用SAX。

以下代码示例使用SAX方法，还处理了Excel文件读取中的两个重要场景。

openxml跳过空单元格，导致您的数据集面临错位和错误索引的问题。
您还需要跳过空行。

此函数在时间上返回单元格的确切实际索引，并处理第一个场景。查看详细信息

private static int CellReferenceToIndex(Cell cell)
        {
            int index = 0;
            string reference = cell.CellReference.ToString().ToUpper();
            foreach (char ch in reference)
            {
                if (Char.IsLetter(ch))
                {
                    int value = (int)ch - (int)'A';
                    index = (index == 0) ? value : ((index + 1) * 26) + value;
                }
                else
                    return index;
            }
            return index;
        }

使用SAX方法读取Excel的代码。

//i want to import excel to data table
            dt = new DataTable();

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false))
            {

                WorkbookPart workbookPart = document.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

                OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

                //row counter
                int rcnt = 0;

                while (reader.Read())
                {


                    //find xml row element type 
                    //to understand the element type you can change your excel file eg : test.xlsx to test.zip
                    //and inside that you may observe the elements in xl/worksheets/sheet.xml
                    //that helps to understand openxml better
                    if (reader.ElementType == typeof(Row))
                    {

                        //create data table row type to be populated by cells of this row
                        DataRow tempRow = dt.NewRow();



                        //***** HANDLE THE SECOND SENARIO*****
                        //if row has attribute means it is not a empty row
                        if (reader.HasAttributes)
                        {
                            
                            //read the child of row element which is cells

                            //here first element
                            reader.ReadFirstChild();



                            do
                            {
                                //find xml cell element type 
                                if (reader.ElementType == typeof(Cell))
                                {
                                    Cell c = (Cell)reader.LoadCurrentElement();

                                    string cellValue;

                                    
                                    int actualCellIndex = CellReferenceToIndex(c);

                                    if (c.DataType != null && c.DataType == CellValues.SharedString)
                                    {
                                        SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));

                                        cellValue = ssi.Text.Text;
                                    }
                                    else
                                    {
                                        cellValue = c.CellValue.InnerText;
                                    }



                                    //if row index is 0 its header so columns headers are added & also can do some headers check incase
                                    if (rcnt == 0)
                                    {
                                        dt.Columns.Add(cellValue);
                                    }
                                    else
                                    {
                                        // instead of tempRow[c.CellReference] = cellValue;
                                        tempRow[actualCellIndex] = cellValue;
                                    }

                                    

                                }


                            }
                            while (reader.ReadNextSibling());


                            //if its not the header row so append rowdata to the datatable
                            if (rcnt != 0)
                            {
                                dt.Rows.Add(tempRow);
                            }

                            rcnt++;


                        }


                    }





                }


            }

- Rouzbeh Zarandi

0

所有的内容都在被接受的答案中解释了。
这里只是一个扩展方法来解决问题。

public static string GetCellText(this Cell cell, in SharedStringTable sst)
{
    if (cell.CellValue is null)
        return string.Empty;

    if ((cell.DataType is not null) &&
        (cell.DataType == CellValues.SharedString))
    {
        int ssid = int.Parse(cell.CellValue.Text);
        return sst.ChildElements[ssid].InnerText;
    }

    return cell.CellValue.Text;
}

- Hakan Fıstık

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- shunty · Accepted Answer

你的方法对我来说似乎还可以——因为它“进入了循环”。不过，你也可以尝试以下内容：

void Main()
{
    string fileName = @"c:\path\to\my\file.xlsx";

    using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
        {
            WorkbookPart workbookPart = doc.WorkbookPart;
            SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
            SharedStringTable sst = sstpart.SharedStringTable;
        
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            Worksheet sheet = worksheetPart.Worksheet;
            
            var cells = sheet.Descendants<Cell>();
            var rows = sheet.Descendants<Row>();
        
            Console.WriteLine("Row count = {0}", rows.LongCount());
            Console.WriteLine("Cell count = {0}", cells.LongCount());
        
            // One way: go through each cell in the sheet
            foreach (Cell cell in cells)
            {
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    int ssid = int.Parse(cell.CellValue.Text);
                    string str = sst.ChildElements[ssid].InnerText;
                    Console.WriteLine("Shared string {0}: {1}", ssid, str);
                }
                else if (cell.CellValue != null)
                {
                    Console.WriteLine("Cell contents: {0}", cell.CellValue.Text);
                }
             }
        
             // Or... via each row
             foreach (Row row in rows)
             {
                 foreach (Cell c in row.Elements<Cell>())
                 {
                     if ((c.DataType != null) && (c.DataType == CellValues.SharedString))
                     {
                         int ssid = int.Parse(c.CellValue.Text);
                         string str = sst.ChildElements[ssid].InnerText;
                         Console.WriteLine("Shared string {0}: {1}", ssid, str);
                     }
                     else if (c.CellValue != null)
                     {
                         Console.WriteLine("Cell contents: {0}", c.CellValue.Text);
                     }
                 }
             }
         }
     }
 }

我使用了filestream方法来打开工作簿，因为这样可以使用共享访问方式打开它，以便您可以同时在Excel中打开该工作簿。如果工作簿在其他地方已经打开，则Spreadsheet.Open(... 方法将无法使用。

也许这就是为什么您的代码不起作用的原因。

另外，请注意在适当的情况下使用SharedStringTable来获取单元格文本。

编辑2018-07-11：

由于这篇文章仍在得到投票，我还应该指出，在许多情况下，使用ClosedXML来操纵/读取/编辑您的工作簿可能会更容易。文档示例非常用户友好，并且编码，在我的有限经验中，更加直观。只需注意，它尚未实现所有Excel函数（例如INDEX和MATCH），这可能是一个问题，也可能不是一个问题。（不是说我想要在OpenXML中处理INDEX和MATCH）。