Error message wile trying to open .xls file

Question

I am creating an excel file on the fly for sending it an an attachment in an email. The relevant code snippet is provided below (It's a console app)

public static void SendEmailWithExcelAttachment(DataTable dt)
{
  try
  {
     string smptHost = smptTuple.Item1;
     MailMessage mailMsg = new MailMessage();
     .............................................
    .............................................
    byte[] data = GetData(dt);

    //save the data to a memory stream
    System.IO.MemoryStream ms = new System.IO.MemoryStream(data);

   mailMsg.Attachments.Add(new System.Net.Mail.Attachment(ms, attachmentName, "application/vnd.ms-excel"));

    ....................................
    ....................................

 //send email
 smtpClient.Send(mailMsg); }
 catch (Exception ex)
  {
      throw ex;
   }
}

private static byte[] GetData(DataTable dt)
{
            string strBody = DataTable2ExcelString(dt);
            byte[] data = Encoding.ASCII.GetBytes(strBody);
            return data;
}

private static string DataTable2ExcelString(System.Data.DataTable dt)
{

            string excelSheetName = "Sheet1";
            StringBuilder sbTop = new StringBuilder();
            sbTop.Append("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
            sbTop.Append("xmlns=\" http://www.w3.org/TR/REC-html40\"><head><meta http-equiv=Content-Type content=\"text/html; charset=windows-1252\">");
            sbTop.Append("<meta name=ProgId content=Excel.Sheet ><meta name=Generator content=\"Microsoft Excel 9\"><!--[if gte mso 9]>");
            sbTop.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + excelSheetName + "</x:Name><x:WorksheetOptions>");
            sbTop.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects>");
            sbTop.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>");
            sbTop.Append("<x:ProtectStructure>False</x:ProtectStructure><x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>");
            sbTop.Append("<![endif]-->");
            sbTop.Append("</head><body><table>");

            string bottom = "</table></body></html>";


            StringBuilder sbHeader = new StringBuilder();

            //Header
            sbHeader.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sbHeader.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
            }
            sbHeader.Append("</tr>");

            //Items
            for (int x = 0; x < dt.Rows.Count; x++)
            {
                sbHeader.Append("<tr>");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sbHeader.Append("<td>" + dt.Rows[x][i] + "</td>");
                }
                sbHeader.Append("</tr>");
            }

            string data = sbTop.ToString() + sbHeader.ToString() + bottom;

            return data;
}

This works but when I tried to open the excel file from the attachement, I receive:

I checked an found some solution in SO Post but could not make it to work. I tried like <x:DisplayAlerts>False</x:DisplayAlerts> but didn't work.

What is the use case for building it manually? Is there a necessity for this manner of creating it? — LimpingNinja, May 08 '17 at 02:09
Excel file is a binary file (the first 8-byte signature of XLS file is `D0 CF 11 E0 A1 B1 1A E1`), hence you can't simply constructing Excel data using text mode like XML or JSON. You can try create a method which uses `Excel.Application` instance, iterating `DataTable` to fill Excel cells, afterwards save as XLS file and send it as mail attachment. — Tetsuya Yamamoto, May 08 '17 at 04:56

alex.pulver · Answer 1 · 2017-05-10T05:24:09.183

The warning is display by MS Excel application because your file is not a real Excel file. It is an HTML with XLS extension. An XLS file is a binary file. MS Excel recognizes the HTML file and it display the file in its spreadsheet grid.

MS Excel displays security warnings for files that comes from external sources like email or internet.

The best solution is to use an Excel library that saves real Excel files in xls (old Excel file format) or xlsx (new Excel file format).

You can choose between free libraries like NPOI, EPPlus or commercial libraries like EasyXLS. Some of them saves only xls files, other only xlsx files and a few of them supports both file formats.

You can construct an HTML table and stream it as Excel. – Scott Hannen May 11 '17 at 00:39 — Scott Hannen, May 11 '17 at 00:39

score 2 · Accepted Answer · edited May 23 '17 at 12:34

If the file you want to create and send doesn't have to be in exactly ".xls" format.. and if you are comfortable with ".xlsx" format... I think you might wanna try with EPPlus library, as it was mentioned Here. As I said you have to work with ".xlsx" (you can work with other excel formats but you'll get the same message about the file format when you open the file). So you can create the Excel file in temp folder with EPPlus using the DataTable as sorce and send the temp file by email... something like this for example:

public static void SendEmailWithExcelAttachment(DataTable dt)
    {
        try
        {
            string smptHost = smptTuple.Item1;
            MailMessage mailMsg = new MailMessage();

            string temp = Path.GetTempPath(); // Get %TEMP% path
            string file = "fileNameHere.xlsx";
            string path = Path.Combine(temp, file); // Get the whole path to the file

            FileInfo fi = new FileInfo(path);
            using (ExcelPackage pck = new ExcelPackage(fi))
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Table");
                ws.Cells["A1"].LoadFromDataTable(dt, true);
                pck.Save();
            }
            mailMsg.Attachments.Add(new System.Net.Mail.Attachment(path, "application/vnd.ms-excel"));
            try
            {
                //send email
                smtp.Send(mailMsg);
            }
            catch (Exception)
            {
                //do smth..
            }
            finally
            {
                File.Delete(path);
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

I created a simple DataTable, sent it to myself in ".xlsx" format and was able to open it without any "Format warnings". I hope that helps.

score 0 · Answer 3 · answered May 08 '17 at 08:47

Solution to the warning / error “The file you are trying to open is in a different format than specified by the file extension”

Cause :

This happens because in the traditional Export to Excel method, the GridView is first converted to an HTML string and then that HTML string is exported to Excel. Thus originally it is not an Excel file hence the Excel Application throws the warning / error “The file you are trying to open is in a different format than specified by the file extension”.

Solution :

The solution to this problem is using ClosedXML library which is wrapper over the DocumentFormat.OpenXml library.

Dependency : OpenXml SDK 2.0 must be installed in system

You can get more help here :)

A link to a solution is welcome, but please ensure your answer is useful without it: [add context around the link](//meta.stackexchange.com/a/8259) so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. [Answers that are little more than a link may be deleted](/help/deleted-answers). — Jim G., Aug 31 '22 at 18:21

Error message wile trying to open .xls file

3 Answers3

Cause :

Solution :