logo标签列表

C# OPEN XML：在从Excel获取数据到DataTable时，空单元格被跳过了

c#datatableopenxmlopenxml-sdkspreadsheetml
25

任务

将数据从 excel 导入到 DataTable 中

问题

不包含任何数据的单元格会被跳过，接下来有数据的单元格会被用作空列的值。例如：

A1 是空的，A2 有一个值 Tom，那么在导入数据时，A1 将获得 A2 的值，而 A2 仍然为空。

为了使其更加清晰，我在下面提供了一些屏幕截图

这是 excel 数据

enter image description here

这是从 excel 导入数据后的 DataTable enter image description here

代码

public class ImportExcelOpenXml
{
    public static DataTable Fill_dataTable(string fileName)
    {
        DataTable dt = new DataTable();

        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
        {

            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
            Worksheet workSheet = worksheetPart.Worksheet;
            SheetData sheetData = workSheet.GetFirstChild<SheetData>();
            IEnumerable<Row> rows = sheetData.Descendants<Row>();

            foreach (Cell cell in rows.ElementAt(0))
            {
                dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
            }

            foreach (Row row in rows) //this will also include your header row...
            {
                DataRow tempRow = dt.NewRow();

                for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                {
                    tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                }

                dt.Rows.Add(tempRow);
            }

        }

        dt.Rows.RemoveAt(0); //...so i'm taking it out here.

        return dt;
    }


    public static string GetCellValue(SpreadsheetDocument document, Cell cell)
    {
        SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
        string value = cell.CellValue.InnerXml;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
        }
        else
        {
            return value;
        }
    }
}

我的想法

我认为public IEnumerable<T> Descendants<T>() where T : OpenXmlElement;存在一些问题。

如果我想使用Descendants得到列数，该怎么办？

IEnumerable<Row> rows = sheetData.Descendants<<Row>();
int colCnt = rows.ElementAt(0).Count();

或者

如果我使用 Descendants 来获取行数

IEnumerable<Row> rows = sheetData.Descendants<<Row>();
int rowCnt = rows.Count();`

在这两种情况下，Descendants 都会跳过空单元格。
是否有 Descendants 的替代方法。
非常感谢您的建议。
附言：我也考虑过使用列名（如A1，A2）获取单元格值，但为了做到这一点，我需要获取确切的列数和行数，这是不可能使用 Descendants 函数实现的。
空单元格没有Cell元素，因此您无法找到它们。 - Alexander Derck
@AlexanderDerck 那么如何解决这个问题呢？ - Vikas Bansal
1
使用EPPlus库会更容易（它使用open xml sdk），请参见此处的示例：https://dev59.com/6GYr5IYBdhLWcg3wytDe#13396787 - Alexander Derck
您还可以要求单元格始终包含一个值。如果没有标记，则默认值为零。 - Taterhead
4个回答
31

如果一行中的所有单元格都有数据，那么一切都能正常工作。但是如果该行中有一个或多个空单元格，则会出现问题。

为什么会出现这种情况？

原因在于以下代码行：

row.Descendants<Cell>().Count()

Count()函数将为您提供一行中非空单元格的数量，即在返回计数时将忽略所有空单元格。因此，当您将 row.Descendants<Cell>().ElementAt(i) 作为参数传递给GetCellValue 方法时：

GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));

接着，它将找到下一个非空单元格的内容，不一定是列索引i处单元格的内容。例如，如果第一列为空并且我们调用ElementAt(1)，它将返回第二列的值，这时程序逻辑会出现问题。

解决方案：我们需要处理空单元格的出现，即我们需要在给定行中确定目标单元格的实际/有效列索引，以防它之前有一些空单元格。因此，您需要将以下代码的for循环替换为：

for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
      tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
}

使用

for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
    Cell cell = row.Descendants<Cell>().ElementAt(i);
    int actualCellIndex = CellReferenceToIndex(cell);
    tempRow[actualCellIndex] = GetCellValue(spreadSheetDocument, cell);
}

同时，在你的代码中添加下面的方法，它被用于上述修改后的代码片段来获取任何单元格的实际/有效列索引：

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

注意：Excel 行中的索引从1开始，与许多编程语言不同，编程语言通常从0开始。

5
我认为方法CellReferenceToIndex对于超出A-Z范围到AA、AB等的Excel表格不适用。当Z列再次超过时，它会返回从0开始的索引...所以如果你有很多列的Excel表格，这个方法就无法正常工作。 - Rouzbeh Zarandi
这是一个非常有帮助的答案！很遗憾OpenXML文档如此混乱。非常感谢你。 - rp.
6
public void Read2007Xlsx()
{
    try
    {
        DataTable dt = new DataTable();
        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(@"D:\File.xlsx", false))
        {
            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
            Worksheet workSheet = worksheetPart.Worksheet;
            SheetData sheetData = workSheet.GetFirstChild<SheetData>();
            IEnumerable<Row> rows = sheetData.Descendants<Row>();
            foreach (Cell cell in rows.ElementAt(0))
            {
                dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
            }
            foreach (Row row in rows) //this will also include your header row...
            {
                DataRow tempRow = dt.NewRow();
                int columnIndex = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    // Gets the column index of the cell with data
                    int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
                    cellColumnIndex--; //zero based index
                    if (columnIndex < cellColumnIndex)
                    {
                        do
                        {
                            tempRow[columnIndex] = ""; //Insert blank data here;
                            columnIndex++;
                         }
                         while (columnIndex < cellColumnIndex);
                     }//end if block
                     tempRow[columnIndex] = GetCellValue(spreadSheetDocument, cell);                            
                     columnIndex++;
                 }//end inner foreach loop
                 dt.Rows.Add(tempRow);
             }//end outer foreach loop
        }//end using block
        dt.Rows.RemoveAt(0); //...so i'm taking it out here.
    }//end try
    catch (Exception ex)
    {
    }
}//end Read2007Xlsx method

/// <summary>
/// Given a cell name, parses the specified cell to get the column name.
/// </summary>
/// <param name="cellReference">Address of the cell (ie. B2)</param>
/// <returns>Column Name (ie. B)</returns>
public static string GetColumnName(string cellReference)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellReference);
    return match.Value;
} //end GetColumnName method

/// <summary>
/// Given just the column name (no row index), it will return the zero based column index.
/// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ). 
/// A length of three can be implemented when needed.
/// </summary>
/// <param name="columnName">Column Name (ie. A or AB)</param>
/// <returns>Zero based index if the conversion was successful; otherwise null</returns>
public static int? GetColumnIndexFromName(string columnName)
{                       
    //return columnIndex;
    string name = columnName;
    int number = 0;
    int pow = 1;
    for (int i = name.Length - 1; i >= 0; i--)
    {
        number += (name[i] - 'A' + 1) * pow;
        pow *= 26;
    }
    return number;
 } //end GetColumnIndexFromName method

public static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
   SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
   if (cell.CellValue ==null)
   {
       return "";
   }
   string value = cell.CellValue.InnerXml;
   if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
   {
        return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
         return value;
    }
 }//end GetCellValue method
0

试试这段代码。我做了一些小修改，它对我有效：

public static DataTable Fill_dataTable(string filePath)
{
    DataTable dt = new DataTable();

    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        Worksheet worksheet = doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart.Worksheet;
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
        DataTable dt = new DataTable();
        List<string> columnRef = new List<string>();
        foreach (Row row in rows)
        {
            if (row.RowIndex != null)
            {
                if (row.RowIndex.Value == 1)
                {
                    foreach (Cell cell in row.Descendants<Cell>())
                    {
                        dt.Columns.Add(GetValue(doc, cell));
                            columnRef.Add(cell.CellReference.ToString().Substring(0, cell.CellReference.ToString().Length - 1));
                     }
                }
                else
                {
                    dt.Rows.Add();
                    int i = 0;
                    foreach (Cell cell in row.Descendants<Cell>())
                    {
                        while (columnRef(i) + dt.Rows.Count + 1 != cell.CellReference)
                        {
                            dt.Rows(dt.Rows.Count - 1)(i) = "";
                            i += 1;
                         }

                         dt.Rows(dt.Rows.Count - 1)(i) = GetValue(doc, cell);
                         i += 1;
                    }
                }
            }
        }
    }

    return dt;
}
0
foreach (Cell cell in row.Descendants<Cell>())
{
    while (columnRef[i] + (dt.Rows.Count + 1) != cell.CellReference)
    {
        dt.Rows[dt.Rows.Count - 1][i] = "";
        i += 1;
    }

    dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
    i++;
}
请为您的代码添加描述。请勿发布原始代码答案。 - Presi
你的回答可以通过提供更多支持信息来改进。请编辑以添加进一步的细节，例如引用或文档，以便他人可以确认你的答案是正确的。您可以在帮助中心中找到有关如何编写良好答案的更多信息。 - Community
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接