inabox/inabox.scripting/Spreadsheet/NPOISpreadsheet.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Xml.Linq;
using InABox.Core;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NCell = NPOI.SS.UserModel.ICell;
using NRow = NPOI.SS.UserModel.IRow;
using NFont = NPOI.SS.UserModel.IFont;
using NSheet = NPOI.SS.UserModel.ISheet;
using NDataFormat = NPOI.SS.UserModel.IDataFormat;
using NCellStyle = NPOI.SS.UserModel.ICellStyle;
using NCellType = NPOI.SS.UserModel.CellType;
using NPOI.SS.Util;
using NPOI.OpenXmlFormats.Spreadsheet;
using System.Security.Policy;
using System.Drawing;
using NPOI.HSSF.Util;
using NPOI.HSSF.UserModel;
using NPOI.XWPF.UserModel;

namespace InABox.Scripting
{

    public class RowEnumerator : IEnumerator<Row>
    {
        public Sheet Sheet { get; }

        private IEnumerator _enumerator { get; set; }

        public Row Current => new Row((_enumerator.Current as NRow)!, Sheet);

        object IEnumerator.Current => new Row((_enumerator.Current as NRow)!, Sheet);

        internal RowEnumerator(IEnumerator enumerator, Sheet sheet)
        {
            _enumerator = enumerator;
            Sheet = sheet;
        }

        public bool MoveNext()
        {
            return _enumerator.MoveNext();
        }

        public void Reset()
        {
            _enumerator.Reset();
        }

        public void Dispose()
        {
        }
    }

    public class Sheet : ISheet
    {
        private NSheet _sheet;

        public string Name => _sheet.SheetName;

        public int FirstRow => _sheet.FirstRowNum;

        public int LastRow => _sheet.LastRowNum;

        public Spreadsheet Spreadsheet { get; }

        ISpreadsheet ISheet.Spreadsheet => Spreadsheet;

        internal Sheet(NSheet sheet, Spreadsheet spreadsheet)
        {
            _sheet = sheet;
            Spreadsheet = spreadsheet;
        }

        public IEnumerable<IRow> Rows()
        {
            var enumerator = _sheet.GetRowEnumerator();
            var row = 0;
            while (enumerator.MoveNext() && row <= int.MaxValue)
            {
                yield return new Row((NRow)enumerator.Current, this);
                row++;
            }
        }

        public IEnumerator<IRow> RowEnumerator()
        {
            return new RowEnumerator(_sheet.GetRowEnumerator(), this);
        }

        public IRow NewRow()
        {
            var row = _sheet.CreateRow(_sheet.LastRowNum + 1);
            return new Row(row, this);
        }

        public IRow? GetRow(int row)
        {
            var nRow = _sheet.GetRow(row);
            if (nRow is null) return null;
            return new Row(nRow, this);
        }

        public float GetRowHeight(int row)
        {
            return _sheet.GetRow(row)?.HeightInPoints ?? _sheet.DefaultRowHeightInPoints;
        }

        public float GetDefaultRowHeight() => _sheet.DefaultRowHeightInPoints;

        public float GetColumnWidth(int column)
        {
            if (_sheet.IsColumnHidden(column)) return 0f;
            var width = _sheet.GetColumnWidth(column) / 256f;
            if (width <= 0f) return float.MinValue;
            return width;
        }
        public ISheet SetColumnWidth(int column, float charWidth)
        {
            _sheet.SetColumnWidth(column, (int)Math.Round(charWidth * 256));
            return this;
        }

        public ISheet MergeCells(int firstRow, int lastRow, int firstColumn, int lastColumn)
        {
            var range = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
            _sheet.AddMergedRegion(range);
            return this;
        }

        public IEnumerable<CellRange> GetMergedCells()
        {
            foreach(var region in _sheet.MergedRegions)
            {
                yield return new CellRange(region.FirstRow, region.LastRow, region.FirstColumn, region.LastColumn);
            }
        }
    }

    public class Row : IRow
    {
        private NRow _row;

        public int RowNumber => _row.RowNum;

        public int FirstColumn => _row.FirstCellNum;

        public int LastColumn => _row.LastCellNum;

        public Sheet Sheet { get; }

        ISheet IRow.Sheet => Sheet;

        internal Row(NRow row, Sheet sheet)
        {
            _row = row;
            Sheet = sheet;
        }
        public string ExtractString(int column, bool uppercase = false)
        {
            try
            {
                var result = "";
                var cell = _row.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                if (cell.CellType == NCellType.Numeric)
                    result = cell.NumericCellValue.ToString();
                else
                    result = cell.StringCellValue;
                if (string.IsNullOrWhiteSpace(result))
                    result = "";
                return uppercase ? result.ToUpper() : result;
            }
            catch (Exception e)
            {
                throw new Exception(string.Format("Row {0}:[{1}:{2}]: {3}", _row.Sheet.SheetName, _row.RowNum + 1, column, e.Message));
            }
        }

        public DateTime ExtractDateTime(int column)
        {
            try
            {
                var cell = _row.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                try
                {
                    return cell.DateCellValue;
                }
                catch
                {
                    var sDate = cell.StringCellValue;
                    DateTime.TryParse(sDate, out var result);
                    return result;
                }
            }
            catch (Exception e)
            {
                throw new Exception(string.Format("Row {0}:[{1}:{2}]: {3}", _row.Sheet.SheetName, _row.RowNum + 1, column, e.Message));
            }
        }

        public double? ExtractDouble(int column)
        {
            try
            {
                double result = 0.0F;
                var cell = _row.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                if (cell.CellType == NCellType.Numeric || cell.CellType == NCellType.Formula)
                    result = cell.NumericCellValue;
                else if (cell.CellType == NCellType.String)
                    result = double.Parse(cell.StringCellValue);
                return result;
            }
            catch (FormatException)
            {
                return null;
            }
            catch (Exception e)
            {
                throw new Exception(string.Format("Row {0}:[{1}:{2}]: {3}", _row.Sheet.SheetName, _row.RowNum + 1, column, e.Message));
            }
        }

        public int GetColumn(string name, bool throwException = true)
        {
            var cells = _row.GetEnumerator();
            while (cells.MoveNext())
            {
                var cell = cells.Current;
                if (!string.IsNullOrWhiteSpace(cell.StringCellValue) && cell.StringCellValue.ToUpper().Trim().Equals(name.ToUpper().Trim()))
                {
                    return cell.ColumnIndex;
                }
            }

            if (!throwException)
            {
                return -1;
            }

            throw new Exception("Unable to find Column: " + name);
        }

        public ICell? GetCell(int column)
        {
            var nCell = _row.GetCell(column);
            if (nCell is null) return null;
            return new Cell(nCell, this);
        }

        public ICell NewCell(int column)
        {
            var cell = _row.CreateCell(column);
            return new Cell(cell, this);
        }

        public IEnumerable<ICell> Cells()
        {
            foreach(var cell in _row)
            {
                yield return new Cell(cell, this);
            }
        }
    }

    public class Cell : ICell
    {

        private NCell _cell;

        IRow ICell.Row => Row;

        public Row Row { get; }

        internal Cell(NCell cell, Row row)
        {
            _cell = cell;
            Row = row;
        }

        private CellType ConvertCellType(NCellType type)
        {
            return type switch
            {
                NCellType.Formula => CellType.Numeric,
                NCellType.Numeric => CellType.Numeric,
                NCellType.Error => CellType.Error,
                NCellType.String => CellType.String,
                NCellType.Boolean => CellType.Boolean,
                NCellType.Blank => CellType.Blank,
                _ or NCellType.Unknown => CellType.Unknown
            };
        }

        public CellType GetCellType()
        {
            if (_cell.CellType == NCellType.Formula)
            {
                return ConvertCellType(_cell.CachedFormulaResultType);
            }
            else if(_cell.CellType == NCellType.Numeric && DateUtil.IsCellDateFormatted(_cell))
            {
                return CellType.Date;
            }
            return ConvertCellType(_cell.CellType);
        }

        public string GetValue()
        {
            if (_cell.CellType == NCellType.Formula)
            {
                if (_cell.CachedFormulaResultType == NCellType.Numeric)
                    return string.Format("{0:F}", _cell.NumericCellValue.ToString());
                return _cell.StringCellValue;
            }

            return _cell.ToString() ?? "";
        }

        public bool? GetBoolValue()
        {
            try
            {
                if (_cell.CellType == NCellType.Boolean)
                    return _cell.BooleanCellValue;
                return null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public double? GetDoubleValue()
        {
            try
            {
                double result = 0.0F;
                if (_cell.CellType == NCellType.Numeric || _cell.CellType == NCellType.Formula)
                    result = _cell.NumericCellValue;
                else if (_cell.CellType == NCellType.String)
                    result = double.Parse(_cell.StringCellValue);
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DateTime GetDateTimeValue()
        {
            try
            {
                return _cell.DateCellValue;
            }
            catch
            {
                var sDate = _cell.StringCellValue;
                if(!DateTime.TryParse(sDate, out var result)){
                    return DateTime.MinValue;
                }
                return result;
            }
        }

        public byte? GetByteValue()
        {
            try
            {
                byte result = 0;
                if (_cell.CellType == NCellType.Numeric || _cell.CellType == NCellType.Formula)
                    result = (byte)_cell.NumericCellValue;
                else if (_cell.CellType == NCellType.String)
                    result = byte.Parse(_cell.StringCellValue);
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public ICell SetValue(bool value)
        {
            _cell.SetCellValue(value);
            return this;
        }
        public ICell SetValue(double value)
        {
            _cell.SetCellValue(value);
            return this;
        }
        public ICell SetValue(string value)
        {
            _cell.SetCellValue(value);
            return this;
        }
        public ICell SetValue(byte value)
        {
            _cell.SetCellValue(value);
            return this;
        }
        public ICell SetValue(DateTime value)
        {
            _cell.SetCellValue(value);
            return this;
        }

        public ICell SetBlank()
        {
            _cell.SetBlank();
            return this;
        }

        public ICellStyle GetStyle()
        {
            return new CellStyle(_cell.CellStyle, Row.Sheet.Spreadsheet);
        }
        public ICell SetStyle(ICellStyle style)
        {
            _cell.CellStyle = (style as CellStyle)!._style;
            return this;
        }
    }

    public class SheetEnumerator : IEnumerator<Sheet>
    {
        public Spreadsheet Spreadsheet { get; }

        public Sheet Current => new(_enumerator.Current, Spreadsheet);

        private IEnumerator<NSheet> _enumerator { get; }

        object IEnumerator.Current => new Sheet(_enumerator.Current, Spreadsheet);

        internal SheetEnumerator(IEnumerator<NSheet> enumerator, Spreadsheet spreadsheet)
        {
            _enumerator = enumerator;
            Spreadsheet = spreadsheet;
        }

        public void Dispose()
        {
            _enumerator.Dispose();
        }

        public bool MoveNext()
        {
            return _enumerator.MoveNext();
        }

        public void Reset()
        {
            _enumerator.Reset();
        }
    }

    public class DataFormat : IDataFormat
    {
        public short FormatIndex { get; }

        public DataFormat(short format)
        {
            FormatIndex = format;
        }
    }

    public class Font : IFont
    {
        public Spreadsheet Spreadsheet { get; set; }

        internal NFont _font { get; }
        public bool Bold { get => _font.IsBold; set => _font.IsBold = value; }
        public bool Italic { get => _font.IsItalic; set => throw new NotImplementedException(); }
        public UnderlineType Underline
        {
            get => _font.Underline switch
            {
                FontUnderlineType.None => UnderlineType.None,
                FontUnderlineType.Single => UnderlineType.Single,
                FontUnderlineType.Double => UnderlineType.Double,
                FontUnderlineType.SingleAccounting => UnderlineType.SingleAccounting,
                FontUnderlineType.DoubleAccounting => UnderlineType.DoubleAccounting,
                _ => UnderlineType.None,
            };
            set
            {
                _font.Underline = value switch
                {
                    UnderlineType.None => FontUnderlineType.None,
                    UnderlineType.Single => FontUnderlineType.Single,
                    UnderlineType.Double => FontUnderlineType.Double,
                    UnderlineType.SingleAccounting => FontUnderlineType.SingleAccounting,
                    UnderlineType.DoubleAccounting => FontUnderlineType.DoubleAccounting,
                    _ => FontUnderlineType.None
                };
            }
        }
        public Color Colour {
            get
            {
                if(_font is XSSFFont xFont)
                {
                    return CellStyle.ConvertColour(xFont.GetXSSFColor());
                }
                else if(_font is HSSFFont hFont && Spreadsheet.Workbook is HSSFWorkbook workbook)
                {
                    return CellStyle.ConvertColour(hFont.GetHSSFColor(workbook));
                }
                else
                {
                    return CellStyle.ColourFromIndex(_font.Color);
                }
            }
        }

        public double FontSize { get => _font.FontHeightInPoints; set => _font.FontHeightInPoints = value; }

        public Font(NFont font, Spreadsheet spreadsheet)
        {
            _font = font;
            Spreadsheet = spreadsheet;
        }
    }

    public class CellStyle : ICellStyle
    {
        internal NCellStyle _style { get; }
        public IDataFormat DataFormat {
            get => new DataFormat(_style.DataFormat);
            set => _style.DataFormat = value.FormatIndex;
        }

        ISpreadsheet ICellStyle.Spreadsheet => Spreadsheet;

        public Spreadsheet Spreadsheet { get; }

        public Color Background => ConvertColour(_style.FillForegroundColorColor);

        public Color Foreground => ColourFromIndex(_style.GetFont(Spreadsheet.Workbook).Color);

        public IFont Font => new Font(_style.GetFont(Spreadsheet.Workbook), Spreadsheet);

        public CellAlignment VerticalAlignment => _style.VerticalAlignment switch
        {
            NPOI.SS.UserModel.VerticalAlignment.Top => CellAlignment.Start,
            NPOI.SS.UserModel.VerticalAlignment.Bottom => CellAlignment.End,
            NPOI.SS.UserModel.VerticalAlignment.Justify => CellAlignment.Justify,
            _ => CellAlignment.Middle
        };

        public CellAlignment HorizontalAlignment => _style.Alignment switch
        {
            NPOI.SS.UserModel.HorizontalAlignment.Center => CellAlignment.Middle,
            NPOI.SS.UserModel.HorizontalAlignment.Right => CellAlignment.End,
            NPOI.SS.UserModel.HorizontalAlignment.Justify => CellAlignment.Justify,
            _ => CellAlignment.Start
        };

        public bool WrapText => _style.WrapText;
        
        public CellStyle(NCellStyle style, Spreadsheet spreadsheet)
        {
            _style = style;
            Spreadsheet = spreadsheet;
        }

        public static Color ColourFromIndex(short index)
        {
            int indexNum = index;
            var hashIndex = HSSFColor.GetIndexHash();
            HSSFColor? indexed = null;
            if (hashIndex.ContainsKey(indexNum))
                indexed = hashIndex[indexNum];
            if (indexed != null)
            {
                byte[] rgb = new byte[3];
                rgb[0] = (byte)indexed.GetTriplet()[0];
                rgb[1] = (byte)indexed.GetTriplet()[1];
                rgb[2] = (byte)indexed.GetTriplet()[2];
                return Color.FromArgb(255, rgb[0], rgb[1], rgb[2]);
            }
            return Color.Empty;
        }

        public static Color ConvertColour(IColor? colour)
        {
            if(colour is null)
            {
                return Color.Empty;
            }
            if(colour is ExtendedColor extendedColour)
            {
                if (extendedColour.IsIndexed)
                {
                    return ColourFromIndex(extendedColour.Index);
                }
                else
                {
                    var rgb = extendedColour.RGBWithTint;
                    return Color.FromArgb(255, rgb[0], rgb[1], rgb[2]);
                }
            }
            else if(colour is HSSFColor hssfColour)
            {
                var rgb = hssfColour.RGB;
                return Color.FromArgb(255, rgb[0], rgb[1], rgb[2]);
            }
            else
            {
                Logger.Send(LogType.Error, "", $"Unknown NPOI Colour class {colour.GetType()}");
                return Color.Empty;
            }
        }
    }

    public class Spreadsheet : ISpreadsheet
    {

        public IWorkbook Workbook;

        private NDataFormat DataFormat;

        private Spreadsheet(IWorkbook workbook)
        {
            Workbook = workbook;
            DataFormat = Workbook.CreateDataFormat();
        }

        public Spreadsheet(string fileName) : this(WorkbookFactory.Create(fileName)) { }

        public Spreadsheet(Stream file) : this(WorkbookFactory.Create(file)) { }

        public Spreadsheet() : this(new XSSFWorkbook()) { }

        public ISheet GetSheet(int index)
        {
            return new Sheet(Workbook.GetSheetAt(index), this);
        }

        public ISheet GetSheet(string name)
        {
            return new Sheet(Workbook.GetSheet(name), this);
        }

        public IEnumerator<ISheet> SheetEnumerator()
        {
            var enumerator = Workbook.GetEnumerator();
            return new SheetEnumerator(enumerator, this);
        }

        public IEnumerable<ISheet> Sheets()
        {
            for (var i = 0; i < Workbook.NumberOfSheets; i++)
                yield return GetSheet(i);
        }

        public void Write(FileStream file)
        {
            Workbook.Write(file);
        }

        public void Write(string filename, FileMode mode = FileMode.Create)
        {
            using(var stream = new FileStream(filename, FileMode.Create))
            {
                Workbook.Write(stream);
            }
        }

        public ISheet NewSheet(string name)
        {
            var sheet = Workbook.CreateSheet(name);
            return new Sheet(sheet, this);
        }

        public ISheet NewSheet()
        {
            var sheet = Workbook.CreateSheet();
            return new Sheet(sheet, this);
        }

        public ICellStyle NewStyle()
        {
            var style = Workbook.CreateCellStyle();
            var x = style.GetDataFormatString();
            return new CellStyle(style, this);
        }

        public IDataFormat GetDataFormat(string format)
        {
            var dataFormat = DataFormat.GetFormat(format);
            return new DataFormat(dataFormat);
        }
    }
}