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
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]