How to set formats into excel file from C# code

We will set “Font” , “Currency”, “Date” and “Percentage” formate into Excel from C# .
For creating excel, we will use “Microsoft.Office.Interop.Excel” dll .

Steps :

1. Add reference of “Microsoft.Office.Interop.Excel” into project .
2. Create data table for data, which will use to display over excel file .
3. Write data from data table to Excel and set the format .

Step 1 :

Creating data table :

Get the data, which you need to display over excel file .

For eg :

public System.Data.DataTable GetData()
{
System.Data.DataTable l_dtEmployee = new System.Data.DataTable("Employee");

// Create columns for l_dtEmployee .
l_dtEmployee.Columns.Add("EmpId", typeof(int));
l_dtEmployee.Columns.Add("Name", typeof(string));
l_dtEmployee.Columns.Add("Sex", typeof(string));
l_dtEmployee.Columns.Add("DateOfReport", typeof(DateTime));
l_dtEmployee.Columns.Add("City", typeof(string));
l_dtEmployee.Columns.Add("Salary", typeof(Decimal));
l_dtEmployee.Columns.Add("Leaves", typeof(float)); /

// Create rows for l_dtEmployee .
l_dtEmployee.Rows.Add(10, "Abhishek", "M", DateTime.Now, "Nainital", 5500, .34);
l_dtEmployee.Rows.Add(20, "Digvijay", "M", DateTime.Now, "Shimla", 4800, .98);
l_dtEmployee.Rows.Add(30, "Shrish", "M", DateTime.Now, "Dehradun", 6700, .31);
l_dtEmployee.Rows.Add(40, "Shaifali", "F", DateTime.Now, "Dehradun", 7000, .10);
l_dtEmployee.Rows.Add(50, "Sonam", "F", DateTime.Now, "Delhi", 6500, .43);

l_dtEmployee.Rows.Add(60, "Ankur", "M", DateTime.Now, "Delhi", 4500, .33);
l_dtEmployee.Rows.Add(70, "Vipin", "M", DateTime.Now, "Dehradun", 8000, .44);
l_dtEmployee.Rows.Add(80, "Jasmeen", "F", DateTime.Now, "Delhi", 6000, .65);
l_dtEmployee.Rows.Add(90, "Rakesh", "M", DateTime.Now, "Jaisalmer", 2000, .32);
l_dtEmployee.Rows.Add(100, "Annirud", "M", DateTime.Now, "Rohtak", 3900, .22);

return l_dtEmployee;

}

Step 2 :

Now we will create a objects of Application, Workbook and worksheet of excel, to write and formats the data into excel .

Currency Format = “$#,##0.00_);[Red]($#,##0.00)” .
Date Format = “mm-d-yy h:mm:ss AM/PM” .
Percentage Format = “0.00%” .

This below function is use to write the data into l_objExcel and set formating with data by using Microsoft.Office.Interop.Excel dll .

We are formating a records of excel on the bases of data type of data table columns .

public void WriteDataIntoExcelWithFormats(System.Data.DataTable p_dtData)
{
// Add reference of Microsoft.Office.Interop.l_objExcel dll into project .
Microsoft.Office.Interop.Excel.Application l_objExcel;
Microsoft.Office.Interop.Excel.Workbook l_objExcelworkBook;
Microsoft.Office.Interop.Excel.Worksheet l_objExcelSheet;

try
{
// Create the object of l_objExcel application .
l_objExcel = new Microsoft.Office.Interop.Excel.Application();

// Create workbook .
l_objExcelworkBook = l_objExcel.Workbooks.Add(Type.Missing);

// Get active sheet from workbook
l_objExcelSheet = l_objExcelworkBook.ActiveSheet;
l_objExcelSheet.Name = "Report";

// For showing alert message of overwritting of existing file .
l_objExcel.DisplayAlerts = false;

// Fill the l_objExcel from p_dtData data .
for (int rowIndex = 0; rowIndex < p_dtData.Rows.Count; rowIndex++)
{
for (int colIndex = 0; colIndex < p_dtData.Columns.Count; colIndex++)
{
// Create the columns in the Excel .
if (rowIndex == 0)
{
// Write column name into Excel cell .
l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = p_dtData.Columns[colIndex].ColumnName;
l_objExcelSheet.Cells.Font.Color = System.Drawing.Color.Black;

}
// Write row value into excel cell .
l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] = p_dtData.Rows[rowIndex][colIndex];

// Formating Excel cell on the bases of column type datatable
if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.Decimal"))
{
// Currency Format .
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat
= "$#,##0.00_);[Red]($#,##0.00)";
}
else if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.DateTime"))
{
//datetime format
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat
= "mm-d-yy h:mm:ss AM/PM";

}
else if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.String"))
{
// Set Font
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].Font.Bold = true;
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].Font.Name = "Arial Narrow";
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].Font.Size = "20";

}
else if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.Single"))
{
// Set percentage .
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat = "0.00%";
}

}
}
// Auto fit automatically adjust the width of columns of excel in givien range .
l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[p_dtData .Rows .Count , p_dtData .Columns.Count]].EntireColumn.AutoFit();

// To set the color, font size and bold, over top row, which represent columns of data table .
l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[1, p_dtData.Columns.Count]].Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green );

l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[1, p_dtData.Columns.Count]].Font.Bold = true;

l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[1, p_dtData.Columns.Count]].Font.Size = 15;

l_objExcelworkBook.SaveAs("F:\\testPersonExceldata.xlsx");
l_objExcelworkBook.Close();
l_objExcel.Quit();
MessageBox.Show("Done");
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);

}

finally
{
l_objExcelSheet = null;
l_objExcelworkBook = null;
}
}

 

Written By: Manoj Pant, Software Engineer, Mindfire Solutions