using NPOI;
using NPOI.OpenXml4Net;
using NPOI.OpenXmlFormats;
using NPOI.XWPF.UserModel;

using NPOI.SS;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.IO;
using Newtonsoft.Json.Linq;
using System.Collections.Generic;

namespace XHD.Core.View.Configs
{
    public class NPOIExt
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="book"></param>
        /// <param name="sheet_name"></param>
        /// <param name="title_name"></param>
        /// <param name="sheet_title"></param>
        /// <param name="arr"></param>
        public void create_sheet(HSSFWorkbook book,string sheet_name, string title_name, string[] sheet_title, JArray arr)
        {
            ICellStyle cellstyle = book.CreateCellStyle();

            #region
            //样式
            cellstyle.BorderBottom = BorderStyle.Thin;
            cellstyle.BottomBorderColor = HSSFColor.Black.Index;
            cellstyle.BorderLeft = BorderStyle.Thin;
            cellstyle.LeftBorderColor = HSSFColor.Black.Index;
            cellstyle.BorderRight = BorderStyle.Thin;
            cellstyle.RightBorderColor = HSSFColor.Black.Index; ;
            cellstyle.BorderTop = BorderStyle.Thin;
            cellstyle.TopBorderColor = HSSFColor.Black.Index; ;
            cellstyle.Alignment = HorizontalAlignment.Center;
            cellstyle.VerticalAlignment = VerticalAlignment.Center;


            ICellStyle cellstyle1 = book.CreateCellStyle();

            cellstyle1.BorderBottom = BorderStyle.Thin;
            cellstyle1.BottomBorderColor = HSSFColor.Black.Index;
            cellstyle1.BorderLeft = BorderStyle.Thin;
            cellstyle1.LeftBorderColor = HSSFColor.Black.Index;
            cellstyle1.BorderRight = BorderStyle.Thin;
            cellstyle1.RightBorderColor = HSSFColor.Black.Index; ;
            cellstyle1.BorderTop = BorderStyle.Thin;
            cellstyle1.TopBorderColor = HSSFColor.Black.Index; ;
            cellstyle1.Alignment = HorizontalAlignment.Center;
            cellstyle1.VerticalAlignment = VerticalAlignment.Center;

            IFont font = book.CreateFont();
            font.IsBold = true;
            cellstyle1.SetFont(font);

            ICellStyle cellstyle3 = book.CreateCellStyle();

            cellstyle3.BorderBottom = BorderStyle.Thin;
            cellstyle3.BottomBorderColor = HSSFColor.Black.Index;
            cellstyle3.BorderLeft = BorderStyle.Thin;
            cellstyle3.LeftBorderColor = HSSFColor.Black.Index;
            cellstyle3.BorderRight = BorderStyle.Thin;
            cellstyle3.RightBorderColor = HSSFColor.Black.Index; ;
            cellstyle3.BorderTop = BorderStyle.Thin;
            cellstyle3.TopBorderColor = HSSFColor.Black.Index; ;
            cellstyle3.Alignment = HorizontalAlignment.Center;
            cellstyle3.VerticalAlignment = VerticalAlignment.Center;

            IFont font3 = book.CreateFont();
            font3.IsBold = true;
            font3.FontHeightInPoints = 10;
            cellstyle3.SetFont(font3);

            ICellStyle cellstyle4 = book.CreateCellStyle();

            cellstyle4.BorderBottom = BorderStyle.Thin;
            cellstyle4.BottomBorderColor = HSSFColor.Black.Index;
            cellstyle4.BorderLeft = BorderStyle.Thin;
            cellstyle4.LeftBorderColor = HSSFColor.Black.Index;
            cellstyle4.BorderRight = BorderStyle.Thin;
            cellstyle4.RightBorderColor = HSSFColor.Black.Index; ;
            cellstyle4.BorderTop = BorderStyle.Thin;
            cellstyle4.TopBorderColor = HSSFColor.Black.Index; ;
            cellstyle4.Alignment = HorizontalAlignment.Right;
            cellstyle4.VerticalAlignment = VerticalAlignment.Center;
            cellstyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0.00");


            #endregion

            ////字段宽度
            //int[] rowWidth = {
            //        15,25,25,25,25,
            //        25,25,25,25,25,
            //        25,25,25,25,25,
            //        25,25,25,25,25,
            //        25,25,25,25,25,
            //        25,25,25,25,25,
            //        25,25,25,25,25,
            //        25,25,25,25,25
            //    };

            #region 项目表            

            //先创建项目表
            ISheet sheet = book.CreateSheet(sheet_name);

            //for (int i = 0; i < guarantee_title.Length; i++)
            //{
            //    sheet_guarantee.SetColumnWidth(i, rowWidth[i] * 240);
            //}

            //标题
            IRow row0 = sheet.CreateRow(0);
            row0.Height = 20 * 30;

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, sheet_title.Length - 1));

            NPOI.SS.UserModel.ICell cell0 = row0.CreateCell(0);
            cell0.SetCellValue(title_name);
            cell0.CellStyle = cellstyle3;

            //首行
            IRow rowDate = sheet.CreateRow(1);
            rowDate.Height = 20 * 25;

            NPOI.SS.UserModel.ICell celldate = rowDate.CreateCell(0);


            for (int r = 0; r < sheet_title.Length; r++)
            {
                celldate = rowDate.CreateCell(r);
                celldate.SetCellValue(sheet_title[r]);
                celldate.CellStyle = cellstyle1;
            }

            //表内容
            int i = 0;
            foreach (JObject obj in arr)
            {
                IRow irow = sheet.CreateRow(i + 2);
                irow.Height = 20 * 20;

                int j = 0;
                IEnumerable<JProperty> properties = obj.Properties();
                foreach (JProperty item in properties)
                {
                    NPOI.SS.UserModel.ICell cell = irow.CreateCell(j);
                    cell.SetCellValue(item.Value.ToString());
                    cell.CellStyle = cellstyle;
                    j++;
                }

                i++;
            }

            #endregion
        }
    }
}
