Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > Visual Web Developer 2008
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Visual Web Developer 2008 Discuss creating ASP.NET 3.5 sites with Microsoft's Visual Web Developer 2008. If your question is more specific to a piece of code than the Visual tool, see the ASP.NET 3.5 forums instead. If your question is specific to the "Express Edition" be sure to state that in your post.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Web Developer 2008 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 13th, 2011, 03:16 PM
Registered User
 
Join Date: Mar 2011
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel and 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. 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
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
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 03:36 AM



All times are GMT -4. The time now is 12:46 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.