Open Excel from ASP.Net

Hello Bro,

Sorry to forgot to tell you one thing more:

all above setting should be done on the server on which you are facing problem.

After all this if you will try to open the same file that was giving you error previously then you will found an error that will be different as previous one that might be "File is already use by another process"

then just do one thing press ctrl + shift + Escap on that server

go to process tab

press button show processes from all users

and select Excel.exe then press End Process

Thats it Now hope you will not face any problem.

Regards,

Asif Ahmed Khan

Thanks for your reply.
I will try the above code and let you know. My account got locked.
if 6 users have to use this then do i need to provide all their user-id's and password's.

How are you consuming Excel, if it is using COM interop?
the posted comment (killing process) will not solve your issues completely, you need to make sure that your excel file is used only once for the whole site. (e.g. Using a synchronization object like lock or Mutex) so that only one web page can access that code interop at the time.

Hello SheelaAnand!

Not web application user means you are not suppose to give the user name or password of application. You have to just add any of a single user name and password that can be yours. After adding impersonation TAG with your credentials all user will be treated as same. So, don't worry about different users.


Regards,

Asif Ahmed KHan

Hello mas_oz2003,

For your kind information I mentioned "KILL PROCESS" only if excel handling crash from C# code for file ABC.xls If you will again use that file for process that are used in another process then you will face the error that I have mentioned before. As you mentioned in your post "so that only one web page can access that code interop at the time." So in the case of exception it will not close the introp object properly so you have to do this.

@SheelaAnand!

Please make sure object that is opening EXCEL in C# should be close properly after completion even in Exception area too.

Regards,

Asif Ahmed Khan

Thanks all,

@Asif: If i code my id and  password..then would that be secure?.

@mas_oz2003:: Eash user has his/her own excel...

i get the message account is locked ...not sure why...

It is visual basic and not in C#

Thanks
Sheela

Hello Bro!

I agree with you if web config can be access by some one else so its a problem so to overcome this problem what you can do is you can apply encryption on that.

The following is the recommended procedure to set and encrypt the identity:

a: Encrypt the identity using the standard aspnet_regiis tool. Although normally the web.config file cannot be viewed by browser users, if the Web server is compromised it may be possible for the credentials to be viewed by unauthorized users. To use aspnet_regiis, open a .NET command prompt from Start --> (All) Programs --> Microsoft .NET Framework SDK v2.0 --> SDK Command Prompt.


b: Type in the command to encrypt the identity section in web.config. For example:

 

aspnet_regiis -pe "system.web/identity" -app "/MyWebMappingApp" -prov "DataProtectionConfigurationProvider"

Select allOpen in new window

where "/MyWebMappingApp" is the URL of the web application on the local IIS server.

If the website uses the File System option now availabe in Visual Studio, where the built-in Web server is used, the following will encrypt the identity section using a file path (note the -pef option and omission of the -app option):

 

aspnet_regiis -pef "system.web/identity" "C:\MyWebFolder\MyWebMappingApp" -prov "DataProtectionConfigurationProvider"

Select allOpen in new window

The above examples use the .NET DataProtectionConfigurationProvider, which uses a machine-specific key for encryption. If you need to deploy the same application to multiple servers, such as in a Web Farm, you should use the RSAProtectedConfigurationProvider, which allows export and import of encryption keys.

The identity is now updated and encrypted in the web.config file. The web mapping application should run normally and use the encrypted identity to access the ArcGIS Server local resource.

You can have more information about this here:

http://msdn.microsoft.com/en-us/library/xh507fc5(v=vs.71).aspx

AND

An alternative option to the encryption with aspnet_regiis is to encrypt the credentials for the identity within the Registry. To do this, use the Aspnet_setreg utility. This utility is available, with instructions, from Microsoft at http://support.microsoft.com/default.aspx?scid=kb;en-us;329290.

Hope this will help you solve your problem. And I f you want to encrypt that then u can perform with the help of above mentioned techniques.

Regards,

Asif Ahmed Khan

Hi,
i removed
<identity impersonate="true" userName="domain\\xyz" password="abc" />
<identity impersonate="true"  />
I brought it back to get my old code back and the logic page..but it still doesn't work
I will try the above when i resolve the excel file issue.

Thanks

Hello Bro!

You have to use this line if you want to get rid off the problem or use encrypted technique that I have mentioned in my last post and apply all steps as well from my first post make sure these all will be on your desire server not on local on server where you want to deploy. I was also stuck in that issue as I told you and resolved this by spending 2 nights hope you will solve your's by these techniques.

Regards,

Asif Ahmed Khan

Can you post the code that is calling Excel?

       Dim oExcel As New Excel.Application()        --->error

Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
        Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
        Dim oCells As Excel.Range
        Dim sFile As String, sTemplate As String

        sFile = Server.MapPath(Request.ApplicationPath) & _
          "\file1.xls"

        oExcel.Visible = False : oExcel.DisplayAlerts = False
        oBooks = oExcel.Workbooks
        oBooks.Open(Server.MapPath(Request.ApplicationPath) & _
        "\file1.xls")
        oBook = oBooks.Item(1)
        oSheets = oBook.Worksheets
        oSheet = CType(oSheets.Item(3), Excel.Worksheet)
        oSheet.Name = "data"
        oCells = oSheet.Cells

        Try
            oraobjconn.Open()
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("select distinct grandtot from schema name.tablename", oraobjconn)
            Dim dt As New DataSet
            Dim wgrandtot As Decimal
            da.Fill(dt, "table")
            Dim row As DataRow
            If dt.Tables("table").Rows.Count = 0 Then
                label1.Text = " Sorry No Data"
            Else
                For Each row In dt.Tables("table").Rows
                    wgrandtot = row("grandtot")
                    Dim wdd As Integer
                    wdd = Day(Session("field1").ToString)
                    If wdd > 10 Then
                        oSheet.Cells(r + wdd, 2) = wgrandtot
                    End If
                    oSheet.SaveAs(sFile)

Hello BRo!

Here's a code snippet that writes the contents of an ADO recordset to a Excel file, you'll need to reference an Excel library in your project for it to work. Basically you need to learn about the Excel object model, try looking in your MSDN documentation.

 

'*******************************************************************************
' excelPrintRecordSet(Sub)
'
' PARAMETERS:
'
'
' RETURN VALUE:
'
'
' DESCRIPTION:
' Test function that will print out all the records from a recordset in Excel.
'*******************************************************************************
Public Sub excelPrintRecordSet(rstTmp As ADODB.Recordset)
  Dim appExcel As Excel.Application
  Dim wbkReport As Excel.Workbook
  Dim wksReport As Excel.Worksheet
  Dim intField As Integer, intRow As Integer
  
  Const PROCEDURE_NAME As String = "excelPrintRecordSet"

  On Error GoTo errorHandler

  Set appExcel = New Excel.Application
  appExcel.Visible = True
  Set wbkReport = appExcel.Workbooks.Add
  'wbkReportame = "Kilometer Report"
  Set wksReport = wbkReport.Worksheets(1)

  If rstTmp.EOF <> True Then
    rstTmp.MoveFirst
    intRow = 1
    Do
      For intField = 0 To rstTmp.Fields.Count - 1
        wksReport.Cells(intRow, intField + 1) = rstTmp.Fields(intField).Name & "=" & rstTmp.Fields(intField).Value
      Next intField
      rstTmp.MoveNext
      intRow = intRow + 1
    Loop Until rstTmp.EOF = True
  End If
  Exit Sub

errorHandler:
  frmErrorHandler.errorForm MODULE_NAME, PROCEDURE_NAME
  Err.Clear
End Sub

Select allOpen in new window

Another way->>>>>>>>>>>>>>>>>>>


 

I did this and it worked here: 

Private Sub cmdExcel_Click() 

Dim app As Excel.Application 
Dim wrk As Excel.Workbooks 
Dim wrkbk As Excel.Workbook 
Dim wrkshs As Excel.Worksheets 
Dim wrksh As Excel.Worksheet 

Set app = New Excel.Application 
Set wrkbk = app.Workbooks.Add 
Set wrksh = wrkbk.Worksheets.Add 
wrksh.Cells(1, 1) = "234" 
wrkbk.SaveAs "C:\NewExcel.xls" 

End Sub 

Private Sub cmdShowExcel_Click() 
Dim app As Excel.Application 
Dim wrk As Excel.Workbooks 
Dim wrkbk As Excel.Workbook 
Dim wrkshs As Excel.Worksheets 
Dim wrksh As Excel.Worksheet 

Set app = New Excel.Application 

Set wrk = app.Workbooks.Open("C:\NewExcel.xls") 
app.Visible = True 

End Sub

Select allOpen in new window

You are using like  Dim oExcel As New Excel.Application() use like Dim oExcel As Excel.Application

hope that helps

Regards,

Asif Ahmed Khan

Hi Asif,

Thanks for helping.. I really appreciate it.

it was the test server creating issues. I didnt have access rights on it.

The excel issue seems to work now, but i still need to go through the security process as uid and pwd has been coded in web.config.

Thanks
Sheela

And thanks to all for helping to resolve this issue.

Thanks
Sheela

hmmm Any way thanks and best of luck for your project and let me know if you need any kind of assistance in future.

Regards,

Asif Ahmed Khan