|
Try this[^]
PLease inform if any errors
|
|
|
|
|
Thanks Tamer
i try your component, but in this line :
extendedDataTableObject.ToExcel(@"c:\test.xlsx");
i got this exception :
Exception Occured While Generating Excel Document
my another goal is to save my dataTable in excel 2003.
|
|
|
|
|
Hello there,
I wrote a tool for importing and exporting data into ExcelSheets (better said: workbooks).
Some relevant parts of my code are:
using Excel = Microsoft.Office.Interop.Excel;
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
excelApp.ScreenUpdating = false;
excelApp.DisplayAlerts = false;
then I open some workbooks, open some sheets, write some stuff into them and close all of them. I also dereference them if they are no more needed (mysheet = null).
But after finally shutting down the ExcelApp doing so:
excelApp.Quit();
excelApp = null;
... I have to wait quite a long time (about half a minute) until I see the EXCEL-thread diappear in my ProcessExplorer.
The problem is, that I can not open the just written xls-files if the application did not end, because it still has got its hands on the files.
So I have to know when this happens.
My questions are:
Why does it take so long?
When does the Excel-thread really end?
How do I end it correctly?
How can I provoke the thread to be ended?
Or is it just a matter of garbage collection? (a field I did not yet explore)
Thanks in advance,
Harry
no plan,
no signature
|
|
|
|
|
Put simply, there is no way to completely shut down Excel (close the task so that you don't see it in the task list anymore) when you want to. It's a COM<->gargabe collector issue.
In earlier versions of the framework, it could be done with a direct call. GC.Collect() after quitting and nulling, but starting from 2.0 or 3.0 (can't remember cleary) it's no use.
The bright side is if you open more than one Excel app, only one will stay in memory for a long time, while all others will close immediately.
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
Thanks a lot! 
But I've still got one question left. Is there any event I can handle when the Excel Application shut down completely. I mean: I can wait some seconds  but is there something I can respond to, because I want to know when I can open the xls-file?
Greets
Harry
no plan,
no signature
|
|
|
|
|
Nope, no event you can handle. You might get a list of the running processes and check when "excel" isn't there anymore, but nothing will let you handle properly the case where the user opens other instances of Excel.
As for opening the xls file, you will be able to do that safely once you call Close on it (on the corresponding Workbook object), so that won't be a problem. 
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
Moreno Airoldi wrote: As for opening the xls file, you will be able to do that safely once you call Close on it (on the corresponding Workbook object), so that won't be a problem. Smile
Unfortunately not!
I tried opening the xls file I just wrote after myWorkbook.Close(), after myWorkbook = null, and even after myFileInfo = null. But doing so, Excel AND my little application will end up in "not responding". Maybe I can collect some garbage in order to get rid of the unreferenced workbook. But - Hey! - I do this at the end, when I wrote all the excelfiles I have to and finally end the application.
And so I still think, the best point to let anyone open the new files is when I closed and cleaned all up.
Greets
Harry
no plan,
no signature
|
|
|
|
|
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
) 
