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