Excel and IIS Interop

drbingen · April 13th, 2011, 02:16 PM

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. The IIS server and all other applications are on one computer.

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? How does one deal with an Interop problem? 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