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
- Add a reference of "Microsoft.Office.Interop.Excel" into the project.
- Create a data table for the data, that will be used to display the Excel file.
- 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");
-
-
- 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)); /
-
-
- 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)
- {
-
- Microsoft.Office.Interop.Excel.Application l_objExcel;
- Microsoft.Office.Interop.Excel.Workbook l_objExcelworkBook;
- Microsoft.Office.Interop.Excel.Worksheet l_objExcelSheet;
-
- try
- {
-
- l_objExcel = new Microsoft.Office.Interop.Excel.Application();
-
-
- l_objExcelworkBook = l_objExcel.Workbooks.Add(Type.Missing);
-
-
- l_objExcelSheet = l_objExcelworkBook.ActiveSheet;
- l_objExcelSheet.Name = "Report";
-
-
- l_objExcel.DisplayAlerts = false;
-
-
- for (int rowIndex = 0; rowIndex < p_dtData.Rows.Count; rowIndex++)
- {
- for (int colIndex = 0; colIndex < p_dtData.Columns.Count; colIndex++)
- {
-
- if (rowIndex == 0)
- {
-
- l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = p_dtData.Columns[colIndex].ColumnName;
- l_objExcelSheet.Cells.Font.Color = System.Drawing.Color.Black;
-
- }
-
-
-
- l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] = p_dtData.Rows[rowIndex][colIndex];
-
-
-
- if (p_dtData.Columns[colIndex].DataType == Type.GetType("System.Decimal"))
- {
-
- 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"))
- {
-
- 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"))
- {
-
- 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"))
- {
-
- l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1], l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat = "0.00%";
- }
-
- }
- }
-
-
-
- l_objExcelSheet.Range[l_objExcelSheet.Cells[1, 1], l_objExcelSheet.Cells[p_dtData .Rows .Count , p_dtData .Columns.Count]].EntireColumn.AutoFit();
-
-
- 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;
- }
- }