using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using HH.WMS.Entitys;

namespace HH.WMS.Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <param name="title">表头名</param>
        /// <param name="cellTexts">每列单元格的名称</param>
        /// <param name="cellWidths">每列单元格的宽度</param>
        /// <param name="cellValues">对应DataTable中的列名</param>
        /// <param name="table"></param>
        /// <returns></returns>
        public static MemoryStream DataTableToExcel(string title, string[] cellTexts, int[] cellWidths, string[] cellValues, DataTable table)
        {
            MemoryStream ms = new MemoryStream();
            using (table)
            {
                //初始化
                HSSFWorkbook _book = new HSSFWorkbook();
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "汉和";
                _book.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = title;
                _book.SummaryInformation = si;

                //创建表格
                ISheet _sheet = _book.CreateSheet(title);

                //第一行:表头
                IRow _row = _sheet.CreateRow(0);
                _row.HeightInPoints = 30;

                ICell _cell = _row.CreateCell(0);
                _cell.SetCellValue(title);

                ICellStyle style = _book.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                IFont font = _book.CreateFont();
                font.FontHeight = 20 * 20;
                font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //粗体
                style.SetFont(font);
                _cell.CellStyle = style;

                _sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellTexts.Length - 1));

                //第二行：字段名称
                ICellStyle _style = _book.CreateCellStyle();
                _style.Alignment = HorizontalAlignment.Center;
                IFont _font = _book.CreateFont();
                _font.FontHeight = 15 * 15;
                _font.FontName = "微软雅黑";
                _style.SetFont(_font);

                _row = _sheet.CreateRow(1);
                _row.HeightInPoints = 20;

                for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
                {
                    _cell = _row.CreateCell(cellIndex);
                    _cell.SetCellValue(cellTexts[cellIndex]);
                    _cell.CellStyle = _style;
                    _sheet.SetColumnWidth(cellIndex, cellWidths[cellIndex]);
                }

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    _row = _sheet.CreateRow(i + 2);
                    _row.HeightInPoints = 20;

                    for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
                    {
                        _cell = _row.CreateCell(cellIndex);
                        _cell.SetCellValue(table.Rows[i][cellValues[cellIndex]].ToString());
                    }
                }
                _book.Write(ms);
                ms.Flush();
            }

            return ms;
        }

        public static MemoryStream ListToExcel<T>(string title, string[] cellTexts, int[] cellWidths, string[] cellValues, List<T> lists)
        {
            MemoryStream ms = new MemoryStream();
            //初始化
            HSSFWorkbook _book = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "汉和";
            _book.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = title;
            _book.SummaryInformation = si;

            //创建表格
            ISheet _sheet = _book.CreateSheet(title);

            //第一行:表头
            IRow _row = _sheet.CreateRow(0);
            _row.HeightInPoints = 30;

            ICell _cell = _row.CreateCell(0);
            _cell.SetCellValue(title);

            ICellStyle style = _book.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            IFont font = _book.CreateFont();
            font.FontHeight = 20 * 20;
            font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //粗体
            style.SetFont(font);
            _cell.CellStyle = style;

            _sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellTexts.Length - 1));

            //第二行：字段名称
            ICellStyle _style = _book.CreateCellStyle();
            _style.Alignment = HorizontalAlignment.Center;
            IFont _font = _book.CreateFont();
            _font.FontHeight = 15 * 15;
            _font.FontName = "微软雅黑";
            _style.SetFont(_font);

            _row = _sheet.CreateRow(1);
            _row.HeightInPoints = 20;

            for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
            {
                _cell = _row.CreateCell(cellIndex);
                _cell.SetCellValue(cellTexts[cellIndex]);
                _cell.CellStyle = _style;
                _sheet.SetColumnWidth(cellIndex, cellWidths[cellIndex]);
            }

            for (int i = 0; i < lists.Count; i++)
            {
                _row = _sheet.CreateRow(i + 2);
                _row.HeightInPoints = 20;

                foreach (var list in lists)
                {
                    for (int cellIndex = 0; cellIndex < cellTexts.Length; cellIndex++)
                    {
                        _cell = _row.CreateCell(cellIndex);
                        _cell.SetCellValue(GetValue<T>(list, cellValues[cellIndex]));
                        _cell.CellStyle = _style;
                    }

                }

            }
            _book.Write(ms);
            ms.Flush();

            return ms;
        }

        private static string GetValue<T>(T t, string name)
        {
            var p = typeof(T).GetProperty(name);
            object obj = null;
            if (p != null)
            {
                obj = p.GetValue(t, null);
                return obj != null ? obj.ToString() : "";
            }
            else
                return "";
        }

        public static OperateResult SaveTemp(dynamic columns,string title,DataTable dt,string fileName)
        {
            try
            {
                string[] cellTexts = new string[columns.Count];
                string[] cellValues = new string[columns.Count];
                int[] cellWidths = new int[columns.Count];

                for (int i = 0; i < columns.Count; i++)
                {
                    cellTexts[i] = columns[i].n;
                    cellValues[i] = columns[i].f;
                    cellWidths[i] = Convert.ToInt16((columns[i].w == null ? 100 : columns[i].w.Value)) * 50;
                }

                MemoryStream ms = ExcelHelper.DataTableToExcel(title,
                                             cellTexts,
                                             cellWidths,
                                             cellValues,
                                             dt
                     );
                byte[] buffer;
                buffer = ms.ToArray();
                ms.Dispose();
                string tempPath = Path.GetTempPath() + fileName;
                using (FileStream fs = new FileStream(tempPath, FileMode.OpenOrCreate, FileAccess.Write))
                {
                    fs.Write(buffer, 0, buffer.Length);
                    fs.Flush();
                    buffer = null;
                }
                return OperateResult.Succeed();
            }
            catch (Exception ex)
            {
                return OperateResult.Error(ex.Message);
            }
        }
    }
}