Create Excel file on the file and send as an Email Attachment

[infinitizon]

Hell guys,
The closer I get to the completion of this project, the more complex it seems to get.
Please help me look at this code

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Net.Mail
Imports Class1
Imports Microsoft.Office.Interop

Partial Class HR_v_rpt
  Inherits System.Web.UI.Page

  Private HRconnectionString As String = ConfigurationManager.ConnectionStrings("HRConnectionString").ConnectionString
  Private ConnectionString As String = ConfigurationManager.ConnectionStrings("NAVConnectionString").ConnectionString
  Private tblConnectionString As String = ConfigurationManager.ConnectionStrings("tblConnectionString").ConnectionString

  Protected Sub btn_getRpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_getRpt.Click
    getRpt("rpt_individual_OVT")
  End Sub

  Protected Function getRpt(ByVal theCommand As String, Optional ByVal opt As String = "what") As Boolean
    Dim dt As New DataTable()
    'Dim dr As SqlClient.SqlDataReader

    Dim connection As New SqlConnection(HRconnectionString)
    Dim sqlCmd As New SqlCommand(theCommand, connection)
    Try
      connection.Open()
      sqlCmd.CommandType = CommandType.StoredProcedure

      sqlCmd.Parameters.AddWithValue("@staff_id", txtStaffId.Text)
      Dim reader As SqlDataReader = sqlCmd.ExecuteReader()

      Dim recipientEmail As String

      Dim xlApp As Excel.Application
      Dim xlWorkBook As Excel.Workbook
      Dim xlWorkSheet As Excel.Worksheet
      Dim misValue As Object = System.Reflection.Missing.Value

      xlApp = New Excel.ApplicationClass
      xlWorkBook = xlApp.Workbooks.Add(misValue)
      xlWorkSheet = xlWorkBook.Sheets("sheet1")

      Dim r As Integer = 1
      While reader.Read()
        xlWorkSheet.Cells(r, 1) = reader("over_id")
        xlWorkSheet.Cells(r, 2) = reader("staff_id")
        xlWorkSheet.Cells(r, 3) = reader("dept_id")
        xlWorkSheet.Cells(r, 4) = reader("apply_date")
        recipientEmail = reader("email")
        r += 1
      End While
      
      xlWorkBook.Close()

      releaseObject(xlApp)
      releaseObject(xlWorkBook)
      releaseObject(xlWorkSheet)

      sendMailToUser(recipientEmail, attach)    'Send the excel file in an attachment to the applicant

      If System.IO.File.Exists(loc) = True Then
        System.IO.File.Delete(loc)
      End If
    Catch ex As Exception
      ShowAlertMessage("Operation Failed! " & ex.Message)
      Return False
    End Try

    connection.Close()

    Return True
  End Function

  Private Sub releaseObject(ByVal obj As Object)
    Try
      System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
      obj = Nothing
    Catch ex As Exception
      obj = Nothing
    Finally
      GC.Collect()
    End Try
  End Sub

  Protected Sub sendMailToUser(ByVal recipient As String, ByVal dattachment As String)
    'mail 1st level supervisor
    Dim SmtpServer As New SmtpClient()
    Dim mail As New MailMessage()
    mail.From = New MailAddress("noreply@ourcompany.com")
    mail.To.Add(recipient)

    mail.Subject = "Overtime report request"
    Dim attachment As System.Net.Mail.Attachment
    attachment = New System.Net.Mail.Attachment(dattachment)
    mail.Attachments.Add(attachment)

    mail.IsBodyHtml = True
    SmtpServer.Send(mail)
  End Sub

End Class

The challenge is that I want the created excel file to be attached in the mail (that is sent to the user) on the fly without first saving it to the user system or to my application.

What can I do

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[infinitizon]

I'm sorry for the first line in the post...It should have been "Hello guys"

 

[Rhys666]

You may want to check this Considerations for server-side Automation of Office. To summarise;

All current versions of Microsoft Office were designed, tested, and configured to run as end-user products on a client workstation. They assume an interactive desktop and user profile. They do not provide the level of reentrancy or security that is necessary to meet the needs of server-side components that are designed to run unattended.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution.

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[jbenson001]

I would look into Rhys's suggestion. I have never used those tools, but it seems the way to go.
If you don't have the time, I would simply just save the file (which will save on the server) and give it a unique name(possibly a GUID). Then attach and send the file then delete the file from the server.

 

[infinitizon]

Thanks guys, I eventually went through the considerations and decided to follow on on it.

I was able to modify my code thus

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Net.Mail
Imports Class1
Imports System.IO
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Partial Class HR_v_rpt
  Inherits System.Web.UI.Page

  '[URL unfurl="true"]http://www.macronimous.com/resources/calling_stored_procedures_from_ASP.NET_and_VB.NET.asp[/URL]
  Private seriesCode As String = "OVT"
  Private HRconnectionString As String = ConfigurationManager.ConnectionStrings("HRConnectionString").ConnectionString
  Private CumminsNavConnectionString As String = ConfigurationManager.ConnectionStrings("CumminsNAVConnectionString").ConnectionString
  Private WebTablesConnectionString As String = ConfigurationManager.ConnectionStrings("WebTablesConnectionString").ConnectionString

  Private Function BuildWorkbook(ByVal theCommand As String, ByVal fileName As String) As String

    Dim connection As New SqlConnection(HRconnectionString)
    Dim sqlCmd As New SqlCommand(theCommand, connection)

    Try
      Using s As SpreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)
        Dim workbookPart As WorkbookPart = s.AddWorkbookPart()
        Dim worksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
        Dim relId As String = workbookPart.GetIdOfPart(worksheetPart)
        Dim workbook As New Workbook
        Dim fileVersion As FileVersion = New FileVersion() With {.ApplicationName = "Microsoft Office Excel"}
        Dim worksheet As New Worksheet
        Dim sheetData As New SheetData

        connection.Open()
        sqlCmd.CommandType = CommandType.StoredProcedure

        sqlCmd.Parameters.AddWithValue("@staff_id", txtStaffId.Text)
        Dim reader As SqlDataReader = sqlCmd.ExecuteReader()

        Dim recipientEmail As String

        Dim newR As Row = New Row() With {.RowIndex = 1}
        Dim headerRow As Row = CreateContentRow(1, "Overtime id", "Staff Id", "Dept Id", "Apply Date")
        sheetData.AppendChild(headerRow)

        Dim r As Integer = 2
        While reader.Read()
          Dim contentRow As Row = CreateContentRow(r, reader("over_id"), reader("staff_id"), reader("dept_id"), reader("apply_date"))
          sheetData.AppendChild(contentRow)

          recipientEmail = reader("email")
          r += 1
        End While

        worksheet.Append(sheetData)
        worksheetPart.Worksheet = worksheet
        worksheetPart.Worksheet.Save()
        Dim sheets As Sheets = New Sheets
        Dim sheet As Sheet = New Sheet() With {.Name = "Sheet1", .SheetId = 1, .Id = relId}
        sheets.Append(sheet)
        workbook.Append(fileVersion)
        workbook.Append(sheets)
        s.WorkbookPart.Workbook = workbook
        s.WorkbookPart.Workbook.Save()
        s.Close()
        Return recipientEmail
      End Using
    Catch ex As Exception
      Console.WriteLine(ex.ToString)
      Console.ReadLine()
      ShowAlertMessage(ex.Message)
    End Try
  End Function

  Private Shared Function CreateContentRow(ByVal index As UInt32, ByVal over_id As String, ByVal staff_id As String, _
                                           ByVal dept_id As String, ByVal apply_date As String) As Row
    Dim r As Row = New Row() With {.RowIndex = index}

    Dim cell1 As Cell = CreateTextCell("A", index, over_id)
    Dim cell2 As Cell = CreateTextCell("B", index, staff_id)
    Dim cell3 As Cell = CreateTextCell("C", index, dept_id)
    Dim cell4 As Cell = CreateTextCell("D", index, apply_date)
    'Dim cell3 As Cell = CreateNumberCell("C", index, salesThisYear) You can also create a number cell
    r.Append(cell1)
    r.Append(cell2)
    r.Append(cell3)
    r.Append(cell4)
    Return r
  End Function

  Private Shared Function CreateTextCell(ByVal header As String, ByVal index As String, ByVal text As String) As Cell
    Try
      Dim c As Cell = New Cell() With {.DataType = CellValues.InlineString, .CellReference = header + index}
      Dim istring As InlineString = New InlineString
      Dim t As Text = New Text() With {.Text = text}
      istring.Append(t)
      c.Append(istring)
      Return c
    Catch ex As Exception
      ShowAlertMessage(ex.Message)
    End Try
    Return New Cell
  End Function

  Private Shared Function CreateNumberCell(ByVal header As String, ByVal index As String, ByVal number As Integer) As Cell
    Dim c As Cell = New Cell() With {.CellReference = header + index}
    Dim v As CellValue = New CellValue() With {.Text = number.ToString()}
    c.Append(v)
    Return c
  End Function

  Protected Sub btn_getRpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_getRpt.Click
    Dim loc As String = Server.MapPath("~/HR/rpt_" & txtStaffId.Text & ".xls")

    Dim email As String = BuildWorkbook("rpt_individual_OVT", loc)
    If sendMailToUser(email, loc) Then
      ShowAlertMessage("You request has been processed and an email sent to the email you provided")
    Else
      ShowAlertMessage("Your request has been processed. \n However, there was an error sending it to your email")
    End If

    'Delete the file as soon as mail is sent
    If System.IO.File.Exists(loc) = True Then
      System.IO.File.Delete(loc)
    End If
  End Sub

  Protected Function sendMailToUser(ByVal recipient As String, ByVal dattachment As String) As Boolean
    Try
      'mail 1st level The requester
      Dim SmtpServer As New SmtpClient()
      Dim mail As New MailMessage()
      mail.From = New MailAddress("noreply@ourcompany.com")
      mail.To.Add(recipient)

      mail.Subject = "Overtime report request"

      mail.Body = "Hello, <br /><br /> The overtime report you requested for is attached herewith.<br /><br />Thank you.<br /><br />"
      mail.Body &= "<span style='font-size:10px;'>You cannot reply to this mail since it is an automatically generated mail.<br />"
      Dim attachment As System.Net.Mail.Attachment
      attachment = New System.Net.Mail.Attachment(dattachment)
      mail.Attachments.Add(attachment)

      mail.IsBodyHtml = True
      SmtpServer.Send(mail)
      Return True
    Catch ex As Exception
      Return False
    End Try
  End Function

End Class

It works well the first time (notice it should delete the file after sending the mail), but subsequently, it gives an error

The process cannot access the file 'C:\inetpub\

http://wwwroot\wwwroot\intranet\HR\rpt_LPS00628.xls'

because it is being used by another process.

which is an IO error

Exception Details: System.IO.IOException: The process cannot access the file 'C:\inetpub\

http://wwwroot\wwwroot\intranet\HR\rpt_LPS00628.xls'

because it is being used by another process.

On my development environment, I see the process WebDev.WebServer.EXE and when I stop the process it works again. I was looking if there is a piece I could write that will automatically stop this process both on development and deployment so as to avoid the error.

Thanks

 

[Rhys666]

May I suggest that restarting the web process every time this code runs may not really be the answer...

Have you considered creating an Excel XML template, an XSL template and transforming the data to generate an output that opens directly in Excel without using interop?

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[infinitizon]

Ok guys,

I have been able to streamline the error, I discover it is the mail process that locks the file that was created.

When I commented out the block (in the btn_getRpt_Click sub):

If sendMailToUser(email, loc) Then
      ShowAlertMessage("You request has been processed and an email sent to the email you provided")
    Else
      ShowAlertMessage("Your request has been processed. \n However, there was an error sending it to your email")
    End If

It did not give me an error again and the file gets deleted. But as soon as I return it back i.e. remove the comments, the error returns.

Basically, the error comes by the server not releasing the mail function after sending.

What can I do?

 

[Rhys666]

See above. Personal experience suggests you're trying to hammer a nail with a sledgehammer, and ignoring best practice. Often the simplest solutions are the best, and are far more supportable in the long run, than persisting with something relatively complex and generally inadvisable.

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[infinitizon]

Can you imagine...I just forgot the mail.Dispose() line after sending the mail.... Silly me!!!

Thanks @Rhys666: but I may not be as much as a pro to know all that...If you can give me a link (or some code) that will help though, I'd be grateful

Thanks all, It was me afterall