Formatting Excel Worksheets in C# Using Interop

Formatting worksheets can make them more readable and easier to understand. This article covers some basics of worksheet formatting in C# using the Microsoft.Office.Interop.Excel library, such as applying bold to text, filling cells with color, and creating tables.

Let's start by applying bold to text and filling a cell with color:

var excelApp = new Microsoft.Office.Interop.Excel.Application();
var workbook = excelApp.Workbooks.Add();
var worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
var range = worksheet.Range["A1"];
range.Value = "Hello, world!";
range.Font.Bold = true;
range.Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbLightBlue;

workbook.SaveAs(@"C:\path\to\your\file.xlsx");
workbook.Close();
excelApp.Quit();

Now, let's create a simple table in our worksheet. We start by defining the range of cells that our table will cover, and then we add a ListObject — Interop's version of a table:

var excelApp = new Microsoft.Office.Interop.Excel.Application();
var workbook = excelApp.Workbooks.Add();
var worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
var range = worksheet.Range["A1:C3"];
var table = worksheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, range, Type.Missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, Type.Missing);
table.TableStyle = "TableStyleMedium9";

workbook.SaveAs(@"C:\path\to\your\file.xlsx");
workbook.Close();
excelApp.Quit();

Here's an example of how the final table in Excel might look:

Header 1 Header 2 Header 3
Hello, world! Cell 2 Cell 3
Cell 4 Cell 5 Cell 6

In this example, we've created a table in the worksheet and applied a pre-defined table style ("TableStyleMedium9") to it.

Interop provides a wide range of capabilities for formatting worksheets, and this article only scratches the surface. For more complex needs, refer to the official Microsoft.Office.Interop.Excel documentation.