Create Excel file on the fly, send as an Email Attachment then delete the file

See more:

C#

VB

Hi all, I hope not to bore you with my epistle but I Need all the help I can get.

The challenge is that I want the created excel file to be attached in an email on the fly without first saving it to the user system or to my application. I noticed it was almost impossible to create the excel file without first saving the file on the system so I decided to create the file save it in a directory on the server first, send it in an email then delete the file.

In order to achieve the above I first used the Microsoft.Office.Interop, but I encountered errors when I hosted on the Windows 7 Server R2. So I went for the option of OpenXML after several persuations.

Here is my code

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

  'http://www.macronimous.com/resources/calling_stored_procedures_from_ASP.NET_and_VB.NET.asp
  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 because of the delete operation saying

The process cannot access the file 'C:\inetpub\wwwroot\wwwroot\intranet\HR\rpt_LPS00628.xls' because it is being used by another process.

On my development/testing environment, I see the process WebDev.WebServer.EXE and when I stop the process it works again but I don't see the process on deployment server. 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 ;-)