Using C# to read an update excel files (version 2)

Create a new solution and add the ClosedXML.Excel Nuget packages to this solution. Add a new class ExcelManager.cs with the following code: 

	using ClosedXML.Excel;
	using Microsoft.Extensions.Logging;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	namespace Excel
	{
	class ExcelManager : IExcelManager
	{
	/// Variable for dependency injection
	private readonly ILogger _logger;
	/// Private variables
	private readonly string _filepath;
	private XLWorkbook _workbook;
	private IXLWorksheet _worksheet;
	/// <summary>
	/// Constructor
	/// </summary>
	/// <param name="logger"></param>
	public ExcelManager(ILogger<ExcelManager> logger)
	{
	_logger = logger;
	_filepath = Helper.GetConfigValueAsString(Constants.FILE_PATH);
	try
	{
	/// read the workbbok
	_workbook = new XLWorkbook(_filepath);
	/// read the worksheet
	_worksheet = _workbook.Worksheets.First();
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nUnable to read file at path: {_filepath}. Error: {ex.Message}\n");
	}
	}
	/// <summary>
	/// Returns all cells with values in a column
	/// </summary>
	/// <typeparam name="IXLCell"></typeparam>
	/// <param name=""></param>
	/// <param name=""></param>
	public IEnumerable<IXLCell> GetAllColumnCells(int columnNo)
	{
	try
	{
	return _worksheet
	.Columns()
	.FirstOrDefault(c => c.ColumnNumber() == columnNo)
	?.Cells() ?? Enumerable.Empty<IXLCell>(); /// in case target column is out of range, so foreach won't throw null-exception
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in reading data from column number :{columnNo}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in reading data from column number :{columnNo}. Error: {ex.Message}\n");
	return Enumerable.Empty<IXLCell>();
	}
	}
	public void InsertRowAbove(int rowNo, int numberofRows = 1)
	{
	try
	{
	_worksheet.Row(rowNo).InsertRowsAbove(numberofRows);
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in inserting row at:{rowNo}##Total number of rows to be inserted:{numberofRows}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in inserting row at:{rowNo}##Total number of rows to be inserted:{numberofRows}. Error: {ex.Message}\n");
	}
	}
	/// <summary>
	/// Get cell value
	/// </summary>
	/// <param name="rowNo"></param>
	/// <param name="columnNo"></param>
	/// <returns></returns>
	public string GetCellValue(int rowNo, int columnNo)
	{
	try
	{
	return _worksheet.Cell(rowNo, columnNo).GetString();
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in retrieving cell value. ##Row:{rowNo}##Column:{columnNo}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in retrieving cell value. ##Row:{rowNo}##Column:{columnNo}. Error: {ex.Message}\n");
	}
	return string.Empty;
	}
	/// <summary>
	/// Append string to cell value
	/// </summary>
	/// <param name="rowNo">Row number as int</param>
	/// <param name="columnNo">Column number as int</param>
	/// <param name="value">Value to append</param>
	public void AppendCellValue(int rowNo, int columnNo, string value)
	{
	try
	{
	_worksheet.Cell(rowNo, columnNo).Value += $". {value}";
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in appending cell value. ##Row:{rowNo}##Column:{columnNo}##Value:{value}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in appending cell value. ##Row:{rowNo}##Column:{columnNo}##Value:{value}. Error: {ex.Message}\n");
	}
	}
	/// <summary>
	/// Update cell values
	/// </summary>
	/// <param name="rowNo">Row number as int</param>
	/// <param name="columnNo">Column number as int</param>
	/// <param name="value">Value to udpate</param>
	public void UpdateCellValue(int rowNo, int columnNo, string value)
	{
	try
	{
	_worksheet.Cell(rowNo, columnNo).Value = value;
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in saving cell value. ##Row:{rowNo}##Column:{columnNo}##Value:{value}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in saving cell value. ##Row:{rowNo}##Column:{columnNo}##Value:{value}. Error: {ex.Message}\n");
	}
	}
	/// <summary>
	/// Update cell values and background color
	/// </summary>
	/// <param name="rowNo">Row number as int</param>
	/// <param name="columnNo">Column number as int</param>
	/// <param name="value">Value to udpate</param>
	/// <param name="bgColor">Background color to apply</param>
	public void UpdateCellValueWithBgColor(int rowNo, int columnNo, string value, XLColor bgColor)
	{
	try
	{
	// set bg color
	_worksheet.Cell(rowNo, columnNo).Style.Fill.SetBackgroundColor(bgColor);
	// set value in excel
	UpdateCellValue(rowNo, columnNo, value);
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in saving cell value and background color. ##Row:{rowNo}##Column:{columnNo}##Value:{value}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in saving cell value and background color. ##Row:{rowNo}##Column:{columnNo}##Value:{value}. Error: {ex.Message}\n");
	}
	}
	/// <summary>
	/// Save updated excel
	/// </summary>
	public void Save()
	{
	try
	{
	// upate header styles
	var header = _worksheet.Range("A1:E1");
	header.Style.Fill.BackgroundColor = Constants.COLOR_HEADER;
	header.SetAutoFilter();
	// save file
	_workbook.SaveAs(_filepath);
	}
	catch (Exception ex)
	{
	Console.WriteLine($"\nError in saving file at path: {_filepath}. Error: {ex.Message}\n");
	_logger.LogError($"\nError in saving file at path: {_filepath}. Error: {ex.Message}\n");
	}
	}
	}
	}

view raw ExcelManager.cs hosted with ❤ by GitHub

This provides the basic items like rows of excel data and functions to save text based on column header names and retrieve cell values.

To add logging, refer to my blog here.