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