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