Od Excela do DataTable w C# z Open XML

Witam powyższy kod działa poprawnie z wyjątkiem jednej zmiany

Zastąp poniższy wiersz kodu

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));

Z

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));

Jeśli użyjesz (i-1) wyrzuci wyjątek:

specified argument was out of the range of valid values. parameter name index.

To rozwiązanie działa dla arkuszy kalkulacyjnych Bez pustych komórek.

Aby obsłużyć puste komórki, musisz zastąpić tę linię:

tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i-1));

Z czymś takim:

Cell cell = row.Descendants<Cell>().ElementAt(i);
int index = CellReferenceToIndex(cell);
tempRow[index] = GetCellValue(spreadSheetDocument, cell);

I dodać tę metodę:

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

To jest moje kompletne rozwiązanie, gdzie pusta komórka jest również brana pod uwagę.

public static class ExcelHelper
        {
            //To get the value of the cell, even it's empty. Unable to use loop by index
            private static string GetCellValue(WorkbookPart wbPart, List<Cell> theCells, string cellColumnReference)
            {
                Cell theCell = null;
                string value = "";
                foreach (Cell cell in theCells)
                {
                    if (cell.CellReference.Value.StartsWith(cellColumnReference))
                    {
                        theCell = cell;
                        break;
                    }
                }
                if (theCell != null)
                {
                    value = theCell.InnerText;
                    // If the cell represents an integer number, you are done. 
                    // For dates, this code returns the serialized value that represents the date. The code handles strings and 
                    // Booleans individually. For shared strings, the code looks up the corresponding value in the shared string table. For Booleans, the code converts the value into the words TRUE or FALSE.
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case CellValues.SharedString:
                                // For shared strings, look up the value in the shared strings table.
                                var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                                // If the shared string table is missing, something is wrong. Return the index that is in the cell. Otherwise, look up the correct text in the table.
                                if (stringTable != null)
                                {
                                    value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                                }
                                break;
                            case CellValues.Boolean:
                                switch (value)
                                {
                                    case "0":
                                        value = "FALSE";
                                        break;
                                    default:
                                        value = "TRUE";
                                        break;
                                }
                                break;
                        }
                    }
                }
                return value;
            }

            private static string GetCellValue(WorkbookPart wbPart, List<Cell> theCells, int index)
            {
                return GetCellValue(wbPart, theCells, GetExcelColumnName(index));
            }

            private static string GetExcelColumnName(int columnNumber)
            {
                int dividend = columnNumber;
                string columnName = String.Empty;
                int modulo;
                while (dividend > 0)
                {
                    modulo = (dividend - 1) % 26;
                    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                    dividend = (int)((dividend - modulo) / 26);
                }
                return columnName;
            }

            //Only xlsx files
            public static DataTable GetDataTableFromExcelFile(string filePath, string sheetName = "")
            {
                DataTable dt = new DataTable();
                try
                {
                    using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
                    {
                        WorkbookPart wbPart = document.WorkbookPart;
                        IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                        string sheetId = sheetName != "" ? sheets.Where(q => q.Name == sheetName).First().Id.Value : sheets.First().Id.Value;
                        WorksheetPart wsPart = (WorksheetPart)wbPart.GetPartById(sheetId);
                        SheetData sheetdata = wsPart.Worksheet.Elements<SheetData>().FirstOrDefault();
                        int totalHeaderCount = sheetdata.Descendants<Row>().ElementAt(0).Descendants<Cell>().Count();
                        //Get the header                    
                        for (int i = 1; i <= totalHeaderCount; i++)
                        {
                            dt.Columns.Add(GetCellValue(wbPart, sheetdata.Descendants<Row>().ElementAt(0).Elements<Cell>().ToList(), i));
                        }
                        foreach (Row r in sheetdata.Descendants<Row>())
                        {
                            if (r.RowIndex > 1)
                            {
                                DataRow tempRow = dt.NewRow();

                                //Always get from the header count, because the index of the row changes where empty cell is not counted
                                for (int i = 1; i <= totalHeaderCount; i++)
                                {
                                    tempRow[i - 1] = GetCellValue(wbPart, r.Elements<Cell>().ToList(), i);
                                }
                                dt.Rows.Add(tempRow);
                            }
                        }                    
                    }
                }
                catch (Exception ex)
                {

                }
                return dt;
            }
        }

 Public Shared Function ExcelToDataTable(filename As String) As DataTable
        Try

            Dim dt As New DataTable()

            Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filename, False)

                Dim workbookPart As WorkbookPart = doc.WorkbookPart
                Dim sheets As IEnumerable(Of Sheet) = doc.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
                Dim relationshipId As String = sheets.First().Id.Value
                Dim worksheetPart As WorksheetPart = DirectCast(doc.WorkbookPart.GetPartById(relationshipId), WorksheetPart)
                Dim workSheet As Worksheet = worksheetPart.Worksheet
                Dim sheetData As SheetData = workSheet.GetFirstChild(Of SheetData)()
                Dim rows As IEnumerable(Of Row) = sheetData.Descendants(Of Row)()

                For Each cell As Cell In rows.ElementAt(0)
                    dt.Columns.Add(GetCellValue(doc, cell))
                Next

                For Each row As Row In rows
                    'this will also include your header row...
                    Dim tempRow As DataRow = dt.NewRow()

                    For i As Integer = 0 To row.Descendants(Of Cell)().Count() - 1
                        tempRow(i) = GetCellValue(doc, row.Descendants(Of Cell)().ElementAt(i))
                    Next

                    dt.Rows.Add(tempRow)
                Next
            End Using

            dt.Rows.RemoveAt(0)

            Return dt

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Shared Function GetCellValue(document As SpreadsheetDocument, cell As Cell) As String
        Try

            If IsNothing(cell.CellValue) Then
                Return ""
            End If

            Dim value As String = cell.CellValue.InnerXml

            If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                Dim stringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
                Return stringTablePart.SharedStringTable.ChildElements(Int32.Parse(value)).InnerText
            Else
                Return value
            End If

        Catch ex As Exception
            Return ""
        End Try
    End Function

Najpierw Dodaj ExcelUtility.cs do twojego projektu:

Doskonałość.cs

using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Core_Excel.Utilities
{
    static class ExcelUtility
    {
        public static DataTable Read(string path)
        {
            var dt = new DataTable();

            using (var ssDoc = SpreadsheetDocument.Open(path, false))
            {
                var sheets = ssDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                var relationshipId = sheets.First().Id.Value;
                var worksheetPart = (WorksheetPart) ssDoc.WorkbookPart.GetPartById(relationshipId);
                var workSheet = worksheetPart.Worksheet;
                var sheetData = workSheet.GetFirstChild<SheetData>();
                var rows = sheetData.Descendants<Row>().ToList();

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

                    var colCount = row.Descendants<Cell>().Count();
                    foreach (var cell in row.Descendants<Cell>())
                    {
                        var index = GetIndex(cell.CellReference);

                        // Add Columns
                        for (var i = dt.Columns.Count; i <= index; i++)
                            dt.Columns.Add();

                        tempRow[index] = GetCellValue(ssDoc, cell);
                    }

                    dt.Rows.Add(tempRow);
                }
            }

            var m = dt.Rows[0][9];

            return dt;
        }

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

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

            return value;
        }

        public static int GetIndex(string name)
        {
            if (string.IsNullOrWhiteSpace(name))
                return -1;

            int index = 0;
            foreach (var ch in name)
            {
                if (char.IsLetter(ch))
                {
                    int value = ch - 'A' + 1;
                    index = value + index * 26;
                }
                else
                    break;
            }

            return index - 1;
        }
    }
}

Użycie:

var path = "D:\\Documents\\test.xlsx";
var dt = ExcelUtility.Read(path);

Wiem, że dawno temu zaczął się ten wątek. Jednak żadne z powyższych rozwiązań nie zadziałało dla mnie. Problem z pustymi komórkami i innymi.

Znalazłem bardzo dobre rozwiązanie z licencją 'MIT' na Githubie: https://github.com/ExcelDataReader/ExcelDataReader To działało dla mnie zarówno dla aplikacji C#, jak i VBnet. Przykładowe wywołanie z sieci VBNET (przykładowy kod dla c# znajduje się na GitHub):

        Using stream As FileStream = New FileStream(DataPath & "\" & fName.Name, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)

            Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(stream)
                ds = reader.AsDataSet(New ExcelDataSetConfiguration() With {
                    .UseColumnDataType = False,
                    .ConfigureDataTable = Function(tableReader) New ExcelDataTableConfiguration() With {
                        .UseHeaderRow = True
                    }
                })
            End Using

        End Using

Rezultatem był zbiór danych z jedną tabelą dla każdego arkusza w skoroszyt.

An naprawdę lubię kompilować dll wykonany w C# przez siebie, a nie za pomocą gotowego dll. Dzięki temu mogę kontrolować, co dostarczam klientom.

Jeśli wartość rows jest null lub empty pobranie wartości źle działa.

Wszystkie kolumny wypełnione danymi, jeśli działa true. ale może wszystkie wiersze nie