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>
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);
}
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);
}
}
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");
}
}
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;
}
}
Upload files
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));
}
...
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);
}
}
...
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);
}
}
...
In this sample, their execution times doesn't have any differences.
After rewriting the values, cell styles haven't been changed.
Result
Top comments (0)