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