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); } }