public static TestcaseBlockerDataset ImportDataset()
{
Console.Write("Enter filename with path: ");
string filename = Console.ReadLine();
if (!File.Exists(filename))
{
Console.WriteLine($"File not found at {filename}");
return(null);
}
Console.WriteLine("Importing dataset...");
var options = new ExcelImportOptions
{
WorksheetName = "Rohdaten_19.02.2020",
FirstDataRow = 2,
ColumnNumberTestcaseId = 2,
ColumnNumberTestcaseName = 4,
ColumnNumberBlockerNames = 1,
BlockerSeparator = ","
};
var dataset = DataGenerator.ImportFromExcel(filename, options);
Console.WriteLine(dataset.ToString());
return(dataset);
}
private static DataTable SampleXYData()
{
var options = new ExcelImportOptions();
var ds = ImportManager.Import(@"Data\PivotData.xlsx", options);
using (var dv = new DataView(ds.Tables["SampleXY$"]))
{
return(dv.ToTable());
}
}
private void ImportDataset()
{
var options = new ExcelImportOptions
{
WorksheetName = "Rohdaten_19.02.2020",
FirstDataRow = 2,
ColumnNumberTestcaseId = 2,
ColumnNumberTestcaseName = 4,
ColumnNumberBlockerNames = 1,
BlockerSeparator = ","
};
var dlg = new OpenFileDialog
{
Filter = "Excel (*.xlsx)|*.xlsx"
};
if (dlg.ShowDialog() == true)
{
var optionsDlg = new ExcelImportDialog();
if (optionsDlg.ShowDialog() == true)
{
options = optionsDlg.ImportOptions;
if (options != null)
{
try
{
Dataset = DataGenerator.ImportFromExcel(dlg.FileName, options);
SetStatusMessage("Dataset imported.");
}
catch
{
Dataset = null;
SetStatusMessage("Importing dataset failed. Please check the data format and options.");
}
}
else
{
SetStatusMessage("Invalid import options given.");
}
}
}
}
/// <summary>
/// 处理excel文件
/// </summary>
/// <typeparam name="TImportDto">表头对应的类
/// <para>表头名称对应 <see cref="System.ComponentModel.DataAnnotations"/> 下的 DisplayName 特性，字段验证也可使用其下的所有特性，如 Required，StringLength，Range，RegularExpression 等】</para>
/// </typeparam>
/// <param name="fileStream">文件流</param>
/// <param name="optionAction">配置选项</param>
/// <returns></returns>
public List <ExcelSheetDataOutput <TImportDto> > ProcessExcelFile <TImportDto>(
Stream fileStream,
Action <ExcelImportOptions> optionAction = null
) where TImportDto : class, new()
{
try
{
//设置、验证 配置
ExcelImportOptions options = new ExcelImportOptions();
optionAction?.Invoke(options);
options.CheckError();
return(ProcessWorkbook <TImportDto>(fileStream, options));
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
protected override ExcelDataRowRangeIndex GetDataRowStartAndEndRowIndex(ExcelWorkbook workbook, ExcelWorksheet worksheet, ExcelImportOptions options)
{
int startRowIndex = options.DataRowStartIndex - 1;
int endRowIndex = worksheet.Dimension.End.Row - 1;
if (options.DataRowEndIndex != null)
{
int end = (int)options.DataRowEndIndex - 1;
endRowIndex = end > endRowIndex ? endRowIndex : end;
}
return(new ExcelDataRowRangeIndex(startRowIndex, endRowIndex));
}
protected override ExcelRow GetHeaderRow(ExcelWorkbook workbook, ExcelWorksheet worksheet, ExcelImportOptions options)
{
return(worksheet.Row(options.HeaderRowIndex));
}
public static TestcaseBlockerDataset ImportFromExcel(string filename, ExcelImportOptions options)
{
if (string.IsNullOrEmpty(options.WorksheetName))
{
throw new ArgumentException("WorksheetName is empty");
}
if (string.IsNullOrEmpty(options.BlockerSeparator))
{
throw new ArgumentException("BlockerSeparator is empty");
}
if (options.ColumnNumberTestcaseId <= 0)
{
throw new ArgumentException("ColumnNumberTestcaseId must be greater than zero.");
}
if (options.ColumnNumberTestcaseName <= 0)
{
throw new ArgumentException("ColumnNumberTestcaseName must be greater than zero.");
}
if (options.ColumnNumberBlockerNames <= 0)
{
throw new ArgumentException("ColumnNumberBlockerNames must be greater than zero.");
}
if (options.FirstDataRow <= 0)
{
throw new ArgumentException("FirstDataRow must be greater than zero.");
}
try
{
var blockers = new List <Blocker>();
var testcases = new List <Testcase>();
FileInfo existingFile = new FileInfo(filename);
using (var excel = new ExcelPackage(existingFile))
{
var ws = excel.Workbook.Worksheets[options.WorksheetName];
if (!options.LastDataRow.HasValue)
{
options.LastDataRow = ws.Dimension.End.Row;
}
for (int row = options.FirstDataRow; row <= options.LastDataRow.Value; row++)
{
var testcaseId = (Convert.ToString(ws.Cells[row, options.ColumnNumberTestcaseId].Value)).Trim();
var testcaseName = (Convert.ToString(ws.Cells[row, options.ColumnNumberTestcaseName].Value)).Trim();
var blockerNamesString = (Convert.ToString(ws.Cells[row, options.ColumnNumberBlockerNames].Value)).Trim();
var blockerNames = new List <string>(blockerNamesString.Split(options.BlockerSeparator.ToCharArray()));
var testtype = options.ColumnNumberTestType.HasValue ? (Convert.ToString(ws.Cells[row, options.ColumnNumberTestType.Value].Value)).Trim() : string.Empty;
var applicationModule = options.ColumnNumberApplicationModule.HasValue ? (Convert.ToString(ws.Cells[row, options.ColumnNumberApplicationModule.Value].Value)).Trim(): string.Empty;
var testcase = new Testcase
{
Id = testcaseId,
Name = testcaseName,
TestType = testtype,
ApplicationModule = applicationModule
};
foreach (var blockerName in blockerNames)
{
var trimmedBlockerName = blockerName.Trim().ToLower();
if (!string.IsNullOrEmpty(trimmedBlockerName))
{
var existingBlocker = blockers.Where(x => x.Name == trimmedBlockerName).FirstOrDefault();
if (existingBlocker == null)
{
existingBlocker = new Blocker
{
Id = Guid.NewGuid().ToString().Replace("-", ""),
Name = trimmedBlockerName,
Cost = 1
};
blockers.Add(existingBlocker);
}
if (!testcase.BlockerIds.Contains(existingBlocker.Id))
{
testcase.BlockerIds.Add(existingBlocker.Id);
}
}
}
testcases.Add(testcase);
}
}
var dataset = new TestcaseBlockerDataset
{
Blockers = blockers,
Testcases = testcases
};
return(dataset);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
protected override IRow GetHeaderRow(IWorkbook workbook, ISheet worksheet, ExcelImportOptions options)
{
return(worksheet.GetRow(options.HeaderRowIndex - 1));
}
/// <summary>
/// 处理excel文件
/// </summary>
/// <param name="fileStream">文件流</param>
/// <param name="options">配置选项</param>
/// <returns></returns>
private List <ExcelSheetDataOutput <TImportDto> > ProcessWorkbook <TImportDto>(Stream fileStream, ExcelImportOptions options) where TImportDto : class, new()
{
var dataList = new List <ExcelSheetDataOutput <TImportDto> >();
//工作册
TWorkbook workbook = GetWorkbook(fileStream);
//工作表总数
int sheetsCount = GetWorksheetNumber(workbook);
if (options.SheetIndex > sheetsCount)
{
throw new Exception($"工作表 sheet 编号超出：最大只能为 {sheetsCount}");
}
//设置工作表数据
if (options.SheetIndex <= 0)
{
//全部 Sheet
for (int i = 0; i < sheetsCount; i++)
{
var data = ProcessWorksheet <TImportDto>(workbook, i, options);
dataList.Add(data);
//验证模式
if (data.InvalidCount > 0)
{
if (options.ValidateMode.Equals(ExcelValidateModeEnum.StopSheet))
{
break;
}
if (options.ValidateMode.Equals(ExcelValidateModeEnum.ThrowSheet))
{
data.CheckError();
}
}
}
}
else
{
//单个 Sheet
var data = ProcessWorksheet <TImportDto>(workbook, options.SheetIndex - 1, options);
dataList.Add(data);
//验证模式
if (dataList.Any(a => a.InvalidCount > 0))
{
if (options.ValidateMode.Equals(ExcelValidateModeEnum.ThrowSheet))
{
dataList.CheckError();
}
}
}
//验证模式
if (dataList.Any(a => a.InvalidCount > 0))
{
if (options.ValidateMode.Equals(ExcelValidateModeEnum.ThrowBook))
{
dataList.CheckError();
}
}
return(dataList);
}
/// <summary> /// 获取数据行的 起始、结束行下标编号（起始下标：0）【步骤 7】 /// </summary> /// <param name="workbook">工作册</param> /// <param name="worksheet">工作表</param> /// <param name="options">配置选项</param> /// <returns></returns> protected abstract ExcelDataRowRangeIndex GetDataRowStartAndEndRowIndex(TWorkbook workbook, TSheet worksheet, ExcelImportOptions options);
/// <summary> /// 获取表头行【步骤 5】 /// </summary> /// <param name="workbook">工作册</param> /// <param name="worksheet">工作表</param> /// <param name="options">配置选项</param> /// <returns></returns> protected abstract TRow GetHeaderRow(TWorkbook workbook, TSheet worksheet, ExcelImportOptions options);
/// <summary>
/// 处理工作表
/// </summary>
/// <param name="workbook">工作册</param>
/// <param name="worksheet">工作表</param>
/// <param name="headerCellInfo"></param>
/// <param name="headerCellProperties">表头单元格信息集合</param>
/// <param name="options">配置选项</param>
/// <returns></returns>
private List <ExcelImportRowInfo <TImportDto> > ProcessWorksheetData <TImportDto>(TWorkbook workbook, TSheet worksheet, ExcelHeaderCellInfo headerCellInfo, ExcelHeaderCellPropertyInfo headerCellProperties, ExcelImportOptions options) where TImportDto : class, new()
{
var rows = new List <ExcelImportRowInfo <TImportDto> >();
//获取数据行区域索引
var rowRangeIndex = GetDataRowStartAndEndRowIndex(workbook, worksheet, options);
for (var i = rowRangeIndex.StartIndex; i <= rowRangeIndex.EndIndex; i++)
{
//获取数据行
TRow row = GetDataRow(workbook, worksheet, i);
//获取行数据
TImportDto entity = GetRowData <TImportDto>(workbook, worksheet, headerCellProperties, row);
if (entity != null)
{
//验证数据
var valid = ExcelHelper.GetValidationResult(entity);
var rowInfo = new ExcelImportRowInfo <TImportDto>
{
SheetName = headerCellInfo.SheetName,
SheetIndex = headerCellInfo.SheetIndex,
Row = entity,
Errors = valid,
RowNum = i + 1,
IsValid = valid == null
};
rows.Add(rowInfo);
if (!rowInfo.IsValid)
{
if (options.ValidateMode.Equals(ExcelValidateModeEnum.StopRow))
{
break;
}
if (options.ValidateMode.Equals(ExcelValidateModeEnum.ThrowRow))
{
rowInfo.CheckError();
}
}
}
}
return(rows);
}
/// <summary>
/// 获取工作表数据
/// </summary>
/// <typeparam name="TImportDto"></typeparam>
/// <param name="workbook">工作册</param>
/// <param name="sheetIndex">工作表下标（其实下表：0）</param>
/// <param name="options">选项配置</param>
/// <returns></returns>
private ExcelSheetDataOutput <TImportDto> ProcessWorksheet <TImportDto>(TWorkbook workbook, int sheetIndex, ExcelImportOptions options) where TImportDto : class, new()
{
//获取工作表
TSheet worksheet = GetWorksheet(workbook, sheetIndex);
//工作表名称
string sheetName = GetWorksheetName(workbook, worksheet);
try
{
//获取表头行
TRow headerRow = GetHeaderRow(workbook, worksheet, options);
//获取表头单元格集合
List <ExcelHeaderCell> headerCells = GetHeaderCells(workbook, worksheet, headerRow);
//表头单元格信息
ExcelHeaderCellInfo headerCellInfo = new ExcelHeaderCellInfo(sheetName, sheetIndex, headerCells);
//验证表头行
ValidateHeaderRow <TImportDto>(headerCellInfo);
//获取表头单元格属性信息
ExcelHeaderCellPropertyInfo headerCellProperties = GetHeaderCellProperties <TImportDto>(headerCellInfo);
//设置工作表数据
return(new ExcelSheetDataOutput <TImportDto>
{
SheetName = sheetName,
SheetIndex = sheetIndex + 1,
Rows = ProcessWorksheetData <TImportDto>(workbook, worksheet, headerCellInfo, headerCellProperties, options),
});
}
catch (Exception e)
{
throw new Exception($"工作表【{sheetName}】存在以下错误：{e.Message}", e);
}
}
