I got an IM from our Customer Service Supervisor. Let’s call her Alice, so I don’t get all tangled up in weird references.
Alice is a good friend. She’s stood for me when I needed her to, she’s helped connect me with a good, reasonably priced mechanic when I need one, and she’s done a lot to offer me perspective about things. We’ve ground our rough edges on each other a couple of times, but it’s nothing we’re going to stop being buds over. Long story longer, I like Alice. She’s a friend, not just a co-worker. So.
When she sends me IMs, I tend to pay attention. She doesn’t bother me unless she needs help, and in general, she doesn’t need help very often. So when she does ask, I pay attention and try to get it done. All her stuff ends up being a nice feather in my cap, usually.
She pinged me today with the request, “Can you help me with an Excel calculation?” I went to her desk to find she was trying to add a number to a date and get a new date, but the date wasn’t where it should be. She figured out what was wrong but didn’t know how to get Excel to cooperate.
What Alice wanted was for Excel to figure out what date results when she adds a number of days to an order date. She’s trying to figure out whether the customer gave us lead time* or not. Customers love to drop orders inside the lead time, then wonder why we have to push those orders back to the date lead time demands on so many of their orders.
Anyway, she had a column of lead time days. And she wanted to be able to add working days to the date, because we don’t tell customers we do stuff on Saturdays. (Even if we do.) So, the customer has to use working days, and the number of days given as lead time for the part has to be translated to working days then added to the request date.
Make sense? In a nutshell, we have to figure out how the lead time translates from calendar days into working days and add the working days to the requested date the customer provided.
So let’s use a real example. She had an order placed date of 7/10/2014 for a part; and she had a lead time of 55 days. She wants to figure out what 55 working days added to 7/10/2014 comes out to be, so she can determine if the customer’s requested date is allowing lead time.
So here’s what I did: I divided the lead time days (55) by 5 to get the number of work weeks in that period (11). Then I multiplied the number of weeks (11) by 7 to get the number of calendar days in that number of weeks (77). Then I added the calendar days to the original date (7/10/2014) to find out what the appropriate lead time date should be (in this case, it came out to 10/15/2014). With that information she can tell whether the customer is requesting at, inside, or beyond lead time.
I love when something just comes to me.
Next up, I had an issue with my incredible Excel spreadsheet library. I’ve been using SpreadsheetLight for months to create real, open XML standard Excel spreadsheets (that’s the format since version 2007) with the .xlsx extension, which do not render a “this file doesn’t match the extension type” warning whenever a user downloads the files from our intranet.
Alas, I was notified by my boss the spreadsheets crash Excel when they’re opened from the web site and sorted. Or filtered. Or anything useful. They create havoc if you’ve got other spreadsheets open, and this isn’t going to work.
So, in a panic, I sent the developer of the library an email. No response, and he’s usually VERY good about responding. So I followed up with yet another email. Nothing. Waited a week, and still nothing. So now I’m thinking he’s either ignoring me (he does charge for support, though he’s been very patient and generous with me in the past), or he’s waiting for me to drop a nickel or two in his cup before he’ll answer. That’s fair; like I said, he gives the software library away, but does charge for support, so…eh.
Not having money to offer him right now, I decided to try another spreadsheet library. It works a treat, too, but I’ve gotten so used to being able to work with SpreadsheetLight, the idea of working with another, less well-documented library sort of made me cringe. I did a simple download of a table full of simple text data, and it worked fine. But I also do manipulation with SpreadsheetLight; that is, I can get raw, delimited text files which are FTP’d to our server every night, and import them into Excel. I don’t see any way to do this on the CodePlex page for the second library**.
I got ClosedXML up and running, and it worked great, so long as I didn’t have to consume the raw text file to produce the spreadsheet. So that’s in production now, but there is that one, little niggling thing.
Now, it isn’t the end of the world. I just wanted to use ONE library for ALL my Excel work on our web pages, so I don’t have to learn a new one. And I planned to use SpreadsheetLight to eradicate the need for Microsoft Office to be installed on our server, because it could consume those nightly files and process them just like Excel, without actually using Excel. Then, from that format, inserting the data in Microsoft Access databases is a snap with C#, and badda bing! no more Office VBA macros, which aren’t meant to run unattended.
But the new library, called ClosedXML, doesn’t show me how to do the text file consumption. So I have to either feel my way through it (ick), or find another user whose documentation covers it, OR, ask the developers. I opted for door number 3 there, and posted a question on their CodePlex page asking if it can be done, and how to do it. Time will show.
Frustrated, I went back and decided to just play with this thing, see if I could figure out what the heck. I started testing other pages where I’d deployed it and sure enough, they were demonstrating the same behavior across the board. I tried downloading and using the new(er) version of SpreadsheetLight’s dll, and nada. Same result.
Then I went to my most recent page, and tested it there, and…nothing happened. It worked fine.
I checked the file and realized something – on all the other pages, I’d set the AutoFilter feature for Excel to be on. I wanted people to be able to filter, sort, whatever, on any column. I also know some people don’t necessarily know how to do that, so I wanted to engage it for them. But on this particular page, the AutoFilter hadn’t been turned on. The spreadsheet created is too small to bother.
Hm…could it be?
So, I went back to one of the broken pages, and disabled all the niceties. I turned off the styling for the headers. I turned off the AutoFilter. I turned off the FreezePanes (where the top row stays fixed when you scroll down a longer sheet) feature. Then I jumped through all the hoops I needed to jump through to remove the ClosedXML library references, recompiled the project, and deployed it again to our test web site.
Guess what? It worked!
I went back and added the stuff back in, one at a time. And as soon as I added the AutoFilter feature, BANG!…failure.
That was the fly spoiling the ointment.
Okay, I can live with this. I have to go back and correct the feature for all the pages using SpreadsheetLight at the moment, but it’s a single line to comment out. I found a couple pages where I’m doing things the older (harder, uglier) way, so I can fix those too, while I’m at it. Problem is, that all takes time, and time is something I just don’t have right now.
And so I’ve rescued myself. It would be nice to have SpreadsheetLight do what it’s supposed to without this weird bug, but at least I can comment out a single line in the code and it won’t crash Excel any more. That’s a lot easier than learning an entirely new library.
I might anyway, though, because if it’s more stable that way, allows the AutoFilter, and can consume text files…well, there’s my one-stop library, right?
I’m out of here for the weekend after today. Have a good one and Shabbat Shalom early.
* – “Lead time” is how much manufacturing/acquisition time is required for a particular part. Sometimes, if we need to manufacture the part, it can be as long as 10 or 12 weeks, especially if we manufacture the part in Europe or China.
** – That doesn’t mean it can’t do it, though. Both libraries are built on the same Open XML Document dll from Microsoft, so it stands to reason they have similar capabilities. But I don’t see any documentation on how to do it in the second library, and of course SpreadsheetLight has a great, working example you can copy and paste from its website. So…sigh.