CODEWITHSUNDEEP
c#formattingexcel-interop
Horbert
Horbert

Reputation: 371

C# Interop Excel format like Excel's format as table

I'm exporting a table from SQLite to Excel (2010) in C#. It works fine. I'm using the Excel.Range.set_Value() method.

How can I format an Excel.Range like Excel's format (like a table) would?

Upvotes: 9

Views: 14156

Answers (3)

a_arias
a_arias

Reputation: 3116

This example selects a rectangular range of every cell in the active sheet. Also, it uses indexed parameters of Range to get the range points. Furthermore, AddEx() (and most methods in Interop.Excel) uses default parameters so you don't have to use System.Reflection.Missing.

// define points for selecting a range
// point 1 is the top, leftmost cell
Excel.Range oRng1 = oSheet.Range["A1"];
// point two is the bottom, rightmost cell
Excel.Range oRng2 = oSheet.Range["A1"].End[Excel.XlDirection.xlToRight]
    .End[Excel.XlDirection.xlDown];

// define the actual range we want to select
oRng = oSheet.Range[oRng1, oRng2];
oRng.Select(); // and select it

// add the range to a formatted table
oRng.Worksheet.ListObjects.AddEx(
    SourceType: Excel.XlListObjectSourceType.xlSrcRange,
    Source: oRng,
    XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);

Upvotes: 2

David Yenglin
David Yenglin

Reputation: 685

To expand upon my comment and add to D Stanley.

Range range = ws.get_Range("A1:D5");
wrksheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, range, missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, missing).Name = "MyTableStyle";
wrksheet.ListObjects.get_Item("MyTableStyle").TableStyle = "TableStyleMedium1";

Upvotes: 14

D Stanley
D Stanley

Reputation: 152634

Here's the VBA that does it:

ActiveSheet.ListObjects.Add xlSrcRange, Range("$J$10:$N$12"), , xlYes

Shouldn't be too hard to translate into an automation call. You can read the documentation as well.

Upvotes: -1

Related Questions