using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.Reflection;
using System.ComponentModel;

namespace YCBX.Office.ExcelXml
{
    /// <summary>
    /// 写入Excel
    /// </summary>
    public class ExcelWrite
    {
        /// <summary>
        /// 写入文本
        /// </summary>
        /// <param name="path">文件</param>
        /// <param name="objs">List<T>对象，他的默认值为第一行</param>
        /// <param name="sheetName">把数据加入到工作薄的工作薄名</param>
        /// <param name="goRow">开始行（从1开始）</param>
        /// <param name="goCol">开始列（从1开始）</param>
        public static void WriteObj(string path, object objs, string sheetName = "", uint goRow = 1, int goCol = 1)
        {
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
            {
                var type = objs.GetType();
                var enumer = type.GetInterface("IEnumerable", false);
                if (type.IsGenericType && enumer != null)
                {
                    WorkbookPart workbookPart = spreadSheet.WorkbookPart;

                    //获取第一个工作表
                    Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
                    WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet);

                    //如果SharedStringTablePart不存在创建一个新的
                    SharedStringTablePart shareStringPart;
                    if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                        shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    else
                        shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();

                    //如果部分不包含SharedStringTable，则创建一个。
                    if (shareStringPart.SharedStringTable == null)
                        shareStringPart.SharedStringTable = new SharedStringTable();

                    uint row = goRow;
                    int col = goCol;
                    List<string> paiChu = new List<string>();
                    //表头
                    foreach (object obj in objs as dynamic)
                    {
                        //取类上的自定义特性
                        bool isPaiChuClass = false;
                        var newType = obj.GetType();
                        var exc = (IEnumerable<ExcelColumnAttribute>)newType.GetCustomAttributes(typeof(ExcelColumnAttribute));
                        if (exc.Any() && !exc.First().IsShow)
                            isPaiChuClass = true;

                        //取属性上的自定义特性
                        foreach (var property in newType.GetRuntimeProperties())
                        {
                            ExcelColumnAttribute att = null;
                            var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute));
                            if (atts.Any())
                                att = atts.First();

                            if (att != null && !att.IsShow)
                            {
                                paiChu.Add(property.Name);
                                continue;
                            }
                            //排除了类的列后不允许添加默认行
                            else if (isPaiChuClass)
                                continue;
                            else if (att == null || string.IsNullOrEmpty(att.ColumnName))
                                NewMethod(row, col, property.Name, shareStringPart, worksheetPart);
                            else
                                NewMethod(row, col, att.ColumnName, shareStringPart, worksheetPart);

                            col++;
                        }

                        if (!isPaiChuClass)
                            row++;

                        break;
                    }

                    //正文
                    foreach (object obj in objs as dynamic)
                    {
                        col = goCol;
                        foreach (var property in obj.GetType().GetRuntimeProperties())
                        {
                            if (paiChu.Contains(property.Name))
                                continue;

                            //var aaa = property.PropertyType.Name;
                            var value = property.GetValue(obj)?.ToString() ?? "";

                            NewMethod(row, col, value, shareStringPart, worksheetPart);

                            col++;
                        }
                        row++;
                    }

                    //保存新工作表
                    worksheetPart.Worksheet.Save();
                }
                else
                {
                    throw new Exception("需要是一个泛型集合");
                }
            }
        }

        private static void NewMethod(uint row, int column, string text, SharedStringTablePart shareStringPart, WorksheetPart worksheetPart)
        {
            #region 将文本插入到SharedStringTablePart中

            int index = 0;

            //遍历SharedStringTable中的所有项。如果文本已经存在，则返回其索引。
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    break;
                index++;
            }

            //这部分没有正文。创建SharedStringItem并返回它的索引。
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

            #endregion

            #region 将单元格A1插入工作表

            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();

            string columnName = ExcelAlphabet.ColumnToABC(column);
            uint rowIndex = row;

            string cellReference = columnName + rowIndex;

            //如果工作表不包含具有指定行索引的行，则插入一行
            Row rowobj;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                rowobj = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                rowobj = new Row() { RowIndex = rowIndex };
                sheetData.Append(rowobj);
            }

            Cell newCell2;
            //如果没有具有指定列名的单元格，则插入一个。 
            if (rowobj.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                newCell2 = rowobj.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                //细胞必须按照细胞参考的顺序排列。确定在何处插入新单元格。
                Cell refCell = null;
                foreach (Cell item in rowobj.Elements<Cell>())
                {
                    if (item.CellReference.Value.Length == cellReference.Length)
                    {
                        if (string.Compare(item.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = item;
                            break;
                        }
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                rowobj.InsertBefore(newCell, refCell);

                newCell2 = newCell;
            }
            #endregion

            //设置单元格A1的值
            newCell2.CellValue = new CellValue(index.ToString());
            newCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);
        }

        /// <summary>
        /// 写入文本
        /// </summary>
        /// <param name="path"></param>
        /// <param name="row">行</param>
        /// <param name="column">列</param>
        /// <param name="text">文本</param>
        /// <param name="sheetName">工作薄</param>
        public static void WriteText(string path, uint row, int column, string text, string sheetName = "")
        {
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
            {
                WorkbookPart workbookPart = spreadSheet.WorkbookPart;

                //如果SharedStringTablePart不存在创建一个新的
                SharedStringTablePart shareStringPart;
                if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                else
                    shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();

                //将文本插入到SharedStringTablePart中
                int index = InsertSharedStringItem(text, shareStringPart);

                //获取第一个工作表
                Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
                WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet);

                //将单元格A1插入新工作表
                Cell cell = InsertCellInWorksheet(ExcelAlphabet.ColumnToABC(column), row, worksheetPart);

                //设置单元格A1的值
                cell.CellValue = new CellValue(index.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                //保存新工作表
                worksheetPart.Worksheet.Save();
            }
        }

        //验证指定的文本是否存在于 SharedStringTablePart 对象中，并在不存在时添加文本
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            //如果部分不包含SharedStringTable，则创建一个。
            if (shareStringPart.SharedStringTable == null)
                shareStringPart.SharedStringTable = new SharedStringTable();

            int i = 0;

            //遍历SharedStringTable中的所有项。如果文本已经存在，则返回其索引。
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    return i;

                i++;
            }

            //这部分没有正文。创建SharedStringItem并返回它的索引。
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

        /// <summary>
        /// 插入一个新的工作表（如Sheet2）
        /// </summary>
        /// <param name="workbookPart">工作簿</param>
        /// <returns></returns>
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            //向工作簿添加新工作表部件。
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            //为新工作表获取唯一的ID
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            //附加新工作表并将其与工作簿关联。
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        // 将新的 Cell 对象插入到 Worksheet 对象中
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            //如果工作表不包含具有指定行索引的行，则插入一行
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            //如果没有具有指定列名的单元格，则插入一个。 
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                //细胞必须按照细胞参考的顺序排列。确定在何处插入新单元格。
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (cell.CellReference.Value.Length == cellReference.Length)
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

    }
}