using System.Data;

using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace Admin.NET.Core.Helper.ExcelHelper
{
    public class Excel
    {
        private HSSFWorkbook _workBook;
        private ISheet _wbSheet = null;
        private DataColumnCollection _columns = null;

        private int _col = 0;    //total columns
        private int _row = 0;    //total rows
        //private int _sheet = 0;  //total sheets
        private int _sheetRowNum = 65536;  //each sheet allow rows

        public Excel()
        {
            InstanceWorkBook();
        }

        /// <summary>
        /// 实例方法
        /// </summary>
        /// <param name="columns">表头</param>
        public Excel(DataColumnCollection columns)
        {
            _columns = columns;
            InstanceWorkBook();
        }

        private void InstanceWorkBook()
        {
            _workBook = new HSSFWorkbook();

            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "BaiyiTimes";
            _workBook.DocumentSummaryInformation = dsi;

            var si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Etimes Secure Document System Log Backup";
            _workBook.SummaryInformation = si;
        }

        private DataColumnCollection GetColumns(DataColumnCollection columns)
        {
            return columns == null || columns.Count == 0 ? _columns : columns;
        }

        private ISheet GetSheet(ISheet sheet)
        {
            return sheet == null ? _wbSheet : sheet;
        }

        private void CreateHeader(ISheet sheet, DataColumnCollection columns)
        {
            _columns = GetColumns(columns);

            var oRow = sheet.CreateRow(0);
            oRow.Height = 20 * 20;
            foreach (DataColumn column in _columns)
            {
                var oCell = oRow.CreateCell(_col);

                var style1 = _workBook.CreateCellStyle();
                style1.Alignment = HorizontalAlignment.Center;
                style1.VerticalAlignment = VerticalAlignment.Center;

                var font = _workBook.CreateFont();
                font.IsBold = true;
                font.FontHeightInPoints = 11;
                style1.SetFont(font);

                oCell.CellStyle = style1;
                var name = column.ColumnName;
                oCell.SetCellValue(name.ToString());
                sheet.SetColumnWidth(_col, 60 * 60);
                _col++;
            }
            _row++;
        }

        public ISheet CreateSheet()
        {
            return CreateSheet(null, null);
        }

        public ISheet CreateSheet(DataColumnCollection columns, string name)
        {
            _wbSheet = _workBook.CreateSheet(name);
            CreateHeader(_wbSheet, columns);
            _col = 0;    //total columns
            _row = 1;
            return _wbSheet;
        }

        public void SetRowValue(DataRowCollection rows, ISheet sheet)
        {
            _wbSheet = GetSheet(sheet);
            foreach (DataRow row in rows)
            {
                SetRowValue(row);
            }
        }

        public void SetRowValue(DataRow row)
        {
            var style1 = _workBook.CreateCellStyle();
            style1.Alignment = HorizontalAlignment.Center;
            style1.VerticalAlignment = VerticalAlignment.Center;
            //if (_row % _sheetRowNum == 0)
            //{
            //    CreateSheet();
            //}
            var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);

            var obj = string.Empty;
            var cell = 0;
            foreach (DataColumn column in _columns)
            {
                obj = row[column.ColumnName].ToString();
                var cells = oRow.CreateCell(cell);
                cells.CellStyle = style1;
                cells.SetCellValue(obj);
                cell++;
            }
            _row++;
        }

        public void SaveAs(string filePath)
        {
            if (File.Exists(filePath)) File.Delete(filePath);
            var file = new FileStream(filePath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }
        public MemoryStream SaveAsMemoryStream()
        {
            var ms = new MemoryStream();
            _workBook.Write(ms);
            return ms;
        }
    }
}