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 = "11/17/2012";
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);
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"));
int res = objclsGlobal.BookOrderEmailAttachement("test@test.com", strMessage, strSubject, fullPath, fileName);
Response.Write("email sent successfully");
}
else
{
Response.Write("No record found");
}
}
catch (Exception ex)
{
}
}
/////////////////////Class file/////////////////////////
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
public class ExcelHelper
{
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);
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>");
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);
//}
}