public static DataTable QueryAsDataTable(string path, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) { using (var stream = FileHelper.OpenSharedRead(path)) { return(QueryAsDataTable(stream, useHeaderRow, sheetName, excelType: ExcelTypeHelper.GetExcelType(path, excelType), startCell, configuration)); } }
public static IEnumerable <dynamic> Query(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) { using (var stream = FileHelper.OpenSharedRead(path)) foreach (var item in Query(stream, useHeaderRow, sheetName, ExcelTypeHelper.GetExcelType(path, excelType), startCell, configuration)) { yield return(item); } }
public static IEnumerable <T> Query <T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new() { using (var excelReader = ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType), configuration)) foreach (var item in excelReader.Query <T>(sheetName, startCell)) { yield return(item); } }
public static IEnumerable <T> Query <T>(string path, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new() { using (var stream = FileHelper.OpenSharedRead(path)) foreach (var item in Query <T>(stream, sheetName, ExcelTypeHelper.GetExcelType(path, excelType), startCell, configuration)) { yield return(item); } //Foreach yield return twice reason : https://stackoverflow.com/questions/66791982/ienumerable-extract-code-lazy-loading-show-stream-was-not-readable }
public static IEnumerable <dynamic> Query(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) { using (var excelReader = ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType), configuration)) foreach (var item in excelReader.Query(useHeaderRow, sheetName, startCell)) { yield return(item.Aggregate(new ExpandoObject() as IDictionary <string, object>, (dict, p) => { dict.Add(p); return dict; })); } }
public static void SaveAs(string path, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null) { if (Path.GetExtension(path).ToLowerInvariant() == ".xlsm") { throw new NotSupportedException("MiniExcel SaveAs not support xlsm"); } using (FileStream stream = new FileStream(path, FileMode.CreateNew)) SaveAs(stream, value, printHeader, sheetName, ExcelTypeHelper.GetExcelType(path, excelType), configuration); }
public static DataTable QueryAsDataTable(this Stream stream, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) { if (sheetName == null && excelType != ExcelType.CSV) /*Issue #279*/ { sheetName = stream.GetSheetNames().First(); } var dt = new DataTable(sheetName); var first = true; var rows = ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType), configuration).Query(useHeaderRow, sheetName, startCell); var keys = new List <string>(); foreach (IDictionary <string, object> row in rows) { if (first) { foreach (var key in row.Keys) { if (!string.IsNullOrEmpty(key)) // avoid #298 : Column '' does not belong to table { var column = new DataColumn(key, typeof(object)) { Caption = key }; dt.Columns.Add(column); keys.Add(key); } } dt.BeginLoadData(); first = false; } var newRow = dt.NewRow(); foreach (var key in keys) { newRow[key] = row[key]; //TODO: optimize not using string key } dt.Rows.Add(newRow); } dt.EndLoadData(); return(dt); }
private static IExcelReaderAsync GetReaderProvider(Stream stream, ExcelType excelType) { return(ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType))); }
/// <summary> /// QueryAsDataTable is not recommended, because it'll load all data into memory. /// </summary> public static Task <DataTable> QueryAsDataTableAsync(string path, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) { return(Task.Run(() => QueryAsDataTable(path, useHeaderRow, sheetName, ExcelTypeHelper.GetExcelType(path, excelType), startCell, configuration))); }
public static Task <IEnumerable <T> > QueryAsync <T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new() { return(ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType)).QueryAsync <T>(sheetName, startCell, configuration)); }
public static async Task <DataTable> QueryAsDataTableAsync(string path, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken)) { return(await Task.Run(() => QueryAsDataTable(path, useHeaderRow, sheetName, ExcelTypeHelper.GetExcelType(path, excelType), startCell, configuration), cancellationToken).ConfigureAwait(false)); }
public static async Task <IEnumerable <T> > QueryAsync <T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null, CancellationToken cancellationToken = default(CancellationToken)) where T : class, new() { return(await ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType), configuration).QueryAsync <T>(sheetName, startCell, cancellationToken).ConfigureAwait(false)); }
public static IEnumerable <dynamic> Query(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) { return(ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType)).Query(useHeaderRow, sheetName, startCell, configuration)); }