public void FileService_IfColumnMapped() { var file = new FileService(); var para = new CSVConvertParameter() { HasHeader = true, Headers = new List <HeaderParameter>() { new HeaderParameter() { Index = 1, Name = "test1", Value = new ConfigurationItemViewModel() { dataType = DataTypeEnum.String, id = Guid.NewGuid(), name = "testcof", required = true, size = 0 } }, new HeaderParameter() { Index = 2, Name = "test2", Value = new ConfigurationItemViewModel() { dataType = DataTypeEnum.String, id = Guid.NewGuid(), name = "testcof", required = true, size = 0 } }, new HeaderParameter() { Index = 3, Name = "test3", Value = new ConfigurationItemViewModel() { dataType = DataTypeEnum.String, id = Guid.NewGuid(), name = "testcof", required = true, size = 0 } }, } }; var mapped = file.IfColumnMapped(para, 3); Assert.IsTrue(mapped.Name == "test3"); }
public DataTable ConvertCSVResult(CSVConvertParameter parameter) { var filePath = $"{Environment.CurrentDirectory}upload/csv/{parameter.id}.csv"; DataTable dt = new DataTable("csvTable"); using (CsvFileReader reader = new CsvFileReader(filePath)) { CsvRow row = new CsvRow(); int rowcount = 0; var columnMap = new Dictionary <int, string>(); while (reader.ReadRow(row)) { DataRow dr = dt.NewRow(); if (rowcount == 0) { foreach (var sColumn in row) { var column = CreateColumn(parameter, sColumn, row.IndexOf(sColumn)); if (column != null) { columnMap.Add(row.IndexOf(sColumn), column.ColumnName); dt.Columns.Add(column); } } if (!parameter.HasHeader) { PopulateRow(parameter, row, columnMap, dr, rowcount); dt.Rows.Add(dr); } } else { PopulateRow(parameter, row, columnMap, dr, rowcount); dt.Rows.Add(dr); } rowcount++; } } return(dt); }
public JsonResult ConvertCSVToDatatable(CSVConvertParameter para) { var result = new ResultData <DataTableViewModel>() { data = new DataTableViewModel() }; try { if (para == null) { throw new Exception("Parameter error."); } var dt = FileService.ConvertCSVResult(para); var res = FileService.GetDataTableResult(dt); result.data = res; result.ok = true; } catch (ParseException e) { result.ok = false; result.message = e.Message; result.error = e.GetData(); LogHelper.LogException("UploadCSVFile Failed", e); } catch (Exception e) { result.ok = false; result.error = new ParseExceptionParameter() { columnIndex = 0, rowIndex = 0 }; result.message = e.Message; LogHelper.LogException("UploadCSVFile Failed", e); } return(Json(result, JsonRequestBehavior.AllowGet)); }
public DataColumn CreateColumn(CSVConvertParameter parameter, string sColumn, int index) { DataColumn column = null; var mapped = IfColumnMapped(parameter, index); if (mapped != null) { column = new DataColumn(); var type = mapped.Value.dataType.ToString(); if (type == "Number") { type = "Int32"; } column.DataType = Type.GetType($"System.{type}"); column.AllowDBNull = !mapped.Value.required; if (mapped.Value.size > 0 && mapped.Value.dataType == DataTypeEnum.String) { column.MaxLength = mapped.Value.size; } column.ColumnName = mapped.Value.name; } return(column); }
public void PopulateRow(CSVConvertParameter parameter, CsvRow row, Dictionary <int, string> columnMap, DataRow dr, int rowIndex) { foreach (var sColumn in row) { var currentIndex = row.IndexOf(sColumn); var mapped = IfColumnMapped(parameter, currentIndex); if (mapped != null) { var columnName = columnMap.FirstOrDefault(x => x.Key == currentIndex); if (!string.IsNullOrEmpty(columnName.Value)) { // required fields if (mapped.Value.required && string.IsNullOrEmpty(sColumn)) { // throw custom exception throw new ParseException($"Column {row.IndexOf(sColumn)} not allow null values", new ParseExceptionParameter() { csv = row, columnIndex = row.IndexOf(sColumn), rowIndex = rowIndex + 1 }); } else if (mapped.Value.size > 0 && sColumn.Length > mapped.Value.size && mapped.Value.dataType == DataTypeEnum.String) { // throw custom exception throw new ParseException($"The data length of Column {row.IndexOf(sColumn)} is larger the configuration value: Max Length <= {mapped.Value.size} ", new ParseExceptionParameter() { csv = row, columnIndex = row.IndexOf(sColumn), rowIndex = rowIndex + 1 }); } else { switch (mapped.Value.dataType) { case DataTypeEnum.Number: int intvalue = 0; var parsed = Int32.TryParse(sColumn, out intvalue); if (!parsed) { throw new ParseException($"The Column {row.IndexOf(sColumn)} can't be parsed to : Int32 ", new ParseExceptionParameter() { csv = row, columnIndex = row.IndexOf(sColumn), rowIndex = rowIndex + 1 }); } break; case DataTypeEnum.Decimal: Decimal decimalvalue = 0; var parsedecimal = Decimal.TryParse(sColumn, out decimalvalue); if (!parsedecimal) { throw new ParseException($"The Column {row.IndexOf(sColumn)} can't be parsed to : Decimal ", new ParseExceptionParameter() { csv = row, columnIndex = row.IndexOf(sColumn), rowIndex = rowIndex + 1 }); } break; } dr[columnName.Value] = sColumn; } } } } }
public HeaderParameter IfColumnMapped(CSVConvertParameter parameter, int index) { return(parameter.Headers.FirstOrDefault(x => x.Value != null && x.Index == index)); }