Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Professionals If you are an experienced ASP.NET programmer, this is the forum for your 3.5 questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Professionals section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 13th, 2011, 02:26 PM
Registered User
 
Join Date: Mar 2011
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel & IIS Interop

Hello,

I have a page with a DropDownList, a GridView and 4 Buttons. The DropDowList is a list of available reports. The Gridview displays the results of a selected report. The buttons btnExcel, btnText and btnXML create an Excel file, a tab-delimited file and an XML file respectively in a temp directory. The btnEmail attaches the created file(s) and sends it/them to the logged-in user.

Everything works fine when using the built-in web server of Visual Studio, however when using IIS with Windows Vista, the Excel file does not get created. When using http://localhost, the tab-delimited and XML file work fine and so does sending the email. The only problem is the creation of the Excel file.

Network Service and IUSR has access to the temp directory. Having access to the temp directory cannot be the problem, since if it was the tab-delimited and XML file would not work. Is there some other account that needs to be used for Excel to work? Below is the code for buttons btnExcel, btnText and btnXML.

Code:
Partial Class Report
  Inherits BasePage
  Dim ds As DataSet, dt As System.Data.DataTable

  Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
    btnEmail.Visible = True
    ds = GetData(GetQueryString())
    dt = ds.Tables(0)
    If ((dt.Columns.Count <> 0) And (dt.Rows.Count <> 0)) Then
      Dim myDirectory As New IO.DirectoryInfo("C:\temp")
      Dim xlApp As New Microsoft.Office.Interop.Excel.ApplicationClass
      Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
      Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
      Dim dc As System.Data.DataColumn, dr As System.Data.DataRow
      Dim colIndex As Integer, rowIndex As Integer, strFileName As String, blnFileOpen As Boolean
      If myDirectory.Exists = False Then myDirectory.Create()
      strFileName = "C:\temp\" & RemoveSpaces(DropDownList1.Items(DropDownList1.SelectedIndex).Text) & ".xlsx"
      blnFileOpen = False
      xlBook = xlApp.Workbooks.Add()
      xlSheet = xlBook.ActiveSheet()
      colIndex = 0
      rowIndex = 0
      For Each dc In dt.Columns
        colIndex = colIndex + 1
        xlApp.Cells(1, colIndex) = dc.ColumnName
      Next
      For Each dr In dt.Rows
        rowIndex = rowIndex + 1
        colIndex = 0
        For Each dc In dt.Columns
          colIndex = colIndex + 1
          xlApp.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
        Next
      Next
      xlSheet.Columns.AutoFit()
      Try
        Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
        fileTemp.Close()
      Catch ex As Exception
        blnFileOpen = False
      End Try
      If System.IO.File.Exists(strFileName) Then System.IO.File.Delete(strFileName)
      xlBook.SaveAs(strFileName)
      xlBook.Close()
      releaseObject(xlBook)
      releaseObject(xlSheet)
    End If
  End Sub

  Protected Sub btnText_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnText.Click
    btnEmail.Visible = True
    ds = GetData(GetQueryString())
    dt = ds.Tables(0)
    If ((dt.Columns.Count <> 0) And (dt.Rows.Count <> 0)) Then
      Dim myDirectory As New IO.DirectoryInfo("C:\temp")
      Dim dc As System.Data.DataColumn, dr As System.Data.DataRow, strFileName As String, colIndex As Integer
      If myDirectory.Exists = False Then myDirectory.Create()
      strFileName = "C:\temp\" & RemoveSpaces(DropDownList1.Items(DropDownList1.SelectedIndex).Text) & ".txt"
      Dim writer As StreamWriter = New StreamWriter(strFileName)
      colIndex = 0
      For Each dc In dt.Columns
        colIndex = colIndex + 1
        If colIndex < dt.Columns.Count Then
          writer.Write(dc.ColumnName & vbTab)
        Else
          writer.WriteLine(dc.ColumnName)
        End If
      Next
      For Each dr In dt.Rows
        colIndex = 0
        For Each dc In dt.Columns
          colIndex = colIndex + 1
          If colIndex < dt.Columns.Count Then
            writer.Write(dr(dc.ColumnName) & vbTab)
          Else
            writer.WriteLine(dr(dc.ColumnName))
          End If
        Next
      Next
      writer.Close()
    End If
  End Sub

  Protected Sub btnXML_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnXML.Click
    btnEmail.Visible = True
    ds = GetData(GetQueryString())
    Dim filename As String = "C:\temp\" & RemoveSpaces(DropDownList1.Items(DropDownList1.SelectedIndex).Text) & ".xml"
    Dim stream As New FileStream(filename, FileMode.Create)
    ds.WriteXml(stream, XmlWriteMode.WriteSchema)
    stream.Close()
  End Sub
Any help would be greatly appreciated.

Thanks,

Dave
  #2 (permalink)  
Old April 14th, 2011, 02:24 AM
Authorized User
 
Join Date: Dec 2004
Location: Madurai, Tamil Nadu, India.
Posts: 69
Thanks: 0
Thanked 5 Times in 5 Posts
Send a message via Yahoo to whiterainbow
Default

Try by providing full permission to ASPNET user. As well do an integrated windows security instead of basic or Digest to integrate the IIS to use the permissions of the logged in windows user...
__________________
Thanks in advance.

Regards,

Senthil Kumar M.
  #3 (permalink)  
Old April 16th, 2011, 12:48 AM
Friend of Wrox
Points: 1,749, Level: 16
Points: 1,749, Level: 16 Points: 1,749, Level: 16 Points: 1,749, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2007
Location: San Diego, CA, USA.
Posts: 477
Thanks: 10
Thanked 19 Times in 18 Posts
Default

Quote:
Originally Posted by whiterainbow View Post
Try by providing full permission to ASPNET user. As well do an integrated windows security instead of basic or Digest to integrate the IIS to use the permissions of the logged in windows user...
Even if this is successful, I would really try to find a different way to do this. Maybe someone more familiar with the ins and outs of server security could provide more details, but I'm pretty sure giving the ASPNET account full permissions and elevated trust levels is doing half of a hackers job for them.
__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel and IIS Interop drbingen Visual Web Developer 2008 0 April 13th, 2011 02:16 PM
Excel Interop Issue geomar .NET Framework 2.0 0 February 10th, 2007 09:21 PM
Help needed regarding excel interop livehed ASP.NET 2.0 Basics 0 November 26th, 2006 10:31 AM
Help needed regarding Excel Interop livehed C# 0 November 20th, 2006 02:25 PM
Problem activating Excel Interop lagyossarian ASP.NET 2.0 Basics 1 July 21st, 2006 02:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.