A Programming Odyssey

Most of you can probably skip this post. It isn't about politics or Judaism or Israel or economics or any of the usual things I talk about. This is about a battle I waged last week with a piece of code. A battle which, as yet, has not been won.

Anyone who feels like they'd like to chime in is welcome to. Those of you who feel, like a coworker of mine, that I'm tilting at windmills unnecessarily, well, all I can say is that I don't like to lose.

The goal

Okay, the company I work for has a customer. They want a web app where, among other things, they can generate a report each month to be sent to their customers. And they want these reports, once generated, to be static. Unchangable. Initially, they wanted them to be made into PDFs. Generating the reports in HTML is child's play. Nothing to it at all. But then things started getting wonky...

PDF

I should give you some background. We have a Microsoft-only shop. Our apps live in IIS (the Windows webserver) and I don't have to worry about cross-browser issues, because our customers know they have to use IE. Of course, we do have compatibility issues with different versions of IE, but so far, these are manageable.

Being all Microsoft, we of course use .NET. ASP.NET, in this case. And it turns out there's a free .NET component that you can use to manipulate and create PDF files, called itextsharp.

So I delved into the world of creating documents in itextsharp, and was able to convert my report to PDF. Which was create. The creation happened on the server, so I could save the PDF to a file share, note its location in a SQL Server table, and all's well with the world.

Private Sub MakePDF(ByVal reporttype As String)
 Dim path As String = "some file path"
 Dim urlpath As String = "the url version of that path"
 Dim reportdate As Date = #date#
 Dim pdfname As String = reportdate.ToString("yyyy_MM_") + reporttype + ".pdf"
 If Not IO.Directory.Exists(path) Then
  'if the path doesn't exist, create it
  IO.Directory.CreateDirectory(path)
 End If
 If IO.File.Exists(path + pdfname) Then
  'if the file already exists, delete it.
  'this should never be called if it's been finalized.
  IO.File.Delete(path + pdfname)
 End If
 Try
  Utils.ConvertHTMLToPDF(strHTMLTable, path + pdfname)
 Catch ex As Exception
  strAlert = "PDF Create Failed: " + ex.Message
  Exit Sub
 End Try
 If Not IO.File.Exists(path + pdfname) Then
  'if the file wasn't created for whatever reason
  strAlert = "PDF Create Failed"
 Else
  'update the location and existence of the file in the database
 End If
End Sub

That could have been it. Except that the PDF didn't format well. The report table was tabular and all, but it wasn't all that readable. I suggested to our customer that it might be better to do it as a protected Excel spreadsheet. I often export reports for them to Excel, and protecting them is simple. And formatting is fairly straightforward. They okayed this, and that's where the fun really started.

Excel

For those of you who have ever wanted to export a table from HTML to Excel, it's really simple. You just select the table, copy, and paste it into the spreadsheet. I've seen people around the interwebs trying to create tables in Excel cell-by-cell, and they can't understand why it's so slow. You just send the innerHTML of the table to the clipboard (simple in Javascript), and paste it using Excel's object model.

(I was going to post the code here, but it's a little long. If anyone is interested, I'll be able to supply it.)

The problem here is that all of this is done on the client. Once I got this working, I needed to find a way to get the report back to the server. And that, it turns out, is a nightmare.

File Browser control

I figured the simplest thing to do would be to put a hidden file browser button on the page, and use that to post the file back to the server. I use these all the time:



But for security reasons, you can't set the file in this control programmatically. Which I suppose is a good thing, usually. You don't want a web page to be able to pillage your files, right? Except that in this case, I made the file, so the security didn't make a lot of sense. Still, I get why there aren't exceptions for situations like mine.

FileCopy

Well, since all of this is on an intranet, where everything is connected on a single network, why not just copy the file from the client to the server? Answer: because the average user doesn't have access to the file server. Thank God. But maybe there's a way for me to do a filecopy, sending credentials through? And indeed, there is. In theory. Here's the code I used:

function NetCopy(savefolder, localfilepath, filename)
 UserName = "domain\user"
 Password = "password"

 Set oNet = CreateObject("WScript.Network")
 Set fso = CreateObject("Scripting.FileSystemObject")

 'Find out next available drive letter
 strDrive = ""
 For i = 67 to 90
   strDrive = Chr(i)&  ":"
   If fso.DriveExists(strDrive) Then
   Else
    Exit For
   End If
 Next

 if strDrive = "" then
  alert("Could not Access network drive")
  exit function
 end if
 
 oNet.MapNetworkDrive strDrive, savefolder, False, UserName, Password

 fso.CopyFile localfile, strDrive + "\\" + filename, true
 
 Set fso = Nothing

 oNet.RemoveNetworkDrive strDrive, True, False

 Set oNet = Nothing
end function

Nice, right? The WScript.Network thing lets me map a drive using enhanced credentials. Which means I should be able to copy the file and then get rid of the mapped drive. Oh, and this is in VBScript, btw. All the examples I saw of WScript were, and mixing scripts in IE is no biggie, so I did it that way. And stuck it in a .vbs file so that users wouldn't be able to grab the credentials just by viewing the page source.

The problem turned out to be that yes, I can map the drive with those credentials. But the filecopy failed anyway. For no apparent reason. And I was unable to unmap the drive afterwards, too. In the code, I mean. I could do it manually through Windows Explorer.

So, so, so close. At this point, my only option seems to be to save the data that makes up the report into a database table and regenerate the report each time anyone needs to see it. The biggest downside to that is that no one can see the report without me coding something. If they were files on a file share, there'd be no coding necessary. But... time constraints being what they are, I don't seem to have much of a choice. My coworker (slash-boss) wrote this to me:

I hope you aren't like the US in Afghanistan-- that you've put too much into the effort to back out now. Becuase I don't see why data can't be put in a table and never touched... I say we should stick with what works-- even if you get around MS security with this file copy, it could very well quit working at some point with an OS update or security policy change.

This feels an awful lot like surrender, which is not something I like to do. So I may keep working on it in my own time.