Need help with Open XML for Excel

[JScannell]

I'm brand new to Open XML for Excel and am finding that I spend most of my time spinning my wheels with it (Has anyone else had this experience?)

I'm trying to convert some code that used to use interop Excel objects and will now use Open XML. I am running into some very serious roadblocks.

Most notably:
I can create a new Excel document with one sheet named "Sheet 1". There is nothing in it yet, but I can create it. What I need to do is:
1. Format the columns (width, data type, font)
2. Add column headings (I can add the text, only. I can't format the headings -- see item 3)
3. Format the column headings (bold, background color, border along the bottom of the cells)
3. Populate the worksheet starting at row 2 with data from one of 3 possible sources (SQL dataset, DataGridView contents, CSV file)

Format the columns. I haven't found any examples of formatting existing columns, only adding new ones. If I do that, then when I save the document it gets into a corrupted state)

Add column headings. It appears that I can do this but only adding the text.

Format the column headings. Again, since I can't find examples of formatting existing columns, I can't change the headings to be the way I want them.

Populate the worksheet. When I used interop.Excel, I was able to convert the DataSet into an ADODB recordset, and populate the entire worksheet very quickly. I don't find any examples of doing this with Open XML. All I find is individual cell updates which will take forever to do if there are a few thousand rows of 20+ columns.

If anybody has a good (and I stress GOOD) tutorial on Open XML or can share their experiences with me, that would be greatly appreciated.

Jerry Scannell

 

[Diancecht]

I think this is more a programming related question. Maybe you should try posting in the VBA forum, if that's the programming language you're using

Cheers,
Dian

 

[k5tm]

Have you looked at this tutorial? It seems to be highly rated, and there are also a few answered questions, in which you can find references to additional documentation.

http://www.codeproject.com/Articles/670141/Read-and-Write-Microsoft-Excel-with-Open-XML-SDK

Tom Morrison
Hill Country Software

 

[JScannell]

Tom, That was a nice tutorial. Since the other day I am able to create and populate an Excel document using Open XML.

Now I have one last problem to solve (unless something else crops up!). I have some special NumberingFormat objects created and have successfully applied them to the StyleSheets of my document. Problem is, when I apply those formats to cell data, the formatting doesn't take effect.

For instance, I have a currency format that should apply the data 24 as $24.00, but all I get is the 24. I think the basic cell formatting is getting applied because I've set that cell format up so that it aligns to the right which seems to work. However, the FormatCode doesn't get applied to the data.

To answer Dian. This question is related to programming using the Open XML architecture that Microsoft makes available. I couldn't find a forum suited for that. The closest thing I found was this XML forum. If there is another one better suited, let me know. I am programming in C# and not VBA. But it really isn't a C# question -- it is an Open XML question. I don't know where else to turn to for this.

Jerry

Jerry Scannell

 

[k5tm]

Jerry,

I can understand why you sought advice here.

While I hate to refer folks to other venues, since this is a proprietary Microsoft issue I would suggest one of the Microsoft-hosted MSDN forums. You can start here:

https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=oxmlsdk

The questions I see there are more along the lines you are asking.

Tom Morrison
Hill Country Software