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:

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 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. 

public void WriteDataIntoExcelWithFormats(System.Data.DataTable p_dtData)
{
      //Step 1 : 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, that 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;
       }
 }