private void button_PIConfig_GetAllTagList_Click(object sender, EventArgs e) { if (MessageBox.Show(this, "Are you sure you want to extract tag list from PI? \r\nThis operation may take a while...", "Fetching PI tag list", MessageBoxButtons.YesNo) == DialogResult.Yes) { //fetch pitags from pi System.Diagnostics.Process process = new System.Diagnostics.Process(); string configName = listBox_ConfigList.GetItemText(listBox_ConfigList.SelectedItem); process.StartInfo.FileName = Support.InstalPath + "\\resources\\piconfig.exe"; process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden; process.StartInfo.RedirectStandardInput = true; process.StartInfo.RedirectStandardOutput = true; process.StartInfo.UseShellExecute = false; process.Start(); string input = ""; input = input + "@logi " + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_Host) + "," + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_User) + "," + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_Pass) + "," + ConfigFile.read_attribute(configName, ConfigFile.AttribPIConfig_Port) + "\r\n"; input = input + "@maxerr 65535" + "\r\n"; input = input + "@table pipoint" + "\r\n"; input = input + "@mode list" + "\r\n"; input = input + "@ostr tag,pointtype" + "\r\n"; input = input + "@ostr ..." + "\r\n"; input = input + "@select tag=*" + "\r\n"; input = input + "@endsection" + "\r\n"; input = input + "@bye"; process.StandardInput.Write(input); process.StandardInput.Flush(); process.StandardInput.Close(); string result = (process.StandardOutput.ReadToEnd()); string[] results = result.Split(new string[] { "\r\n" }, StringSplitOptions.None); string finalresult = ""; for (int i = 0; i < results.Length; i++) { //test to see if line is valid information or not (alert, error, etc) if (results[i].Contains(",") == true) { finalresult = finalresult + results[i].Split(',')[0] + "\r\n"; } } process.WaitForExit(); //save on the alltags file Support.CreateFile(Support.AllTagFilePrefix + configName + ".txt"); try { Support.WriteOneLine(Support.AllTagFilePrefix + configName + ".txt", finalresult, false); } catch (Exception exc) { LogFile.write_LogFile("Error trying to save list of all PI tags: " + exc.Message); } //DateTime time = DateTime.Now; // Use current time. //string format = "dd/mm/yyyy hh:mm"; // Use this format. } }
/// <summary> /// Tries to convert excel file to csv. Returns TRUE in case of success. /// </summary> /// <param name="m_filePath"></param> /// <returns></returns> public bool ConvertExcelFile(string m_filePath) { string[] whitelistLines; bool fileinlist = false; List <string[]> SheetFilters = new List <string[]>(); try { FileInfo currentFileInfo = new FileInfo(m_filePath); try { Support.CreateFile(Support.WhitelistFilePrefix + ConfigName + ".txt"); whitelistLines = Support.getFileLines(Support.WhitelistFilePrefix + ConfigName + ".txt"); } catch { LogFile.write_LogFile("No WhiteList file was found. All files will be converted."); whitelistLines = new string[] { "*;*" }; throw; } if (currentFileInfo.Extension == ".xlsx" || currentFileInfo.Extension == ".xls") { try { string fileName = currentFileInfo.Name; foreach (string currentLine in whitelistLines) { string[] currentLineSplit = currentLine.Split(';'); if (Regex.IsMatch(fileName.ToLower(), "^" + currentLineSplit[0].ToLower().Replace("*", ".*") + "$")) { fileinlist = true; if (currentLineSplit.Length == 2) { SheetFilters.Add(new string[] { currentLineSplit[1], "" }); } else if (currentLineSplit.Length >= 3) { SheetFilters.Add(new string[] { currentLineSplit[1], currentLineSplit[2] }); } } } } catch (Exception e) { LogFile.write_LogFile("Error reading whitelist: " + e.Message); //throw; } DataSet outputDS = new DataSet(); string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_filePath + ";" + "Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;'"; using (OleDbConnection oleCon = new OleDbConnection(ConnectionString)) { oleCon.Open(); OleDbCommand oleCmd = new OleDbCommand(); oleCmd.Connection = oleCon; DataTable SheetsTable = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow current_sheet in SheetsTable.Rows) { string sheetName = Regex.Replace(current_sheet["TABLE_NAME"].ToString(), @"[;\'$]", ""); if (!fileinlist || SheetFilters.Exists(x => Regex.IsMatch(sheetName.ToLower(), "^" + x[0].ToLower().Replace("*", ".*") + "$"))) { oleCmd.CommandText = "select * from [" + sheetName + "$]"; DataTable currentDataTable = new DataTable(); OleDbDataAdapter OleDA; try { OleDA = new OleDbDataAdapter(oleCmd); currentDataTable.TableName = sheetName; OleDA.Fill(currentDataTable); } catch { continue; } outputDS.Tables.Add(currentDataTable); } } oleCmd = null; oleCon.Close(); StringBuilder outputString = new StringBuilder(); string cellvalue = ""; foreach (DataTable currentTable in outputDS.Tables) { outputString.Clear(); int firstrow = 0; foreach (string[] currentfilter in SheetFilters) { if (Regex.IsMatch(Regex.Replace(currentTable.TableName, @"[;\'$]", ""), "^" + currentfilter[0].Replace("*", ".*") + "$")) { if (!int.TryParse(currentfilter[1], out firstrow) && currentfilter[1] != "") { for (int i = 0; i < currentTable.Rows.Count; i++) { for (int j = 0; j < currentTable.Columns.Count; j++) { if (currentfilter[1] == currentTable.Rows[i].ItemArray[j].ToString()) { firstrow = i; } } } } break; } } for (int i = firstrow; i < currentTable.Rows.Count; i++) { cellvalue = ""; for (int j = 0; j < currentTable.Columns.Count; j++) { cellvalue += Regex.Replace(currentTable.Rows[i].ItemArray[j].ToString(), @"[;\'$|\r\n|\r|\n]", " ") + ";"; } outputString.AppendFormat("{0}", cellvalue.TrimEnd(';')); outputString.AppendLine(); } string Outputpath = Regex.Split(Destination, @"(.*[\\|\/])([^\\|\/]*)")[1]; //LogFile.write_LogFile("Saving excel output to: " + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[;\'$]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".txt"); try { File.AppendAllText(Outputpath + NamePrefix + currentFileInfo.Name.Split('.')[0] + "_" + Regex.Replace(currentTable.TableName, @"[:punct:]", "") + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv", outputString.ToString()); } catch (Exception exx) { LogFile.write_LogFile("Error saving output for file " + currentFileInfo.Name.Split('.')[0] + " at sheet " + Regex.Replace(currentTable.TableName, @"[:punct:]", "") + " with message: " + exx.Message); } } return(true); } } return(false); } catch (Exception e) { LogFile.write_LogFile("Error during excel conversion, with message: " + e.Message); LogFile.write_LogFile("File was added to list of processed files: " + m_filePath); return(true); } }