Example #1
0
 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));
     }
 }
Example #2
0
 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);
         }
 }
Example #3
0
 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);
         }
 }
Example #4
0
 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
 }
Example #5
0
 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; }));
         }
 }
Example #6
0
 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);
 }
Example #7
0
        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);
        }
Example #8
0
 private static IExcelReaderAsync GetReaderProvider(Stream stream, ExcelType excelType)
 {
     return(ExcelReaderFactory.GetProvider(stream, ExcelTypeHelper.GetExcelType(stream, excelType)));
 }
Example #9
0
 /// <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)));
 }
Example #10
0
 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));
 }
Example #11
0
 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));
 }
Example #12
0
 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));
 }
Example #13
0
 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));
 }