Programatically Reordering Excel Columns

My task is to reorder the columns in an Excel spreadsheet.  The files are generally quite large (75-100K rows, up to 200 columns).

My first inclination was, as usual, EPPlus.  It took a bit of doing, but I got it working.  Only it was slow.  Really slow.  It will stream properly and not overload memory, but too slow.

After reading this post that mentioned the benefits of using Excel automation, I thought I'd give that a try.  I was amazed.  Even though I'm not actually saving the resulting file until after I've copy/pasted all columns of data in, I never see the memory spike.  There's some really good memory management in there.  Yes, having a dependency on a COM object isn't optimal, but when we're talking Excel, I think it's acceptable.  It freakin rocks, and the code is very simple.

Anyway, I'm including both here (if you have a need for this and your files aren't large, you might want to use EPPlus).

In either case the call is like:
EPPlus version:

Helpers:
Main Class:
Excel Automation version:

MP3 Player Hack (one way Windows 8 on a tablet is so useful)

I have a few requirements for an MP3 player on my tablet:

• I need playlists.  Pretty basic.
• I need a large area that, when tapped, will pause play.
• Another fairly large area that will rewind 15-30 seconds.
• I need the app to remember where I left off and resume there if I close/reopen.
• I need the dang thing not to shut off or for the screen to go black disallowing input.

The reasons for these is because I listen to lectures while driving (with the tablet connected to the car audio).  When I want to pause, I need to be able to simply reach over and tap darn near anywhere to pause.  I need to rewind 30 for when I didn't quite catch part of the lecture.

After quite a search, I couldn't find anything in the windows store that worked (one app came close, had those features, but simply wigged out whenever resuming play after turning the tablet back on).  I probably could find something in the google or apple stores that worked, but for myriad other reasons, I love my windows tablet.

Legacy windows to the rescue.  I can run legacy windows applications, so surely the ever-loved, classic, most fully featured MP3 player in history; WinAmp will fill my needs, right?  Well, it came really close.  Of course, it installed and worked fine on the legacy side of Windows 8, which right there is awesome.  The other vendor's tablets just aren't going to run a 15 year old (or however old WinAmp is) application.

Close.  Prefs allowed me to zoom the interface (using the "modern" skin) so the buttons became large.  Pause is pretty easy to find and push now.  Amazingly, WinAmp doesn't remember where you left off (unless it's a podcast) so I needed a plug-in for that.  Now I'm close.  I need it to keep the screen on (not sure there's a plug-in for that), and I still can't rewind.  The closest way is to invoke the on-screen keyboard and hit the back arrow.

Simple.  I want a controller for WinAmp.  I want something that starts WinAmp (allowing it to simply resume where it left off), issues 2 keystroke commands to it (pause, rewind), and prevents the tablet from going to sleep.  So I created that in very little time.  I threw together an old-school WinForms (damn that technology was useful in many situations, wasn't it?) app that starts WinAmp when it starts, shows itself full-screen with ~ 75% of it's surface for pause, the rest for rewind, and sends a mouse move command every 2 minutes to prevent screen shutdown.  Ye olde Win32 API is ugly, but having the ability to still use it is pretty cool.
If someone out there actually wants the executable, contact me and I'll send it.

The Win32 Code.

10. class Win32Trash
11. {
12.     [DllImport( "User32" )]
13.     public static extern int SetForegroundWindow( IntPtr hwnd );
14.  
15.     /// <summary>
16.     /// Synthesizes keystrokes, mouse motions, and button clicks.
17.     /// </summary>
18.     [DllImport( "User32.dll" )]
19.     public static extern uint SendInput( uint numberOfInputs, [MarshalAs( UnmanagedType.LPArray, SizeConst = 1 )] INPUT[] input, int structSize );
20.  
21.     public enum InputType
22.     {
23.         INPUT_MOUSE = 0,
24.         INPUT_KEYBOARD = 1,
25.         INPUT_HARDWARE = 2
26.     };
27.  
28.     public enum MouseEventFlags
29.     {
30.         MOVE = 0x00000001,
31.         LEFTDOWN = 0x00000002,
32.         LEFTUP = 0x00000004,
33.         RIGHTDOWN = 0x00000008,
34.         MIDDLEDOWN = 0x00000020,
35.         MIDDLEUP = 0x00000040,
36.         ABSOLUTE = 0x00008000,
37.         RIGHTUP = 0x00000010
38.     };
39.  
40.     public struct MOUSEINPUT
41.     {
42.         public int dx;
43.         public int dy;
44.         public int mouseData;
45.         public MouseEventFlags dwFlags;
46.         public int time;
47.         public IntPtr dwExtraInfo;
48.     };
49.  
50.     public struct KEYBDINPUT
51.     {
52.         public short wVk;
53.         public short wScan;
54.         public int dwFlags;
55.         public int time;
56.         public IntPtr dwExtraInfo;
57.     };
58.  
59.     public struct HARDWAREINPUT
60.     {
61.         public int uMsg;
62.         public short wParamL;
63.         public short wParamH;
64.     };
65.  
66.     [StructLayout(LayoutKind.Explicit)]
67.     public struct MOUSEKEYBDHARDWAREINPUT
68.     {
69.         [FieldOffset(0)]
70.         public MOUSEINPUT mi;
71.         [FieldOffset( 0 )]
72.         public KEYBDINPUT ki;
73.         [FieldOffset( 0 )]
74.         public HARDWAREINPUT hi;
75.     };
76.  
77.     public struct INPUT
78.     {
79.         public InputType dwType;
80.         public MOUSEKEYBDHARDWAREINPUT mkhi;
81.     };
82.  
83. }

The WinFormCode.  Set the form state to maximized.  There is a timer hard coded to fire every 2 minutes (to issue the mouse move command to prevent tablet sleep), and the path to WinAmp is hard coded to where it's installed by default.

16. public partial class WinAmpController : Form
17. {
18.  
19.     private static Win32Trash.INPUT[] _junkForMouseInput = CreateMouseInput();
20.     private static Process _winampProcess;
21.  
22.     public WinAmpController()
23.     {
24.         InitializeComponent();
25.     }
26.  
27.     public void launchWinAmp()
28.     {
29.         try
30.         {
31.             // We don't want to attach to an existing process if one exists
32.             var si = new ProcessStartInfo( @"C:\Program Files\Winamp\winamp.exe" ) { UseShellExecute = false, WindowStyle = ProcessWindowStyle.Normal };
33.             _winampProcess = Process.Start( si );
34.             _winampProcess.WaitForInputIdle();
35.         }
36.         catch( Exception e )
37.         {
38.             MessageBox.Show( "Error launching Winamp: " + e.Message );
39.         }
40.  
41.     }
42.  
43.     private void WinAmpController_Load( object sender, EventArgs e )
44.     {
45.         launchWinAmp();
46.         /// Expected that the plug-in will begin playing where left off.
47.         this.TopMost = true;
48.     }
49.  
50.     private void WinAmpController_FormClosing( object sender, FormClosingEventArgs e )
51.     {
52.         if ( _winampProcess != null )
53.         {
54.             _winampProcess.CloseMainWindow();
55.             _winampProcess.Close();
56.         }
57.     }
58.  
59.     private static void SendKey( string key )
60.     {
61.         IntPtr windowHandle = _winampProcess.MainWindowHandle;
62.         //ShowWindow( windowHandle, 1 );  // ain't shit
63.         Win32Trash.SetForegroundWindow( windowHandle );
64.         SendKeys.SendWait( key );
65.     }
66.  
67.     private void btnPause_Click( object sender, EventArgs e )
68.     {
69.         SendKey("c");
70.     }
71.  
72.     /// <summary>
73.     /// Each left command is only 5 secs back.  This results in about 19 secs back.  Putting multiple calls to SendWait inside the SendKey method didn't work.
74.     /// </summary>
75.     /// <param name="sender"></param>
76.     /// <param name="e"></param>
77.     private void btnBack_Click( object sender, EventArgs e )
78.     {
79.         SendKey( "{LEFT}" );
80.         SendKey( "{LEFT}" );
81.         SendKey( "{LEFT}" );
82.     }
83.  
84.     /// <summary>
85.     /// Set for every 2 min.
86.     /// </summary>
87.     /// <param name="sender"></param>
88.     /// <param name="e"></param>
89.     private void timer1_Tick( object sender, EventArgs e )
90.     {
91.         this.Text = "timer clicked";
92.         MoveMouse();
93.     }
94.  
95.     private static Win32Trash.INPUT[] CreateMouseInput()
96.     {
97.         var i = new Win32Trash.INPUT()
98.         {
99.             dwType = Win32Trash.InputType.INPUT_MOUSE,
100.             mkhi = new Win32Trash.MOUSEKEYBDHARDWAREINPUT()
101.             {
102.                 mi = new Win32Trash.MOUSEINPUT()
103.                 {
104.                     dx = 0,
105.                     dy = 0,
106.                     mouseData = 0,
107.                     dwFlags = Win32Trash.MouseEventFlags.MOVE,
108.                     time = 0,
109.                     dwExtraInfo = IntPtr.Zero
110.                 }
111.             }
112.         };
113.         return new Win32Trash.INPUT[] { i };
114.     }
115.  
116.     private void MoveMouse()
117.     {
118.         Win32Trash.SendInput( 1, _junkForMouseInput, Marshal.SizeOf( _junkForMouseInput[0] ) );
119.     }
120.  
121. }