锐客网

  1. 首页 > it技术 > >

.NET|c#以单据标准格式写EXCEL并发送邮件出去

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Net.Mail;
namespace EXCEL操作
{
public class CelValues
{
public string CelValue;
public int CelRow;
public int CelCol;
}
public classEXCELFunction
{
///
/// 每页显示单头数
///
private int _colsParentRow;
public EXCELFunction (int colsParentRow)
{
_colsParentRow = colsParentRow;
}
/// 取列序号对应的列标识
///
/// 列序号，从1开始
///
public string GetCelColumnName(int aColIndex)
{
string result = string.Empty;
string temp = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (aColIndex > 0)
{
aColIndex = aColIndex - 1;
int index = aColIndex % 26;
int page = (int)(aColIndex / 26);

if (page == 0)
{
result = Convert.ToString(temp[index]);
}
else
{
result = Convert.ToString(temp[page-1]) + Convert.ToString(temp[index ]);
}
}
return result;
}
///
/// 将数据写入EXCEL单元格，形成单据格式
///
/// 单据标题描述
/// 单头列表
/// 明细数据列标题
///单据明细数据
///单据脚列表
public void WriteCellData(string aBillTitle, System.Data.DataTable aDtBillHead,System.Data.DataTable aDtColsHeader ,System.Data.DataTable aDtBillDetail,System.Data.DataTable aDtBillFoot)
{
int colsParentRow = _colsParentRow;
string billTitle = aBillTitle;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
if (app == null)
{
MessageBox.Show("Excel无法启动");
return;
}
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
//Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Workbook wb=app.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
//写入单头
//dtBillHead 单头内容，行序号
System.Data.DataTable dtBillHead = aDtBillHead;
if ((dtBillHead != null) && (dtBillHead.Rows.Count > 0))
{
for (int k = 0; k < dtBillHead.Rows.Count; k++)
{
int rowIndex = (int)(k / colsParentRow) + 1;
int colIndex = (k % colsParentRow) + 1;
int t = colsParentRow;
if ((dtBillHead.Rows.Count % colsParentRow) != 0)
{
if (rowIndex == Convert.ToInt16(dtBillHead.Rows.Count / colsParentRow) + 1)
{
t = dtBillHead.Rows.Count % colsParentRow;
}
}
rowIndex += billTitle != "" ? 1 : 0;
Microsoft.Office.Interop.Excel.Range rBillHead = ws.get_Range(GetCelColumnName(colIndex) + rowIndex.ToString(), GetCelColumnName(t) + rowIndex.ToString()); //列标题区域
object[] objBillHead = { dtBillHead.Rows[k]["HeadValue"].ToString().Trim() };
rBillHead.Value2 = objBillHead;
rBillHead = rBillHead.get_Resize(1, 1);
rBillHead.EntireColumn.AutoFit();
}
}
IList itemHeader = new List();
for (int i = 0; i < aDtColsHeader.Rows.Count; i++)
{
itemHeader.Add(aDtColsHeader.Rows[i][0].ToString().Trim());
}
int beginRowNo = 1;
if (billTitle != "")
{
beginRowNo = 2;
}
if (dtBillHead != null && dtBillHead.Rows.Count > 0)
{
int k = dtBillHead.Rows.Count / colsParentRow;
k += dtBillHead.Rows.Count % colsParentRow == 0 ? 0 : 1;
beginRowNo += k;
}
Microsoft.Office.Interop.Excel.Range rHeader = ws.get_Range("A" + beginRowNo.ToString(), GetCelColumnName(itemHeader.Count) + beginRowNo.ToString()); //列标题区域
object[] objHeader = new object[itemHeader.Count];
for (int i = 0; i < itemHeader.Count; i++)
{
objHeader[i] = itemHeader[i];
}
rHeader.Value2 = objHeader;
rHeader = rHeader.get_Resize(itemHeader.Count, itemHeader.Count);
rHeader.EntireColumn.AutoFit();
//明细数据
System.Data.DataTable dtBillDetail = aDtBillDetail;
//SqlDataAdapter da = new SqlDataAdapter("select *,'ddd' as afrom ClientBussPerson", MyConnectionString);
//try
//{
//da.Fill(dtBillDetail);
//}
//catch (Exception ex)
//{
//MessageBox.Show("Operation failed: " + ex.ToString(), " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
//return;
//}

IList itemDetail = new List();
for (int k = 0; k < dtBillDetail.Rows.Count; k++)
{
for (int c = 0; c < dtBillDetail.Columns.Count; c++)
{
itemDetail.Add(new CelValues { CelValue = https://www.it610.com/article/dtBillDetail.Rows[k][c].ToString().Trim(), CelCol = (c + 1), CelRow = k + 1 });
}
}
for (int i = 0; i < itemDetail.Count; i++)
{
int col = itemDetail[i].CelCol;
int row = itemDetail[i].CelRow;
if (billTitle != "") { row = row + beginRowNo; } else { row = row + beginRowNo; }
Microsoft.Office.Interop.Excel.Range rDetail = ws.get_Range(GetCelColumnName(col) + Convert.ToString(row), GetCelColumnName(col) + Convert.ToString(row));
//rDetail.Borders.LineStyle= 0;
object[] objDetail = { itemDetail[i].CelValue };
rDetail.Value2 = objDetail;
rDetail = ws.get_Range(GetCelColumnName(col) + Convert.ToString(row), Missing.Value);
rDetail = rDetail.get_Resize(1, 1);
rDetail.Value2 = objDetail;
rDetail.EntireColumn.AutoFit();
//rDetail.Borders.LineStyle = 0;
rDetail.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); //给单元格加
}

//设置单据标题名
Microsoft.Office.Interop.Excel.Range rTitle;
rTitle = ws.get_Range("A1", "A1"); //列标题区域
rTitle.NumberFormatLocal = "@ ";
rTitle.Font.Size = 15; //设置字体大小
rTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式
rTitle.EntireColumn.AutoFit(); //自动调整列宽
rTitle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
object[] objTitle = { billTitle };
rTitle.Value2 = objTitle;
rTitle = rTitle.get_Resize(itemHeader.Count, itemHeader.Count);
rTitle.EntireColumn.AutoFit();
rTitle = ws.get_Range("A1", GetCelColumnName(itemHeader.Count) + "1"); //取得合并的区域
rTitle.MergeCells = true;
rTitle.Merge(0);
//rTitle.Borders.LineStyle= 0;
//rTitle.Borders.LineStyle = 0.5; //设置单元格边框的粗细
//设置单脚
System.Data.DataTable dtBillFoot = aDtBillFoot;
if (dtBillFoot != null && dtBillFoot.Rows.Count > 0)
{
int k = dtBillFoot.Rows.Count / colsParentRow;
k += dtBillFoot.Rows.Count % colsParentRow == 0 ? 0 : 1;
beginRowNo += k;
}
if ((dtBillFoot != null) && (dtBillFoot.Rows.Count > 0))
{
for (int k = 0; k < dtBillFoot.Rows.Count; k++)
{
int rowIndex = (int)(k / colsParentRow) + 1;
int colIndex = (k % colsParentRow) + 1;
int t = colsParentRow;
if ((dtBillFoot.Rows.Count % colsParentRow) != 0)
{
if (rowIndex == Convert.ToInt16(dtBillFoot.Rows.Count / colsParentRow) + 1)
{
t = dtBillFoot.Rows.Count % colsParentRow;
}
}
rowIndex += billTitle != "" ? 1 : 0;
Microsoft.Office.Interop.Excel.Range rBillFoot = ws.get_Range(GetCelColumnName(colIndex) + (rowIndex + beginRowNo + dtBillDetail.Rows.Count - 2).ToString(), GetCelColumnName(t) + (rowIndex + beginRowNo + dtBillDetail.Rows.Count - 2).ToString()); //列标题区域
object[] objBillFoot = { dtBillHead.Rows[k]["HeadValue"].ToString().Trim() };
rBillFoot.Value2 = objBillFoot;
rBillFoot = rBillFoot.get_Resize(1, 1);
rBillFoot.EntireColumn.AutoFit();
}
}
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wb.Save();
//保存excel文件
app.Save(System.IO.Directory.GetCurrentDirectory()+"\\OutPut.xls");
}
catch
{
}
finally
{
//确保Excel进程关闭
app.Quit();
app = null;
}
}

///
/// 发送电子邮件
///
/// SMTP事务的主机的名称,如:smtp.126.com
/// SMTP事务的主机的端口,默认25
/// 收件人地址
/// >发件人地址
/// 发件人名称
/// 邮件标题
/// 发件人帐号
/// 发件人密码
/// 邮件内容
/// 附件路径及名称
public string SendEMAIL(string asmtpHost, int asmtPort, string aRecieveAddr, string aSendAddr, string aSendName, string aSubject, string aSendUID, string aSendPWD, string amailContent, IList aattachFile)
{
string result = string.Empty;
try
{
System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
msg.To.Add(aRecieveAddr); //收件人
//发件人信息
msg.From = new MailAddress(aSendAddr, aSendName, System.Text.Encoding.UTF8);
msg.Subject = aSubject; //邮件标题
msg.SubjectEncoding = System.Text.Encoding.UTF8; //标题编码
msg.Body = amailContent; //邮件主体
msg.BodyEncoding = System.Text.Encoding.UTF8;
msg.IsBodyHtml = true; //是否HTML
msg.Priority = System.Net.Mail.MailPriority.High; //优先级
//构造添加附件
foreach (string p in aattachFile)
{
msg.Attachments.Add(new Attachment(p)); //增加附件
}
SmtpClient client = new SmtpClient();
//设置GMail邮箱和密码
client.Credentials = new System.Net.NetworkCredential(aSendUID, aSendPWD);
client.Port = asmtPort;
client.Host = asmtpHost; // "smtp.126.com";
client.EnableSsl = true;
object userState = msg;
try
{
client.Send(msg);
}
catch (Exception ex)
{
result = ex.Message;
}
}
catch (Exception e)
{
result = e.Message;
}
return result;
}

}
}


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
namespace EXCEL操作
{

public partial class Form1 : Form
{
EXCELFunction excelFun;
string MyConnectionString = "Server=127.0.0.1; Database=master; User ID=sa; Password=newman2007; Connect Timeout=20; Pooling=True; Min Pool Size=1; Max Pool Size=100; Persist Security Info=False; Asynchronous Processing=true; Integrated Security=false; ";
SqlConnection con;
public Form1()
{
InitializeComponent();
}
///
/// 取列序号对应的列标识
///
/// 列序号，从1开始
///
public string GetCelColumnName(int aColIndex)
{
string result = string.Empty;
string temp = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
if (aColIndex > 0)
{
aColIndex = aColIndex - 1;
int index = aColIndex % 26;
int page = (int)(aColIndex / 26);

if (page == 0)
{
result = Convert.ToString(temp[index]);
}
else
{
result = Convert.ToString(temp[page-1]) + Convert.ToString(temp[index ]);
}
}
return result;
}
private System.Data.DataTable GetColsHeader()
{
System.Data.DataTable result = new System.Data.DataTable();
SqlDataAdapter da = new SqlDataAdapter("select '标题1' ColsHeader union all select '标题2'union all select '标题3'union all select '标题4'union all select '标题5'union all select '标题6'union all select '标题7'union all select '标题8'union all select '标题9'union all select '标题10'union all select '标题11'union all select '标题12' ", MyConnectionString);
try
{
da.Fill(result);
}
catch
{
}
return result;
}
private System.Data.DataTable GetBillHead()
{
System.Data.DataTable result = new System.Data.DataTable();
SqlDataAdapter da = new SqlDataAdapter("select '出货仓库：00' as HeadValue union all select '出货门店：0000配送中心' as HeadValue union all select '出货金额：23456.12' as HeadValue union all select '收货仓库：01门店仓库' as HeadValue ", MyConnectionString);
try
{
da.Fill(result);
}
catch
{
}
return result;
}
private System.Data.DataTable GetBillDetail()
{
System.Data.DataTable result = new System.Data.DataTable();
SqlDataAdapter da = new SqlDataAdapter("select top 20 name,id,xtype,uid,info,status,crdate,refdate,'aa' a,'bb' b,'cc' c,'ff' gfrom sysobjects", MyConnectionString);
try
{
da.Fill(result);
}
catch
{
}
return result;
}
private System.Data.DataTable GetBillFoot()
{
System.Data.DataTable result = new System.Data.DataTable();
SqlDataAdapter da = new SqlDataAdapter("select '出货仓库：00' as HeadValue union all select '出货门店：0000配送中心' as HeadValue union all select '出货金额：23456.12' as HeadValue union all select '收货仓库：01门店仓库' as HeadValue ", MyConnectionString);
try
{
da.Fill(result);
}
catch
{
}
return result;
}
private void button1_Click(object sender, EventArgs e)
{
int colsParentRow = 3; //每页显示标题数
string billTitle = string.Empty;
billTitle = "aaaaaaaaaaaa";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (app == null)
{
MessageBox.Show("Excel无法启动");
return;
}
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

//写入单头
//dtBillHead 单头内容，行序号
System.Data.DataTable dtBillHead = GetBillHead();
if ((dtBillHead != null) && (dtBillHead.Rows.Count > 0))
{
for (int k = 0; k < dtBillHead.Rows.Count; k++)
{
int rowIndex = (int)(k / colsParentRow) + 1;
int colIndex = (k % colsParentRow) + 1;
int t = colsParentRow;
if ((dtBillHead.Rows.Count % colsParentRow) != 0)
{
if (rowIndex == Convert.ToInt16(dtBillHead.Rows.Count / colsParentRow) + 1)
{
t = dtBillHead.Rows.Count % colsParentRow;
}
}
rowIndex+= billTitle!=""?1:0;
Microsoft.Office.Interop.Excel.Range rBillHead = ws.get_Range(GetCelColumnName(colIndex) + rowIndex.ToString(), GetCelColumnName(t) + rowIndex.ToString()); //列标题区域
object[] objBillHead = { dtBillHead.Rows[k]["HeadValue"].ToString().Trim() };
rBillHead.Value2 = objBillHead;
rBillHead = rBillHead.get_Resize(1,1);
rBillHead.EntireColumn.AutoFit();
}
}

IList itemHeader =new List();
itemHeader.Add("标题a") ;
itemHeader.Add("标题b");
itemHeader.Add("标题c");
itemHeader.Add("标题d");
itemHeader.Add("标题e");
itemHeader.Add("标题f");
itemHeader.Add("标题g");
itemHeader.Add("标题h");
itemHeader.Add("标题i");
itemHeader.Add("标题j");
itemHeader.Add("标题k");
itemHeader.Add("标题l");
int beginRowNo = 1;
if (billTitle != "")
{
beginRowNo = 2;
}
if (dtBillHead!=null && dtBillHead.Rows.Count > 0)
{
int k= dtBillHead.Rows.Count / colsParentRow;
k += dtBillHead.Rows.Count % colsParentRow == 0 ? 0 : 1;
beginRowNo += k ;
}
Microsoft.Office.Interop.Excel.Range rHeader = ws.get_Range("A" + beginRowNo.ToString(), GetCelColumnName(itemHeader.Count) + beginRowNo.ToString()); //列标题区域

object[] objHeader =new object[itemHeader.Count];
for (int i = 0; i < itemHeader.Count; i++)
{
objHeader[i] =itemHeader[i] ;
}
rHeader.Value2 = objHeader;
rHeader = rHeader.get_Resize(itemHeader.Count, itemHeader.Count);
rHeader.EntireColumn.AutoFit();
//明细数据
System.Data.DataTable dtBillDetail = new System.Data.DataTable();
SqlDataAdapter da = new SqlDataAdapter("select top 30 name,id,xtype,uid,info,status,crdate,refdate,'aa' a,'bb' b,'cc' c,'ff' gfrom sysobjects", MyConnectionString);
try
{
da.Fill(dtBillDetail);
}
catch (Exception ex)
{
MessageBox.Show("Operation failed: " + ex.ToString(), " - Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}

IList itemDetail = new List();
for (int k = 0; k < dtBillDetail.Rows.Count; k++)
{
for (int c = 0; c < dtBillDetail.Columns.Count; c++)
{
itemDetail.Add(new CelValues { CelValue = https://www.it610.com/article/dtBillDetail.Rows[k][c].ToString().Trim(), CelCol = (c + 1), CelRow = k + 1 });
}
}
for (int i = 0; i < itemDetail.Count; i++)
{
int col = itemDetail[i].CelCol;
int row = itemDetail[i].CelRow;
if (billTitle != "") { row = row + beginRowNo ; } else { row = row + beginRowNo ; }
Microsoft.Office.Interop.Excel.Range rDetail = ws.get_Range(GetCelColumnName(col) + Convert.ToString(row), GetCelColumnName(col) + Convert.ToString(row));
//rDetail.Borders.LineStyle= 0;
object[] objDetail = { itemDetail[i].CelValue };
rDetail.Value2 = objDetail;
rDetail = ws.get_Range(GetCelColumnName(col) + Convert.ToString(row), Missing.Value);
rDetail = rDetail.get_Resize(1, 1);
rDetail.Value2 = objDetail;
rDetail.EntireColumn.AutoFit();
//rDetail.Borders.LineStyle = 0;
rDetail.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); //给单元格加
}

//设置单据标题名
Microsoft.Office.Interop.Excel.Range rTitle;
rTitle = ws.get_Range("A1", "A1"); //列标题区域
rTitle.NumberFormatLocal = "@ ";
rTitle.Font.Size = 15; //设置字体大小
rTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式
rTitle.EntireColumn.AutoFit(); //自动调整列宽
rTitle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
object[] objTitle = { billTitle };
rTitle.Value2 = objTitle;
rTitle = rTitle.get_Resize(itemHeader.Count, itemHeader.Count);
rTitle.EntireColumn.AutoFit();

rTitle = ws.get_Range("A1", GetCelColumnName(itemHeader.Count) + "1"); //取得合并的区域
rTitle.MergeCells = true;
rTitle.Merge(0);
//rTitle.Borders.LineStyle= 0;
//rTitle.Borders.LineStyle = 0.5; //设置单元格边框的粗细
//设置单脚
System.Data.DataTable dtBillFoot = GetBillFoot() ;
if (dtBillFoot != null && dtBillFoot.Rows.Count > 0)
{
int k = dtBillFoot.Rows.Count / colsParentRow;
k += dtBillFoot.Rows.Count % colsParentRow == 0 ? 0 : 1;
beginRowNo += k;
}
if ((dtBillFoot != null) && (dtBillFoot.Rows.Count > 0))
{
for (int k = 0; k < dtBillFoot.Rows.Count; k++)
{
int rowIndex = (int)(k / colsParentRow) + 1;
int colIndex = (k % colsParentRow) + 1;
int t = colsParentRow;
if ((dtBillFoot.Rows.Count % colsParentRow) != 0)
{
if (rowIndex == Convert.ToInt16(dtBillFoot.Rows.Count / colsParentRow) + 1)
{
t = dtBillFoot.Rows.Count % colsParentRow;
}
}
rowIndex += billTitle != "" ? 1 : 0;
Microsoft.Office.Interop.Excel.Range rBillFoot = ws.get_Range(GetCelColumnName(colIndex) + (rowIndex + beginRowNo + dtBillDetail.Rows.Count-3).ToString(), GetCelColumnName(t) + (rowIndex + beginRowNo + dtBillDetail.Rows.Count-3).ToString()); //列标题区域
object[] objBillFoot = { dtBillHead.Rows[k]["HeadValue"].ToString().Trim() };
rBillFoot.Value2 = objBillFoot;
rBillFoot = rBillFoot.get_Resize(1, 1);
rBillFoot.EntireColumn.AutoFit();
}
}

app = null;
}
private void button2_Click(object sender, EventArgs e)
{
//label1.Text = GetCelColumnName(Convert.ToInt16(textBox1.Text));

excelFun.WriteCellData("商品入库单", GetBillHead(), GetColsHeader(), GetBillDetail(), GetBillFoot());
}
private void button3_Click(object sender, EventArgs e)
{
IList attFils = new List();
attFils.Add(System.IO.Directory.GetCurrentDirectory() + "\\OutPut.xls");
excelFun.SendEMAIL("smtp.126.com", 25, "110855663@qq.com", "emailqjc918@126.com", "qjc", "test", "emailqjc918", "adminasqjc918>", "aaa", attFils);
//
//
//
//
//
//
//"/>
}
private void Form1_Load(object sender, EventArgs e)
{
excelFun = new EXCELFunction(5);
}
}
}

【.NET|c#以单据标准格式写EXCEL并发送邮件出去】

    推荐阅读


    上一篇：缘起

    下一篇：JAVA里的锁之二独占锁与共享锁实现分析