static void AnonymousExportData() { var filename = Guid.NewGuid().ToString() + ".xlsx"; System.IO.File.WriteAllBytes(filename, Properties.Resources.Empty); Random r = new Random(); var items = Enumerable.Range(0, 1000).Select(i => new { Id = i, Name = "Product " + i.ToString(), Category = r.Next(1, 10), IsValid = true }).ToArray(); using (var context = new ExcelContext(filename)) { var item = items[0]; var operate = context.Database.Manager.CreateTable(item.GetType(), DbName.NameOnly("Sheet1$")); context.Executor.Execute(operate); context.Set(item, "[Sheet1$]").AddRange(items); context.Executor.Execute(); } }
/// <summary> /// Initializes a new instance of the <see cref="ExcelDataLayerTest"/> class. /// </summary> public ExcelDataLayerTest() { ExcelConnection connection = new ExcelConnection(); AdoExcelDataLayerFactory factory = new AdoExcelDataLayerFactory(connection); _context = new ExcelContext(factory); }
static void SimpleImportData() { using (var context = new ExcelContext("sample.xls")) { var data = context.Products.ToArray(); } }
static void AnonymousImportData() { using (var context = new ExcelContext("sample.xls")) { var item = new { Id = 1, Name = "P", IsValid = false }; var data = context.Set(item, "Products").ToArray(); } }
public static ExcelContext GetContext() { //create a base context var context = new ExcelContext(); //tell it to map your entity (parses the custom attributes) context.BuildAttributedEntity <DomainObject_Attributes>(); //use the context for your repository return(context); }
/// <summary> /// Initializes a new instance of the <see cref="ExcelReportsTest"/> class. /// </summary> public ExcelReportsTest() { DbSqlConnection connection = new DbSqlConnection(); ExcelConnection excelConnection = new ExcelConnection(); AdoSqlServerDataLayerFactory factory = new AdoSqlServerDataLayerFactory(connection); AdoExcelDataLayerFactory excelFactory = new AdoExcelDataLayerFactory(excelConnection); _dbContext = new DbContext(factory); _excelContext = new ExcelContext(excelFactory); }
public static string ToHtml(ExcelContext excelContext) { StringBuilder sb = new StringBuilder(); sb.Append(@"<style>.xlsText{mso-number-format:""@"";}</style>") .Append(@"<meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"" />") .Append(@"<table border=""1"" align=""center"" cellspacing=""1"" cellpadding=""1"" width=""100%"">") .Append(GetHtmlHeader(excelContext)) .Append(GetHtmlBody(excelContext)) .Append("</table>"); return(sb.ToString()); }
/// <summary> /// create new instance of <see cref="EPPlusDomainRepository"/> <br/> /// </summary> /// <param name="context">inforamtion about the data context i.e. entity mapping, general file options</param> /// <param name="excelFileOptions">data and options related to file specific information i.e. file location, data start, data length</param> /// <param name="logger">used for internal logging prints</param> public EPPlusDomainRepository(ExcelContext context, ExcelFileOptions excelFileOptions, ILogger <EPPlusDomainRepository <TEntity, TKey> > logger = null) { _logger = logger; Context = context; ExcelFileOptions = excelFileOptions; TargetProperties = Context.GetPropertyBuilders <TEntity>(); ValidateExcelSourceConfiguration(excelFileOptions, context); if (excelFileOptions.ExcelFileStream != null) { SetExcelDataSource(excelFileOptions.ExcelFileStream, excelFileOptions.WorksheetName, excelFileOptions.FilePassword); } }
/// <summary> /// 创建HTML主体 /// </summary> /// <param name="dt"></param> /// <param name="formats"></param> /// <returns></returns> static StringBuilder GetHtmlBody(ExcelContext excelContext) { StringBuilder sb = new StringBuilder(); foreach (DataRow dr in excelContext.Data.Rows) { sb.Append("<tr>"); foreach (var column in excelContext.Columns) { sb.Append(@"<td style=""text-align:center;"" width=""20%"" class=""xlsText"">"); sb.Append(ValueFormat(dr[column.Key], column.Format)); sb.Append("</td>"); } sb.Append("</tr>"); } return(sb); }
static StringBuilder GetHtmlHeader(ExcelContext excelContext) { StringBuilder sb = new StringBuilder(); sb.Append(@"<thead>"); if (!string.IsNullOrEmpty(excelContext.Title)) { sb.Append(@"<tr><th align=""center"" colspan=""") .Append(excelContext.Columns.Count) .Append(@"""><font size=""+1"" "); if (!string.IsNullOrEmpty(excelContext.HeaderBackground)) { sb.Append(" color=\"").Append(excelContext.HeaderBackground).Append("\""); } sb.Append(">").Append(excelContext.Title) .Append(@"</font></th></tr>"); } if (!string.IsNullOrEmpty(excelContext.Condition)) { sb.Append(@"<tr><td colspan=""") .Append(excelContext.Columns.Count) .Append(@""" ><font size=""+1"">统计条件:</font>") .Append(excelContext.Condition).Append(@"</td></tr>"); } ; sb.Append(@"</thead>"); sb.Append("<tr>"); foreach (var item in excelContext.Columns) { sb.Append(@"<th style=""text-align:center; background:#F3C458;"">") .Append(item.Name) .Append("</th>"); } sb.Append("</tr>"); return(sb); }
static void SimpleExportData() { var filename = Guid.NewGuid().ToString() + ".xlsx"; System.IO.File.WriteAllBytes(filename, Properties.Resources.Empty); Random r = new Random(); var products = Enumerable.Range(0, 1000).Select(i => new Product() { Id = i, Name = "Product " + i.ToString(), Category = r.Next(1, 10), Code = "P" + i.ToString(), IsValid = true }); using (var context = new ExcelContext(filename)) { var operate = context.Database.Manager.CreateTable <Product>(); context.Executor.Execute(operate); context.Products.AddRange(products); context.Executor.Execute(); } }
/// <summary> /// Initializes a new instance of the <see cref="ExcelRecordsMaker" /> class. /// </summary> /// <param name="dbConxtext">The database conxtext.</param> /// <param name="excelContext">The excel context.</param> /// <exception cref="ArgumentNullException">dbConxtext /// or /// excelContext</exception> public ExcelRecordsMaker(DbContext dbConxtext, ExcelContext excelContext) { _dbConxtext = dbConxtext ?? throw new ArgumentNullException(nameof(dbConxtext)); _excelContext = excelContext ?? throw new ArgumentNullException(nameof(excelContext)); }
/// <summary> /// create new instance of <see cref="EPPlusDomainRepository"/> <br/> /// for more options cosider calling <see cref="EPPlusDomainRepository{TEntity, TKey}.EPPlusDomainRepository(ExcelContext, ExcelFileOptions, ILogger{EPPlusDomainRepository{TEntity, TKey}})"/> and configer <see cref="ExcelFileOptions"/></param> /// </summary> /// <param name="context">inforamtion about the data context i.e. entity mapping, general file options</param> /// <param name="excelFileStream">file location for parsing, for more options cosider calling <see cref="EPPlusDomainRepository{TEntity, TKey}.EPPlusDomainRepository(ExcelContext, ExcelFileOptions, ILogger{EPPlusDomainRepository{TEntity, TKey}})"/> and configer <see cref="ExcelFileOptions"/></param> /// <param name="password">password for file access</param> /// <param name="logger">used for internal logging prints</param> public EPPlusDomainRepository(ExcelContext context, Stream excelFileStream, string password = default, ILogger <EPPlusDomainRepository <TEntity, TKey> > logger = null) : this(context, new ExcelFileOptions(excelFileStream, password), logger) { }
/// <summary> /// configures the repository to handle a new kind of data-format (row placement of data) /// </summary> /// <param name="headerRowIndex">index (1-Based) of the header row</param> /// <param name="dataStartRowIndex">index (1-Based) of the first data row</param> /// <param name="dataEndRowIndex">index (1-Based) of last valid Row; use 0 = unlimited but EndRowIndicator must be supplied.</param> /// <param name="endRowIndicator">returns true if parsed entry is not a valid row anymore. this marked row will be discarded</param> protected virtual void ValidateExcelSourceConfiguration(ExcelFileOptions options, ExcelContext context) { var excelEntityTypeBuilder = (context.ObtainEntityFromDictionary <TEntity>() as ExcelEntityBuilder <TEntity>); if (excelEntityTypeBuilder == null) { throw new ArgumentOutOfRangeException(nameof(options.HeaderLineNumber), $"Excel-{nameof(options.HeaderLineNumber)} indices are 1-Based."); } #region ParameterChecks if (options.HeaderLineNumber < 1) { throw new ArgumentOutOfRangeException(nameof(options.HeaderLineNumber), $"Excel-{nameof(options.HeaderLineNumber)} indices are 1-Based."); } if (options.DataStartLineNumber < 1) { throw new ArgumentOutOfRangeException(nameof(options.DataStartLineNumber), $"Excel-{nameof(options.DataStartLineNumber)} indices are 1-Based."); } if (options.DataMaximumLineNumber != ExcelFileOptions.DEFAULT_END_UNLIMITED && options.DataStartLineNumber > options.DataMaximumLineNumber) { throw new ArgumentOutOfRangeException($"{nameof(options.DataStartLineNumber)}{nameof(options.DataMaximumLineNumber)}" , $"{nameof(options.DataStartLineNumber)} must not be higher than {nameof(options.DataMaximumLineNumber)}"); } #endregion }
public static IExcelRepository <DomainObject_FluentApi, int> GetRepo_EndIndicator(ExcelContext context, ExcelFileOptions fileOptions, ILogger <EPPlusDomainRepository <DomainObject_FluentApi, int> > logger = null) { //how does the Domain object look like when no data is available anymore? //in our case primary key will still have the row id but both name fields will be null or empty string context.AddEndDelimiter <DomainObject_FluentApi>(x => string.IsNullOrEmpty(x.Prename) && string.IsNullOrEmpty(x.Surname)); return(new EPPlusDomainRepository <DomainObject_FluentApi, int>(context, fileOptions, logger));; }
public static IExcelRepository <DomainObject_FluentApi, int> GetRepo_LengthDelimited(ExcelContext context, ExcelFileOptions fileOptions, ILogger <EPPlusDomainRepository <DomainObject_FluentApi, int> > logger = null) { fileOptions.DataMaximumLineNumber = UnitTestMetadata.DelimitedExcelRepo_MaxmimumLineNumber; return(new EPPlusDomainRepository <DomainObject_FluentApi, int>(context, fileOptions, logger)); }
/// <summary> /// Used to test the Read method. /// </summary> /// <typeparam name="T"> /// Type of the output elements. /// </typeparam> /// <param name="testInput"> /// String representing the contents of the file or StreamReader. This string is fed to the Read method /// as though it came from a file or StreamReader. /// </param> /// <param name="fileDescription"> /// Passed to Read. /// </param> /// <returns> /// Output of Read. /// </returns> public IEnumerable <T> TestRead <T>(string filePath, string sheetName, ExcelFileDescription fileDescription) where T : class, new() { ExcelContext cc = new ExcelContext(); return(cc.Read <T>(filePath, sheetName, fileDescription)); }
public ListProducts(ExcelContext context) { _context = context; }
/// <summary> /// Used to test the Write method /// </summary> /// <typeparam name="T"> /// The type of the input elements. /// </typeparam> /// <param name="values"> /// The collection of input elements. /// </param> /// <param name="fileDescription"> /// Passed directly to write. /// </param> /// <returns> /// Returns a string with the content that the Write method writes to a file or TextWriter. /// </returns> public void TestWrite <T>(IEnumerable <T> values, string filePath, string sheetName, ExcelFileDescription fileDescription) where T : class { ExcelContext cc = new ExcelContext(); cc.Write(values, filePath, sheetName, fileDescription); }
public ReadExcelSpreadsheet(ExcelContext context, IHostingEnvironment hostingEnvironment) { _context = context; _hostingEnvironment = hostingEnvironment; }