先看一下手动发送邮件内容加入表格操作(下图所示),直接复制Excel内容,再粘贴到邮件内容中，就是这么便捷，如果我们想自动发送邮件,也实现同样的效果如果实现呢，在这里介绍2种方法:
一.读取Excel转为HTML
1.C#读取Excel转为HTML代码
/// <summary> /// 测试用拼接Html /// </summary> public void Test() { string Body = @"你好： 附件为【{FileName}】资料,请注意查收. {Table} {Data} ============================ 此为系统自动发送邮件,请勿回复 pcbren致力于PCB自动化研究 ============================ "; string FileName = "pcbren"; Body = Body.Replace("{FileName}", FileName); Body = Body.Replace("{Data}", DateTime.Now.ToString("yyyy-MM-dd")); Body = Body.Replace(" ", "<br>"); string table = ReadXLS(@"E:AA est.xls"); Body = Body.Replace("{Table}", table); Body = @" <html> <style> table,table tr th, table tr td { border:1px solid #0094ff; } table { min-height: 25px; line-height: 25px; text-align: center; border-collapse: collapse;} </style> <body> {body} </body> </html> ".Replace("{body}", Body); ; } /// <summary> /// 读取Excel表格内容 /// </summary> /// <param name="xlsPath"></param> /// <returns></returns> public string ReadXLS(string xlsPath) { object missing = System.Reflection.Missing.Value; Excel.Application app = new Excel.Application(); app.Visible = false; app.UserControl = true; Excel.Workbook wb = app.Application.Workbooks.Open(xlsPath, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing); Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1); int rowsint = ws.UsedRange.Cells.Rows.Count; //自动发邮件 表格行数应该固定更好 int columnsint = ws.UsedRange.Cells.Columns.Count;//自动发邮件 表格列数应该固定更好 StringBuilder sbHtml = new StringBuilder(1000); sbHtml.AppendLine("<table>"); for (int x = 1; x <= rowsint; x++) { string trLine = " <tr>"; int columnsSum = 0; for (int y = 1; y <= columnsint; y++) { trLine += (x == 1) ? " <th>" : " <td>"; var tt = (Excel.Range)ws.Cells[x, y]; string CellValue = (tt.Value ?? "").ToString().Trim(); trLine += CellValue; trLine += (x == 1) ? "</th>" : "</td>"; if (!string.IsNullOrEmpty(CellValue)) columnsSum++; } trLine += " </tr>"; if (columnsSum > 0) sbHtml.AppendLine(trLine); } sbHtml.AppendLine("</table>"); return sbHtml.ToString(); }
2.转换后的HTML格式
<html> <head> <style> table,table tr th, table tr td { border:1px solid #0094ff; } table { min-height: 25px; line-height: 25px; text-align: center; border-collapse: collapse;} </style> </head> <body> Dear: <br /> Please see attached working gerble files of PN:pcbren11111 11-20 , only for your reference . <br /> <table> <tbody> <tr> <th>编码 </th> <th>型号 </th> <th>信息说明 </th> <th>备注 </th> </tr> <tr> <td>1 </td> <td>AAAA </td> <td>A001-pcbren11111.rar </td> <td>测试 </td> </tr> <tr> <td>2 </td> <td>BBBB </td> <td>A001-pcbren22222.rar </td> <td>测试 </td> </tr> <tr> <td>3 </td> <td>CCCC </td> <td>A001-pcbren33333.rar </td> <td>测试 </td> </tr> <tr> <td>4 </td> <td>DDDD </td> <td>A001-pcbren44444.rar </td> <td>测试 </td> </tr> <tr> <td>5 </td> <td>EEEE </td> <td>A001-pcbren55555.rar </td> <td>测试 </td> </tr> <tr> <td>6 </td> <td>FFFF </td> <td>A001-pcbren66666.rar </td> <td>测试 </td> </tr> </tbody> </table> <br />============================= <br />此为系统自动发送邮件,请勿回复 <br />pcbren致力于PCB自动化研究 <br />============================= <br /> </body> </html>
3.HTML格式转换后效果
二.SQL转为HTML发送
1.SQL转为HTML并发送邮件
DECLARE @SQL VARCHAR(MAX) DECLARE @HtmlBody VARCHAR(MAX) DECLARE @HtmlTable VARCHAR(MAX) SET @SQL = 'SELECT TechName,ItemName,ItemPara FROM FP_EMS_DB.dbo.V_ppegeneral WHERE pdctno = ''2V011Z30A4'' AND TechNo = ''CC_01'' ORDER BY ItemNo ' SET @HtmlTable = dbo.ExecSQL2Html(@SQL,0) set @HtmlBody = '你好：</br>' + @HtmlTable + '</br>' + ' ==============</br> 发送邮件测试</br> ==============</br> ' exec msdb.dbo.sp_send_dbmail @profile_name = 'pcbren邮箱', --配置名称 @recipients = 'pcbren@aliyun.com', --收件名称 @body_format = 'HTML', --内容格式 @subject = 'pcbren', @body = @HtmlBody
2.转换后的HTML格式
你好：</br><html ><head></head><body> <style> table,table tr th, table tr td { border:1px solid #0094ff;padding: 0px 10px } table { min-height: 25px; line-height: 25px; text-align: center; border-collapse: collapse;} th {background:green;color:white;} </style> <table><tr><th>TechName</th><th>ItemName</th><th>ItemPara</th></tr><tr><td>开料</td><td>综合利用率是否为最高</td><td>/</td></tr><tr><td>开料</td><td>综合利用率</td><td>68.36</td></tr><tr><td>开料</td><td>纬向余料</td><td>0</td></tr><tr><td>开料</td><td>经向余料</td><td>0</td></tr><tr><td>开料</td><td>是否为小交货面积拼板</td><td>n</td></tr><tr><td>开料</td><td>纬向尺寸</td><td>24</td></tr><tr><td>开料</td><td>是否为阴阳铜结构</td><td>N</td></tr><tr><td>开料</td><td>是否横竖开料</td><td>N</td></tr><tr><td>开料</td><td>生产尺寸长</td><td>24</td></tr><tr><td>开料</td><td>生产尺寸宽</td><td>18</td></tr><tr><td>开料</td><td>拼板利用率</td><td>68.36</td></tr><tr><td>开料</td><td>开料图纸</td><td>/</td></tr><tr><td>开料</td><td>是否顾客指定板材</td><td>N</td></tr><tr><td>开料</td><td>开料数</td><td>4</td></tr><tr><td>开料</td><td>大料经向尺寸</td><td>36</td></tr><tr><td>开料</td><td>大料纬向尺寸</td><td>48</td></tr><tr><td>开料</td><td>成品尺寸长</td><td>12</td></tr><tr><td>开料</td><td>成品尺寸宽</td><td>13.5</td></tr><tr><td>开料</td><td>是否为PTFE板材</td><td>N</td></tr><tr><td>开料</td><td>交货拼板个数</td><td>1</td></tr><tr><td>开料</td><td>生产拼板个数</td><td>1176</td></tr><tr><td>开料</td><td>交货单位</td><td>U</td></tr><tr><td>开料</td><td>是否为凹蚀板材</td><td>N</td></tr></table></body></html></br> ==============</br> 发送邮件测试</br> ==============</br>
3.收到邮件HTML效果
