Click here to Skip to main content
12,817,112 members (35,367 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# VB 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

  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)

      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

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

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

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

        worksheetPart.Worksheet = worksheet
        Dim sheets As Sheets = New Sheets
        Dim sheet As Sheet = New Sheet() With {.Name = "Sheet1", .SheetId = 1, .Id = relId}
        s.WorkbookPart.Workbook = workbook
        Return recipientEmail
      End Using
    Catch ex As Exception
    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
    Return r
  End Function

  Private Shared Function CreateTextCell(ByVal header As String, ByVal index As String, ByVal text As String) As Cell
      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}
      Return c
    Catch ex As Exception
    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()}
    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")
      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
    End If
  End Sub

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

      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.IsBodyHtml = True
      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 ;-)
Posted 22-Nov-12 22:57pm
Rickin Kane 23-Nov-12 4:15am
use xslt
infinitizon 23-Nov-12 4:20am
How? Will it solve the challenge? Can you give a better explanation? Thanks..
aspnet_regiis -i 23-Nov-12 6:46am
Kill the excel process before deleting the excel file
Rate this: bad
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)

                        using (FileStream stream = File.Open(FolderAndFileName, FileMode.Open, FileAccess.Write, FileShare.None))
                            doesExist = stream.CanWrite;
                    catch (Exception err)
            return doesExist;

and after you have saved the excel file don't forget to dispose your SpreadsheetDocument object.
Rate this: bad
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")
      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?
infinitizon 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
Dr Maq 9-Mar-15 21:52pm
This was the case in my problem as well. Thanks for your sharing

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

    Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170308.1 | Last Updated 23 Nov 2012
Copyright © CodeProject, 1999-2017
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