Forum Moderators: open
This is something I've been working with for the past few weeks, and I've come across a large stumbling block. However, I've already learnt so much so my plan is this: to share my new-found knowledge, and to humbly seek advice!
So, I've been creating Excel documents from within C#. I remember trying to work with the various Office applications with .NET years ago, and it left a bitter taste in my mouth after playing around with Interop, and the like. So I wanted to avoid that this time. I thought I was going to have to go down that route, until I discovered that Excel works with XML pretty darn well. So, this is how I create Excel documents now -
Step 1. Create the template of the document manually in Excel.
Step 2. Save as .xml
Step 3. Open the file in notepad
Step 4. Break this text into logical sections (document header, styling, worksheet header, rows, worksheet footer, document footer, etc)
Step 5. Work out what isn't needed in the xml (such as currently selected cell, etc) and delete it
Step 6. In your asp.net project, create a series of methods that produce the text for each logical section (GetDocumentHeader(), GetDocumentStyling(), etc)
Step 7. Populate each method to return the required xml content for each section, passing any parameters in such as a datatable to display as rows, etc
Step 8. Write all this text together into a .xls file
Step 9. Open the file in Excel/Notepad and compare with your original template - keep on tweaking until you get what you want
Brilliant.
The key thing is to work out how an Excel document is defined in XML, and knowing the difference between static text and actual data content. Once you've got that sorted, you'll be fine.
And as a side note, for any decent-sized document, you'll want to use the StringBuilder, instead of a String. For years I was afraid of the StringBuilder, until I tried it with this, and the performance benefits were staggering. We're talking load times of over a minute when using a String, and under a second with the StringBuilder.
Lovely.
Now then, this is great. However, I was in a meeting this afternoon and I promised that one of these Excel documents I'm creating could have a logo image in it. Problem: Excel XML doesn't allow images. Hence my problem.
So here's my plea for help - does anyone know how to trick Excel into accepting XML with images (long shot, I know), or does anyone have any other suggestions that doesn't involve delving into the depths of Interop?
Cheers guys!
[edited by: Ocean10000 at 8:11 pm (utc) on Oct. 24, 2008]
Thanks for the reply, but I'm afraid I've already tried that to no avail! It simply complains that it cannot store images in xml, then saves the spreadsheet without the image.
Good attempt though! Any other thoughts?
You can manipulate xls files using sql-like queries. They aren't the easiest things to master, but it would mean you could store a template xls file, use a query to manipulate the spreadsheet content, and this would allow you to put an image - into the template beforehand.
It all follows a similar course to inserting / updating a sql or access database, just slightly different query syntax....
Once you have mastered the syntax, it is actually a far more straight forward means to manipulate spreadsheets programatically
