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 _rows; public Dictionary 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 ReadLineValues() { var results = new List(); 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 ReadLine() { if (EndOfData) { return new Dictionary(); } 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; } } }