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
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)
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
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
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 ;-)