快速创建Excel文件，作为电子邮件附件发送，然后删除文件 [英] Create Excel file on the fly, send as an Email Attachment then delete the file
问题描述
大家好，我希望不要让您的书信使您烦恼，但我需要我能得到的所有帮助.
面临的挑战是，我希望将创建的excel文件即时附加到电子邮件中，而无需先将其保存到用户系统或我的应用程序中.我注意到在不首先将文件保存到系统中的情况下创建excel文件几乎是不可能的，因此我决定先创建文件，然后将其保存在服务器上的目录中，然后通过电子邮件发送然后删除该文件.
为了实现上述目的，我首先使用了 Microsoft.Office.Interop ，但是在Windows 7 Server R2上托管时遇到了错误.因此，经过多次劝说，我选择了OpenXML.
这是我的代码
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
该进程无法访问文件"C:\ inetpub \ wwwroot \ wwwroot \ intranet \ HR \ rpt_LPS00628.xls"，因为该文件正在被另一个进程使用.
The process cannot access the file ''C:\inetpub\wwwroot\wwwroot\intranet\HR\rpt_LPS00628.xls'' because it is being used by another process.
在我的开发/测试环境中，我看到了 WebDev.WebServer.EXE 进程，当我停止该进程时，它又可以正常工作了，但是我看不到部署过程服务器.我一直在寻找是否可以写的一篇文章会在开发和部署时自动停止该过程，从而避免出现错误.
谢谢;-)
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 ;-)
推荐答案
通过使用下面的代码，您可以检查是否可以独占该文件:
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;
}
}
并且保存了excel文件后，别忘了处理SpreadsheetDocument对象.
and after you have saved the excel file don''t forget to dispose your SpreadsheetDocument object.
好的，伙计们，
我已经能够简化错误，我发现是邮件过程锁定了所创建的文件.
当我注释掉该块时(在btn_getRpt_Click子目录中):
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?
这篇关于快速创建Excel文件，作为电子邮件附件发送，然后删除文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
