Breakout xlsx file handling into separate class from cli

[dkopec]

Make it easier to incorporate new functions and interfaces

According to Copilot:

ExcelHandler.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ExcelFindReplace
{
    public class ExcelHandler : IDisposable
    {
        private readonly SpreadsheetDocument _document;
        private readonly WorkbookPart _wbPart;
        private readonly WorksheetPart _wsPart;

        public ExcelHandler(FileInfo filePath, string sheetName)
        {
            _document = SpreadsheetDocument.Open(filePath.FullName, true);
            _wbPart = _document.WorkbookPart ?? throw new ArgumentException("WorkbookPart is null");
            Sheet? theSheet = _wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName)
                              ?? throw new ArgumentException($"A sheet with the name {sheetName} could not be found in file: {filePath.FullName}");
            _wsPart = (WorksheetPart)_wbPart.GetPartById(theSheet.Id!);
        }

        public string? ReplaceTextInExcel(string findText, string replaceText, int rowOffset = 0, int columnOffset = 0)
        {
            string? value = null;
            Cell? theCell = _wsPart.Worksheet?.Descendants<Cell>()?.FirstOrDefault(c => c.CellValue != null && GetCellValue(_wbPart, c) == findText);

            if (theCell == null)
            {
                Console.WriteLine($"Did not find value: {findText}");
            }
            else
            {
                Console.WriteLine($"Found value: {findText} in cell: {theCell?.CellReference}");
                string? cellReference = theCell?.CellReference?.Value;
                string column = new string(cellReference?.Where(char.IsLetter).ToArray());
                string row = new string(cellReference?.Where(char.IsDigit).ToArray());
                string nextColumn = ((char)(column[0] + columnOffset)).ToString();
                int nextRow = int.Parse(row) + rowOffset;
                string adjacentCellReference = nextColumn + nextRow;
                Cell? adjacentCell = _wsPart?.Worksheet?.Descendants<Cell>().FirstOrDefault(c => c.CellReference?.Value == adjacentCellReference);

                if (adjacentCell != null && adjacentCell.CellValue != null)
                {
                    Console.WriteLine($"Offset cell {adjacentCellReference} contains: {GetCellValue(_wbPart, adjacentCell)}");
                    SetCellValue(_wbPart, adjacentCell, replaceText);
                    value = GetCellValue(_wbPart, adjacentCell);
                    Console.WriteLine($"Offset cell {adjacentCellReference} replaced with: {value}");
                }
                else
                {
                    Console.WriteLine($"Adjacent cell {adjacentCellReference} is empty or not found.");
                }
            }
            return value;
        }

        public string? FindTextInExcel(string findText)
        {
            Cell? theCell = _wsPart.Worksheet?.Descendants<Cell>()?.FirstOrDefault(c => c.CellValue != null && GetCellValue(_wbPart, c) == findText);

            if (theCell != null)
            {
                Console.WriteLine($"Found value: {findText} in cell: {theCell?.CellReference}");
                return theCell?.CellReference?.Value;
            }
            else
            {
                Console.WriteLine($"Did not find value: {findText}");
                return null;
            }
        }

        private static string GetCellValue(WorkbookPart wbPart, Cell cell)
        {
            string? value = cell.InnerText;
            if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString)
            {
                var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                if (stringTable is not null)
                {
                    value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                }
            }
            return value;
        }

        private static void SetCellValue(WorkbookPart wbPart, Cell cell, string value)
        {
            if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString)
            {
                var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                if (stringTable is not null)
                {
                    OpenXmlElement sharedString = stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText));
                    sharedString.ReplaceChild(new Text(value), sharedString.FirstChild);
                    stringTable.SharedStringTable.Save();
                }
            }
        }

        public void Dispose()
        {
            _document.Dispose();
        }
    }
}

Program.cs

using System.CommandLine;

namespace ExcelFindReplace
{
    public partial class Program
    {
        static async Task<int> Main(string[] args)
        {
            var fileOption = new Argument<FileInfo>(
                name: "file",
                description: "The file path to use for the find and replace.");

            var sheetNameOption = new Option<string>(
                name: "--sheet",
                description: "The sheet to look in the spreadsheet.");

            var findOption = new Option<string>(
                name: "--find",
                description: "The value to look for in the spreadsheet.");

            var replaceOption = new Option<string>(
                name: "--replace",
                description: "The value to replace with the cell with in the spreadsheet.");

            var rowOffsetOption = new Option<int>(
                name: "--row_offset",
                description: "The value to replace with the cell with in the spreadsheet.",
                getDefaultValue: () => 0);

            var colOffsetOption = new Option<int>(
                name: "--column_offset",
                description: "The value to replace with the cell with in the spreadsheet.",
                getDefaultValue: () => 0);

            var rootCommand = new RootCommand("Excel xlsx find find and replace.");

            var replaceCommand = new Command("replace", "find and replace string")
            {
                fileOption,
                sheetNameOption,
                findOption,
                replaceOption,
                rowOffsetOption,
                colOffsetOption
            };

            var findCommand = new Command("find", "find string")
            {
                fileOption,
                sheetNameOption,
                findOption
            };

            replaceCommand.SetHandler((filePath, sheetName, findText, replaceText, rowOffset, columnOffset) =>
                {
                    using var excelHandler = new ExcelHandler(filePath!, sheetName);
                    excelHandler.ReplaceTextInExcel(findText, replaceText, rowOffset, columnOffset);
                },
                fileOption, sheetNameOption, findOption, replaceOption, rowOffsetOption, colOffsetOption);

            findCommand.SetHandler((filePath, sheetName, findText) =>
                {
                    using var excelHandler = new ExcelHandler(filePath!, sheetName);
                    excelHandler.FindTextInExcel(findText);
                },
                fileOption, sheetNameOption, findOption);

            rootCommand.Add(replaceCommand);
            rootCommand.Add(findCommand);

            return await rootCommand.InvokeAsync(args);
        }
    }
}