|
I am trying to use Process.Start to launch Excel. If I immediately grab the process returned by the Start method, any addins or extensions to Excel (xlls or xlam) will not work. If I wait until Excel finishes opening and loading all the addins, then grab the process, it works. Need them to work for this project.
I mashed up a class from code I found around to either grab a reference to an existing instance of Excel or to start a new one if it isn't running. Unfortunately, it doesn't work. If the Process.Start method is called, Excel starts loading. The WaitForInputIdle method waits for the message loop to start. But when the message loop starts, Excel is still loading things and has not created any child windows. So the class tries to enumerate the child windows but there are none. And I discovered that once you call EnumChildWindows() it "freezes" things in that it will not refer to a child window created subsequently even if you call it again after the child window is created. So loops to wait are out.
One solution is to recursively create new instances of the class to get around the EnumChildWindows problem, but I end up with 1000's of these instances.
As a kludge, I have a long Thread.Sleep in the code to wait until everything is open. It works correctly but I would like to know if there is a better way to determine if a process has finished opening, especially given the bug/feature in EnumChildWindows.
public class ExcelInteropService
{
private const string EXCEL_CLASS_NAME = "EXCEL7";
private const uint DW_OBJECTID = 0xFFFFFFF0;
private static Guid rrid = new Guid("{00020400-0000-0000-C000-000000000046}");
public delegate bool EnumChildCallback(int hwnd, ref int lParam);
[DllImport("Oleacc.dll")]
public static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, byte[] riid, ref Microsoft.Office.Interop.Excel.Window ptr);
[DllImport("User32.dll")]
public static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, ref int lParam);
[DllImport("User32.dll")]
public static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);
public static Microsoft.Office.Interop.Excel.Application GetExcelInterop(int? processId = null)
{
var p = processId.HasValue ? Process.GetProcessById(processId.Value) : Process.Start("excel.exe");
p.WaitForInputIdle();
System.Threading.Thread.Sleep(60000);
Debug.Assert(p != null, "p != null");
try {
return new ExcelInteropService().SearchExcelInterop(p);
}
catch (Exception) {
Debug.Assert(p != null, "p != null");
return GetExcelInterop(p.Id);
}
}
private bool EnumChildFunc(int hwndChild, ref int lParam)
{
var buf = new StringBuilder(128);
GetClassName(hwndChild, buf, 128);
if (buf.ToString() == EXCEL_CLASS_NAME) { lParam = hwndChild; return false; }
return true;
}
private Microsoft.Office.Interop.Excel.Application SearchExcelInterop(Process p)
{
bool timeout = false;
DateTime start = new DateTime();
TimeSpan span = new TimeSpan();
TimeSpan d = new TimeSpan(0, 1, 0);
Microsoft.Office.Interop.Excel.Window ptr = null;
int hwnd = 0;
int hWndParent = 0;
int hr = -1;
Debug.Assert(p != null, "p != null");
try {
start = DateTime.Now;
do {
do {
i++;
hWndParent = (int)p.MainWindowHandle;
if (hWndParent == 0) {
Debug.WriteLine("MainWindowNotFound");
break;
}
EnumChildWindows(hWndParent, EnumChildFunc, ref hwnd);
if (hwnd == 0) {
Debug.WriteLine("ChildWindowNotFound");
break;
}
hr = AccessibleObjectFromWindow(hwnd, DW_OBJECTID, rrid.ToByteArray(), ref ptr);
if (hr < 0) {
Debug.WriteLine("AccessibleObjectNotFound");
break;
}
if (ptr != null)
return ptr.Application;
} while (ptr == null);
span = DateTime.Now - start;
if (span > d)
timeout = true;
} while (timeout == false);
}
catch (Exception ex) {
Debug.Write("Search Exception - ");
Debug.WriteLine(ex.Message);
return null;
}
try {
p.CloseMainWindow();
}
catch (Exception ex) {
Debug.Write("CloseWinMain Exception = ");
Debug.WriteLine(ex.Message);
}
return null;
}
}
Mark Jackson
|
|
|
|
|
mjackson11 wrote: grab a reference to an existing instance of Excel or to start a new one if it isn't running
I haven't done this for a while, but I think you can replace all of that code with:
using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
public static class ExcelInteropService
{
public static Excel.Application FindOrStartExcel()
{
try
{
return (Excel.Application)Marshal.GetActiveObject("Excel.Application");
}
catch (COMException)
{
}
return new Excel.Application();
}
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Unfortunately, the new Excel.Application call is buggy. The Excel instance that is launched will show the addins as being loaded but the addins cannot be used from a vba macro.
|
|
|
|
|
According to this SO question[^], you need to loop through the installed add-ins, set the Installed property to false, and then back to true.
Alternatively, this blog post[^] suggests that you should use the Application.AddIns.Add method to load the add-ins, and explains why they're not loaded when you use new Excel.Application().
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
OMG, I spent two days on Google looking for this. Thank you thank you thank you. It was driving me nuts.
Mark Jackson
|
|
|
|
|
What do you actually want to achieve? To me, your approach looks line an "XY problem": you do not describe the problem proper, but your idea of a solution which failed somewhere, and now you ask us how to procede on your wrong way. Let's get a few steps back and find the correct way to cope with it.
|
|
|
|
|
Hi guys.
I'm working on a creating an application that will read from and write to Excel spreadsheets here at work (personally I'd prefer that we migrate to using databases but I ain't the boss... yet ). Here's the problem, when reading a particular value from a cell in the spreadsheet, it should be empty but it seems like when I get the range from the spreadsheet programmaticlly, it creates and assigns a value even to the empty cells in the range. Am I perhaps calling the range the wrong way? If so, how can I do this the right so that the values in the range (on the program match the values on the actual spreadsheet?
Here's the code of how I got the ranges from the spreadsheet (I commented as much as possible):
Excel.Workbook source;
Excel.Workbook file;
Excel.Workbook submit;
Excel.Range currentRange;
string fileIMDR = ConfigurationManager.AppSettings["IMDR"].ToString();
string controlSource = ConfigurationManager.AppSettings["Source"].ToString();
string submitted = ConfigurationManager.AppSettings["Submitted"].ToString();
string imdrNo = "";
application = new Excel.Application();
//Get the control source and get the next IMDRNumber
source = application.Workbooks.Open(controlSource, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workSheet = source.Sheets["Sheet1"];
workSheet = source.ActiveSheet;
//Selecting the range
currentRange = workSheet.Range["A:B"];
|
|
|
|
|
Hi guys. I was reading from the wrong file - I was accessing the actual file that the company uses, not the one I was I am using for testing. Sorry.
|
|
|
|
|
Hi,
I have upgrade my code from 3.5 to 4.5 framework.
In 3.5 framework I am able to connect to web servcies from code but in 4.5 I am unable to connect to web services from code but I can access this service from browser.
I am getting below mentioned error:-
- InnerException {"No connection could be made because the target machine actively refused it "} System.Exception {System.Net.Sockets.SocketException}
+ [System.Net.Sockets.SocketException] {"No connection could be made because the target machine actively refused it "} System.Net.Sockets.SocketException
+ Data {System.Collections.ListDictionaryInternal} System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
HelpLink null string
HResult -2147467259 int
+ InnerException null System.Exception
Message "No connection could be made because the target machine actively refused it " string
Source "System" string
StackTrace " at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)\r\n at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)" string
+ TargetSite {Void DoConnect(System.Net.EndPoint, System.Net.SocketAddress)} System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
|
|
|
|
|
Try DELETING the web service reference and then re-adding it again (in the upgraded project).
Changing the .NET version of an existing project with an existing web service reference does not automatically "upgrade" the web reference.
modified 28-Aug-14 11:40am.
|
|
|
|
|
hi to all
i want wrote a one line Code Similare This
myDataTable.Select("Guid IN ('8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e')")
but i face With this Problem
'myDataTable.Select("Guid IN ('8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e')")' threw an exception of type 'System.Data.EvaluateException' System.Data.DataRow[] {System.Data.EvaluateException}
this is a one line Code That is ok, but i dont want use of this Method
myDataTable.Select("Guid = '8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e'")
any way is Exist that Use Guid in Linq FilterExpresion With 'IN'????
thank for Any Help
|
|
|
|
|
Is the Guid in the database as a string representation or a byte [] ?
|
|
|
|
|
Guid save in DataBase AS UniqueIdentifier
|
|
|
|
|
That's most probably the culprit. This "UniqueIdentifier" is not a string and apparently your database engine cannot convert it implicitly. Try to follow Richard Deeming's advice. Alternatively you could use a parametrized query instead and try to pass the guid as a byte[] (blob) instead of a string.
Hope this helps.
|
|
|
|
|
Try converting the column to a string in the filter expression:
myDataTable.Select("Convert(Guid, 'System.String') IN ('8556c1fd-d4a1-4c1c-b2af-c1fa9403ce4e')")
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks Mr Deeming,Your Answer Save My life 
|
|
|
|
|
I've implemented a progress bar to my application but it makes the file copy process painfully slow whereas without the progress bar the copy is very fast. I know without the progress bar it only takes a few seconds to copy a 10 megabyte file but with the progress bar it takes more than a minute. What am I doing wrong?
namespace Compression_Util
{
class DropBox
{
private object sender;
private EventArgs e;
private DragEventArgs de;
private Design zip;
private string hover;
private string[] files;
private string filetype;
public DropBox(object sender, DragEventArgs de, Design zip, string hover)
{
this.sender = sender;
this.de = de;
this.zip = zip;
this.hover = hover;
this.Hover();
}
public DropBox(object sender, EventArgs e, Design zip, string hover)
{
this.sender = sender;
this.e = e;
this.zip = zip;
this.hover = hover;
this.Hover();
}
private void InitializeProgressBar(int fileSize)
{
zip.DropProgress.Visible = true;
zip.DropProgress.Minimum = sizeof(Byte);
zip.DropProgress.Step = sizeof(Byte);
zip.DropProgress.Maximum = fileSize;
}
private void DeInitializeProgressBar()
{
zip.DropProgress.Invalidate();
zip.DropProgress.Visible = false;
}
private void IncrementProgressBar()
{
zip.DropProgress.PerformStep();
}
private void CreateFile(string read, string write)
{
try
{
FileStream fileStreamReader = new FileStream(read, FileMode.Open, FileAccess.Read);
FileStream fileStreamWriter = new FileStream(write, FileMode.Create, FileAccess.ReadWrite);
BinaryReader binaryReader = new BinaryReader(fileStreamReader);
BinaryWriter binaryWriter = new BinaryWriter(fileStreamWriter);
int position = 0;
int length = (int)binaryReader.BaseStream.Length;
InitializeProgressBar(length);
while (position < length)
{
Byte line = binaryReader.ReadByte();
binaryWriter.Write(line);
position += sizeof(Byte);
IncrementProgressBar();
}
DeInitializeProgressBar();
binaryWriter.Close();
binaryReader.Close();
fileStreamWriter.Close();
fileStreamReader.Close();
}
catch (FileNotFoundException FileEx)
{
MessageBox.Show(FileEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
}
private string CreateFileName(string file)
{
int i = 0;
string deleteText = "";
string newFileName = "";
while ((i = file.IndexOf('.', i)) != -1)
{
deleteText = file.Substring(i);
i++;
}
this.filetype = deleteText;
newFileName = file.Replace(deleteText, ".xxx");
return newFileName;
}
private void Hover()
{
switch (this.hover)
{
case "Enter":
{
zip.DragLabel.Visible = false;
zip.DropLabel.Visible = true;
zip.BackColor = System.Drawing.Color.DarkGray;
zip.DropBox.BackColor = System.Drawing.Color.Thistle;
zip.DropBox.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
if (de.Data.GetDataPresent(DataFormats.FileDrop, false) == true)
{
de.Effect = DragDropEffects.All;
}
break;
}
case "Leave":
{
zip.DragLabel.Visible = true;
zip.DropLabel.Visible = false;
zip.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BorderStyle = System.Windows.Forms.BorderStyle.None;
break;
}
case "Drop":
{
zip.DragLabel.Visible = true;
zip.DropLabel.Visible = false;
zip.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BackColor = System.Drawing.Color.WhiteSmoke;
zip.DropBox.BorderStyle = System.Windows.Forms.BorderStyle.None;
this.Drop();
break;
}
}
}
private void Drop()
{
this.files = (string[])this.de.Data.GetData(DataFormats.FileDrop);
foreach (string file in files)
{
string newFileName = this.CreateFileName(file);
if (!File.Exists(newFileName))
{
this.CreateFile(file, newFileName);
}
else
{
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
string message = "File already exists, Overwrite?";
string caption = "";
result = MessageBox.Show(zip, message, caption, buttons);
switch (result)
{
case DialogResult.Yes:
{
this.CreateFile(file, newFileName);
break;
}
case DialogResult.No:
{
MessageBox.Show("Operation has been cancelled!");
break;
}
default: break;
}
}
}
}
}
}
|
|
|
|
|
kiasta wrote: What am I doing wrong?
You're performing UI operations during the copy. What you should instead do is use a BackgroundWorker[^] to perform the file copy and publish UI updates.
/ravi
|
|
|
|
|
Thanks for the reply! I will definitely look into that.
|
|
|
|
|
Not only are you copying the file in the UI, you are updating the progress bar every IO. I always calculate percent complete using the following formula: (number of bytes copied * 100) / size of file in bytes
All numbers used in the calculation are long but the result is converted to int. Update the progress bar only when the percent changes.
|
|
|
|
|
Hmm OK that makes some sense, but why would copying it inside the UI make any difference? Without the progress bar the files copy very fast, almost instantly. I don't really understand, I guess. Is the progress bar really that resource intensive?
|
|
|
|
|
As you said, the progress bar slows down the copy. The progress bar is CPU intensive while the file copy is most likely IO bound. The progress bar slows the copy when the two operations share the same thread. Copying the file in a separate thread will allow more efficient use of multiple processors; both operations run almost independently.
Also, try copying a file several gigabytes in size and watch your UI lock up.
I tend to reuse code a lot. Code a process right the first time and use it many times.
|
|
|
|
|
Ah OK I see now what you mean, thanks for the clarification!
|
|
|
|
|
Buffer it: allocate a buffer of say 1/2 meg and fill it. Write it, update progress. Fill it, write it, update progress.
But I do it in a background worker like this:
private void MoveFile(string src, string dst, BackgroundWorker worker = null, ProgressReport prMain = null)
{
if (src != dst)
{
int iSrc = src.IndexOf(':');
int iDst = dst.IndexOf(':');
FileInfo fiSrc = new FileInfo(src);
if (fiSrc.Length < blockSize || (iSrc > 0 && iDst > 0 && iSrc == iDst && src.Substring(0, iSrc) == dst.Substring(0, iDst)))
{
if (doCopyOnly)
{
File.Copy(src, dst);
}
else
{
File.Move(src, dst);
}
}
else
{
using (Stream sr = new FileStream(src, FileMode.Open))
{
using (Stream sw = new FileStream(dst, FileMode.Create))
{
long total = sr.Length;
long bytes = 0;
long cnt = total;
int progress = 0;
while (cnt > 0)
{
int n = sr.Read(transfer, 0, blockSize);
sw.Write(transfer, 0, n);
bytes += n;
cnt -= n;
int percent = (int)((bytes * 100) / total);
if (progress != percent)
{
progress = percent;
if (worker != null && prMain != null)
{
ProgressReport pr = new ProgressReport
{
FilesCount = prMain.FilesCount,
FileNumber = prMain.FileNumber,
Filename = prMain.Filename,
Action = prMain.Action,
FilePercentage = percent
};
worker.ReportProgress(-prMain.FileNumber, pr);
}
}
}
}
}
FileInfo fiDst = new FileInfo(dst);
fiDst.Attributes = fiSrc.Attributes;
fiDst.CreationTime = fiSrc.CreationTime;
fiDst.CreationTimeUtc = fiSrc.CreationTimeUtc;
fiDst.IsReadOnly = fiSrc.IsReadOnly;
fiDst.LastAccessTime = fiSrc.LastAccessTime;
fiDst.LastAccessTimeUtc = fiSrc.LastAccessTimeUtc;
fiDst.LastWriteTime = fiSrc.LastWriteTime;
fiDst.LastWriteTimeUtc = fiSrc.LastWriteTimeUtc;
if (!doCopyOnly)
{
File.Delete(src);
}
}
}
}
That's a general purpose method which does a lot more than you probably want to, but it moves a couple of gig files pretty quickly and reports progress.
You looking for sympathy?
You'll find it in the dictionary, between sympathomimetic and sympatric
(Page 1788, if it helps)
|
|
|
|
|
The main objective is to compress the files into a container, but I want to at least be able to copy files with some type of progress notification at the very least first before I start making an algorithm for compression. Thanks for the source I'll study it and try to make something work.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
