For many years the COM Interop approach facilitated by the code above was the main technique I used for generating Excel files - but by the mid-2010s it was clear to me that using libraries like ClosedXML, which don't require the Excel application to be installed, are a better approach.
At work the task of moving many years of Excel COM Interop based reporting to ClosedXML is an ongoing, long-term, low-priority task.
At the end of 2022 I tackled converting a heavily used report where the final step of the report creation is splitting and positioning the user's initial view. (Note here that 'splitting' is not the same as 'freezing'...)
I couldn't find a way to do this in ClosedXML... So I decided that after writing the file to disk with ClosedXML I would use OpenXML to setup the split. A simple enough approach (that eventually worked), but working directly thru OpenXML is not my usual code path and the first few failures made it clear that I was going to need some reference material on what properties to set with what values.
I used the one trick I remembered for these files to help me look at the underlying XML - I renamed the file with a .zip extension and then navigated into it. This works and it is a good trick because it doesn't require any additional programs/tooling - if I had managed to immediately intuit the correct code changes I wouldn't have given this hack a second thought... But I didn't get the code right in the first few tries and as I tested code changes I very quickly wanted a better way to take a direct look at the files' XML.
After a little searching I found the Borislav Ivanov's Open XML Package Editor Power Tool for Visual Studio. Once installed in Visual Studio you can drop an Open XML/Excel file into Visual Studio and start exploring. This was fantastic and let me find the last detail I needed...
The code below has so far stood up to some light testing - presented as research and reading material more than code you should copy and paste...
/// Splits the first sheet in an OpenXML Excel File into 2 vertical panes.
/// If the file, workbook or worksheet don't exist the method will exit
/// without throwing an exception.
///<paramname="filename">Full Path and Filename of the OpenXML Excel Files</param>
///<paramname="verticalSplitHeight">Value in 1/20th of a point</param>
/// In A1 style - upper left corner of the top pane, left column for both
///<paramname="topRowForLowerPane">Top row for the lower pane</param>
Created by Charles on 12/20/2022. Updated on 12/27/2022.
2022... This year started with a week long septic install and ends with COVID - questionable bookends but there were lots of great times and great people in-between. Notes from the end of the year.
I've been working on the Pointless Waymarks CMS for over 3 years! I currently use it to create and maintain this site, Pointless Waymarks and 2 private sites. This is by far the largest and longest-lived personal software project I have worked on and I am excited to continue development in 2023! The targets/goals/setup of the project still seem like good choices to me:
Desktop Software: This continues to be a good choice for zero cost personal projects that are fully functional offline - three years in and all things considered I'm glad Pointless Waymarks is a WPF Windows Desktop Application.
Static Sites with Simple Presentations: The sites that I am creating and maintaining with the Pointless Waymarks software are purely personal - no growth goals or investors, no need to be on-trend. I continue to suspect that static sites with simple presentations are a great bet for long term durability with minimal maintenance.
Offline Viewing: with a custom way to organize and present content why limit the output to online presentation - the project includes a simple offline viewer.
Custom Software: There are a practically infinite number of programs, sites and frameworks that you can use to create websites - but I remain very happy with the decision to write custom software. It can be surprisingly nice to use software that is actually 'for you'.
Late this year I added a GeoTools GUI to the Pointless Waymarks Project. This program is centered around geotagging photographs and adding tags automatically based on locations.
My interest in geotagging photographs has waxed and waned over the years. Part of the reason that my interest dwindled is because I believe that it is rarely appropriate to make public the exact location of a landscape photograph. But more recently I have been convinced that having the GPS location of a photograph for personal use is well worth the cost of having to carefully manage what metadata is exported when publishing an image.
I have added tags (keywords) to at least 8,000 photographs by hand over the last two decades. Especially in the last decade I have tried to include location information including land ownership and management agency, for example: 'Santa Catalina Mountains', 'San Pedro River Valley', 'Coronado National Forest', 'Zion National Park'. For the most part I have remembered to apply these tags and have applied them correctly - but just like all 'by hand' data entry no matter how much you know, care and try there will always be mistakes...
After working with more geospatial data this year it occurred to me that tagging locations - if the photograph is geotagged - is really better done by a computer! I'm based in the Southwestern United States and there is quite a bit of publicly available data about Public Lands - a great example of the available information is the Protected Areas Database (PAD-US) from the U.S. Geological Survey.
How it works:
You setup reference GeoJson files for the program to use - including telling it which properties you want to become tags
You select the photographs to tag
The program scans the GeoJson files for features that intersect the photograph's location and creates tags from the intersections
The Pointless Waymarks GeoTools Feature Intersect Tagging works offline and it is easy to create your own reference files (perhaps for unofficial areas/names - geojson.io is a good starting point. Because the program scans thru the local GeoJson files on each run it can be relatively slow - but for now it seems 'good enough' considering the amount, quality and value of the tags that it produces.
When I was in High School in the 1990s I saw Night Music on TV - I remember several episodes but the episode that stick in my memory the most over the years is Night Music #210. This episode manages , in 40-some minutes, to show Diamanda Galas, Indigo Girls, Daniel Lanois, Aretha Franklin, Sonic Youth and Evan Lurie & His Tango Band in both individual performances and collaborations! Rewatching random episodes of Night Music in 2022 it strikes me as the most interesting, diverse and authentic media I have ever seen - it is almost beyond belief that this made it onto commercial over-the-air TV - what a marvel...
Created by Charles on 7/17/2022. Updated on 8/17/2022.
At the end of 2020 we did a big clean-up at work - old manuals for tape backup units, routers with notes about bad ports, floppy disks with long disused software, prismatic CDs with mysterious sharpie scribbles, an array of legacy cables, on and on... Decades of moving forward and leaving things behind.
My favorite two items from the cleanup were old handsets from a telephone setup that I believe date to the late 1990s and the box for Delphi Power Builder Software box. Mostly these are just items of amusement and nostalgia - fun techy anti-relics good for a knowing laugh.
But it turns out that Delphi and (different) phones are still in use in the business today: the Delphi is important but aging, sometimes painful and has largely been replaced by .NET code; the phone system is more sophisticated than handsets with labels. I'm glad we have moved on, but I have to admit that corded phones and Delphi look suspiciously functional...
In the IT area, when the motion detecting lights question my existence and leave me in the dark, the old relics seem to be giving me silent knowing glances, subtle suggestions that maybe I have been going in circles - relentlessly making things more complicated when these phones and that version of Delphi might have just done the entire job the whole time - scary ghost stories from long dead tech, I exit the IT area and head back to my desk in the Buying Office to calm my nerves with a YouTube video about the new improvements in the latest preview vNext version of .NET Core while downloading a .NET Rocks episode about a technology I don't yet know exists...
Created by Charles on 6/10/2022. Updated on 7/21/2022.
There is so much Starlink information online that it can be hard to discern the useful details... Most of this post is just a 'for fun personal story' - but if you considering a Starlink system and are here for info the tldr quick details I can offer are below, ymmv:
Not only do you need an obstruction free view of the sky but you need a view in a specific direction (basically North in North America I believe)... In retrospect this seems obvious but in my first round of research I missed the part about the specific direction - I hadn't considered that our generally rather open view of the sky might not look the right way... (Thankfully it does!)
The Starlink cable is proprietary and has connectors permanently attached on both ends - you'll want a 3/4" hole thru your wall for the cable - this is larger than anything I have ever wanted to pass thru a residential wall. For most people this is a non-issue involving enlarging or creating a hole in your wall - but if you are in a rental, end up with some special circumstance or, like me, just have a deep dislike of holes in your house this is probably worth knowing in advance...
Upload speeds are not impressive - if this rather vague statement concerns you at all be sure to look at reported speeds online and confirm the upload speeds will work for you...
We are happy with the service so far, but a friend 1.5 miles away who started service a few weeks before us is unhappy with network interruptions causing streaming videos to freeze and VOIP calls to drop, why? Who knows... And for reference we are fairly 'easy' internet consumers (no games, not so picky about streaming quality, we have remote work meetings but on the order of a few week max... )
Getting an invite to start service was the 'hardest' part of the whole process for us. (We waited for and are on a residential service plan.)
If you want to do anything interesting with your router (or maybe just don't want to re-setup various devices around your house) just order the Starlink Ethernet Adapter immediately...
The long version:
In the summer of 2021 my wife and I moved to Vail, AZ - the property isn't tremendously remote, but it is much more rural than our homes in Central Tucson (where we lived for two decades). When we moved we tried to ask all the right questions - and had a friend who lived in the area to consult - but in retrospect, regardless of amount of time and research, I just don't think we would have ever understood the differences moving 45 minutes away would mean - Internet service is one of those slightly interesting details...
In the early 2010s we switched to using mobile hotspots for internet - at first because we had problems with a DSL connection, later because it just worked and it was great to travel with. For many years this was directly thru carriers like Verizon, for the two of us and our internet usage the data caps generally weren't a problem. But the addition of the NordicTrack iFit Treadmill (which essentially streams video while you workout) and COVID making our long time patronage of Casa Video less attractive data caps became a problem. Rather than switch to a wired connection in 2019 we tried an unlimited AT&T data plan thru OTR Mobile, great for awhile but we had problems as the company grew, and later from Unlimita Wireless.
Our Nighthawk MR1100 4G LTE Mobile Router and Unlimita Wireless Plan made it to our new house and sometimes worked better than it did in town (thanks, I think, to a hillside location with a great view). But with our new location things changed - we started working remotely more often (during the Covid shutdowns we were physically in the office - part of a radically slimmed down staff doing 'everything' in order to keep the business going) and with a longer commute, gas prices soaring and inflation cutting into our budget we started to think about a more remote future - more remote work? Remote education opportunities? Maybe other things ... ?
So in 2022 when the opportunity for Starlink Residential Service came up we said yes! Basically at our location Starlink seems like the only reasonable chance for any considerable improvement in our internet connection. We don't have any options for 'wired' internet - other options we looked at: Simply Bits, Rincon Wireless and HughesNet - I'm grateful to have all these choices at our location but considering connection quality, price, speed and data caps Starlink seems like the most interesting option atm.
The gen 2 system that we received has a dish (rectangular), mount, long cable and router. With a largely unobstructed view of the sky to the West/North/East our setup was pretty simple - put the dish on the mount, set it in the yard (eventually we used the Starlink Pipe Adapter to attach it to a mount on the roof), enlarged an existing hole from outside to inside the house and plugged it in. For us it 'Just Worked' and so far the app has reported speeds between 40-160 Mbps down (seems like 75 is typical) and 7-20 up (often on the lower side of that...). The downloads are much improved from our previous setup! The upload speed is basically the same, or sometimes a little slower than our hotspot setup. Too bad the uploads haven't improved, but for us it has not been a huge problem and for the most part the download speeds mean more for our current internet usage.
Probably the only install detail of note is that we had to enlarge an existing hole thru the wall (to 3/4") to accommodate the Starlink cable. The cable has custom connectors at either end (apparently ethernet with custom power - see Hacking the Rectangular Starlink Dishy Cable) that you aren't intended to detach. (Btw if you are passing the connector directly thru the wall (not thru a nice cable pass thru) consider taping the connector - the cable enters the connector near the mid point and if things go wrong and you need to pull the cable back the connector can catch and pivot into a shape that is quite like a wall anchor making getting it out of the wall frustrating...)
Now that the system is setup and running it seems like the biggest challenge was getting a dish/service - our timeline: email interest June 2020, deposit February 2021, changed address (different Starlink zone I believe) May 2022, subscription opportunity end of May 2022.
I'm excited that Starlink is working well for us so far - but have been concerned by my close by friend's report of issues. The investment in the equipment is admittedly a bit of a gamble, who knows if Starlink will be around in 5 years and if they are what the quality/price/details of their service will be...
Interesting what sticks with you, I couldn't tell you exactly which tools I downloaded or name a year as my favorite, but I can still remember getting excited about these lists and seeing them again makes me smile!
Those were different times and for various reasons in later years lists of tools weren't as interesting - with more tools, better search and more programming information online it become much easier to quickly find a tool 'on demand'. These days I'm back to wishing for more tool lists - lists by small business devs, lists from search-ranking-backpagers who aren't sponsored/popular/advocates, lists by craftsmen just for the love of it, lists from CRUDy developers decades in and still trying to do it better every time... I'd read those lists - so in the spirit of sharing here is my 2022 tools list!
Visual Studio Professional (often the latest Preview) - This has been a staple in my programming for about 15 years. I have of course had frustrations, installs gone wrong, mystery problems and other assorted issues - but mostly I just appreciate all the help it gives for getting things done and it has certainly improved over the years. Like many large/complex tools I would guess I probably only use 20% of this rather huge program...
JetBrains ReSharper - After some early use of DevExpress's CodeRush I started using Resharper in the late 2000s - I don't think anyone from that era would disagree that Resharper's refactoring capabilities at the time were magical and I have rarely run Visual Studio without Resharper since. In recent years Visual Studio's built in refactoring/tools have improved, but for now I'm sticking with Resharper... One detail about Resharper that I think deserves extra mention is that it has helped me learn a number of language features over the years - string interpolations, linq operators, initializers, etc... - that I would have otherwise been slow to 'see'.
LINQPad - my first LINQPad purchase was in 2008 and I'm still using it! I don't use it daily - but every week there seems to be some reason to reach for it. The program has gained all kinds of interesting capabilities over the years but my goto uses are code scratchpad (quicker than a new project, the always available db layer is often relevant, easy place to save fragments and extend over time), place to play with new Nuget packages (I'll often try new packages with test code in LINQPad before bothering to add it into any 'real' projects) and as a database query and update tool (I think SSMS is a better tool if I am thinking about 'the database' - LINQPad is a better tool if I am thinking about querys/data for code).
Fork - my generally straightforward use of Git seems to match well with the basic capabilities of most GUI git clients but for some reason Fork is the first tool that I am really happy with. Of course anything Git can be done on the command line - and Visual Studio offers some nice Git tooling also - but I continue to find that many day to day tasks are, for me, quicker and better in Fork. I have used Fork daily since late 2021 - well worth the cost!
GitHub - I work alone a lot in recent years and my use of GitHub is very basic - for me the real value in GitHub is that, at the moment, it has become ubiquitous enough that you never have to explain to anyone where your code is or why. My account on GitHub dates back to 2008.
Notepad++ - I'm not exactly sure when I settled on this as my goto 'notepad' variation - probably late 2010s - for me this matches perfectly with what I want for a 'right-click->quick edit' - honestly not a lot to say about this but it is a favorite.
Beyond Compare - Needing to compare things is inevitable and Beyond Compare continues to surprise me on the variety of files that it will produce useful comparison for (and the variety of data sources it will connect to). Used since 2014.
PowerShell - my Powershell skills are not deep but I know enough to get things done - for many years I have used Powershell for various build and deploy scripts.
grepWin - Mention grep and the command line should come to mind - with good reason, but grepWin is a simple way to get those features into a GUI File Explorer environment. Not sure when I found this but it has been a welcome addition to my toolbox.
RegexBuddy - without help I only remember the most basic regular expression syntax - for years RegexBuddy has been my goto for composing and testing regular expressions. I haven't really tried other options - just never felt the need. Used since 2008.
Postman and Fiddler - I'm lumping these together because basically I would rate these tools like these as 'critically important' but over the years I have only been a simple user of both of them. I don't know what I would do without the functionality I also have just never quite fallen in love with either of these programs...
AutoHotkey - It takes me a second when Capslock+h/j/k/l don't map to arrow keys and Capslock+y/u/i/o aren't control+arrow key movements... I love my Kinesis Advantage2 Keyboard and keyboard/mouse-trackball/chair/desk/ergonomics are worth serious consideration if you spend any notable time programming - but over time I've found that I also put value on being able to quickly get to work efficiently on any machine. AutoHotkey is a nice compromise for me - no hardware to remember and nothing to plug in, but anywhere I can run the program I can add a few of my favorite keyboard details with minimal hassle.
Tailscale - This year I started using Tailscale - at first it was just an easy/secure/zero hassle way to connect to home (for years not much of a concern for me but now that home and work are no longer 5 minutes away from each other...). The ease of connecting a set of machines with nothing but a Tailscale install has been a revelation for me - it hasn't made it into any official use at work but I have already used it to connect dev machines for testing...
ErgoMax Adjustable Height Crank Desk - I've been lucky enough to have a motorized sit/stand desk at work for about 6 years now - I like it but we recently purchased 3 'manual' crank sit stand desks for home and I have to say these are a new favorite. They are smaller and simpler than my desk at work and these days that fits in well with what I want - nothing to plug in, less to break/go wrong. Used for about 9 months.
The list above are tools that I use regularly - for fun here are a few things I have been thinking about:
KeyMouse Track 304 - In addition normal/average keyboards I have used: Kinesis Advantage2 Keyboard, Kinesis Freestyle Split-Adjustable Keyboard, ThinkPad TrackPoint Keyboard II and owned/heavily used the keyboards on several Thinkpad laptops. I think the keyboard features I want are: split, quiet, curved keywell, dedicated physical function keys and a trackpoint or similiar pointing device that gives you the option to 'mouse' without your hands straying too far from the home keys. The closest I've seen that isn't a build it yourself affair is the KeyMouse Track 304 (the Dactyl Manuform Keyboard lacks the pointer but is worth mentioning also). For my budget the price ($700 as of writing) gives me a lot of pause but the real puzzle is what todo about having 5 different desks that I want to work from? Especially since I want 4 of those desks to be setup so that 'anyone' can sit down get work done... Do I get 5 of my preferred keyboard and have 2 keyboards at every computer? That doesn't seem great... Maybe in the spirit of not letting perfection get in the way of better just put one great keyboard at one desk? Smart but idk I'm just not inspired by that... So far the simplest solution has been to do mostly 'normal' keyboards, try to remember that more thinking and less typing is nearly always useful and vary my work routine enough that relatively unimpressive ergonomics of a normal keyboard aren't an issue.
JetBrains Rider - All of the great things about Resharper wrapped into an IDE that seems to be notably faster than Visual Studio! And the similarities between Rider and other JetBrains IDEs is a bonus. I've tried at time to switch to mainly using Rider but 'things' always come up - occasionally it seems behind supporting the pre-release version I am using, in the past WPF and Xamarin support were issues, I've had mystery problems while debugging that I can't replicate in production or debugging in VS... Some (maybe all!) of the issues are could be about my lack of experience with Rider - but regardless I haven't quite been able to justify switching from Visual Studio full time.
JetBrains Space plus JetBrains Gateway - When I look at Space ("The All-in-One Solution for Software Teams") it immediately makes sense to me and pairing it with the idea that anyone logged in can also connect to a remote development environment setup for the project seems very interesting! But two big things have tempered my excitement: it isn't GitHub (these days a core value of GitHub seems to be that you just don't ever need to explain or justify GitHub - I like sourcehut as well but...) and the Gateway remote machine is (at least currently) a linux box and I don't think atm there is a way to set this up for Windows Desktop dev... I'm looking forward to seeing how this evolves
Visual Studio Code - so much attention and obviously a huge amount of functionality - it feels like there is something here I should be taking advantage of? But I can't quite figure out what - it seems worth loading Visual Studio for .NET dev and anything else loads faster and works more simply in Notepad++? Maybe at some point I'll figure out what I'm missing...
Welllll that's it I guess - let me know if you have thoughts on what I should use! charles at cmiles dot info
Created by Charles on 5/7/2022. Updated on 5/10/2022.
In upgrading an older service to CoreWCF I ran into some Pinvoke code for 'RawPrinting' (DllImport "winspool.Drv") with an unexpected return value that was causing nothing to print. I suppose this could make an interesting article - identify why this code worked under .NET Framework 4.61 and not .NET Core 6+, show the subtle code changes between .NET versions and maybe offer a clever PowerShell one-liner that would fix this problem in all of your code... I did none of that - I quickly made an honest assessment that my DllImports were copy and pasted, that I have only the most basic knowledge of how Pinvoke/IntPtr/Dllimports 'really' work and that exploring the details of various interop printing structures was unlikely to really be all-things-considered worth the time. In the end vague memories and web searches led me to Vanara: A set of .NET libraries for Windows implementing PInvoke calls to many native Windows APIs with supporting wrappers. Several minutes of light refactoring later printing was working again! (Oh and yes - this was all in service of pushing ZPL to the wonder, and occasional terror, that is our Zebra tag printers...)
At work over the past two decades we basically maxed out at 4 in-house developers - always with some part time positions - but right now I'm the sole in-house Dev/IT person. A big challenge with over 2 decades worth of in-house development is figuring out how to move things forward. Recently I took some inspiration from larger dev organizations and made the last commit in a branch that combines 137 projects under one solution. You can easily find information online about monorepo and monolith (usually vs micro-service) setups -> in the big picture our codebase is small and I am currently a team of 1, a much much much simpler situation than anything you will read about - but since our code base has traditionally been divided into silos I am hoping with this change to:
Take Maximum Advantage of Tooling - assuming you are on a reasonably capable dev machine Visual Studio+Resharper is happy to show you references, apply refactorings and show you errors from ALL 137 projects! Forget remembering what else your code change might impact - just have it all in front of you.
One Version - different syntax across language versions, library versions across framework versions, constantly changing external libraries and confusing problems referencing one library from another because of versions - all of these things cost painfully valuable time as projects spread out over years and decades of different versions... My hope is that having everything accessible in one solution makes keeping everything at one version possible.
Latest Versions - I know that staying on one version is at best a hopeful goal - but in truth I have a more ambitious goal: stay on 'latest'. Sure, every upgrade is a chance for some painful bug - but every update is also a chance that your programs improve with zero effort or new opportunities emerge. I have no idea why some of my database related code is running better under .NET 6 and latest EF - but it is, and in many projects it cost me basically zero time... My best guess is that in many cases the time to make upgrades will pay off in only having to do it once - upgrading Automapper to the next version even with breaking changes isn't that bad, having to figure it out multiple times with months/years in-between with upgrades from different versions involved each time is costly.
Constant Updates: What I have watched at work is that software that stagnates not only make life hard for devs but also causes process stagnation - this could be about the latest big-new-thing but is just as likely to be about small/quick changes: adding a shortcut key, coding a new warning when a user does something that works but is usually wrong, or a new report for a new problem. There are only so many resources (especially time) but both software and processes benefit when software can keep moving forward. In this setup the app with the shortcut key is already open, updated and ready to go in Visual Studio and adding the shortcut key might be an 'actual' 5 minute or less project! Right now it might be a 5 minute project - or it might be a half day to figure out which repo, checking it out and make sure the right build tools are installed, find the config file that was missed in the commit a year ago, try to upgrade packages but instead fall into a mire of version/package conflicts, figure out internal updates, etc...
Let Go of Believing You Know - In the 'old days' between number of people, fresh code and operational setup there was a period of time when the best skill may have been knowing who to talk to about some code. Over the years as decisions, software, people and processes have spread out over years, decades, of time - there is no one to ask, you just have to read the code, and that holds true even if you wrote the code! The single solution approach puts all the code in front of you - exactly what you need if you can't hold it all in your head.
We have plenty of code not in the current 137 project solution, legacy problems to solve and business challenges ahead - it isn't that I think this is a magic solution to our, or anyone's, problems - but I'm writing about it because I'm excited to find a way to improve and move forward even if it isn't a silver-bullet-everything solution to all software issues.
Mojave Sonoran Trail: A 625 Mile Thru-Hiking Route Introduction & Guide - always interesting to see something new and I have to say that even with 2 decades in the Southwest this route touches on MANY areas that I never considered exploring - worth checking out although I'd watch the first few YouTube videos before getting too excited as some of the scrambling looked a little intimidating to me!
Advanced Outdoor Technologies - I do love a good front-pack setup and this new-to-me company is making some interesting gear! I haven't ever seen these bags in person but at a glance they to be designed with a smart 'depth' - having tried a decent number of front packs this is key if you want to be able to see your feet (and you really do want to see you feet)... I currently use a Pajaro GRANDE Field Bag as a front pack, mainly for my camera.