using System;
using System.Collections.Generic;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;


namespace RobvanderWoude
{
	class Excel2Any
	{
		static string progver = "1.00";


		static int Main( string[] args )
		{
			#region Initialize Variables

			bool overwrite = false;
			string inputfilespec = null;
			string outputfilespec = null;
			string inputfolder;
			string outputfolder;
			string inputfile;
			string outputfile;
			string inputfilename;
			string outputfilename;
			string inputfileext;
			string outputfileext = null;
			int inputformat = -1;
			int outputformat = -1;

			#endregion Initialize Variables


			#region Command Line Parsing

			if ( args.Length == 0 || ( args.Length == 1 && args[0].ToUpper( ) != "/T" ) )
			{
				return ShowHelp( );
			}
			foreach ( string arg in args )
			{
				if ( arg[0] == '/' )
				{
					if ( arg.ToString( ).ToUpper( ) == "/O" )
					{
						if ( overwrite )
						{
							return ShowHelp( "Duplicate command line switch /O" );
						}
						overwrite = true;
						break;
					}
					else if ( arg.ToString( ).ToUpper( ) == "/T" )
					{
						ListFormats( );
						return 0;
					}
					else if ( arg.Length > 3 && arg.Substring( 0, 3 ).ToUpper( ) == "/T:" )
					{
						if ( outputformat != -1 )
						{
							return ShowHelp( "Duplicate command line switch /T" );
						}
						outputformat = GetFormat( arg.Substring( 3 ) );
						if ( outputformat == -1 )
						{
							return ShowHelp( "Output file type not recognized, use /T to list all available types" );
						}
					}
					else
					{
						return ShowHelp( "Invalid command line switch {0}", arg );
					}
				}
				else
				{
					if ( String.IsNullOrEmpty( inputfilespec ) )
					{
						inputfilespec = arg;
					}
					else if ( String.IsNullOrEmpty( outputfilespec ) )
					{
						outputfilespec = arg;
					}
					else
					{
						return ShowHelp( "Invalid command line argument \"{0}\"", arg );
					}
				}
			}

			#endregion Command Line Parsing


			#region Command Line Validation

			// Validate input filespec
			if ( String.IsNullOrEmpty( inputfilespec ) )
			{
				return ShowHelp( "Please specify an input file" );
			}
			switch ( ValidateFilespec( inputfilespec ) )
			{
				case -1:
					if ( inputfilespec.IndexOf( '*' ) == -1 )
					{
						return ShowHelp( "Parent folder of input file not found" );
					}
					else
					{
						return ShowHelp( "Parent folder of input files not found" );
					}
				case 0:
					if ( inputfilespec.IndexOf( '*' ) == -1 )
					{
						return ShowHelp( "Input file not found" );
					}
					else
					{
						return ShowHelp( "No matching input files found" );
					}
				case 1:
					break;
				default:
					if ( !String.IsNullOrEmpty( outputfilespec ) && Path.GetFileNameWithoutExtension( outputfilespec ) != "*" )
					{
						return ShowHelp( "When using wildcards in the input file names,\n\tyou must use wildcard \"*\" for the output file names, if specified" );
					}
					break;
			}
			inputfolder = Directory.GetParent( inputfilespec ).FullName;
			inputfile = Path.GetFileName( inputfilespec );
			inputfilename = Path.GetFileNameWithoutExtension( inputfilespec );
			inputfileext = Path.GetExtension( inputfilespec );
			inputformat = GetFormatByExtension( inputfilespec );

			// Validate or build output filespec
			if ( String.IsNullOrEmpty( outputfilespec ) )
			{
				if ( outputformat == -1 )
				{
					return ShowHelp( "Please specify output file(s) and/or valid output file type" );
				}
				outputfolder = inputfolder;
				outputfile = "*";
				outputfilename = "*";
				outputfilespec = Path.Combine( outputfolder, outputfile );
				// Extension will be default extension based on file type
			}
			else
			{
				outputfile = Path.GetFileName( outputfilespec );
				outputfilename = Path.GetFileNameWithoutExtension( outputfilespec );
				outputfileext = Path.GetExtension( outputfilespec );
				if ( outputfilespec.IndexOf( '\\' ) == -1 ) // e.g. "*.xps" or "filename.pdf"
				{
					outputfolder = inputfolder;
					outputfilespec = Path.Combine( outputfolder, outputfile );
				}
				outputfolder = Directory.GetParent( outputfilespec ).FullName;
				if ( ValidateFilespec( outputfilespec ) == -1 )
				{
					if ( outputfilespec.IndexOf( '*' ) == -1 )
					{
						return ShowHelp( "Parent folder for output file not found" );
					}
					else
					{
						return ShowHelp( "Parent folder for output files not found" );
					}
				}
			}
			if ( outputformat == -1 )
			{
				outputformat = GetFormatByExtension( outputfilespec );
			}

			// Input and output file types should be different
			if ( inputformat == outputformat )
			{
				return ShowHelp( "Input and output file types should be different" );
			}
			// Input and output extensions should be different
			if ( inputfileext == outputfileext )
			{
				return ShowHelp( "Input and output file extensions should be different" );
			}

			#endregion Command Line Validation


			#region Iterate File List and Convert Each File

			int rc = 0;

			foreach ( string file in Directory.GetFiles( inputfolder, inputfile ) )
			{
				if ( Path.GetExtension( file ) == inputfileext ) // prevent including *.docx when *.doc is specified
				{
					string output;
					if ( inputfilename.IndexOf( '*' ) > -1 )
					{
						output = Path.Combine( outputfolder, Path.GetFileNameWithoutExtension( file ) + outputfileext );
					}
					else
					{
						output = outputfilespec;
					}
					if ( File.Exists( output ) && !overwrite )
					{
						if ( inputfilename.IndexOf( '*' ) > -1 )
						{
							Console.WriteLine( "Skipped \"{0}\" because \"{1}\" already exists", Path.GetFileName( file ), Path.GetFileName( output ) );
						}
						else
						{
							return ShowHelp( "Output file \"{0}\" already exists, use /O to silently overwrite existing files", Path.GetFileName( output ) );
						}
					}
					else
					{
						Console.Write( "Converting \"{0}\" . . . ", Path.GetFileName( file ) );
						if ( ExcelConvert( file, output, outputformat ) )
						{
							Console.WriteLine( "Success" );
						}
						else
						{
							Console.WriteLine( "Failed" );
							rc += 1;
						}
					}
				}
			}

			#endregion Iterate File List and Convert Each File


			return rc;
		}


		// Opens the specified spreadsheet and saves or exports it with the specified file name and file type
		static bool ExcelConvert( string inputpath, string outputpath, int outputtype )
		{
			bool error = true;
			string outputname = Path.GetFileNameWithoutExtension( outputpath );
			if ( outputname == "*" )
			{
				outputpath = Path.Combine( Directory.GetParent( outputpath ).FullName, Path.GetFileNameWithoutExtension( inputpath ) );
			}
			try
			{
				Excel.Application excelapp = new Excel.Application( );
				excelapp.Visible = false;
				excelapp.Visible = true;
				Excel.Workbook excelworkbook = excelapp.Workbooks.Open( inputpath );
				try
				{
					// export as pdf or xps
					Excel.XlFixedFormatType type = (Excel.XlFixedFormatType) outputtype;
					excelworkbook.ExportAsFixedFormat( type, outputpath );
					error = false;
				}
				catch
				{
					try
					{
						// save as ...
						Excel.XlFileFormat type = (Excel.XlFileFormat) outputtype;
						excelworkbook.SaveAs( outputpath, type );
						error = false;
					}
					catch { }
				}
				excelworkbook.Close( );
				excelapp.Quit( );
			}
			catch { }
			return !error;
		}


		// Returns the numeric representation of the Excel file or export format for the specified extension
		static int GetFormatByExtension( string file )
		{
			string ext = Path.GetExtension( file ).ToLower( ).Substring( 1 );
			Dictionary<string, int> knownexcelexts = new Dictionary<string, int>( );
			knownexcelexts["csv"] = (int) Excel.XlFileFormat.xlCSV;
			knownexcelexts["htm"] = (int) Excel.XlFileFormat.xlHtml;
			knownexcelexts["html"] = (int) Excel.XlFileFormat.xlHtml;
			knownexcelexts["ods"] = (int) Excel.XlFileFormat.xlOpenDocumentSpreadsheet;
			knownexcelexts["pdf"] = (int) Excel.XlFixedFormatType.xlTypePDF;
			knownexcelexts["txt"] = (int) Excel.XlFileFormat.xlTextWindows;
			knownexcelexts["xls"] = (int) Excel.XlFileFormat.xlWorkbookDefault;
			knownexcelexts["xlsm"] = (int) Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
			knownexcelexts["xlsx"] = (int) Excel.XlFileFormat.xlOpenXMLWorkbook;
			knownexcelexts["xps"] = (int) Excel.XlFixedFormatType.xlTypeXPS;
			if ( knownexcelexts.ContainsKey( ext ) )
			{
				return knownexcelexts[ext];
			}
			return -1;
		}


		// Returns the numeric representation for the specified Excel file or export format
		static int GetFormat( string format )
		{
			// test for numeric fomat (type number)
			try { return (int) (Excel.XlFileFormat) Convert.ToInt32( format ); }
			catch { }
			// test for numeric fomat (pdf/xps)
			try { return (int) (Excel.XlFixedFormatType) Convert.ToInt32( format ); }
			catch { }
			// test for string format (type name)
			for ( int i = 0; i < 64; i++ )
			{
				// all types except pdf/xps
				try
				{
					if ( ( (Excel.XlFileFormat) i ).ToString( ) != i.ToString( ) )
					{
						string type = ( (Excel.XlFileFormat) i ).ToString( );
						if ( format.ToUpper( ) == type.ToUpper( ) )
						{
							return (int) (Excel.XlFileFormat) i;
						}
					}
				}
				catch { }
				// pdf/xps
				try
				{
					if ( ( (Excel.XlFixedFormatType) i ).ToString( ) != i.ToString( ) )
					{
						string type = ( (Excel.XlFixedFormatType) i ).ToString( );
						if ( format.ToUpper( ) == type.ToUpper( ) )
						{
							return (int) (Excel.XlFixedFormatType) i;
						}
					}
				}
				catch { }
			}
			// return -1 if format not valid
			return -1;
		}


		// Lists all available file formats as string (name) and number
		static void ListFormats( )
		{
			int maxlen = 0;
			for ( int i = 0; i < 64; i++ )
			{
				if ( ( (Excel.XlFileFormat) i ).ToString( ).Length > maxlen )
				{
					maxlen = ( (Excel.XlFileFormat) i ).ToString( ).Length;
				}
				if ( ( (Excel.XlFixedFormatType) i ).ToString( ).Length > maxlen )
				{
					maxlen = ( (Excel.XlFixedFormatType) i ).ToString( ).Length;
				}
			}
			ConsoleColor bgblue = ConsoleColor.DarkBlue;
			ConsoleColor bgdefault = Console.BackgroundColor;
			Console.ForegroundColor = ConsoleColor.White;
			Console.WriteLine( String.Format( "{0,-" + maxlen + "}  {1}", "File Type", "Number" ) );
			if ( maxlen > 12 )
			{
				Console.WriteLine( new String( '=', 12 ) + new String( ' ', maxlen - 12 + 2 ) + new String( '=', 6 ) );
			}
			else
			{
				Console.WriteLine( new String( '=', maxlen ) + "  " + new String( '=', 6 ) );
			}
			int linenum = 0;
			for ( int i = 0; i < 64; i++ )
			{
				if ( ( (Excel.XlFileFormat) i ).ToString( ) != i.ToString( ) )
				{
					if ( linenum % 2 == 1 )
					{
						Console.BackgroundColor = bgblue;
					}
					Console.Write( String.Format( "{0,-" + maxlen + "}  {1,4}  ", (Excel.XlFileFormat) i, i ) );
					if ( linenum % 2 == 1 )
					{
						Console.BackgroundColor = bgdefault;
					}
					Console.WriteLine( );
					linenum += 1;
				}
				if ( ( (Excel.XlFixedFormatType) i ).ToString( ) != i.ToString( ) )
				{
					if ( linenum % 2 == 1 )
					{
						Console.BackgroundColor = bgblue;
					}
					Console.Write( String.Format( "{0,-" + maxlen + "}  {1,4}  ", (Excel.XlFixedFormatType) i, i ) );
					if ( linenum % 2 == 1 )
					{
						Console.BackgroundColor = bgdefault;
					}
					Console.WriteLine( );
					linenum += 1;
				}
			}
			Console.ResetColor( );
		}


		// Displays help text
		static int ShowHelp( params string[] errmsg )
		{
			/*
			Excel2Any,  Version 1.00
			Open a spreadsheet in Microsoft Excel and save it in "any" (known) format
 
			Usage:     EXCEL2ANY   "workbook"  [ "outfile" ]  [ options ]
 
			Where:     "workbook"  Excel document(s) to be converted (wildcard "*" allowed
			                       in file name, e.g. "name*.xlsx")
			           "outfile"   output file(s) to be created (wildcard "*" allowed for
			                       file name, e.g. "*.ods" or "*.pdf")
			Options:   /O          silently overwrite existing output file(s)
			                       (default: abort or skip if output file exists)
			           /T          list available output file types
			           /T:type     set output file type (required if "outfile" is not
			                       specified; type may be number or string)
 
			Notes: [1] This program requires a "regular" (MSI based) Microsoft Excel
			           (2007 or later) installation, it will fail on an MS Office
			           "click-to-run" installation.
			       [2] For Excel 2007, to save as PDF or XPS, this program requires the
			           "Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office
			           programs", available at:
			           http://www.microsoft.com/en-us/download/details.aspx?id=7
			       [3] If wildcards are used in the Excel file names, and the output file
			           path is not specified, /T:type must be used, and the input file
			           names should not contain dots.
			       [4] If wildcards are used in the Excel file names, and the output file
			           path is specified, the output file name must be "*".
			       [5] If wildcards are used in the Excel file names, and the /O switch
			           is not used, the program will display an error message in case an
			           output file already exists, but it will then continue to convert
			           the next file instead of aborting.
			       [6] If Excel was already active when this program is started, any
			           other opened spreadsheet(s) will be left alone, and only the
			           spreadsheet(s) opened by this program will be closed.

			Examples:  EXCEL2ANY "D:\folder\myfile.xls" *.pdf
			           will save to "D:\folder\myfile.pdf"

			           EXCEL2ANY "D:\folder\myfile.xlsx" "D:\otherfolder\*.ods"
			           will save to "D:\otherfolder\myfile.ods"

			           EXCEL2ANY "D:\folder\myfile.xlsx" "D:\elsewhere\newfile.xps"
			           will save to "D:\elsewhere\newfile.xps"

			           EXCEL2ANY "D:\folder\name*.xlsx" *.html
			           will save all matching files as HTML to "D:\folder\"
			           recognized extensions: csv, htm(l), ods, pdf, txt, xls(x), xlsm, xps

			           EXCEL2ANY "D:\folder\*.xlsx" /T:8
			           like previous example, but more file types available

			           EXCEL2ANY /T
			           will list all available file types
 
			Written by Rob van der Woude
			http://www.robvanderwoude.com
			*/

			if ( errmsg.Length > 0 )
			{
				List<string> errargs = new List<string>( 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( );
			}

			Console.Error.WriteLine( );

			Console.Error.WriteLine( "Excel2Any,  Version {0}", progver );

			Console.Error.WriteLine( "Open a spreadsheet in Microsoft Excel and save it in \"any\" (known) format" );

			Console.Error.WriteLine( );

			Console.Error.Write( "Usage:     " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "EXCEL2ANY    \"workbook\"  [ \"outfile\" ]  [ options ]" );
			Console.ResetColor( );

			Console.Error.WriteLine( );

			Console.Error.Write( "Where:     " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "\"workbook\"" );
			Console.ResetColor( );
			Console.Error.WriteLine( "  Excel document(s) to be converted (wildcard \"*\" allowed" );

			Console.Error.WriteLine( "                       in file name, e.g. \"name*.xlsx\")" );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "           \"outfile\"" );
			Console.ResetColor( );
			Console.Error.WriteLine( "   output file(s) to be created (wildcard \"*\" allowed for" );

			Console.Error.WriteLine( "                       file name, e.g. \"*.ods\" or \"*.pdf\")" );

			Console.Error.Write( "Options:   " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "/O" );
			Console.ResetColor( );
			Console.Error.Write( "          silently " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "O" );
			Console.ResetColor( );
			Console.Error.WriteLine( "verwrite existing output file(s)" );

			Console.Error.WriteLine( "                       (default: abort or skip if output file exists)" );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "           /T" );
			Console.ResetColor( );
			Console.Error.Write( "          list available output file " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "T" );
			Console.ResetColor( );
			Console.Error.WriteLine( "ypes" );


			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "           /T:type" );
			Console.ResetColor( );
			Console.Error.Write( "     set output file " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "T" );
			Console.ResetColor( );
			Console.Error.Write( "ype (required if " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "\"outfile\"" );
			Console.ResetColor( );
			Console.Error.WriteLine( " is not" );

			Console.Error.Write( "                       specified; " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "type" );
			Console.ResetColor( );
			Console.Error.WriteLine( " may be number or string)" );

			Console.Error.WriteLine( );

			Console.Error.WriteLine( "Notes: [1] This program requires a \"regular\" (MSI based) Microsoft Excel" );

			Console.Error.WriteLine( "           (2007 or later) installation, it will fail on an MS Office" );

			Console.Error.WriteLine( "           \"click-to-run\" installation" );

			Console.Error.WriteLine( "       [2] For Excel 2007, to save as PDF or XPS, this program requires the" );

			Console.Error.WriteLine( "           \"Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office" );

			Console.Error.WriteLine( "           programs\", available at:" );

			Console.Error.WriteLine( "           http://www.microsoft.com/en-us/download/details.aspx?id=7" );

			Console.Error.WriteLine( "       [3] If wildcards are used in the Excel file names, and the output file" );

			Console.Error.Write( "           path is not specified, " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "/T:type" );
			Console.ResetColor( );
			Console.Error.WriteLine( " must be used, and the input file" );

			Console.Error.WriteLine( "           names should not contain dots." );

			Console.Error.WriteLine( "       [4] If wildcards are used in the Excel file names, and the output file" );

			Console.Error.WriteLine( "           path is specified, the output file name must be \"*\"." );

			Console.Error.Write( "       [5] If wildcards are used in the Excel file names, and the " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.Write( "/O" );
			Console.ResetColor( );
			Console.Error.WriteLine( " switch" );

			Console.Error.WriteLine( "           is not used, the program will display an error message in case an" );

			Console.Error.WriteLine( "           output file already exists, but it will then continue to convert" );

			Console.Error.WriteLine( "           the next file instead of aborting." );

			Console.Error.WriteLine( "       [6] If Excel was already active when this program is started, any" );

			Console.Error.WriteLine( "           other opened spreadsheet(s) will be left alone, and only the" );

			Console.Error.WriteLine( "           spreadsheet(s) opened by this program will be closed." );

			Console.Error.WriteLine( );

			Console.Error.Write( "Examples:  " );
			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "EXCEL2ANY \"D:\\folder\\myfile.xls\" *.pdf" );
			Console.ResetColor( );

			Console.Error.WriteLine( "           will save to \"D:\\folder\\myfile.pdf\"" );

			Console.Error.WriteLine( );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "           EXCEL2ANY \"D:\\folder\\myfile.xlsx\" \"D:\\otherfolder\\*.ods\"" );
			Console.ResetColor( );

			Console.Error.WriteLine( "           will save to \"D:\\otherfolder\\myfile.ods\"" );

			Console.Error.WriteLine( );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "           EXCEL2ANY \"D:\\folder\\myfile.xlsx\" \"D:\\elsewhere\\newfile.xps\"" );
			Console.ResetColor( );

			Console.Error.WriteLine( "           will save to \"D:\\elsewhere\\newfile.xps\"" );

			Console.Error.WriteLine( );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "           EXCEL2ANY \"D:\\folder\\name*.xlsx\" *.html" );
			Console.ResetColor( );

			Console.Error.WriteLine( "           will save all matching files as HTML to \"D:\\folder\\\"" );

			Console.Error.WriteLine( "           recognized extensions: csv, htm(l), ods, pdf, txt, xls(x), xlsm, xps" );

			Console.Error.WriteLine( );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "           EXCEL2ANY \"D:\\folder\\*.xlsx\" /T:8" );
			Console.ResetColor( );

			Console.Error.WriteLine( "           like previous example, but more file types available" );

			Console.Error.WriteLine( );

			Console.ForegroundColor = ConsoleColor.White;
			Console.Error.WriteLine( "           EXCEL2ANY /T" );
			Console.ResetColor( );

			Console.Error.WriteLine( "           will list all available file types" );

			Console.Error.WriteLine( );

			Console.Error.WriteLine( "Written by Rob van der Woude" );

			Console.Error.WriteLine( "http://www.robvanderwoude.com" );

			return 1;
		}


		// Returns -1 if folder not found, 0 if no matching files found, otherwise the matching file count
		static int ValidateFilespec( string filespec )
		{
			int matchingfiles = -1;
			try
			{
				string parentfolder = Directory.GetParent( filespec ).FullName;
				if ( Directory.Exists( parentfolder ) )
				{
					matchingfiles = 0;
					foreach ( string matchingfile in Directory.GetFiles( parentfolder, Path.GetFileName( filespec ) ) )
					{
						matchingfiles += 1;
					}
				}
			}
			catch { };
			return matchingfiles;
		}
	}
}