// this method loads the config file from a external json file
private static GraphConfigsBE LoadConfig(string configFileName)
{
GraphConfigsBE config = null;
try
{
// deserialze from JSON
config = JsonConvert.DeserializeObject <GraphConfigsBE>(File.ReadAllText(configFileName));
}
catch (Exception ex)
{
System.Console.WriteLine($"Error loading config: {ex}");
}
return(config);
}
/// <summary>
/// this method retrieves the latest log file from the RoboRio (using SFTP)
/// </summary>
/// <param name="config">The configuration.</param>
/// <returns>System.String.</returns>
/// <see cref="https://github.com/sshnet/SSH.NET/"/>
private static string CopyLatestFileFromRoboRio(GraphConfigsBE config)
{
string targetLogFilePathName = string.Empty;
try
{
// config SFTP connection
var connectionInfo = new ConnectionInfo(config.RoboRio.Ipv4Address,
config.RoboRio.Username,
new PasswordAuthenticationMethod(config.RoboRio.Username, config.RoboRio.Password),
new PrivateKeyAuthenticationMethod("rsa.key"));
// create a sftp client using the connection params
using (var sftpClient = new SftpClient(connectionInfo))
{
// connect
sftpClient.Connect();
// get a list of the remote files
var remoteFiles = sftpClient.ListDirectory(config.RoboRio.LogFileFolder);
Dictionary <DateTime, RoboRIOLogFileBE> logFiles = new Dictionary <DateTime, RoboRIOLogFileBE>();
// loop thru each log file
foreach (SftpFile file in remoteFiles)
{
// skip directories
if (file.IsDirectory)
{
continue;
}
// skip empty files
if (file.Length == 0)
{
continue;
}
// skip files with the wrong extension
if (Path.GetExtension(file.Name).ToLower() != config.LogFileExtension.ToLower())
{
continue;
}
// add file to dictionary
logFiles.Add(file.LastWriteTime, new RoboRIOLogFileBE()
{
FileName = file.Name,
FilePathName = file.FullName,
LastModDT = file.LastWriteTime
});
}
// sort the list in descending order and pink the newest filename
RoboRIOLogFileBE latestLogFile = logFiles.OrderByDescending(f => f.Key).First().Value;
// build target file path name
targetLogFilePathName = System.IO.Path.Combine(config.LocalWorkingFolder, latestLogFile.FileName);
//see if we already have this file
if (!System.IO.File.Exists(targetLogFilePathName))
{
// download the most recent file
using (Stream fileStream = File.Create(targetLogFilePathName))
{
System.Console.WriteLine();
System.Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine($"... Now downloading latest log file: [{targetLogFilePathName}]");
System.Console.ResetColor();
sftpClient.DownloadFile(latestLogFile.FilePathName, fileStream);
}
}
else
{
System.Console.WriteLine();
System.Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"... Latest log file: [{targetLogFilePathName}] already downloaded");
System.Console.ResetColor();
}
}
}
catch (Exception ex)
{
System.Console.WriteLine($"Error downloading latest file from RoboRIO: {ex}");
}
return(targetLogFilePathName);
}
public static string ProcessLogFile(string logFilePathName, string graphSetName, GraphConfigsBE config)
{
// Activate SpreadsheetGear
SpreadsheetGear.Factory.SetSignedLicense("SpreadsheetGear.License, Type=Trial, Product=BND, Expires=2019-07-27, Company=Tom Bruns, [email protected], Signature=orH+RFO9hRUB8SJXBSWQZJuXP9OfSkV9fLcU9suehfgA#dgunwBK9VssTgnfowKGWaqMNfVgwVetxEWbayzGM1uIA#K");
// Create a new empty workbook in a new workbook set.
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
// import the csv file
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(logFilePathName);
// get a reference to the active (only) worksheet
SpreadsheetGear.IWorksheet dataWorksheet = workbook.ActiveWorksheet;
dataWorksheet.Name = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);
// freeze 1st row & 1st column(to make scrolling more user friendly)
dataWorksheet.WindowInfo.ScrollColumn = 0;
dataWorksheet.WindowInfo.SplitColumns = 1;
dataWorksheet.WindowInfo.ScrollRow = 0;
dataWorksheet.WindowInfo.SplitRows = 1;
dataWorksheet.WindowInfo.FreezePanes = true;
// build index of column names
var columnNameXref = BuildColumnNameXref(dataWorksheet);
// find the config for the requested Set of Graphs
GraphSetBE graphSet = config.GraphSets.Where(gs => gs.SetName.ToLower() == graphSetName.ToLower()).FirstOrDefault();
if (graphSet == null)
{
List <string> availableGraphSetNames = config.GraphSets.Select(gs => gs.SetName).ToList();
throw new ApplicationException($"Requested GraphSet: [{graphSetName}], Options: [{String.Join(",", availableGraphSetNames)}]");
}
// do any required conversions on the source data (ex Radians to Degrees)
if (graphSet.AngleConversions != null)
{
foreach (AngleConversionBE angleConversion in graphSet.AngleConversions)
{
PerformAngleConversion(dataWorksheet, angleConversion, columnNameXref);
}
// rebuild column name index
columnNameXref = BuildColumnNameXref(dataWorksheet);
}
// resize column widths to fit header text
dataWorksheet.UsedRange.Columns.AutoFit();
// ====================================
// create any new sheets with a subset of the original columns to make analysis easier
// ====================================
foreach (NewSheetBE newSheet in graphSet.NewSheets)
{
BuildNewSheet(dataWorksheet, newSheet, columnNameXref);
}
string pathNameColumnName = graphSet.PathNameColumnName;
// ====================================
// build a new line graph for each one in the selected graphset
// ====================================
foreach (LineGraphBE lineGraph in graphSet.LineGraphs)
{
BuildLineGraph(dataWorksheet, lineGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new XY graph for each one in the selected graphset
// fyi: these were separated because they require slightly different config data structures
// ====================================
foreach (XYGraphBE xyGraph in graphSet.XYGraphs)
{
BuildXYGraph(dataWorksheet, xyGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new bar graph for each one in the selected graphset
// ====================================
foreach (BarGraphBE barGraph in graphSet.BarGraphs)
{
BuildBarGraph(dataWorksheet, barGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new histogram for each one in the selected graphset
// ====================================
foreach (HistogramBE histogram in graphSet.Histograms)
{
BuildHistogram(dataWorksheet, histogram, columnNameXref, pathNameColumnName);
}
// save the workbook
string pathName = GetCellValue <string>(dataWorksheet, graphSet.PathNameColumnName, 1, columnNameXref);
string folderPathName = System.IO.Path.GetDirectoryName(logFilePathName);
string fileName = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);
fileName = (!string.IsNullOrEmpty(pathName)) ? $"{fileName}_{pathName}" : fileName;
fileName = System.IO.Path.ChangeExtension(fileName, @".xlsx");
string xlsFilePathName = System.IO.Path.Combine(folderPathName, fileName);
workbook.SaveAs(xlsFilePathName, FileFormat.OpenXMLWorkbook);
return(xlsFilePathName);
}
