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 NSheet = NPOI.SS.UserModel.ISheet;
using NDataFormat = NPOI.SS.UserModel.IDataFormat;
using NCellStyle = NPOI.SS.UserModel.ICellStyle;
using NPOI.SS.Util;

namespace InABox.Scripting
{

    public class RowEnumerator : IEnumerator<Row>
    {
        private IEnumerator _enumerator { get; set; }

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

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

        internal RowEnumerator(IEnumerator enumerator)
        {
            _enumerator = enumerator;
        }

        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;

        internal Sheet(NSheet sheet)
        {
            _sheet = sheet;
        }

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

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

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

        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 class Row : IRow
    {
        private NRow _row;

        public int RowNumber => _row.RowNum;

        internal Row(NRow row)
        {
            _row = row;
        }
        public string ExtractString(int column, bool uppercase = false)
        {
            try
            {
                var result = "";
                var cell = _row.GetCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                if (cell.CellType == CellType.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 == CellType.Numeric || cell.CellType == CellType.Formula)
                    result = cell.NumericCellValue;
                else if (cell.CellType == CellType.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) => new Cell(_row.GetCell(column));

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

    public class Cell : ICell
    {

        private NCell _cell;

        internal Cell(NCell cell)
        {
            _cell = cell;
        }

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

            return _cell.ToString();
        }

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

        public double? GetDoubleValue()
        {
            try
            {
                double result = 0.0F;
                if (_cell.CellType == CellType.Numeric || _cell.CellType == CellType.Formula)
                    result = _cell.NumericCellValue;
                else if (_cell.CellType == CellType.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 == CellType.Numeric || _cell.CellType == CellType.Formula)
                    result = (byte)_cell.NumericCellValue;
                else if (_cell.CellType == CellType.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 ICell SetStyle(ICellStyle style)
        {
            _cell.CellStyle = (style as CellStyle)._style;
            return this;
        }
    }

    public class SheetEnumerator : IEnumerator<Sheet>
    {
        public Sheet Current => new(_enumerator.Current);

        private IEnumerator<NSheet> _enumerator { get; }

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

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

        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 CellStyle : ICellStyle
    {
        internal NCellStyle _style { get; }
        public IDataFormat DataFormat {
            get => new DataFormat(_style.DataFormat);
            set => _style.DataFormat = value.FormatIndex;
        }

        public CellStyle(NCellStyle style)
        {
            _style = style;
        }
    }

    public class Spreadsheet : ISpreadsheet
    {

        public IWorkbook Workbook;

        private NCellStyle DateStyle;
        private NDataFormat DataFormat;

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

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

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

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

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

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

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

        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);
        }

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

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

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