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);
}
}
