DEV Community: Masui Masanori

[ASP.NET Core] Try reading a spreadsheet file by OpenXML 1

Masui Masanori — Mon, 03 Feb 2025 07:51:57 +0000

Intro

This time, I will try reading a spreadsheet file(MS Excel file) by DocumentFormat.OpenXml.
I will use this file to check the result.

Getting cells

To load a spreadsheet using OpenXML, I will get a WorkbookPart from the spreadsheet file first.
And then I will get a WorksheetPart from it, finally I will be able to get Worksheets from the WorksheetPart.

XlsFileReader.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using Drawing = DocumentFormat.OpenXml.Drawing;
using OfficeFileAccessor.Apps;
using System.Text.RegularExpressions;

namespace OfficeFileAccessor.OfficeFiles.Readers;

public class XlsFileReader(ILogger<XlsFileReader> Logger) : IXlsFileReader
{
...
    public void Read(IFormFile file)
    {
        using SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(file.OpenReadStream(), false);
        WorkbookPart? bookPart = spreadsheet.WorkbookPart;
        if(bookPart == null) {
            return;
        }
        List<string> sheetNames = GetSheetNameList(bookPart);
        foreach(var name in sheetNames)
        {
            Worksheet? targetSheet = GetWorksheet(bookPart, name);
            if(targetSheet == null) {
                return;
            }
            WorksheetPart? sheetPart = targetSheet.WorksheetPart;
            foreach(Row row in targetSheet.Descendants<Row>())
            {
                foreach(Cell cell in row.Cast<Cell>())
                {
                    // Get cell values
                    Worksheets.Cell? cellValue = GetCellValue(bookPart, cell);                    
                }
            }
            // Load first sheet for testing.
            break;
        }
    }
...
    private static List<string> GetSheetNameList(WorkbookPart bookPart) =>
        [.. bookPart.Workbook.Descendants<Sheet>().Where(s => string.IsNullOrEmpty(s.Name) == false).Select(s => s.Name?.Value ?? "")];
    private static Worksheet? GetWorksheet(WorkbookPart bookPart, string sheetName)
    {
        foreach(Sheet s in bookPart.Workbook.Descendants<Sheet>())
        {
            if(s.Name == sheetName && string.IsNullOrEmpty(s.Id) == false)
            {
                if(bookPart.TryGetPartById(s.Id!, out var part))
                {
                    if (part is WorksheetPart result)
                    {
                        return result.Worksheet;
                    }
                }
            }
        }
        return null;
    }
...
}

Getting cell values

After getting cell instances, I can get values from them.

Text

Cell.cs

namespace OfficeFileAccessor.OfficeFiles.Worksheets;
public class Cell
{
    public required string Address { get; init; }
    public required CellValueType Type { get; init; }
    public required string Value { get; init; }
    public string? Formula { get; init; }
...
}

XlsFileReader.cs

...
    private static Worksheets.Cell GetCellValue(WorkbookPart bookPart, Cell cell)
    {    
        // Get value
        string value = cell.InnerText;
        // if the data type is SharedString, find the value from Shared String Table
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            SharedStringTablePart? sharedStringTablePart = bookPart.GetPartsOfType<SharedStringTablePart>()
                ?.FirstOrDefault();
            if (sharedStringTablePart != null)
            {
                OpenXmlElement sharedStringItem = sharedStringTablePart.SharedStringTable
                    .ElementAt(int.Parse(value));
                // Concatenate all text except phonetic reading
                string result = string.Concat(
                    sharedStringItem.Descendants<DocumentFormat.OpenXml.Spreadsheet.Text>()
                                    .Where(t => CheckIsPhonetic(t) == false)
                                    .Select(t => t.Text)
                );
                return new Worksheets.Cell
                {
                    Address = cell.CellReference?.Value ?? "A1",
                    Type = Worksheets.CellValueType.Text,
                    Value = result,
                };
            }
        }
        Worksheets.CellValueType valueType = Worksheets.CellValueType.Text;
        return new Worksheets.Cell
        {
            Address = cell.CellReference?.Value ?? "A1",
            Type = valueType,
            Value = value, 
        };
    }
    /// <summary>
    /// Check if the parent element is "PhoneticRun"
    /// </summary>
    /// <param name="textElement"></param>
    /// <returns></returns>
    private static bool CheckIsPhonetic(DocumentFormat.OpenXml.Spreadsheet.Text textElement)
    {
        return textElement.Ancestors<PhoneticRun>().Any();
    }
...

SharedStringTablePart

When I get the cell value from the SharedStringTablePart, if I get it from InnerText, the phonetic will also be combined.

example

Cell value: 建築物に関する事項（集約版）（参考様式）
InnerText: 建築物に関する事項（集約版）（参考様式）ケンチクブツシュウヤクバンサンコウヨウシキ

So I should filter the values.

XlsFileReader.cs

...
    private static Worksheets.Cell GetCellValue(WorkbookPart bookPart, Cell cell)
    {
...
                OpenXmlElement sharedStringItem = sharedStringTablePart.SharedStringTable
                    .ElementAt(int.Parse(value));
                // Concatenate all text except phonetic reading
                string result = string.Concat(
                    sharedStringItem.Descendants<DocumentFormat.OpenXml.Spreadsheet.Text>()
                                    .Where(t => CheckIsPhonetic(t) == false)
                                    .Select(t => t.Text)
                );
...
    }
...
    private static bool CheckIsPhonetic(DocumentFormat.OpenXml.Spreadsheet.Text textElement)
    {
        return textElement.Ancestors<PhoneticRun>().Any();
    }
...

Double

I can get numeric values as same as getting texts.
But some values may not be correct if they have decimal points.

XlsFileReader.cs

...
        Worksheets.CellValueType valueType = Worksheets.CellValueType.Text;
        if (string.IsNullOrEmpty(value) == false &&
            double.TryParse(value, out double nv))
        {
            valueType = Worksheets.CellValueType.Double;
            value = nv.ToString("G");
        }
        return new Worksheets.Cell
        {
            Address = cell.CellReference?.Value ?? "A1",
            Type = valueType,
            Value = value, 
        };
...

Formula

XlsFileReader.cs

...
        // Formula
        string? formula = cell.CellFormula?.Text;
        // Get calculation result
        string? calcResult = cell.CellValue?.InnerText;

        if(string.IsNullOrEmpty(formula) == false && string.IsNullOrEmpty(calcResult) == false)
        {
            if (double.TryParse(calcResult, out double n))
            {
                calcResult = n.ToString("G");
            }
            return new Worksheets.Cell
            {
                Address = cell.CellReference?.Value ?? "A1",
                Type = Worksheets.CellValueType.Formula,
                Value = calcResult,
                Formula = formula,
            };
        }
...

Getting cell styles

Border

Get cell borders from WorkbookStylesPart.

Cell.cs

namespace OfficeFileAccessor.OfficeFiles.Worksheets;
public class Cell
{
    public required string Address { get; init; }
    public required CellValueType Type { get; init; }
    public required string Value { get; init; }
    public string? Formula { get; init; }
    public required double Width { get; init; }
    public required double Height { get; init; }
    public string? BackgroundColor { get; init; }
    public required CellBorders Borders { get; init; }
...
}

CellBorders.cs

namespace OfficeFileAccessor.OfficeFiles.Worksheets;

public record CellBorders
{
    public BorderType Left { get; init; }
    public BorderType Top { get; init; }
    public BorderType Right { get; init; }
    public BorderType Bottom { get; init; }
...
    public static CellBorders GetNoBorders()
    {
        return new ()
        {
            Left = BorderType.None,
            Top = BorderType.None,
            Right = BorderType.None,
            Bottom = BorderType.None,
        };
    }
}

BorderType.cs

namespace OfficeFileAccessor.OfficeFiles.Worksheets;
public enum BorderType
{
    None = 0,
    Thin,
}
public static class BorderTypeFactory
{
    public static BorderType Get(string? borderValue)
    {
        if(string.IsNullOrEmpty(borderValue))
        {
            return BorderType.None;
        }
        return borderValue.ToLower() switch
        {
            "thin" => BorderType.Thin,
            _ => BorderType.None,
        };
    }
}

XlsFileReader.cs

...
    private Worksheets.Cell GetCellValue(WorkbookPart bookPart, Cell cell, double width, double height)
    {
        // Borders
        Worksheets.CellBorders borders = GetBorders(bookPart, cell);
...
        return new Worksheets.Cell
        {
            Address = cell.CellReference?.Value ?? "A1",
            Type = valueType,
            Value = value,
            Borders = borders,
        };
    }
...
    private static Worksheets.CellBorders GetBorders(WorkbookPart bookPart, Cell cell)
    {
        if(cell.StyleIndex?.Value == null)
        {
            return Worksheets.CellBorders.GetNoBorders();
        }
        CellFormat? cellFormat = bookPart.WorkbookStylesPart?.Stylesheet?.CellFormats?.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
        if(cellFormat?.BorderId?.Value != null)
        {
            Border? border = bookPart.WorkbookStylesPart?.Stylesheet?.Borders?.ElementAt(
                    (int)cellFormat.BorderId.Value) as Border;
            if(border != null)
            {
                return new ()
                {
                    Left = Worksheets.BorderTypeFactory.Get(border?.LeftBorder?.Style?.InnerText),
                    Top = Worksheets.BorderTypeFactory.Get(border?.TopBorder?.Style?.InnerText),
                    Right = Worksheets.BorderTypeFactory.Get(border?.RightBorder?.Style?.InnerText),
                    Bottom = Worksheets.BorderTypeFactory.Get(border?.BottomBorder?.Style?.InnerText),
                };
            }            
        }
        return Worksheets.CellBorders.GetNoBorders();
    }
...

Background color

XlsFileReader.cs

...
    private static Worksheets.Cell GetCellValue(WorkbookPart bookPart, Cell cell)
    {
        // Cell color
        string? backgroundColor = GetCellColor(cell, bookPart);
...
        return new Worksheets.Cell
        {
            Address = cell.CellReference?.Value ?? "A1",
            Type = valueType,
            Value = value,
            BackgroundColor = backgroundColor,
            Borders = borders,
        };
    }
...
    private static string? GetCellColor(Cell cell, WorkbookPart bookPart)
    {
        uint? styleIndex = cell.StyleIndex?.Value;
        if(styleIndex == null)
        {
            return null;
        }
        CellFormat? cellFormat = bookPart.WorkbookStylesPart?.Stylesheet?.CellFormats?.ElementAt((int)styleIndex) as CellFormat;
        if (cellFormat?.FillId != null)
        {
            Fill? fill = bookPart.WorkbookStylesPart?.Stylesheet?.Fills?.ElementAt((int)cellFormat.FillId.Value) as Fill;
            PatternFill? patternFill = fill?.PatternFill;
            string? rgbColor = GetRgbColor(patternFill?.ForegroundColor?.Rgb);
            if(string.IsNullOrEmpty(rgbColor) == false)
            {
                return rgbColor;
            }
            string? themeColor = GetThemeColor(bookPart, patternFill?.ForegroundColor?.Theme?.Value);
            if(string.IsNullOrEmpty(themeColor) == false)
            {
                return themeColor;
            }
        }    
        return null;
    }
    private static string? GetRgbColor(HexBinaryValue? rgb)
    {
        if(rgb?.InnerText == null)
        {
            return null;
        }
        // Remove alpha value
        return rgb.InnerText[2..];
    }
    private static string? GetThemeColor(WorkbookPart bookPart, uint? themeColorIndex)
    {
        if(themeColorIndex == null || themeColorIndex <= 0)
        {
            return null;
        }
        ThemePart? themePart = bookPart.ThemePart;
        Drawing.Theme? theme = themePart?.Theme;
        if(theme != null)
        {
            Drawing.Color2Type? color2Type = theme.ThemeElements?.ColorScheme?.ElementAt((int)themeColorIndex) as Drawing.Color2Type;
            return color2Type?.RgbColorModelHex?.Val;
        }
        return null;
    }
...

Width, Height

By default, cells don't have own widths and heights.
So I should get them from columns and rows.

PrintArea

To get column widths, I should get PrintArea first to get last column index.

PageArea.cs

namespace OfficeFileAccessor.OfficeFiles.Worksheets;
public record PageArea(CellAddress Start, CellAddress End);

XlsFileReader.cs

...
    private List<Worksheets.PageArea> GetPrintArea(WorkbookPart bookPart, Worksheet worksheet)
    {
        DefinedNames? definedNames = bookPart.Workbook.DefinedNames;
        if(definedNames == null)
        {
            return [];
        }
        List<Worksheets.PageArea> results = [];
        foreach (DefinedName definedName in definedNames.Elements<DefinedName>())
        {
            if(string.IsNullOrEmpty(definedName.Name?.Value))
            {
                continue;
            }
            if (definedName.Name.Value.StartsWith("_xlnm.Print_Area"))
            {
                string sheetName = "default sheet";
                if(definedName.LocalSheetId != null)
                {
                    Sheet? sheet = bookPart.Workbook.Sheets?.Elements<Sheet>()
                        ?.FirstOrDefault(s => s.SheetId?.Value != null && s.SheetId.Value == definedName.LocalSheetId.Value + 1);
                    if(sheet?.Name != null)
                    {
                        sheetName = sheet.Name!;
                    }
                }
                string printAreaValue = definedName.Text;
                // SheetName is like SheetName!$A$1:$Z$20
                string[] ranges = printAreaValue.Split('!');                
                foreach(var r in ranges)
                {
                    string[] addresses = r.Split(":");
                    if(addresses.Length < 2)
                    {
                        continue;
                    }
                    (string columnNameStart, int rowStart) = GetCellAddress(addresses[0]);
                    if(string.IsNullOrEmpty(columnNameStart) || rowStart <= 0)
                    {
                        continue;
                    }
                    Worksheets.CellAddress startAddress = new (columnNameStart, ConvertAlphabetToIndex(columnNameStart),
                        rowStart);
                    (string columnNameEnd, int rowEnd) = GetCellAddress(addresses[1]);
                    if(string.IsNullOrEmpty(columnNameEnd) || rowEnd <= 0)
                    {
                        continue;
                    }
                    Worksheets.CellAddress endAddress = new (columnNameEnd, ConvertAlphabetToIndex(columnNameEnd),
                        rowEnd);

                    results.Add(new (startAddress, endAddress)); 
                }
            }
        }
        return results;
    }
...

Width, Height

XlsFileReader.cs

...
    public class XlsFileReader(ILogger<XlsFileReader> Logger) : IXlsFileReader
    {
        private readonly double DefaultWidth = Numbers.ConvertFromPixelToCentimeter(8.38 * 7.0);
        private readonly double DefaultHeight = Numbers.ConvertFromPointToCentimeter(18.75);
        private static readonly Regex CellAddressRegex = new (@"\$([a-zA-Z]+)\$([0-9]+)");
        private static readonly Regex ColumnNameRegex = new ("([a-zA-Z]+)");
        public void Read(IFormFile file)
        {
            using SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(file.OpenReadStream(), false);
            WorkbookPart? bookPart = spreadsheet.WorkbookPart;
            List<string> sheetNames = GetSheetNameList(bookPart);
            foreach(var name in sheetNames)
            {
                Worksheet? targetSheet = GetWorksheet(bookPart, name);
                if(targetSheet == null)
                {
                    return;
                }
                List<Worksheets.PageArea> printAreas = GetPrintArea(bookPart, targetSheet);
                Worksheets.PageArea? target = printAreas.FirstOrDefault();
                List<Worksheets.ColumnWidth> widths = [];
                if(target != null)
                {
                    widths = GetColumnWidths(targetSheet, target.Start.Column, target.End.Column);
                }
                foreach(Row row in targetSheet.Descendants<Row>())
                {
                    double height = DefaultHeight;
                    if(row.Height?.Value != null)
                    {
                        // The row.Height value is expressed in points
                        // convert it to centimeters
                        height = Numbers.ConvertFromPointToCentimeter(row.Height.Value);
                    }
                    foreach(Cell cell in row.Cast<Cell>())
                    {
                        string columnName = GetColumnNameFromAddress(cell.CellReference);
                        double? width = widths.FirstOrDefault(w => w.ColumnName == columnName)?.Width;
                        width ??= DefaultWidth;
                        Worksheets.Cell? cellValue = GetCellValue(bookPart, cell, (double)width, height);
                    }
                }
                // Load first sheet for testing.
                break;
            }
        }
        private static Worksheets.Cell GetCellValue(WorkbookPart bookPart, Cell cell, double width, double height)
        {
...
            return new Worksheets.Cell
            {
                Address = cell.CellReference?.Value ?? "A1",
                Type = valueType,
                Value = value,            
                Width = width,
                Height = height,
                BackgroundColor = backgroundColor,
                Borders = borders,
            };
        }
...
        private List<Worksheets.ColumnWidth> GetColumnWidths(Worksheet sheet, int startColumn, int lastColumn)
        {
            Columns? columns = sheet.Descendants<Columns>().FirstOrDefault();
            if (columns == null)
            {
                return [];
            }
            List<Worksheets.ColumnWidth> results = [];
            for (int i = startColumn; i <= lastColumn; i++)
            {
                double columnWidth = DefaultWidth;
                if (columns != null)
                {
                    uint idx = (uint)i;
                    Column? column = columns.Elements<Column>().FirstOrDefault(c => 
                        c?.Min != null && c.Max != null && c.Min <= idx && c.Max >= idx);
                    if (column?.Width != null)
                    {
                        // The column.width value represents the number of characters (7 pixels by default)
                        // convert it to centimeters.
                        columnWidth = Numbers.ConvertFromPixelToCentimeter(column.Width * 7.0);
                    }
                }
                results.Add(new Worksheets.ColumnWidth(i, ConvertIndexToAlphabet(i), columnWidth));
            }
            return results;
        }
...

Numbers.cs

namespace OfficeFileAccessor.Apps;
public static class Numbers
{
    private const double EmuToCm = 1.0 / 360000.0;
    private const double PointToCm = 0.0352778;
    private const double PixelToCm = 0.0264583;

    public static int ParseInt(string? value, int defaultValue)
    {
        if(string.IsNullOrEmpty(value))
        {
            return defaultValue;
        }
        if(int.TryParse(value, out var result))
        {
            return result;
        }
        return defaultValue;
    }
    public static double ConvertFromEMUToCentimeter(int emuValue)
    {
        return EmuToCm * (double)emuValue;
    }
    public static double ConvertFromPointToCentimeter(double pointValue)
    {
        return PointToCm * pointValue;
    }
    public static double ConvertFromPixelToCentimeter(double pixelValue)
    {
        return PixelToCm * pixelValue;
    }
}

[PostgreSQL][Entity Framework Core] Try ASP.NET Core Identity with React

Masui Masanori — Mon, 20 Jan 2025 17:30:44 +0000

Intro

In this time, I will try signing in and signing out with my React application.
I will access PostgreSQL by Entity Framework Core(Npgsql) and create tables by DB migrations First.

My project is as same as the last time I used.

OfficeFileAccessor - GitHub

DB migrations

For create "ApplicationUser" table, I create a DbContext class and "ApplicationUser" entity class as same as below post.

[ASP.NET Core][Entity Framework Core][Npgsql] Try code first & DB first

But then I try creating a migration file, I get an exception.

An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: The entry point exited without ever building an IHost.
Unable to create a 'DbContext' of type 'RuntimeType'. The exception 'Unable to resolve service for type 'Microsoft.EntityFrameworkCore.DbContextOptions`1[OfficeFileAccessor.OfficeFileAccessorContext]' while attempting to activate 'OfficeFileAccessor.OfficeFileAccessorContext'.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

To avoid this probrem, I have to add "builder.Services.AddRazorPages();" into Program.cs.

[Server-Side] Program.cs

using System.Text;
using System.Text.Json.Serialization;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.FileProviders;
using Microsoft.IdentityModel.Tokens;
using NLog;
using NLog.Web;
using OfficeFileAccessor;
using OfficeFileAccessor.AppUsers.Repositories;
using OfficeFileAccessor.OfficeFiles;
...
    var builder = WebApplication.CreateBuilder(args);
...
    builder.Services.AddDbContext<OfficeFileAccessorContext>(options =>
        options.UseNpgsql(builder.Configuration.GetConnectionString("OfficeFileAccessor")));


    // ---- Add this line ----    
    builder.Services.AddRazorPages();

...
    builder.Services.AddControllers()
        .AddJsonOptions(options =>
        {
            // stop reference loop.
            options.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
        });
    builder.Services.AddScoped<IApplicationUsers, ApplicationUsers>();
    var app = builder.Build();
...
    app.Run();
...

MvcServiceCollectionExtensions.AddRazorPages Method - Microsoft Learn

Sign-in from the React application

Server-side

After completing authentication with E-mail address and password, I will set a JWT into Http cookie
to confirm the sign-in status.

[Server-Side] ApplicationUserService.cs

using Microsoft.AspNetCore.Identity;
using OfficeFileAccessor.Apps;
using OfficeFileAccessor.AppUsers.DTO;
using OfficeFileAccessor.AppUsers.Entities;
using OfficeFileAccessor.AppUsers.Repositories;
using OfficeFileAccessor.Web;

namespace OfficeFileAccessor.AppUsers;

public class ApplicationUserService(SignInManager<ApplicationUser> SignIn,
    IApplicationUsers Users,
    IUserTokens Tokens): IApplicationUserService
{
    public async Task<ApplicationResult> SignInAsync(SignInValue value, HttpResponse response)
    {
        var target = await Users.GetByEmailForSignInAsync(value.Email);
        if(target == null)
        {
            return ApplicationResult.GetFailedResult("Invalid e-mail or password");
        }
        SignInResult result = await SignIn.PasswordSignInAsync(target, value.Password, false, false);
        if(result.Succeeded)
        {
            // Add generated JWT into HTTP cookie.
            response.Cookies.Append("User-Token", Tokens.GenerateToken(target), DefaultCookieOption.Get());         
            return ApplicationResult.GetSucceededResult();
        }
        return ApplicationResult.GetFailedResult("Invalid e-mail or password");
    }
    public async Task SignOutAsync(HttpResponse response)
    {
        await SignIn.SignOutAsync();
        // Remove the cookie value.
        response.Cookies.Delete("User-Token");     
    }
}

When the server-side application receives the client accesses, it will get JWT from Http cookies and set as the HTTP Authorization header.

[Server-Side] Program.cs

...
    var builder = WebApplication.CreateBuilder(args);
...
    var app = builder.Build();
...
    app.Use(async (context, next) =>
    {
        // Get JWT from the HTTP cookie.
        if(context.Request.Cookies.TryGetValue("User-Token", out string? token))
        {
            // If a token exists, set as HTTP Authorization header.
            if(string.IsNullOrEmpty(token) == false)
            {            
                context.Request.Headers.Append("Authorization", $"Bearer {token}");
            }
        }        
        await next();
    });
    app.UseStaticFiles();
    app.UseAuthentication();
    app.UseAuthorization();
...
    app.Run();
...

Client-Side

To share the sign-in status to every pages, I create a React.Context and define sign-in/sign-out functions.

[Client-Side] AuthenticationContext.tsx

import { createContext, useContext } from "react";
import { AuthenticationType } from "./authenticationType";

export const AuthenticationContext = createContext<AuthenticationType|null>(null);
export const useAuthentication = (): AuthenticationType|null => useContext(AuthenticationContext);

[Client-Side] AuthenticationProvider.tsx

import { ReactNode, useState } from "react";
import { getServerUrl } from "../web/serverUrlGetter";
import { AuthenticationContext } from "./authenticationContext";
import { getCookieValue } from "../web/cookieValues";
import { hasAnyTexts } from "../texts/hasAnyTexts";

export const AuthenticationProvider = ({children}: { children: ReactNode }) => {
    // to show or hide the sign-out button.
    const [signedIn, setSignedIn] = useState(false);
    const signIn = async (email: string, password: string) => {
        const res = await fetch(`${getServerUrl()}/api/users/signin`, {
            mode: "cors",
            method: "POST",
            headers: {
                "Content-Type": "application/json",
            },
            body: JSON.stringify({ email, password })
        });
        if(res.ok) {
            const result = await res.json();
            setSignedIn(result?.succeeded === true);
            return result;
        }
        return {
            succeeded: false,
            errorMessage: "Something wrong"
        };
    };

    const signOut = async () => {
        const res = await fetch(`${getServerUrl()}/api/users/signout`, {
            mode: "cors",
            method: "GET",
        });
        if(res.ok) {
            setSignedIn(false);
            return true;
        };
        return false;
    };
    // Access a page that requires authentication to check current sign-in status.
    const check = () => 
        fetch(`${getServerUrl()}/api/auth`, {
            mode: "cors",
            method: "GET",
        })
        .then(res => res.ok);
    return <AuthenticationContext.Provider value={{ signedIn, signIn, signOut, check }}>
        {children}
    </AuthenticationContext.Provider>
}

[Client-Side] App.tsx

import './App.css'
import {
  BrowserRouter as Router,
  Route,
  Routes,
  Link
} from "react-router-dom";
import { IndexPage } from './IndexPage';
import { RegisterPage } from './RegisterPage';
import { SigninPage } from './SigninPage';
import { AuthenticationProvider } from './auth/AuthenticationProvider';
import { SignOutButton } from './components/SignoutButton';

function App() {
  return (
    <>
      <AuthenticationProvider>
        <Router basename='/officefiles'>
        <SignOutButton />
...
        <Routes>
          <Route path="/pages/signin" element={<SigninPage />} />
          <Route path="/" element={<IndexPage />} />
...
        </Routes  >
        </Router>
      </AuthenticationProvider>
    </>
  )
}
export default App

[Client-Side] SigninPage.tsx

import { useState } from "react";
import { useNavigate } from "react-router-dom";
import { useAuthentication } from "./auth/authenticationContext";

export function SigninPage(): JSX.Element {
    const [email, setEmail] = useState<string>("");
    const [password, setPassword] = useState<string>("");
    const authContext = useAuthentication();
    const navigate = useNavigate();
    const handleEmailChanged = (event: React.ChangeEvent<HTMLInputElement>) => {
        setEmail(event.target.value);
    }
    const handlePasswordChanged = (event: React.ChangeEvent<HTMLInputElement>) => {
        setPassword(event.target.value);
    }
    const signin = () => {
        if(authContext == null) {
            console.error("No Auth context");
            return;
        }
        // If success, move to the top page. 
        authContext.signIn(email, password)
            .then(res => {
                if(res.succeeded) {
                    navigate("/");
                }                
            })
            .catch(err => console.error(err));
    };
    return <div>
        <h1>Signin</h1>
        <input type="text" placeholder="Email" value={email}
            onChange={handleEmailChanged}></input>
        <input type="password" value={password}
            onChange={handlePasswordChanged}></input>
        <button onClick={signin}>Signin</button>
    </div>
}

Building a React Login Page Template - Clerk

CSRF

Because I set JWT into HTTP cookie to store sign-in status, I will add another type of token to prevent CSRF attacks.
When the client open a page, the server-side application set tokens into the HTTP cookie.
After finishing loading the page, the client-side gets the token and puts it into the HTTP request header to sign-in.

[Server-Side] Program.cs

using System.Text;
using System.Text.Json.Serialization;
using Microsoft.AspNetCore.Antiforgery;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.FileProviders;
using Microsoft.IdentityModel.Tokens;
...
    var builder = WebApplication.CreateBuilder(args);
...
    builder.Services.AddAntiforgery(options =>
    {
        // HTTP request header name
        options.HeaderName = "X-XSRF-TOKEN";
        options.SuppressXFrameOptionsHeader = false;
    });
...
    var app = builder.Build();
...
    var antiforgery = app.Services.GetRequiredService<IAntiforgery>();
    app.Use((context, next) =>
    {
        var requestPath = context.Request.Path.Value;

        if (requestPath != null &&
            (string.Equals(requestPath, "/", StringComparison.OrdinalIgnoreCase) ||
            requestPath.StartsWith("/pages", StringComparison.CurrentCultureIgnoreCase)))
        {
            // Generate a token and put into the cookie.
            AntiforgeryTokenSet tokenSet = antiforgery.GetAndStoreTokens(context);
            if(tokenSet.RequestToken != null) {
                // To use this token on the client-side, set "HttpOnly=false" 
                context.Response.Cookies.Append("XSRF-TOKEN", tokenSet.RequestToken,
                new CookieOptions { 
                    HttpOnly = false,
                    SameSite = SameSiteMode.Lax,
                });
            }
        }
        return next(context);
    });
...
    app.Run();
...

[Server-Side] ApplicationUserController.cs

using Microsoft.AspNetCore.Antiforgery;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using OfficeFileAccessor.AppUsers.DTO;

namespace OfficeFileAccessor.AppUsers;

// Automatically validates AntiforgeryToken for POST, PUT, etc.
[AutoValidateAntiforgeryToken]
[Authorize(AuthenticationSchemes = JwtBearerDefaults.AuthenticationScheme)]
public class ApplicationUserController(IAntiforgery Antiforgery, IApplicationUserService Users): Controller
{
    [AllowAnonymous]
    [HttpPost("/api/users/signin")]
    public async Task<IActionResult> ApplicationSignIn([FromBody] SignInValue value)
    {
        return Json(await Users.SignInAsync(value, Response));
    }
...
    [HttpGet("/api/auth")]
    public IActionResult CheckAuthenticationStatus()
    {
        AntiforgeryTokenSet tokenSet = Antiforgery.GetAndStoreTokens(HttpContext);
        if(tokenSet.RequestToken != null) {
            HttpContext.Response.Cookies.Append("XSRF-TOKEN", tokenSet.RequestToken,
            new CookieOptions { 
                HttpOnly = false,
                SameSite = SameSiteMode.Lax,
            });
        }
        return Ok();
    }
}

[Client-Side] AuthenticationProvider.tsx

import { ReactNode, useState } from "react";
import { getServerUrl } from "../web/serverUrlGetter";
import { AuthenticationContext } from "./authenticationContext";
import { getCookieValue } from "../web/cookieValues";
import { hasAnyTexts } from "../texts/hasAnyTexts";

export const AuthenticationProvider = ({children}: { children: ReactNode }) => {
    const [signedIn, setSignedIn] = useState(false);
    const signIn = async (email: string, password: string) => {
        // Get AntiforgeryToken
        const cookieValue = getCookieValue("XSRF-TOKEN"); 

        if(!hasAnyTexts(cookieValue)) {
            throw Error("Invalid token");
        }
        // Set the token into the HTTP request header.
        const res = await fetch(`${getServerUrl()}/api/users/signin`, {
            mode: "cors",
            method: "POST",
            headers: {
                "Content-Type": "application/json",
                "X-XSRF-TOKEN": cookieValue,
            },
            body: JSON.stringify({ email, password })
        });
        if(res.ok) {
            const result = await res.json();
            setSignedIn(result?.succeeded === true);
            return result;
        }
        return {
            succeeded: false,
            errorMessage: "Something wrong"
        };
    };
...

cookieValues.ts

export function getCookieValue(name: string): string|null { 
    const value = `; ${document.cookie}`;
    const parts = value.split(`; ${name}=`); 
    if (parts.length === 2) {
        const result = parts.pop()?.split(';')?.shift();;
        if(result != null) {
            return result;
        }
    }
    return null; 
};

[ASP.NET Core] Try reading a word processing file by OpenXML 2

Masui Masanori — Sun, 05 Jan 2025 16:51:56 +0000

Intro

I will try getting text styles and fonts in this time.

Getting specified styles and fonts

First, I will try getting the text styles and font what I specify them by myself.

DocFileReader.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

namespace OfficeFileAccessor.OfficeFiles.Readers;

public class DocFileReader : IOfficeFileReader
{
    private readonly NLog.Logger logger;
    public DocFileReader()
    {
        this.logger = NLog.LogManager.GetCurrentClassLogger();
    }
    public void Read(IFormFile file)
    {
        using WordprocessingDocument wordDoc = WordprocessingDocument.Open(file.OpenReadStream(), false);
        Body? body = wordDoc.MainDocumentPart?.Document?.Body;
        if (body == null)
        {
            logger.Warn("Failed reading the document");
            return;
        }
        foreach (OpenXmlElement elm in body.Elements())
        {
            if (elm is Table table)
            {
...
            }
            else if (elm is Paragraph paragraph)
            {
                if (elm.InnerText.Trim().Length <= 0)
                {
                    continue;
                }
                // Get full text from paragraph.InnerText
                logger.Info($"Paragraph Text: {paragraph.InnerText}");
                PrintFontInfoFromParagraph(wordDoc.MainDocumentPart, paragraph);
            }
        }
    }
    private void PrintFontInfoFromParagraph(MainDocumentPart? mainPart, Paragraph paragraph)
    {
        // One paragraph is separated as multiple Run elements by styles and fonts
        foreach (Run run in paragraph.Elements<Run>())
        {
            logger.Info($"Run Text: {run.InnerText}");
            // Get text style and font from RunProperties.
            RunProperties? runProperties = run.RunProperties;
            if (runProperties != null)
            {
                logger.Info($"RunProperties found:");
                var fonts = runProperties.RunFonts;
                if (fonts != null)
                {
                    logger.Info($"Font Name: {GetFontName(fonts, mainPart)}");
                }
                if (runProperties.Color != null)
                {
                    logger.Info($"Color: {runProperties.Color.Val}");
                }
                if (runProperties.Bold != null)
                {
                    logger.Info($"Bold: {runProperties.Bold.Val}");
                }
                if (runProperties.FontSize == null)
                {
                    logger.Info($"FontSize was null");
                }
                else if(int.TryParse(runProperties.FontSize.Val, out var size))
                {
                    // runProperties.FontSize.Val represents half-points
                    logger.Info($"FontSize: {size / 2}");
                }
            }
            logger.Info("------------");
        }
    }
    private string GetFontName(RunFonts? runFonts, MainDocumentPart? mainPart)
    {
        string? result = runFonts?.Ascii ??
            runFonts?.HighAnsi ??
            runFonts?.EastAsia ??
            runFonts?.ComplexScript;
        if (string.IsNullOrEmpty(result))
        {
            result = "No font set";
        }
        return result;
    }
}

Result

Paragraph Text: カスタムfontに設定した場合
Run Text: カス
RunProperties found:
Font Name: No font set
FontSize was null
------------
Run Text: タムfo
RunProperties found:
Font Name: Noto Sans JP Black
FontSize was null
------------
Run Text: ntに
RunProperties found:
Font Name: No font set
FontSize was null
------------
Run Text: 設定
RunProperties found:
Font Name: No font set
Bold:
FontSize was null
------------
Run Text: した
RunProperties found:
Font Name: Meiryo UI
FontSize: 16
------------
Run Text: 場合
RunProperties found:
Font Name: No font set
Color: 60CAF3
FontSize: 22
------------

Getting default styles and fonts

Unless I change the font, color, size, etc. by myself, the above code won't get that informations.

So I have to get them from the base style or ThemeFonts.
I can get informations of "見出し(Headline)".
But some styles like "標準(Normal)" don't have style informations, so I set the default values if I can't get "ParagraphStyleId" from the paragraph.

DocFileReader.cs

...
public class DocFileReader : IOfficeFileReader
{
    private readonly NLog.Logger logger;
    private enum FontType
    {
        Ascii = 0,
        HighAnsi,
        EastAsia,
        Latin,
    }
    private enum FontPriority {
        Major = 0,
        Minor
    }
    private record ThemeFont(string? EastAsiaMajorFont, string? EastAsiaMinorFont, string? LatinMajorFont, string? LatinMinorFont);
    private record TextFont (FontType FontType, string FontName);
    private class TextProps
    {
        public List<TextFont> Fonts { get; set; } = [];
        public int FontSize { get; set; } = 11;
        public bool Bold { get; set; } = false;
        public string Color { get; set; } = "000000";
    }
...
    public void Read(IFormFile file)
    {
        using WordprocessingDocument wordDoc = WordprocessingDocument.Open(file.OpenReadStream(), false);

        Body? body = wordDoc.MainDocumentPart?.Document?.Body;
...
        ThemeFont themeFont = GetThemeFont(wordDoc.MainDocumentPart);
        foreach (OpenXmlElement elm in body.Elements())
        {
            if (elm is Table table)
            {
...
            }
            else if (elm is Paragraph paragraph)
            {
                // Get full text from paragraph.InnerText
                logger.Info($"Paragraph Text: {paragraph.InnerText}");
                PrintFontInfoFromParagraph(wordDoc.MainDocumentPart, paragraph, themeFont);
            }
        }
    }    
    /// <summary>
    /// Get fonts from Theme
    /// </summary>
    /// <param name="mainPart"></param>
    /// <returns></returns>
    private ThemeFont GetThemeFont(MainDocumentPart? mainPart)
    {
        if (mainPart?.ThemePart == null)
        {
            return new(null, null, null, null);
        }
        var theme = mainPart.ThemePart.Theme;
        var themeElements = theme.ThemeElements;
        if (themeElements == null)
        {
            return new(null, null, null, null);
        }
        var majorFontScheme = themeElements.FontScheme?.MajorFont;
        var minorFontScheme = themeElements.FontScheme?.MinorFont;
        if(majorFontScheme == null && minorFontScheme == null)
        {
            return new(null, null, null, null);
        }
        return new ThemeFont(EastAsiaMajorFont: majorFontScheme?.EastAsianFont?.Typeface,
            EastAsiaMinorFont: minorFontScheme?.EastAsianFont?.Typeface,
            LatinMajorFont: majorFontScheme?.LatinFont?.Typeface,
            LatinMinorFont: minorFontScheme?.LatinFont?.Typeface);
    }
    private void PrintFontInfoFromParagraph(MainDocumentPart? mainPart, Paragraph paragraph, ThemeFont themeFont)
    {
        TextProps? props = GetTextProps(mainPart, paragraph, themeFont); 

        // One paragraph is separated as multiple Run elements by styles and font types
        foreach (Run run in paragraph.Elements<Run>())
        {
            logger.Info($"Run Text: {run.InnerText}");
            RunProperties? runProperties = run.RunProperties;
            if (runProperties == null)
            {
                logger.Info("runProperties was null");
            }
            else
            {
                var fonts = GetFonts(runProperties.RunFonts);                
                if(fonts.Count > 0)
                {
                    foreach(var f in fonts)
                    {
                        logger.Info($"Font Name: {f.FontName} Type: {f.FontType}");
                    }
                }
                else if(props?.Fonts != null)
                {
                    foreach(var f in props.Fonts)
                    {
                        logger.Info($"Font Name: {f.FontName} Type: {f.FontType}");
                    }
                }

                if (runProperties.Color == null)
                {
                    if(props?.Color != null)
                    {
                        logger.Info($"Color: {props.Color}");
                    }
                }
                else
                {
                    logger.Info($"Color: {runProperties.Color.Val}");
                }
                if (runProperties.Bold == null)
                {
                    if(props?.Bold != null)
                    {
                        logger.Info($"Bold: {props.Bold}");
                    }
                }
                else
                {
                    logger.Info($"Bold: {runProperties.Bold.Val}");
                }
                if (runProperties.FontSize == null)
                {
                    if(props?.FontSize != null)
                    {
                        logger.Info($"FontSize: {props.FontSize}");
                    }
                }
                else if(int.TryParse(runProperties.FontSize.Val, out var size))
                {
                    // runProperties.FontSize.Val represents half-points
                    logger.Info($"FontSize: {size / 2}");
                }
            }
            logger.Info("------------");
        }
    }
    /// <summary>
    /// Get style and font from paragraph
    /// </summary>
    /// <param name="mainPart"></param>
    /// <param name="paragraph"></param>
    /// <param name="themeFont"></param>
    /// <returns></returns>
    private TextProps? GetTextProps(MainDocumentPart? mainPart, Paragraph paragraph, ThemeFont themeFont)
    {
        string? styleId = paragraph.ParagraphProperties?.ParagraphStyleId?.Val?.Value;
        Style? style = GetStyleById(mainPart, styleId);

        TextProps? result = GetTextPropsFromRunProperties(style?.StyleRunProperties, themeFont);
        if(style == null)
        {
            return GenerateDefaultProps(themeFont);
        } 
        else if(result == null || result.Fonts == null || result.Fonts.Count <= 0)
        {
            StyleRunProperties? inheritedRunProperties = GetInheritedRunProperties(style, mainPart);
            if (inheritedRunProperties == null)
            {
                return GenerateDefaultProps(themeFont);
            }
            else
            {
                logger.Info("Inherited from Base Style:");
                return GetTextPropsFromRunProperties(inheritedRunProperties, themeFont);
            }
        }
        return result;
    }    
    private static StyleRunProperties? GetInheritedRunProperties(Style style, MainDocumentPart? mainPart)
    {
        if (style.BasedOn != null)
        {
            string? baseStyleId = style.BasedOn.Val?.Value;
            Style? baseStyle = mainPart?.StyleDefinitionsPart?.Styles?.Elements<Style>()
                .FirstOrDefault(s => s.StyleId == baseStyleId);
            if (baseStyle != null)
            {
                if (baseStyle.StyleRunProperties != null)
                {
                    return baseStyle.StyleRunProperties;
                }
                else
                {
                    return GetInheritedRunProperties(baseStyle, mainPart);
                }
            }
        }
        return null;
    }
    private static List<TextFont> GetFonts(RunFonts? runFonts)
    {
        List<TextFont> results = [];
        if(string.IsNullOrEmpty(runFonts?.Ascii?.Value) == false)
        {
            results.Add(new TextFont(FontType.Ascii, runFonts.Ascii.Value));
        }
        if(string.IsNullOrEmpty(runFonts?.HighAnsi?.Value) == false)
        {
            results.Add(new TextFont(FontType.HighAnsi, runFonts.HighAnsi.Value));
        }
        if(string.IsNullOrEmpty(runFonts?.EastAsia?.Value) == false)
        {
            results.Add(new TextFont(FontType.EastAsia, runFonts.EastAsia.Value));
        }
        return results;
    }
    private static TextProps GenerateDefaultProps(ThemeFont themeFont)
    {
        // If the style cannot be gotton, return the default font information.
        List<TextFont> fonts = [];
        if(string.IsNullOrEmpty(themeFont.LatinMinorFont) == false)
        {
            fonts.Add(new(FontType.Latin, themeFont.LatinMinorFont));
        }
        if(string.IsNullOrEmpty(themeFont.EastAsiaMinorFont) == false)
        {
            fonts.Add(new(FontType.EastAsia, themeFont.EastAsiaMinorFont));
        }
        return new ()
        {
            Fonts = fonts,
        };
    }    
    private static Style? GetStyleById(MainDocumentPart? mainPart, string? styleId)
    {
        if(string.IsNullOrEmpty(styleId))
        {
            return null;
        }
        IEnumerable<Style>? styles = mainPart?.StyleDefinitionsPart?.Styles?.Elements<Style>();
        if (styles != null)
        {
            return styles.FirstOrDefault(s => s.StyleId == styleId);
        }
        return null;
    }
    private TextProps? GetTextPropsFromRunProperties(StyleRunProperties? runProperties, ThemeFont themeFont)
    {
        if (runProperties == null)
        {
            return null;
        }
        TextProps? result = new();
        var runFonts = runProperties.RunFonts;
        if (runFonts != null)
        {
            result.Fonts = GetTextFonts(runFonts);
            if(result.Fonts.Count <= 0)
            {
                result.Fonts = GetTextFonts(themeFont, runFonts);
            }
        }
        if (runProperties.Color?.Val != null)
        {
            result.Color = runProperties.Color.Val!;
        }
        if (runProperties.Bold != null)
        {
            result.Bold = true;
        }
        // runProperties.FontSize.Val represents half-points
        if (string.IsNullOrEmpty(runProperties.FontSize?.Val) == false &&
            int.TryParse(runProperties.FontSize?.Val, out var size))
        {
            result.FontSize = size / 2;
        }
        return result;
    }
    /// <summary>
    /// Get font name from RunFonts
    /// </summary>
    /// <param name="runFonts"></param>
    /// <returns></returns>
    private static List<TextFont> GetTextFonts(RunFonts runFonts)
    {
        List<TextFont> results = [];
        if (runFonts.Ascii?.Value != null && runFonts.Ascii.HasValue)
        {
            results.Add(new TextFont(FontType.Ascii, runFonts.Ascii.Value));
        }
        if (runFonts.HighAnsi?.Value != null && runFonts.HighAnsi.HasValue)
        {
            results.Add(new TextFont(FontType.HighAnsi, runFonts.HighAnsi.Value));
        }
        if (runFonts.EastAsia?.Value != null && runFonts.EastAsia.HasValue)
        {
            results.Add(new TextFont(FontType.EastAsia, runFonts.EastAsia.Value));
        }
        return results;
    }
    /// <summary>
    /// Get font name from ThemeFonts
    /// </summary>
    /// <param name="themeFont"></param>
    /// <param name="runFonts"></param>
    /// <returns></returns>
    private static List<TextFont> GetTextFonts(ThemeFont themeFont, RunFonts runFonts)
    {
        List<TextFont> results = [];
        // ThemeFont is divided into MajorFont and MinorFont.
        if(runFonts.EastAsiaTheme?.Value == ThemeFontValues.MajorEastAsia)
        {
            if(string.IsNullOrEmpty(themeFont.LatinMajorFont) == false)
            {
                results.Add(new(FontType.Latin, themeFont.LatinMajorFont));
            }
            if(string.IsNullOrEmpty(themeFont.EastAsiaMajorFont) == false)
            {
                results.Add(new(FontType.EastAsia, themeFont.EastAsiaMajorFont));
            }
        }
        else
        {
            if(string.IsNullOrEmpty(themeFont.LatinMinorFont) == false)
            {
                results.Add(new(FontType.Latin, themeFont.LatinMinorFont));
            }
            if(string.IsNullOrEmpty(themeFont.EastAsiaMinorFont) == false)
            {
                results.Add(new(FontType.EastAsia, themeFont.EastAsiaMinorFont));
            }
        }
        return results;
    }
}

Result

Found a Paragraph with text: This is みだし１
Paragraph Text: This is みだし１
Run Text: This is みだし１
Font Name: 游ゴシック Light Type: Latin
Color: 000000
Bold: False
FontSize: 16
------------
Found a Paragraph with text: あいう
Paragraph Text: あいう
Run Text: あいう
Font Name: 游明朝 Type: Latin
Color: 000000
Bold: False
FontSize: 11
------------
Found a Paragraph with text: 見出し２
Paragraph Text: 見出し２
Run Text: 見出し２
Font Name: 游ゴシック Light Type: Latin
Color: 000000
Bold: False
FontSize: 14
------------
Found a Paragraph with text: えおか
Paragraph Text: えおか
Run Text: えおか
Font Name: 游明朝 Type: Latin
Color: 000000
Bold: False
FontSize: 11
------------
Found a Paragraph with text: きくけ
Paragraph Text: きくけ
Run Text: きくけ
Font Name: 游明朝 Type: Latin
Color: 000000
Bold: False
FontSize: 11
------------
Found a Paragraph with text: こさし
Paragraph Text: こさし
Run Text: こさし
Font Name: 游明朝 Type: Latin
Color: 000000
Bold: False
FontSize: 11
------------
...

[ASP.NET Core] Try reading a word processing file by OpenXML 1

Masui Masanori — Wed, 18 Dec 2024 18:04:21 +0000

Intro

This time, I will try reading a word processing file(MS Word file) by OpenXML.
I will use MS Word template to check the result.

OfficeFileAccessor - GitHub

Environments

.NET ver.9.0.100
DocumentFormat.OpenXml ver.3.1.1
NLog.Web.AspNetCore ver.5.3.14
Microsoft.AspNetCore.SpaServices.Extensions ver.9.0.0

Reading

I can get stream from IFormFile what is sent from the client-side and read as "WordprocessingDocument".

DocFileReader.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;

namespace OfficeFileAccessor.OfficeFiles.Readers;

public class DocFileReader: IOfficeFileReader
{
   private readonly NLog.Logger logger;
   public DocFileReader()
   {
      this.logger = NLog.LogManager.GetCurrentClassLogger();
   }
   public void Read(IFormFile file)
   {
      // Open file stream and read
      using WordprocessingDocument wordDoc = WordprocessingDocument.Open(file.OpenReadStream(), false);
      Body? body = wordDoc.MainDocumentPart?.Document?.Body;
      if(body == null)
      {
         logger.Warn("Failed reading the document");
         return;
      }
      // Get all elements as XML from body
      foreach(OpenXmlElement elm in body.Elements())
      {
         logger.Info($"Type: {elm.GetType()} XML: {elm.InnerXml}");
      }
   }
}

Result

Type: DocumentFormat.OpenXml.Wordprocessing.Paragraph XML: <w:pPr xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:pStyle w:val="a6" /><w:jc w:val="left" /></w:pPr><w:r xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"><w:rPr><w:noProof /></w:rPr><mc:AlternateContent xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><mc:Choice Requires="wps"><w:drawing><wp:anchor distT="0" distB="0" distL="114300" distR="114300" simplePos="0" relativeHeight="251659264" behindDoc="0" locked="0" layoutInCell="1" allowOverlap="1" wp14:editId="2621265D" wp14:anchorId="3EB8B806" xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing"><wp:simplePos x="0" y="0" /><wp:positionH relativeFrom="column"><wp:posOffset>485140</wp:posOffset></wp:positionH><wp:positionV relativeFrom="paragraph"><wp:posOffset>329565</wp:posOffset></wp:positionV><wp:extent cx="1495425" cy="604837" /><wp:effectExtent l="0" t="0" r="28575" b="24130" /><wp:wrapNone />
...

I also can get inner text and style information for each element type.

Table

DocFileReader.cs

...
   public void Read(IFormFile file)
   {
...
      foreach(OpenXmlElement elm in body.Elements())
      {
         if(elm is Table table)
         {
            logger.Info("Table found:");
            ReadTableProps(table);
         }
      }
   }
   private void ReadTableProps(Table table)
   {
      // Get Table properties
      TableProperties? tableProperties = table.GetFirstChild<TableProperties>();
      if(tableProperties != null)
      {
         // Table width
         TableWidth? tableWidth = tableProperties.GetFirstChild<TableWidth>();
         logger.Info($"Table Width: {tableWidth?.Width}");
         // Table borders
         TableBorders? borders = tableProperties.GetFirstChild<TableBorders>();
         if(borders != null)
         {
               logger.Info($"Table Border Left Val: {borders.LeftBorder?.Val} Color: {borders.LeftBorder?.Color} Size: {borders.LeftBorder?.Size}");
               logger.Info($"Table Border Top Val: {borders.TopBorder?.Val} Color: {borders.TopBorder?.Color} Size: {borders.TopBorder?.Size}");
         }
      }
      // Get rows
      var rows = table.Elements<TableRow>();
      foreach (var row in rows)
      {
         // Get row properties
         TableRowProperties? rowProperties = row.GetFirstChild<TableRowProperties>();
         if(rowProperties != null)
         {
               TableRowHeight? rowHeight = rowProperties.GetFirstChild<TableRowHeight>();
               logger.Info($"Row Height: {rowHeight?.Val}");

         }
         // Get cells
         var cells = row.Elements<TableCell>();
         foreach (var cell in cells)
         {
               // Get cell texts
               string cellText = cell.InnerText;
               logger.Info($"CELL Text: {cellText}");
               // Get cell properties
               TableCellProperties? cellProperties = cell.GetFirstChild<TableCellProperties>();
               if(cellProperties != null)
               {
                  TableCellWidth? cellWidth = cellProperties.GetFirstChild<TableCellWidth>();
                  logger.Info($"Cell Width: {cellWidth?.Width}");
                  TableCellBorders? borders = cellProperties.GetFirstChild<TableCellBorders>();
                  if(borders != null)
                  {
                     logger.Info($"Cell Border Right Val: {borders.RightBorder?.Val} Color: {borders.RightBorder?.Color} Size: {borders.RightBorder?.Size}");
                     logger.Info($"Cell Border Bottom Val: {borders.BottomBorder?.Val} Color: {borders.BottomBorder?.Color} Size: {borders.BottomBorder?.Size}");
                  }
                  // Get colors
                  Shading? shading = cellProperties.GetFirstChild<Shading>();
                  if(shading != null)
                  {
                     logger.Info($"Cell BackgroundColor: {shading.Fill?.Value} Color:{shading.Color}");
                  }
               }
         }
         logger.Info("-----------");
      }
      logger.Info("\n");
   }
}

Word processing - Microsoft Learn

[Vite + React] Running an ASP.NET Core application behind a reverse proxy

Masui Masanori — Thu, 05 Dec 2024 16:00:39 +0000

Intro

This time, I will try running my ASP.NET Core application behind reverse proxy.
On the client-side, I will use a React application generated by Vite.

The URL will be changed when I use a reverse proxy.

How to access	URL
Direct access	http//localhost:5170
Using a reverse proxy	http://localhost/officefiles

Thus, I need to change the settings to match this URL.

vite.config.ts

To change the asset paths of the React application, I can change "base" in vite.config.ts to "/officefiles/".

vite.config.ts

import { defineConfig } from "vite";
import react from "@vitejs/plugin-react";

// https://vite.dev/config/
export default defineConfig({
  base: "/officefiles/",
  plugins: [react()],

})

Building for Production - Vite

Problems

When I access the React application, the URL automatically changes from "http://localhost:5173" to "http://localhost:5173/officefiles/".
However, after the change, four problems occurred.

ERR_TOO_MANY_REDIRECTS
Couldn't access ASP.NET Core Web API
Route paths of react-router-dom
Accessing ASP.NET Core Web API from the React application

Problem 1. ERR_TOO_MANY_REDIRECTS

When I tried to access "http://localhost/officefiles" on debug mode, I got an ERR_TOO_MANY_REDIRECTS error and the page couldn't be opened.
Because this problem only occurred when the application has been run with the "dotnet run" command, I decided not to use a reverse proxy for debugging.

Problem 2. Couldn't access ASP.NET Core Web API

Although I tried to access an ASP.NET Core Web API(ex. http://localhost:5170/api/files), it would be routed to the Vite-side and I would get a 404 error.
So I changed "UseSpa" would be executed only if the accessed URL does not contain "/api".

Program.cs

...
   app.MapControllers();
   app.MapWhen(context => context.Request.Path.StartsWithSegments("/api") == false,
       b => {
           b.UseSpa(spa =>
           {
               if (builder.Environment.EnvironmentName == "Development") 
               {
                   spa.Options.SourcePath = "office-file-accessor";
                   spa.UseProxyToSpaDevelopmentServer("http://localhost:5173");
               }
               else
               {
                    spa.Options.SourcePath = "office-file-accessor/dist";
               }
           });
       });
   app.Run();
...

Vite on dotnet spa setting - StackOverflow

Problem 3. Route paths of react-router-dom

Route paths of react-router-dom wouldn't be changed, so I added "basename" into the "Router" tag.

App.tsx

import './App.css'
import {
  BrowserRouter as Router,
  Route,
  Routes,
  Link
} from "react-router-dom";
import { IndexPage } from './IndexPage';
import { RegisterPage } from './RegisterPage';
function App() {
  return (
    <>

      <Router basename="/officefiles">

      <Link to="/">TOP</Link>
      <p>|</p>
      <Link to="/register">Register</Link>
      <Routes>
        <Route path="/" element={<IndexPage />} />
        <Route path="/register" element={<RegisterPage />} />
      </Routes  >
      </Router>
    </>
  )
}
export default App

Router - React Router API Reference

Problem 4. Accessing ASP.NET Core Web API from the React application

The ASP.NET Core Web API URLs what were accessed by the React application wouldn't be changed.
Because I couldn't find how to change the URLs automatically, so I decided to switch the URL depending on whether I was in the product environment or not.

serverUrlGetter.ts

export function getServerUrl() {
    if(import.meta.env.MODE === "production") {
        return "/officefiles";
    }
    return "";
}

IndexPage.tsx

import { useEffect } from "react"
import { getServerUrl } from "./web/serverUrlGetter";

export function IndexPage(): JSX.Element {
    useEffect(() => {
        fetch(`${getServerUrl()}/api/files`, {
            mode: "cors",
            method: "GET"
        })
        .then(res => res.text())
        .then(res => console.log(res))
        .catch(err => console.error(err));
    }, []);


    return <h1>Hello World!</h1>
}

Using React for the client-side of an ASP.NET Core application

Masui Masanori — Sat, 23 Nov 2024 17:22:22 +0000

Intro

This time, I will try using React for the client-side of my ASP.NET Core application.
I want to build the client-side code and the server-side code separately, because they will rarely be changed at the same time.

OfficeFileAccessor - GitHub

Environments

.NET ver.9.0.100
Microsoft.AspNetCore.SpaServices.Extensions ver.9.0.0
Node.js ver.22.11.0
React ver.18.3.1

Project file structures

OfficeFileAccessor - Root folder of ASP.NET Core application
L office-file-accessor - Root folder of React application
L React application files
L ASP.NET Core application files

UseSpa

I can also save the pre-built React code as static files in wwwroot directory and use it server-side.
However, in the debug environment, I want to access the page displayed at localhost:5173 as a result of running npm run dev, and in the release environment, I want to load the files from the dist folder.

To do this, I can use "UseSpa".
(I have to add Microsoft.AspNetCore.SpaServices.Extensions first).

Program.cs

using System.Text.Json.Serialization;
using Microsoft.Extensions.FileProviders;
using NLog;
using NLog.Web;
...
    builder.Services.AddSpaStaticFiles(configuration =>
    {
        if (builder.Environment.EnvironmentName == "Development") 
        {
            configuration.RootPath = "office-file-accessor";
        }
        else
        {
            configuration.RootPath = "office-file-accessor/dist";
        }
    });
    builder.Services.AddControllers()
        .AddJsonOptions(options =>
        {
            options.JsonSerializerOptions.ReferenceHandler = ReferenceHandler.IgnoreCycles;
        });
    var app = builder.Build();    
    app.UseRouting();
    app.MapStaticAssets();

    // To allow the application to load React files other than the index.html, such as JavaScript and CSS.
    if (builder.Environment.EnvironmentName != "Development")
    {
        app.UseStaticFiles(new StaticFileOptions {
            FileProvider = new PhysicalFileProvider(Path.Combine(Directory.GetCurrentDirectory(), "office-file-accessor/dist")),
            RequestPath = ""
        });
    }    
    app.MapControllers();
    app.UseSpa(spa =>
    {
        if (builder.Environment.EnvironmentName == "Development") 
        {
            spa.Options.SourcePath = "office-file-accessor";
            spa.UseProxyToSpaDevelopmentServer("http://localhost:5173");
        }
        else
        {
            spa.Options.SourcePath = "office-file-accessor/dist";
        }
    });
    app.Run();
...

[ASP.NET Core][EntityFramework Core] Update from .NET 6 to .NET 8

Masui Masanori — Sat, 16 Nov 2024 18:11:55 +0000

Intro

This time, I will try ASP.NET Core Identity sign-in again.
I will upgrade my ASP.NET Core project.

As of 2024-11-17, the stable version of Npgsql.EntityFrameworkCore.PostgreSQL for .NET 9 has not yet been released, so I will upgrade the project to .NET 8.

Environments

.NET ver.8.0.404
Microsoft.EntityFrameworkCore ver.8.0.11
Npgsql.EntityFrameworkCore.PostgreSQL ver.8.0.10
NLog.Web.AspNetCore ver.5.3.14
Microsoft.AspNetCore.Identity.EntityFrameworkCore ver.8.0.11
Microsoft.AspNetCore.Authentication.JwtBearer ver.8.0.11
Microsoft.EntityFrameworkCore.Design ver.8.0.11

Getting environment variables

I can use appsettings.json as an external config file.
But because I don't want to write secrets threre(ex. database connection strings), so I set them as Windows environment variables.

For example, if I set an environment variable as shown below, the application will replace the value of "ConnectionStrings:BookShelf" with the value of the environment variable at runtime.

Name	Value
ConnectionStrings__BookShelf	Host=localhost;Port=5432;Database=book_shelf;Username={User Name};Password={Password};

appsettings.json

...
  "ConnectionStrings": {
    "BookShelf": "sample_strings"
  },
...

Configuration in ASP.NET Core - Microsoft Learn

Upgrading the project

Most program code will work without modification.
However, some program code generates deprecation warnings or just can be simplified, so I will update them.

NLog(Logging)

"NLogBuilder.ConfigureNLog" is marked obsolete and "builder.Host.ConfigureLogging" isn't recommended.

update logging configurations

[From] Program.cs

...
var logger = NLogBuilder.ConfigureNLog("Nlog.config").GetCurrentClassLogger();
try
{
    var builder = WebApplication.CreateBuilder(args);
    builder.Host.ConfigureLogging(logging =>
    {
        logging.ClearProviders();
        logging.SetMinimumLevel(LogLevel.Trace);
    })
    .UseNLog();
...

[To] Program.cs

...
var logger = LogManager.Setup().LoadConfigurationFromFile("Nlog.config").GetCurrentClassLogger();
try
{
    var builder = WebApplication.CreateBuilder(args);
    builder.Logging.ClearProviders();
    builder.Logging.SetMinimumLevel(Microsoft.Extensions.Logging.LogLevel.Trace);
    builder.Host.UseNLog();
...

UseEndpoints

I can remove "app.UseEndpoints".

remove app.UseEndpoints

[From] Program.cs

...
    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
...

[To] Program.cs

...
    app.MapControllers();
...

ASP0014: Suggest using top level route registrations - Microsoft Learn

HttpContext.Request.Headers.Add

To avoid duplicate the key, I change the method from "Add" to "Append".

change from Request.Headers.Add to Request.Headers.Append

[From] Program.cs

...
    app.Use(async (context, next) =>
    {
        var token = context.Session.GetString("user-token");
        if(string.IsNullOrEmpty(token) == false)
        {            
            context.Request.Headers.Add("Authorization", $"Bearer {token}");
        }
        await next();
    });
...

[To] Program.cs

...
    app.Use(async (context, next) =>
    {
        var token = context.Session.GetString("user-token");
        if(string.IsNullOrEmpty(token) == false)
        {            
            context.Request.Headers.Append("Authorization", $"Bearer {token}");
        }
        await next();
    });
...

ASP0019: Suggest using IHeaderDictionary.Append or the indexer - Microsoft Learn

Primary constructor

Because Most constructors simply assign instances to member variables, so they can be simplified with a primary constructor.

use primary constructor

[From] UserController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using UpgradeProjectSample.Apps;
using UpgradeProjectSample.Users;
using UpgradeProjectSample.Users.Dto;

namespace UpgradeProjectSample.Controllers;
public class UserController: Controller
{
    private readonly IApplicationUserService userService;
    public UserController(IApplicationUserService userService)
    {
        this.userService = userService;
    }
    [AllowAnonymous]
    [HttpPost]
    [Route("/user/signin")]
    public async Task<UserActionResult> Signin([FromBody] SigninValue value)
    {
        if (value == null)
        {
            return ActionResultFactory.GetFailed("Failed getting sign in values");
        }
        return await userService.SigninAsync(value, HttpContext.Session);
    }
...
}

[To] UserController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using UpgradeProjectSample.Apps;
using UpgradeProjectSample.Users;
using UpgradeProjectSample.Users.Dto;

namespace UpgradeProjectSample.Controllers;
public class UserController(IApplicationUserService userService) : Controller
{
    [AllowAnonymous]
    [HttpPost]
    [Route("/user/signin")]
    public async Task<UserActionResult> Signin([FromBody] SigninValue value)
    {
        if (value == null)
        {
            return ActionResultFactory.GetFailed("Failed getting sign in values");
        }
        return await userService.SigninAsync(value, HttpContext.Session);
    }
...
}

Try Vite + React + Tailwind CSS

Masui Masanori — Mon, 14 Oct 2024 17:10:45 +0000

Intro

This time, I will try creating a React application by Vite.
And I will also try using Tailwind CSS.

react-sample - GitHub

Creating a React project

First, I will create a React project using Vite by following the "Getting Started".

Getting Started - Vite

npm create vite@latest my-react-app -- --template react-ts

After creating the project, I will install Tailwind CSS, PostCSS, autoprefixer.

To use TailWind CSS, I should execute "npx tailwindcss init" and add "postcss.config.js".

postcss.config.js

export default {
  plugins: {
    tailwindcss: {},
    autoprefixer: {},
  },
}

And I change the "tailwind.config.js" like below.

tailwind.config.js

/** @type {import('tailwindcss').Config} */
export default {
  content: [
    "./src/**/*.{js,ts,jsx,tsx}",
  ],
  theme: {
    extend: {},
  },
  plugins: [],
}

After that, I also need to add these three rows into "index.css".

index.css

@tailwind base;
@tailwind components;
@tailwind utilities;

...

To avoid "Unknown at rule @tailwind" on VSCode, I should install "Tailwind CSS IntelliSense" and add "Files: Associations" into VSCode settings.

warning

settings.json

After installing, I can write like below.

App.tsx

import "./App.css";
import {
  BrowserRouter as Router,
  Route,
  Routes,
  Link
} from "react-router-dom";
import { IndexPage } from "./IndexPage";

function App() {
  return (
    <>
      <div className="w-full h-screen flex flex-col items-center justify-center">
        <div className="w-[86%] h-[90%] bg-[green]">
          <Router>
            <Link to="/">TOP</Link>
            <Routes>
              <Route path="/" element={<IndexPage />} />
            </Routes>
          </Router>
        </div>
      </div>     
    </>
  )
}

export default App

Environmental variables

I can use environmental variables by ".env", ".env.development(for development)", and ".env.production(for production)".
In Vite projects, variable names have to be prefixed with "VITE_".

.env

VITE_SERVER_APP_URL="http://localhost:3000"

I can get the value like below.

Index.page.tsx

import "./IndexPage.css";
import { useEffect, useState } from "react";

export function IndexPage(): JSX.Element {
  useEffect(() => {
    fetch(`${import.meta.env.VITE_SERVER_APP_URL}/pointclouds`, {
      mode: "cors",
      method: "GET"
    }).then(res => res.json())
    .then(res => console.log(res))
    .catch(err => console.error(err));
  }, []);
...
}
export default IndexPage;

Env Variables and Modes - Vite

Using Potree from code written in TypeScript 2

Masui Masanori — Sun, 29 Sep 2024 18:16:26 +0000

Intro

This time, I will try rotating objects and cameras first.
I will also try replacing the previously loaded point cloud data with newly loaded point cloud data.

Using Potree from code written in TypeScript 1

ts/types/potree/index.d.ts

namespace Potree {
    const PointSizeType = {
        FIXED: 0,
        ATTENUATED: 1,
        ADAPTIVE: 2
    };
    const PointShape = {
        SQUARE: 0,
        CIRCLE: 1,
        PARABOLOID: 2
    };

    class Viewer {
        scene: Scene;
        renderer: THREE.WebGLRenderer;
        orbitControls: OrbitControls;
        constructor(element: HTMLElement);

        setPointBudget(budget: number): void;
        loadPointCloud(path: string, name: string, callback?: (e: PointCloudEventVariable) => void): void;
        setEDLEnabled(enabled: boolean): void;
        setFOV(fov: number): void;
        setBackground(color: string): void;
        fitToScreen(): void;
        loadSettingsFromURL(): void;
        zoomTo(node, factor, animationDuration = 0): void;
    }

    class PointCloudEventVariable {
        pointcloud: PointCloudOctree;
    }
    class PointCloudOctree {
        name: string;
        position: THREE.Vector3;
        scale: THREE.Vector3;
        rotation: THREE.Vector3;
        material: PointCloudMaterial;
        updateMatrixWorld(force: boolean = false): void;
    }
    class Scene {
        constructor(renderer: any);
        addPointCloud(pointcloud: PointCloudOctree): void;
        pointclouds: PointCloudOctree[];
        scene: THREE.Scene;
        scenePointCloud: THREE.Scene;
    }
    class PointCloudMaterial {
        size: number;
        pointSizeType: PointSizeType;
        shape: PointShape;
        materials: THREE.WebGLMaterials;
    }
    class OrbitControls {
        yawDelta: number;
        pitchDelta: number;
    }
    function loadPointCloud(
        path: string,
        name: string,
        callback: (e: PointCloudEventVariable) => void
    ): void;
}

Rotation

Rotating point clouds

In this time, I will use "requestAnimationFrame" to rotate loaded point cloud data and camera.

Window: requestAnimationFrame() method - MDN

After changing the angle of the point cloud data, I need to call "updateMatrixWorld".

potreeAccessor.ts

export class PotreeAccessor {
    private viewer: Potree.Viewer;

    public constructor() {
        this.viewer = new Potree.Viewer(document.getElementById("potree_render_area") as HTMLElement);
        this.viewer.setEDLEnabled(false);
        this.viewer.setFOV(60);
        this.viewer.setPointBudget(1_000_000);
        this.viewer.loadSettingsFromURL();
        this.viewer.setBackground("");
        this.loadPointCloud("http://localhost:3000/lion_takanawa_normals/cloud.js", true);
        // Change the point cloud data angles per frame.
        requestAnimationFrame(() => this.rotatePointCloud());        
    }
    private loadPointCloud(cloudURL: string, fitToScreen: boolean) {
        Potree.loadPointCloud(cloudURL, "cloud", e => {
            const pointcloud = e.pointcloud;
            const material = pointcloud.material;
            material.size = 1;
            material.pointSizeType = Potree.PointSizeType.ADAPTIVE;
            material.shape = Potree.PointShape.SQUARE;

            this.viewer.scene.addPointCloud(pointcloud);
            this.viewer.fitToScreen();
        });
    }
    private rotatePointCloud() {
        requestAnimationFrame(() => this.rotatePointCloud());

        if(this.lastPointCloud == null) {
            return;
        }
        // Rotate 
        this.lastPointCloud.rotation.x += 0.01;
        this.lastPointCloud.updateMatrixWorld();
    }
}

Rotating camera

I also can rotate camera.
When I drag the parent element for rendering the point cloud with left mouse button, the camera will be rotated.

This function is implemented in Potree's navigation/OrbitControls.js.
Similarly, I can rotate the camera by changing the yawDelta and pitchDelta values of navigation/OrbitControls.js.

potreeAccessor.ts

...
    private rotatePointCloud() {
        requestAnimationFrame(() => this.rotatePointCloud());
        this.viewer.orbitControls.yawDelta += 0.01;
    }
...

Replacing point cloud data

To replace previously loaded point cloud data with newly loaded point cloud data, delete the previous one first, and then load the new one.

export class PotreeAccessor {
    private viewer: Potree.Viewer;
    private currentIndex = -1;
    private lastPointCloud: Potree.PointCloudOctree|null = null;
    private urls = [
        "http://localhost:3000/PotreeData/metadata.json",
        "http://localhost:3000/vol_total/cloud.js",
        "http://localhost:3000/lion_takanawa_laz/cloud.js",
        "http://localhost:3000/lion_takanawa_las/cloud.js",
        "http://localhost:3000/lion_takanawa/cloud.js"
    ];
    public constructor() {
        this.viewer = new Potree.Viewer(document.getElementById("potree_render_area") as HTMLElement);
        this.viewer.setEDLEnabled(false);
        this.viewer.setFOV(60);
        this.viewer.setPointBudget(1_000_000);
        this.viewer.loadSettingsFromURL();
        this.viewer.setBackground("");
        this.loadPointCloud("http://localhost:3000/lion_takanawa_normals/cloud.js", true);
        requestAnimationFrame(() => this.rotatePointCloud());

        setInterval(() => {
            this.currentIndex += 1;
            if(this.currentIndex >= this.urls.length) {
                this.currentIndex = 0;
            }
            this.clearLastPointCloud();
            this.loadPointCloud(this.urls[this.currentIndex]!, false);
        }, 3000);

    }
    private rotatePointCloud() {
...
    }
    private clearLastPointCloud() {
        // removing last loaded point cloud data.
        if(this.lastPointCloud == null) {
            return;
        }
        this.viewer.scene.scenePointCloud.remove(this.lastPointCloud);

        if(this.lastPointCloud.material?.materials != null) {
            this.lastPointCloud.material.materials.forEach((m: { dispose: () => void; }) => {
                m.dispose();
            });
        }
        this.viewer.scene.pointclouds = this.viewer.scene.pointclouds.filter(p => p.name !== "cloud");

    }
    private loadPointCloud(cloudURL: string, fitToScreen: boolean) {
        Potree.loadPointCloud(cloudURL, "cloud", e => {
            const pointcloud = e.pointcloud;
            const material = pointcloud.material;
            material.size = 1;
            material.pointSizeType = Potree.PointSizeType.ADAPTIVE;
            material.shape = Potree.PointShape.SQUARE;

            this.viewer.scene.addPointCloud(pointcloud);
            this.lastPointCloud = pointcloud;

            if (fitToScreen) {
                this.viewer.fitToScreen();
            } else {
                this.viewer.zoomTo(pointcloud, 1);
            }
        });
    }
}

Using Potree from code written in TypeScript 1

Masui Masanori — Mon, 16 Sep 2024 17:50:36 +0000

Intro

In this time, I will try loading point cloud data by Potree.

potree/potree: WebGL point cloud viewer for large datasets - GitHub

Because I want to use TypeScript in my project, I will start by using Potree from code written in TypeScript.
I will use Express for the server-side code.

Sample project

potree-sample - GitHub

Building Potree

Because Potree isn't officially published on npm, so I have to build it myself.
To do this, I clone the repository and execute "npm install" and "npm run build".

After the build is complete, I will copy three folders from the directory into my project.

potree          <-the root directory of the Potree repository
    Ｌbuild     <-build files
    Ｌlibs      <-dependencies
    Ｌresources <-sample point cloud data

Adding d.ts

To use Potree from TypeScript code, I should create a type definition file.

For now, I'm not defining the complete types, but only defining the variables and functions that I will use in this time.

ts/types/potree/index.d.ts

namespace Potree {
    const PointSizeType = {
        FIXED: 0,
        ATTENUATED: 1,
        ADAPTIVE: 2
    };
    const PointShape = {
        SQUARE: 0,
        CIRCLE: 1,
        PARABOLOID: 2
    };

    class Viewer {
        scene: Scene;
        constructor(element: HTMLElement);

        setPointBudget(budget: number): void;
        loadPointCloud(path: string, name: string, callback?: (e: PointCloudEventVariable) => void): void;
        setEDLEnabled(enabled: boolean): void;
        setFOV(fov: number): void;
        setBackground(color: string): void;
        fitToScreen(): void;
        loadSettingsFromURL(): void;
    }

    class PointCloudEventVariable {
        pointcloud: PointCloud;
    }
    class PointCloud {
        position: THREE.Vector3;
        scale: THREE.Vector3;
        material: PointCloudMaterial;
    }
    class Scene {
        addPointCloud(pointcloud: PointCloud): void;
    }
    class PointCloudMaterial {
        size: number;
        pointSizeType: PointSizeType;
        shape: PointShape;
    }
    function loadPointCloud(
        path: string,
        name: string,
        callback: (e: PointCloudEventVariable) => void
    ): void;
}

Because the Potree script files are loaded as external scripts, I can't use "import" like "import * as Potree from 'potree'".

Also, I can't write like "Viewer()" directly, I have to call it with "Potree." added, like "Potree.Viewer()".

Loading point cloud data

Potree can load a laz file.
But I have to convert it by PotreeConverter first.

I will get a laz file from 静岡県 - 掛川城オープンデータ化プロジェクト - データセット.

After finishing converting it, I can load it like below.

potreeAccessor.ts

export class PotreeAccessor {
    private viewer: Potree.Viewer;
    private scene: (Potree.Scene|null) = null;

    public constructor() {
        this.viewer = new Potree.Viewer(document.getElementById("potree_render_area") as HTMLElement);
        this.viewer.setEDLEnabled(false);
        this.viewer.setFOV(60);
        this.viewer.setPointBudget(1_000_000);
        this.viewer.loadSettingsFromURL();
        this.viewer.setBackground("skybox");
        Potree.loadPointCloud("./resources/PotreeData/metadata.json", "KakegawaCastle", e => {
            this.scene = this.viewer.scene;
            const pointcloud = e.pointcloud;

            const material = pointcloud.material;
            material.size = 1;
            material.pointSizeType = Potree.PointSizeType.ADAPTIVE;
            material.shape = Potree.PointShape.CIRCLE;

            this.scene.addPointCloud(pointcloud);
            this.viewer.fitToScreen();
        });
    }
}

[Go][Excelize] Determining if a cell's value has a strike through

Masui Masanori — Mon, 26 Aug 2024 18:16:02 +0000

Intro

I want to determine if a cell's value has a strike through.

Determining if a cell's value has a strike through

To determine if a cell's value has a strike through, I have to get cell styles in two ways.

If only some values of a cell are struck through like "A1", I should get cell styles from "excelize.RichTextRun".
If all values in a cell are struck through like "A2", I should get a cell style from "excelize.xlsxXf".

xlsxReader.go

package main

import (
    "fmt"
    "log"

    "github.com/xuri/excelize/v2"
)

func GetCellStyles(filePath string) {
    xlFile, err := excelize.OpenFile(filePath)
    if err != nil {
        fmt.Println(err)
    }
    defer func() {
        // Close the spreadsheet.
        if err := xlFile.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    sheetName := xlFile.GetSheetName(0)
    log.Println(sheetName)
    printCellStyles(xlFile, sheetName, "A1")
    printCellStyles(xlFile, sheetName, "A2")
    printCellStyles(xlFile, sheetName, "A3")
}
func printCellStyles(xlFile *excelize.File, sheetName string, cellAddress string) {
    log.Printf("--------%s--------", cellAddress)
    value, _ := xlFile.GetCellValue(sheetName, cellAddress)
    log.Println(value)
    // If the cell value has multiple formats, "GetCellRichText" will return multiple values.
    runs, _ := xlFile.GetCellRichText(sheetName, cellAddress)
    if len(runs) > 0 {
        for _, r := range runs {
            if r.Font == nil {
                log.Printf("Value: %s No fonts", r.Text)
            } else {
                log.Printf("Value: %s Strike through?: %t", r.Text, r.Font.Strike)
            }
        }
    }
    styleID, _ := xlFile.GetCellStyle(sheetName, cellAddress)
    // Get cell style info by styleID
    style := xlFile.Styles.CellXfs.Xf[styleID]
    // Get font info by style.FontID
    font := xlFile.Styles.Fonts.Font[*style.FontID]
    // Check
    if font.Strike != nil && *font.Strike.Val {
        log.Println("The cell value has a strike through")
    } else {
        log.Println("The cell value doesn't have a strike through")
    }
}

Results

2024/08/27 03:02:32 Sheet1
2024/08/27 03:02:32 --------A1--------
2024/08/27 03:02:32 123abc
2024/08/27 03:02:32 Value: 12 No fonts
2024/08/27 03:02:32 Value: 3ab Strike through?: true
2024/08/27 03:02:32 Value: c Strike through?: false
2024/08/27 03:02:32 The cell value doesn't have a strike through
2024/08/27 03:02:32 --------A2--------
2024/08/27 03:02:32 aiu
2024/08/27 03:02:32 The cell value has a strike through
2024/08/27 03:02:32 --------A3--------
2024/08/27 03:02:32 abc
def
ghi
2024/08/27 03:02:32 Value: abc
 No fonts
2024/08/27 03:02:32 Value: def Strike through?: false
2024/08/27 03:02:32 Value:
 Strike through?: false
2024/08/27 03:02:32 Value: ghi Strike through?: false
2024/08/27 03:02:32 The cell value doesn't have a strike through

Resources

Installing Nginx on Linux Mint 22

Masui Masanori — Fri, 26 Jul 2024 16:56:21 +0000

Intro

I just started using Linux Mint, so this time I will install Nginx.

Adding a repository

The basic installation procedure is the same as when I installed on Xubuntu 22.04.

Xubuntu + Nginx + Go(+ Pion/WebRTC) + Node.js

After I added the Nginx repository, I got a warning on executing "sudo apt update".

...
無視:8 http://nginx.org/packages/ubuntu wilma InRelease                        
ヒット:9 http://archive.ubuntu.com/ubuntu noble-updates InRelease
ヒット:10 http://archive.ubuntu.com/ubuntu noble-backports InRelease
エラー:11 http://nginx.org/packages/ubuntu wilma Release      
  404  Not Found [IP: 2a05:d014:5c0:2601::6 80]
パッケージリストを読み込んでいます... 完了
E: リポジトリ http://nginx.org/packages/ubuntu wilma Release には Release ファイルがありません。
N: このようなリポジトリから更新を安全に行うことができないので、デフォルトでは更新が無効になっています。
N: リポジトリの作成とユーザ設定の詳細は、apt-secure(8) man ページを参照してください。

When I add the repository, I need to explicitly specify the Ubuntu version instead of using "lsb_release -cs"

echo "deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg] \
http://nginx.org/packages/ubuntu noble nginx" \
    | sudo tee /etc/apt/sources.list.d/nginx.list

The version of Ubuntu that Linux Mint is based on can be obtained using the following command.

cat /etc/os-release

Results

NAME="Linux Mint"
VERSION="22 (Wilma)"
ID=linuxmint
ID_LIKE="ubuntu debian"
PRETTY_NAME="Linux Mint 22"
VERSION_ID="22"
HOME_URL="https://www.linuxmint.com/"
SUPPORT_URL="https://forums.linuxmint.com/"
BUG_REPORT_URL="http://linuxmint-troubleshooting-guide.readthedocs.io/en/latest/"
PRIVACY_POLICY_URL="https://www.linuxmint.com/"
VERSION_CODENAME=wilma
UBUNTU_CODENAME=noble

Specifying the repository architecture

I have another warning when I execute "sudo apt update".

...
取得:10 http://nginx.org/packages/ubuntu noble InRelease [2,843 B]             
取得:11 http://nginx.org/packages/ubuntu noble/nginx amd64 Packages [9,372 B]
12.2 kB を 2秒 で取得しました (6,647 B/s)
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています... 完了
状態情報を読み取っています... 完了        
パッケージはすべて最新です。
N: リポジトリ 'http://nginx.org/packages/ubuntu noble InRelease' がアーキテクチャ 'i386' をサポートしないため設定ファイル 'nginx/binary-i386/Packages' の取得をスキップ

I should specify the architecture in "/etc/apt/sources.list.d/nginx.list".

/etc/apt/sources.list.d/nginx.list

deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg arch=amd64] http://nginx.org/packages/ubuntu noble nginx:q