/// <summary>
/// 从Excel文件中读取指定类型的数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public List <T> ReadData <T>(string tag = null) where T : class, new()
{
var modelType = typeof(T);
var configModel = configLoader.Load(modelType, tag);
if (configModel == null)
{
// 配置不存在
throw new DataReaderException($"Configurationo not found: type is '{modelType.AssemblyQualifiedName}', Tag is {tag ?? "null"}.");
}
var wb = WorkbookFactory.Create(filePath);
var sheet = wb.GetSheet(configModel.Sheet);
int startRow = configModel.StartRow < 1 ? 1 : configModel.StartRow;
if (sheet.LastRowNum < startRow)
{
// 起始行号错误
throw new DataReaderException($"StartRow configuration not correct. Type is {configModel.Class}.");
}
var formatter = new DataFormatter();
var headers = new ColumnNameIndexPair[0];
if (!configModel.NoHeaderRow)
{
int headerRowNum = configModel.HeaderRow > 0 ? configModel.HeaderRow - 1 : startRow - 1;
if (headerRowNum >= startRow)
{
// 表头行必须在起始数据行之前
throw new DataReaderException($"StartRow must greater than HeaderRow. Type is {configModel.Class}.");
}
var headerRow = sheet.GetRow(headerRowNum);
if (headerRow != null)
{
headers = headerRow.Select(cell => new ColumnNameIndexPair
{
Column = formatter.FormatCellValue(cell),
Index = cell.ColumnIndex
}).ToArray();
}
}
var validMappings = (from c in configModel.Properties
let tmp = headers.FirstOrDefault(h => h.Column == c.Column)
let hasColumn = !string.IsNullOrEmpty(c.Column)
where modelType.GetProperty(c.Property) != null && (!hasColumn || (hasColumn && tmp != null))
select new
{
c.Property,
c.Converter,
Index = string.IsNullOrEmpty(c.Column) ? c.ColumnIndex :
tmp == null ? -1 : tmp.Index
}).ToArray();
bool skipEmptyRow = configModel.SkipEmptyRow;
var list = new List <T>();
for (int i = startRow; i < sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (skipEmptyRow && IsBlankRow(row))
{
// 跳过空行
continue;
}
var model = new T();
foreach (var item in validMappings)
{
var prop = modelType.GetProperty(item.Property);
IDataConverter c = GetDataConverter(item.Converter);
var cell = row.GetCell(item.Index);
if (cell == null)
{
continue;
}
object value;
switch (cell.CellType)
{
case CellType.Numeric:
// 进一步验证是否为Date数据
if (DateUtil.IsCellDateFormatted(cell))
{
value = cell.DateCellValue;
}
else
{
value = cell.NumericCellValue;
}
break;
case CellType.String:
value = cell.StringCellValue;
break;
case CellType.Boolean:
value = cell.BooleanCellValue;
break;
default:
value = null;
break;
}
prop.SetValue(model, c == null ? value : c.ConvertData(value, prop.PropertyType));
}
list.Add(model);
}
return(list);
}
private async Task ProcessMessageAsync(SQSEvent.SQSMessage message)
{
var(bucket, key) = GetS3DetailsFromMessage(message);
await _dataUploader.UploadData(_dataConverterService.ConvertData(_s3Repository.GetData(bucket, key)));
}