XYY.Core/1.Command/XYY.Core.Standard/ExcelHelper/MSExcelHelper/MSExcelHelper.cs

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.AliYun;

namespace XYY.Core.Standard.ExcelHelper.MSExcelHelper
{

    /// <summary>
    /// 默认值配置
    /// </summary>
    public struct ExcelMappingConfig
    {
        /// <summary>
        /// 列最长宽度
        /// </summary>
        public int CoulmnMaxWidht => 50;
    }



    public class MSExcelHelper : IExcelHelper
    {
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd);   //得到这个句柄，具体作用是得到这块内存入口

            int k = 0;
            GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
            p.Kill();     //关闭进程k

        }

        IAliYunPostFileSerivce _aliYunPostFileSerivce;
        public MSExcelHelper(IAliYunPostFileSerivce aliYunPostFileSerivce)
        {
            _aliYunPostFileSerivce = aliYunPostFileSerivce;
        }

        public MSExcelHelper()
        {

        }

        public string OutData<Mapping, T>(List<T> data) where Mapping : MSExcelClassMapping<T>
        {
            string filename = OutLocalData<Mapping, T>(data);
            string url = _aliYunPostFileSerivce.GetPublicCDN(_aliYunPostFileSerivce.PostMd5File(filename, "temp\\" + Guid.NewGuid().ToString()));
            //清理临时文件
            System.IO.File.Delete(filename);
            return url;
        }

        public string OutData<Mapping, T>(List<T> data, string fileName) where Mapping : MSExcelClassMapping<T>
        {
            string filename = OutLocalData<Mapping, T>(data);
            string url = _aliYunPostFileSerivce.GetPublicCDN(_aliYunPostFileSerivce.PostMd5File(filename, fileName));
            //清理临时文件
            System.IO.File.Delete(filename);
            return url;
        }

        public int GetRowCount(Worksheet worksheet)
        {
            return worksheet.Cells.Find("*", System.Reflection.Missing.Value,
                                           System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                           Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
                                           false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
        }
        public List<T> LoadData<Mapping, T>(string fileName) where Mapping : MSExcelClassMapping<T>
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                Workbooks wbs = app.Workbooks; //获取工作薄
                _Workbook wb = wbs.Add(fileName);
                Microsoft.Office.Interop.Excel.Range range;

                List<T> list = new List<T>();
                var mapping = CreateT<Mapping>();
                List<string> sheetNames = new List<string>();
                foreach (Microsoft.Office.Interop.Excel.Worksheet item in wb.Sheets)
                {
                    if (item.Rows.Row > 0)
                    {
                        sheetNames.Add(item.Name);
                    }
                }
                var sheetsIndex = mapping.SheetIndexs(sheetNames);
                foreach (var si in sheetsIndex)
                {
                    Worksheet sh = (Worksheet)wb.Sheets[si];
                    range = sh.get_Range("A1", Missing.Value);
                    var rowCount = GetRowCount(sh);
                    var colCount = range.get_End(XlDirection.xlToRight).Column;


                    if (rowCount > 0)
                    {
                        range = range.get_Resize(rowCount, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        ///获取Map与列序号的映射关系
                        Dictionary<int, int> keyValuePairs = new Dictionary<int, int>();
                        for (int i = 0; i < mapping.Items.Count; i++)
                        {
                            for (int j = 0; j < colCount; j++)
                            {

                                if (mapping.Items[i].ColumnName == value22[1, j + 1].ToString())
                                {
                                    keyValuePairs.Add(i, j + 1);
                                    continue;
                                }
                            }
                        }
                        for (int iRow = 1; iRow < rowCount; iRow++)
                        {

                            var item = CreateT<T>();
                            for (int iCol = 0; iCol < mapping.Items.Count; iCol++)
                            {
                                try
                                {
                                    if (keyValuePairs.ContainsKey(iCol))
                                    {
                                        var value = value22[iRow + 1, keyValuePairs[iCol]];
                                        if (value != null)
                                        {
                                            var c = mapping.Items[iCol].PropertyInfo;
                                            if (c.PropertyType == typeof(int))
                                            {
                                                c.SetValue(item, Convert.ToInt32(Convert.ToDecimal(value.ToString())));
                                            }
                                            else if (c.PropertyType == typeof(DateTime) || c.PropertyType == typeof(DateTime?))
                                            {
                                                c.SetValue(item, DateTime.FromOADate((double)value));
                                            }
                                            else if (c.PropertyType == typeof(decimal))
                                            {
                                                c.SetValue(item, Convert.ToDecimal(value.ToString()));
                                            }
                                            else if (c.PropertyType == typeof(string))
                                            {
                                                c.SetValue(item, value.ToString());
                                            }
                                            else
                                            {
                                                c.SetValue(item, value);
                                            }
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine(mapping.Items[iCol].PropertyInfo.Name + ex.Message);
                                }
                            }
                            list.Add(item);
                        }
                    }
                }

                wb.Close();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                app.Quit();
            }
        }

        public List<T> LoadDataByUrl<Mapping, T>(string url) where Mapping : MSExcelClassMapping<T>
        {
            var client = new System.Net.WebClient();
            var data = client.DownloadData(url);
            return LoadDataByByte<Mapping, T>(data);
        }

        public List<T> LoadDataByByte<Mapping, T>(byte[] data) where Mapping : MSExcelClassMapping<T>
        {

            string file = getTempFilePath();
            System.IO.File.WriteAllBytes(file, data);
            var result = LoadData<Mapping, T>(file);
            System.IO.File.Delete(file);
            return result;
        }

        public List<T> LoadDataAsStream<Mapping, T>(Stream stream) where Mapping : MSExcelClassMapping<T>
        {
            byte[] data = new byte[stream.Length];
            stream.Read(data, 0, data.Length);
            return LoadDataByByte<Mapping, T>(data);

        }

        public string getTempFilePath()
        {
            string basePath = System.IO.Path.Combine(System.AppContext.BaseDirectory, "temp");
            if (!System.IO.Directory.Exists(basePath))
                System.IO.Directory.CreateDirectory(basePath);
            return System.IO.Path.Combine(basePath, Guid.NewGuid().ToString() + ".xlsx");
        }

        private Mapping CreateT<Mapping>()
        {
            var type = typeof(Mapping);
            var ass = type.Assembly;
            Mapping mapping = (Mapping)ass.CreateInstance(type.FullName);
            return mapping;
        }

        public byte[] OutDataAsByte<Mapping, T>(List<T> data) where Mapping : MSExcelClassMapping<T>
        {
            string file = OutLocalData<Mapping, T>(data);
            byte[] bytes = System.IO.File.ReadAllBytes(file);
            System.IO.File.Delete(file);
            return bytes;
        }

        public byte[] OutDataAsByte<T>(List<T> data, List<MSExcelClassMapping<T>> list)
        {
            string file = OutLocalData<T>(data, list);
            byte[] bytes = System.IO.File.ReadAllBytes(file);
            System.IO.File.Delete(file);
            return bytes;
        }

        public byte[] OutDataAsByte<T, Mapping>(List<T> data, Mapping mapping) where Mapping : MSExcelClassMapping<T>
        {
            string file = OutLocalData<T, Mapping>(data, mapping);
            byte[] bytes = System.IO.File.ReadAllBytes(file);
            System.IO.File.Delete(file);
            return bytes;
        }
        public byte[] OutLocalTableDataByte(System.Data.DataTable data)
        {
            string file = OutLocalTableData(data);
            byte[] bytes = System.IO.File.ReadAllBytes(file);
            System.IO.File.Delete(file);
            return bytes;
        }

        public string OutLocalTableData(System.Data.DataTable data)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                string filename = getTempFilePath();
                //System.IO.File.Create(filename);
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度

                Workbooks wbs = app.Workbooks; //获取工作薄

                _Workbook wb = wbs.Add(Missing.Value);

                Microsoft.Office.Interop.Excel.Worksheet Sheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
                OutLocalTableData(data, Sheet);
                app.Columns.AutoFit();

                wb.SaveCopyAs(filename);
                wb.Close(); //关闭工作薄
                            //wb._SaveAs(filename); //保存
                return filename;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + " ");
            }
            finally
            {
                app.Quit(); //关闭EXCEL
            }
        }

        public void OutLocalTableData(System.Data.DataTable data, Microsoft.Office.Interop.Excel.Worksheet Sheet, string Start = "A1",
            System.Drawing.Color? _color = null, bool hasHeader = true)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            //string filename = getTempFilePath();
            ////System.IO.File.Create(filename);
            //app.Visible = false; //不显示EXCEL
            //app.DisplayAlerts = false; //不提示信息
            //app.ScreenUpdating = false; //停止更新屏幕，加快速度

            //Workbooks wbs = app.Workbooks; //获取工作薄

            //_Workbook wb = wbs.Add(Missing.Value);

            System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"([A-Z]?)(\d+)");
            //Microsoft.Office.Interop.Excel.Worksheet Sheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
            //Sheet.Activate();
            //Sheet.Application.ActiveWindow.SplitRow = 1;
            //Sheet.Application.ActiveWindow.FreezePanes = true;
            int startQty = 1;
            if (regex.IsMatch(Start))
            {
                startQty = (int.Parse(regex.Match(Start).Groups[2].Value)) + 1;
            }
            else
            {
                Start = "A1";
            }
            int colCount, rowCount;
            colCount = data.Columns.Count;
            rowCount = data.Rows.Count;
            //写入标题行
            Microsoft.Office.Interop.Excel.Range range;
            if (hasHeader)
            {


                range = Sheet.get_Range(Start, Missing.Value);
                range = range.get_Resize(1, colCount);
                if (_color == null)
                    range.Interior.Color = System.Drawing.Color.FromArgb(204, 255, 255);
                else
                    range.Interior.Color = _color;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Borders.LineStyle = XlLineStyle.xlContinuous;
                range.RowHeight = 30;
                object[,] headerData = new object[1, colCount];

                for (int iCol = 0; iCol < colCount; iCol++)
                {
                    headerData[0, iCol] = data.Columns[iCol].ColumnName;
                    //var column=   Sheet.Columns[iCol] as 
                    string ColumnFormattext = ExcelFormattextHelper.GetColumnFormattext(data.Columns[iCol].DataType);

                    if (!string.IsNullOrEmpty(ColumnFormattext))
                    {
                        var r2 = Sheet.Range[Sheet.Cells[startQty, iCol + 1], Sheet.Cells[data.Rows.Count + startQty, iCol + 1]];
                        r2.NumberFormat = ColumnFormattext;
                    }
                }
                range.set_Value(Missing.Value, headerData);
            }
            if (rowCount > 0)
            {
                if (regex.IsMatch(Start))
                {
                    Start = regex.Match(Start).Groups[1].Value + (int.Parse(regex.Match(Start).Groups[2].Value) + 1);
                }
                else
                {
                    Start = "A2";
                }
                range = Sheet.get_Range(Start, Missing.Value);
                range = range.get_Resize(rowCount, colCount);
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.RowHeight = 20;
                object[,] cellData = new object[rowCount, colCount];
                for (int iRow = 0; iRow < rowCount; iRow++)
                {
                    for (int iCol = 0; iCol < colCount; iCol++)
                    {
                        cellData[iRow, iCol] = data.Rows[iRow][iCol];
                    }
                }
                range.set_Value(Missing.Value, cellData);
                //设置单元格格式 
            }
        }


        public void SetSheet<T, Mapping>(List<T> data, MSExcelClassMapping<T> mapping, Microsoft.Office.Interop.Excel.Worksheet Sheet, List<int> repeat = null) where Mapping : MSExcelClassMapping<T>
        {

            int colCount, rowCount;
            colCount = mapping.Items.Count;
            rowCount = data.Count;

            //写入标题行
            Microsoft.Office.Interop.Excel.Range range;
            range = Sheet.get_Range("A1", Missing.Value);
            range = range.get_Resize(1, colCount);
            range.Interior.Color = System.Drawing.Color.FromArgb(191, 191, 191);
            range.Font.Bold = true;
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
            range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range.RowHeight = 30;
            object[,] headerData = new object[1, colCount];
            for (int iCol = 0; iCol < colCount; iCol++)
            {
                if (!string.IsNullOrEmpty(mapping.Items[iCol].ColumnName))
                {
                    headerData[0, iCol] = mapping.Items[iCol].ColumnName;
                }
                else
                {
                    headerData[0, iCol] = mapping.Items[iCol].PropertyInfo.Name;
                }
                if (!string.IsNullOrEmpty(mapping.Items[iCol].ColumnAnnotation))
                {
                    //批注
                    var r = Sheet.Range[Sheet.Cells[1, iCol + 1], Sheet.Cells[1, iCol + 1]];
                    if (r.Comment != null)
                    {
                        r.Comment.Delete();
                    }
                    r.AddComment(mapping.Items[iCol].ColumnAnnotation);
                }
                //var column=   Sheet.Columns[iCol] as 


                if (!string.IsNullOrEmpty(mapping.Items[iCol].ColumnFormattext))
                {
                    var r2 = Sheet.Range[Sheet.Cells[2, iCol + 1], Sheet.Cells[data.Count + 1, iCol + 1]];
                    r2.NumberFormat = mapping.Items[iCol].ColumnFormattext;
                }
            }
            range.set_Value(Missing.Value, headerData);
            decimal sum = 0;
            int sumIndex = mapping.RemarkIndex();
            if (rowCount > 0)
            {
                range = Sheet.get_Range("A2", Missing.Value);
                range = range.get_Resize(rowCount, colCount);
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.RowHeight = 20;

                object[,] cellData = new object[rowCount, colCount];
                for (int iRow = 0; iRow < rowCount; iRow++)
                {
                    for (int iCol = 0; iCol < colCount; iCol++)
                    {
                        var item = data[iRow];
                        cellData[iRow, iCol] = mapping.Items[iCol].PropertyInfo.GetValue(item, null);
                    }
                    if (sumIndex > 0)
                    {
                        var item = data[iRow];
                        sum += (decimal)mapping.Items[sumIndex + 1].PropertyInfo.GetValue(item, null);
                    }
                }
                range.set_Value(Missing.Value, cellData);
                //设置单元格格式

                if (repeat != null)
                {
                    foreach (var i in repeat)
                    {
                        var ranges = (Microsoft.Office.Interop.Excel.Range)Sheet.Rows[2+i, Missing.Value];
                        ranges.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0);
                    }
                }
                

                if (sumIndex > 0)
                {
                    //汇总
                    int rows = data.Count + 1;
                    var rangeS = Sheet.get_Range("A" + (rows + 1), Missing.Value);
                    rangeS = rangeS.get_Resize(1, colCount);
                    rangeS.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    rangeS.RowHeight = 20;

                    object[,] sumData = new object[1, colCount];
                    sumData[0, sumIndex] = "合计";
                    sumData[0, sumIndex + 1] = sum;
                    rangeS.set_Value(Missing.Value, sumData);
                }
            }
        }


        public string OutLocalData<T>(List<T> data, List<MSExcelClassMapping<T>> mapping)
        {
            string filename = getTempFilePath();
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                //System.IO.File.Create(filename);
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                app.DefaultSaveFormat = XlFileFormat.xlWorkbookDefault;
                Workbooks wbs = app.Workbooks; //获取工作薄

                _Workbook wb = wbs.Add(Missing.Value);

                for (int i = 1; i <= mapping.Count; i++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet Sheet;
                    if (i > 1)
                    {

                        Sheet = (Worksheet)wb.Sheets.Add(Type.Missing, wb.ActiveSheet, 1, XlSheetType.xlWorksheet);
                    }
                    else
                    {
                        Sheet = (Worksheet)wb.Sheets[i];
                    }

                    SetSheet<T, MSExcelClassMapping<T>>(data, mapping[i - 1], Sheet);
                }
                //Sheet.Activate();
                //Sheet.Application.ActiveWindow.SplitRow = 1;
                //Sheet.Application.ActiveWindow.FreezePanes = true;

                app.Columns.AutoFit();

                wb.SaveCopyAs(filename);
                wb.Close(); //关闭工作薄
                            //wb._SaveAs(filename); //保存
                return filename;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + " " + " filename" + filename + " " + ex.StackTrace);
            }
            finally
            {
                app.Quit(); //关闭EXCEL
            }
        }


        public string OutLocalData<T, Mapping>(List<T> data, Mapping mapping) where Mapping : MSExcelClassMapping<T>
        {
            return OutLocalData(data, new List<MSExcelClassMapping<T>> { mapping });
        }

        /// <summary>
        /// 导出数据并返回本地链接
        /// </summary>
        /// <typeparam name="Mapping"></typeparam>
        /// <param name="data"></param>
        /// <param name="Sheet"></param>
        /// <returns></returns>
        public string OutLocalData<Mapping, T>(List<T> data) where Mapping : MSExcelClassMapping<T>
        {
            var mapping = CreateT<Mapping>();
            return OutLocalData(data, mapping);

        }

    }
}