Rob van der Woude's Scripting Pages
Powered by GeSHi

Source code for printexcel.cs

(view source code of printexcel.cs as plain text)

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Drawing.Printing;
  4. using System.IO;
  5. using System.Text.RegularExpressions;
  6. using Microsoft.Win32;
  7. using Excel = Microsoft.Office.Interop.Excel;
  8.  
  9.  
  10. namespace RobvanderWoude
  11. {
  12. 	class PrintExcel
  13. 	{
  14. 		static string progver = "1.00";
  15.  
  16.  
  17. 		static Excel.Application excelapp;
  18. 		static Excel.Workbook excelworkbook;
  19.  
  20.  
  21. 		static int Main( string[] args )
  22. 		{
  23. 			string file;
  24. 			string sheet;
  25. 			string rangestart;
  26. 			string rangeend;
  27. 			string printer = new PrinterSettings( ).PrinterName; // Default Printer
  28. 			bool usedefaultprinter = true;
  29.  
  30. 			switch ( args.Length )
  31. 			{
  32. 				case 2:
  33. 					file = args[0];
  34. 					sheet = args[1];
  35. 					break;
  36. 				case 3:
  37. 					file = args[0];
  38. 					sheet = args[1];
  39. 					printer = args[2];
  40. 					usedefaultprinter = false;
  41. 					break;
  42. 				case 4:
  43. 					file = args[0];
  44. 					sheet = args[1];
  45. 					rangestart = args[2];
  46. 					rangeend = args[3];
  47. 					break;
  48. 				case 5:
  49. 					file = args[0];
  50. 					sheet = args[1];
  51. 					rangestart = args[2];
  52. 					rangeend = args[3];
  53. 					printer = args[4];
  54. 					usedefaultprinter = false;
  55. 					break;
  56. 				default:
  57. 					return ShowHelp( );
  58. 			}
  59.  
  60. 			// Check if Excel file exists
  61. 			if ( !File.Exists( file ) )
  62. 			{
  63. 				return ShowHelp( "File not found: \"{0}\"", file );
  64. 			}
  65.  
  66. 			// Check if printer exists
  67. 			bool validprinter = false;
  68. 			foreach ( string prn in PrinterSettings.InstalledPrinters )
  69. 			{
  70. 				if ( prn.ToUpper( ) == printer.ToUpper( ) )
  71. 				{
  72. 					validprinter = true;
  73. 				}
  74. 			}
  75. 			if ( !validprinter )
  76. 			{
  77. 				return ShowHelp( "Printer not found: \"{0}\"", printer );
  78. 			}
  79.  
  80. 			// Open Excel
  81. 			excelapp = new Excel.Application( );
  82. 			excelapp.Visible = false;
  83. 			excelapp.Visible = true;
  84. 			excelworkbook = excelapp.Workbooks.Open( file );
  85.  
  86. 			// Change printer - must be done AFTER opening the excel workbook
  87. 			if ( !usedefaultprinter )
  88. 			{
  89. 				// Format printer string for Excel, e.g. "HPLaserJet on Ne01:"
  90. 				string prnspl = Registry.GetValue( @"HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices", printer, null ).ToString( );
  91. 				if ( !String.IsNullOrEmpty( prnspl ) )
  92. 				{
  93. 					if ( prnspl.IndexOf( ',' ) > 0 )
  94. 					{
  95. 						prnspl = prnspl.Substring( prnspl.IndexOf( ',' ) + 1 );
  96. 						if ( !String.IsNullOrEmpty( prnspl ) )
  97. 						{
  98. 							printer = String.Format( "{0} on {1}", printer, prnspl );
  99. 						}
  100. 					}
  101. 				}
  102. 				try
  103. 				{
  104. 					excelapp.ActivePrinter = printer;
  105. 				}
  106. 				catch ( Exception e )
  107. 				{
  108. 					try
  109. 					{
  110. 						excelworkbook.Close( );
  111. 					}
  112. 					catch { }
  113. 					try
  114. 					{
  115. 						excelapp.Quit( );
  116. 					}
  117. 					catch { }
  118. 					return ShowHelp( e.Message );
  119. 				}
  120. 			}
  121.  
  122. 			if ( Regex.IsMatch( sheet, @"^\d+$" ) )
  123. 			{
  124. 				int index = int.Parse( sheet );
  125. 				if ( index <= excelworkbook.Sheets.Count )
  126. 				{
  127. 					ExcelPrint( index );
  128. 				}
  129. 				else
  130. 				{
  131. 					excelworkbook.Close( );
  132. 					excelapp.Quit( );
  133. 					return ShowHelp( "Sheet index out of bound: {0} (maximum for this workbook: {1})", index.ToString( ), excelworkbook.Sheets.Count.ToString( ) );
  134. 				}
  135. 			}
  136. 			else
  137. 			{
  138. 				bool validsheetname = true;
  139. 				for ( int i = 1; i <= excelworkbook.Sheets.Count; i++ )
  140. 				{
  141. 					Excel.Worksheet ws = excelworkbook.Worksheets[i];
  142. 					if ( ws.Name.ToUpper( ) == sheet.ToUpper( ) )
  143. 					{
  144. 						validsheetname = true;
  145. 					}
  146. 				}
  147. 				if ( validsheetname )
  148. 				{
  149. 					ExcelPrint( sheet );
  150. 				}
  151. 				else
  152. 				{
  153. 					excelworkbook.Close( );
  154. 					excelapp.Quit( );
  155. 					return ShowHelp( "No matching worksheet found: \"{0}\"", sheet );
  156. 				}
  157. 			}
  158.  
  159. 			excelworkbook.Close( );
  160. 			excelapp.Quit( );
  161.  
  162. 			return 0;
  163. 		}
  164.  
  165.  
  166. 		static bool ExcelPrint( string tab = "Sheet1", string start = null, string end = null )
  167. 		{
  168. 			// Print all sheets if "name" is "/ALL"
  169. 			if ( tab.ToUpper( ) == "/ALL" )
  170. 			{
  171. 				bool success = true;
  172. 				for ( int i = 1; i <= excelworkbook.Sheets.Count; i++ )
  173. 				{
  174. 					Excel.Worksheet sheet = excelworkbook.Worksheets[i];
  175. 					if ( sheet.Name.ToUpper( ) == tab.ToUpper( ) )
  176. 					{
  177. 						success = success && ExcelPrint( i, start, end );
  178. 					}
  179. 				}
  180. 				return success;
  181. 			}
  182. 			else
  183. 			{
  184. 				// Print sheet with matching name
  185. 				for ( int i = 1; i <= excelworkbook.Sheets.Count; i++ )
  186. 				{
  187. 					Excel.Worksheet sheet = excelworkbook.Worksheets[i];
  188. 					if ( sheet.Name.ToUpper( ) == tab.ToUpper( ) )
  189. 					{
  190. 						return ExcelPrint( i, start, end );
  191. 					}
  192. 				}
  193. 			}
  194. 			return false;
  195. 		}
  196.  
  197.  
  198. 		static bool ExcelPrint( int tab = 1, string start = null, string end = null )
  199. 		{
  200. 			try
  201. 			{
  202. 				Excel.Worksheet sheet = excelworkbook.Worksheets[tab];
  203. 				Excel.Range range;
  204. 				if ( String.IsNullOrEmpty( start ) || String.IsNullOrEmpty( end ) )
  205. 				{
  206. 					range = sheet.UsedRange;
  207. 				}
  208. 				else
  209. 				{
  210. 					range = sheet.get_Range( start, end );
  211. 				}
  212. 				range.PrintOutEx( );
  213. 				return true;
  214. 			}
  215. 			catch ( Exception )
  216. 			{
  217. 				return false;
  218. 			}
  219. 		}
  220.  
  221.  
  222. 		static int ShowHelp( params string[] errmsg )
  223. 		{
  224. 			/*
  225. 			PrintExcel.exe,  Version 1.00
  226. 			Command line Excel sheet printing tool
  227.  
  228. 			Usage:   PrintExcel.exe  file  sheet  [ firstcell  lastcell ]  [ printer ]
  229.  
  230. 			Where:   file         is the Excel file to be printed
  231. 			         sheet        is the sheet name or (1 based) index to be printed
  232. 			         firstcell    is the first cell of the range to be printed
  233. 			         lastcell     is the last cell of the range to be printed
  234. 			         printer      is the printer name
  235.  
  236. 			Notes:   Instead of a sheet name/index, /ALL can be used to print all sheets.
  237. 			         The default range to be printed is the sheet's "used range".
  238. 			         If no printer is specified, the default printer will be used.
  239. 			         Return code is 1 if an error is detected, otherwise 0.
  240.  
  241. 			Example: PRINTEXCEL  sales.xls  Sheet1  A1  F45  "LaserJet Sales 1st Floor"
  242.  
  243. 			Written by Rob van der Woude
  244. 			http://www.robvanderwoude.com
  245. 			*/
  246.  
  247.  
  248. 			if ( errmsg.Length > 0 )
  249. 			{
  250. 				List<string> errargs = new List<string>( errmsg );
  251. 				errargs.RemoveAt( 0 );
  252. 				Console.Error.WriteLine( );
  253. 				Console.ForegroundColor = ConsoleColor.Red;
  254. 				Console.Error.Write( "ERROR:\t" );
  255. 				Console.ForegroundColor = ConsoleColor.White;
  256. 				Console.Error.WriteLine( errmsg[0], errargs.ToArray( ) );
  257. 				Console.ResetColor( );
  258. 			}
  259.  
  260. 			Console.Error.WriteLine( );
  261.  
  262. 			Console.Error.WriteLine( "PrintExcel.exe,  Version {0}", progver );
  263.  
  264. 			Console.Error.WriteLine( "Command line Excel sheet printing tool" );
  265.  
  266. 			Console.Error.WriteLine( );
  267.  
  268. 			Console.Error.Write( "Usage:   " );
  269. 			Console.ForegroundColor = ConsoleColor.White;
  270. 			Console.Error.WriteLine( "PrintExcel.exe  file  sheet  [ firstcell  lastcell ]  [ printer ]" );
  271. 			Console.ResetColor( );
  272.  
  273. 			Console.Error.WriteLine( );
  274.  
  275. 			Console.Error.Write( "Where:   " );
  276. 			Console.ForegroundColor = ConsoleColor.White;
  277. 			Console.Error.Write( "file" );
  278. 			Console.ResetColor( );
  279. 			Console.Error.WriteLine( "         is the Excel file to be printed" );
  280.  
  281. 			Console.ForegroundColor = ConsoleColor.White;
  282. 			Console.Error.Write( "         sheet" );
  283. 			Console.ResetColor( );
  284. 			Console.Error.WriteLine( "        is the sheet name or (1 based) index to be printed" );
  285.  
  286. 			Console.ForegroundColor = ConsoleColor.White;
  287. 			Console.Error.Write( "         firstcell" );
  288. 			Console.ResetColor( );
  289. 			Console.Error.WriteLine( "    is the first cell of the range to be printed" );
  290.  
  291. 			Console.ForegroundColor = ConsoleColor.White;
  292. 			Console.Error.Write( "         lastcell" );
  293. 			Console.ResetColor( );
  294. 			Console.Error.WriteLine( "     is the last cell of the range to be printed" );
  295.  
  296. 			Console.ForegroundColor = ConsoleColor.White;
  297. 			Console.Error.Write( "         printer" );
  298. 			Console.ResetColor( );
  299. 			Console.Error.WriteLine( "      is the printer name" );
  300.  
  301. 			Console.Error.WriteLine( );
  302.  
  303. 			Console.Error.Write( "Notes:   Instead of a sheet name/index, " );
  304. 			Console.ForegroundColor = ConsoleColor.White;
  305. 			Console.Error.Write( "/ALL" );
  306. 			Console.ResetColor( );
  307. 			Console.Error.WriteLine( " can be used to print all sheets." );
  308.  
  309. 			Console.Error.WriteLine( "         The default range to be printed is the sheet's \"used range\"." );
  310.  
  311. 			Console.Error.WriteLine( "         If no printer is specified, the default printer will be used." );
  312.  
  313. 			Console.Error.WriteLine( "         Return code is 1 if an error is detected, otherwise 0." );
  314.  
  315. 			Console.Error.WriteLine( );
  316.  
  317. 			Console.Error.WriteLine( "Example: PRINTEXCEL  sales.xls  Sheet1  A1  F45  \"LaserJet Sales 1st Floor\"" );
  318.  
  319. 			Console.Error.WriteLine( );
  320.  
  321. 			Console.Error.WriteLine( "Written by Rob van der Woude" );
  322.  
  323. 			Console.Error.WriteLine( "http://www.robvanderwoude.com" );
  324.  
  325.  
  326. 			return 1;
  327. 		}
  328. 	}
  329. }
  330.  

page last uploaded: 2017-08-21, 14:26