INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help with Open XML for Excel

Need help with Open XML for Excel

(OP)
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

RE: Need help with Open XML for Excel

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

RE: Need help with Open XML for Excel

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-an...

Tom Morrison
Hill Country Software

RE: Need help with Open XML for Excel

(OP)
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

RE: Need help with Open XML for Excel

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...
The questions I see there are more along the lines you are asking.

Tom Morrison
Hill Country Software

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close