How to format an Excel file using C#
When creating applications using Microsoft Office Excel, developers can utilize the extensive Excel object model to interact with a wide range of objects. This empowers them to perform tasks such as creating worksheets, accessing and modifying data within individual cells, and applying formatting to enhance the visual appeal of Excel documents, all directly from their C# applications. In this article, we will explore the intricacies of manipulating Excel cells, investigating into the multitude of possibilities available to developers, enabling them to unleash the full potential of Excel integration within their applications.
Microsoft Excel 12.0 Object Library
In order to facilitate our comprehensive exploration, we will utilize the Microsoft Excel 12.0 Object Library as an essential component within our C# project. As discussed earlier, we have already covered the process of importing and incorporating this library into our project, thus granting us seamless access to its vast array of powerful functionalities. This inclusion enables us to fully use the capabilities offered by the library, empowering us to explore deeper into our exploration of Excel integration within our C# application.
Format Excel Column (or Cell)
Format Excel cells to store values as text
Formating Excel cells to text format will solve the problem of losing leading zeo values when you export data from other data sources to excel.
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "@";
xlWorkSheet.Cells[1, 1] = "098";
Excel Number Formatting
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "#,###,###";
xlWorkSheet.Cells[1, 1] = "1234567890";
Excel Currency Formatting
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "$ #,###,###.00";
xlWorkSheet.Cells[1, 1] = "1234567890";
Excel Date Formatting
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "mm/dd/yyyy";
//formatRange.NumberFormat = "mm/dd/yyyy hh:mm:ss";
xlWorkSheet.Cells[1, 1] = "31/5/2014";
By utilizing the capabilities of the Excel object model and using the features offered by the Microsoft Excel 12.0 Object Library, we can dynamically create and modify worksheets, extract data from cells, apply formatting options such as font styles and cell borders, and much more. This level of control empowers developers to automate Excel-related tasks and streamline data manipulation processes, enhancing the overall functionality and efficiency of their C# applications.
Bold the fonts of a specific row or cell
Bold entire row
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1");
formatRange.EntireRow.Font.Bold = true;
xlWorkSheet.Cells[1, 5] = "Bold";
Bold specific cell
workSheet.Cells[2, 1].Font.Bold = true;
Add border to a specific cell
Excel.Range formatRange = xlWorkSheet.UsedRange;
Excel.Range cell = formatRange.Cells[3, 3];
Excel.Borders border = cell.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
Border around multiple cells in excel
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("b2", "e9");
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
Excel Cell coloring
Cell background color
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("b1", "b1");
formatRange.Interior.Color = System.Drawing.
ColorTranslator.ToOle(System.Drawing.Color.Red);
xlWorkSheet.Cells[1, 2] = "Red";
Cell font color , size
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("b1", "b1");
formatRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
formatRange.Font.Size = 10;
xlWorkSheet.Cells[1, 2] = "Red";
Excel Styles to named range
Excel.Style myStyle = Globals.ThisWorkbook.Styles.Add("myStyle");
myStyle.Font.Name = "Verdana";
myStyle.Font.Size = 12;
myStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
myStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
myStyle.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
How to merge Excel cells
xlWorkSheet.get_Range("b2", "e3").Merge(false);
Adding Custom header to the excel file
xlWorkSheet.get_Range("b2", "e3").Merge(false);
chartRange = xlWorkSheet.get_Range("b2", "e3");
chartRange.FormulaR1C1 = "Your Heading Here";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
The following C# program create a mark list in Excel file and format the cells. First we MERGE excel cell and create the heading , then the students name and totals make as BOLD . And finally create a border for the whole mark list part.
output
Full Source C#
using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Excel.Range chartRange;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[4, 2] = "";
xlWorkSheet.Cells[4, 3] = "Student1";
xlWorkSheet.Cells[4, 4] = "Student2";
xlWorkSheet.Cells[4, 5] = "Student3";
xlWorkSheet.Cells[5, 2] = "Term1";
xlWorkSheet.Cells[5, 3] = "80";
xlWorkSheet.Cells[5, 4] = "65";
xlWorkSheet.Cells[5, 5] = "45";
xlWorkSheet.Cells[6, 2] = "Term2";
xlWorkSheet.Cells[6, 3] = "78";
xlWorkSheet.Cells[6, 4] = "72";
xlWorkSheet.Cells[6, 5] = "60";
xlWorkSheet.Cells[7, 2] = "Term3";
xlWorkSheet.Cells[7, 3] = "82";
xlWorkSheet.Cells[7, 4] = "80";
xlWorkSheet.Cells[7, 5] = "65";
xlWorkSheet.Cells[8, 2] = "Term4";
xlWorkSheet.Cells[8, 3] = "75";
xlWorkSheet.Cells[8, 4] = "82";
xlWorkSheet.Cells[8, 5] = "68";
xlWorkSheet.Cells[9, 2] = "Total";
xlWorkSheet.Cells[9, 3] = "315";
xlWorkSheet.Cells[9, 4] = "299";
xlWorkSheet.Cells[9, 5] = "238";
xlWorkSheet.get_Range("b2", "e3").Merge(false);
chartRange = xlWorkSheet.get_Range("b2", "e3");
chartRange.FormulaR1C1 = "MARK LIST";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow );
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
chartRange.Font.Size = 20;
chartRange = xlWorkSheet.get_Range("b4", "e4");
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("b9", "e9");
chartRange.Font.Bold = true;
chartRange = xlWorkSheet.get_Range("b2", "e9");
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkBook.SaveAs("d:\\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
MessageBox.Show("File created !");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
Conclusion
Programming with Microsoft Office Excel using C# offers a rich set of features for interacting with Excel objects. By incorporating the Microsoft Excel 12.0 Object Library and employing the appropriate methods and properties, developers can seamlessly manipulate Excel cells, enabling them to create powerful and customized Excel-based solutions within their C# applications.