locked
excel generation RRS feed

  • Question

  • User-2016485187 posted
    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
    Thursday, November 20, 2003 9:55 PM

All replies

  • User491112072 posted
    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
    Friday, November 21, 2003 12:35 AM
  • User-2016485187 posted
    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
    Friday, November 21, 2003 3:12 AM
  • User491112072 posted
    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
    Friday, November 21, 2003 5:38 PM
  • User-1133784284 posted
    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.
    Friday, February 6, 2004 9:49 PM
  • User-636418385 posted
    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.
    Monday, February 9, 2004 1:30 AM
  • User-1133784284 posted
    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
    Tuesday, February 10, 2004 12:20 PM
  • User-636418385 posted
    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.
    Tuesday, February 10, 2004 10:22 PM
  • User-1133784284 posted
    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.
    Wednesday, February 11, 2004 12:39 PM
  • User1691669121 posted
    Have a look at SoftArtisans ExcelWriter: http://officewriter.softartisans.com/officewriter-37.aspx
    Tuesday, February 17, 2004 1:51 PM
  • User558493921 posted

     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 

    Wednesday, August 29, 2007 8:37 AM