protected override void DoSeed()
{
var parser = new ExcelFileParser <CategoryFileRecordModel>();
ParseResult <CategoryFileRecordModel> result = null;
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(ResourceName))
{
result = parser.Parse(stream);
}
Defensive.AssertTrue(result.Success);
foreach (var group in result.Records.GroupBy(x => x.Category))
{
var category = new Category {
Name = group.Key
};
group.ToList().ForEach(x => category.Categories.Add(new SubCategory {
Name = x.SubCategory
}));
Context.Add(category); //todo use repository
}
}
protected override void DoSeed()
{
var parser = new ExcelFileParser <EntityAccountFileRecordModel>();
ParseResult <EntityAccountFileRecordModel> result = null;
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(ResourceName))
{
result = parser.Parse(stream);
}
Defensive.AssertTrue(result.Success);
foreach (var group in result.Records.GroupBy(x => x.Name))
{
var entity = Mapper.Map <Entity>(group.First());
entity.Ibans = group.Select(x =>
{
if (x.Iban != null)
{
x.Iban = x.Iban.Replace(" ", "");
}
return(Mapper.Map <Iban>(x));
})
.ToList();
Context.Add(entity); //todo use repository
}
}
protected override void DoSeed()
{
var parser = new ExcelFileParser <ProjectFileRecordModel>();
parser.AddDateTimeFormat("dd/MM/yyyy");
ParseResult <ProjectFileRecordModel> result = null;
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(ResourceName))
{
result = parser.Parse(stream);
}
//Defensive.AssertTrue(result.Success);
result.Records.ForEach(model =>
{
Console.WriteLine(model.Entity);
var account = Mapper.Map <Project>(model);
account.Budget = account.Budget * 100;
//var entity = Context.Entities.Single(x => x.Name == model.Entity);
//Defensive.AssertNotNull(entity, $"Entity with name {model.Entity} was not found");
//account.Entities.Add(new EntityProject { LeftLink = entity });
Context.Add(account);
});
}
public void ImportEmptyExcelFileTest()
{
string filePath = Path.Combine(this.TestContext.TestDeploymentDir, "Files\\1095_Import_Template.xlsx");
IExcelFileParser parser = new ExcelFileParser(filePath, "Employees", 1, 2);
IImportFileParameters <Employee> insertParameters = new ImportFileParameters <Employee>(parser, "dbo.Import_Employees", "EmployeeData");
//insertParameters.Mapper = this.MapValues;
var repo = new FileDataImportRepository();
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
conn.Open();
DeleteEmployees(conn); //just in case they switched the connection string to a regular sql server
Stopwatch sw = Stopwatch.StartNew();
repo.ImportFile <Employee>(conn, insertParameters);
sw.Stop();
Debug.WriteLine("Import elapsed time: {0}", sw.Elapsed);
int employeesCount = GetEmployeesCount(conn);
Assert.AreEqual(0, employeesCount); //as the number of the rows may change just assert we got any data added to the table
}
}
public void ParseSheetNames_AsIsTest()
{
string filePath = Path.Combine(this.TestContext.TestDeploymentDir, "Files\\1095_Import_WithData.xlsx");
var sheetNames = ExcelFileParser.GetSheetNames(filePath);
Assert.AreEqual(4, sheetNames.Count);
}
public AppExecutor()
{
_excelRepo = new ExcelFileParser(@"C:\Users\k.blazevicius\Desktop\test.xlsx");
var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["GerveSqlDbConnection"].ToString());
var sqlContext = new GerveContext(sqlConnection);
_sqlRepo = new GerveSqlRepository(sqlContext);
_dataProcesser = new DataProcesser <ExcelDto, SqlDto>();
}
public async Task UpdateFromFileAsync(Stream fileStream, bool onlyActual)
{
var fileParser = new ExcelFileParser();
var parsedPrograms = fileParser.ParsePrograms(fileStream);
parsedPrograms = NormalizePrograms(parsedPrograms, onlyActual);
foreach (var nProg in parsedPrograms)
{
var program = (await Database.Programs.GetAsync(nProg.Name))
?? new Program
{
Name = nProg.Name
};
var user = await Database.Users.GetAsync(nProg.UserId);
if (user != null)
{
var activeProgram =
await Database.UserPrograms.GetActiveAsync(nProg.UserId); //TODO Заменить на Find в случае чего
if (activeProgram != null)
{
if (activeProgram.StartedAt >= nProg.StartedAt)
{
continue;
}
activeProgram.FinishedAt = DateTime.Now;
Database.UserPrograms.Update(activeProgram);
}
}
else
{
user = new User
{
Id = nProg.UserId
};
Database.Users.Create(user);
}
var newUserProgram = new UserProgram
{
UserId = user.Id,
Foolowing = nProg.IsFollowed,
Program = program,
StartedAt = nProg.StartedAt
};
Database.UserPrograms.Create(newUserProgram);
await Database.SaveAsync();
}
}
public void TestImport()
{
var parser = new ExcelFileParser <StudentDraft> (fieldMaps);
if (TryGetSampleFile("Test1014.xlsx", out var path))
{
var values = parser.Read(File.OpenRead(path));
Assert.True(parser.ParseResult.IsValid);
Assert.NotEmpty(values);
}
}
public void ParseFileHeaders_NoBadCharsTest()
{
string filePath = Path.Combine(this.TestContext.TestDeploymentDir, "Files\\1095_Import_WithData.xlsx");
IExcelFileParser parser = new ExcelFileParser(filePath, "Employees", 1, 2);
var headers = parser.GetHeaders(true);
Assert.AreEqual(42, headers.Count);
Assert.AreEqual("L1Name", headers["A"]);
Assert.AreEqual("L2SSN", headers["B"]);
}
public IEnumerable <MeasurementDTO> GetFromFile(Stream file)
{
var fileParser = new ExcelFileParser();
var parsedMeasurements = fileParser.ParseMeasurements(file);
return(from measurement in parsedMeasurements
select new MeasurementDTO
{
Name = measurement.Name,
Value = measurement.Value,
MeasuredAt = measurement.MeasuredAt,
UserId = measurement.UserId
});
}
public static void ExcelImport1(string path)
{
Console.WriteLine("Running ExcelImport1");
/*This example shows importing a Company class from a file to a database using Streaming TVP. */
//same excel file parser for reading
IExcelFileParser parser = new ExcelFileParser(path, "Companies", 2, 3);
IImportFileParameters <Company> insertParameters = new ImportFileParameters <Company>(parser, "dbo.ImportCompanies", "Company_Import_tt");
/*
* REQUIRED Parameters:
* parser: The file parser
* procedureName: The stored proc to execute the import
* tableTypeName: The name of the table type that the parer is passed in to OR
* sqlMetaData: If, the table type name is not passed in, this must be. It is a list of the sql metadata columns that represent the table type. The FileDataImportRepository.GetSqlMetadata can build these for you.
*
* OPTIONAL Properties:
* CustomValidator: Provides custom validation to validate the object, allowing for custom errors
* Data: This represents the data rows from the parser object passed into the ctor, and should not be touched.
* ErrorColumnName: This is the column that the errors will be written to with an xml representation of the file import errors. Will only be automatically be written to when no mapping is provided.
* ExtraParameters: These are extra parameters that can be passed into the stored procedure.
* FileValuesMapper: Provides a custom mapper for the file parser to map the file row to the object
* Parser: This represents the parser itself that was passed into the ctor, and should not be touched
* ProcedureName: The name of the import stored procedure
* ProcedureParameterName: The named of the table type parameter in the stored proc
* SqlMetadata: the sqlmeta data that represents the columns of the table type
* TableTypeName: The name of the table type
* NOTE: Either the TableTypeName OR the SqlMetadata must be provided.
* Validator: The validator to use for file to object mapping
* CustomSqlMapper: Provides custom mapping from the class being imported to the sqlrecord which contains the sqlmetadata
*/
insertParameters.ProcedureParameterName = "@data";
insertParameters.CustomSqlMapper = Common.MapValues;
insertParameters.ErrorColumnName = "ImportErrors";
var repo = new FileDataImportRepository();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
repo.ImportFile <Company>(tran, insertParameters);
tran.Commit();
}
}
}
public void ParseExcelFileTest()
{
string filePath = Path.Combine(this.TestContext.TestDeploymentDir, "Files\\1095_Import_WithData.xlsx");
IExcelFileParser parser = new ExcelFileParser(filePath, "Employees", 1, 2);
IDictionary <int, IList <string> > fileErrors = new Dictionary <int, IList <string> >();
IObjectValidator validator = new ObjectValidator();
int rowIndex = parser.RowStart;
foreach (dynamic row in parser.ParseFile())
{
List <string> errors = new List <string>();
Employee rowObj = new Employee();
rowObj.MapValues(rowIndex, row, validator, ref errors);
validator.TryValidate(rowObj, ref errors);
if (errors.Count > 0)
{
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
Assert.IsTrue(fileErrors.Count >= 2);
parser.SheetName = "Dependents";
fileErrors = new Dictionary <int, IList <string> >();
rowIndex = parser.RowStart;
foreach (dynamic row in parser.ParseFile())
{
List <string> errors = new List <string>();
Dependent rowObj = new Dependent();
rowObj.MapValues(rowIndex, row, validator, ref errors);
validator.TryValidate(rowObj, ref errors);
if (errors.Count > 0)
{
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
Assert.IsTrue(fileErrors.Count == 0);
}
public void ParseFile_StopAtColumn()
{
string filePath = Path.Combine(this.TestContext.TestDeploymentDir, "Files\\1095_Import_WithData.xlsx");
IExcelFileParser parser = new ExcelFileParser(filePath, "Employees", 1, 2);
IDictionary <int, IList <string> > fileErrors = new Dictionary <int, IList <string> >();
IObjectValidator validator = new ObjectValidator();
parser.EndColumnKey = "H";
var data = parser.ParseFile();
dynamic firstRow = data.First();
Assert.AreEqual("Lucy Davis", firstRow.L1Name);
Assert.AreEqual("308-35-1715", firstRow.L2SSN);
}
public void ExcelTest()
{
IExcelFIleParser <IExcelFileSource, IParseResult> Parser = new ExcelFileParser <IExcelFileSource, IParseResult>();
Parser.Parse(new ExcelFileSource());
}
public void TestMethod1()
{
var instance = new ExcelFileParser();
instance.Parse(@"C:\Users\Paul\Documents\Dev\TestTool\Bdd.xlsx");
}
public static IList <Company> ReadExcelFile1(string path = null, int headerRow = 2, int dataRow = 3)
{
/* Some important notes about header names:
* 1) As the header name is being used for the "property" name of the dynamic object there are many characters that are not valid to be a valid c# property name.
* During the reading of the file, the invalid characters in the header name are replaced using this regex [^A-Za-z0-9_]* with an empty string.
* Then if the return of that replace operation is empty, then the column is named "Col(i)". Where (i) is the zero based column index.
* Examples:
* Column Name---------------Property Name
* Company Id----------------row.CompanyId
* Aims Company Id-----------row.AimsCompanyId
* Some_Id_9-----------------row.Some_Id_9
* (@@@)---------------------row.Col1 - where 1 is the zero based index of that column.
*
* 2) Case sensitivity of the property names does not matter either. As the sender could change the case indiscrimiately.
* 3) If a column is removed that you were expecting, the property will return empty and will not throw an exception.
*/
Console.WriteLine("Running ReadExcelFile1");
/*This example shows reading a Company class from a file. */
if (String.IsNullOrWhiteSpace(path))
{
path = Common.ExcelDataPath;
}
//the parser is designed to read one sheet from a file at a time. Other sheets would require a new parser, or just use the same parser and change the sheet name.
IExcelFileParser parser = new ExcelFileParser(path, "Companies", headerRow, dataRow);
//this is where we will store any parser errors as we parse the file
var fileErrors = new Dictionary <int, IList <string> >();
//this validator provides validation when parsing the columns, and data attributes like [Required] and it will also invoke the IValidatableObject interface
IObjectValidator validator = new ObjectValidator();
//as the rowindex may not start at row 1, get it from the parser
int rowIndex = parser.RowStart;
var companies = new List <Company>();
foreach (dynamic row in parser.ParseFile())
{
List <string> errors = new List <string>();
//create a reference to a custom mapper. this provides complete control over the mapping of the file row to the object, and the interface is skipped
FileValuesMap <Company> mapper = Common.Company_FileMapper;
//this utility performs mapping of the row to the object and invokes column mapping validation
var rowObj = FileIOUtilities.MapObject <Company>(row, rowIndex, validator, mapper, ref errors);
//calling the TryValidate method invoke the data annotation validation, and the IValidatableObject interface
validator.TryValidate(rowObj, ref errors);
companies.Add(rowObj);
if (errors.Count > 0)
{
//we got errors for this row, so add them to the fileErrors dictionary
fileErrors.Add(rowIndex, errors);
}
rowIndex++;
}
//write all the file errors out to the console
foreach (var errs in fileErrors)
{
foreach (var err in errs.Value)
{
Console.WriteLine("Line:{0}, Error: {1}", errs.Key, err);
}
}
return(companies);
}