mes-client/UAS_MES_ZKLH/FunctionCode/Query/Query_SOP.cs

using System;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Collections.Generic;
using System.Threading;
using System.Data;
using System.Drawing.Imaging;
using System.Drawing;
using UAS_MES_NEW.Entity;
using UAS_MES_NEW.PublicMethod;
using Aspose.Cells;
using UAS_MES_NEW.DataOperate;
using UAS_MES_NEW.PublicForm;
using Aspose.Cells.Drawing;
using Aspose.Cells.Rendering;
using System.Web.Script.Serialization;
using Seagull.BarTender.Print;

namespace UAS_MES_NEW.Query
{
    public partial class Query_SOP : Form
    {
        DataHelper dh;

        string usercode;
        string sourcecode;
        DataTable Dbfind;

        public Query_SOP()
        {
            usercode = "";
            sourcecode = "";
            InitializeComponent();
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            CheckForIllegalCrossThreadCalls = false;

            ps_prodcode.TableName = "productsop";
            ps_prodcode.SelectField = "ps_code # 流水号,ps_prodcode # SOP编号";
            ps_prodcode.FormName = Name;
            ps_prodcode.DBTitle = "SOP查询";
            ps_prodcode.SetValueField = new string[] { "ps_code", "ps_prodcode" };
            ps_prodcode.Condition = "";
            ps_prodcode.DbChange += nr_rule_DBChange;

            pr_code.TableName = "product";
            pr_code.SelectField = "pr_code # 产品编号,pr_detail # 产品名称,pr_spec # 产品规格";
            pr_code.FormName = Name;
            pr_code.DBTitle = "物料查询";
            pr_code.Condition = "pr_code not like '%LX%'";
            pr_code.SetValueField = new string[] { "pr_code" };
            DateTime targetMonth = DateCode.Value.AddMonths(2);
            DateTime endOfMonth = new DateTime(targetMonth.Year, targetMonth.Month,
                                               DateTime.DaysInMonth(targetMonth.Year, targetMonth.Month));
            DateCode.Value = endOfMonth;
            dh = SystemInf.dh;
        }

        private void nr_rule_DBChange(object sender, EventArgs e)
        {
            Dbfind = ps_prodcode.ReturnData;
            BaseUtil.SetFormValue(this.Controls, Dbfind);
        }

        void uploadfile()
        {
            if (FilePath.Text == "")
            {
                MessageBox.Show("文件不能为空!");
                return;
            }
            //if (pr_code.Text == "")
            //{
            //    MessageBox.Show("产品编号不能为空!");
            //    return;
            //}
            if (JPG.Checked)
            {
                string fileName = FilePath.Text.Substring(FilePath.Text.LastIndexOf(@"\") + 1).Replace(".xls", "").Replace(".xlsx", "");
                // 入库的ps_prodcode用完整文件名，如 (R1)WI-LC-31-544 冷端热端铜柱焊接组件检漏作业指导书x
                ps_prodcode.Text = fileName;
                // 匹配/删除时用截取出来的编号，如 WI-LC-31-544(新旧版本号都指向同一条数据)
                string prodCode = GetProdCodeFromFileName(fileName);
                OperatResult.AppendText("解析SOP编号: " + prodCode + "，文件名: " + ps_prodcode.Text + "\n");
                pr_code_Leave(new object(), new EventArgs());
                Workbook workbook = new Workbook(FilePath.Text);
                string pnTools = GetPnToolsFromExcel(workbook);
                if (pnTools == null)
                {
                    // 整个Excel都没有PN/TOOLS列时，回退用表单上的产品编号
                    pnTools = pr_code.Text;
                }
                OperatResult.AppendText("解析PN/TOOLS: " + pnTools + "\n");
                // 匹配已存在的旧数据：新旧版本号不同(如(R0)/(R1))、或历史数据存的是完整文件名，都按中间不变的SOP编号(如WI-LC-31-544)来匹配
                List<string> oldIds = FindDuplicatedSopIds(prodCode);
                if (oldIds.Count > 0)
                {
                    // 已存在相同SOP编号：先删除原明细和主表数据，后面重新插入
                    for (int d = 0; d < oldIds.Count; d++)
                    {
                        dh.ExecuteSql("delete from ProductSOPdetail where psd_psid='" + oldIds[d] + "'", "delete");
                        dh.ExecuteSql("delete from productsop where ps_id='" + oldIds[d] + "'", "delete");
                    }
                    OperatResult.AppendText("已删除原SOP编号【" + prodCode + "】的旧数据(" + oldIds.Count + "条)，重新插入\n");
                }
                string code = LogicHandler.GetPiInoutCode("ProductSOP", "1");
                ps_code.Text = code;
                if (VaildDate.Checked)
                {
                    dh.ExecuteSql("insert into productsop(ps_id,ps_code,ps_prodcode,ps_indate,ps_inman,ps_status,ps_statuscode,ps_vailddate,ps_prcode)values(productsop_seq.nextval,'" + code + "','" + ps_prodcode.Text + "',sysdate,'" + User.UserName + "','在录入','ENTERING',to_date('" + DateCode.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd'),'" + pnTools + "')", "insert");
                }
                else
                {
                    dh.ExecuteSql("insert into productsop(ps_id,ps_code,ps_prodcode,ps_indate,ps_inman,ps_status,ps_statuscode,ps_prcode)values(productsop_seq.nextval,'" + code + "','" + ps_prodcode.Text + "',sysdate,'" + User.UserName + "','在录入','ENTERING','" + pnTools + "')", "insert");
                }
                List<Worksheet> list = new List<Worksheet>();
                List<string> filename = new List<string>();

                if (VaildDate.Checked)
                {
                    dh.ExecuteSql("update ProductSOP set ps_attachsop='',ps_inman='" + User.UserName + "',ps_prcode='" + pnTools + "',ps_vailddate=to_date('" + DateCode.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') where ps_prodcode='" + ps_prodcode.Text + "'", "update");
                }
                else
                {
                    dh.ExecuteSql("update ProductSOP set ps_attachsop='',ps_inman='" + User.UserName + "',ps_prcode='" + pnTools + "' where ps_prodcode='" + ps_prodcode.Text + "'", "update");
                }

                OperatResult.AppendText("一共Sheet: " + workbook.Worksheets.Count + "\n");
                for (int i = 0; i < workbook.Worksheets.Count; i++)
                {
                    OperatResult.AppendText("正在解析Sheet" + workbook.Worksheets[i].Name + "\n");
                    if (JPG.Checked)
                    {
                        //try
                        //{
                        // ---------- 输出目录 ----------
                        if (!Directory.Exists(Application.StartupPath + @"\" + ps_prodcode.Text))
                        {
                            Directory.CreateDirectory(Application.StartupPath + @"\" + ps_prodcode.Text);
                        }
                        string path = Application.StartupPath + @"\" + ps_prodcode.Text + @"\";
                        Worksheet she = workbook.Worksheets[i];
                        // ---------- 基础参数 ----------
                        int contentMaxCol = she.Cells.MaxDataColumn;
                        for (int p = 0; p < she.Pictures.Count; p++)
                        {
                            if (she.Pictures[p].LowerRightColumn > contentMaxCol)
                                contentMaxCol = she.Pictures[p].LowerRightColumn;
                        }
                        for (int s = 0; s < she.Shapes.Count; s++)
                        {
                            if (she.Shapes[s].LowerRightColumn > contentMaxCol)
                                contentMaxCol = she.Shapes[s].LowerRightColumn;
                        }
                        int lastColIndex = contentMaxCol + 3;   // 额外留3列边距
                        string lastColLetter = CellsHelper.ColumnIndexToName(lastColIndex);
                        const int headerRows = 3;
                        int dataRowsPerPage = int.Parse(RowCount.Value.ToString());
                        int contentMaxRow = she.Cells.MaxDataRow;
                        for (int p = 0; p < she.Pictures.Count; p++)
                        {
                            if (she.Pictures[p].LowerRightRow > contentMaxRow)
                                contentMaxRow = she.Pictures[p].LowerRightRow;
                        }
                        for (int s = 0; s < she.Shapes.Count; s++)
                        {
                            if (she.Shapes[s].LowerRightRow > contentMaxRow)
                                contentMaxRow = she.Shapes[s].LowerRightRow;
                        }
                        int actualDataRows = Math.Max(contentMaxRow + 1 - headerRows, 0);
                        int totalPages = (int)Math.Ceiling(actualDataRows / (double)dataRowsPerPage);
                        if (totalPages == 0) totalPages = 1;
                        int totalRows = headerRows + totalPages * dataRowsPerPage;
                        int totalDataRows = Math.Max(totalRows - headerRows, 0);
                        int wmRowGlobal = int.Parse(Y.Value.ToString());
                        int wmCol = int.Parse(X.Value.ToString());
                        int imageIndex = 1;

                        for (int page = 0; page < totalPages; page++)
                        {
                            // ---------- 1. 复制原表到临时工作表 ----------
                            int newSheetIndex = workbook.Worksheets.Add();
                            Worksheet newshe = workbook.Worksheets[newSheetIndex];
                            newshe.Copy(she);
                            newshe.Name = $"Temp_{page}";
                            int dataStartRow = headerRows + page * dataRowsPerPage;
                            int dataEndRow = Math.Min(headerRows + (page + 1) * dataRowsPerPage - 1, totalRows - 1);

                            // ---------- 2. 按页过滤原表图片（Pictures） ----------
                            for (int p = newshe.Pictures.Count - 1; p >= 0; p--)
                            {
                                Aspose.Cells.Drawing.Picture pic = newshe.Pictures[p];
                                int topRow = pic.UpperLeftRow;
                                bool isHeaderPic = topRow < headerRows;
                                bool belongsToPage = topRow >= dataStartRow && topRow <= dataEndRow;
                                if (!isHeaderPic && !belongsToPage)
                                {
                                    newshe.Pictures.RemoveAt(p);
                                }
                            }
                            // ---------- 3. 按页过滤原表形状（Shapes） ----------
                            for (int s = newshe.Shapes.Count - 1; s >= 0; s--)
                            {
                                Aspose.Cells.Drawing.Shape shape;
                                try { 
                                    shape = newshe.Shapes[s];
                                }
                                catch { continue; }
                                int topRow = shape.UpperLeftRow;
                                bool isHeaderShape = topRow < headerRows;
                                bool belongsToPage = topRow >= dataStartRow && topRow <= dataEndRow;
                                if (!isHeaderShape && !belongsToPage)
                                {
                                    try { newshe.Shapes.RemoveAt(s); }
                                    catch { shape.IsHidden = true; }
                                }
                                else {
                                    try
                                    {
                                        // 1. 启用文本自动换行
                                        shape.IsTextWrapped = true;

                                        Console.WriteLine(shape.Text);
                                        // 2. 获取文本对齐对象并设置内边距
                                        if (shape.TextBody != null && shape.TextBody.TextAlignment != null)
                                        {
                                            var textAlign = shape.TextBody.TextAlignment;
                                            textAlign.LeftMarginPt = 1;
                                            textAlign.RightMarginPt = 1;
                                            textAlign.TopMarginPt = 1;
                                            textAlign.BottomMarginPt = 1;
                                        }

                                        // 3. 设置文本对齐方式
                                        shape.TextVerticalAlignment = TextAlignmentType.Left;
                                        shape.TextHorizontalAlignment = TextAlignmentType.Left;

                                        // 4. 自动调整形状大小以适应文本（如果形状允许）
                                        if (shape.TextBody != null && shape.TextBody.TextAlignment != null)
                                        {
                                            shape.TextBody.TextAlignment.TextHorizontalOverflow = TextOverflowType.Ellipsis;
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        // 某些形状可能不支持所有属性，忽略异常
                                        Console.WriteLine($"Shape优化失败: {ex.Message}");
                                    }
                                }
                            }
                            // ---------- 4. 清空非本页单元格内容 ----------
                            for (int r = 0; r < totalRows; r++)
                            {
                                if (r < headerRows) continue;
                                if (r >= dataStartRow && r <= dataEndRow) continue;
                                newshe.Cells.ClearRange(r, 0, r, lastColIndex);
                            }
                            // ---------- 5. 删除非本页整行 ----------
                            for (int r = totalRows - 1; r >= 0; r--)
                            {
                                if (r < headerRows) continue;
                                if (r >= dataStartRow && r <= dataEndRow) continue;
                                newshe.Cells.DeleteRow(r);
                            }
                            // ---------- 6. 页面设置 ----------
                            int printMaxRow = newshe.Cells.MaxDataRow;
                            for (int p = 0; p < newshe.Pictures.Count; p++)
                            {
                                if (newshe.Pictures[p].LowerRightRow > printMaxRow)
                                {
                                    printMaxRow = newshe.Pictures[p].LowerRightRow;
                                }
                            }
                            for (int s = 0; s < newshe.Shapes.Count; s++)
                            {
                                if (newshe.Shapes[s].LowerRightRow > printMaxRow)
                                {
                                    printMaxRow = newshe.Shapes[s].LowerRightRow;
                                }
                            }

                            int newTotalRows = printMaxRow + 1;
                            int pageHeight = headerRows + dataRowsPerPage;
                            int printEndRow = Math.Max(newTotalRows, pageHeight);
                            if (i == 0)
                                newshe.PageSetup.PrintArea = $"A1:{lastColLetter}{25}";
                            else
                                newshe.PageSetup.PrintArea = $"A1:{lastColLetter}{printEndRow}";

                            newshe.PageSetup.CenterHorizontally = true;
                            newshe.PageSetup.CenterVertically = true;
                            newshe.PageSetup.LeftMargin = 2;
                            newshe.PageSetup.RightMargin = 2;
                            newshe.PageSetup.TopMargin = 2;
                            newshe.PageSetup.BottomMargin = 2;
                            newshe.PageSetup.SetFooter(0, "");
                            newshe.PageSetup.SetFooter(1, "");
                            newshe.PageSetup.SetFooter(2, "");
                            // ---------- 7. 插入水印 ----------
                            if (File.Exists(PrintPath.Text))
                            {
                                int rowOffset = headerRows + int.Parse(Y.Value.ToString());
                                newshe.Pictures.Add(rowOffset, int.Parse(X.Value.ToString()), PrintPath.Text);
                            }
                            // ---------- 8. 页脚 ----------
                            string pageFooter = $"第 {imageIndex} 页 / 共 {totalPages} 页";
                            newshe.PageSetup.SetFooter(1, pageFooter);

                            // ==================★ 优化点：提高渲染分辨率（核心改动） ==================
                            ImageOrPrintOptions options = new ImageOrPrintOptions();
                            options.ImageFormat = ImageFormat.Png;
                            options.OnePagePerSheet = true;             // 保留：整页一张图
                            options.PrintingPage = PrintingPageType.Default;
                            // ★ 把默认 96 DPI 提高到 300 DPI，文字/表格瞬间变清晰（约放大3倍像素）
                            options.HorizontalResolution = 300;
                            options.VerticalResolution = 300;
                            // ★ 开启抗锯齿，边缘更平滑（18.x 支持，可选）
                            options.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
                            // ★ 文本渲染更清晰（可选）
                            options.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAliasGridFit;
                            // ==================★ 优化点结束 ==================

                            SheetRender sr = new SheetRender(newshe, options);
                            if (sr.PageCount > 0)
                            {
                                using (Bitmap bitmap = sr.ToImage(0))
                                {
                                    string imagename = $"{she.Name}_{imageIndex}.png";
                                    string imagePath = Path.Combine(path, imagename);
                                    bitmap.Save(imagePath, ImageFormat.Png);
                                    OperatResult.AppendText("解析图片【" + imagePath + "】\n");
                                    // ---------- 10. 上传 ----------
                                    Dictionary<string, object> dic = new Dictionary<string, object>();
                                    dic.Add("em_name", "管理员");
                                    dic.Add("em_code", "ADMIN");
                                    dic.Add("caller", "ProductSOP");
                                    OperatResult.AppendText("上传文件【" + imagePath + "】\n");
                                    string fp_id = UploadFilesToRemoteUrl(
                                        "https://mes.lihantech.cn/mes/MEScommon/uploadFiles.action?_noc=1",
                                        imagePath, dic);
                                    // ---------- 11. 入库 ----------
                                    string ps_id = dh.getFieldDataByCondition(
                                        "ProductSOP", "ps_id", "ps_prodcode='" + ps_prodcode.Text + "'").ToString();
                                    dh.ExecuteSql(
                                        "insert into ProductSOPdetail(psd_id,psd_psid,psd_attach) " +
                                        "select ProductSOPdetail_seq.nextval,ps_id,'" + imagename + ";" + fp_id +
                                        "' from ProductSOP where ps_prodcode='" + ps_prodcode.Text + "'", "insert");
                                }
                            }
                            // ---------- 12. 删除临时工作表 ----------
                            workbook.Worksheets.RemoveAt(newSheetIndex);
                            imageIndex++;
                        }
                        //}
                        //catch (Exception ex)
                        //{
                        //    OperatResult.AppendText(ex.Message + ex.StackTrace);
                        //}
                    }


                }
            }
            else if (PDF.Checked)
            {
                string fileName = FilePath.Text.Substring(FilePath.Text.LastIndexOf(@"\") + 1).Replace(".pdf", "");
                // 入库的ps_prodcode用完整文件名
                ps_prodcode.Text = fileName;
                // 匹配/删除时用截取出来的编号
                string prodCode = GetProdCodeFromFileName(fileName);
                OperatResult.AppendText("解析SOP编号: " + prodCode + "，文件名: " + ps_prodcode.Text + "\n");
                pr_code_Leave(new object(), new EventArgs());
                // 匹配已存在的旧数据：新旧版本号不同(如(R0)/(R1))、或历史数据存的是完整文件名，都按中间不变的SOP编号(如WI-LC-31-544)来匹配
                List<string> oldIds = FindDuplicatedSopIds(prodCode);
                if (oldIds.Count > 0)
                {
                    // 已存在相同SOP编号：先删除原明细和主表数据，后面重新插入
                    for (int d = 0; d < oldIds.Count; d++)
                    {
                        dh.ExecuteSql("delete from ProductSOPdetail where psd_psid='" + oldIds[d] + "'", "delete");
                        dh.ExecuteSql("delete from productsop where ps_id='" + oldIds[d] + "'", "delete");
                    }
                    OperatResult.AppendText("已删除原SOP编号【" + prodCode + "】的旧数据(" + oldIds.Count + "条)，重新插入\n");
                }
                string code = LogicHandler.GetPiInoutCode("ProductSOP", "1");
                ps_code.Text = code;
                if (VaildDate.Checked)
                {
                    dh.ExecuteSql("insert into productsop(ps_id,ps_code,ps_prodcode,ps_indate,ps_inman,ps_status,ps_statuscode,ps_vailddate)values(productsop_seq.nextval,'" + code + "','" + ps_prodcode.Text + "',sysdate,'" + User.UserName + "','在录入','ENTERING',to_date('" + DateCode.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd'))", "insert");
                }
                else
                {
                    dh.ExecuteSql("insert into productsop(ps_id,ps_code,ps_prodcode,ps_indate,ps_inman,ps_status,ps_statuscode)values(productsop_seq.nextval,'" + code + "','" + ps_prodcode.Text + "',sysdate,'" + User.UserName + "','在录入','ENTERING')", "insert");
                }
                if (VaildDate.Checked)
                {
                    dh.ExecuteSql("update ProductSOP set ps_attachsop='',ps_inman='" + User.UserName + "',ps_vailddate=to_date('" + DateCode.Value.ToString("yyyy-MM-dd") + "','yyyy-mm-dd') where ps_prodcode='" + ps_prodcode.Text + "'", "update");
                }
                else
                {
                    dh.ExecuteSql("update ProductSOP set ps_attachsop='',ps_inman='" + User.UserName + "' where ps_prodcode='" + ps_prodcode.Text + "'", "update");
                }
                Dictionary<string, object> dic = new Dictionary<string, object>();
                dic.Add("em_name", "管理员");
                dic.Add("em_code", "ADMIN");
                dic.Add("caller", "ProductSOP");
                OperatResult.AppendText("上传文件【" + ps_prodcode.Text + "" + "】\n");
                string fp_id = UploadFilesToRemoteUrl("https://mes.lihantech.cn/mes/MEScommon/uploadFiles.action?_noc=1", FilePath.Text, dic);
                string ps_id = dh.getFieldDataByCondition("ProductSOP", "ps_id", "ps_prodcode='" + ps_prodcode.Text + "'").ToString();
                dh.ExecuteSql("insert into ProductSOPdetail(psd_id,psd_psid,psd_attach) select ProductSOPdetail_seq.nextval,ps_id,'" + ps_prodcode.Text + ";" + fp_id + "' from ProductSOP where ps_prodcode='" + ps_prodcode.Text + "'", "insert");
            }
        }

        /// <summary>
        /// 从文件名中截取中间不变的SOP编号(忽略版本号)
        /// 新版本：(R1)WI-LC-31-544 冷端热端铜柱焊接组件检漏作业指导书x
        /// 旧版本：(R0)WI-LC-31-544 冷端热端铜柱焊接组件检漏作业指导书x
        /// 两者都取到同一个编号：WI-LC-31-544，按它来匹配是否重复
        /// 匹配不到编号时返回原文件名
        /// </summary>
        private string GetProdCodeFromFileName(string fileName)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                return "";
            }
            string name = fileName.Trim();
            // 1. 去掉版本号，如 (R1)、(R0)、（R1）、(Rev.1)，以及开头的 R1- / R0_ 这类写法
            name = System.Text.RegularExpressions.Regex.Replace(name, @"[(（]\s*[Rr](?:[Ee][Vv]?)?\.?\s*\d+\s*[)）]", " ");
            name = System.Text.RegularExpressions.Regex.Replace(name, @"^\s*[Rr](?:[Ee][Vv]?)?\.?\s*\d+\s*[-_\s]+", "");
            // 2. 取编号形式的字符串：由字母数字和短横线组成，如 WI-LC-31-544
            System.Text.RegularExpressions.Match m = System.Text.RegularExpressions.Regex.Match(
                name, "[A-Za-z0-9]+(?:-[A-Za-z0-9]+)+");
            if (m.Success)
            {
                return m.Value;
            }
            return name.Trim();
        }

        /// <summary>
        /// 按SOP编号查找productsop中已存在的重复数据(返回ps_id列表)
        /// 兼容两种情况：
        /// 1. 历史数据存的是完整文件名，如 (R0)WI-LC-31-544 冷端热端铜柱焊接组件检漏作业指导书x
        /// 2. 已按编号存储的数据，如 WI-LC-31-544
        /// 只要去掉版本号后中间那段编号与目标编号一致，就视为同一条SOP
        /// </summary>
        private List<string> FindDuplicatedSopIds(string prodcode)
        {
            List<string> ids = new List<string>();
            if (string.IsNullOrEmpty(prodcode))
            {
                return ids;
            }
            // 先用like把可能命中的记录捞出来，再在程序里按编号精确比对，避免 WI-LC-31-5441 这类误匹配
            DataTable dt = (DataTable)dh.ExecuteSql(
                "select ps_id,ps_prodcode from productsop where ps_prodcode like '%" + prodcode + "%'", "select");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string dbValue = dt.Rows[i]["ps_prodcode"].ToString();
                if (dbValue.Trim().Equals(prodcode, StringComparison.OrdinalIgnoreCase)
                    || GetProdCodeFromFileName(dbValue).Equals(prodcode, StringComparison.OrdinalIgnoreCase))
                {
                    ids.Add(dt.Rows[i]["ps_id"].ToString());
                }
            }
            return ids;
        }

        /// <summary>
        /// 从Excel中查找标题为PN/TOOLS的列，收集该列标题下方所有非空内容，去重后用逗号拼接返回
        /// 值为JT开头的不记录
        /// 找不到PN/TOOLS列时返回null；找到但内容为空(或被JT过滤完)时返回空字符串
        /// </summary>
        private string GetPnToolsFromExcel(Workbook workbook)
        {
            bool headerFound = false;
            List<string> values = new List<string>();
            for (int i = 0; i < workbook.Worksheets.Count; i++)
            {
                Worksheet she = workbook.Worksheets[i];
                Cells cells = she.Cells;
                int maxRow = cells.MaxDataRow;
                int maxCol = cells.MaxDataColumn;
                int headerCol = -1;
                int headerRow = -1;
                // 1. 先在单元格中查找PN/TOOLS标题
                for (int r = 0; r <= maxRow && headerCol < 0; r++)
                {
                    for (int c = 0; c <= maxCol; c++)
                    {
                        object val = cells[r, c].Value;
                        if (val == null) continue;
                        string text = val.ToString().Trim().ToUpper();
                        if (text.Contains("PN/TOOLS") || text.Contains("PN／TOOLS"))
                        {
                            headerCol = c;
                            headerRow = r;
                            break;
                        }
                    }
                }
                // 2. 单元格中没找到时，尝试从形状(文本框)标题中定位列
                if (headerCol < 0)
                {
                    for (int s = 0; s < she.Shapes.Count; s++)
                    {
                        string shapeText;
                        try { shapeText = she.Shapes[s].Text; }
                        catch { continue; }
                        if (shapeText != null && shapeText.Trim().ToUpper().Contains("PN/TOOLS"))
                        {
                            headerCol = she.Shapes[s].UpperLeftColumn;
                            headerRow = she.Shapes[s].UpperLeftRow;
                            break;
                        }
                    }
                }
                if (headerCol < 0) continue;
                headerFound = true;
                // 3. 收集标题下方该列所有非空内容，去重(保持顺序)，JT开头的不记录
                for (int r = headerRow + 1; r <= Math.Max(maxRow, cells.MaxDataRow); r++)
                {
                    object val = cells[r, headerCol].Value;
                    if (val == null) continue;
                    string text = val.ToString().Trim();
                    if (text == "") continue;
                    if (text.ToUpper().StartsWith("JT")) continue;
                    if (!values.Contains(text))
                    {
                        values.Add(text);
                    }
                }
            }
            if (!headerFound)
            {
                return null;
            }
            return string.Join(",", values.ToArray());
        }

        /// <summary>
        /// 请求上传图片到阿里云
        /// </summary>
        /// <param name="url">上传地址</param>
        /// <param name="filepath">本地文件路径</param>
        /// <param name="dic">上传的数据信息</param>
        /// <returns></returns>
        public string UploadFilesToRemoteUrl(string url1, string filepath, Dictionary<string, object> dic)
        {
            try
            {
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11;
                ServicePointManager.DefaultConnectionLimit = 50;

                string boundary = DateTime.Now.Ticks.ToString("x");

                byte[] boundarybytes = System.Text.Encoding.UTF8.GetBytes("--" + boundary + "\r\n");

                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url1);
                request.Method = "POST";
                request.Timeout = 10 * 10000;
                request.ContentType = "multipart/form-data; boundary=" + boundary;

                Stream rs = request.GetRequestStream();

                var endBoundaryBytes = Encoding.UTF8.GetBytes("--" + boundary + "--\r\n");

                string formdataTemplate = "Content-Disposition: form-data; name=\"{0}\"\r\n" + "\r\n" + "{1}" + "\r\n";
                if (dic != null)
                {
                    foreach (string key in dic.Keys)
                    {
                        rs.Write(boundarybytes, 0, boundarybytes.Length);

                        string formitem = string.Format(formdataTemplate, key, dic[key]);

                        byte[] formitembytes = System.Text.Encoding.UTF8.GetBytes(formitem);

                        rs.Write(formitembytes, 0, formitembytes.Length);
                    }

                }

                string headerTemplate = "Content-Disposition: form-data; name=\"{0}\"; filename=\"{1}\"\r\n\r\n";
                {
                    rs.Write(boundarybytes, 0, boundarybytes.Length);

                    var header = string.Format(headerTemplate, "file", Path.GetFileName(filepath));

                    var headerbytes = System.Text.Encoding.UTF8.GetBytes(header);

                    rs.Write(headerbytes, 0, headerbytes.Length);

                    using (var fileStream = new FileStream(filepath, FileMode.Open, FileAccess.Read))
                    {
                        var buffer = new byte[1024];

                        var bytesRead = 0;

                        while ((bytesRead = fileStream.Read(buffer, 0, buffer.Length)) != 0)
                        {
                            rs.Write(buffer, 0, bytesRead);
                        }
                    }
                    var cr = Encoding.UTF8.GetBytes("\r\n");

                    rs.Write(cr, 0, cr.Length);
                }

                rs.Write(endBoundaryBytes, 0, endBoundaryBytes.Length);

                var response = request.GetResponse() as HttpWebResponse;

                StreamReader newReader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
                string Content = newReader.ReadToEnd();
                Dictionary<string, object> dic1 = new Dictionary<string, object>();
                List<Dictionary<string, object>> dic2 = null;
                dic1 = BaseUtil.ToDictionary(Content);
                dic2 = dic1["data"] as List<Dictionary<string, object>>;
                string fp_id = "";
                if (dic2[0]["filepath"] != null)
                {
                    fp_id = dic2[0]["filepath"].ToString();
                }
                if (response.StatusCode == HttpStatusCode.OK)
                {
                    return fp_id;
                }
            }
            catch (Exception e)
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "上传SOP", "上传失败", ps_prodcode.Text, "");
                Console.WriteLine(e.Message + e.StackTrace);
            }
            return "";
        }

        private void ChooseFile_Click(object sender, EventArgs e)
        {
            ChoosePath.Filter = "(*.xlsx)|*.xlsx|(*.xls)|*.xls|(*.pdf)|*.pdf";
            DialogResult result = ChoosePath.ShowDialog();
            if (result == DialogResult.OK)
            {
                FilePath.Text = ChoosePath.FileName;
            }
        }

        private void UploadSOP_Click(object sender, EventArgs e)
        {
            Thread thread = new Thread(uploadfile);
            SetLoadingWindow stw = new SetLoadingWindow(thread, "上传文件");
            stw.StartPosition = FormStartPosition.CenterScreen;
            stw.ShowDialog();
            if (JPG.Checked)
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "上传SOP【图片格式】", "上传成功", ps_prodcode.Text, "");
            }
            else
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "上传SOP【PDF格式】", "上传成功", ps_prodcode.Text, "");
            }
        }

        private void OperatResult_TextChanged(object sender, EventArgs e)
        {
            //OperatResult.SelectionStart = Text.Length;
            OperatResult.ScrollToCaret();
        }

        private void SendSop_Click(object sender, EventArgs e)
        {
            try
            {
                if (ps_prodcode.Text == "")
                {
                    MessageBox.Show("线别和SOP文档不能为空");
                    return;
                }
                if (SendDGV.Rows.Count > 0)
                {
                    List<Dictionary<string, string>> listr = new List<Dictionary<string, string>>();
                    JavaScriptSerializer jss = new JavaScriptSerializer();
                    Dictionary<string, object> map1 = new Dictionary<string, object>();
                    Dictionary<string, object> map = new Dictionary<string, object>();
                    string path;
                    string pathroot = dh.GetConfig("filePathUrl", "sys").ToString();
                    dh.ExecuteSql("insert into soprelease(SR_ID, sr_pscode, SR_STATUS, SR_MACHINETYPE,sr_releaseman,sr_readman)" +
                   "select soprelease_seq.nextval,'" + ps_code.Text + "',0,'" + ps_prodcode.Text + "','" + User.UserName + "',em_code from employee ", "insert");
                    LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "广播SOP", "广播成功", ps_prodcode.Text, "");
                    MessageBox.Show("文档[" + ps_prodcode.Text + "]下放成功");
                }
                else
                {
                    MessageBox.Show("产品编号" + ps_prodcode.Text + "未维护SOP文档");
                    LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "广播SOP", "广播失败，未维护SOP文档", ps_prodcode.Text, "");
                }
            }
            catch (Exception ex)
            {
                LogicHandler.DoCommandLog("SOP", usercode, "", "", sourcecode, "广播SOP", "广播失败", ps_prodcode.Text, "");
                Console.WriteLine(ex.Message + ex.StackTrace);
            }
        }

        public string encryptBASE64(string key)
        {
            Console.WriteLine(key);
            byte[] bytes = Encoding.Default.GetBytes(key);
            return Convert.ToBase64String(bytes);
        }

        public static string EncodeBase64(string code_type, string code)
        {
            string encode = "";
            byte[] bytes = Encoding.GetEncoding(code_type).GetBytes(code);
            try
            {
                encode = Convert.ToBase64String(bytes);
            }
            catch
            {
                encode = code;
            }
            return encode;
        }

        private void pr_code_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                LoadPrCode();
            }
        }

        private BindingSource bs = new BindingSource(); // 类成员变量

        private void LoadPrCode()
        {
            DataTable dt = (DataTable)dh.ExecuteSql("select * from productsop where ps_prodcode='" + ps_prodcode.Text + "'", "select");
            if (dt.Rows.Count > 0)
            {
                string ps_id = dt.Rows[0]["ps_id"].ToString();
                dt = (DataTable)dh.ExecuteSql(@"select fp_name,fp_path,'' issend,psd_iskey 
                                        from ProductSOPdetail 
                                        left join filepath on FP_ID=substr(PSD_ATTACH,instr(PSD_ATTACH,';')+1)  
                                        where psd_psid=" + ps_id, "select");
                if (dt.Rows.Count > 0)
                {
                    // 在 UI 线程安全执行（如果已经在 UI 线程则直接调用）
                    if (SendDGV.InvokeRequired)
                        SendDGV.Invoke(new Action(() => BindData(dt)));
                    else
                        BindData(dt);
                }
            }
        }
        private void BindData(DataTable dt)
        {
            // 使用 BindingSource 避免直接置 null
            if (SendDGV.DataSource == null)
            {
                bs.DataSource = dt;
                SendDGV.DataSource = bs;
            }
            else
            {
                bs.DataSource = dt; // 直接替换内部数据，控件不会经历“无数据”状态
            }
            // 可选：刷新显示
            SendDGV.Refresh();
        }

        private void pr_code_Leave(object sender, EventArgs e)
        {
            //LoadPrCode();
        }

        bool AllChecked = false;
        private void ChooseALL_Click(object sender, EventArgs e)
        {
            if (AllChecked)
            {
                foreach (DataGridViewRow dr in SendDGV.Rows)
                    dr.Cells[0].Value = false;
                AllChecked = false;
            }
            else
            {
                foreach (DataGridViewRow dr in SendDGV.Rows)
                    dr.Cells[0].Value = true;
                AllChecked = true;
            }
        }
        private void ps_prodcode_UserControlTextChanged(object sender, EventArgs e)
        {
            LoadPrCode();
        }

        private void GetSOP_Click(object sender, EventArgs e)
        {
            LoadPrCode();
        }

        private void ChoosePrint_Click(object sender, EventArgs e)
        {
            ChoosePath.Filter = "(*.png)|*.png";
            DialogResult result = ChoosePath.ShowDialog();
            if (result == DialogResult.OK)
            {
                PrintPath.Text = ChoosePath.FileName;
            }
        }

        private void SendDGV_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.RowIndex < 0 || e.ColumnIndex < 0) return;
        }
    }
}