发送带有excel附件的电子邮件(通过从数据表创建excel文件) [英] Send email with excel attachment(by creating excel file from datatable)
本文介绍了发送带有excel附件的电子邮件(通过从数据表创建excel文件)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我必须发送带有excel附件的电子邮件.此excel将通过从datatable导出数据来创建.
我创建了一个Excel文件，并将其保存在服务器上，并通过电子邮件附件发送.
但是我收到空白Excel的邮件.我已经在服务器上的物理路径上检查了excel文件，它包含数据.但是在打开它时会给我类似"test.xls文件已被另一用户锁定以供编辑的消息.打开只读或单击通知以打开只读并在不再使用文档时接收通知"
我已使用以下代码导出和通过电子邮件发送功能
Hi,
I have to send email with excel attachment . This excel will created by exporting data from datatable.
I have created one excel file and saved it on server and sent it with email attachement.
BUT I am getting mail with blank excel. I have checked excel file on its physical path means on server , it contains data. BUT while opening it give me message like " test.xls file is locked for editing by another user. Open read only or click notify to open read only and recieve notification when the document is no longer use"
I have used following code to export and email functionality
private void BindtoGrid()
{
try
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Date", SqlDbType.NVarChar);
//param[0].Value = DateTime.Now.Date.ToString("MM/dd/yyyy");
param[0].Value = "11/17/2012";//DateTime.Now.Date.ToString("MM/dd/yyyy");
SqlDataReader dr = DBOPS.SqlHelper.ExecuteReader(objGlobal.connString, CommandType.StoredProcedure, "sp_rptBookOrderReportPerDay", param);
DataTable objdt = new DataTable();
objdt.Load(dr);
if (objdt.Rows.Count > 0)
{
string fileFullPath = string.Empty;
string fileName = string.Concat("test.xls");
string fullPath = string.Concat(Server.MapPath("../Reports/BookOrdersFile/"), fileName);
//if (System.IO.File.Exists(fullPath))
// System.IO.File.Delete(fullPath);
//GridViewExportUtil.ExportGrid(grd, fullPath);
DataSet ds=new DataSet();
ds.Tables.Add(objdt);
ExcelHelper.ToExcel(ds, fileName, fullPath);
string strMessage = string.Empty;
string strSubject = "EnergiseYou book order details";
clsGlobal objclsGlobal = new clsGlobal();
strMessage = objclsGlobal.getMessage(Server.MapPath("..") + "//Email Templates//BookOrderPerDayEmail.htm");
strMessage = strMessage.Replace("##Date", DateTime.Now.Date.ToString("MM/dd/yyyy"));
//strMessage = strMessage.Replace("##OrderDetails", orderDetails);
int res = objclsGlobal.BookOrderEmailAttachement("test@test.com", strMessage, strSubject, fullPath, fileName);
// sendDailyNotificationEmail(fileName);
// int res1 = objclsGlobal.BookOrderEmail("test@test.com", strMessage, strSubject);
// if (res == 1)
Response.Write("email sent successfully");
}
else
{
Response.Write("No record found");
}
}
catch (Exception ex)
{
}
}
/////////////////////Class文件/////////////////////////
/////////////////////Class file/////////////////////////
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
public class ExcelHelper
{
//Row limits older excel verion per sheet, the row limit for excel 2003 is 65536
const int rowLimit = 65000;
private static string getWorkbookTemplate()
{
var sb = new StringBuilder(818);
sb.AppendFormat(@"{0}", Environment.NewLine);
sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""??>{0}", Environment.NewLine);
sb.AppendFormat(@"{0}", Environment.NewLine);
sb.AppendFormat(@" <Styles>{0}", Environment.NewLine);
sb.AppendFormat(@" <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);
sb.AppendFormat(@" <alignment ss:vertical="" bottom="" />{0}", Environment.NewLine);
sb.AppendFormat(@" <borders />{0}", Environment.NewLine);
sb.AppendFormat(@" <font ss:fontname="" calibri="" x:family="" swiss="" x:size="" 11="" x:color="" />{0}", Environment.NewLine);
sb.AppendFormat(@" <interior />{0}", Environment.NewLine);
// <interior ss:color="#800080" ss:pattern="Solid" />
sb.AppendFormat(@" <numberformat />{0}", Environment.NewLine);
sb.AppendFormat(@" <protection />{0}", Environment.NewLine);
sb.AppendFormat(@" </Style>{0}", Environment.NewLine);
sb.AppendFormat(@" <Style ss:ID=""s62"">{0}", Environment.NewLine);
sb.AppendFormat(@" <font ss:fontname="" calibri="" x:family="" swiss="" x:size="" 11="" x:backgroundcolor="" x:color="" hold=" /> sb.AppendFormat(@" x:bold="" 1="" />{0}", Environment.NewLine);
sb.AppendFormat(@" </Style>{0}", Environment.NewLine);
sb.AppendFormat(@" <Style ss:ID=""s63"">{0}", Environment.NewLine);
sb.AppendFormat(@" <numberformat ss:format="" short="" date="" />{0}", Environment.NewLine);
sb.AppendFormat(@" </Style>{0}", Environment.NewLine);
sb.AppendFormat(@" </Styles>{0}", Environment.NewLine);
sb.Append(@"{0}\r\n");
return sb.ToString();
}
private static string replaceXmlChar(string input)
{
input = input.Replace("&", "&");
input = input.Replace("<", "<");
input = input.Replace(">", ">");
input = input.Replace("\"", """);
input = input.Replace("''", "''");
return input;
}
private static string getCell(Type type, object cellData)
{
var data = (cellData is DBNull) ? "" : cellData;
if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<cell><data ss:type="\"Number\"" >{0}</data></cell>", data);
if (type.Name.Contains("Date") && data.ToString() != string.Empty)
{
return string.Format("<cell ss:styleid="\"s63\""><data ss:type="\"DateTime\"">{0}</data></cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));
}
return string.Format("<cell><data ss:type="\"String\"">{0}</data></cell>", replaceXmlChar(data.ToString()));
}
private static string getWorksheets(DataSet source)
{
var sw = new StringWriter();
if (source == null || source.Tables.Count == 0)
{
sw.Write("<worksheet ss:name="\"Sheet1\"">\r\n<table>\r\n<row><cell><data ss:type="\"String\""></data></cell></row>\r\n</table>\r\n</worksheet>");
return sw.ToString();
}
foreach (DataTable dt in source.Tables)
{
if (dt.Rows.Count == 0)
sw.Write("<worksheet ss:name="\""">\r\n<table>\r\n<row><cell ss:styleid="\"s62\""><data ss:type="\"String\""></data></cell></row>\r\n</table>\r\n</worksheet>");
else
{
//write each row data
var sheetCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if ((i % rowLimit) == 0)
{
//add close tags for previous sheet of the same data table
if ((i / rowLimit) > sheetCount)
{
sw.Write("\r\n\r\n");
sheetCount = (i / rowLimit);
}
sw.Write("\r\n<worksheet ss:name="\""" mode="hold" /> (((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n<table>");
//write column name row
sw.Write("\r\n<ss:column ss:width="\"200\"/">");
sw.Write("\r\n<row ss:height="\"20\"">");
foreach (DataColumn dc in dt.Columns)
sw.Write(string.Format("<cell ss:bgcolor="\"red\"" ss:styleid="\"s62\""><data ss:type="\"String\"">{0}</data></cell>", replaceXmlChar(dc.ColumnName)));
sw.Write("</row>");
}
// sw.Write("\r\n<ss:column ss:width="\"500\"/">");
sw.Write("\r\n<row ss:height="\"20\"">");
foreach (DataColumn dc in dt.Columns)
sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));
sw.Write("</row>");
}
sw.Write("\r\n</ss:column></ss:column></table>\r\n");
}
}
return sw.ToString();
}
public static string GetExcelXml(DataTable dtInput, string filename)
{
var excelTemplate = getWorkbookTemplate();
var ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
var worksheets = getWorksheets(ds);
var excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}
public static string GetExcelXml(DataSet dsInput, string filename)
{
var excelTemplate = getWorkbookTemplate();
var worksheets = getWorksheets(dsInput);
var excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}
public static void ToExcel(DataSet dsInput, string filename, string fullPath)
{
var excelXml = GetExcelXml(dsInput, filename);
string str_FileName = fullPath;//"ExportData" + System.DateTime.Now.Ticks.ToString() + ".xls";
// Open File stream for writing.
FileStream fileStream;
StreamWriter streamWriter;
fileStream = new FileStream(str_FileName, FileMode.Create, FileAccess.ReadWrite);
//fileStream = new FileStream(serverPath + "Xls\\" + str_FileName, FileMode.Create, FileAccess.Write);
streamWriter = new StreamWriter(fileStream);
streamWriter.WriteLine(excelXml.ToString());
streamWriter.Close();
streamWriter.Dispose();
fileStream.Dispose();
fileStream.Close();
GC.Collect();
}
//public static void ToExcel(DataTable dtInput, string filename, HttpResponse response)
//{
// var ds = new DataSet();
// ds.Tables.Add(dtInput.Copy());
// ToExcel(ds, filename, response);
//}
}
推荐答案
我看不到您在哪里声明了ExcelHelper-可能是该类的实例正在锁定文件.尝试重构该函数，以便在尝试通过电子邮件发送文件之前，ExcelHelper超出范围.
看来您已经尝试使用GC.Collect()和.Dispose()调用来解决该问题-这太过分了，不需要-.close()应该足够.
I can''t see where you have declared ExcelHelper - it''s probably the instance of that class that is locking the file. Try refactoring the function so that ExcelHelper goes out of scope before you try to email the file.
It looks like you''ve tried to address the issue with the GC.Collect() and the .Dispose() calls - this is overkill and they aren''t required -.close() should be sufficient.
这篇关于发送带有excel附件的电子邮件(通过从数据表创建excel文件)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
