inabox/inabox.scripting/FileReader/ExcelFileReader.cs

using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using InABox.Core;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static NPOI.HSSF.UserModel.HeaderFooter;
using System.Diagnostics.CodeAnalysis;

namespace InABox.Scripting
{
    public class ExcelFileReader : ITabularFileReader
    {

        private readonly Spreadsheet _sheet;
        
        private IEnumerator<IRow> _rows;

        public Dictionary<string, int> Columns { get; set; } = new();

        [MemberNotNullWhen(false, nameof(_row))]
        public bool EndOfData { get; private set; }

        private IRow? _row;

        public ExcelFileReader(Stream stream)
        {
            _sheet = new Spreadsheet(stream);
            SelectSheet(0);
        }

        public void SelectSheet(int sheet)
        {
            _rows = _sheet.GetSheet(sheet).RowEnumerator();
            Columns.Clear();
            SkipLine();
        }
        
        public IList<string> ReadLineValues()
        {
            var results = new List<string>();

            if (!EndOfData)
            {
                foreach (var cell in _row.Cells())
                {
                    results.Add(cell.GetValue());
                }
                SkipLine();
            }
            return results;
        }

        public bool ReadHeader()
        {
            Columns.Clear();

            if (EndOfData)
            {
                return false;
            }

            int i = 0;
            foreach(var cell in _row.Cells())
            {
                var column = cell.GetValue();
                if (!column.IsNullOrWhiteSpace())
                {
                    Columns.Add(column, i);
                }
                ++i;
            }
            SkipLine();

            return true;
        }

        public Dictionary<string, object?> ReadLine()
        {
            if (EndOfData)
            {
                return new Dictionary<string, object?>();
            }

            var results = Columns.ToDictionary(
                x => x.Key,
                x =>
                {
                    object? result;
                    var cell = _row.GetCell(x.Value);
                    if(cell is null)
                    {
                        result = null;
                    }
                    else
                    {
                        result = cell.GetCellType() switch
                        {
                            CellType.Formula => cell.GetValue(),
                            CellType.Numeric => cell.GetDoubleValue(),
                            CellType.Date => cell.GetDateTimeValue(),
                            CellType.String => cell.GetValue(),
                            CellType.Boolean => cell.GetBoolValue(),
                            _ => null,
                        };
                    }
                    return result;
                });
            SkipLine();
            return results;
        }

        public bool SkipLine()
        {
            if (!EndOfData)
            {
                EndOfData = !_rows.MoveNext();
                if (!EndOfData)
                {
                    _row = _rows.Current;
                }
            }
            return EndOfData;
        }
    }
}