I have been using E-IceBlue's Spire.XLS library (License Purchase Page | nuget Package), and while it is excellent I've hit a couple of hurdles.
The gist of my requirement is this:
I have to take a bunch of data from our intranet CMS, along with attachments users have uploaded to it, and email that information to a third-party outside of the company, periodically. We were originally sending the data and the user-uploaded attachments separately, but as the documents became more numerous and unwieldy - I then got the request to try and combine everything in to one file. The attachments were small enough to embed, so I achieved this by creating an Excel report using Spire.XLS -- which allows me to not only add OleObjects to the package, but to position them (anchor them) to a specific row or column as well - maintaining a nice visual link with the data from the CMS record. As such I can have all my data on a row in columns A through AB for example, and the attachments start appearing right at the end of the row in columns AC, AD etc.
In terms of how I implemented that - I grab my data from the CMS, iterate through each item (which includes attachment / File data), I get the default image / icon for the relevant file-type, create an OleObject on the Worksheet and then I position it -- something a bit like this:
MyAttachmentCollection attachments = GetAttachments(itemId);
foreach(File attachment in attachments) {
string fileType; string localFilePath;
// Use WebClient to download file locally..
/* --- pseudo-code omitted for brevity -- */
worksheet.OleObjects.Add(localFilePath, image, OleLinkType.Embed);
worksheet.OleObjects.Last().Location = worksheet.Range[row,col + 1];
worksheet.OleObjects.Last().ObjectType = fileType;
col++;
}
Nice and simple and the result is pretty good. Sadly, the success of it means that the powers that be have wanted to send more and more data this way, without ponying up the cash for a Spire.XLS license. The free license only allows 200 rows of data, or 5 worksheet tabs. This is a single-use case for us, so I think they're finding it hard to justify the license cost for this single development and its' future upkeep. We're public services too, so budget wise we have to try and do things on the cheap!
I'm aware that XLSX / Open XML spreadsheet documents are basically zipped/packaged storage containers, so I've taken a look at the contents of an Excel file that contains some attachments added in this way, and I've tried to go about understanding the various schemas and how I might replicate the effect, but I'm struggling to wrap my head around it to be honest and I'm wondering if any other libraries might exist that do this sort of leg work already?
One of the things I love about EPPlus (Old Codeplex Page | nuget Package) is being able to take a DataSet or DataTable and insert that directly in to a worksheet at a given cell reference. I also like that I can use built in Excel styles or define my own and apply those. I can create really lovely looking spreadsheets (sad I know!) while writing very little code. So initially, I looked in to whether I might be able to use or extend EPPlus... And as described in this answer, EPPlus does expose the underlying XML, but from what I can figure - I'd need to:
- add the icon/image data to the package first (the actual visual representation of the file in the worksheet) and make that live in the drawings and/or media folder within the XLSX,
- the drawing data would need to exist in the new format and the legacy (VML) format (unless Spire XLS is just being overly backwards-compat friendly?? Side note: -- I believe if you use the Office SDK / Excel Interop DLLs you can call for the image information to be generated - but as this is a server based solution I'm looking to avoid that if possible),
- I would need to register relationship IDs for those in various XML files,
- add the attachment as a BIN file (assuming that's just a binary dump?) and create a relationship ID for that,
- and then somehow tie all that together in my worksheet XML...
...headache inducing! Unfortunately I'm not really au-fait with the OpenXML-SDK and I'm not sure how quickly I could pick it up. There's a very real risk I could put a lot of effort in, only to end up with a corrupt / non-compliant file. Unless all of this just seems more complicated than it really is??
The other library that I have used before is NPOI (GitHub repo | nuget Package) -- this is based on Java POI a Java API for Microsoft documents. It supports the older Microsoft Office formats as well as the newer ones.
I've seen some SO answers such as this one which indicate its possible to use POI to embed other MS family documents, but I don't know if the .NET fork (NPOI) is fully implementing this stuff. I've found very little evidence of people doing this using that particular library... it may just be that this requirement is somewhat rare so I can't find examples?
Another example of someone solving the embed problem in Java's POI is here - but that appears to be writing in the older office format and using OLE1.0 embeds.
Just posting as I figure it may be possible one of you super helpful guys out there has done exactly this sort of thing before! ;)
Thank you for reading, and sorry if I've been a bit verbose / wasted too much of your time with the wall of text! Any help greatly appreciated!
