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