|
Then there must be something else in your code which prevents the workbook to be released.
Consider the following example:
Microsoft.Office.Interop.Excel.Application myApp = null;
Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
try
{
myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
myApp.AskToUpdateLinks = false;
myApp.DisplayAlerts = false;
myWorkbook = myApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
myWorkbook.SaveAs("D:\\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
System.Type.Missing, System.Type.Missing, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (myWorkbook != null)
{
myWorkbook.Close(false, "", null);
myWorkbook = null;
}
if (myApp != null)
{
myApp.Quit();
myApp = null;
}
}
System.Diagnostics.Process.Start("D:\\test.xls");
The created workbook is successfully opened right after it's Saved and Closed (please note that Process.Start will leave you with yet another open Excel process in the task list).
I've been using this kind of interop a few times, and I assure you it works fine. I'll be more than glad to try and help you more if you want. 
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
|
Yes you are right! Calling Process.Start() before Application.Quit() won't work properly. I made some tests and I confirm it.
The problem is in how Excel optimizes opening documents. Consider the following example (derived from my previous):
Microsoft.Office.Interop.Excel.Application myApp = null;
Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
try
{
try
{
myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
myApp.Visible = true;
myApp.AskToUpdateLinks = false;
myApp.DisplayAlerts = false;
myWorkbook = myApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
myWorkbook.SaveAs("D:\\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
System.Type.Missing, System.Type.Missing, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
}
finally
{
if (myWorkbook != null)
{
myWorkbook.Close(false, "", null);
myWorkbook = null;
}
}
System.Threading.Thread.Sleep(3000);
System.Diagnostics.Process.Start("D:\\test.xls");
System.Threading.Thread.Sleep(3000);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (myApp != null)
{
myApp.Quit();
myApp = null;
}
}
You will clearly see that since one Excel task is still active (the one we instanced for opening and saving the workbook), the call to Process.Start() will cause it to be the container for the re-opening of the document. Since we call Application.Quit() right after that, we close the workbook! This "task optimization" is an Excel internal, so I doubt there's any way to bypass it.
All the other differences between your code and my example don't seem to count: it's all basically the same.
I think you'll just have to call Application.Quit() before opening the file. There's probably no way to avoid that (unless you use another application - not Excel - to show the file...).
If you are working on multiple documents, and you want to open each saved file while processing the next, you may consider opening a different Excel.Application for each single one. There will sure be an overhead, but you can make some tests and see if it's acceptable (it probably is).
This (brutal) example works fine:
for (int i = 1; i < 6; i++)
{
Microsoft.Office.Interop.Excel.Application myApp = null;
Microsoft.Office.Interop.Excel.Workbook myWorkbook = null;
try
{
try
{
myApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
myApp.Visible = true;
myApp.AskToUpdateLinks = false;
myApp.DisplayAlerts = false;
myWorkbook = myApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
myWorkbook.SaveAs("D:\\test" + i.ToString() + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,
System.Type.Missing, System.Type.Missing, false, false,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
}
finally
{
if (myWorkbook != null)
{
myWorkbook.Close(false, "", null);
myWorkbook = null;
}
if (myApp != null)
{
myApp.Quit();
myApp = null;
}
}
System.Diagnostics.Process.Start("D:\\test" + i.ToString() + ".xls");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Good luck, let me know how it goes!
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
|
Have a nice one, man! 
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
Hi,
I haven't done any of this, but it does not look OK you would have to wait for Excel to quit. Here are two suggestions:
1) look for a way to close your excel document, without closing excel
2) what happens if you tell your excelApp to save the current document and start a new document, doesn't that relinquish the old one?
BTW: if you modified the excel document, chances are background processes such as Office Indexers and Antivirus Software are reading your document and preventing you from opening it with write access for a while.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Hello Luc,
as I supposed it was just a matter of garbage collection.
I inserted the line
GC.Collect() and now it works fine.
Answering your suggestions:
1)I tell the workbook to SaveAs(...) then I Quit() the application. I want Excel to Quit when I finished writing the xls-documents, because someone (e.g. myself) might want to open the documents. And this is not possible as long as another task locks the documents. And Excel locks the documents as long as it is not shut down.
2)I'm starting one single Excel Application, with this I'm writing several workbooks (xls-files). When I wrote the file, I save and close it. Then I create the next file.
Actually, I copy an original to temp.xls (using only IO), then I write several stuff into this temp.xls, at the end I copy temp.xls to the final thisIsHowIWantToCallIt.xls (using IO)
An Excel Application may have more than one workbooks. the App.'s got a collection called workbooks. So opening a new would probably not abandon the old one.
3)There is no problem with the rights afaik. I create all the files by myself. They are just copies from the master. I'm using some makros in this Excel-sheets. If they were not allowed my programm would tell you to stop working and try playing solitaire  (just kidding)
Greets
Harry
no plan,
no signature
|
|
|
|
|
Hi Harry,
Harry666 wrote: I inserted the line GC.Collect() and now it works fin
That cannot possibly be the right solution. GC.Collect() should not be needed at any time, calling it most often even is a bad idea.
It does tell me some object is no longer in use (e.g. its reference got set to null) but was still keeping resources because it wasn't closed or disposed properly. I'm not fimiliar with Office Interop, my guess would be saving a workbook is not enough, it needs to be closed as well (preventing more edits and saves, and discarding data and resources).
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Hello Luc,
it's understood that I do not only save the workbook, I also close it and finally set the reference to null. (Sorry, if I forgot to tell)
As I can see, the task is loitering around and I have to wait for its shutdown.
When I call GC.Collect() I do NOT have to wait.
The last thing I do, before the user gets back the control, is shutting down Excel. So this has to be finished before the user gets the control. I can not wait at this point. Or I have to know when Excel finished (some event to handle would be nice).
Do you know alternatives to GC.Collect() ?
Harry
no plan,
no signature
|
|
|
|
|
It's a known problem with Office interop, and one Excel task will remain active after you correctly dispose all references and stuff, on this he's right.
I know GC.Collect() was used as a patch solution (and of course I strongly disagree with that) and was working up to .NET 1.1 or 2.0 (can't remember clearly).
The real point is, he should be able to open the workbook he closed anyway. I've been using this kind of interop a few times and it works fine.
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
Can i set multiple datatable in ActiveX crystal Report like in using ReportDocument.
How to set multiple data Sources in ActiveX crystal Report. Using Report this can be done as following. It is ok but got problem in Nepali font display.
ReportDocument rptdmt=new ReportDucument();
rptdmt.Load("C:\\aa.rpt");
DataTable dt1=GetGeneralData();
DataTable dt2=GetSpecificData();
rptdmt.SubReports["rptGeneralSub"].SetDataSource(dt1);
rptdmt.SubReports["rptSpecific"].SetDataSource(dt2);
crViewer.ReportSource=rptdmt;
Can this type of work be done in Crystal Report using ActiveX component. How ..? Please tell me.
|
|
|
|
|
Hello
I have a string (string j;) inside the Program.cs file and when my application runs and Form1 is visible, I want to get the value of j from Program.cs file on the Form1_Shown event. Does anybody know how I would go about retrieving this value? Any help would be appreciated
Thank you.
Regards,
Jason Pezzimenti.
Knock knock.....
|
|
|
|
|
you can add a string parameter to the constructor of the form, and send it via that.
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
|
|
|
|
|
I’ll soon enter a project where I’m going to create an “Mail Report Generator”. In our SQL Server database we have a lot of information connected to users to the system.
Now I’m looking for different techniques to create these reports. The demands are that the email that is going to be sent should have an attached PDF-file which should contain tables, charts, and different types of diagram.
I’ve been thinking of Microsoft Reporting Services as a solution for generating PDFs. Can that be a good way of doing it?
All suggestions are welcomed!!
_____________________________
...and justice for all
|
|
|
|
|
Reporting Services would be really good..As far as I know, it's not only PDF but it also supports other formats like Excel, Images etc. I used it long back to create reports which users used to save their reports in different. If you are interested for sending automated mails, then you should definitely look into the APIs availble for generating PDFs.
Anyway,I can suggest you to see few more availble reporting tools also for your comparison. Checkout once DevExpreshttp://www.devexpress.com/Products/NET/Reporting/[^]s Xtrareports.
Let me know if you need any further details.
|
|
|
|
|
Hi All
Does anyone have an answer to this? im using C# Visual studio 2008?
So far I was thinking just loop through the first root folder, compare the length of the first file with the rest, and if the first files length is smaller then swap the bigger one with it, so at the end all the biggest files will be at the top..
DirectoryInfo dir = new DirectoryInfo(@"C:\");
foreach (FileInfo file in dir.GetFiles())
{
Console.WriteLine(file.Name);
foreach (FileInfo file2 in dir.GetFiles())
{
if (file.Length < file2.Length)
file2.MoveTo(file.ToString());
}
}
}
|
|
|
|
|
You can use LINQ to do this easily.
http://dotnetperls.com/sort-file-size
Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen
|
|
|
|
|
Luke Perrin wrote: file2.MoveTo(file.ToString());
do you really want to move files around?????
and do you think you can get a folder size without summing file sizes?
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
You can use the code below, but it may need some performance improvements
List<FileInfo> fi = new List<FileInfo>();
ListBox l = new ListBox();
private void Form1_Load(object sender, EventArgs e)
{
}
private void GetSubFoldersAndFiles(string path)
{
foreach (string d in Directory.GetDirectories(path))
{
try
{
GetSubFoldersAndFiles(d);
}
catch (Exception)
{
}
}
foreach (string f in Directory.GetFiles(path))
{
fi.Add(new FileInfo(f));
}
}
private void Form1_Shown(object sender, EventArgs e)
{
l.Dock = DockStyle.Fill;
this.Controls.Add(l);
foreach (DriveInfo di in DriveInfo.GetDrives())
{
if (di.IsReady)
{
GetSubFoldersAndFiles(di.RootDirectory.FullName);
}
}
var a = (from s in fi orderby s.Length descending select s);
l.DataSource = a.ToList();
}
|
|
|
|
|
Hello,
How we can convert a PCL to PDF using C#, please help me to esolve this problem, thank you verry mutch.
|
|
|
|
|
C# does not have any existing API for this.
You may look at google [^]and try out of any thrid party or open source library for this.
Manas Bhardwaj
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
i want to sync my calendar application with microsoft outlook calendar. I want to share this data through XML. i want to implement 2 way sync. Does any one have any idea of approach which i can follow ??? I am facing problem while comparing my application data with outlook data while merging. plz help
|
|
|
|
|
I have been working on this issue for a few days now and I am growing tired and desperate 
I have made this application which sends a screenshot (Image type) to another PC. All works well on most PCs except some very few of them. Those that do not work complain of a Binary stream '0' does not contain a valid BinaryHeader.
The code is exactly the same on the working / non-working PCs. I have also attempted to run the program locally on the PCs not working, and the exact problem occurs.
The Algorithm is as follows:
- Screenshot is taken (returned as Image)
- Size of the Image is sent (to prepare a byte[] for the receiving end)
- Image is converted into a MemoryStream from BinaryFormatter.Serialize
- MemoryStream.ToArray() gets byte[] which is written on a NetworkStream
- Receiving end receives the length of the object and prepares a byte[] with that length
- Receiving end reads the data on a byte[]
- Byte[] is Deserialized to object <- Exception occurs 
I have checked the first 50 bytes being sent and received and they are exactly identical!! 
It is true that the first byte is a 0 but this works with no problems at all on other systems
Why is this happening? How can I avoid this? Maybe using UnsafeDeserialize or something completely different?! 
Any help is sooo greatly appreciated!!
|
|
|
|
|
Hi,
I don't know what problem you are having. One possibility is the (de)serializer is having an out-of-memory problem due to fragmentation in the large-object-heap.
Why do you need serialization at all? Couldn't you use Bitmap.Save(NetworkStream) and new Bitmap(NetworkStream)? I don't like your scheme requiring a potentially large array that much.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
i m doin device application
the problem is wih socket
my code was
clientsocket = new TcpClient(ip,2000);
clientsocket.ReceiveTimeout=10000;
this code gives an exception "option is not supported or something like that"
but this workd fine in windows application
so i wrote
clientsocket = new TcpClient(ip,2000);
this line raises exception
i m receiving exception
"An operation was attempted on something that is not a socket" after sometime or after a sequence of clicks
i think the problem is with socket timeout
if so how can i increase the socket time out
can any one help
This code was posted by me...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
) 
