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.