mes-client/FileAnalysis/Form1.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace FileAnalysis
{
    public partial class Form1 : Form
    {
        DataHelper dh = new DataHelper();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            提示.ShowBalloonTip(30, "提示", "程序启动", ToolTipIcon.Info);
            timer1.Interval = 1000 * 60 * 10;
            timer1.Tick += Timer1_Tick;
            timer1.Start();
            Timer1_Tick(sender, e);
        }

        private void Timer1_Tick(object sender, EventArgs e)
        {
            IPHostEntry IpEntry = Dns.GetHostEntry(Dns.GetHostName());
            string IPAddress = "";
            for (int i = 0; i < IpEntry.AddressList.Length; i++)
            {
                if (IpEntry.AddressList[i].AddressFamily == AddressFamily.InterNetwork)
                    IPAddress = IpEntry.AddressList[i].ToString();
            }

            StringBuilder sql = new StringBuilder();
            sql.Clear();
            sql.Append("select * from(select chipcode_,to_char(createdate_,'yymmdd')cer_testdate,substr(ip,0,instr(ip,'/')-1)ip,localdatapath_,");
            sql.Append("localpath_ from (select A.chipcode_,createdate_,replace(substr(localdatapath_,0,instr(localdatapath_,'$'))");
            sql.Append(",'smb://@','') IP,localdatapath_,replace(replace(replace(substr(localdatapath_,instr(localdatapath_,'$')-1),'$',':'),'/','\\'),'\\\\','\\')  ");
            sql.Append("localpath_ from datacenter$chip@SZSI_P A left join datacenter$mestemp@SZSI_P B on A.CHIPCODE_=B.chipcode_ ");
            sql.Append("where nvl(readstatus_,0)=0) order by createdate_ desc) where ip= '" + IPAddress + "' and rownum<60");
            DataTable dt = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
            List<string> insertSQL = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FileStream fs = null;
                IWorkbook workbook = null;
                ISheet sheet = null;
                string test = dt.Rows[i]["cer_testdate"].ToString();
                string chipcode = dt.Rows[i]["chipcode_"].ToString();
                string filepath = dt.Rows[i]["localpath_"].ToString();
                //string filepath = @"c:\filewatcher\F20036121#07.xls";
                try
                {
                    richTextBox1.AppendText(test + " " + dt.Rows[i]["localpath_"].ToString() + "\n");
                    //如果文件不存在则调用转换
                    if (!File.Exists(filepath))
                    {
                        filepath = filepath.Replace("xls", "jdf");
                        if (!File.Exists(filepath))
                        {
                            filepath = filepath.Replace("jdf", "njdf");
                        }
                        if (filepath.Substring(filepath.LastIndexOf(".") + 1) == "jdf" || filepath.Substring(filepath.LastIndexOf(".") + 1) == "njdf")
                        {
                            if (!filepath.Contains("RECYCLE"))
                            {
                                string FullName = filepath;
                                string Filename = FullName.Substring(FullName.LastIndexOf(@"\") + 1).Split('.')[0];
                                string StartPath = FullName.Substring(0, FullName.LastIndexOf(@"\") + 1);
                                if (File.Exists(FullName))
                                {
                                    richTextBox1.AppendText(DateTime.Now.ToString("yyyy/MM/dd h:mm:ss.fff") + filepath + "\n");
                                }
                                else
                                {
                                    richTextBox1.AppendText("不存在文件" + FullName + "\n");
                                }
                                //不存在同名的文件则进行转换
                                if (!File.Exists(StartPath + Filename + ".xls") && File.Exists(FullName))
                                {
                                    if (FullName.Substring(FullName.LastIndexOf(".") + 1) == "jdf")
                                    {
                                        exec(@"DTS-JDFData2Excel.exe", @"DTS-JDFData2Excel.exe " + FullName);
                                    }
                                    else if (FullName.Substring(FullName.LastIndexOf(".") + 1) == "njdf")
                                    {
                                        exec(@"DTS-Data2Excel.exe", @"DTS-Data2Excel.exe " + FullName);
                                    }
                                }
                            }
                        }
                    }
                    //如果文件存在
                    if (File.Exists(filepath))
                    {
                        using (fs = File.OpenRead(filepath))
                        {
                            workbook = new HSSFWorkbook(fs);
                            sheet = workbook.GetSheet("Data");

                            List<string> itemname = new List<string>();
                            List<string> detno = new List<string>();
                            List<string> bias1 = new List<string>();
                            List<string> bias2 = new List<string>();
                            List<string> bias3 = new List<string>();
                            List<string> minlimit = new List<string>();
                            List<string> maxlimit = new List<string>();
                            List<string> avg = new List<string>();
                            List<string> stdev = new List<string>();
                            List<string> mindata = new List<string>();
                            List<string> maxdata = new List<string>();
                            for (int k = 1; k < sheet.GetRow(13).PhysicalNumberOfCells; k++)
                            {
                                itemname.Add(sheet.GetRow(13).GetCell(k + 1).StringCellValue.Split(' ')[1]);
                                bias1.Add(sheet.GetRow(14).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                bias2.Add(sheet.GetRow(15).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                bias3.Add(sheet.GetRow(16).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                minlimit.Add(sheet.GetRow(17).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                maxlimit.Add(sheet.GetRow(18).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                avg.Add(sheet.GetRow(19).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                stdev.Add(sheet.GetRow(20).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                mindata.Add(sheet.GetRow(21).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                maxdata.Add(sheet.GetRow(22).GetCell(k + 1).StringCellValue.Replace(" ", ""));
                                detno.Add(k.ToString());
                            }
                            //解析前面的范围文件
                            sql.Clear();
                            sql.Append("insert into DATACENTER$CHIP_DATAMAIN@SZSI_P(CHIPCODE_,DATE_,ITEM_NAME,DETNO,BIAS1,BIAS2,BIAS3,MINLIMIT,MAXLIMIT,AVG_,STDEV,MIN_,MAX_)");
                            sql.Append("values('" + chipcode + "',sysdate,:ITEM_NAME,:DETNO,:BIAS1,:BIAS2,:BIAS3,:MINLIMIT,:MAXLIMIT,:AVG_,:STDEV,:MIN_,:MAX_)");
                            dh.BatchInsert(sql.ToString(), new string[] { "itemname", "detno", "bias1", "bias2", "bias3", "minlimit", "maxlimit", "avg", "stdev", "mindata", "maxdata" }, itemname.ToArray(), detno.ToArray(), bias1.ToArray(), bias2.ToArray(), bias3.ToArray(), minlimit.ToArray(), maxlimit.ToArray(), avg.ToArray(), stdev.ToArray(), mindata.ToArray(), maxdata.ToArray());
                            for (int j = 24; j <= sheet.LastRowNum; j++)
                            {
                                string Filed = "";
                                string Value = "";
                                sql.Clear();
                                sql.Append("insert into RECORD$" + test + "(chipcode,detno,");
                                for (int k = 1; k < sheet.GetRow(13).PhysicalNumberOfCells; k++)
                                {
                                    string CellValue = sheet.GetRow(j).GetCell(k + 1).StringCellValue.Replace(" ", "");
                                    Filed += "ITEM" + (k) + ",";
                                    Value += "'" + CellValue + "',";
                                }
                                sql.Append(Filed.Substring(0, Filed.Length - 1) + ") values('" + chipcode + "'," + (j - 23) + "," + Value.Substring(0, Value.Length - 1) + ");");
                                insertSQL.Add(sql.ToString());
                            }
                            string[] strarr = insertSQL.ToArray();
                            string sqlcount = "";
                            for (int j = 0; j < strarr.Length; j++)
                            {
                                sqlcount += strarr[j];
                                if (j > 0 && (j % 1000 == 0 || j == strarr.Length - 1))
                                {
                                    dh.ExecuteSql("begin " + sqlcount + " end;", "insert");
                                    sqlcount = "";
                                }
                            }
                            insertSQL.Clear();
                            //更新为失效状态
                            dh.ExecuteSql("UPDATE CHIPTABLE SET STATUS_=0  WHERE CHIP_CODE='" + chipcode + "'", "insert");
                            //插入有效
                            dh.ExecuteSql("INSERT INTO CHIPTABLE (CHIP_CODE,TABLE_NAME,STATUS_) VALUES ('" + chipcode + "','RECORD$" + test + "',-1)", "insert");

                            dh.ExecuteSql("update datacenter$chip@SZSI_P set readstatus_=-1 where chipcode_='" + chipcode + "'", "update");
                            richTextBox1.AppendText(DateTime.Now.ToString("yyyy/MM/dd h:mm:ss.ff") + chipcode + "\n");
                        }
                    }
                    else
                    {
                        dh.ExecuteSql("update datacenter$chip@SZSI_P set readstatus_=-99 where chipcode_='" + chipcode + "'", "update");
                        dh.ExecuteSql("insert into EDCsyscfaillog@SZSI_P(el_id,el_ip,el_synctime,el_chipcode,el_reason) values(EDCsyscfaillog_seq.nextval@SZSI_P,'" + IPAddress + "',sysdate,'" + chipcode + "','Excel文件不存在，无法解析')", "insert");
                    }
                }
                catch (Exception ex)
                {
                    dh.ExecuteSql("update datacenter$chip@SZSI_P set readstatus_=-99 where chipcode_='" + chipcode + "'", "update");
                    dh.ExecuteSql("insert into EDCsyscfaillog@SZSI_P(el_id,el_ip,el_synctime,el_chipcode,el_reason) values(EDCsyscfaillog_seq.nextval@SZSI_P,'" + IPAddress + "',sysdate,'" + chipcode + "','" + ex.Message + "')", "insert");
                }
            }
        }

        public void exec(string exePath, string parameters)
        {
            Process process = new Process();
            process.StartInfo.FileName = exePath;
            process.StartInfo.Arguments = parameters;
            process.StartInfo.UseShellExecute = false;
            process.StartInfo.CreateNoWindow = true;
            process.StartInfo.RedirectStandardOutput = true;
            process.Start();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Timer1_Tick(sender, e);
        }
    }
}