Hi i am doing project in asp.net using vb i want to show the output in excel format. after closing the excel application also i can see the EXCEL.EXE in my taskmanager means in PRocesses tab i can see the EXCEL.EXE. how to close this EXCEL>EXE from memory.If
i run my code 10 times 10 EXCEL.EXE are shoing in taskmanager. my code like this Dim excel As New Excel.Application excel.Application.Workbooks.Add(True) lcdgrd.Visible = True Dim table1 As System.Data.DataTable table1 = GetData() Dim col As DataColumn Dim
colIndex As Integer Dim rowIndex As Integer rowIndex = 1 For Each col In table1.Columns colIndex += 1 excel.Cells(rowIndex, colIndex) = col.ColumnName Next col Dim row As DataRow For Each row In table1.Rows rowIndex += 1 colIndex = 0 For Each col In table1.Columns
colIndex += 1 excel.Cells(rowIndex, colIndex) = row(col.ColumnName).ToString() Next col Next row excel.Application.Visible = True Marshal.ReleaseComObject(excel) GC.Collect() GC.WaitForPendingFinalizers() excel = Nothing pl advice me. thanks sr
I've worked on this and hope this will help you. Instead of using Excel COM you can simply go for this which saves system resources. Consider this example.... Dim myConnection As SqlConnection Dim myCommand As SqlDataAdapter ' Create a connection to the "pubs"
SQL database located on the local computer. myConnection = New SqlConnection("server=localhost; database=pubs;UID=;PWD=;") ' Connect to the SQL database using a SQL SELECT query to get all ' the data from the "Authors" table. myCommand = New SqlDataAdapter("SELECT
* FROM Authors", myConnection) ' Create and fill a DataSet. Dim ds As DataSet = New DataSet() myCommand.Fill(ds) ' now that you got the dataset, u use this to populate the temporary datagrid Response.Clear() Response.Charset = "" Response.ContentType = "application/vnd.ms-excel"
' set the content type Dim stringWrite As New System.IO.StringWriter() Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite) Dim dg As New System.Web.UI.WebControls.DataGrid() dg.DataSource = ds.Tables(0) ' it is here where u need to assign ur datatable
dg.DataBind() dg.RenderControl(htmlWrite) Response.Write(stringWrite.ToString) Response.End() ' this will not have any refence to teh Excel object....hope u njoi it.. Cheers Srinivas
Hi Srinivas Thanks for your response.When i follow the code i can see the blank excel sheet only.more over its openning in same browser.how to open the excel sheet in different browser.when i debug the code datatable.rows shwing 20 but still i its displaying
blank sheet.can you pl advice me thanks srinu
Hi, If you want to open it in a new window then u need to do this... Just add this line after Response.ContentType = "application/vnd.ms-excel" ' set the content type Response.AddHeader("Content-Disposition", " attachment; filename=urreportname.xls") This will
forcibly prompt the user either to open it/save it/cancel. I think this will do. Regarding the blank XL sheet, u better try with dataset first, as I've worked this example using sample DB and then sent it 2 u. Cheers Srinivas
You need to give security right to launch and configure excel application to the user who use the excel instance. I think in the case it would be ASPNET user use dcomcnfg tool and search for Microsoft Excel Application on the security tab you can give right
to the user. I am using the same kind of application which export the data to excel and i was having same problem. after some playing around this i found out about this. and now all excel process are getting terminated properly. second you need to monitor
your code to see if excel generates any error. use try catch finally block to terminate excel instance in all event.
Hi rnamin, I also get the same problem, can you help to specify which object should be granted with which authority. Many Thanks. I have already granted ASP.NET to use the DCOM, Microsoft Excel with Launch, Access and Configuration.
Hi, From Start - > Run Menu start the application dcomcnfg. on the application tab search for Application Microsoft Excel and click property. on the security tab of excel select custom permission for access, launch and configuration in that add you local ASPNET
user for full control, also add ann. internet user that IIS use. and give them full permission to launch,access and configure. That should solve the problem. In case if still this don't solve the problem use identity impersonate in web.config and give that
user also right to launch, config and access right for excel. However one problem normally we encounter in all office automation that if the application at any time throw any exception or need user interactivity. it wan't work and cause application to hang
like waiting state. so check you code very carefully to see if that is not the case. However i would recommand to use the approach of changing dcom settings only if you are developing application for local intranet and that would be accessed by only known
set of users. Let me know the result
Thx rnamin, I think the problem is "partially" solved, as even I run 20 times the Excel, there is only 1 Excel.exe in task manager. However, even I closed all my application, this Excel.exe still exists in my task manager. Do you also have the same case. Anyway,
Thanks a lot.
Check if you are manually able to terminate that last excel instance. If you are able to terminate that instance. I think it may be something like com instance pooling, I am not sure for that. but in this forum or somewhere i read about it from one user who
has always one instance left in the memory. In my case all the instance of excel are getting terminated properly.
Response.Charset = ""
Dim tw As System.IO.StringWriter = New System.IO.StringWriter
Dim hw As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(tw)
Member
1 Points
106 Posts
excel generation
Nov 20, 2003 09:55 PM|msrnivas|LINK
None
0 Points
10 Posts
Re: excel generation
Nov 21, 2003 12:35 AM|Y.Srinivas|LINK
Member
1 Points
106 Posts
Re: excel generation
Nov 21, 2003 03:12 AM|msrnivas|LINK
None
0 Points
10 Posts
Re: excel generation
Nov 21, 2003 05:38 PM|Y.Srinivas|LINK
None
0 Points
38 Posts
Re: excel generation
Feb 06, 2004 09:49 PM|rnamin|LINK
None
0 Points
18 Posts
Re: excel generation
Feb 09, 2004 01:30 AM|chaoslaw0362|LINK
None
0 Points
38 Posts
Re: excel generation
Feb 10, 2004 12:20 PM|rnamin|LINK
None
0 Points
18 Posts
Re: excel generation
Feb 10, 2004 10:22 PM|chaoslaw0362|LINK
None
0 Points
38 Posts
Re: excel generation
Feb 11, 2004 12:39 PM|rnamin|LINK
None
0 Points
1 Post
Re: excel generation
Feb 17, 2004 01:51 PM|Eisbaer57|LINK
Contributor
3697 Points
1235 Posts
Re: excel generation
Aug 29, 2007 08:37 AM|srijaya_ramaraju@yahoo.com|LINK
Plz Check This Code
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment;Filename=patientdetails.xls")
Response.Write ("<meta http-equiv=""Content-Type"" content=""text/html; charset=Utf-8"">")
Response.Charset = ""
Dim tw As System.IO.StringWriter = New System.IO.StringWriter
Dim hw As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(tw)
dbgrid_patients.Enableviewstate=False
dbGrid_patients.RenderControl(hw)
Response.Write(tw.ToString)
Response.End()
Note : Set Allowsorting property to false
Thank u
Baba
Please remember to click "Mark as Answer" on this post if it helped you.