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