/// <summary> /// Explicitly release all com objects /// </summary> static void ReleaseAllObjects() { if (LogCalls) { DebugLog.Message("ReleaseAllObjects"); } ReleaseObject(XlRange); ReleaseObject(XlShapeRange); ReleaseObject(XlShapes); // ReleaseObject(XlSelection); // ReleaseObject(XlTemp); ReleaseObject(XlQueryTables); ReleaseObject(XlPictures); ReleaseObject(XlSheet); ReleaseObject(XlBook); ReleaseObject(XlSheet2); ReleaseObject(XlBook2); ReleaseObject(XlBooks); XlRange = null; XlShapeRange = null; XlShapes = null; // XlSelection = null; // XlTemp = null; XlQueryTables = null; XlPictures = null; XlSheet = null; XlBook = null; XlSheet2 = null; XlBook2 = null; XlBooks = null; }
/// <summary> /// Import a .csv file /// </summary> /// <param name="?"></param> public static void ImportCsv(string fileName) { if (LogCalls) { DebugLog.Message("ExcelOp ImportCsv " + fileName); } try { ReleaseObject(XlQueryTables); XlQueryTables = (Microsoft.Office.Interop.Excel.QueryTables)XlSheet.QueryTables; QueryTables qt = XlQueryTables; CellSelect(1, 1); // set XlRange to ("$A$1") // Add a QueryTable for the file XlQueryTables.Add( Connection: "TEXT;" + fileName, Destination: XlRange); // Set the other QueryTable properties qt[1].Name = Path.GetFileNameWithoutExtension(fileName); //qt[1].FieldNames = true; //qt[1].RowNumbers = false; //qt[1].FillAdjacentFormulas = false; //qt[1].PreserveFormatting = true; //qt[1].RefreshOnFileOpen = false; //qt[1].RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells; //qt[1].SavePassword = false; //qt[1].SaveData = true; //qt[1].AdjustColumnWidth = true; //qt[1].RefreshPeriod = 0; qt[1].TextFilePromptOnRefresh = false; qt[1].TextFilePlatform = 437; // default = 2 qt[1].TextFileStartRow = 1; qt[1].TextFileParseType = XlTextParsingType.xlDelimited; qt[1].TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote; qt[1].TextFileConsecutiveDelimiter = false; qt[1].TextFileTabDelimiter = false; // default: true qt[1].TextFileSemicolonDelimiter = false; // default: true qt[1].TextFileCommaDelimiter = true; // default: false qt[1].TextFileSpaceDelimiter = false; qt[1].TextFileTrailingMinusNumbers = true; qt[1].Refresh(false); qt[1].Delete(); // cleanup return; } catch (Exception ex) { throw new Exception(ex.Message, ex); } }
private void RefreshSheetData() { Excel.Worksheet sheet = null; Excel.QueryTables queryTables = null; Excel.QueryTable queryTable = null; Excel.CustomProperty changesProperty = null; try { var module = this.AddinModule as AddinModule; module.SheetChangeEvent = false; sheet = ExcelApp.ActiveSheet as Excel.Worksheet; if (sheet != null) { queryTables = sheet.QueryTables; if (queryTables.Count > 0) { queryTable = queryTables.Item(1); queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells; queryTable.PreserveColumnInfo = true; queryTable.PreserveFormatting = true; queryTable.Refresh(false); } changesProperty = sheet.GetProperty("uncommittedchanges"); if (changesProperty != null) { changesProperty.Delete(); } } module.SheetChangeEvent = true; } finally { if (changesProperty != null) { Marshal.ReleaseComObject(changesProperty); } if (queryTable != null) { Marshal.ReleaseComObject(queryTable); } if (queryTables != null) { Marshal.ReleaseComObject(queryTables); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } } }
private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e) { Excel.Worksheet sheet = null; Excel.Range insertionRange = null; Excel.QueryTable queryTable = null; Excel.QueryTables queryTables = null; Excel.Range cellRange = null; Excel.CustomProperties sheetProperties = null; Excel.CustomProperty primaryKeyProperty = null; SqlConnectionStringBuilder builder = null; string connString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename"; string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename"; string databaseName = string.Empty; string tableName = string.Empty; try { var module = this.AddinModule as AddinModule; module.SheetChangeEvent = false; tableName = e.Node.Text; sheet = ExcelApp.ActiveSheet as Excel.Worksheet; cellRange = sheet.Cells; insertionRange = cellRange[1, 1] as Excel.Range; builder = new SqlConnectionStringBuilder(dcd.ConnectionString); databaseName = builder.InitialCatalog; if (!builder.IntegratedSecurity) { connString = connStringSQL; } connString = connString.Replace("@servername", builder.DataSource) .Replace("@databasename", databaseName) .Replace("@username", builder.UserID) .Replace("@password", builder.Password); queryTables = sheet.QueryTables; if (queryTables.Count > 0) { queryTable = queryTables.Item(1); queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName); } else { queryTable = queryTables.Add(connString, insertionRange, String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName)); } queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells; queryTable.PreserveColumnInfo = true; queryTable.PreserveFormatting = true; queryTable.Refresh(false); var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName); // save original table this.tableName = tableName; // to sheet name must be less then 31 characters long sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30)); chPrimaryKey.Text = primaryKey; sheetProperties = sheet.CustomProperties; primaryKeyProperty = sheetProperties.Add("PrimaryKey", primaryKey); module.SheetChangeEvent = true; } catch (Exception ex) { Console.Write(ex.Message); } finally { if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } if (sheetProperties != null) { Marshal.ReleaseComObject(sheetProperties); } if (cellRange != null) { Marshal.ReleaseComObject(cellRange); } if (queryTables != null) { Marshal.ReleaseComObject(queryTables); } if (queryTable != null) { Marshal.ReleaseComObject(queryTable); } if (insertionRange != null) { Marshal.ReleaseComObject(insertionRange); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } } }
private void tvTables_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e) { Excel.Worksheet sheet = null; Excel.Range insertionRange = null; Excel.QueryTable queryTable = null; Excel.QueryTables queryTables = null; Excel.Range cellRange = null; Excel.CustomProperties sheetProperties = null; Excel.CustomProperty primaryKeyProperty = null; Excel.CustomProperty tableColumnsProperty = null; Excel.CustomProperty tableLoadedProperty = null; SqlConnectionStringBuilder builder = null; string connString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=@servername;Initial Catalog=@databasename"; string connStringSQL = "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=@username;Password=@password;Data Source=@servername;Initial Catalog=@databasename"; string databaseName = string.Empty; string tableName = string.Empty; string xmlString = string.Empty; try { ExcelApp.EnableEvents = false; tableName = e.Node.Text; sheet = ExcelApp.ActiveSheet as Excel.Worksheet; cellRange = sheet.Cells; insertionRange = cellRange[1, 1] as Excel.Range; builder = new SqlConnectionStringBuilder(dcd.ConnectionString); databaseName = builder.InitialCatalog; if (!builder.IntegratedSecurity) { connString = connStringSQL; } connString = connString.Replace("@servername", builder.DataSource) .Replace("@databasename", databaseName) .Replace("@username", builder.UserID) .Replace("@password", builder.Password); queryTables = sheet.QueryTables; //clear Excel Querytables foreach (Excel.QueryTable prop in queryTables) { prop.Delete(); } sheet.Cells.ClearContents(); if (queryTables.Count > 0) { queryTable = queryTables.Item(1); queryTable.CommandText = String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName); } else { queryTable = queryTables.Add(connString, insertionRange, String.Format("SELECT * FROM [{0}].{1}", databaseName, tableName)); } queryTable.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells; queryTable.PreserveColumnInfo = true; queryTable.PreserveFormatting = true; queryTable.Refresh(false); var primaryKey = SqlUtils.GetPrimaryKey(dcd.ConnectionString, tableName); var tableColumns = SqlUtils.GetAllColumns(dcd.ConnectionString, tableName); // save original table this.tableName = tableName; // to sheet name must be less then 31 characters long sheet.Name = tableName.Substring(0, Math.Min(tableName.Length, 30)); chPrimaryKey.Text = primaryKey; sheetProperties = sheet.CustomProperties; //clear Excel properties to prevent duplicate primary key property error foreach (Excel.CustomProperty prop in sheetProperties) { prop.Delete(); } primaryKeyProperty = sheetProperties.Add("PrimaryKey", primaryKey); tableLoadedProperty = sheetProperties.Add("TableLoaded", 1); foreach (var cols in tableColumns) { xmlString += "<row column=\"" + cols.Key + "\" "; xmlString += "columndatatype=\"" + cols.Value + "\">"; xmlString += cols.Key; xmlString += "</row>"; } tableColumnsProperty = sheetProperties.Add("TableColumns", xmlString); ExcelApp.EnableEvents = true; } catch (Exception ex) { Console.Write(ex.Message); throw; } finally { if (primaryKeyProperty != null) { Marshal.ReleaseComObject(primaryKeyProperty); } if (tableLoadedProperty != null) { Marshal.ReleaseComObject(tableLoadedProperty); } if (tableColumnsProperty != null) { Marshal.ReleaseComObject(tableColumnsProperty); } if (sheetProperties != null) { Marshal.ReleaseComObject(sheetProperties); } if (cellRange != null) { Marshal.ReleaseComObject(cellRange); } if (queryTables != null) { Marshal.ReleaseComObject(queryTables); } if (queryTable != null) { Marshal.ReleaseComObject(queryTable); } if (insertionRange != null) { Marshal.ReleaseComObject(insertionRange); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } } }