101 lines
3.2 KiB
C#
101 lines
3.2 KiB
C#
|
using ClosedXML.Excel;
|
|||
|
|
using Microsoft.Extensions.Options;
|
|||
|
|
using YD_TrainingCompetition_Api.Options;
|
|||
|
|
|
|||
|
|
namespace YD_TrainingCompetition_Api.Competition;
|
|||
|
|
|
|||
|
|
public sealed class RosterService : IRosterService
|
|||
|
|
{
|
|||
|
|
private readonly RosterOptions _options;
|
|||
|
|
|
|||
|
|
public RosterService(IOptions<RosterOptions> options)
|
|||
|
|
{
|
|||
|
|
_options = options.Value;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public IReadOnlyCollection<string> GetTemplates()
|
|||
|
|
{
|
|||
|
|
if (!Directory.Exists(_options.FolderPath))
|
|||
|
|
{
|
|||
|
|
return [];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return Directory.GetFiles(_options.FolderPath, "*.xlsx")
|
|||
|
|
.Select(Path.GetFileName)
|
|||
|
|
.OfType<string>()
|
|||
|
|
.OrderBy(x => x, StringComparer.OrdinalIgnoreCase)
|
|||
|
|
.ToArray();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
public Task<IReadOnlyCollection<RosterEntry>> LoadAsync(string templateFileName, CancellationToken cancellationToken = default)
|
|||
|
|
{
|
|||
|
|
var fullPath = Path.Combine(_options.FolderPath, templateFileName);
|
|||
|
|
if (!File.Exists(fullPath))
|
|||
|
|
{
|
|||
|
|
throw new FileNotFoundException("Roster template not found", fullPath);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
using var workbook = new XLWorkbook(fullPath);
|
|||
|
|
var ws = workbook.Worksheets.First();
|
|||
|
|
var used = ws.RangeUsed();
|
|||
|
|
if (used is null)
|
|||
|
|
{
|
|||
|
|
return Task.FromResult<IReadOnlyCollection<RosterEntry>>([]);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
var headerRow = used.FirstRow().RowNumber();
|
|||
|
|
var lastRow = used.LastRow().RowNumber();
|
|||
|
|
var lastCol = used.LastColumn().ColumnNumber();
|
|||
|
|
|
|||
|
|
var idCol = FindColumn(ws, headerRow, lastCol, "序号", "编号", "道次", "设备");
|
|||
|
|
var nameCol = FindColumn(ws, headerRow, lastCol, "姓名", "队员", "学生");
|
|||
|
|
var schoolCol = FindColumn(ws, headerRow, lastCol, "单位", "学校", "班级");
|
|||
|
|
|
|||
|
|
if (idCol == 0) idCol = 1;
|
|||
|
|
if (nameCol == 0) nameCol = 2;
|
|||
|
|
if (schoolCol == 0) schoolCol = 3;
|
|||
|
|
|
|||
|
|
var result = new List<RosterEntry>();
|
|||
|
|
for (var row = headerRow + 1; row <= lastRow; row++)
|
|||
|
|
{
|
|||
|
|
var idText = ws.Cell(row, idCol).GetString().Trim();
|
|||
|
|
var name = ws.Cell(row, nameCol).GetString().Trim();
|
|||
|
|
var school = ws.Cell(row, schoolCol).GetString().Trim();
|
|||
|
|
|
|||
|
|
if (string.IsNullOrWhiteSpace(idText) || string.IsNullOrWhiteSpace(name))
|
|||
|
|
{
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (!int.TryParse(new string(idText.Where(char.IsDigit).ToArray()), out var idx))
|
|||
|
|
{
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
result.Add(new RosterEntry
|
|||
|
|
{
|
|||
|
|
DeviceId = idx.ToString(),
|
|||
|
|
Name = name,
|
|||
|
|
School = string.IsNullOrWhiteSpace(school) ? null : school,
|
|||
|
|
GroupName = Path.GetFileNameWithoutExtension(templateFileName)
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return Task.FromResult<IReadOnlyCollection<RosterEntry>>(result);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private static int FindColumn(IXLWorksheet ws, int headerRow, int lastCol, params string[] keywords)
|
|||
|
|
{
|
|||
|
|
for (var c = 1; c <= lastCol; c++)
|
|||
|
|
{
|
|||
|
|
var text = ws.Cell(headerRow, c).GetString().Trim();
|
|||
|
|
if (keywords.Any(k => text.Contains(k, StringComparison.OrdinalIgnoreCase)))
|
|||
|
|
{
|
|||
|
|
return c;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return 0;
|
|||
|
|
}
|
|||
|
|
}
|