1

I have a (winforms) project which allows users to export to an Excel spreadsheet. This exporting is done via the interop (Microsoft.Office.Interop.Excel) and works off a "template" spreadsheet I have created. The template should remain unchanged during the export as the code programmatically saves to a different file path. I want to know what the cleanest approach to this is.

At the moment I have added the template spreadsheet as a file to my project. My understanding is that if I set it's Build Action property to Embedded Resource, I would still need to at least save the file to the user's hard drive at least temporarily in order for the interop to access it. My understanding is that the interop automates an Excel process to open the file. So is it advisable to copy my template out of the embedded resources into the user's temp folder each time they try and export and work from there (i.e. using System.IO.Path.GetTempFileName())? If so, then how do I actually get hold of the embedded resource? I tried looking for it in Properties.Resources but it is not there. Or is there a smarter way to go about this?

Dan
  • 45,079
  • 17
  • 88
  • 157

2 Answers2

1

Look at the following link, which explains how to save the embedded ressource to file: https://stackoverflow.com/a/864276/342594

As a side note: I would store the file somewhere in the user's folder (e.g. My Documents). Then you could give the user the opportunity to alter the template. You could then first check if the template exists and create it if not. Otherwise use the existing template. If the user broke the template your export method would probably notice it and you could give the user the opportunity to revert to the original template.

Community
  • 1
  • 1
Sascha
  • 1,210
  • 1
  • 17
  • 33
  • Thanks I'll take a look a the link. The user must not edit the template though. – Dan Nov 25 '15 at 09:48
  • Ok, in that case I would agree to use GetTempFileName(). – Sascha Nov 25 '15 at 10:51
  • Been looking into this, I need to delete this temporary template file at the end of the function, but what happens if there are uncaught errors (e.g. during development). I'm just going to generate multiple copies of this template in the temp folder... – Dan Nov 25 '15 at 15:35
  • Otherwise, together with this article (https://support.microsoft.com/en-us/kb/319292), that link does indeed help. I will accept your answer if you explain the relevant parts from your link (and you might as well change `CopyStream(input, output)` to `input.CopyTo(output)` which is found in the comments). Thanks. – Dan Nov 25 '15 at 15:46
  • You could guard your code with a try/catch/finally where your file gets deleted in the finally clause. But I wouldn't worry about the problem too much, because windows delete temp files by itselfs after a while. – Sascha Nov 25 '15 at 15:46
  • It is in a `finally`, but I don't think `finally` runs when you hit uncaught errors which happens a fair amount during development. But if Windows automatically clears out the old temp files then that's perfect. – Dan Nov 25 '15 at 15:48
  • 1
    It should hit the finally even during development. But depending on your setting for handly caught and uncaught exception visual studio might stop at the exception and only if you hit run once more it will process the finally. Since I have visual studio with german localization I can't tell you exact option in VS but it's under the debugging menu and then execptions...In any case you should veryfiy the behaviour with the release version of your application. – Sascha Nov 25 '15 at 16:02
0

I would recommend to use OpenXML (or its a high-level wrapper like ClosedXML), not Microsoft.Office.Interop.Excel. You can read your embedded resource as array of bytes (somehow like that) and feed SpreadsheetDocument.Open with it.

// stream "contains" bytes of your embedded template file.
using (var doc = SpreadsheetDocument.Open(stream, true))
{
...
}

After editing a spreadsheet document you can save it anywhere.

Community
  • 1
  • 1
Evgeni Nabokov
  • 2,421
  • 2
  • 32
  • 36
  • Can you provide reasons to switch from code already written using the interop to OpenXML? Why would it provide a better / cleaner solution? – Dan Nov 26 '15 at 12:53
  • @Dan 1) You can't use Microsoft.Office.Interop.Excel without having MS Office installed. We create xlsx file on a web server using pure OpenXML API. 2) No temporary files. – Evgeni Nabokov Nov 26 '15 at 13:15
  • Thanks but the code is already written using the interop. Excel is guaranteed to be installed on the production environment so that is not a problem. Rewriting all the code is pretty much a new language is though. – Dan Nov 26 '15 at 13:23