/// <summary>Create a table in the database based on the specified one.</summary>
/// <param name="simulationName">Name of the simulation.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="table">The table.</param>
public void WriteTable(string simulationName, string tableName, DataTable table)
{
if (DoingPostProcessing)
{
TableToWrite tableToWrite = new TableToWrite();
tableToWrite.SimulationName = simulationName;
tableToWrite.TableName = tableName;
tableToWrite.Data = table;
WriteTable(new TableToWrite[1] {
tableToWrite
});
}
else
{
lock (TablesToWrite)
TablesToWrite.Add(new TableToWrite()
{
FileName = Filename,
SimulationName = simulationName,
TableName = tableName,
Data = table
});
}
}
/// <summary>
/// Write the specified tables to a single table in the DB. i.e. merge
/// all columns and rows in all specified tables into a single table.
/// </summary>
/// <param name="tables">The tables.</param>
private void WriteTable(TableToWrite[] tables)
{
// Open the .db for writing.
Open(forWriting: true);
// What table are we writing?
string tableName = tables[0].TableName;
// Get a list of all names and datatypes for each field in this table.
List<string> names = new List<string>();
List<Type> types = new List<Type>();
names.Add("SimulationID");
types.Add(typeof(int));
foreach (TableToWrite table in tables)
{
if (table.Data != null)
{
// If the table has a simulationname then go find its ID for later
if (table.Data.Columns.Contains("SimulationID"))
{
// do nothing.
}
else if (table.SimulationName != null)
table.SimulationID = GetSimulationID(table.SimulationName);
else if (table.Data.Columns.Contains("SimulationName"))
AddSimulationIDColumnToTable(table.Data);
// Go through all columns for this table and add to 'names' and 'types'
foreach (DataColumn column in table.Data.Columns)
{
if (!names.Contains(column.ColumnName) && column.ColumnName != "SimulationName")
{
names.Add(column.ColumnName);
types.Add(column.DataType);
}
}
}
}
// Create the table.
CreateTable(tableName, names.ToArray(), types.ToArray());
// Prepare the insert query sql
IntPtr query = PrepareInsertIntoTable(Connection, tableName, names.ToArray());
// Tell SQLite that we're beginning a transaction.
Connection.ExecuteNonQuery("BEGIN");
// Go through all tables and write the data.
foreach (TableToWrite table in tables)
{
// Write each row to the .db
if (table.Data != null)
{
object[] values = new object[names.Count];
foreach (DataRow row in table.Data.Rows)
{
for (int i = 0; i < names.Count; i++)
{
if (names[i] == "SimulationID" && table.SimulationID != int.MaxValue)
values[i] = table.SimulationID;
else if (table.Data.Columns.Contains(names[i]))
values[i] = row[names[i]];
}
// Write the row to the .db
Connection.BindParametersAndRunQuery(query, values);
}
}
}
// tell SQLite we're ending our transaction.
Connection.ExecuteNonQuery("END");
// finalise our query.
Connection.Finalize(query);
}
protected override void Execute(NativeActivityContext context)
{
string strFilePath = FilePath.Get(context);
string workbookName = FilePath.Get(context);
Worksheet xlWorksheet = null;
Range xlRange = null;
bool excelFileVisible = false;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(strFilePath))
{
ExcelHelper.Shared.Close_OpenedFile(strFilePath);
workbookName = Path.GetFileName(strFilePath);
string workSheetName = WorksheetName.Get(context);
Workbook xlWorkbook = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, workSheetName, false) != null;
dynamic worksheets = xlWorkbook.Worksheets;
dynamic worksheetObject = null;
DataTable dt = TableToWrite.Get(context);
if (dt == null)
{
xlWorkbook.Close();
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
Log.Logger.LogData("Table To Write parameter(Datatable) is null in activity Excel_Append", LogLevel.Error);
}
else
{
if (false == sheetExist)
{
worksheetObject = worksheets.Add();
worksheetObject.Name = workSheetName;
}
object[,] TwoDimensionalArray = null;
string colLetter = String.Empty;
string endingCell = String.Empty;
int rangeEnd = 0;
xlWorksheet = xlWorkbook.Sheets[workSheetName];
Range xlRangeExcel = xlWorksheet.UsedRange;
int rowCountExcel = xlRangeExcel.Rows.Count;
if (1 != rowCountExcel)
{
rowCountExcel = rowCountExcel + 1;
}
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
if (IsHeader == true)
{
rangeEnd = rowCount + rowCountExcel;
TwoDimensionalArray = ConvertDataTableToArray(dt);
}
else
{
rangeEnd = rowCount + rowCountExcel - 1;
TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(dt);
}
colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(colCount);
endingCell = colLetter + rangeEnd;
string startingCell = "A" + rowCountExcel;
xlRange = xlWorksheet.Range[startingCell, endingCell];
xlRange.Value = TwoDimensionalArray;
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
xlWorkbook.Save();
if (NeedToClose == true)
{
xlWorkbook.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
xlWorkbook.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
xlWorkbook.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + strFilePath + "\" in activity Excel_Append", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Append", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
/// <summary>Create a table in the database based on the specified one.</summary>
/// <param name="simulationName">Name of the simulation.</param>
/// <param name="tableName">Name of the table.</param>
/// <param name="table">The table.</param>
public void WriteTable(string simulationName, string tableName, DataTable table)
{
if (DoingPostProcessing)
{
TableToWrite tableToWrite = new TableToWrite();
tableToWrite.SimulationName = simulationName;
tableToWrite.TableName = tableName;
tableToWrite.Data = table;
DeleteTable(tableName);
WriteTable(new TableToWrite[1] { tableToWrite });
}
else
{
lock (TablesToWrite)
TablesToWrite.Add(new TableToWrite()
{
FileName = Filename,
SimulationName = simulationName,
TableName = tableName,
Data = table
});
}
}
protected override void Execute(NativeActivityContext context)
{
string strFilePath = FilePath.Get(context);
string workbookName = FilePath.Get(context);
Worksheet xlWorksheet = null;
Range xlRange = null;
bool excelFileVisible = false;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(strFilePath))
{
ExcelHelper.Shared.Close_OpenedFile(strFilePath);
workbookName = Path.GetFileName(strFilePath);
string workSheetName = WorksheetName.Get(context);
Workbook xlWorkbook = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, workSheetName, false) != null;
dynamic worksheets = xlWorkbook.Worksheets;
dynamic worksheetObject = null;
DataTable dt = TableToWrite.Get(context);
if (dt == null)
{
xlWorkbook.Close();
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Table To Write parameter(Datatable) is null in activity Excel_WriteFile", LogLevel.Error);
}
else
{
if (false == sheetExist)
{
worksheetObject = worksheets.Add();
worksheetObject.Name = workSheetName;
}
object[,] TwoDimensionalArray = null;
string colLetter = String.Empty;
string endingCell = String.Empty;
int rangeEnd = 0;
xlWorksheet = xlWorkbook.Sheets[workSheetName];
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
if (IsHeader == true)
{
rangeEnd = 1 + rowCount;
TwoDimensionalArray = ConvertDataTableToArray(dt);
}
else
{
rangeEnd = rowCount;
TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(dt);
}
colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(colCount);
endingCell = colLetter + rangeEnd;
xlRange = xlWorksheet.Range["A1", endingCell];
xlRange.Value = TwoDimensionalArray;
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
xlWorkbook.Save();
if (true == NeedToClose)
{
xlWorkbook.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
xlWorkbook.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
xlWorkbook.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + strFilePath + "\" in activity Excel_WriteFile", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_WriteFile", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
//if(xlRange != null)
// Marshal.ReleaseComObject(xlRange);
//if (xlWorksheet != null)
// Marshal.ReleaseComObject(xlWorksheet);
//if (xlWorkbook != null)
//{
// //close and release
// xlWorkbook.Close();
// Marshal.ReleaseComObject(xlWorkbook);
//}
//if (xlApp != null)
//{
// //quit and release
// xlApp.Quit();
// Marshal.ReleaseComObject(xlApp);
//}
//GC.Collect();
//GC.WaitForPendingFinalizers();
}
}
