using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Text; using ExcelDataReader; namespace RobvanderWoude { class AirRegPHCmd { static readonly string progver = "1.00"; static readonly string progdir = Directory.GetParent( new Uri( Assembly.GetExecutingAssembly( ).CodeBase ).LocalPath ).FullName; static string sheet = string.Empty; static int Main( string[] args ) { int rc = 0; if ( args.Length == 0 ) { return ShowHelp( ); } string regdir = Path.Combine( progdir, "PH" ); if ( !Directory.Exists( regdir ) ) { return ShowHelp( "Directory \"{0}\" not found", regdir ); } foreach ( string file in Directory.GetFiles( regdir, "*.xlsx" ) ) { sheet = Path.GetFullPath( file ); } if ( string.IsNullOrEmpty( sheet ) ) { return ShowHelp( "No Excel sheet found in directory \"{0}\"", regdir ); } ICSharpCode.SharpZipLib.Zip.ZipConstants.DefaultCodePage = Encoding.UTF8.CodePage; foreach ( string arg in args ) { rc += AircraftData( arg ); } return rc; } public static int AircraftData( string reg ) { int rc = 0; using ( var stream = File.Open( sheet, FileMode.Open, FileAccess.Read ) ) { using ( var reader = ExcelReaderFactory.CreateReader( stream, new ExcelReaderConfiguration( ) { FallbackEncoding = Encoding.UTF8 } ) ) { DataSet dataset = reader.AsDataSet( new ExcelDataSetConfiguration( ) { UseColumnDataType = false, ConfigureDataTable = ( tableReader ) => new ExcelDataTableConfiguration( ) { EmptyColumnNamePrefix = "Column", UseHeaderRow = true } } ); var query = from aircraft in dataset.Tables[0].AsEnumerable( ) where aircraft.Field( "Registration" ).Replace( "-", "" ).ToUpper( ) == reg.Replace( "-", "" ).ToUpper( ) select new { registration = aircraft.Field( "Registration" ), manufacturer = aircraft.Field( "Manufacturer" ), model = aircraft.Field( "Model" ) }; foreach ( var airplane in query ) { rc += 1; Console.WriteLine( "{0}\t{1}\t{2}", airplane.registration, airplane.manufacturer, airplane.model ); } } return rc; } } #region Error Handling public static int ShowHelp( params string[] errmsg ) { #region Error Message if ( errmsg.Length > 0 ) { List errargs = new List( errmsg ); errargs.RemoveAt( 0 ); Console.Error.WriteLine( ); Console.ForegroundColor = ConsoleColor.Red; Console.Error.Write( "ERROR:\t" ); Console.ForegroundColor = ConsoleColor.White; Console.Error.WriteLine( errmsg[0], errargs.ToArray( ) ); Console.ResetColor( ); } #endregion Error Message #region Help Text /* AirRegPHCmd.exe, Version 1.00 Search downloaded Dutch aircraft database (PH-***) for specific aircraft(s) Usage: AirRegPHCmd.exe PH-*** [ PH-*** [ PH-*** [ ... ] ] ] Where: PH-*** is a Dutch aircraft registration "number" Notes: This program requires a downloaded aircraft registration file in Excel format, located in the subdirectory "PH"; the Excel file can be downloaded at: https://www.ilent.nl/onderwerpen/luchtvaartuigregister/documenten /publicaties/2019/05/27/luchtvaartuigregister-aircraft-registration This program requires ExcelDataReader.dll, ExcelDataReader.DataSet.dll and ICSharpCode.SharpZipLib.dll in the program's parent directory to read the Excel file; NuGet packages for these DLLs available at https://www.nuget.org/packages/ExcelDataReader/ and https://www.nuget.org/packages/ExcelDataReader.DataSet/ Make sure to add the packages under References to recompile this code. Command line arguments are case insensitive, dashes are not required. Output is tab-delimited Registration, Manufacturer and Model for each command line argument. Return code ("errorlevel") equals the number of registrations found or -1 in case of errors. Written by Rob van der Woude https://www.robvanderwoude.com */ Console.Error.WriteLine( ); Console.Error.WriteLine( "AirRegPHCmd.exe, Version {0}", progver ); Console.Error.WriteLine( "Search downloaded Dutch aircraft database (PH-***) for specific aircraft(s)" ); Console.Error.WriteLine( ); Console.Error.Write( "Usage: " ); Console.ForegroundColor = ConsoleColor.White; Console.Error.WriteLine( "AirRegPHCmd.exe PH-*** [ PH-*** [ PH-*** [ ... ] ] ]" ); Console.ResetColor( ); Console.Error.WriteLine( ); Console.Error.Write( "Where: " ); Console.ForegroundColor = ConsoleColor.White; Console.Error.Write( "PH-***" ); Console.ResetColor( ); Console.Error.WriteLine( " is a Dutch aircraft registration \"number\"" ); Console.Error.WriteLine( ); Console.Error.WriteLine( "Notes: This program requires a downloaded aircraft registration file in" ); Console.Error.WriteLine( " Excel format, located in the subdirectory \"PH\"; the Excel file" ); Console.Error.WriteLine( " can be downloaded at:" ); Console.ForegroundColor = ConsoleColor.DarkGray; Console.Error.WriteLine( " https://www.ilent.nl/onderwerpen/luchtvaartuigregister/documenten" ); Console.Error.WriteLine( " /publicaties/2019/05/27/luchtvaartuigregister-aircraft-registration" ); Console.ResetColor( ); Console.Error.WriteLine( " This program requires ExcelDataReader.dll, ExcelDataReader.DataSet.dll" ); Console.Error.WriteLine( " and ICSharpCode.SharpZipLib.dll in the program's parent directory" ); Console.Error.WriteLine( " to read the Excel file; NuGet packages for these DLLs available at" ); Console.ForegroundColor = ConsoleColor.DarkGray; Console.Error.Write( " https://www.nuget.org/packages/ExcelDataReader/" ); Console.ResetColor( ); Console.Error.WriteLine( " and" ); Console.ForegroundColor = ConsoleColor.DarkGray; Console.Error.WriteLine( " https://www.nuget.org/packages/ExcelDataReader.DataSet/" ); Console.ResetColor( ); Console.Error.WriteLine( " Make sure to add the packages under References to recompile this code." ); Console.Error.WriteLine( " Command line arguments are case insensitive, dashes are not required." ); Console.Error.WriteLine( " Output is tab-delimited Registration, Manufacturer and Model for each" ); Console.Error.WriteLine( " command line argument." ); Console.Error.WriteLine( " Return code (\"errorlevel\") equals the number of registrations found" ); Console.Error.WriteLine( " or -1 in case of errors." ); Console.Error.WriteLine( ); Console.Error.WriteLine( "Written by Rob van der Woude" ); Console.Error.WriteLine( "https://www.robvanderwoude.com" ); #endregion Help Text return -1; } #endregion Error Handling } }