[ASP.NET Core][ClosedXML] Get addresses from cell values and rewrite them

<input type="file" accept=".xlsx,.xlsm" id="send_file_input"> <button onclick="Page.sendFile()">Send File</button> <a id="download_target"></a> <script src="/js/main.page.js"></script> 

export async function sendFile(): Promise<void> { const file = await getSelectedFile(); if(file == null) { return; } if(/(xlsx|xlsm)+$/.test(file.name) === false) { console.error("Only for xlsx or xlsm"); return; } const fileData = await getSelectedFileData(file); if(fileData == null) { return; } const formData = new FormData(); formData.append("file", new Blob([fileData])) const response = await fetch("/files", { method: "POST", headers: { "File-Name": file.name, "File-Type": file.type }, body: formData }); if(response.ok) { await handleResponse(response); } else { console.error(response.statusText); } } async function handleResponse(response: Response): Promise<void> { switch(response.headers.get("Content-Type")){ case "application/json": await handleResultAsJson(response); break; default: await handleResultAsFile(response); break; } } async function handleResultAsJson(response: Response): Promise<void>{ const json = await response.json(); const result = JSON.parse(JSON.stringify(json)); if(result?.succeeded != null && result.succeeded === true){ alert(result.errorMessage); return; } alert("Failed"); } async function handleResultAsFile(response: Response): Promise<void> { const target = document.getElementById("download_target") as HTMLAnchorElement; target.download = response.headers.get("File-Name") ?? "file"; target.href = window.URL.createObjectURL(await response.blob()); target.click(); } function getSelectedFile(): File|null { const fileInput = document.getElementById("send_file_input") as HTMLInputElement; const file = fileInput.files?.item(0); if(file == null) { console.error("File was null"); return null; } return file; } async function getSelectedFileData(file: File): Promise<Uint8Array|null> { const fileData = await file.arrayBuffer(); if(fileData == null) { console.error("Failed getting ArrayBuffer"); return null; } return new Uint8Array(fileData); } 

using BookshelfSample.Apps; using BookshelfSample.Files; using Microsoft.AspNetCore.Mvc; namespace BookshelfSample.Controllers; public class FileController: Controller { private readonly ISpreadsheetUpdater spreadsheetUpdater; public FileController(ISpreadsheetUpdater spreadsheetUpdater) { this.spreadsheetUpdater = spreadsheetUpdater; } [HttpPost] [Route("files")] public async Task<IActionResult> WriteFile([FromForm] IFormFile? file) { if(file == null) { Response.Headers["Content-Type"] = "application/json"; return Json(ActionResultFactory.GetFailed("file was null")); } var fileName = Request.Headers["File-Name"]; if(string.IsNullOrEmpty(fileName)) { Response.Headers["Content-Type"] = "application/json"; return Json(ActionResultFactory.GetFailed("FileName was null")); } var contentType = Request.Headers["File-Type"]; if(string.IsNullOrEmpty(contentType)) { Response.Headers["Content-Type"] = "application/json"; return Json(ActionResultFactory.GetFailed("ContentType was null")); } var result = await this.spreadsheetUpdater.SearchAndWriteAsync(file, fileName, contentType); Response.Headers["File-Name"] = result.FileName; return File(result.FileData, result.ContentType, result.FileName); } } 

using System.Text; using System.Text.Json; using ClosedXML.Excel; using BookshelfSample.Apps; namespace BookshelfSample.Files; public class SpreadsheetUpdater: ISpreadsheetUpdater { private record CellAddress(int Column, int Row); private record CellArea(CellAddress AreaFrom, CellAddress AreaTo); public async Task<DownloadFile> SearchAndWriteAsync(IFormFile file, string fileName, string contentType) { try { // load uploaded file data. using var memoryStream = new MemoryStream(); using(var stream = file.OpenReadStream()) { await stream.CopyToAsync(memoryStream); } using var saveStream = new MemoryStream(); using (var book = new XLWorkbook(memoryStream)) { foreach(var sheet in book.Worksheets) { // TODO: Search and rewrite cell values. } book.SaveAs(saveStream); } return new DownloadFile(fileName, saveStream.ToArray(), contentType); } catch(Exception ex) { return GenerateFailedFile("Something wrong"); } } private DownloadFile GenerateFailedFile(string errorMessage) { var failedResult = ActionResultFactory.GetFailed(errorMessage); var resultJsonData = Encoding.UTF8.GetBytes(JsonSerializer.Serialize(failedResult)); return new DownloadFile("failed.json", resultJsonData, "application/json"); } } 

namespace BookshelfSample.Files; public static class WriteValueSamples { public static Dictionary<string, string> GetValues() { var values = new Dictionary<string, string>(); values.Add("A01", "SampleA"); values.Add("A02", "SampleB"); values.Add("A03", "SampleC"); values.Add("A04", "SampleD"); values.Add("A05", "SampleE"); values.Add("B01", "あ"); values.Add("B02", "い"); values.Add("B03", "う"); values.Add("B04", "え"); values.Add("B05", "お"); values.Add("C01", "00111"); values.Add("C02", "00222"); values.Add("C03", "00333"); values.Add("C04", "00444"); values.Add("C05", "00555"); values.Add("D01", "AA"); values.Add("D02", "BB"); values.Add("D03", "CC"); values.Add("D04", "DD"); values.Add("D05", "EE"); values.Add("E01", "!#"); values.Add("E02", "$%&"); values.Add("E03", "()"); values.Add("E04", "|=\\"); values.Add("E05", "]-^["); return values; } } 

... public async Task<DownloadFile> SearchAndWriteAsync(IFormFile file, string fileName, string contentType) { try { ... using var saveStream = new MemoryStream(); using (var book = new XLWorkbook(memoryStream)) { foreach(var sheet in book.Worksheets) { var searchArea = this.GetSearchAreas(sheet); // TODO: search and write values } book.SaveAs(saveStream); } return new DownloadFile(fileName, saveStream.ToArray(), contentType); } catch(Exception ex) { return GenerateFailedFile("Something wrong"); } } private CellArea GetSearchAreas(IXLWorksheet sheet) { var printArea = this.GetPrintAreaAddresses(sheet); var usedAreaFrom = sheet.FirstCellUsed().Address; var usedAreaTo = sheet.LastCellUsed().Address; if(printArea == null) { // if the file doesn't have print areas, the result will be created by CellUsed return new CellArea(AreaFrom: new CellAddress(usedAreaFrom.ColumnNumber, usedAreaFrom.RowNumber), new CellAddress(usedAreaTo.ColumnNumber, usedAreaTo.RowNumber)); } // get the smallest area from PrintArea(+ Page breaks) and CellUsed. var columnFrom = (printArea.AreaFrom.Column > usedAreaFrom.ColumnNumber)? printArea.AreaFrom.Column: usedAreaFrom.ColumnNumber; var columnTo = (printArea.AreaTo.Column > usedAreaTo.ColumnNumber)? usedAreaTo.ColumnNumber: printArea.AreaTo.Column; var rowFrom = (printArea.AreaFrom.Row > usedAreaFrom.RowNumber)? printArea.AreaFrom.Row: usedAreaFrom.RowNumber; var rowTo = (printArea.AreaTo.Row > usedAreaTo.RowNumber)? usedAreaTo.RowNumber: printArea.AreaTo.Row; return new CellArea(AreaFrom: new CellAddress(columnFrom, rowFrom), new CellAddress(columnTo, rowTo)); } private CellArea? GetPrintAreaAddresses(IXLWorksheet sheet) { var printArea = sheet.PageSetup.PrintAreas.FirstOrDefault(); if(printArea == null) { return null; } var columnTo = 1; var rowTo = 1; var cellTo = printArea.LastCell().Address; // I only can get the last cell of page breaks. var columnBreak = sheet.PageSetup.ColumnBreaks.FirstOrDefault(); if(columnBreak <= 0 || cellTo.ColumnNumber < columnBreak) { columnTo = cellTo.ColumnNumber; } else { columnTo = columnBreak; } var rowBreak = sheet.PageSetup.RowBreaks.FirstOrDefault(); if(rowBreak <= 1 || cellTo.RowNumber < rowBreak) { rowTo = cellTo.RowNumber; } else { rowTo = rowBreak; } var firstCell = printArea.FirstCell().Address; return new CellArea(AreaFrom: new CellAddress(firstCell.ColumnNumber, firstCell.RowNumber), new CellAddress(columnTo, rowTo)); } ... 

... public async Task<DownloadFile> SearchAndWriteAsync(IFormFile file, string fileName, string contentType) { try { ... using var saveStream = new MemoryStream(); using (var book = new XLWorkbook(memoryStream)) { foreach(var sheet in book.Worksheets) { var searchArea = this.GetSearchAreas(sheet); this.WriteValues(sheet, searchArea); } book.SaveAs(saveStream); } return new DownloadFile(fileName, saveStream.ToArray(), contentType); } catch(Exception ex) { return GenerateFailedFile("Something wrong"); } } ... private void WriteValues(IXLWorksheet sheet, CellArea area) { var values = WriteValueSamples.GetValues(); var searchArea = sheet.Range(area.AreaFrom.Row, area.AreaFrom.Column, area.AreaTo.Row, area.AreaTo.Column); // Search only for cells that have a value foreach(var cell in searchArea.CellsUsed()) { var key = cell.GetString(); var value = values.FirstOrDefault(v => v.Key == key); if(string.IsNullOrEmpty(value.Key)) { continue; } cell.SetValue<string>(value.Value); } } ... 

... private void WriteValues(IXLWorksheet sheet, CellArea area) { var values = WriteValueSamples.GetValues(); var searchArea = sheet.Range(area.AreaFrom.Row, area.AreaFrom.Column, area.AreaTo.Row, area.AreaTo.Column); foreach(var v in values) { var target = searchArea.Search(v.Key).FirstOrDefault(); if(target == null) { continue; } target.SetValue<string>(v.Value); } } ...