Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# VB.NET
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 Wink | ;-)
Posted 22-Nov-12 21:57pm
Comments
Rickin Kane at 23-Nov-12 4:15am
   
use xslt
infinitizon at 23-Nov-12 4:20am
   
How? Will it solve the challenge? Can you give a better explanation? Thanks..
aspnet_regiis -i at 23-Nov-12 6:46am
   
Kill the excel process before deleting the excel file
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

By using the next code you can check if you can exclusively touch the file:
 
public class CheckAvailability
    {
        public static Boolean ForFile(String FolderAndFileName)
        {
            bool doesExist = false;
 
            if (!string.IsNullOrWhiteSpace(FolderAndFileName))
            {
                FileInfo info = new FileInfo(FolderAndFileName);
                if (info.Exists)
                {
 
                    try
                    {
                        using (FileStream stream = File.Open(FolderAndFileName, FileMode.Open, FileAccess.Write, FileShare.None))
                        {
                            doesExist = stream.CanWrite;
                        }
                    }
                    catch (Exception err)
                    {
                        UVS.BLL.LOG.Log.Exceptions(err);
                    }
                }
            }
            return doesExist;
        }
    }
 
and after you have saved the excel file don't forget to dispose your SpreadsheetDocument object.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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?
  Permalink  
v3
Comments
infinitizon at 23-Nov-12 5:53am
   
Can you imagine...I just forgot the mail.Dispose() line after sending the mail....
Silly me!!!
 
Thanks guys, It was me afterall

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 458
1 OriginalGriff 344
2 George Jonsson 233
3 Animesh Datta 130
4 Shemeemsha RA 128
0 OriginalGriff 6,179
1 Sergey Alexandrovich Kryukov 5,616
2 CPallini 4,770
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 23 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100