using Admin.NET.Core.Helper.ExcelHelper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Drawing.Imaging;
using ICell = NPOI.SS.UserModel.ICell;

namespace PandaWork.Infrastructure;

/// <summary>
/// ExcelHelper导入
/// </summary>
public class ExcelOperation
{
    /// <summary>
    /// 以时间命名
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>
    public static string GetFileName(string name)
    {
        string FileName = $"{name}{DateTime.Now.ToString("yyyyMMddhhmmss")}.xls";
        return FileName;
    }
    /// <summary>
    /// 以时间命名不带后缀名
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>
    public static string GetFileNameEmpty(string name)
    {
        string FileName = $"{name}{DateTime.Now.ToString("yyyyMMddhhmmss")}";
        return FileName;
    }
    /// <summary>
    /// 导出 返回具体的路径文件
    /// </summary>
    /// <param name="dtDictionary">支持多个sheet导入</param>
    /// <param name="FileName"> xxx.xlsx</param>
    /// <param name="ProjectPath">路径可为null</param>
    /// <param name="ExportPath"> 默认  "DownloadExcel\\"</param>
    /// <returns></returns>
    public static string ExportExcel(Dictionary<string, DataTable> dtDictionary, string FileName, string ProjectPath = "", string ExportPath = "")
    {
        if (dtDictionary is null || dtDictionary.Count == 0) return null;
        if (string.IsNullOrWhiteSpace(FileName)) FileName = Guid.NewGuid().ToString() + ".xlsx";
        if (string.IsNullOrWhiteSpace(ProjectPath)) ProjectPath = ExcelOperation.ProjectPath();
        if (string.IsNullOrWhiteSpace(ExportPath)) ExportPath = "DownloadExcel\\";

        Excel excel = new Excel();
        foreach (var dt in dtDictionary)
        {
            if (dt.Value != null && dt.Value.Rows.Count > 0)
            {
                excel.CreateSheet(dt.Value.Columns, dt.Key);
                foreach (DataRow row in dt.Value.Rows)
                {
                    excel.SetRowValue(row);
                }
            }
        }
        string filePath = ProjectPath + ExportPath;
        if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
        filePath = filePath + FileName;
        excel.SaveAs(filePath);
        // return "\\" + ExportPath + FileName;
        return filePath;
    }

    /// <summary>
    /// 导出 返回个steam流
    /// </summary>
    /// <param name="dtDictionary">支持多个sheet导入</param>
    /// <param name="FileName"> xxx.xlsx</param>
    /// <param name="ProjectPath">路径可为null</param>
    /// <param name="ExportPath"> 默认  "DownloadExcel\\"</param>
    /// <returns></returns>
    public static Stream ExportExcel(Dictionary<string, DataTable> dtDictionary)
    {
        if (dtDictionary is null || dtDictionary.Count == 0) return null;
        //  if (string.IsNullOrWhiteSpace(FileName)) FileName = Guid.NewGuid().ToString() + ".xlsx";
        //if (string.IsNullOrWhiteSpace(ProjectPath)) ProjectPath = ExcelOperation.ProjectPath();
        //if (string.IsNullOrWhiteSpace(ExportPath)) ExportPath = "DownloadExcel\\";

        Excel excel = new Excel();
        foreach (var dt in dtDictionary)
        {
            if (dt.Value != null && dt.Value.Rows.Count > 0)
            {
                excel.CreateSheet(dt.Value.Columns, dt.Key);
                foreach (DataRow row in dt.Value.Rows)
                {
                    excel.SetRowValue(row);
                }
            }
        }
        MemoryStream memoryStream = excel.SaveAsMemoryStream();
        return memoryStream;
    }
    /// <summary>
    /// 获取当前项目的目录
    /// </summary>
    /// <returns></returns>
    public static string ProjectPath()
    {
        string Paths = System.AppDomain.CurrentDomain.BaseDirectory;
        return Paths;
    }

    /// <summary>
    /// 去除空行
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    private static DataTable RemoveEmpty(DataTable dt)
    {
        List<DataRow> removelist = new List<DataRow>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            bool rowdataisnull = true;
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                {
                    rowdataisnull = false;
                }
            }
            if (rowdataisnull)
            {
                removelist.Add(dt.Rows[i]);
            }
        }
        for (int i = 0; i < removelist.Count; i++)
        {
            dt.Rows.Remove(removelist[i]);
        }
        return dt;
    }

    /// <summary>
    /// 将excel导入到datatable
    /// </summary>
    /// <param name="filePath">excel路径</param>
    /// <param name="isColumnName">第一行是否是列名</param>
    /// <returns>返回datatable</returns>
    public static System.Data.DataTable ExcelToDataTable(string filePath, bool isColumnName)
    {
        System.Data.DataTable dataTable = null;
        FileStream fs = null;
        DataColumn column = null;
        DataRow dataRow = null;
        IWorkbook workbook = null;
        ISheet sheet = null;
        IRow row = null;
        ICell cell = null;
        int startRow = 0;
        try
        {
            using (fs = File.OpenRead(filePath))
            {

                if (fs == null)
                {
                    throw Oops.Oh("请上传事件文件");
                }
                long length = fs.Length;
                if (length > 1024 * 1024 * 100) //200M
                {
                    throw Oops.Oh("上传文件不能超过100M");
                }
                fs.Position = 0;
                // 2007版本
                if (filePath.ToLower().IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                // 2003版本
                else if (filePath.ToLower().IndexOf(".xls") > 0)
                    // workbook = new HSSFWorkbook(fs);
                    workbook = new XSSFWorkbook(fs);
                else
                {
                    throw Oops.Oh("请上传事件文件");
                }

                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
                    dataTable = new System.Data.DataTable();
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;//总行数
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(0);//第一行
                            int cellCount = firstRow.LastCellNum;//列数

                            //构建datatable的列
                            if (isColumnName)
                            {
                                startRow = 1;//如果第一行是列名，则从第二行开始读取
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    cell = firstRow.GetCell(i);
                                    if (cell != null)
                                    {
                                        if (cell.StringCellValue != null)
                                        {
                                            column = new DataColumn(cell.StringCellValue);
                                            dataTable.Columns.Add(column);
                                        }
                                    }
                                }
                            }
                            else
                            {
                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    column = new DataColumn("column" + (i + 1));
                                    dataTable.Columns.Add(column);
                                }
                            }

                            //填充行
                            for (int i = startRow; i <= rowCount; ++i)
                            {
                                row = sheet.GetRow(i);
                                if (row == null) continue;

                                dataRow = dataTable.NewRow();
                                for (int j = row.FirstCellNum; j < cellCount; ++j)
                                {
                                    cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        dataRow[j] = "";
                                    }
                                    else
                                    {
                                        //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                        switch (cell.CellType)
                                        {
                                            case CellType.Blank:
                                                dataRow[j] = "";
                                                break;

                                            case CellType.Numeric:
                                                short format = cell.CellStyle.DataFormat;
                                                //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                if (format == 14 || format == 31 || format == 57 || format == 58)
                                                {
                                                    if (DateUtil.IsCellDateFormatted(cell))
                                                    {
                                                        dataRow[j] = Convert.ToDateTime(cell.DateCellValue).Date.ToString();
                                                    }
                                                }
                                                else
                                                {
                                                    dataRow[j] = cell.NumericCellValue;
                                                }

                                                break;

                                            case CellType.String:
                                                dataRow[j] = cell.StringCellValue;
                                                break;
                                        }
                                    }
                                    //为创建人赋值
                                    // dataRow[cellCount] = userad;
                                }
                                //去除空白行数据
                                bool rowdataisnull = true;
                                for (int k = row.FirstCellNum; k < cellCount - 1; ++k)
                                {
                                    if (!string.IsNullOrEmpty(dataRow[k] as string))
                                    {
                                        rowdataisnull = false;
                                    }
                                }
                                if (!rowdataisnull)
                                {
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
            }
            return dataTable;
        }
        catch (Exception ex)
        {
            if (fs != null)
            {
                fs.Close();
            }
            throw Oops.Oh("错误：" + ex.Message);
        }
    }

    /// <summary>
    /// 以文件流IFormFile方式导入到DataTable
    /// </summary>
    /// <param name="file"></param>
    /// <returns></returns>
    public static DataTable TaskFileImport(IFormFile file)
    {
        string[] fileExcels = ".xls,.xlsx".Split(',');
        string fileType = "";

        if (file == null)
        {
            throw Oops.Oh("请上传事件文件");
        }
        var fileExtension = Path.GetExtension(file.FileName);
        if (fileExtension == null)
        {
            throw Oops.Oh("文件无后缀信息");
        }
        long length = file.Length;
        if (length > 1024 * 1024 * 100) //200M
        {
            throw Oops.Oh("上传文件不能超过100M");
        }
        if (!fileExcels.Contains(fileExtension))
        {
            throw Oops.Oh("上传文件只支持.xls,.xlsx");
        }

        string filePath = ExcelOperation.ProjectPath() + "DownloadExcel\\";
        if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
        var saveName = filePath + $"{DateTime.Now.ToString("yyyyMMddhhmmss")}" + (file.FileName);
        using (FileStream fs = File.Create(saveName))
        {
            file.CopyTo(fs);
        }
        var datatel = ExcelToDataTable(saveName, true);
        return datatel;
    }

    /// <summary>
    /// 将excel导入到datatable，指定sheetname
    /// </summary>
    /// <param name="filePath">excel路径</param>
    /// <param name="isColumnName">第一行是否是列名</param>
    /// <param name="sheetName">Excel的sheet名</param>
    /// <returns>返回datatable</returns>
    public static List<DataTable> ExcelToDataTableList(string filePath, bool isColumnName, string[] sheetName)
    {
        List<DataTable> dataTableList = null;
        FileStream fs = null;
        DataColumn column = null;
        DataRow dataRow = null;
        IWorkbook workbook = null;
        ISheet sheet = null;
        IRow row = null;
        ICell cell = null;
        int startRow = 0;
        try
        {
            using (fs = File.OpenRead(filePath))
            {
                // 2007版本
                if (filePath.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                // 2003版本
                else if (filePath.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(fs);

                if (workbook != null && sheetName != null && sheetName.Length > 0)
                {
                    dataTableList = new List<DataTable>();
                    //string[] sheetName = { "工单", "工单材料", "杆件", "杆件工时", "配料单", "配料单详情", "物料主数据", "工艺", "工序", "工艺工序", "检验项" };
                    for (int w = 0; w < sheetName.Length; w++)
                    //for (int w = 0; w < workbook.NumberOfSheets; w++)
                    {
                        //sheet = workbook.GetSheetAt(w);//循环读取每个sheet(根据下标获取)
                        sheet = workbook.GetSheet(sheetName[w]);//循环读取每个sheet(根据名称获取)
                        DataTable dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                #region 构建datatable的列
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }
                                #endregion

                                #region 填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    bool IsNull = true;//是否是空行
                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            object value = null;
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    value = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176)
                                                        value = cell.DateCellValue;
                                                    else
                                                        value = Math.Round(cell.NumericCellValue, 2);//2位小数
                                                    break;
                                                case CellType.String:
                                                    value = cell.StringCellValue;
                                                    break;
                                                case CellType.Formula://公式
                                                    row.GetCell(j).SetCellType(CellType.String);//先改变公式为字符
                                                    value = cell.StringCellValue;
                                                    break;
                                                default:
                                                    value = cell.StringCellValue;
                                                    break;
                                            }

                                            if (!string.IsNullOrWhiteSpace(value.ToString()))
                                            {
                                                IsNull = false;
                                                dataRow[j] = value;
                                            }
                                        }
                                    }
                                    if (!IsNull)
                                        dataTable.Rows.Add(dataRow);
                                }
                                #endregion
                            }
                            dataTableList.Add(dataTable);
                        }
                    }
                }
            }
            return dataTableList;
        }
        catch (Exception ex)
        {
            if (fs != null)
            {
                fs.Close();
            }
            return null;
        }
    }

    /// <summary>
    /// 格式化关键字集合
    /// </summary>
    /// <typeparam name="T">泛型对象</typeparam>
    /// <param name="t">关键字集对象</param>
    /// <returns></returns>
    public static Dictionary<string, string> getProperties<T>(T t)
    {
        Dictionary<string, string> keywords = new Dictionary<string, string>();
        if (t == null)
        {
            return keywords;
        }
        System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

        if (properties.Length <= 0)
        {
            return keywords;
        }
        foreach (System.Reflection.PropertyInfo item in properties)
        {
            string name = "{$" + item.Name + "}";
            object value = item.GetValue(t, null);
            if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
            {
                keywords.Add(name, value.ToString());
            }
            else
            {
                getProperties(value);
            }
        }
        return keywords;
    }

    /// <summary>
    /// NPOI使用ShiftRows向excel插入行，并复制原有样式
    /// </summary>
    /// <param name="SavePath">模板文件，包含物理路径</param>
    /// <param name="list">数据集合</param>
    /// <param name="startRow">开始插入行索引</param>
    /// <param name="func">循环赋值的方法</param>
    public static void ShiftRows<T>(string SavePath, List<T> list, int startRow, Func<IRow, int, bool> func)//开始插入行索引
    {
        //创建Excel文件的对象
        FileStream fs = new FileStream(SavePath, FileMode.Open, FileAccess.ReadWrite);
        XSSFWorkbook workbook = new XSSFWorkbook(fs);
        fs.Close();
        ISheet sheet = workbook.GetSheetAt(0);
        if (list != null && list.Count > 1)
        {
            //插入行
            sheet.ShiftRows(startRow, sheet.LastRowNum, list.Count - 1, true, false);
            var rowSource = sheet.GetRow(startRow - 1);
            var rowStyle = rowSource.RowStyle;//获取当前行样式
            for (int i = startRow; i < startRow + list.Count - 1; i++)
            {
                var rowInsert = sheet.CreateRow(i);
                if (rowStyle != null) rowInsert.RowStyle = rowStyle;
                rowInsert.Height = rowSource.Height;
                for (int col = 0; col < rowSource.LastCellNum; col++)
                {
                    var cellsource = rowSource.GetCell(col);
                    var cellInsert = rowInsert.CreateCell(col);
                    var cellStyle = cellsource.CellStyle;
                    //设置单元格样式
                    if (cellStyle != null)
                        cellInsert.CellStyle = cellsource.CellStyle;
                }
            }
        }
        //绑定数据
        for (int j = startRow; j < startRow + list.Count; j++)
        {
            //单元格赋值等其他代码
            IRow r = sheet.GetRow(j);
            bool ls = func(r, j);
            // 标准写法 r.Cells[0].SetCellValue(j + 1);
        }
        FileStream success = new FileStream(SavePath, FileMode.Create);
        workbook.Write(success);
        success.Close();
        workbook.Close();
    }

    /// <summary>
    /// 数据替换
    /// </summary>
    /// <typeparam name="T">实体  替换默认为 {Name} </typeparam>
    /// <param name="entity"></param>
    /// <param name="FromSourcePath"></param>
    /// <param name="ToSourcePath"></param>
    /// <returns></returns>
    public static string ExcelReplace<T>(T entity, string FromSourcePath, string ToSourcePath)
    {
        FileStream fs = new FileStream(FromSourcePath, FileMode.Open, FileAccess.Read);
        IWorkbook workbook = WorkbookFactory.Create(fs);
        ISheet sheet = workbook.GetSheetAt(0);
        IRow firstRow = sheet.GetRow(0);
        int cellCount = firstRow.LastCellNum;
        int rowCount = sheet.LastRowNum;
        for (int i = 0; i < rowCount; i++)
        {
            IRow row = sheet.GetRow(i);
            for (int j = 0; j < cellCount; j++)
            {
                NPOI.SS.UserModel.ICell cell = row.GetCell(j);
                ReplaceExcelKey<T>(entity, cell);
            }
        }
        byte[] buffer = new byte[1024 * 5];
        if (File.Exists(ToSourcePath)) File.Delete(ToSourcePath);
        var file = new FileStream(ToSourcePath, FileMode.Create);
        workbook.Write(file);
        file.Close();
        return ToSourcePath;
    }

    private static void ReplaceExcelKey<T>(T etity, NPOI.SS.UserModel.ICell cell)
    {
        Type entityType = typeof(T);
        PropertyInfo[] properties = entityType.GetProperties();
        if (cell != null)
        {
            var text = "";
            if (cell.CellType == CellType.String)//这⾥根据不同的类型进⾏不同的处理
            {
                text = cell.ToString();
            }
            if (text != "")
            {
                foreach (var p in properties)
                {
                    string propteryName = "{" + p.Name + "}";
                    object value = p.GetValue(etity);
                    if (value == null)
                    {
                        value = "";
                    }
                    if (text.Contains(propteryName))
                    {
                        var v = value.ToString();
                        text = text.Replace(propteryName, v);
                    }
                    cell.SetCellValue(text);
                }
            }

        }
    }

    ///// <summary>
    ///// excel转image
    ///// </summary>
    ///// <param name="filename"></param>
    ///// <param name="imagepath"></param>
    //public static string ChangeExcel2Image(string filename, string imagepath)
    //{
    //    string filePath = ExcelOperation.ProjectPath() + "DownloadExcel\\";
    //    if (!System.IO.Directory.Exists(filePath)) Directory.CreateDirectory(filePath);
    //    var saveName = filePath + $"{DateTime.Now.ToString("yyyyMMddhhmmss")}" + imagepath;
    //    Workbook workbook = new Workbook();
    //    workbook.LoadFromFile(filename);
    //    Worksheet sheet = workbook.Worksheets[0];
    //    //sheet.SaveToImage(imagepath); //图片后缀.bmp ,imagepath自己设置
    //    //                              //加载生成图表后的Excel文档
    //    //workbook.LoadFromFile("ColumnChart.xlsx");
    //    sheet.ToImage(0, 0, 0, 0).Save(saveName, ImageFormat.Jpeg);
    //    return saveName;
    //}

    public static string ExcelRepleImages(string TagetPath, string imgpath)
    {
        string fileExtensionName = Path.GetExtension(TagetPath);
        if (fileExtensionName.ToLower() == ".xlsx")
        {
            return InsertImageToXLSXExcel(TagetPath, imgpath);
        }
        if (fileExtensionName.ToLower() == ".xls")
        {
            return InsertImageToXLSExcel(TagetPath, imgpath);
        }
        return "";
    }
    /// <summary>
    /// .xlsx后缀的Excel文件添加图片
    /// </summary>
    /// <param name="excelPath"></param>
    /// <param name="imgPath"></param>
    private static string InsertImageToXLSXExcel(string excelPath, string imgPath)
    {
        try
        {
            using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
            {
                //创建工作簿
                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
                //获取第一个工作表（下标从0起）
                XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
                //获取指定图片的字节流
                byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
                //将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
                //图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
                //同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
                int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.PNG);
                //创建画布
                XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                //设置图片坐标与大小
                //函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
                //坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
                //坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
                //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
                //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
                //注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 20, 15, 0, 2, 2, 6);
                //正式在指定位置插入图片
                XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                //创建一个新的Excel文件流，可以和原文件名不一样，
                //如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
                FileStream file = new FileStream("E:/图片3333333333.xlsx", FileMode.Create);
                //将已插入图片的Excel流写入新创建的Excel中
                xssfworkbook.Write(file);
                file.Close();
                //关闭工作簿
                xssfworkbook.Close();
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return excelPath;
    }

    /// <summary>
    /// .xls后缀的Excel文件添加图片
    /// </summary>
    /// <param name="TagetPath"></param>
    /// <param name="imgpath"></param>
    private static string InsertImageToXLSExcel(string TagetPath, string imgpath)
    {
        FileStream file = new FileStream(TagetPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
        IWorkbook hssfworkbook = new HSSFWorkbook(file);
        HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("领料单");

        System.Drawing.Image image = System.Drawing.Image.FromFile(imgpath);
        MemoryStream ms = new MemoryStream();
        image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
        HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
        //设置图片坐标与大小
        //函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
        //坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
        //坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
        //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
        //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
        //注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 20, 15, 0, 2, 2, 6);
        anchor.AnchorType = AnchorType.MoveDontResize;
        int index = hssfworkbook.AddPicture(ms.ToArray(), PictureType.PNG);
        HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, index);

        int idex1 = hssfworkbook.GetSheetIndex("领料单");
        hssfworkbook.SetSheetName(idex1, "领料单");

        FileStream fs2 = new FileStream("E:/图片操2222222作.xls", FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
        hssfworkbook.Write(fs2);
        fs2.Close();
        image.Dispose();
        file.Close();
        return TagetPath;
    }
}