inabox/inabox.scripting/FileReader/ExcelFileReader.cs

using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using InABox.Core;
using System;
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;

namespace InABox.Scripting
{
    public class ExcelFileReader : ITabularFileReader
    {
        private IEnumerator<IRow> rows;

        public Dictionary<string, int> Columns { get; set; }

        public bool EndOfData { get; private set; }

        private IRow _row;

        public ExcelFileReader(Stream stream)
        {
            rows = new Spreadsheet(stream).GetSheet(0).RowEnumerator();
            Columns = new Dictionary<string, int>();
            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
                    {
                        switch(cell.GetCellType())
                        {
                            case CellType.Formula:
                                result = cell.GetValue();
                                break;
                            case CellType.Numeric:
                                result = cell.GetDoubleValue();
                                break;
                            case CellType.Date:
                                result = cell.GetDateTimeValue();
                                break;
                            case CellType.String:
                                result = cell.GetValue();
                                break;
                            case CellType.Boolean:
                                result = cell.GetBoolValue();
                                break;
                            default:
                                result = null;
                                break;
                        }
                    }
                    return result;
                });
            SkipLine();
            return results;
        }

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