Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
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 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("&", "&amp");
            input = input.Replace("<", "<");
            input = input.Replace(">", ">");
            input = input.Replace("\"", """);
            input = input.Replace("'", "&apos;");
            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);
        //}
    }
Posted 21-Nov-12 1:24am
Pravinjas1.4K
v2
Comments
aspnet_regiis -i at 21-Nov-12 8:03am
   
This means the excel.exe process is not getting killed after creating the excelsheet. Check it in the task manager.. Use Process.Kill() to kill this process
digimanus at 21-Nov-12 8:05am
   
you exception: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
tells it all something still has the file opened. Is the file closed before sending?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 389
1 Sergey Alexandrovich Kryukov 362
2 CPallini 130
3 Abdul Samad KP 125
4 Richard MacCutchan 110
0 OriginalGriff 6,249
1 Sergey Alexandrovich Kryukov 5,680
2 CPallini 4,850
3 George Jonsson 3,454
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web01 | 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