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

Masui Masanori - Mar 20 '22 - - Dev Community

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 

<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>
Enter fullscreen mode Exit fullscreen mode

main.page.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);
}
Enter fullscreen mode Exit fullscreen mode

FileController.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);
    }
}
Enter fullscreen mode Exit fullscreen mode

SpreadsheetUpdater.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");
    }
}
Enter fullscreen mode Exit fullscreen mode

WriteValueSamples.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;
    }
}
Enter fullscreen mode Exit fullscreen mode

Upload files

Image description

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 

...
    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));
    }
...
Enter fullscreen mode Exit fullscreen mode

Search cells from their values

How can I search rewrite target cells?
I can search by all cell values.

SpreadsheetUpdater.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);  
        }
    }
...
Enter fullscreen mode Exit fullscreen mode

I also can use IXLRange.Search.

SpreadsheetUpdater.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);
        }
    }
...
Enter fullscreen mode Exit fullscreen mode

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

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

Result

Image description

Image
LeetCode Day32 Dynamic Programming Part 5

leetcode, java, algorithms
Image
Build a server with python flask

webdev, beginners, tutorial, python
Image
Sizing a metering current transformer (CT)

electrical, transformer
Image
Revolutionizing React Applications with AI Voice Integration and Conversational AI

ai, react, javascript, typescript
Image
THE ROLE OF SEMANTIC HTML IN ENHANCING SEO AND WEB ACCESSIBILITY
Image
BDD - Desenvolvimento Orientado pelo Comportamento
Image
How to Use AI in Trading?

ai, beginners
Image
Mastering Go: A Comprehensive Guide to Building Production-Ready Apps 🚀

getvm, programming, freetutorial, technicaltutorials
Image
reshape() and view() in PyTorch

pytorch, reshape, view, function
Image
1st of 100 days?

frontend, frontendchallenge, learning
Image
Creating Complex Animations with CSS

scss, css, tailwindcss, sass
Image
Quine: Self replicating computer programs

quine, dna, selfrep, python
Image
Bộ laravel khoapham cho devs mới
Image
React: useEffect
Image
This is not a presentation..."

design, frontend, learning
Image
Some lesser known tools that helped me learn Web Development

webdev, beginners
Image
[Unity] Publish assets that automatically organizes folders.

unity3d, asset, free, automation
Image
Part 2: Learning HTML

html
Image
How to Build a Frontend for Excel (Fast & Easy)

tutorial, database, beginners, development
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player