public override void sendVectorToSim <ValueType>(string path, ValueType[] value)
{
//Need to upgrade to 2D array for Excel
object[,] sendValueArray = (Object[, ]) new Object[1, value.Length];
for (int ii = 0; ii < value.Length; ++ii)
{
sendValueArray[0, ii] = (object)value[ii];
}
try
{
string worksheetName;
string cellName;
string[] addressSplit = path.Split(new char[] { pathSeperator }, 2);
if (addressSplit.Length != 2)
{
throw new System.IO.IOException(String.Format("Mal-formed Excel address string {0}", path));
}
worksheetName = addressSplit[0];
cellName = addressSplit[1];
Excel.Sheets xlWorksheets = (Excel.Sheets)o_xlWorkbook.Worksheets;
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorksheets[worksheetName];
Excel.Range xlRange = xlWorksheet.get_Range(cellName, resolveVectorPath(cellName, value.Length - 1));
xlRange.Value = sendValueArray;
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheets);
}
catch
{
throw new System.IO.IOException("Could not set " + path + " to " + Convert.ToString(value) + ".");
}
}
public override void recvVectorFromSim <ValueType>(string path, int[] indicies, ValueType[] value)
{
try
{
string worksheetName;
string cellName;
string[] addressSplit = path.Split(new char[] { pathSeperator }, 2);
if (addressSplit.Length != 2)
{
throw new System.IO.IOException(String.Format("Mal-formed Excel address string {0}", path));
}
worksheetName = addressSplit[0];
cellName = addressSplit[1];
Excel.Sheets xlWorksheets = (Excel.Sheets)o_xlWorkbook.Worksheets;
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorksheets[worksheetName];
Excel.Range xlRange = xlWorksheet.get_Range(cellName, resolveVectorPath(cellName, value.Length - 1)); //Grab the whole range
object[,] excelArray = (object[, ])xlRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
for (int ii = 0; ii < value.Length; ++ii)
{
value[ii] = (ValueType)excelArray[1, ii + 1];
}
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheets);
}
catch
{
throw new System.IO.IOException("Could not get value from " + path);
}
}
public override void sendValueToSim <ValueType>(string path, ValueType value)
{
try
{
string worksheetName;
string cellName;
string[] addressSplit = path.Split(new char[] { pathSeperator }, 2);
if (addressSplit.Length != 2)
{
throw new System.IO.IOException(String.Format("Mal-formed Excel address string {0}", path));
}
worksheetName = addressSplit[0];
cellName = addressSplit[1];
Excel.Sheets xlWorksheets = (Excel.Sheets)o_xlWorkbook.Worksheets;
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorksheets[worksheetName];
Excel.Range xlRange = xlWorksheet.get_Range(cellName);
xlRange.Value = value;
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheets);
}
catch (Exception)
{
throw new System.IO.IOException("Could not set " + path + " to " + Convert.ToString(value) + ".");
}
}
private static void ReleaseCom(object obj)
{
if (obj != null && Marshal.IsComObject(obj))
{
Marshal.ReleaseComObject(obj);
}
obj = null;
}
private void closeDocument()
{
Debug.WriteLine("o_xlWorkbook.closeDocument", GetType().Name);
o_xlWorkbook.Close(false, Type.Missing, Type.Missing);
Debug.WriteLine("Marshal.ReleaseComObject: o_xlWorkbook", GetType().Name);
Marshal.ReleaseComObject(o_xlWorkbook);
o_xlWorkbook = null;
}
private void openDocument()
{
Debug.WriteLine("openDocument", GetType().Name);
var fname = System.IO.Path.Combine(workingDir, simFile);
//Excel works better with an absolute path.
string speadsheet = System.IO.Path.GetFullPath(fname);
Excel.Workbooks workbooks = o_xlApp.Workbooks;
Debug.WriteLine("workbooks.Open", GetType().Name);
o_xlWorkbook = workbooks.Open(speadsheet, 0, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, 0, true, 1, 0);
Debug.WriteLine("Marshal.ReleaseComObject: workbooks", GetType().Name);
Marshal.ReleaseComObject(workbooks);
}
private object recvValueFromSimInternal(string path)
{
if (path == null || path.Contains("*")) //Paths that contain "*" are incomplete
{
return(null);
}
try
{
string worksheetName;
string cellName;
string[] addressSplit = path.Split(new char[] { pathSeperator }, 2);
Object retVal;
if (addressSplit.Length != 2)
{
return(null); //Incomplete path
}
worksheetName = addressSplit[0];
cellName = addressSplit[1];
Excel.Sheets xlWorksheets = (Excel.Sheets)o_xlWorkbook.Worksheets;
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorksheets[worksheetName];
Excel.Range xlRange = xlWorksheet.get_Range(cellName);
retVal = xlRange.Value;
if (retVal is decimal) //Sinter doesn't really support decimal type, make do with double
{
retVal = Convert.ToDouble(retVal);
}
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheets);
return(retVal);
}
catch
{
throw new System.IO.IOException("Could not get value from " + path);
}
}
public override void sendValueToSim <ValueType>(string path, int ii, ValueType value)
{
try
{
string worksheetName;
string cellName;
string[] addressSplit = path.Split(new char[] { pathSeperator }, 2);
if (addressSplit.Length != 2)
{
throw new System.IO.IOException(String.Format("Mal-formed Excel address string {0}", path));
}
worksheetName = addressSplit[0];
cellName = addressSplit[1];
//The address is the base of the vector, add ii to the int component to get the individual address.
string cellLetter = Regex.Match(cellName, "[a-zA-Z]+").ToString();
string cellNumber = Regex.Match(cellName, "[0-9]+").ToString();
int cellNumberInt = Convert.ToInt32(cellNumber);
cellNumberInt += ii;
string newCellName = string.Format("${0}${1}", cellLetter, cellNumberInt);
Excel.Sheets xlWorksheets = (Excel.Sheets)o_xlWorkbook.Worksheets;
Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorksheets[worksheetName];
Excel.Range xlRange = xlWorksheet.get_Range(newCellName);
xlRange.Value = value;
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlWorksheets);
}
catch (Exception)
{
throw new System.IO.IOException("Could not set " + path + " to " + Convert.ToString(value) + ".");
}
}
/// <summary>
/// Creating Hyper Links of every component in TOC sheet and adding corresponding description against them.
/// Also Adding Back To Index links and Source file Name texts in every sheets.
/// </summary>
/// <param name="PivotOutputReportFullPath"></param>
/// <param name="htHyperLinks"></param>
/// <param name="otherNodes"></param>
public static void Create_HyperLinks(string PivotOutputReportFullPath, List <Hashtable> htHyperLinks, List <XmlNode> otherNodes)
{
// Creates a new Excel Application
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkbook = null;
var workbooks = excelApp.Workbooks;
XmlNode tocNode = otherNodes.Find(item => item.Name == "TOC");
XmlNode sourceNode = otherNodes.Find(item => item.Name == "SourceFile");
XmlNode style = otherNodes.Find(item => item.Name == "Style");
Logger.LogInfoMessage(string.Format("[GeneratePivotReports][Create_HyperLinks] Processing Started to add Links in Table of Content Sheet and Back Links in all output sheets"), false);
try
{
excelWorkbook = workbooks.Open(PivotOutputReportFullPath);
}
catch
{
//Create a new workbook if the existing workbook failed to open.
excelWorkbook = excelApp.Workbooks.Add();
}
try
{
// The following gets the Worksheets collection
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
XmlNode tocHeading = tocNode.SelectSingleNode("TOCHeading");
XmlNode tocTitle = tocNode.SelectSingleNode("TOCTitle");
XmlNode tocDescription = tocNode.SelectSingleNode("TOCDescription");
XmlNode sourceHead = sourceNode.SelectSingleNode("SourceFileHeading");
XmlNode backToIndex = sourceNode.SelectSingleNode("BackToIndex");
XmlNode tocHeadStyle = style.SelectSingleNode("TOCStyle").SelectSingleNode("TOCHeading");
XmlNode tocTitleStyle = style.SelectSingleNode("TOCStyle").SelectSingleNode("TOCTitle");
XmlNode tocDescStyle = style.SelectSingleNode("TOCStyle").SelectSingleNode("TOCDescription");
XmlNode tocStyle = style.SelectSingleNode("TOCStyle").SelectSingleNode("Style");
XmlNode sourceHeadStyle = style.SelectSingleNode("SourceFileStyle").SelectSingleNode("SourceFileHeading");
XmlNode sourceFileNameStyle = style.SelectSingleNode("SourceFileStyle").SelectSingleNode("SourceFileName");
XmlNode backToIndexStyle = style.SelectSingleNode("SourceFileStyle").SelectSingleNode("BackToIndex");
string sheetName = "";
string sourceFileNameText = "";
// The following gets Sheet1 for editing
string currentSheet = tocNode.Name;
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
//Fixed Table Location
Excel.Range tRange = excelWorksheet.get_Range("A2", "A22");
//borders.Weight = 1d;
int rowNumber = 3;
foreach (var htRowIndex in htHyperLinks)
{
foreach (DictionaryEntry hyperlink in htRowIndex.Cast <DictionaryEntry>().OrderBy(item => item.Key).ToList())
{
if (!htRowIndex.Keys.Cast <String>().Contains("SummaryView"))
{
string[] keyValue = hyperlink.Key.ToString().Split('~');
sheetName = keyValue[0];
sourceFileNameText = keyValue[1];
//to put the header for the table of contents sheet
if (rowNumber == 3)
{
Excel.Range excelCellRowHeader = (Excel.Range)excelWorksheet.get_Range("A2", "B2");
excelCellRowHeader.Merge(Missing.Value);
excelCellRowHeader.Value = tocHeading.InnerText;
excelCellRowHeader.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
excelCellRowHeader.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", tocHeadStyle, style));
excelCellRowHeader.Font.Color = CommonHelper.GetColor(CheckAttributes("FontColor", tocHeadStyle, style));
Excel.Borders border = excelCellRowHeader.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
excelCellRowHeader.Columns.AutoFit();
excelCellRowHeader.Font.Bold = true;
excelCellRowHeader.Font.Underline = true;
excelCellRowHeader.Font.Name = CheckAttributes("FontFamily", tocHeadStyle, style);
rowNumber++;
}
if (rowNumber == 4)
{
var excelCellRowHeader2 = (Excel.Range)excelWorksheet.get_Range("A3");
excelCellRowHeader2.Value = tocTitle.InnerText;
excelCellRowHeader2.Font.Color = CommonHelper.GetColor(CheckAttributes("FontColor", tocTitleStyle, style));
Excel.Borders border2 = excelCellRowHeader2.Borders;
border2.LineStyle = Excel.XlLineStyle.xlContinuous;
excelCellRowHeader2.Columns.AutoFit();
excelCellRowHeader2.Font.Bold = true;
excelCellRowHeader2.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", tocTitleStyle, style));
excelCellRowHeader2.Font.Name = CheckAttributes("FontFamily", tocTitleStyle, style);
excelCellRowHeader2.Interior.Color = CommonHelper.GetColor(CheckAttributes("BgColor", tocTitleStyle, style));
Excel.Range excelCellRowHeader1 = (Excel.Range)excelWorksheet.get_Range("B3");
excelCellRowHeader1.Value = tocDescription.InnerText;
excelCellRowHeader1.ColumnWidth = Convert.ToDouble(CheckAttributes("ColumnWidth", tocDescStyle, style));
excelCellRowHeader1.Font.Color = CommonHelper.GetColor(CheckAttributes("FontColor", tocDescStyle, style));
Excel.Borders border1 = excelCellRowHeader1.Borders;
border1.LineStyle = Excel.XlLineStyle.xlContinuous;
excelCellRowHeader1.Font.Bold = true;
excelCellRowHeader1.Interior.Color = CommonHelper.GetColor(CheckAttributes("BgColor", tocDescStyle, style));
excelCellRowHeader1.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", tocDescStyle, style));
excelCellRowHeader1.Font.Name = CheckAttributes("FontFamily", tocDescStyle, style);
rowNumber++;
}
}
else
{
sheetName = hyperlink.Key.ToString();
rowNumber = 4;
}
// The following gets cell A1 for editing
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A" + rowNumber);
excelWorksheet.Activate();
//Add the Text for hyper Link in Table of contents Sheet
excelCell.Value = sheetName;
//var s = tocStyle.Attributes["BgColor"];
//excelCell.Font.Color = CommonHelper.GetColor((tocStyle.Attributes["BgColor"].InnerText == null) ? bgColor : tocStyle.Attributes["BgColor"].InnerText);
Excel.Borders borders = excelCell.Borders;
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
excelCell.Font.Bold = true;
excelWorksheet.Hyperlinks.Add(excelCell, "#" + sheetName + "!A1", Type.Missing, Type.Missing, sheetName);
excelWorksheet.Application.Range["A" + rowNumber].Select();
//excelApp.Selection.Font;
excelWorksheet.Application.Selection.Font.Name = CheckAttributes("FontFamily", tocStyle, style);
excelWorksheet.Application.Selection.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", tocStyle, style));
Excel.Range excelCell3 = (Excel.Range)excelWorksheet.get_Range("B" + rowNumber);
//Add the Text for hyper Link in Table of contents Sheet
excelCell3.Value = hyperlink.Value.ToString().Trim();
excelCell3.Font.Color = CommonHelper.GetColor(CheckAttributes("FontColor", tocStyle, style));
Excel.Borders borders2 = excelCell3.Borders;
borders2.LineStyle = Excel.XlLineStyle.xlContinuous;
excelCell3.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", tocStyle, style));
excelCell3.WrapText = true;
Excel.Worksheet excelWorksheet2 = (Excel.Worksheet)excelSheets.get_Item(Convert.ToString(sheetName));
excelWorksheet2.Activate();
Range Line = (Range)excelWorksheet2.Rows[1];
Line.Insert();
Excel.Range excelCell2 = (Excel.Range)excelWorksheet2.get_Range(Convert.ToString("A1"));
excelCell2.Value = backToIndex.InnerText;
excelWorksheet2.Hyperlinks.Add(excelCell2, "#" + currentSheet + "!A1", Type.Missing, Type.Missing, Type.Missing);
excelWorksheet2.Application.Range["A1"].Select();
//excelApp.Selection.Font;
excelWorksheet2.Application.Selection.Font.Name = CheckAttributes("FontFamily", backToIndexStyle, style);
excelWorksheet2.Application.Selection.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", backToIndexStyle, style));
if (!sheetName.Equals("SummaryView"))
{
//Get the range of sheet to fill count
Excel.Range sourceFileTitle = excelWorksheet2.get_Range("B1", "B1");
sourceFileTitle.Value = sourceHead.InnerText.Trim();
sourceFileTitle.Font.Color = CommonHelper.GetColor(CheckAttributes("FontColor", sourceHeadStyle, style));
sourceFileTitle.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", sourceHeadStyle, style));
sourceFileTitle.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
sourceFileTitle.Font.Bold = true;
sourceFileTitle.Columns.AutoFit();
//Get the range of sheet to fill count
Excel.Range sourceFileName = excelWorksheet2.get_Range("C1", "F1");
sourceFileName.Merge();
sourceFileName.Value = sourceFileNameText;
sourceFileName.Font.Color = CommonHelper.GetColor(CheckAttributes("FontColor", sourceFileNameStyle, style));
sourceFileName.Font.Size = Convert.ToDouble(CheckAttributes("FontSize", sourceFileNameStyle, style));
sourceFileName.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
sourceFileName.Columns.AutoFit();
}
rowNumber++;
}
}
tRange.Columns.AutoFit();
// Close the excel workbook
excelWorkbook.Close(true, Type.Missing, Type.Missing);
workbooks.Close();
excelApp.Application.Quit();
excelApp.Quit();
Marshal.ReleaseComObject(excelSheets);
Marshal.ReleaseComObject(excelWorkbook);
Marshal.ReleaseComObject(workbooks);
Logger.LogInfoMessage(string.Format("[GeneratePivotReports][Create_HyperLinks] Process Completed to add Links in Table of Content Sheet and Back Links in all output sheets"), true);
}
catch (Exception ex)
{
if (excelWorkbook != null)
{
excelWorkbook.Save();
excelWorkbook.Close();
}
if (excelApp != null)
{
excelApp.Quit();
excelApp.Application.Quit();
}
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excelWorkbook);
Logger.LogErrorMessage(string.Format("[GeneratePivotReports][Create_HyperLinks][Exception]: " + ex.Message + "\n" + ex.StackTrace.ToString()), true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(),
"[GeneratePivotReports]: Create_HyperLinks", ex.GetType().ToString(), Constants.NotApplicable);
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(excelApp);
}
}
/// <summary>
/// Dispose COM objects
/// </summary>
private void DisposeCOMObjects()
{
FileWorker fileWorker = new FileWorker();
try
{
xlWorkbook.Close(false);
}
catch (Exception ex)
{
errorsMessages.Add(ex.Message);
}
try
{
xlApp.Quit();
}
catch (Exception ex)
{
errorsMessages.Add(ex.Message);
if (ex.Message == "Exception from HRESULT: 0x800AC472")
{
GetWindowThreadProcessId((IntPtr)hWnd, out pid);
if (hWnd != 0)
{
try
{
Process[] procs = Process.GetProcessesByName("EXCEL");
if (pid != 0)
{
foreach (Process p in procs)
{
if (p.Id == pid)
{
fileWorker.Logs(p.Id.ToString());
p.Kill();
}
}
}
}
catch (Exception ex1)
{
errorsMessages.Add(ex1.Message);
}
}
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
try
{
Marshal.ReleaseComObject(xlWorkbook);
}
catch (Exception ex)
{
errorsMessages.Add(ex.Message);
}
try
{
Marshal.ReleaseComObject(xlApp);
}
catch (Exception ex)
{
errorsMessages.Add(ex.Message);
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}