mes-client/FileWatcher/UploadMakePlan.cs

using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Threading;
using System.Windows.Forms;
using System.Xml;
using System.Text;
using System.Media;
using System.Linq;
using NPOI.SS.Formula.Functions;
using System.Windows.Media.TextFormatting;
using UMESDLLService;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Oracle.ManagedDataAccess.Client;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Tab;

namespace FileWatcher
{
    public partial class UploadMakePlan : Form
    {

        string connectionString = "Connection Timeout=0;Pooling=false;Password=select!#%*(;User ID=N_MES;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.7)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";


        StringBuilder sql = new StringBuilder();

        DataHelper dh;

        string imaster;

        public UploadMakePlan()
        {
            InitializeComponent();
            StartPosition = FormStartPosition.CenterScreen;
        }

        string fieltype = "";
        string ftpulr = "";
        private void Form1_Load(object sender, EventArgs e)
        {
            dh = new DataHelper(connectionString);
        }

        private void Upload_Click(object sender, EventArgs e)
        {
            DateTime today = DateTime.Value;
            string todayDate = today.ToString("MMdd");
            string code = dh.GetSerialNumberByCaller("MakePlan");
            string id = "";
            DataTable dt = (DataTable)dh.ExecuteSql("select mp_id from MakePlan where to_char(mp_begintime,'yyyymmdd')='" + today.ToString("yyyyMMdd") + "'", "select");
            if (dt.Rows.Count == 0)
            {
                id = dh.GetSEQ("MakePlan_seq");
                dh.ExecuteSql("insert into MakePlan(MP_ID, MP_CODE, MP_KIND, MP_INDATE, MP_STATUS, MP_STATUSCODE, MP_BEGINTIME)" +
                    "values(" + id + ",'" + code + "','日计划',sysdate,'在录入','ENTERING',to_date('" + today.ToString("yyyyMMdd") + "','yyyymmdd'))", "insert");
            }
            else
            {
                id = dt.Rows[0]["mp_id"].ToString();
                dh.ExecuteSql("delete from MakePlandetail where mpd_mpid=" + id, "delete");
            }

            using (FileStream file = new FileStream(FilePath.Text, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(file);
                int detno = 1;

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    string sheetName = sheet.SheetName;
                    if (sheetName.Contains(todayDate))
                    {
                        OperateResult.AppendText(sheetName);
                        OperateResult.AppendText($"Processing sheet: {sheetName}\n");
                        //Console.WriteLine($"Processing sheet: {sheetName}");

                        // 获取标题行（第4行）
                        IRow headerRow = sheet.GetRow(3); // 第4行索引为3
                        if (headerRow == null)
                        {
                            Console.WriteLine("Header row not found.");
                            continue;
                        }

                        // 获取标题列的索引
                        int wccodeIndex = -1;
                        int orderdetnoIndex = -1;
                        int ordercodeIndex = -1;
                        int planqtyIndex = -1;
                        int remarkIndex = -1;

                        for (int colIndex = 0; colIndex < headerRow.LastCellNum; colIndex++)
                        {
                            ICell cell = headerRow.GetCell(colIndex);
                            if (cell != null)
                            {
                                string headerValue = cell.ToString().Trim().Replace("\r\n", "").Replace("\n", "").Replace("\r", "");
                                if (headerValue == "工作中心")
                                {
                                    wccodeIndex = colIndex;
                                }
                                else if (headerValue == "订单序号")
                                {
                                    orderdetnoIndex = colIndex;
                                }
                                else if (headerValue == "订单编号")
                                {
                                    ordercodeIndex = colIndex;
                                }
                                else if (headerValue == "计划数量")
                                {
                                    planqtyIndex = colIndex;
                                }
                                else if (headerValue.Contains("备注"))
                                {
                                    remarkIndex = colIndex;
                                }
                            }
                        }
                        
                        if (wccodeIndex == -1 || orderdetnoIndex == -1 || ordercodeIndex == -1 || planqtyIndex == -1||remarkIndex==-1)
                        {
                            Console.WriteLine("Required columns not found in the header.");
                            continue;
                        }

                        using (OracleConnection conn = new OracleConnection(connectionString))
                        {
                            conn.Open();
                            for (int rowIndex = 4; rowIndex <= sheet.LastRowNum; rowIndex++) // 从第5行开始解析数据
                            {
                                IRow row = sheet.GetRow(rowIndex);
                                if (row != null)
                                {
                                    string wccode = row.GetCell(wccodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).StringCellValue.ToString();
                                    if (wccode == "")
                                    {
                                        continue;
                                    }
                                    string orderdetno = row.GetCell(orderdetnoIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    string ordercode = row.GetCell(ordercodeIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    string planqty = row.GetCell(planqtyIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).NumericCellValue.ToString();
                                    string remark = row.GetCell(remarkIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString();
                                    if (!string.IsNullOrEmpty(wccode) && !string.IsNullOrEmpty(orderdetno) && !string.IsNullOrEmpty(ordercode))
                                    {
                                        string insertQuery = "INSERT INTO MakePlanDetail (mpd_mpid,mpd_detno,mpd_id,mpd_wccode, mpd_orderdetno, mpd_ordercode,mpd_outqty,mpd_remark) VALUES (" + id + ","+ detno + ",MakePlanDetail_seq.nextval,:1, :2, :3,:4,:5)";
                                        using (OracleCommand cmd = new OracleCommand(insertQuery, conn))
                                        {
                                            cmd.Parameters.Add(new OracleParameter(":1", wccode));
                                            cmd.Parameters.Add(new OracleParameter(":2", orderdetno));
                                            cmd.Parameters.Add(new OracleParameter(":3", ordercode));
                                            cmd.Parameters.Add(new OracleParameter(":4", planqty));
                                            cmd.Parameters.Add(new OracleParameter(":5", remark));
                                            cmd.ExecuteNonQuery();
                                        }
                                    }
                                }
                                detno = detno + 1;
                            }
                        }
                        dh.ExecuteSql("delete from MakePlanDetail where mpd_orderdetno is null", "delete");
                        OperateResult.AppendText("计划上传成功");
                    }
                }
            }
        }

        private void ChooseFile_Click(object sender, EventArgs e)
        {
            DialogResult result;
            result = ImportExcel1.ShowDialog();
            if (result == DialogResult.OK)
            {
                FilePath.Text = ImportExcel1.FileName;
            }
        }
    }
}