Question How To Format Table In The Body Of Mail

raushanaj5

Member
Joined
Apr 15, 2017
Messages
7
Programming Experience
Beginner
I am using C# for my problem.
I have an Excel file which has many worksheets. From "Fisrt Sheet", I am looking for a character "x" which will be present in some cells of a specific column (occurence of "x" will be in one specific column only, in different cells of that column). I am looking for "x" and extracting the corresponding row's details in a generic list (with naming the headers of extracted field). Now, I have to send this generic list in "tabular format" in body of the mail via Outlook.
I am getting the output but not in proper format. My code is extracting the information but I can't see any table borders and also the table headers are missing under which the extracted data should be filled.
Table header information should be like this:
Column 1 - MemoName
Column 1 - Type
Column 1 - Ext
Column 1 - Seller
Column 1 - Warehouse
Also the header should be in black color and the extracted details in red.
Please help me with my problem

C#:
[LIST=|INDENT=1]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] System;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] System.Collections.Generic;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] System.Linq;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] System.Text;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] System.IO;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] System.Data;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] Excel = Microsoft.Office.Interop.Excel;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]using[/B][/COLOR] Outlook = Microsoft.Office.Interop.Outlook;  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]namespace[/B][/COLOR] xlsm  [/COLOR]
[*][COLOR=black]{  [/COLOR]
[*][COLOR=black]    [COLOR=#006699][B]class[/B][/COLOR] New  [/COLOR]
[*][COLOR=black]    {  [/COLOR]
[*][COLOR=black]        [COLOR=#006699][B]static[/B][/COLOR] [COLOR=#006699][B]void[/B][/COLOR] Main(sting[] args)  [/COLOR]
[*][COLOR=black]        {  [/COLOR]
[*][COLOR=black]        sting st;   [/COLOR]
[*][COLOR=black]            [COLOR=#006699][B]long[/B][/COLOR] rCnt, cCnt;  [/COLOR]
[*][COLOR=black]            [COLOR=#006699][B]long[/B][/COLOR] rows = 0, columns = 0;  [/COLOR]
[*][COLOR=black]        Excel.Application xlApp;  [/COLOR]
[*][COLOR=black]        Excel.Workbook xlWorkBook;  [/COLOR]
[*][COLOR=black]        Excel.Worksheet xlWorkSheet;  [/COLOR]
[*][COLOR=black]        Excel.Range rng;  [/COLOR]
[*][COLOR=black]        xlApp = [COLOR=#006699][B]new[/B][/COLOR] Excel.Application();  [/COLOR]
[*][COLOR=black]        xlWorkBook = xlApp.Workbooks.Open(@[COLOR=blue]"F:\Doc_Excel"[/COLOR], 0, [COLOR=#006699][B]true[/B][/COLOR], 5, [COLOR=blue]""[/COLOR], [COLOR=blue]""[/COLOR], [COLOR=#006699][B]true[/B][/COLOR], Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, [COLOR=blue]"\t"[/COLOR], [COLOR=#006699][B]false[/B][/COLOR], [COLOR=#006699][B]false[/B][/COLOR], 0, [COLOR=#006699][B]true[/B][/COLOR], 1, 0);  [/COLOR]
[*][COLOR=black]        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets[[COLOR=blue]"First Sheet"[/COLOR]];  [/COLOR]
[*][COLOR=black]        rng = xlWorkSheet.UsedRange;  [/COLOR]
[*][COLOR=black]        rows = rng.Rows.Count;  [/COLOR]
[*][COLOR=black]        columns = rng.Columns.Count;  [/COLOR]
[*][COLOR=black]        List<Memo> lst = [COLOR=#006699][B]new[/B][/COLOR] List<Memo>();  [/COLOR]
[*][COLOR=black]        [COLOR=#006699][B]for[/B][/COLOR] (rCnt = 1; rCnt < rows; rCnt++)  [/COLOR]
[*][COLOR=black]        {  [/COLOR]
[*][COLOR=black]            [COLOR=#006699][B]for[/B][/COLOR] (cCnt = 1; cCnt < columns; cCnt++)  [/COLOR]
[*][COLOR=black]            {  [/COLOR]
[*][COLOR=black]                [COLOR=#006699][B]if[/B][/COLOR] ((rng.Cells[rCnt, cCnt] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2 != [COLOR=#006699][B]null[/B][/COLOR])  [/COLOR]
[*][COLOR=black]                {  [/COLOR]
[*][COLOR=black]                    st = (rng.Cells[rCnt, cCnt] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2.Tosting();  [/COLOR]
[*][COLOR=black]                    [COLOR=#006699][B]if[/B][/COLOR] (st == [COLOR=blue]"x"[/COLOR])  [/COLOR]
[*][COLOR=black]                    {  [/COLOR]
[*][COLOR=black]                        Memo ms = [COLOR=#006699][B]new[/B][/COLOR] Memo();  [/COLOR]
[*][COLOR=black]                        ms.MemoName = (rng.Cells[rCnt, 1] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2.Tosting();  [/COLOR]
[*][COLOR=black]                        ms.Type = (rng.Cells[rCnt, 2] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2.Tosting();  [/COLOR]
[*][COLOR=black]                        ms.Ext = (rng.Cells[rCnt, 3] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2.Tosting();  [/COLOR]
[*][COLOR=black]                        ms.Seller = (rng.Cells[rCnt, 4] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2.Tosting();  [/COLOR]
[*][COLOR=black]                        ms.Warehouse = (rng.Cells[rCnt, 5] [COLOR=#006699][B]as[/B][/COLOR] Excel.rng).Value2.Tosting();  [/COLOR]
[*][COLOR=black]                        lst.Add(ms);    [/COLOR]
[*][COLOR=black]                    }  [/COLOR]
[*][COLOR=black]                }  [/COLOR]
[*][COLOR=black]            }  [/COLOR]
[*][COLOR=black]        }  [/COLOR]
[*][COLOR=black]        [COLOR=#006699][B]try[/B][/COLOR]  [/COLOR]
[*][COLOR=black]        {  [/COLOR]
[*][COLOR=black]            Outlook.Application oApp = [COLOR=#006699][B]new[/B][/COLOR] Outlook.Application();                 [/COLOR]
[*][COLOR=black]            Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);  [/COLOR]
[*][COLOR=black]            StringBuilder sb = [COLOR=#006699][B]new[/B][/COLOR] StringBuilder();  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<table>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            [COLOR=#006699][B]foreach[/B][/COLOR] (var row [COLOR=#006699][B]in[/B][/COLOR] lst)  [/COLOR]
[*][COLOR=black]           {  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<tr>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<td>"[/COLOR] + row.MemoName + [COLOR=blue]"</td>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<td>"[/COLOR] + row.Type + [COLOR=blue]"</td>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<td>"[/COLOR] + row.Ext + [COLOR=blue]"</td>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<td>"[/COLOR] + row.Seller + [COLOR=blue]"</td>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"<td>"[/COLOR] + row.Warehouse + [COLOR=blue]"</td>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"</tr>"[/COLOR]);  [/COLOR]
[*][COLOR=black]           }  [/COLOR]
[*][COLOR=black]            sb.Append([COLOR=blue]"</table>"[/COLOR]);  [/COLOR]
[*][COLOR=black]            oMsg.HTMLBody = sb.ToString();  [/COLOR]
[*][COLOR=black]            oMsg.Subject = [COLOR=blue]"Memo contents as required."[/COLOR];                 [/COLOR]
[*][COLOR=black]            Outlook.Recipients oRecims = (Outlook.Recipients)oMsg.Recipients;                 [/COLOR]
[*][COLOR=black]            Outlook.Recipient oRecip = (Outlook.Recipient)oRecims.Add([COLOR=blue]"abc@xyz.com"[/COLOR]);  [/COLOR]
[*][COLOR=black]            oRecip.Resolve();                 [/COLOR]
[*][COLOR=black]            oMsg.Send();  [/COLOR]
[*][COLOR=black]            oRecip = [COLOR=#006699][B]null[/B][/COLOR];  [/COLOR]
[*][COLOR=black]            oRecims = [COLOR=#006699][B]null[/B][/COLOR];  [/COLOR]
[*][COLOR=black]            oMsg = [COLOR=#006699][B]null[/B][/COLOR];  [/COLOR]
[*][COLOR=black]            oApp = [COLOR=#006699][B]null[/B][/COLOR];  [/COLOR]
[*][COLOR=black]        }  [/COLOR]
[*][COLOR=black]        [COLOR=#006699][B]catch[/B][/COLOR] (Exception ex)  [/COLOR]
[*][COLOR=black]        {  [/COLOR]
[*][COLOR=black]        }  [/COLOR]
[*][COLOR=black]        xlWorkBook.close([COLOR=#006699][B]true[/B][/COLOR], [COLOR=#006699][B]null[/B][/COLOR], [COLOR=#006699][B]null[/B][/COLOR]);  [/COLOR]
[*][COLOR=black]        xlApp.Quit();  [/COLOR]
[*][COLOR=black]        Marshal.ReleaseComObject(xlWorkSheet);  [/COLOR]
[*][COLOR=black]        Marshal.ReleaseComObject(xlWorkBook);  [/COLOR]
[*][COLOR=black]        Marshal.ReleaseComObject(xlApp);  [/COLOR]
[*][COLOR=black]    }          [/COLOR]
[*][COLOR=black]}  [/COLOR]
[*][COLOR=black][COLOR=#006699][B]public[/B][/COLOR] [COLOR=#006699][B]class[/B][/COLOR] Memo  [/COLOR]
[*][COLOR=black]{  [/COLOR]
[*][COLOR=black]    [COLOR=#006699][B]public[/B][/COLOR] [COLOR=#006699][B]string[/B][/COLOR] MemoName { [COLOR=#006699][B]get[/B][/COLOR]; [COLOR=#006699][B]set[/B][/COLOR]; }  [/COLOR]
[*][COLOR=black]    [COLOR=#006699][B]public[/B][/COLOR] [COLOR=#006699][B]string[/B][/COLOR] Type { [COLOR=#006699][B]get[/B][/COLOR]; [COLOR=#006699][B]set[/B][/COLOR]; }  [/COLOR]
[*][COLOR=black]    [COLOR=#006699][B]public[/B][/COLOR] [COLOR=#006699][B]string[/B][/COLOR] Ext { [COLOR=#006699][B]get[/B][/COLOR]; [COLOR=#006699][B]set[/B][/COLOR]; }  [/COLOR]
[*][COLOR=black]    [COLOR=#006699][B]public[/B][/COLOR] [COLOR=#006699][B]string[/B][/COLOR] Seller { [COLOR=#006699][B]get[/B][/COLOR]; [COLOR=#006699][B]set[/B][/COLOR]; }  [/COLOR]
[*][COLOR=black]    [COLOR=#006699][B]public[/B][/COLOR] [COLOR=#006699][B]string[/B][/COLOR] Warehouse { [COLOR=#006699][B]get[/B][/COLOR]; [COLOR=#006699][B]set[/B][/COLOR]; }  [/COLOR]
[*][COLOR=black]}  [/COLOR]
[/LIST]

 
You've made you code hard to read. Please post it as plain text - not HTML or other formatted text - inside formatting tags like this:
[xcode=c#]your code here[/xcode]
 
  • using System;
  • using System.Collections.Generic;
  • using System.Linq;
  • using System.Text;
  • using System.IO;
  • using System.Data;
  • using Excel = Microsoft.Office.Interop.Excel;
  • using Outlook = Microsoft.Office.Interop.Outlook;
  • namespace xlsm
  • {
  • class New
  • {
  • static void Main(sting[] args)
  • {
  • sting st;
  • long rCnt, cCnt;
  • long rows = 0, columns = 0;
  • Excel.Application xlApp;
  • Excel.Workbook xlWorkBook;
  • Excel.Worksheet xlWorkSheet;
  • Excel.Range rng;
  • xlApp = new Excel.Application();
  • xlWorkBook = xlApp.Workbooks.Open(@"F:\Doc_Excel", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
  • xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["First Sheet"];
  • rng = xlWorkSheet.UsedRange;
  • rows = rng.Rows.Count;
  • columns = rng.Columns.Count;
  • List<Memo> lst = new List<Memo>();
  • for (rCnt = 1; rCnt < rows; rCnt++)
  • {
  • for (cCnt = 1; cCnt < columns; cCnt++)
  • {
  • if ((rng.Cells[rCnt, cCnt] as Excel.rng).Value2 != null)
  • {
  • st = (rng.Cells[rCnt, cCnt] as Excel.rng).Value2.Tosting();
  • if (st == "x")
  • {
  • Memo ms = new Memo();
  • ms.MemoName = (rng.Cells[rCnt, 1] as Excel.rng).Value2.Tosting();
  • ms.Type = (rng.Cells[rCnt, 2] as Excel.rng).Value2.Tosting();
  • ms.Ext = (rng.Cells[rCnt, 3] as Excel.rng).Value2.Tosting();
  • ms.Seller = (rng.Cells[rCnt, 4] as Excel.rng).Value2.Tosting();
  • ms.Warehouse = (rng.Cells[rCnt, 5] as Excel.rng).Value2.Tosting();
  • lst.Add(ms);
  • }
  • }
  • }
  • }
  • try
  • {
  • Outlook.Application oApp = new Outlook.Application();
  • Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);
  • StringBuilder sb = new StringBuilder();
  • sb.Append("<table>");
  • foreach (var row in lst)
  • {
  • sb.Append("<tr>");
  • sb.Append("<td>" + row.MemoName + "</td>");
  • sb.Append("<td>" + row.Type + "</td>");
  • sb.Append("<td>" + row.Ext + "</td>");
  • sb.Append("<td>" + row.Seller + "</td>");
  • sb.Append("<td>" + row.Warehouse + "</td>");
  • sb.Append("</tr>");
  • }
  • sb.Append("</table>");
  • oMsg.HTMLBody = sb.ToString();
  • oMsg.Subject = "Memo contents as required.";
  • Outlook.Recipients oRecims = (Outlook.Recipients)oMsg.Recipients;
  • Outlook.Recipient oRecip = (Outlook.Recipient)oRecims.Add("abc@xyz.com");
  • oRecip.Resolve();
  • oMsg.Send();
  • oRecip = null;
  • oRecims = null;
  • oMsg = null;
  • oApp = null;
  • }
  • catch (Exception ex)
  • {
  • }
  • xlWorkBook.close(true, null, null);
  • xlApp.Quit();
  • Marshal.ReleaseComObject(xlWorkSheet);
  • Marshal.ReleaseComObject(xlWorkBook);
  • Marshal.ReleaseComObject(xlApp);
  • }
  • }
  • public class Memo
  • {
  • public string MemoName { get; set; }
  • public string Type { get; set; }
  • public string Ext { get; set; }
  • public string Seller { get; set; }
  • public string Warehouse { get; set; }
  • }
 
Now, I can make the table and fill the extracted data in rows, but unable to make column headers. Please help me with making column headers and making column headers Black in colour and Other extracted data in red.
Table header information should be like this:
Column 1 - MemoName
Column 2 - Type
Column 3 - Ext
Column 4 - Seller
Column 5 - Warehouse

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Data;
    using Excel = Microsoft.Office.Interop.Excel;
    using Outlook = Microsoft.Office.Interop.Outlook;
 
    namespace xlsm
    {
        class New
        {
            static void Main(sting[] args)
            {
            sting st; 
                long rCnt, cCnt;
                long rows = 0, columns = 0;
 
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range rng;
 
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(@"F:\Doc_Excel", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets["First Sheet"];
 

            rng = xlWorkSheet.UsedRange;
            rows = rng.Rows.Count;
            columns = rng.Columns.Count;
 
            List<Memo> lst = new List<Memo>();
 
            for (rCnt = 1; rCnt < rows; rCnt++)
            {
                for (cCnt = 1; cCnt < columns; cCnt++)
                {
 
                    if ((rng.Cells[rCnt, cCnt] as Excel.rng).Value2 != null)
                    {
                        st = (rng.Cells[rCnt, cCnt] as Excel.rng).Value2.Tosting();
                        if (st == "x")
                        {
                            Memo ms = new Memo();
 
                            ms.MemoName = (rng.Cells[rCnt, 1] as Excel.rng).Value2.Tosting();
                            ms.Type = (rng.Cells[rCnt, 2] as Excel.rng).Value2.Tosting();
                            ms.Ext = (rng.Cells[rCnt, 3] as Excel.rng).Value2.Tosting();
                            ms.Seller = (rng.Cells[rCnt, 4] as Excel.rng).Value2.Tosting();
                            ms.Warehouse = (rng.Cells[rCnt, 5] as Excel.rng).Value2.Tosting();
                            lst.Add(ms);  
 
                        }
                    }
 
                }
            }
 
            try
            {
 
                Outlook.Application oApp = new Outlook.Application();               
                Outlook.MailItem oMsg = (Outlook.MailItem)oApp.CreateItem(Outlook.OlItemType.olMailItem);
                var sb = new StringBuilder();
                sb.AppendLine("<head><style> table {font-family: arial, sans-serif; border-collapse: collapse; width: 100%;} " +                    "td, th {border: 1px solid #dddddd;text-align: left; padding: 8px;} </style></head>");
                sb.AppendLine("<body>");
                sb.AppendLine("<table>");
                foreach (var row in lst)
               {
                sb.Append("<tr>");
                sb.Append("<td>" + row.MemoName + "</td>");
                sb.Append("<td>" + row.Type + "</td>");
                sb.Append("<td>" + row.Ext + "</td>");
                sb.Append("<td>" + row.Seller + "</td>");
                sb.Append("<td>" + row.Warehouse + "</td>");
                sb.Append("</tr>");
               }
                sb.AppendLine("</table>");                sb.AppendLine("</body>");
                oMsg.HTMLBody = sb.ToString();
                oMsg.Subject = "Memo contents as required.";               
                Outlook.Recipients oRecims = (Outlook.Recipients)oMsg.Recipients;               
                Outlook.Recipient oRecip = (Outlook.Recipient)oRecims.Add("abc@xyz.com");
                oRecip.Resolve();               
                oMsg.Send();
 
                oRecip = null;
                oRecims = null;
                oMsg = null;
                oApp = null;
            }
            catch (Exception ex)
            {
            }
            xlWorkBook.close(true, null, null);
            xlApp.Quit();
 
            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
        }        
 
    }
    public class Memo
    {
        public string MemoName { get; set; }
        public string Type { get; set; }
        public string Ext { get; set; }
        public string Seller { get; set; }
        public string Warehouse { get; set; }
    }
 
I said to post your code as plain text inside code formatting tags. First you posted it as formatted text inside code formatting tags and then you posted it as plain text without code formatting tags. That's not a very good record. I have edited your last code snippet so that it is posted properly. Please take a look so that you can post code snippets correctly in future. If we can read your code easily then you're more likely to get the help you want, so it's for your own sake.
 
Back
Top Bottom