C# OpenXml组件

    public class OpenXmlHelper
    {
        #region 导入
        /// <summary>
        /// 按照给定的地址读取Excel转换成DataTable
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="page">页码</param>
        /// <param name="pagesize">页行数</param>
        /// <param name="sheetName">Excel的Sheet名；默认第一个Sheet</param>
        /// <param name="index">列头行；默认第一列</param>
        /// <returns></returns>
        /// OpenXml只支持Excel2007版后的.xlsx
        public static DataTable Read(string path, int page = 0, int pagesize = 0, string sheetName = null, int index = 1)
        {
            try
            {
                DataTable dt = new DataTable();
                if (!path.ToLower().Trim().EndsWith(".xlsx"))
                {
                    throw new Exception("OpenXml组件只支持Office Excel2007版后的.xlsx格式文件");
                }
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false))
                {
                    //打开Stream
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower());
                        if (sheets.Count() == 0)
                        {
                            throw new Exception("没有找到该Sheet");
                        }
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                    //获取Excel中共享数据
                    SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == index)//Excel的列名列
                        {
                            GetDataColumn(row, stringTable, ref dt);
                            break;
                        }
                    }
                    if (page != 0 && pagesize != 0)
                    {
                        rows = rows.Skip((pagesize * (page - 1)) + 1).Take(pagesize);
                        foreach (Row row in rows)
                        {
                            if (row.RowIndex != index)
                            {
                                GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理
                            }
                        }
                    }
                    else
                    {

                        foreach (Row row in rows)
                        {
                            if (row.RowIndex != index)
                            {
                                GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理
                            }

                        }
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        /// <summary>
        /// 按照给定的流读取Excel转换成DataTable
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="page">页码</param>
        /// <param name="pagesize">页行数</param>
        /// <param name="sheetName">Excel的Sheet名；默认第一个Sheet</param>
        /// <param name="index">列头行；默认第一列</param>
        /// <returns></returns>
        /// OpenXml只支持Excel2007版后的.xlsx
        public static DataTable Read(Stream stream, int page = 0, int pagesize = 0, string sheetName = null, int index = 1)
        {
            try
            {
                DataTable dt = new DataTable();
              
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
                {
                    //打开Stream
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower());
                        if (sheets.Count() == 0)
                        {
                            throw new Exception("没有找到该Sheet");
                        }
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                    //获取Excel中共享数据
                    SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == index)//Excel的列名列
                        {
                            GetDataColumn(row, stringTable, ref dt);
                        }
                        else {
                            break;
                        }
                    }
                    if (page != 0 && pagesize != 0)
                    {
                        rows = rows.Skip((pagesize * (page - 1)) + 1).Take(pagesize);
                        foreach (Row row in rows)
                        {
                            if (row.RowIndex != index)
                            {
                                GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理
                            }
                        }
                    }
                    else
                    {

                        foreach (Row row in rows)
                        {
                            if (row.RowIndex != index)
                            {
                                GetDataRow(row, stringTable, ref dt, document.WorkbookPart);//Excel中行数大于列头的都作为数据列处理
                            }

                        }
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        /// <summary>
        /// 获取excel列头
        /// </summary>
        /// <returns></returns>
        /// OpenXml只支持Excel2007版后的.xlsx
        public static DataTable ReadHeaders(Stream stream, ref int count, string sheetName = null, int index = 1)
        {
            try
            {
                DataTable dt = new DataTable();

                using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
                {
                    //打开Stream
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower());
                        if (sheets.Count() == 0)
                        {
                            throw new Exception("没有找到该Sheet");
                        }
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                    //获取Excel中共享数据
                    SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                    foreach (Row row in rows)
                    {
                        if (row.RowIndex == index)//Excel的列名列
                        {
                            GetDataColumn(row, stringTable, ref dt);

                        }
                        else
                        {
                            break;
                        }
                    }
                    count = rows.Count() - 1;
                    return dt;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        /// <summary>
        /// Excel数据列总行数（不包括列头）
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static int GetExcelCount(string path, string sheetName = null, int index = 1)
        {
            try
            {
                DataTable dt = new DataTable();
                if (!path.ToLower().Trim().EndsWith(".xlsx"))
                {
                    throw new Exception("OpenXml组件只支持Office Excel2007版后的.xlsx格式文件");
                }
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, false))
                {
                    //打开Stream
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        sheets = sheets.Where(s => s.Name.ToString().ToLower() == sheetName.ToLower());
                        if (sheets.Count() == 0)
                        {
                            throw new Exception("没有找到该Sheet");
                        }
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                    //获取Excel中共享数据
                    SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                    IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                    return rows.Count() - 1;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 获取Excel原始列下标
        /// </summary>
        /// <param name="str">原始列名</param>
        /// <returns></returns>
        private static int GetIndexNumber(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return -1;
            }
            char[] chars = value.ToLower().ToCharArray();
            int index = 0;
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'a' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index - 1;
        }

        /// <summary>
        /// 构建DataTable的列
        /// </summary>
        /// <param name="row">OpenXML定义的Row对象</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">须要返回的DataTable对象</param>
        /// <returns></returns>
        private static void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCount = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                col = new DataColumn(cellVal);
                if (IsContainsColumn(dt, col.ColumnName))
                {
                    if (!columnCount.ContainsKey(col.ColumnName))
                        columnCount.Add(col.ColumnName, 0);
                    col.ColumnName = col.ColumnName + (columnCount[col.ColumnName]++);
                }
                dt.Columns.Add(col);
            }
        }
        /// <summary>
        /// 构建DataTable的每一行数据,并返回该Datatable
        /// </summary>
        /// <param name="row">OpenXML的行</param>
        /// <param name="stringTablePart"></param>
        /// <param name="dt">DataTable</param>
        private static void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt, WorkbookPart workbookPart)
        {
            //读取数据
            DataRow dr = dt.NewRow();
            int number = 0;
            foreach (Cell cell in row)
            {
                //获取Excel列头
                var column = Regex.Replace(cell.CellReference.Value, "[0-9]", "");
                //列头转换成下标
                var index = GetIndexNumber(column);
                if (index < 0)
                {
                    continue;
                }
                string cellVal = GetValue(cell, stringTable);
                if (!string.IsNullOrEmpty(cellVal))
                {
                    if (index < dt.Columns.Count) {
                        if (cell.DataType != null)
                        {
                            switch ((int)cell.DataType?.Value)
                            {
                                case (int)CellValues.Date:
                                    cellVal = Convert.ToDateTime(cellVal).ToString("yyyy-MM-dd HH:mm:ss");
                                    break;
                                default:
                                    break;
                            }
                        }
                        else {
                            #region NumberFormatId 含义
                            //0 = 'General';
                            //1 = '0';
                            //2 = '0.00';
                            //3 = '#,##0';
                            //4 = '#,##0.00';
                            //5 = '$#,##0;\\-$#,##0';
                            //6 = '$#,##0;[Red]\\-$#,##0';
                            //7 = '$#,##0.00;\\-$#,##0.00';
                            //8 = '$#,##0.00;[Red]\\-$#,##0.00';
                            //9 = '0%';
                            //10 = '0.00%';
                            //11 = '0.00E+00';
                            //12 = '# ?/?';
                            //13 = '# ??/??';
                            //14 = 'mm-dd-yy';
                            //15 = 'd-mmm-yy';
                            //16 = 'd-mmm';
                            //17 = 'mmm-yy';
                            //18 = 'h:mm AM/PM';
                            //19 = 'h:mm:ss AM/PM';
                            //20 = 'h:mm';
                            //21 = 'h:mm:ss';
                            //22 = 'm/d/yy h:mm';

                            //37 = '#,##0 ;(#,##0)';
                            //38 = '#,##0 ;[Red](#,##0)';
                            //39 = '#,##0.00;(#,##0.00)';
                            //40 = '#,##0.00;[Red](#,##0.00)';

                            //44 = '_("$"* #,##0.00_);_("$"* \\(#,##0.00\\);_("$"*"-"??_);_(@_)';
                            //45 = 'mm:ss';
                            //46 = '[h]:mm:ss';
                            //47 = 'mmss.0';
                            //48 = '##0.0E+0';
                            //49 = '@';

                            //27 = '[$-404]e/m/d';
                            //30 = 'm/d/yy';
                            //36 = '[$-404]e/m/d';
                            //50 = '[$-404]e/m/d';
                            //57 = '[$-404]e/m/d';

                            //59 = 't0';
                            //60 = 't0.00';
                            //61 = 't#,##0';
                            //62 = 't#,##0.00';
                            //67 = 't0%';
                            //68 = 't0.00%';
                            //69 = 't# ?/?';
                            //70 = 't# ??/??';
                            #endregion
                            //日期格式
                            if (workbookPart.WorkbookStylesPart != null)
                            {
                                var styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
                                if (styleSheet != null && cell.StyleIndex!=null)
                                {
                                    CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];
                                    int formatId = (int)cellFormat.NumberFormatId.Value;
                                    switch (formatId)
                                    {
                                        case 14:

                                        case 15:

                                        case 16:

                                        case 17:

                                        case 18:

                                        case 19:

                                        case 20:

                                        case 21:

                                        case 22:
                                        case 177:
                                            cellVal = DateTime.FromOADate(double.Parse(cellVal)).ToString("yyyy-MM-dd HH:mm:ss");
                                            break;
                                        default:
                                            break;
                                    }
                                }

                            }
                        }
                    }
                }

                dr[index] = cellVal;
                if (!string.IsNullOrEmpty(cellVal))
                {
                    number++;
                }

            }
            if (number != 0)
            {
                dt.Rows.Add(dr);
            }
        }
        /// <summary>
        /// 获取单位格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="stringTablePart"></param>
        /// <returns></returns>
        private static string GetValue(Cell cell, SharedStringTable stringTable)
        {
            //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if ((cell.DataType != null))
                {
                    if ((cell.DataType == CellValues.SharedString))
                    {
                        value = stringTable.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText;
                    }
                }
            }
            catch (Exception)
            {
                value = string.Empty;
            }
            return value;
        }
        /// <summary>
        /// 判断网格是否存在列
        /// </summary>
        /// <param name="dt">网格</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        private static bool IsContainsColumn(DataTable dt, string columnName)
        {
            if (dt == null || columnName == null)
            {
                return false;
            }
            return dt.Columns.Contains(columnName);
        }
        #endregion
        #region 导出
        /// <summary>
        /// 获取Excel原始Colum名称
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        static string GetLetter(int index)
        {
            if (index < 0)
            {
                throw new Exception($"参数{nameof(index)}异常");
            }
            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return String.Join(string.Empty, chars.ToArray());
        }
        /// <summary>
        /// 添加WorkSheet
        /// </summary>
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            //创建新的WorksheetPart
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();
            //在末尾追加一个Sheets
            Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = 1, Name = sheetName };
            //添加Sheet
            sheets.Append(sheet);
            workbookPart.Workbook.Save();
            return newWorksheetPart;
        }
        [Obsolete]
        private static Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
        {
            //iterate over all cells getting a max char value for each column
            Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
            var row = sheetData.Elements<Row>()?.FirstOrDefault();

            if (row != null)
            {
                var cells = row.Elements<Cell>().ToArray();
                //defalut width
                for (int i = 0; i < cells.Length; i++)
                {
                    maxColWidth.Add(i, 50);
                   
                }
            }
       

            return maxColWidth;
        }
        [Obsolete]
        private static Columns AutoSize(SheetData sheetData)
        {
            var maxColWidth = GetMaxCharacterWidth(sheetData);

            Columns columns = new Columns();
            //this is the width of my font - yours may be different
            double maxWidth = 7;
            foreach (var item in maxColWidth)
            {
                //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
                double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;

                //pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})
                double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth);

                //character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100
                double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100;

                Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
                columns.Append(col);
            }

            return columns;
        }
        /// <summary>
        /// 添加单元格样式
        /// </summary>
        /// <param name="workbookPart"></param>
        private static void InitializeStyleSheet(WorkbookPart workbookPart)
        {
            workbookPart.AddNewPart<WorkbookStylesPart>();

            workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet();
            Stylesheet stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;

            stylesheet.Fonts = new Fonts(new Font(new FontSize() { Val = 11D }, new Color() { Theme = 1U },
                                        new FontName() { Val = "Calibri" }, new FontFamily() { Val = 2 },
                                        new FontScheme() { Val = FontSchemeValues.Minor }))
            { Count = 1U };
            stylesheet.Fills = new Fills(new Fill(new PatternFill() { PatternType = PatternValues.None })) { Count = 2U };
            stylesheet.Borders = new Borders(new Border(new LeftBorder(),
                new RightBorder(), new TopBorder(),
                new BottomBorder(), new DiagonalBorder()))
            { Count = 1U };

            stylesheet.CellFormats = new CellFormats();
            stylesheet.CellFormats.Count = 2;
            CellFormat cf0 = stylesheet.CellFormats.AppendChild(new CellFormat());
            cf0.NumberFormatId = 49;
            cf0.FontId = 0;
            cf0.BorderId = 0;
            cf0.FillId = 0;
            CellFormat cf = stylesheet.CellFormats.AppendChild(new CellFormat());
            cf.Alignment = new Alignment();
            cf.ApplyAlignment = true;
            cf.NumberFormatId = 49;
            cf.FontId = 0;
            cf.BorderId = 0;
            cf.FillId = 0;
            cf.Alignment.WrapText = true;
            workbookPart.WorkbookStylesPart.Stylesheet.Save();
        }
        /// <summary>
        /// DataTable导出Excel到MemoryStream;
        ///Return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "test.xlsx");
        ///MVC模式下返回文件流结果，参数一为流，参数二为文件类型，参数三为文件名
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <returns></returns>
        public static MemoryStream ToExcelIntoMemoryStream(DataTable dtSource, string sheetName = "Sheet0")
        {
            try
            {
                var memoryStream = new MemoryStream();
                using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();
                    InitializeStyleSheet(workbookPart);

                    //创建新的SharedStringTablePart
                    SharedStringTablePart shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    // 添加一个WorkSheet
                    WorksheetPart worksheetPart = InsertWorksheet(workbookPart, sheetName);
           
                    // 添加SharedStringTable
                    shareStringPart.SharedStringTable = new SharedStringTable();
                    int rowIndex = 1;
                    int cellIndex = 0;
                    Worksheet worksheet = worksheetPart.Worksheet;
                    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                    //Excel表头列
                    SetSheetDataHeadRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex);
                    //Excel数据列
                    SetSheetDataRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex);
                    Columns columns = AutoSize(sheetData);
                    worksheet.Append(columns);
                    shareStringPart.SharedStringTable.Save();
                    worksheet.Save();
                    worksheetPart.Worksheet.Save();
                }
                memoryStream.Seek(0, SeekOrigin.Begin);
                return memoryStream;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 实体集合直接转为Excel 可配合XmlPropertyAttribute使用
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static MemoryStream ToExcelIntoMemoryStream<T>(List<T> list, string sheetName = "Sheet0")
        {
            try
            {
                var dtSource = ToDataTable(list);
                List<XmlPropertyAttribute> attrs = XmlPropertyAttribute.GetTypeAttributes<T>();
                SetTableProperty(dtSource, attrs);
                var memoryStream = new MemoryStream();
                using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();
                    InitializeStyleSheet(workbookPart);

                    //创建新的SharedStringTablePart
                    SharedStringTablePart shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    // 添加一个WorkSheet
                    WorksheetPart worksheetPart = InsertWorksheet(workbookPart, sheetName);

                    // 添加SharedStringTable
                    shareStringPart.SharedStringTable = new SharedStringTable();
                    int rowIndex = 1;
                    int cellIndex = 0;
                    Worksheet worksheet = worksheetPart.Worksheet;
                    SheetData sheetData = worksheet.GetFirstChild<SheetData>();

                    //Excel表头列
                    SetSheetDataHeadRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex);
                    //Excel数据列
                    SetSheetDataRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex);

                    Columns columns = GetColumnsProperty(sheetData, document.WorkbookPart.SharedStringTablePart.SharedStringTable, attrs);
                    worksheet.Append(columns);
                    shareStringPart.SharedStringTable.Save();
                    worksheet.Save();
                    worksheetPart.Worksheet.Save();
                }
                memoryStream.Seek(0, SeekOrigin.Begin);
                return memoryStream;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

 

        /// <summary>
        /// DataTable固定路径导出Excel，单个Sheet生成
        /// 指定路径
        /// </summary>
        /// <param name="excelFilePath">存储路径</param>
        /// <param name="table">数据</param>
        /// <param name="sheetName">Sheet名称</param>
        public static void ToExcelFixUrl(string excelFilePath, DataTable dtSource, string sheetName = "Sheet0")
        {
            try
            {
                using (var document = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();
                    //创建新的SharedStringTablePart
                    SharedStringTablePart shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
                    // 添加WorkSheet
                    WorksheetPart worksheetPart = InsertWorksheet(workbookPart, sheetName);
                    // 添加SharedStringTable
                    shareStringPart.SharedStringTable = new SharedStringTable();
                    int rowIndex = 1;
                    int cellIndex = 0;
                    Worksheet worksheet = worksheetPart.Worksheet;
                    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                    //Excel表头列
                    SetSheetDataHeadRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex);
                    //Excel数据列
                    SetSheetDataRow(dtSource, ref shareStringPart, ref sheetData, ref rowIndex, ref cellIndex);
                    shareStringPart.SharedStringTable.Save();
                    worksheet.Save();
                    worksheetPart.Worksheet.Save();
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 将DataTable的ColumnsName转换为Excel表头Row
        /// </summary>
        /// <param name="dtSource">数据</param>
        /// <param name="shareStringPart"></param>
        /// <param name="sheetData"></param>
        /// <param name="rowIndex">行下标</param>
        /// <param name="cellIndex">单元格下标/param>
        private static void SetSheetDataHeadRow(DataTable dtSource, ref SharedStringTablePart shareStringPart, ref SheetData sheetData, ref int rowIndex, ref int cellIndex)
        {
            {
                Row row = new Row() { RowIndex = Convert.ToUInt32(rowIndex) };
                sheetData.Append(row);
                for (int c = 0; c < dtSource.Columns.Count; c++)
                {

                    string cellReference = GetLetter(c) + rowIndex;
                    //SharedStringTable里，取数据也是根据SharedStringTable来取
                    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(Convert.ToString(dtSource.Columns[c]))));


                    Cell refCell = null;
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }
                    Cell newCell = new Cell() { CellReference = cellReference };
                    row.InsertBefore(newCell, refCell);

                    newCell.CellValue = new CellValue(cellIndex++.ToString());
                    newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                    if (c == dtSource.Columns.Count - 1)
                    {
                        rowIndex++;
                    }
                }

            }
        }
        /// <summary>
        /// 将DataTable的ColumnsData转换为数据Row
        /// </summary>
        /// <param name="dtSource">数据</param>
        /// <param name="shareStringPart"></param>
        /// <param name="sheetData"></param>
        /// <param name="rowIndex">行下标</param>
        /// <param name="cellIndex">单元格下标/param>
        private static void SetSheetDataRow(DataTable dtSource, ref SharedStringTablePart shareStringPart, ref SheetData sheetData, ref int rowIndex, ref int cellIndex)
        {
            {

                //Excel的数据列生成
                for (int r = 0; r < dtSource.Rows.Count; r++)
                {

                    //CellFormats
                    Row dataRow = new Row() { RowIndex = Convert.ToUInt32(rowIndex) };

                    sheetData.Append(dataRow);
                    for (int c = 0; c < dtSource.Columns.Count; c++)
                    {
                        //特殊字符串处理
                        string cellValue= Convert.ToString(dtSource.Rows[r][c])?.Replace("\0", " ");
                        string cellReference = GetLetter(c) + rowIndex;
                        shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(cellValue)));

                        Cell refCell = null;
                        foreach (Cell cell in dataRow.Elements<Cell>())
                        {
                            if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                            {
                                refCell = cell;
                                break;
                            }
                        }
                        Cell newCell = new Cell() { CellReference = cellReference, StyleIndex = 1, };
                        dataRow.InsertBefore(newCell, refCell);

                        newCell.CellValue = new CellValue(cellIndex++.ToString());
                        newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                    }
                    dataRow.Height = 7U;
                    rowIndex++;
                }
            }
        }
        /// <summary>
        /// 根据枚举重新定义table属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        public static void SetTableProperty(DataTable dt, List<XmlPropertyAttribute> attrs) {
            foreach (var attr in attrs)
            {
                if (attr.Sort > 0)
                {
                    attr.Sort = attr.Sort > (dt.Columns.Count - 1) ? (dt.Columns.Count - 1) : attr.Sort;
                    dt.Columns[attr.FieldName].SetOrdinal(attr.Sort);
                }
                if (!string.IsNullOrEmpty(attr.Disc))
                {
                    dt.Columns[attr.FieldName].ColumnName = attr.Disc;
                }
            }
        }
        /// <summary>
        /// 根据枚举设置单元格宽度
        /// </summary>
        /// <param name="sheetData"></param>
        /// <param name="stringTable"></param>
        /// <param name="attrs"></param>
        /// <returns></returns>
        private static Dictionary<int, int> GetPropertyWidth(SheetData sheetData, SharedStringTable stringTable,List<XmlPropertyAttribute> attrs)
        {
            //iterate over all cells getting a max char value for each column
            Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
            var firstRow = sheetData.Elements<Row>()?.FirstOrDefault();
            if (firstRow != null)
            {
               var cells= firstRow.Elements<Cell>().ToArray();
                for (int i = 0; i < cells.Length; i++)
                {
                    var cell = cells[i];

                    var cellValue = GetValue(cell, stringTable);
                    var cellTextLength = 50;
                    var attr = attrs.FirstOrDefault(t => t.Disc == cellValue);
                    if (attr != null)
                    {
                        cellTextLength = attr.Width > 0 ? attr.Width : cellTextLength;
                    }
                    maxColWidth.Add(i, cellTextLength);
                   
                }
            }

            return maxColWidth;
        }
        private static Columns GetColumnsProperty(SheetData sheetData, SharedStringTable sharedStringTable, List<XmlPropertyAttribute> attrs)
        {
            var maxColWidth = GetPropertyWidth(sheetData, sharedStringTable,attrs);

            Columns columns = new Columns();
            //this is the width of my font - yours may be different
            double maxWidth = 7;
            foreach (var item in maxColWidth)
            {
                //width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
                double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;

                //pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})
                double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth);

                //character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100
                double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100;

                Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
                columns.Append(col);
            }

            return columns;
        }
        #endregion
        /// <summary>
        /// list模型转换为DataTable
        /// </summary>
        /// <typeparam name="T">模型</typeparam>
        /// <param name="list">数据集合</param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(List<T> list)
        {
            if (list == null || list.Count <= 0)
            {
                throw new Exception("参数异常");
            }
            DataTable dt = new DataTable();
            if (list.Count <= 0 || list == null)
            {
                return dt;
            }
            //添加列名
            {
                var type = list.First().GetType();
                PropertyInfo[] PropertyList = type.GetProperties();
                foreach (PropertyInfo item in PropertyList)
                {
                    DataColumn dc = new DataColumn();
                    string name = item.Name;
                    dc.ColumnName = name;//反射类字段添加列名
                    dt.Columns.Add(dc);
                }

            }
            //添加数据列
            foreach (var model in list)
            {
                var type = model.GetType();
                PropertyInfo[] PropertyList = type.GetProperties();
                DataRow dr = dt.NewRow();
                foreach (PropertyInfo item in PropertyList)
                {
                    string name = item.Name;
                    string value = Convert.ToString(item.GetValue(model, null));
                    dr[name] = value;
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }


    }

    public class XmlPropertyAttribute : Attribute
    {
        /// <summary>
        /// 排序 最小值1
        /// </summary>
        public int Sort { get; set; }
        /// <summary>
        /// 单元格宽度 默认50
        /// </summary>
        public int Width { get; set; }
        /// <summary>
        /// 别名
        /// </summary>
        public string Disc { get; set; }
        public string FieldName { get; private set; }
        public XmlPropertyAttribute() { }
        public XmlPropertyAttribute(string fieldName)
        {
            FieldName = fieldName;
        }

        public static List<XmlPropertyAttribute> GetTypeAttributes<T>() {
            var type = typeof(T);
            PropertyInfo[] PropertyList = type.GetProperties();
            List<XmlPropertyAttribute> attrs = new List<XmlPropertyAttribute>();
            foreach (PropertyInfo item in PropertyList)
            {
                string name = item.Name;
                var xmlAttr = item.CustomAttributes.FirstOrDefault(t => t.AttributeType == typeof(XmlPropertyAttribute));
                if (xmlAttr != null)
                {
                    int sort = Convert.ToInt32(xmlAttr.NamedArguments.FirstOrDefault(t => t.MemberName == nameof(Sort)).TypedValue.Value);
                    int width = Convert.ToInt32(xmlAttr.NamedArguments.FirstOrDefault(t => t.MemberName == nameof(Width)).TypedValue.Value);
                    string disc = Convert.ToString(xmlAttr.NamedArguments.FirstOrDefault(t => t.MemberName == nameof(Disc)).TypedValue.Value);
                    attrs.Add(new XmlPropertyAttribute(name)
                    {
                        Sort = sort,
                        Disc = !string.IsNullOrWhiteSpace(disc) ? disc : name,
                        Width = width,
                    });

                }
            }
            return attrs;
        }

    }