How to Set Formats In Excel File From C# Code

We will set "Font" , "Currency", "Date" and "Percentage" formate in Excel from C# . For creating Excel, we will use the "Microsoft.Office.Interop.Excel" DLL.

Procedure

  1. Add a reference of "Microsoft.Office.Interop.Excel" into the project.
  2. Create a data table for the data, that will be used to display the Excel file.
  3. Write data from a data table to Excel and set the format.

Step 1

The following describes how to create the data table.

Get the data to display over an Excel file.

For example:

  1. public System.Data.DataTable GetData()  
  2. {  
  3.       System.Data.DataTable l_dtEmployee = new System.Data.DataTable("Employee");  
  4.   
  5.       // Create columns for l_dtEmployee  .                
  6.       l_dtEmployee.Columns.Add("EmpId"typeof(int));  
  7.       l_dtEmployee.Columns.Add("Name"typeof(string));  
  8.       l_dtEmployee.Columns.Add("Sex"typeof(string));  
  9.       l_dtEmployee.Columns.Add("DateOfReport"typeof(DateTime));    
  10.       l_dtEmployee.Columns.Add("City"typeof(string));  
  11.       l_dtEmployee.Columns.Add("Salary"typeof(Decimal));      
  12.       l_dtEmployee.Columns.Add("Leaves"typeof(float));      /  
  13.   
  14.       // Create rows for l_dtEmployee .  
  15.       l_dtEmployee.Rows.Add(10, "Abhishek""M", DateTime.Now, "Nainital", 5500, .34);  
  16.       l_dtEmployee.Rows.Add(20, "Digvijay""M", DateTime.Now, "Shimla", 4800, .98);  
  17.       l_dtEmployee.Rows.Add(30, "Shrish""M", DateTime.Now, "Dehradun", 6700, .31);  
  18.       l_dtEmployee.Rows.Add(40, "Shaifali""F", DateTime.Now, "Dehradun", 7000, .10);  
  19.       l_dtEmployee.Rows.Add(50, "Sonam""F", DateTime.Now, "Delhi", 6500, .43);  
  20.       l_dtEmployee.Rows.Add(60, "Ankur""M", DateTime.Now, "Delhi", 4500, .33);  
  21.       l_dtEmployee.Rows.Add(70, "Vipin""M", DateTime.Now, "Dehradun", 8000, .44);  
  22.       l_dtEmployee.Rows.Add(80, "Jasmeen""F", DateTime.Now, "Delhi", 6000, .65);  
  23.       l_dtEmployee.Rows.Add(90, "Rakesh""M", DateTime.Now, "Jaisalmer", 2000, .32);  
  24.       l_dtEmployee.Rows.Add(100, "Annirud""M", DateTime.Now, "Rohtak", 3900, .22);  
  25.   
  26.       return l_dtEmployee;  
  27.   
  28. }  
Step 2

Now we will create objects of Application, Workbook and Worksheet of Excel, to write and format 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%" .

The following function writes the data into l_objExcel and sets the formating with data using the Microsoft.Office.Interop.Excel DLL.

We are formating records of Excel on the basis of the data type of data table columns. 
  1. public void WriteDataIntoExcelWithFormats(System.Data.DataTable p_dtData)  
  2. {  
  3.       //Step 1 : Add reference of Microsoft.Office.Interop.l_objExcel dll into project .  
  4.       Microsoft.Office.Interop.Excel.Application l_objExcel;  
  5.       Microsoft.Office.Interop.Excel.Workbook l_objExcelworkBook;  
  6.       Microsoft.Office.Interop.Excel.Worksheet l_objExcelSheet;  
  7.   
  8.       try  
  9.       {  
  10.             // Create the object of l_objExcel application .  
  11.             l_objExcel = new Microsoft.Office.Interop.Excel.Application();  
  12.   
  13.            // Create workbook .  
  14.            l_objExcelworkBook = l_objExcel.Workbooks.Add(Type.Missing);  
  15.   
  16.            // Get active sheet from workbook  
  17.            l_objExcelSheet = l_objExcelworkBook.ActiveSheet;  
  18.            l_objExcelSheet.Name = "Report";  
  19.                  
  20.            // For showing alert message of overwritting of existing file .  
  21.            l_objExcel.DisplayAlerts = false;  
  22.   
  23.            // Fill the l_objExcel from p_dtData data  .  
  24.            for (int rowIndex = 0; rowIndex < p_dtData.Rows.Count; rowIndex++)  
  25.            {                      
  26.                 for (int colIndex = 0; colIndex < p_dtData.Columns.Count; colIndex++)  
  27.                 {  
  28.                         // Create the columns in the Excel .  
  29.                         if (rowIndex == 0)  
  30.                         {  
  31.                             // Write column name into Excel cell .  
  32.                             l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = p_dtData.Columns[colIndex].ColumnName;                              
  33.                             l_objExcelSheet.Cells.Font.Color = System.Drawing.Color.Black;  
  34.                             
  35.                         }  
  36.   
  37.   
  38.                     // Write row value into Excel cell .  
  39.                      l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] = p_dtData.Rows[rowIndex][colIndex];  
  40.   
  41.                      // Formating Excel cell on the bases of column type datatable  
  42.   
  43.                         if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.Decimal"))  
  44.                         {  
  45.                             // Currency Format .  
  46.                             l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat  
  47.                                 = "$#,##0.00_);[Red]($#,##0.00)";  
  48.                         }  
  49.                         else if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.DateTime"))  
  50.                         {  
  51.                             //datetime format  
  52.                             l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat  
  53.                                 = "mm-d-yy h:mm:ss AM/PM";  
  54.   
  55.                         }  
  56.                         else if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.String"))  
  57.                         {  
  58.                             // Set Font  
  59.                             l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].Font.Bold = true;  
  60.                             l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].Font.Name = "Arial Narrow";  
  61.                             l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].Font.Size = "20";  
  62.   
  63.                         }  
  64.                         else if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.Single"))  
  65.                         {  
  66.                             // Set percentage  .  
  67.                             l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat = "0.00%";  
  68.                         }  
  69.   
  70.                     }  
  71.               }  
  72.   
  73.                 
  74.          // Auto fit automatically adjust the width of columns of Excel  in givien range .  
  75.              l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[p_dtData .Rows .Count , p_dtData .Columns.Count]].EntireColumn.AutoFit();  
  76.   
  77.         // To set the color, font size and bold, over top row, that represent columns of data table .  
  78.             l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[1, p_dtData.Columns.Count]].Interior.Color =  
  79.             System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green );  
  80.   
  81.            l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[1, p_dtData.Columns.Count]].Font.Bold = true;  
  82.   
  83.            l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[1,    p_dtData.Columns.Count]].Font.Size = 15;  
  84.                 
  85.   
  86.            l_objExcelworkBook.SaveAs("F:\\testPersonExceldata.xlsx");  
  87.            l_objExcelworkBook.Close();  
  88.            l_objExcel.Quit();  
  89.            MessageBox.Show("Done");  
  90.        }  
  91.   
  92.        catch (Exception ex)  
  93.        {  
  94.              MessageBox.Show(ex.Message);  
  95.                   
  96.        }  
  97.   
  98.        finally  
  99.        {  
  100.               l_objExcelSheet = null;  
  101.               l_objExcelworkBook = null;  
  102.        }  
  103.  }