nobobo
96 lines
2.8 KiB
C#
96 lines
2.8 KiB
C#
using ClosedXML.Excel;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Text.RegularExpressions;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace KssSmaPlaLib.IO.Excel
|
||
{
|
||
public class XlUxer
|
||
{
|
||
public static IXLCell GCell(IXLWorksheet sheet, string a1Address)
|
||
{
|
||
var cell = sheet.Cell(a1Address);
|
||
return GCell(cell);
|
||
}
|
||
|
||
public static IXLCell GCell(IXLWorksheet sheet, int row, int col)
|
||
{
|
||
var cell = sheet.Cell(row, col);
|
||
return GCell(cell);
|
||
}
|
||
|
||
public static IXLCell GCell(IXLCell cell)
|
||
{
|
||
return cell.IsMerged() ? cell.MergedRange().FirstCell() : cell;
|
||
}
|
||
|
||
public static bool TryValidateA1CellAddress(string a1Address, out string normalizedAddress)
|
||
{
|
||
normalizedAddress = null;
|
||
|
||
if (string.IsNullOrWhiteSpace(a1Address))
|
||
return false;
|
||
|
||
// 正規表現でA1形式（例：A1, B2, AA10など）をチェック
|
||
var regex = new Regex(@"^[A-Z]{1,3}[1-9][0-9]*$", RegexOptions.IgnoreCase);
|
||
if (!regex.IsMatch(a1Address))
|
||
return false;
|
||
|
||
// 正規化（大文字化）
|
||
normalizedAddress = a1Address.ToUpperInvariant();
|
||
return true;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Shorter
|
||
/// </summary>
|
||
/// <param name="colLetter"></param>
|
||
/// <returns></returns>
|
||
public static int GetColNo(string colLetter)
|
||
{
|
||
if (!TryConvertColLetterToNumber(colLetter, out var no))
|
||
{
|
||
return int.MinValue;
|
||
}
|
||
return no;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 列アルファベットを列番号（1オリジン）に変換
|
||
/// </summary>
|
||
/// <param name="colLetter">列アルファベット</param>
|
||
/// <param name="colNo">[出力]列番号（1オリジン）</param>
|
||
/// <returns>成否</returns>
|
||
public static bool TryConvertColLetterToNumber(string colLetter, out int colNo)
|
||
{
|
||
colNo = int.MinValue;
|
||
|
||
// バリデ（A～XFD）
|
||
if(!Regex.IsMatch(colLetter, @"^(?i)([A-Z]|[A-Z]{2}|[A-E][A-Z]{2})$"))
|
||
{
|
||
return false;
|
||
}
|
||
|
||
// 大文字化
|
||
colLetter = colLetter.ToUpper();
|
||
|
||
// アルファベット1文字ずつ見て列番号を算出
|
||
int no = 0;
|
||
foreach (char c in colLetter)
|
||
{
|
||
no = no * 26 + (c - 'A' + 1);
|
||
}
|
||
|
||
// 列番号範囲外なら失敗
|
||
if (no > 16384 || no < 1) return false;
|
||
|
||
// 成功
|
||
colNo = no;
|
||
return true;
|
||
}
|
||
}
|
||
}
|