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.

Helpful GIT Commands

 Some helpful GIT Commands:

	# Undo all changes
	# ----------------
	git reset --hard origin/<branch_name/master>
	git pull
	# Overwrite Local Files
	# ---------------------
	git fetch --all
	git reset --hard origin/<branch_name/master>
	# OR
	git reset --hard origin/<branch_name/master>
	# Undo Specific files
	# -------------------
	git checkout -- <filename.txt>
	# To get git commits in a certain time period
	# -------------------------------------------
	git log --pretty=format:"%ad - %an: %s" --after="2022-03-01" --until="2022-04-01"
	# GIT remove saved username and password
	# --------------------------------------
	git config --system --unset credential.helper

view raw GIT.ps1 hosted with ❤ by GitHub

How to check if your machine supports SSL or TLS 1.1, 1.2 or 1.3

Open the following link in the browser: https://clienttest.ssllabs.com:8443/ssltest/viewMyClient.html

Scroll down to section: Protocol Features

Avoid CORS issues in Chrome, Firefox, Safari and Edge for local JavaScript development

Setup a simple proxy using nodejs and npm to bypass CORS issues. 

This will solve the following issue in the web browsers:

No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:3000' is therefore not allowed access. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disable

Install the following npm package globally:

npm install -g local-cors-proxy

Now you are ready to setup a local proxy. Let's say the URL that's throwing the CORS issue is: 

https://www.example.com/v1/api

To start the proxy, execute the following command:

lcp --proxyUrl https://www.example.com

You will get the following output:

Note: Do not close this window as long as you need the proxy.

Now, in the JavaScript code, API endpoint will be:

http://localhost:8010/proxy/v1/api

This will make a request to URL: https://www.example.com/v1/api without any issues.

Additional options to setup the proxy: 

Option	Example	Default
--proxyUrl	https://www.google.ie
--proxyPartial	foo	proxy
--port	8010	8010
--credentials	(no value needed)	false
--origin	http://localhost:4200	*

Original article can be viewed here.

Creating an angular service to share data between components using session storage

Create the session storage service as:

	import { Injectable, OnDestroy, OnInit } from "@angular/core";
	import { ReplaySubject } from "rxjs";
	import { environment } from "../../../environments/environment";
	@Injectable({
	providedIn: "root",
	})
	export class StorageService implements OnInit, OnDestroy {
	private storageChange$: ReplaySubject<{
	key: string;
	value: string;
	}> = new ReplaySubject();
	constructor() {}
	ngOnInit() {
	sessionStorage.clear();
	localStorage.clear();
	}
	ngOnDestroy() {
	this.storageChange$.unsubscribe();
	}
	public store(key: string, data: any): void {
	// log in console for each store method call. In development mode only
	if (environment.production === false) {
	console.log("StorageService.store():", key, sessionStorage);
	}
	sessionStorage.setItem(key, JSON.stringify(data));
	this.storageChange$.next({ key: key, value: JSON.stringify(data) });
	}
	public get() {
	// log in console if key exists in data store. In development mode only
	if (environment.production === false) {
	console.log("StorageService.get()", sessionStorage);
	}
	//window[area].getItem(key);
	return this.storageChange$;
	}
	}

view raw SessionStorage.Service.ts hosted with ❤ by GitHub

To store and retrieve the session storage data, use the service as:

	// to store data
	this._storageService.store(
	key, // as string
	object // any object to store
	);
	// to retrieve data
	this._storageService
	.get()
	.pipe(
	filter(({ key }) => key === KEY_AS_STRING),
	map((data) => {
	return JSON.parse(data.value) as Object;
	})
	)
	.subscribe((data) => {
	// use data
	});

view raw Example.SessionStorage.ts hosted with ❤ by GitHub

Creating an angular service to share data between components

Create a Data store to save data as key and values. E.g:

	export class DataStore {
	private map = new Map<string, any>();
	add(key: string, value: any) {
	this.map.set(key, value);
	}
	hasKey(key: string) {
	return this.map.has(key);
	}
	getValue(key: string) {
	return this.map.get(key);
	}
	remove(key: string) {
	if (this.hasKey(key)) {
	this.map.delete(key);
	return true;
	} else {
	return false;
	}
	}
	}

view raw DataStore.ts hosted with ❤ by GitHub

Note: It uses build in Map data structure and it already has function to add, remove, delete and check objects.

Now let's create a service.

	import { Injectable } from "@angular/core";
	import { Subject, Observable } from "rxjs";
	import { DataStore } from "../models/datastore.model";
	import { environment } from "../../../environments/environment";
	@Injectable({
	providedIn: "root",
	})
	export class StorageService {
	private _dataStore = new DataStore();
	private readonly changes = new Subject<DataStore>();
	constructor() {}
	public get(): Observable<DataStore> {
	// log in console if key exists in data store. In development mode only
	if (environment.production === false) {
	console.log("StorageService.get()", this._dataStore);
	}
	return this.changes.asObservable();
	}
	public store(key: string, data: any): void {
	// check if object already exist in data store and remove it
	if (this._dataStore.hasKey(key)) {
	this._dataStore.remove(key);
	}
	this._dataStore.add(key, data);
	this.changes.next(this._dataStore);
	// log in console for each store method call. In development mode only
	if (environment.production === false) {
	console.log("StorageService.store() : DataStore:", key, this._dataStore);
	}
	}
	public remove(key) {
	// log in console if key exists in data store. In development mode only
	if (environment.production === false) {
	console.log(
	"StorageService.remove() : DataStore has key:",
	this._dataStore.hasKey(key)
	);
	}
	if (this._dataStore.hasKey(key)) {
	this._dataStore.remove(key);
	this.changes.next(this._dataStore);
	}
	}
	}

view raw DataStore.Service.ts hosted with ❤ by GitHub

To store and save data call the service as:

	// to store data
	this._dataStorageService.store(
	key, // string
	object //any item
	);
	// to retrieve data, can be used any component
	this._dataStorageService.get().subscribe((data) => {
	console.log(data);
	});

view raw Example.DataStore.ts hosted with ❤ by GitHub

Using C# to read an update excel files

Create a new solution and add the DocumentFormat.OpenXml Nuget packages to this solution.

Add a new class ExcelManager.cs with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using Logger;
using DocumentFormat.OpenXml;

namespace Demo
{
    class ExcelManager : IDisposable
    {
        #region Private Variables
        SpreadsheetDocument _document;
        Sheet _sheet;
        Worksheet _worksheet;
        #endregion

        #region Public Properties
        public IEnumerable<Row> Rows { get; private set; }
        #endregion

        #region Constructor
        public ExcelManager(string filePath)
        {
            if(!File.Exists(filePath))
            {
                Logger.log.Error($"Error in DataManager(). File with the following path does not exists:{filePath}");
                return;
            }
            else
            {
                Logger.log.Info($"Processing file at path: {filePath}");
                //Open the Excel file in Edit Mode using OpenXml.
                _document = SpreadsheetDocument.Open(filePath, true);
                _sheet = _document.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
                _worksheet = (_document.WorkbookPart.GetPartById(_sheet.Id.Value) as WorksheetPart).Worksheet;
                Rows = _worksheet.GetFirstChild<SheetData>().Descendants<Row>();
            }
        }
        #endregion

        public void Dispose()
        {
            _document.Dispose();
            _document = null;
            _sheet = null;
            _worksheet = null;
        }

        #region Public Excel Functions
        public string GetCellValue(Cell cell)
        {
            return _document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(Convert.ToInt32(cell.InnerText)).InnerText;
        }

        public string GetCellValueByReference(string cellReference)
        {
            var cell = _worksheet.Descendants<Cell>().Where(c => c.CellReference == cellReference).FirstOrDefault();

            if (cell != null)
            {
                return GetCellValue(cell);
            }
            else
            {
                return null;
            }
        }

        public void UpdateCell(string columnName, uint rowIndex, string value)
        {
            try
            {
                Cell cell = GetCellByColumnName(columnName, rowIndex);
                if (cell != null)
                {
                    cell.CellValue = new CellValue(value);
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                    // Save the worksheet.
                    _worksheet.Save();
                }
                else
                {
                    Logger.log.Warn($"Error in DataManager.UpdateCell(). Error: Cell is null");
                }
            }
            catch (Exception ex)
            {
                Logger.log.Error($"Error in DataManager.UpdateCell().Error: {ex.Message} StackTrace:{ex.StackTrace}");
            }
        }
        #endregion

        #region Private Excel functions
        private Cell GetCellByColumnName(string columnName, uint rowIndex)
        {
            Row row = GetRow(rowIndex);

            if (row == null)
                return null;

            string cellReference = GetColumnReference(columnName).FirstOrDefault().ToString();

            if (cellReference == null)
            { 
                Logger.log.Warn($"Error in DataManager.GetCellByColumnName(). Error: Cell Reference is null");
                return null;
            }
            else
                return row.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, cellReference + (rowIndex + 1), true) == 0).First();
        }

        private string GetColumnReference(string columnName)
        {
            var cell = GetRow(0).Elements<Cell>().FirstOrDefault(c => GetCellValue(c) == columnName);

            if (cell == null)
            {
                Logger.log.Warn($"Error in DataManager.GetColumnReference(). Error: Cannot retrieve cell with column name: {columnName}");
                return null;
            }
            else
                return cell.CellReference.Value;
        }

        private Row GetRow(uint rowIndex)
        {
            return Rows.ElementAt(int.Parse(rowIndex.ToString()));
        }
        #endregion
    }
}

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.