使用OpenXML SDK读取Excel文件遇到了问题

Question

我有一个函数可以从Excel文件中读取结果并存储在DataSet中。我还有另一个函数可以写入Excel文件。当我尝试从一个由人生成的普通Excel文件中读取时，Excel读取函数会返回一个空的DataSet，但是当我从写入功能生成的Excel文件中读取时，它可以正常工作。即使我只是复制和粘贴函数生成的Excel文件的内容，也无法在常规生成的Excel文件上运行该函数。最终我追踪到了这个问题，但是我不知道该怎么做。我的代码出了什么问题吗？

以下是生成Excel的函数：

public static Boolean writeToExcel(string fileName, DataSet data)
{
    Boolean answer = false;
    using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(tempPath + fileName, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = excelDoc.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        Sheets sheets = excelDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
        Sheet sheet = new Sheet()
        {
            Id = excelDoc.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "Page1"
        };

        sheets.Append(sheet);

        CreateWorkSheet(worksheetPart, data);
        answer = true;
    }

    return answer;
}

private static void CreateWorkSheet(WorksheetPart worksheetPart, DataSet data)
{
    Worksheet worksheet = new Worksheet();
    SheetData sheetData = new SheetData();
    UInt32Value currRowIndex = 1U;
    int colIndex = 0;
    Row excelRow;
    DataTable table = data.Tables[0];

    for (int rowIndex = -1; rowIndex < table.Rows.Count; rowIndex++)
    {
        excelRow = new Row();
        excelRow.RowIndex = currRowIndex++;
        for (colIndex = 0; colIndex < table.Columns.Count; colIndex++)
        {
            Cell cell = new Cell()
            {
                CellReference = Convert.ToString(Convert.ToChar(65 + colIndex)),
                DataType = CellValues.String
            };

            CellValue cellValue = new CellValue();

            if (rowIndex == -1)
            {
                cellValue.Text = table.Columns[colIndex].ColumnName.ToString();
            }
            else
            {
                cellValue.Text = (table.Rows[rowIndex].ItemArray[colIndex].ToString() != "") ? table.Rows[rowIndex].ItemArray[colIndex].ToString() : "*";
            }

            cell.Append(cellValue);
            excelRow.Append(cell);
        }

        sheetData.Append(excelRow);
    }

    SheetFormatProperties formattingProps = new SheetFormatProperties()
    {
        DefaultColumnWidth = 20D,
        DefaultRowHeight = 20D
    };

    worksheet.Append(formattingProps);
    worksheet.Append(sheetData);
    worksheetPart.Worksheet = worksheet;
}

阅读函数如下：

public static void readInventoryExcel(string fileName, ref DataSet set)
{
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
        int count = -1;
        foreach (Row r in sheetData.Elements<Row>())
        {
            if (count >= 0)
            {
                DataRow row = set.Tables[0].NewRow();
                row["SerialNumber"]         = r.ChildElements[1].InnerXml;
                row["PartNumber"]           = r.ChildElements[2].InnerXml;
                row["EntryDate"]            = r.ChildElements[3].InnerXml;
                row["RetirementDate"]       = r.ChildElements[4].InnerXml;
                row["ReasonForReplacement"] = r.ChildElements[5].InnerXml;
                row["RetirementTech"]       = r.ChildElements[6].InnerXml;
                row["IncludeInMaintenance"] = r.ChildElements[7].InnerXml;
                row["MaintenanceTech"]      = r.ChildElements[8].InnerXml;
                row["Comment"]              = r.ChildElements[9].InnerXml;
                row["Station"]              = r.ChildElements[10].InnerXml;
                row["LocationStatus"]       = r.ChildElements[11].InnerXml;
                row["AssetName"]            = r.ChildElements[12].InnerXml;
                row["InventoryType"]        = r.ChildElements[13].InnerXml;
                row["Description"]          = r.ChildElements[14].InnerXml;
                set.Tables[0].Rows.Add(row);
            }
            count++;
        }
    }
}

- user2850818

Answer 1

我认为这是由于你只有一个工作表，而Excel有三个工作表引起的。虽然我不确定，但我认为这些工作表是按照相反的顺序返回的，所以你应该改变这一行：

WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

to

WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();

如果您可以通过工作表名称来识别它，那么查找WorksheetPart可能会更加安全。 首先需要找到Sheet，然后使用该ID来查找SheetPart：

private WorksheetPart GetWorksheetPartBySheetName(WorkbookPart workbookPart, string sheetName)
{
    //find the sheet first.
    IEnumerable<Sheet> sheets = workbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);

    if (sheets.Count() > 0)
    {
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(relationshipId);
        return worksheetPart;
    }

    return null;
}

您可以使用以下方法：

WorksheetPart worksheetPart = GetWorksheetPartBySheetName(workbookPart, "Sheet1");

在查看您的代码时，我注意到了一些其他事情，您可能会（或者可能不会）感兴趣：

在您的代码中，您只读取了InnerXml，所以这对您来说可能并不重要，但Excel存储字符串的方式与您编写它们的方式不同，因此读取由Excel生成的文件可能无法给您带来预期的值。在您的示例中，您直接将字符串写入单元格，如下所示：

但是Excel使用共享字符串的概念，其中所有字符串都写入名为sharedStrings.xml的单独的XML文件中。该文件包含Excel文件中使用的字符串及其引用，而该值存储在工作表XML中的单元格值中。

sharedString.xml如下所示：

然后单元格如下所示：

<v>元素中的47是第47个共享字符串的引用。请注意，您生成的XML中的类型（t属性）为str，但Excel生成的文件中的类型为s。这表示您的是内联字符串，而他们的是共享字符串。

您可以像读取任何其他部分一样读取SharedStrings：

var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

if (stringTable != null)
{
    sharedString = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}

其次，如果您查看代码生成的单元格引用以及Excel生成的单元格引用，您会发现您只是输出了列而没有输出行（例如，您输出A而不是A1）。要解决这个问题，您应该更改以下一行代码：

CellReference = Convert.ToString(Convert.ToChar(65 + colIndex)),

to

CellReference = Convert.ToString(Convert.ToChar(65 + colIndex) + rowIndex.ToString()),

我希望你能有所帮助。

- petelids

---

那真的帮了很多忙。但是，现在我遇到了另一个以前没有遇到过的问题。返回的SheetData包含了工作表中的所有行，而不仅仅是我正在使用的行，这导致我的应用崩溃。现在我知道它崩溃的原因了，但是我有点迷失，不知道如何找出实际上有多少行具有信息。至于其他方面，我确实只是从互联网上抄写了写代码，不像读代码那样。所以谢谢你提醒我。一旦解决这个问题，我会尽快看一下的。 - user2850818

@user2850818 请看这个问题 https://dev59.com/goHba4cB1Zd3GeqPYPZt#25183597。那应该会给你所需的东西。 - petelids

所以，我看到了这个问题，但是当我尝试使用三元条件运算符做类似的事情时，该语句总是被评估为真，即CellType为SharedString。我错过了什么还是我的Excel电子表格出了问题？row ["SerialNumber"] = (((Cell)r.ChildElements [0]).DataType.Value == CellValues.SharedString)? stringTable.SharedStringTable.ElementAt(int.Parse(r.ChildElements[0].InnerText)).InnerText：r.ChildElements[ 0] .InnerText; 这是序列号，它不是共享的，导致程序崩溃。 - user2850818

很棒的帖子。非常感谢。 - Jason

@petelids 这是一个非常良好结构的答案，非常有信息量。我完全忘记了这个共享字符串表，这给我带来了很多痛苦。感谢您在这个回答中提供如此详细的解释，我们非常感激您的努力！ - Stevo

Answer 2

我曾经遇到过类似的问题，尝试对Word文档进行此操作（程序生成的可以，但人工生成的不行）。我发现这个工具非常有帮助：

http://www.microsoft.com/en-us/download/details.aspx?id=30425

基本上，它会查看一个文件并显示出微软生成用于读取该文件的代码，以及文件本身的XML结构。像微软产品一样，有很多菜单，而且不太直观，但是在点击一段时间后，您将能够准确地了解任何两个文件的情况。我建议您打开一个工作正常的Excel文件和一个无法正常工作的文件，比较它们之间的差异，找出导致问题的原因。

- welegan

---

我正在使用OpenXml SDK进行这个项目。然而，它仍然给我错误。我应该查看SDK中的特定类吗？ - user2850818

Answer 3

以下是我用来从Excel文件中读取特定工作表的OpenXML代码，转换成DataTable。

首先，这是你该如何调用它：

DataTable dt = OpenXMLHelper.ExcelWorksheetToDataTable("C:\\SQL Server\\SomeExcelFile.xlsx", "Mikes Worksheet");

这里是代码：

    public class OpenXMLHelper
    {
        public static DataTable ExcelWorksheetToDataTable(string pathFilename, string worksheetName)
        {
            DataTable dt = new DataTable(worksheetName);

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathFilename, false))
            {
                // Find the sheet with the supplied name, and then use that 
                // Sheet object to retrieve a reference to the first worksheet.
                Sheet theSheet = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName).FirstOrDefault();
                if (theSheet == null)
                    throw new Exception("Couldn't find the worksheet: " + worksheetName);

                // Retrieve a reference to the worksheet part.
                WorksheetPart wsPart = (WorksheetPart)(document.WorkbookPart.GetPartById(theSheet.Id));
                Worksheet workSheet = wsPart.Worksheet;

                string dimensions = workSheet.SheetDimension.Reference.InnerText;       //  Get the dimensions of this worksheet, eg "B2:F4"

                int numOfColumns = 0;
                int numOfRows = 0;
                CalculateDataTableSize(dimensions, ref numOfColumns, ref numOfRows);
                System.Diagnostics.Trace.WriteLine(string.Format("The worksheet \"{0}\" has dimensions \"{1}\", so we need a DataTable of size {2}x{3}.", worksheetName, dimensions, numOfColumns, numOfRows));

                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                string[,] cellValues = new string[numOfColumns, numOfRows];

                int colInx = 0;
                int rowInx = 0;
                string value = "";
                SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;

                //  Iterate through each row of OpenXML data
                foreach (Row row in rows)
                {
                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        //  *DON'T* assume there's going to be one XML element for each item in each row...
                        Cell cell = row.Descendants<Cell>().ElementAt(i);
                        if (cell.CellValue == null || cell.CellReference == null)
                            continue;                       //  eg when an Excel cell contains a blank string

                        //  Convert this Excel cell's CellAddress into a 0-based offset into our array (eg "G13" -> [6, 12])
                        colInx = GetColumnIndexByName(cell.CellReference);             //  eg "C" -> 2  (0-based)
                        rowInx = GetRowIndexFromCellAddress(cell.CellReference)-1;     //  Needs to be 0-based  

                        //  Fetch the value in this cell
                        value = cell.CellValue.InnerXml;
                        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                        {
                            value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
                        }

                        cellValues[colInx, rowInx] = value;
                    }
                    dt.Rows.Add(dataRow);
                }

                //  Copy the array of strings into a DataTable
                for (int col = 0; col < numOfColumns; col++)
                    dt.Columns.Add("Column_" + col.ToString());

                for (int row = 0; row < numOfRows; row++)
                {
                    DataRow dataRow = dt.NewRow();
                    for (int col = 0; col < numOfColumns; col++)
                    {
                        dataRow.SetField(col, cellValues[col, row]);
                    }
                    dt.Rows.Add(dataRow);
                }

#if DEBUG
                //  Write out the contents of our DataTable to the Output window (for debugging)
                string str = "";
                for (rowInx = 0; rowInx < maxNumOfRows; rowInx++)
                {
                    for (colInx = 0; colInx < maxNumOfColumns; colInx++)
                    {
                        object val = dt.Rows[rowInx].ItemArray[colInx];
                        str += (val == null) ? "" : val.ToString();
                        str += "\t";
                    }
                    str += "\n";
                }
                System.Diagnostics.Trace.WriteLine(str);
#endif
                return dt;
            }
        }

        private static void CalculateDataTableSize(string dimensions, ref int numOfColumns, ref int numOfRows)
        {
            //  How many columns & rows of data does this Worksheet contain ?  
            //  We'll read in the Dimensions string from the Excel file, and calculate the size based on that.
            //      eg "B1:F4" -> we'll need 6 columns and 4 rows.
            //
            //  (We deliberately ignore the top-left cell address, and just use the bottom-right cell address.)
            try
            {
                string[] parts = dimensions.Split(':');     // eg "B1:F4" 
                if (parts.Length != 2)
                    throw new Exception("Couldn't find exactly *two* CellAddresses in the dimension");

                numOfColumns = 1 + GetColumnIndexByName(parts[1]);     //  A=1, B=2, C=3  (1-based value), so F4 would return 6 columns
                numOfRows = GetRowIndexFromCellAddress(parts[1]);
            }
            catch
            {
                throw new Exception("Could not calculate maximum DataTable size from the worksheet dimension: " + dimensions);
            }
        }

        public static int GetRowIndexFromCellAddress(string cellAddress)
        {
            //  Convert an Excel CellReference column into a 1-based row index
            //  eg "D42"  ->  42
            //     "F123" ->  123
            string rowNumber = System.Text.RegularExpressions.Regex.Replace(cellAddress, "[^0-9 _]", "");
            return int.Parse(rowNumber);
        }

        public static int GetColumnIndexByName(string cellAddress)
        {
            //  Convert an Excel CellReference column into a 0-based column index
            //  eg "D42" ->  3
            //     "F123" -> 5
            var columnName = System.Text.RegularExpressions.Regex.Replace(cellAddress, "[^A-Z_]", "");
            int number = 0, pow = 1;
            for (int i = columnName.Length - 1; i >= 0; i--)
            {
                number += (columnName[i] - 'A' + 1) * pow;
                pow *= 26;
            }
            return number - 1;
        }
    }

顺便提一下，我们公司的一些Excel工作表在顶部有一个或多个空行。奇怪的是，这会阻止一些其他OpenXML库正确读取这样的工作表。

此代码故意创建一个DataTable，其中包含工作表中每个单元格的一个值，即使是顶部的空白单元格也是如此。

- Mike Gledhill

---

测试过了。我发现在open xml规范中，SheetDimension是可选的，这不太可靠。而且SheetDimension返回dimensions = "A1:XEZ64"，所以我们需要一个大小为16380x64的DataTable。16380列。这是针对我有一个横跨所有列但只有少数列有数据的样本excelfile。但这是一个很好的起点，我认为它只需要一些改进。 - osexpert