Beispiel #1
0
        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();
            }
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
 static void SimpleImportData()
 {
     using (var context = new ExcelContext("sample.xls"))
     {
         var data = context.Products.ToArray();
     }
 }
Beispiel #4
0
        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();
            }
        }
Beispiel #5
0
            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);
            }
Beispiel #6
0
        /// <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());
        }
Beispiel #8
0
        /// <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);
        }
Beispiel #11
0
        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();
            }
        }
Beispiel #12
0
 /// <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));
 }
Beispiel #13
0
 /// <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)
 {
 }
Beispiel #14
0
        /// <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
        }
Beispiel #15
0
            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));;
            }
Beispiel #16
0
            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));
            }
Beispiel #17
0
        /// <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));
        }
Beispiel #18
0
 public ListProducts(ExcelContext context)
 {
     _context = context;
 }
Beispiel #19
0
        /// <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);
        }
Beispiel #20
0
 public ReadExcelSpreadsheet(ExcelContext context, IHostingEnvironment hostingEnvironment)
 {
     _context            = context;
     _hostingEnvironment = hostingEnvironment;
 }