Cell/region names

[TasosSarris]

Hi Can a name be defined for a cell or region?

[anmcgrath]

Not at the moment, named variables can only refer to values.

[ADefWebserver]

@TasosSarris - I am curious, how would you use this?

[TasosSarris]

I’m looking for a control that allows me to edit and view Microsoft Excel files. If I can name or tag specific cells, I can allow users to design forms under version control and, using the same control, create a viewer where other users can fill out these forms with values. The named cells will enable me to store these values in a table. In my previous Winforms application, I used the Grapecity Spread control for the same scope. Now, Blazor lacks something like this and I think that your control is very close to allowing scenarios like the above. Thank you for your quick response

[anmcgrath]

Hi @TasosSarris that sounds like a reasonable thing to do. Have you managed to use this control to load/save excel files? I'd be interested in seeing an example if so.

There is the ability to set metadata to each cell, not sure if that would help in your situation. E.g

sheet.Cells.SetMetadata(row, col, name, value);
var metaData = sheet.Cells.GetMetadata(row, col, value);

Cell metadata currently isn't handled correctly when inserting/removing rows/columns but I'm going to fix that soon.

[TasosSarris]

Thank you very much. I have not yet done the import/export. I plan to do it during the next weeks. I will send you an example as soon as I manage it.

[timrobertsusa]

nice work-- keep going!

[TasosSarris]

Hi, This is a start (using Spire.Xls):

private async Task HandleFiles(IBrowserFile mFile) { if (mFile != null) { using var stream = new MemoryStream(); await mFile.OpenReadStream().CopyToAsync(stream); stream.Position = 0;

        // Now use DocumentFormat.OpenXml to create the Excel workbook
        Worksheet aSheet = LoadExcelFile(stream);

        GetAllCellsWithValues(aSheet);
    }
}

public Worksheet LoadExcelFile(Stream fileStream)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromStream(fileStream);
    return workbook.Worksheets[0]; // Load the first worksheet
}

public string GetCellValue(Worksheet worksheet, int row, int column)
{
    CellRange cell = worksheet.Range[row, column];
    return cell.Text;
}

public Dictionary<string, string> GetAllCellsWithValues(Worksheet worksheet)
{
    var cellsWithValues = new Dictionary<string, string>();

    var aMaxRows = (from f in worksheet.Cells where !string.IsNullOrEmpty(f.DisplayedText) select f.Row).Max();
    var aMaxCols = (from f in worksheet.Cells where !string.IsNullOrEmpty(f.DisplayedText) select f.Column).Max();
    aBlazorSheet = new(aMaxRows, aMaxCols);

    var merggedAreas = (from f in worksheet.Cells
        where f.Column <= aMaxCols && f.Row < aMaxRows && f.MergeArea?.CellsCount > 1
        select f.MergeArea.RangeAddressLocal).Distinct().ToList();

    foreach (var area in merggedAreas)
    { 
        aBlazorSheet.Range(area).Merge();
    }

    foreach (CellRange cell in worksheet.Cells.Where(x => x.Column <= aMaxCols && x.Row <= aMaxRows))
    {
        if (!string.IsNullOrEmpty(cell.DisplayedText))
        {
            aBlazorSheet.Range(cell.Row - 1, cell.Column - 1).Format = new CellFormat()
            {
                ForegroundColor = $"#{cell.Style.Color.Name}",
            };

            if (cell.Formula == null)
                aBlazorSheet.Cells.SetValue(cell.Row - 1, cell.Column - 1, cell.DisplayedText);
            else
                aBlazorSheet.Cells.SetFormula(cell.Row - 1, cell.Column - 1, cell.Formula);
        }
    }

    return cellsWithValues;
}

For the moment, load the values, apply the formulas, and merge the cells.

[anmcgrath]

As of #188 you can set a named range/cell in a sheet:

Sheet.NamedRanges.Set("name", "A1");