[ASP.NET Core][ClosedXML] Get addresses from cell values and rewrite them
Tags: csharp, aspnetcor, closedxml
Published: 2022-03-20 04:04:05

## What I want to do?

* Updating Excel files(.xlsx or .xlsm) from client-side
* Getting sheets and finding cells by cell values
* Rewriting texts into founded cells
* Default cell values are like "A01", "B02", and so on
* The address of cell values aren't well defined
* The files are set print areas and page breaks
* All of the target cells are in the first page
* After writing aving as files and downloading them

## Environments
* .NET ver.6.0.201
* ClosedXML ver.0.95.4

## Samples
### Base project
#### Index.cshtml
```html
<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>
```

#### main.page.ts
```ts
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);
}
```

#### FileController.cs
```cs
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);
    }
}
```

#### SpreadsheetUpdater.cs
```cs
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");
    }
}
```
#### WriteValueSamples.cs
```cs
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;
    }
}
```

### Upload files

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l5u6l7rhbdtzmk0u9mdj.jpg)

## Get searching cells area
This is because the search cell area cannot be determined before loading the files.
So I get the areas from the page breaks, print areas, or used cells.

#### SpreadsheetUpdater.cs
```cs
...
    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));
    }
...
```

* [Print Areas and Page Breaks - ClosedXML/ClosedXML Wiki - ClosedXML - GitHub](https://github.com/closedxml/closedxml/wiki/Print-Areas-and-Page-Breaks)

## Search cells from their values
How can I search rewrite target cells?
I can search by all cell values.

#### SpreadsheetUpdater.cs
```cs
...
    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);  
        }
    }
...
```

I also can use IXLRange.Search.

#### SpreadsheetUpdater.cs
```cs
...
    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);
        }
    }
...
```

In this sample, their execution times doesn't have any differences.

After rewriting the values, cell styles haven't been changed.

## Result
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2e5w7t7spcd017ucjbve.jpg)