using LLAirtightApi; using Microsoft.Office.Interop.Excel; using Newtonsoft.Json; using System; using System.Data; using System.Data.SqlClient; using System.IO; using System.Reflection; using System.Text; namespace SLZ_4 { class SaveRecord { public static void WriteTXT(ref TEST_PARAM testInfo, string code, string strPath, int iCH) { string fileName = strPath + "\\" + (iCH + 1).ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".txt"; string str1 = "时间: " + DateTime.Now + "操作员: " + FormMain.form.labelOperator.Text + " 条形码: " + code + " 测试压力: " + testInfo.chTest[iCH].fTestPressure.ToString("F3") + " 测试压力单位: " + FormMain.form.strListPressureUnit[testInfo.iPressureUnit] + " 当前压差: " + testInfo.chTest[iCH].fTestDiffPressure.ToString("F3") + " 泄漏量: " + testInfo.chTest[iCH].fLeakAmount.ToString("F3") + " 泄漏量单位: " + FormMain.form.strListLeakageUnit[testInfo.iLeakUnit] + " 测试结果: " + FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag] + " \n"; System.IO.File.AppendAllText(fileName, str1); } public static void WriteCSV(ref TEST_PARAM testInfo, string code, string strPath, int iCH) { try { string fileName = strPath + "\\" + (iCH + 1).ToString() + "_" + DateTime.Now.ToString("yyyyMMdd") + ".csv"; bool bWriter = false; if (!File.Exists(fileName)) { bWriter = true; } StreamWriter fileWriter = new StreamWriter(fileName, true, Encoding.UTF8); if (bWriter) { fileWriter.Write("时间,操作员,条形码,测试压力,测试压力单位,当前压差,泄漏量,泄漏量单位,测试结果" + "\r\n"); fileWriter.Flush(); } string nowdate = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); fileWriter.Write(nowdate + ","); fileWriter.Write(FormMain.form.labelOperator.Text + ","); fileWriter.Write(code + ","); fileWriter.Write(testInfo.chTest[iCH].fTestPressure.ToString("F3") + ","); fileWriter.Write(FormMain.form.strListPressureUnit[testInfo.iPressureUnit] + ","); fileWriter.Write(testInfo.chTest[iCH].fTestDiffPressure.ToString("F3") + ","); fileWriter.Write(testInfo.chTest[iCH].fLeakAmount.ToString("F3") + ","); fileWriter.Write(FormMain.form.strListLeakageUnit[testInfo.iLeakUnit] + ","); fileWriter.Write(FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag] + "\r\n"); fileWriter.Flush(); fileWriter.Close(); } catch (Exception ex) { FileLogger.Log("写入CSV失败: " + ex.Message); //MessageBox.Show(ex.Message); } } public static bool OpenExcel(string sPath, string code, string result) { string path = sPath + "\\" + DateTime.Now.ToString("yyyyMMdd"); string fileName = path + "\\" + code + "_" + result + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } try { FormMain.form.xapp = new Microsoft.Office.Interop.Excel.Application(); if (File.Exists(fileName) == false)//判断所选路径是否有文件 { //若不存在该文件，则创建新文件 Workbooks xbook1 = FormMain.form.xapp.Workbooks; FormMain.form.xbook = FormMain.form.xapp.Workbooks.Add(true); Worksheet xsheet = (Worksheet)FormMain.form.xbook.Sheets[1]; //添加表头 string[] fieldArr = { "时间", "条形码", "测试压力", "测试压力单位", "当前压差", "泄漏量", "泄漏量单位", "测试结果" }; for (int i = 0; i < fieldArr.Length; i++) { xsheet.Cells[i + 1][1] = fieldArr[i]; } FormMain.form.xbook.SaveAs(fileName);//按照指定路径存储新文件 } else { //若存在该文件，则打开文件并写入数据 FormMain.form.xbook = FormMain.form.xapp.Workbooks._Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); } FormMain.form.xapp.DisplayAlerts = false; } catch (Exception e) { FileLogger.Log("打开Excel文件失败: " + e.Message, LogLevel.ERROR); //MessageBox.Show("打开Excel文件失败: " + e.Message); return false; } return true; } public static void WriteExcel(TEST_PARAM testInfo, string code, string strPath, int iCH) { try { if (OpenExcel(strPath, code, FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag])) { Worksheet xsheet = (Worksheet)FormMain.form.xbook.Sheets[1]; string[] dataArr = { DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), code, testInfo.chTest[iCH].fTestPressure.ToString("F3"), FormMain.form.strListPressureUnit[testInfo.iPressureUnit], testInfo.chTest[iCH].fTestDiffPressure.ToString("F1"), testInfo.chTest[iCH].fLeakAmount.ToString("F3"), FormMain.form.strListLeakageUnit[testInfo.iLeakUnit], FormMain.form.strResult[testInfo.chTest[iCH].ResultFlag]}; int c = xsheet.UsedRange.Rows.Count; for (int i = 0; i < dataArr.Length; i++) { xsheet.Cells[i + 1][c + 1] = dataArr[i]; } FormMain.form.xbook.Save(); if (FormMain.form.xbook != null) { FormMain.form.xbook.Close(); FormMain.form.xbook = null; if (FormMain.form.xapp != null) { FormMain.form.xapp.Quit(); FormMain.form.xapp = null; } } } } catch (Exception ex) { FileLogger.Log("打开Excel失败: "+ ex.Message); //MessageBox.Show("Excel:" + ex.Message); } } public static bool OpenSQL() { if (FormMain.form.sqlConnection != null) { if (FormMain.form.sqlConnection.State == ConnectionState.Open) { return true; } else { FormMain.form.sqlConnection.Close(); FormMain.form.sqlConnection = null; } } try { //string connectionString = "Data source=" + configParam.databaseParam.sServer; //connectionString += ";Initial Catalog=master;User Id=" + configParam.databaseParam.sDBUser // + ";Password=" + configParam.databaseParam.sDBPassword;//定义连接字符串 //sqlConnection = new SqlConnection(connectionString);//创建连接 //sqlConnection.Open();//打开并连接数据库 //if (configParam.databaseParam.sDBName == "") //{ // configParam.databaseParam.sDBName = "LeakTest"; //} //string selectString = "Select * FROM master..SYSDATABASES WHERE name ='" + configParam.databaseParam.sDBName + "'";//输入sql语句,判断是否存在名为LeakTest的数据库 //SqlCommand cmd = new SqlCommand(selectString, sqlConnection); //var str1 = cmd.ExecuteScalar(); //if (str1 is null) //{ // cmd.CommandText = "CREATE DATABASE " + configParam.databaseParam.sDBName;//如果不存在则新建数据库 // cmd.ExecuteNonQuery(); //} //sqlConnection.Close();//关闭数据库 return true; } catch (Exception e) { FileLogger.Log("打开数据库失败:" + e.Message, LogLevel.ERROR); return false; throw; } } public static void WriteSQL(ref TEST_PARAM testInfo, string code, string strPath, int iCH) { try { if (OpenSQL()) { string nowday = DateTime.Now.ToString("yyyy_MM_dd"); string selectString2 = "select * from dbo.sysobjects where name=('test" + nowday + "')";//查询是否有名为leaktest+日期的表格 //SqlCommand cmd2 = new SqlCommand(selectString2, sqlConnection); //var str2 = cmd2.ExecuteScalar(); //if (str2 is null) //{ // //若没有表格则新建表格 // cmd2.CommandText = "CREATE TABLE test" + nowday + // " ([Time] DATETIME ,[BarCode] VarChar(50),[Pressure] FLOAT,[PressureUnit] VarChar(50)," + // "[PressureDiff] FLOAT,[Leakage] FLOAT,[LeakageUnit] VarChar(50),[Result] VarChar(50))"; //Office is the named range. // cmd2.ExecuteNonQuery(); //} ////往表格插入数据 //cmd2.CommandText = "INSERT INTO test" + nowday + // " (Time, BarCode, Pressure, PressureUnit, PressureDiff, Leakage, LeakageUnit, Result)" + // " VALUES('" + DateTime.Now + "', '" + textCode.Text + "', '" + testInfo.fTestPressure.ToString("F3") + "', '" + // strListPressureUnit[iPressureUnit] + "', '" + testInfo.fTestDiffPressure.ToString("F1") + "', '" + // testInfo.fLeakAmount.ToString("F3") + "', '" + strListLeakageUnit[ileakageUnit] + "', '" + labelResult.Text + "')"; //cmd2.ExecuteNonQuery(); } } catch (SqlException ex) { FileLogger.Log("写入数据库失败: " + ex.Message); //MessageBox.Show(ex.Message); } } } }