public async Task ImportAsync(string filename) { var path = PathUtility.GetFullPath(filename, this.CommandContext.BaseDirectory); var sheetNames = SpreadsheetReader.ReadTableNames(path); var authentication = this.CommandContext.GetAuthentication(this); var dataBase = await this.DataBaseContext.GetDataBaseAsync(this.DataBaseName); var tables = await dataBase.GetTablesAsync(); var tableNames = tables.Select(item => item.Name).ToArray(); var query = from sheet in sheetNames join table in tableNames on sheet equals SpreadsheetUtility.Ellipsis(table) where StringUtility.GlobMany(table, FilterProperties.FilterExpression) orderby table select sheet; var filterExpression = string.Join(";", query); var revision = dataBase.Dispatcher.Invoke(() => dataBase.DataBaseInfo.Revision); var dataSet = await dataBase.GetDataSetAsync(authentication, CremaDataSetFilter.Default, revision); this.ReadDataSet(dataSet, path); await dataBase.ImportAsync(authentication, dataSet, this.Message); this.Out.WriteLine($"importing data has been completed."); }
public void ExportTestUseTemplate() { try { SpreadsheetUtility <Test> su = new SpreadsheetUtility <Test>(Test.GetDatas()); su.TemplateFile = @"D:\GithubRep\OpenXml\OpenXml.Utils\test\OpenXml.UtilsTests\TestFile\PlanSapDataT.xlsx"; su.TemplateRowIndexContent = 1; su.GenerateTableHead = false; su.TemplateUseLastRowStyle = true; su.Export("tt.xlsx"); } catch (Exception ex) { Assert.Fail(ex.Message); } }
public string[] GetTableNames() { if (this.cremaAppHost.GetService(typeof(IDataBase)) is IDataBase dataBase) { var tableNames = dataBase.Dispatcher.Invoke(() => { return(dataBase.TableContext.Tables.Select(item => item.Name).ToArray()); }); var sheetNames = this.itemsSource.SelectMany(item => item.GetSelectedSheetNames()).ToArray(); var query = from tableName in tableNames let tableName2 = SpreadsheetUtility.Ellipsis(tableName) join sheetName in sheetNames on tableName2 equals sheetName select tableName; return(query.ToArray()); } throw new NotImplementedException(); }
public List <PageElement> GetPageElementsFromExccelFile(string filename, string[] folderStructure) { var exeDir = FileAndFolder.GetExecutionDirectory(); Log.Info("Execution directory is: " + exeDir); var fileLocation = exeDir; foreach (var folder in folderStructure) { fileLocation = Path.Combine(fileLocation, folder); Log.Info("New Path: " + fileLocation); } fileLocation = Path.Combine(fileLocation, filename); if (!File.Exists(fileLocation)) { var message = $"Trying to Open {filename} from {fileLocation}"; Log.Error(message); throw new FileNotFoundException(message); } var spreadsheet = new SpreadsheetUtility(); var excelObjects = spreadsheet.GetExcelFileObjects(fileLocation); var pageElements = new List <PageElement>(); var rows = excelObjects.GetLength(0); //var columns = allObjects.GetLength(1); for (var r = 1; r < rows; r++) { var pageElement = new PageElement() { Name = excelObjects.GetValue(r, 0).ToString(), By = excelObjects.GetValue(r, 1).ToString(), Query = excelObjects.GetValue(r, 2).ToString(), }; Log.Info(pageElement + " Added To the Collection"); Console.WriteLine(pageElement); Assert.NotNull(pageElement); pageElements.Add(pageElement); } return(pageElements); }
private void ReadTables(CremaDataSet dataSet, IDataBase dataBase, string filename, string itemNames) { var sheetNames = SpreadsheetReader.ReadSheetNames(filename); var tableInfos = dataBase.Dispatcher.Invoke(() => { var query = from table in dataBase.TableContext.Tables let tableName2 = SpreadsheetUtility.Ellipsis(table.Name) join sheetName in sheetNames on tableName2 equals sheetName where table.Name.GlobMany(itemNames) || table.Path.GlobMany(itemNames) orderby table.Name select table.TableInfo; return(query.ToArray()); }); var typeInfos = dataBase.Dispatcher.Invoke(() => { var query = from table in dataBase.TableContext.Tables let tableName2 = SpreadsheetUtility.Ellipsis(table.Name) join sheetName in sheetNames on tableName2 equals sheetName where table.Name.GlobMany(itemNames) || table.Path.GlobMany(itemNames) from column in table.TableInfo.Columns where CremaDataTypeUtility.IsBaseType(column.DataType) == false let type = dataBase.TypeContext[column.DataType] as IType where type != null select type.TypeInfo; return(query.Distinct().ToArray()); }); foreach (var item in typeInfos) { dataSet.Types.Add(item); } foreach (var item in tableInfos) { if (item.TemplatedParent != string.Empty) { continue; } if (item.ParentName == string.Empty) { dataSet.Tables.Add(item); } else { dataSet.Tables[item.ParentName].Childs.Add(item); } } foreach (var item in tableInfos) { if (item.TemplatedParent != string.Empty && item.ParentName == string.Empty) { var dataTable = dataSet.Tables[item.TemplatedParent]; dataTable.Inherit(item.TableName); } } var progress = new ConsoleProgress(this.Out) { Style = ConsoleProgressStyle.None }; using (var reader = new SpreadsheetReader(filename)) { reader.Read(dataSet, progress); } }
public void ExportTest() { SpreadsheetUtility <Test> su = new SpreadsheetUtility <Test>(Test.GetDatas()); su.Export("cc.xlsx"); }
public void Setup() { _excel = new SpreadsheetUtility(); _exePath = FileAndFolder.GetProjectPath(); _filePath = Path.Combine(_exePath, "DevTests", SolutionFolders.Resources.ToString()); }