private static bool Exists(Excel.Names names, string fragment)
{
foreach (Excel.Name name in names)
{
if (name.NameLocal.Equals(fragment))
{
return(true);
}
}
return(false);
}
public void CreateControl(ExcelInterop.Range range)
{
ExcelInterop.Worksheet workSheet = null;
try
{
Range = range;
workSheet = Range.Worksheet;
if (!string.IsNullOrEmpty(name))
{
ExcelInterop.Names names = null;
try
{
names = workSheet.Names;
rangeName = names.Add(name, Range);
}
catch (COMException ex)
{
throw new EtkException($"Cannot create named caller '{name}': {ex.Message}");
}
finally
{
if (names != null)
{
ExcelApplication.ReleaseComObject(names);
names = null;
}
}
}
if (NestedContextItem != null && NestedContextItem is IExcelControl)
{
((IExcelControl)NestedContextItem).CreateControl(range);
}
}
finally
{
if (workSheet != null)
{
ExcelApplication.ReleaseComObject(workSheet);
workSheet = null;
}
}
}
/// <summary>
/// Retrieve worksheet and name range names.
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public bool GetInformation()
{
bool Success = true;
if (!(System.IO.File.Exists(FileName)))
{
Exception ex = new Exception("Failed to locate '" + FileName + "'");
this.LastException = ex;
throw ex;
}
mSheets.Clear();
mNameRanges.Clear();
mSheetsData.Clear();
if (mReferenceTables != null)
{
mReferenceTables.Clear();
}
Excel.Application xlApp = null;
Excel.Workbooks xlWorkBooks = null;
Excel.Workbook xlWorkBook = null;
Excel.Workbook xlActiveRanges = null;
Excel.Names xlNames = null;
Excel.Sheets xlWorkSheets = null;
try
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlWorkBooks = xlApp.Workbooks;
xlWorkBook = xlWorkBooks.Open(FileName);
xlActiveRanges = xlApp.ActiveWorkbook;
xlNames = xlActiveRanges.Names;
for (int x = 1; x <= xlNames.Count; x++)
{
Excel.Name xlName = xlNames.Item(x);
mNameRanges.Add(xlName.Name);
Marshal.FinalReleaseComObject(xlName);
xlName = null;
}
xlWorkSheets = xlWorkBook.Sheets;
for (int x = 1; x <= xlWorkSheets.Count; x++)
{
Excel.Worksheet Sheet1 = (Excel.Worksheet)xlWorkSheets[x];
mSheets.Add(Sheet1.Name);
mSheetsData.Add(x, Sheet1.Name);
Marshal.FinalReleaseComObject(Sheet1);
Sheet1 = null;
}
GetReferenceTables(xlWorkSheets);
ReleaseComObject(xlWorkSheets);
xlWorkBook.Close();
xlApp.UserControl = true;
xlApp.Quit();
}
catch (Exception ex)
{
this.LastException = ex;
Success = false;
}
finally
{
if (xlWorkSheets != null)
{
Marshal.FinalReleaseComObject(xlWorkSheets);
xlWorkSheets = null;
}
if (xlNames != null)
{
Marshal.FinalReleaseComObject(xlNames);
xlNames = null;
}
if (xlActiveRanges != null)
{
Marshal.FinalReleaseComObject(xlActiveRanges);
xlActiveRanges = null;
}
if (xlActiveRanges != null)
{
Marshal.FinalReleaseComObject(xlActiveRanges);
xlActiveRanges = null;
}
if (xlWorkBook != null)
{
Marshal.FinalReleaseComObject(xlWorkBook);
xlWorkBook = null;
}
if (xlWorkBooks != null)
{
Marshal.FinalReleaseComObject(xlWorkBooks);
xlWorkBooks = null;
}
if (xlApp != null)
{
Marshal.FinalReleaseComObject(xlApp);
xlApp = null;
}
}
return(Success);
}
public static void EntityToExcel(string template, string excelFileName, IQueryable query, ObjectContext context, object bgWorker)
{
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
Excel.Range range = null;
BackgroundWorker bW = (BackgroundWorker)bgWorker;
bW.ReportProgress(percentageHalf);
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
wb = excelApp.Workbooks.Add(template);
ws = (Excel.Worksheet)wb.ActiveSheet;
Excel.Names names = wb.Names;
DataSet ds = EntityToDataSet(query, context);
int tableCount = ds.Tables.Count;
foreach (DataTable dt in ds.Tables)
{
int rowCount = 0;
int rows = dt.Rows.Count;
foreach (DataRow row in dt.Rows)
{
rowCount++;
foreach (DataColumn column in dt.Columns)
{
foreach (Excel.Name name in names)
{
if (name.Name.ToUpper() == column.ColumnName.ToUpper())
{
range = (Excel.Range)ws.get_Range(name);
range.Value = row[column.ColumnName];
break;
}
}
}
if (rows > 1 && rowCount < rows)
{
range = range.EntireRow;
range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, true);
Excel.Range destRange = (Excel.Range)ws.Rows[range.Row - 1];
range.Copy(destRange);
destRange = null;
}
}
bW.ReportProgress(percentageComplete / (tableCount)--);
}
names = null;
wb.SaveAs(excelFileName,
Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
wb.Close(Type.Missing, Type.Missing, Type.Missing);
excelApp.Quit();
range = null;
ws = null;
wb = null;
excelApp = null;
}
catch (Exception ex)
{
throw ex;
}
}
public static void DeleteNamesWithExcelApp(String[] tps)
{
Excel.Workbooks xlWorkbooks = null;
Excel.Workbook xlWorkbook = null;
Excel.Names ranges = null;
var xlApp = new Excel.Application();
try
{
foreach (String m in tps)
{
xlWorkbooks = xlApp.Workbooks;
xlWorkbook = xlWorkbooks.Open(m);
ranges = xlWorkbook.Names;
int leftoveritems = ranges.Count;
Excel.Name name = null;
try
{
for (int i = leftoveritems; i >= 1; i--)
{
name = xlWorkbook.Names.Item(i);
name.Delete();
if (name != null)
{
Marshal.ReleaseComObject(name);
}
name = null;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (name != null)
{
Marshal.ReleaseComObject(name);
}
}
if (xlWorkbook != null)
{
xlWorkbook.Close(true);
Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
if (xlWorkbooks != null)
{
Marshal.ReleaseComObject(xlWorkbooks);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (ranges != null)
{
Marshal.ReleaseComObject(ranges);
}
if (xlWorkbook != null)
{
Marshal.ReleaseComObject(xlWorkbook);
}
if (xlWorkbooks != null)
{
Marshal.ReleaseComObject(xlWorkbooks);
}
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
}