Click here to Skip to main content
11,482,402 members (74,680 online)
Click here to Skip to main content

Tagged as

Tip: Format an Excel Range as a Table Programatically

, 26 Aug 2011 CPOL 36.1K 9
Rate this:
Please Sign up or sign in to vote.
C# code to format a certain range of Excel cells using the Format As Table button -- but from C# interop code
Sometimes, you have a range of cells and you want to choose the Format As Table button to format the cells as a nice table.

Here's how to do that using Excel interop:

public void FormatAsTable(Excel.Range SourceRange, string TableName, string TableStyleName)
{
    SourceRange.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
    SourceRange, System.Type.Missing, XlYesNoGuess.xlYes, System.Type.Missing).Name =
        TableName;
    SourceRange.Select();
    SourceRange.Worksheet.ListObjects[TableName].TableStyle = TableStyleName;
}

That's it! To apply a table style, e.g. TableStyleMedium15, to a range of cells, you say:

Excel.Range SourceRange = (Excel.Range)oSheet.get_Range("A6","X10"); // or whatever range you want here
FormatAsTable(SourceRange, "Table1", "TableStyleMedium15");

The "Table1" is just a random name for the table; it's arbitrary, but every table you format must be a unique range. The table style names you can find out by recording a macro, applying the formatting by hand, and then reading off of the VBA module what style name Excel filled in once you've stopped the recording.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Brian C Hart
Software Developer (Senior) Corrugated Technologies, Inc.
United States United States
From Fridley, Minnesota and I like computer programming! When I got started, I was working mostly with Windows GUI programming in C/C++. Then later on I worked with COM/DCOM for a school internship. I used COM/DCOM to write an ad hoc cluster server and job-running environment for a cluster of 24 Windows-based high-end visualization workstations. I moved on to C# and have been working in C# and Windows Forms ever since. I have yet to embrace Silverlight Smile | :)
Follow on   Twitter

Comments and Discussions

 
QuestionHow to find the strings corresponding to Table Style Pin
Sri Lakshmanan22-Mar-14 7:42
memberSri Lakshmanan22-Mar-14 7:42 
AnswerRe: How to find the strings corresponding to Table Style Pin
Brian C Hart23-Mar-14 18:17
memberBrian C Hart23-Mar-14 18:17 
GeneralGood Tip Pin
Ice_LS002-Aug-12 7:43
memberIce_LS002-Aug-12 7:43 
GeneralRe: Good Tip Pin
Brian C Hart2-Aug-12 8:00
memberBrian C Hart2-Aug-12 8:00 
GeneralReason for my vote of 5 My Vote 5 Pin
Abdul Quader Mamun17-Dec-10 6:11
memberAbdul Quader Mamun17-Dec-10 6:11 
GeneralGood tips Pin
Abdul Quader Mamun16-Dec-10 9:36
memberAbdul Quader Mamun16-Dec-10 9:36 
GeneralExcellent tip, firm 5! Pin
DrABELL17-Dec-10 17:14
memberDrABELL17-Dec-10 17:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 26 Aug 2011
Article Copyright 2010 by Brian C Hart
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid