Post
Ask Question

CRUD In Excel File In C#

Excel file operations are one of the most used codes in the IT industry. Many developers get a requirement to push their data to the Excel file and read the data from the file.

In this article, I am going to explain how we can CREATE, UPDATE, DELETE and READ the records in Excel files.

Here, I am creating one sample Visual Studio C# Console Application for this. 

Create a Console application and add Microsoft.Office.Interop.Excel package from the NuGet.

C#

Creating New Excel File

Copy the below code in your program.cs file, this code will create an Excel file with sample data.

Here we are also checking if Microsoft Excel is installed on the host machine, if not it will simply return from the code.

If the file already exists in the specified path, it will ask confirmation to override or skip the file.

Once the file is created I am closing and releasing all related objects.

  1. public static string filePath = @"c:\ExcelSample\RK_Excel.xlsx";  
  2.   
  3. private static void CreateExcelFile()  
  4. {  
  5.     Excel.Application xlApp = new Excel.Application();  
  6.   
  7.     if (xlApp == null)  
  8.     {  
  9.         Console.WriteLine("Excel is not installed in the system...");  
  10.         return;  
  11.     }  
  12.   
  13.     object misValue = System.Reflection.Missing.Value;  
  14.   
  15.     Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);  
  16.     Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
  17.   
  18.     xlWorkSheet.Cells[1, 1] = "ID";  
  19.     xlWorkSheet.Cells[1, 2] = "Name";  
  20.     xlWorkSheet.Cells[2, 1] = "1001";  
  21.     xlWorkSheet.Cells[2, 2] = "Ramakrishna";  
  22.     xlWorkSheet.Cells[3, 1] = "1002";  
  23.     xlWorkSheet.Cells[3, 2] = "Praveenkumar";  
  24.   
  25.     xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue,  
  26.         Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);  
  27.   
  28.     xlWorkBook.Close();  
  29.     xlApp.Quit();  
  30.   
  31.     Marshal.ReleaseComObject(xlWorkSheet);  
  32.     Marshal.ReleaseComObject(xlWorkBook);  
  33.     Marshal.ReleaseComObject(xlApp);  
  34.   
  35.     Console.BackgroundColor = ConsoleColor.DarkBlue;  
  36.     Console.WriteLine("Excel file created successfully...");  
  37.     Console.BackgroundColor = ConsoleColor.Black;  
  38. }  

C#

C#

Reading the Excel File

The below code will help you in reading the existing Excel file.

Here I have read data at the Row level, we can also loop at Column.

  1. private static void ReadExcelFile()  
  2. {  
  3.     Console.BackgroundColor = ConsoleColor.DarkBlue;  
  4.     Console.WriteLine("\nReading the Excel File...");  
  5.     Console.BackgroundColor = ConsoleColor.Black;  
  6.   
  7.     Excel.Application xlApp = new Excel.Application();  
  8.     Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath);  
  9.     Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
  10.   
  11.     Excel.Range xlRange = xlWorkSheet.UsedRange;  
  12.     int totalRows = xlRange.Rows.Count;  
  13.     int totalColumns = xlRange.Columns.Count;  
  14.   
  15.     string firstValue, secondValue;  
  16.   
  17.     for (int rowCount = 1; rowCount <= totalRows; rowCount++)  
  18.     {  
  19.   
  20.         firstValue = Convert.ToString((xlRange.Cells[rowCount, 1] as Excel.Range).Text);  
  21.         secondValue = Convert.ToString((xlRange.Cells[rowCount, 2] as Excel.Range).Text);  
  22.   
  23.         Console.WriteLine(firstValue + "\t" + secondValue);  
  24.   
  25.     }  
  26.   
  27.     xlWorkBook.Close();  
  28.     xlApp.Quit();  
  29.   
  30.     Marshal.ReleaseComObject(xlWorkSheet);  
  31.     Marshal.ReleaseComObject(xlWorkBook);  
  32.     Marshal.ReleaseComObject(xlApp);  
  33.   
  34.     Console.WriteLine("End of the file...");  
  35. }  

Adding new Rows/Values to the Existing Excel File

In most of the cases we will be updating the existing excel file and adding the new records to them, the below code will help you to do the same operations.

Here I have taken Employee list collection do the add the recodes.

Once the records are added it will save the file without asking confirmation to override the file.

  1. private static void AddNewRowsToExcelFile()  
  2. {  
  3.     IList<Employee> empList = new List<Employee>() {  
  4.         new Employee(){ ID=1003, Name="Indraneel"},  
  5.         new Employee(){ ID=1004, Name="Neelohith"},  
  6.         new Employee(){ ID=1005, Name="Virat"}  
  7.     };  
  8.   
  9.     Excel.Application xlApp = new Excel.Application();  
  10.   
  11.     Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false,  
  12.         Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);  
  13.     Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
  14.   
  15.     Excel.Range xlRange = xlWorkSheet.UsedRange;  
  16.     int rowNumber = xlRange.Rows.Count + 1;  
  17.   
  18.     foreach (Employee emp in empList)  
  19.     {  
  20.         xlWorkSheet.Cells[rowNumber, 1] = emp.ID;  
  21.         xlWorkSheet.Cells[rowNumber, 2] = emp.Name;  
  22.         rowNumber++;  
  23.     }  
  24.   
  25.     // Disable file override confirmaton message  
  26.     xlApp.DisplayAlerts = false;  
  27.     xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook,  
  28.         Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,  
  29.         Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value,  
  30.         Missing.Value, Missing.Value);  
  31.     xlWorkBook.Close();  
  32.     xlApp.Quit();  
  33.   
  34.     Marshal.ReleaseComObject(xlWorkSheet);  
  35.     Marshal.ReleaseComObject(xlWorkBook);  
  36.     Marshal.ReleaseComObject(xlApp);  
  37.   
  38.     Console.BackgroundColor = ConsoleColor.DarkBlue;  
  39.     Console.WriteLine("\nRecords Added successfully...");  
  40.     Console.BackgroundColor = ConsoleColor.Black;  
  41. }  
  42. public class Employee  
  43. {  
  44.     public int ID { get; set; }  
  45.     public string Name { get; set; }  
  46. }  

Deleting Row, Cell from Existing Excel File

Below code will help in deleting the records from the Excel file.

Here I have given samples for deleting entire Row and selected Cell.

  1. private static void DeleteRowCellFromExcelFile()  
  2. {  
  3.     Console.BackgroundColor = ConsoleColor.DarkBlue;  
  4.     Console.WriteLine("\nDeleting the Records...");  
  5.     Console.BackgroundColor = ConsoleColor.Black;  
  6.   
  7.     Excel.Application xlApp = new Excel.Application();  
  8.   
  9.     Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", false,  
  10.         Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);  
  11.     Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);  
  12.   
  13.     Excel.Range range1 = xlWorkSheet.get_Range("A2", "B2");  
  14.   
  15.     // To Delete Entire Row - below rows will shift up  
  16.     range1.EntireRow.Delete(Type.Missing);  
  17.   
  18.     Excel.Range range2 = xlWorkSheet.get_Range("B3", "B3");  
  19.     range2.Cells.Clear();  
  20.   
  21.     // To Delete Cells - Below cells will shift up  
  22.     // range2.Cells.Delete(Type.Missing);  
  23.   
  24.     // Disable file override confirmaton message  
  25.     xlApp.DisplayAlerts = false;  
  26.     xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbook,  
  27.         Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,  
  28.         Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value,  
  29.         Missing.Value, Missing.Value);  
  30.     xlWorkBook.Close();  
  31.     xlApp.Quit();  
  32.   
  33.     Marshal.ReleaseComObject(xlWorkSheet);  
  34.     Marshal.ReleaseComObject(xlWorkBook);  
  35.     Marshal.ReleaseComObject(xlApp);  
  36. }  

Final Output

Below is the final output which I have performed in sequence, you can see the output image.

  1. public static string filePath = @"c:\ExcelSample\RK_Excel.xlsx";  
  2. static void Main(string[] args)  
  3. {  
  4.     CreateExcelFile();  
  5.     ReadExcelFile();  
  6.     AddNewRowsToExcelFile();  
  7.     ReadExcelFile();  
  8.     DeleteRowCellFromExcelFile();  
  9.     ReadExcelFile();  
  10.     Console.Read();  
  11. }  

C#

C#

I hope this article will help you in your daily coding life, please leave your comments to improve this article.


Similar Articles