Read an Excel File in UWP/WinRT

Excel.Reader.cs

using Client.Excel.Models;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Windows.Storage;

namespace Client.Excel.Models
{
    public class ExcelWorkBook
    {
        public List<ExcelWorkSheet> WorkSheets { get; } = new List<ExcelWorkSheet>();
    }

    public class ExcelWorkSheet
    {
        public string Id { get; set; }

        public string Name { get; set; }

        public List<ExcelTable> Tables { get; } = new List<ExcelTable>();

        public override string ToString() => Name;
    }

    public class ExcelTable
    {
        public string Id { get; set; }

        public string Name { get; set; }

        public IEnumerable<string> Headers { get; set; }

        public List<ExcelRow> Rows { get; } = new List<ExcelRow>();

        public (string Letter, int Number) TopLeft { get; internal set; }

        public (string Letter, int Number) BottomRight { get; internal set; }

        public bool IsInside((string Letter, int Number) cellReference)
        {
            return (string.Compare(cellReference.Letter, TopLeft.Letter) != -1)
                && (string.Compare(cellReference.Letter, BottomRight.Letter) != 1)
                && cellReference.Number >= TopLeft.Number
                && cellReference.Number <= BottomRight.Number;
        }

        public override string ToString() => Name;
    }

    public class ExcelRow
    {
        public int Index { get; set; }

        public List<ExcelCell> Cells { get; } = new List<ExcelCell>();
    }

    public class ExcelCell
    {
        public int Index { get; set; }

        public string DataType { get; set; }

        public string Value { get; set; }

        public (string Letter, int Number) Reference { get; set; }

        public override string ToString() => Value;
    }
}

namespace Client.Excel
{
    // NUGET: install-package DocumentFormat.OpenXml
    public static class Reader
    {
        private const int _rowThreshold = 1000;
        private const int _columnThreshold = 1000;

        /// <summary>
        /// Read the given StorageFile Excel file.
        /// </summary>
        /// <param name="file">Excel Workbook.</param>
        /// <param name="sheetFilters">If you know the name of the sheets in advance, you can include names to include, ignoring the rest. Otherwise, every sheet is returned.</param>
        /// <returns>An instance of ExcelWorkbook with Sheets/Tables/Rows/Cells.</returns>
        public static async Task<ExcelWorkBook> ReadAsync(StorageFile file, params string[] sheetFilters)
        {
            if (file is null)
            {
                throw new System.ArgumentNullException(nameof(file));
            }

            using (var stream = await file.OpenStreamForReadAsync())
            {
                using (var document = SpreadsheetDocument.Open(stream, false))
                {
                    var wbPart = document.WorkbookPart;
                    var sharedStrings = GetSharedStrings(wbPart);

                    var xlBook = new ExcelWorkBook();
                    FillBook(xlBook, wbPart, sheetFilters, sharedStrings);
                    return xlBook;
                }
            }

            SharedStringItem[] GetSharedStrings(WorkbookPart wbPart)
            {
                var sharedTable = wbPart.SharedStringTablePart.SharedStringTable;
                return sharedTable.Descendants<SharedStringItem>().ToArray();
            }
        }

        private static void FillBook(ExcelWorkBook xlBook, WorkbookPart wbPart, string[] sheetFilters, SharedStringItem[] sharedStrings)
        {
            foreach (var sheet in wbPart.Workbook.Descendants<Sheet>())
            {
                var wsPart = wbPart.GetPartById(sheet.Id) as WorksheetPart;
                var xlSheet = BuildSheet(wbPart, sheet);
                if (sheetFilters is null || sheetFilters.Contains(xlSheet.Name))
                {
                    FillSheet(wsPart, xlSheet, sharedStrings);
                }

                xlBook.WorkSheets.Add(xlSheet);
            }
        }

        private static ExcelWorkSheet BuildSheet(WorkbookPart wbPart, Sheet sheet)
        {
            return new ExcelWorkSheet
            {
                Id = sheet.Id,
                Name = sheet.Name,
            };
        }

        private static void FillSheet(WorksheetPart wsPart, ExcelWorkSheet xlSheet, SharedStringItem[] sharedStrings)
        {
            foreach (var tableDefinitionPart in wsPart.TableDefinitionParts)
            {
                var xlTable = BuildTable(xlSheet, tableDefinitionPart);
                FillTable(wsPart, xlTable, sharedStrings);
                xlSheet.Tables.Add(xlTable);
            }
        }

        private static ExcelTable BuildTable(ExcelWorkSheet xlSheet, TableDefinitionPart tdPart)
        {
            var table = tdPart.Table;
            return new ExcelTable
            {
                Id = table.Id,
                Name = table.DisplayName,
                Headers = table.TableColumns.OfType<TableColumn>().Select(x => x.Name.ToString()),
                TopLeft = ParseCellReference(table.Reference.Value.Split(":")[0]),
                BottomRight = ParseCellReference(table.Reference.Value.Split(":")[1]),
            };
        }

        private static void FillTable(WorksheetPart wsPart, ExcelTable xlTable, SharedStringItem[] sharedStrings)
        {
            var sheetTables = GetSheetTables();
            foreach (var table in sheetTables)
            {
                var xlRow = BuildRow(table.Index);
                FillRow(xlTable, xlRow, table.Row, sharedStrings);
                xlTable.Rows.Add(xlRow);
            }

            IEnumerable<(OpenXmlElement Row, int Index)> GetSheetTables()
            {
                var sheetData = wsPart.Worksheet
                    .Elements<SheetData>()
                    .First().AsEnumerable();
                if (xlTable.Headers.Any())
                {
                    sheetData = sheetData.Skip(1);
                }

                return sheetData
                    .Take(_rowThreshold)
                    .Select((x, i) => (x, i));
            }
        }

        private static ExcelRow BuildRow(int index)
        {
            return new ExcelRow
            {
                Index = index,
            };
        }

        private static void FillRow(ExcelTable xlTable, ExcelRow xlRow, OpenXmlElement xmlRow, SharedStringItem[] sharedStrings)
        {
            var cells = GetRowCells(xlTable, xmlRow);
            foreach (var cell in cells)
            {
                var xlCell = BuildCell(cell.Cell, cell.Index);
                FillCell(xlCell, cell.Cell, sharedStrings);
                xlRow.Cells.Add(xlCell);
            }

            IEnumerable<(Cell Cell, int Index)> GetRowCells(ExcelTable xlTable, OpenXmlElement xmlRow)
            {
                return xmlRow
                    .Elements<Cell>()
                    .Where(x =>
                    {
                        var reference = ParseCellReference(x.CellReference);
                        return xlTable.IsInside(reference);
                    })
                    .Take(_columnThreshold)
                    .Select((x, i) => (x, i));
            }
        }

        private static ExcelCell BuildCell(Cell cell, int index)
        {
            return new ExcelCell
            {
                Index = index,
                DataType = ((CellValues)cell.DataType).ToString(),
                Reference = ParseCellReference(cell.CellReference),
            };
        }

        private static void FillCell(ExcelCell xlCell, Cell cell, SharedStringItem[] sharedStrings)
        {
            if (xlCell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                var i = int.Parse(cell.CellValue.InnerText);
                xlCell.Value = sharedStrings[i].InnerText;
            }
            else
            {
                xlCell.Value = cell.CellValue.Text;
            }
        }

        private static (string Letter, int Number) ParseCellReference(string cellReference)
        {
            var letter = Regex.Match(cellReference, "[A-Z]{1,}").Value;
            var number = int.Parse(Regex.Match(cellReference, "[0-9]{1,}").Value);
            return (letter, number);
        }
    }
}

Usage syntax.

try
{
    var folder = Windows.ApplicationModel.Package.Current.InstalledLocation;
    folder = await folder.GetFolderAsync("assets");
    folder = await folder.GetFolderAsync("samples");
    var file = await folder.GetFileAsync("file.xlsx");

    // important part
    var data = await Excel.Reader.ReadAsync(file);

    System.Diagnostics.Debugger.Break();
}
catch (Exception ex)
{
    System.Diagnostics.Debugger.Break();
}