使用C#将Outlook中的邮件内容分解并导入到Excel中

最新推荐文章于 2020-10-11 14:39:21 发布

weixin_30567471

最新推荐文章于 2020-10-11 14:39:21 发布

阅读量256

点赞数

文章标签： c#

原文链接：http://www.cnblogs.com/abei108/archive/2006/06/06/418883.html

版权

昨天接到一个需求，要从Outlook2003中读取某类用户调查邮件，将正文中用户所填的问题和答案进行分析，将内容导入Excel中。今天花了一天时间写好了，很简单的功能，代码贴出来给需要的人参考吧。

代码如下：

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

namespace MailAnalyze

{

/**//// <summary>

/// Summary description for Mainform.

/// </summary>

public class Mainform : System.Windows.Forms.Form

{

private System.Windows.Forms.Button ConnectButton;

private System.Windows.Forms.ListBox MailFolderList;

private bool connOutlook = false;

private System.Windows.Forms.TextBox FilterBox;

private System.Windows.Forms.Label label1;

private System.Windows.Forms.Label label2;

private ArrayList maillist = new ArrayList();

private System.Windows.Forms.TabControl tabControl1;

private System.Windows.Forms.TabPage tabPage1;

private System.Windows.Forms.TabPage tabPage2;

private System.Windows.Forms.TextBox BodyBox;

private System.Windows.Forms.Button ExportBtn;

private System.Windows.Forms.ListView QuestionListView;

private System.Windows.Forms.ColumnHeader columnHeader1;

private System.Windows.Forms.ColumnHeader columnHeader3;

private System.Windows.Forms.ColumnHeader columnHeader4;

private System.Windows.Forms.ColumnHeader columnHeader5;

private System.Windows.Forms.Button QuitBtn;

/**//// <summary>

/// Required designer variable.

/// </summary>

private System.ComponentModel.Container components = null;

public Mainform()

{

// Required for Windows Form Designer support

InitializeComponent();

MailFolderList.Items.Clear();

label2.Text = "";

// TODO: Add any constructor code after InitializeComponent call

}

/**//// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if(components != null)

{

components.Dispose();

}

base.Dispose( disposing );

}

Windows Form Designer generated code#region Windows Form Designer generated code

/**//// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.ConnectButton = new System.Windows.Forms.Button();

this.MailFolderList = new System.Windows.Forms.ListBox();

this.FilterBox = new System.Windows.Forms.TextBox();

this.label1 = new System.Windows.Forms.Label();

this.label2 = new System.Windows.Forms.Label();

this.tabControl1 = new System.Windows.Forms.TabControl();

this.tabPage1 = new System.Windows.Forms.TabPage();

this.tabPage2 = new System.Windows.Forms.TabPage();

this.BodyBox = new System.Windows.Forms.TextBox();

this.ExportBtn = new System.Windows.Forms.Button();

this.QuestionListView = new System.Windows.Forms.ListView();

this.columnHeader1 = new System.Windows.Forms.ColumnHeader();

this.columnHeader3 = new System.Windows.Forms.ColumnHeader();

this.columnHeader4 = new System.Windows.Forms.ColumnHeader();

this.columnHeader5 = new System.Windows.Forms.ColumnHeader();

this.QuitBtn = new System.Windows.Forms.Button();

this.tabControl1.SuspendLayout();

this.tabPage1.SuspendLayout();

this.tabPage2.SuspendLayout();

this.SuspendLayout();

// ConnectButton

this.ConnectButton.Location = new System.Drawing.Point(232, 8);

this.ConnectButton.Name = "ConnectButton";

this.ConnectButton.Size = new System.Drawing.Size(120, 24);

this.ConnectButton.TabIndex = 0;

this.ConnectButton.Text = "Access Outlook";

this.ConnectButton.Click += new System.EventHandler(this.ConnectButton_Click);

// MailFolderList

this.MailFolderList.HorizontalScrollbar = true;

this.MailFolderList.Location = new System.Drawing.Point(8, 40);

this.MailFolderList.Name = "MailFolderList";

this.MailFolderList.Size = new System.Drawing.Size(208, 381);

this.MailFolderList.TabIndex = 1;

this.MailFolderList.SelectedIndexChanged += new System.EventHandler(this.MailFolderList_SelectedIndexChanged);

// FilterBox

this.FilterBox.Location = new System.Drawing.Point(80, 10);

this.FilterBox.Name = "FilterBox";

this.FilterBox.Size = new System.Drawing.Size(136, 20);

this.FilterBox.TabIndex = 2;

this.FilterBox.Text = "";

// label1

this.label1.Location = new System.Drawing.Point(8, 14);

this.label1.Name = "label1";

this.label1.Size = new System.Drawing.Size(64, 16);

this.label1.TabIndex = 3;

this.label1.Text = "Title Filter:";

// label2

this.label2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((System.Byte)(134)));

this.label2.Location = new System.Drawing.Point(8, 424);

this.label2.Name = "label2";

this.label2.Size = new System.Drawing.Size(100, 16);

this.label2.TabIndex = 4;

this.label2.Text = "label2";

// tabControl1

this.tabControl1.Controls.Add(this.tabPage1);

this.tabControl1.Controls.Add(this.tabPage2);

this.tabControl1.Location = new System.Drawing.Point(232, 40);

this.tabControl1.Name = "tabControl1";

this.tabControl1.SelectedIndex = 0;

this.tabControl1.Size = new System.Drawing.Size(440, 384);

this.tabControl1.TabIndex = 5;

// tabPage1

this.tabPage1.Controls.Add(this.BodyBox);

this.tabPage1.Location = new System.Drawing.Point(4, 22);

this.tabPage1.Name = "tabPage1";

this.tabPage1.Size = new System.Drawing.Size(432, 358);

this.tabPage1.TabIndex = 0;

this.tabPage1.Text = "Mailbody";

// tabPage2

this.tabPage2.Controls.Add(this.QuestionListView);

this.tabPage2.Location = new System.Drawing.Point(4, 22);

this.tabPage2.Name = "tabPage2";

this.tabPage2.Size = new System.Drawing.Size(432, 358);

this.tabPage2.TabIndex = 1;

this.tabPage2.Text = "Question Analyze";

// BodyBox

this.BodyBox.Location = new System.Drawing.Point(0, 0);

this.BodyBox.Multiline = true;

this.BodyBox.Name = "BodyBox";

this.BodyBox.ScrollBars = System.Windows.Forms.ScrollBars.Both;

this.BodyBox.Size = new System.Drawing.Size(432, 360);

this.BodyBox.TabIndex = 0;

this.BodyBox.Text = "";

// ExportBtn

this.ExportBtn.Location = new System.Drawing.Point(368, 8);

this.ExportBtn.Name = "ExportBtn";

this.ExportBtn.Size = new System.Drawing.Size(96, 23);

this.ExportBtn.TabIndex = 6;

this.ExportBtn.Text = "Export to Excel";

this.ExportBtn.Click += new System.EventHandler(this.ExportBtn_Click);

// QuestionListView

this.QuestionListView.Columns.AddRange(new System.Windows.Forms.ColumnHeader[]

{

this.columnHeader1,

this.columnHeader3,

this.columnHeader4,

this.columnHeader5});

this.QuestionListView.FullRowSelect = true;

this.QuestionListView.GridLines = true;

this.QuestionListView.Location = new System.Drawing.Point(0, 0);

this.QuestionListView.MultiSelect = false;

this.QuestionListView.Name = "QuestionListView";

this.QuestionListView.Size = new System.Drawing.Size(432, 360);

this.QuestionListView.TabIndex = 0;

this.QuestionListView.View = System.Windows.Forms.View.Details;

// columnHeader1

this.columnHeader1.Text = "MailID";

this.columnHeader1.Width = 42;

// columnHeader3

this.columnHeader3.Text = "Q No.";

this.columnHeader3.Width = 40;

// columnHeader4

this.columnHeader4.Text = "Question";

this.columnHeader4.Width = 280;

// columnHeader5

this.columnHeader5.Text = "Answer";

// QuitBtn

this.QuitBtn.Location = new System.Drawing.Point(609, 8);

this.QuitBtn.Name = "QuitBtn";

this.QuitBtn.Size = new System.Drawing.Size(64, 23);

this.QuitBtn.TabIndex = 7;

this.QuitBtn.Text = "Quit";

this.QuitBtn.Click += new System.EventHandler(this.QuitBtn_Click);

// Mainform

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(688, 442);

this.Controls.Add(this.QuitBtn);

this.Controls.Add(this.ExportBtn);

this.Controls.Add(this.tabControl1);

this.Controls.Add(this.label2);

this.Controls.Add(this.label1);

this.Controls.Add(this.FilterBox);

this.Controls.Add(this.MailFolderList);

this.Controls.Add(this.ConnectButton);

this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow;

this.Name = "Mainform";

this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;

this.Text = "Site Survey Analysis Tool";

this.tabControl1.ResumeLayout(false);

this.tabPage1.ResumeLayout(false);

this.tabPage2.ResumeLayout(false);

this.ResumeLayout(false);

}

#endregion

private void ConnectButton_Click(object sender, System.EventArgs e)

{

try

{

ConnectButton.Enabled = false;

Outlook.ApplicationClass outLookApp = new Outlook.ApplicationClass();

Outlook.NameSpace outlookNS = outLookApp.GetNamespace("MAPI");

Outlook.MAPIFolder selectFolder = outlookNS.PickFolder();

MailFolderList.Items.Clear();

maillist.Clear();

QuestionListView.Items.Clear();

BodyBox.Text = string.Empty;

int i=0;

foreach(object item in selectFolder.Items)

{

Outlook.MailItem mi = item as Outlook.MailItem;

if (FilterBox.Text.Trim()!=string.Empty)

{

if (mi.Subject.IndexOf(FilterBox.Text.Trim())>0)

{

i++;

MailFolderList.Items.Add(i.ToString()+"."+mi.Subject+mi.ReceivedTime.ToString());

maillist.Add(mi);

}

else

{

i++;

MailFolderList.Items.Add(i.ToString()+"."+mi.Subject+mi.ReceivedTime.ToString());

maillist.Add(mi);

}

label2.Text = "Total "+i.ToString()+" mails";

outLookApp = null;

connOutlook = true;

}

catch

{

connOutlook = false;

MessageBox.Show("Can't connect to Outlook!");

}

finally

{

ConnectButton.Enabled = true;

}

/**//// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main()

{

Application.Run(new Mainform());

}

private void MailFolderList_SelectedIndexChanged(object sender, System.EventArgs e)

{

try

{

int midx = MailFolderList.SelectedIndex;

Outlook.MailItem mi = maillist[midx] as Outlook.MailItem;

BodyBox.Text = mi.Body;

QuestionListView.Items.Clear();

try

{

AnalyzeBody(midx+1,mi);

}

catch

{

MessageBox.Show("The mailbody don't follow the format of Site Survey!");

}

catch

{

MessageBox.Show("Can't find this mail.");

}

private void AnalyzeBody(int midx,Outlook.MailItem mi)

{

string body = mi.Body;

body = body.Replace("\r\n","|");

string[] linearr = body.Split('|');

string currquestion = "";

string currmainquest = "";

string curranswer = "";

string currqnum = string.Empty;

int secqnum = 0;

foreach(string line in linearr)

{

if (line.IndexOf('Q')>-1)

{

//新的题目

if (line.StartsWith("Q"))

{

currqnum = line.Substring(0,line.IndexOf(":"));

currquestion = line.Remove(0,line.IndexOf(":")+1);

secqnum = 0;

if (currquestion.IndexOf("Q")>-1)

{

curranswer = "Q"+currquestion.Split('Q')[1];

currquestion = currquestion.Split('Q')[0];

//保存这个题目

ListViewItem listitem = new ListViewItem();

listitem.Text = midx.ToString();

listitem.SubItems.Add(currqnum);

listitem.SubItems.Add(currquestion);

listitem.SubItems.Add(curranswer);

QuestionListView.Items.Add(listitem);

currqnum = "";

currquestion = "";

curranswer = "";

}

else

{

currmainquest = currquestion;

curranswer = "";

}

else

{

//有子题目，分别保存

secqnum++;

curranswer += "Q"+line.Split('Q')[1];

currquestion = currmainquest+"--"+line.Split('Q')[0];

ListViewItem listitem = new ListViewItem();

listitem.Text = midx.ToString();

listitem.SubItems.Add(currqnum+"_"+secqnum.ToString());

listitem.SubItems.Add(currquestion);

listitem.SubItems.Add(curranswer);

QuestionListView.Items.Add(listitem);

currquestion = "";

curranswer = "";

}

private void ExportBtn_Click(object sender, System.EventArgs e)

{

if (connOutlook)

{

try

{

string log = string.Empty;

tabControl1.TabIndex = 0;

ExportBtn.Enabled = false;

ConnectButton.Enabled = false;

ArrayList SurveyList = new ArrayList();

//导出记录到ArrayList中

if (maillist!=null && maillist.Count>0)

{

for (int i=0;i<maillist.Count;i++)

{

Outlook.MailItem mi = maillist[i] as Outlook.MailItem;

try

{

AnalyzeBody(i+1,mi);

//将listview中的内容导出

for (int j=0;j<QuestionListView.Items.Count;j++)

{

SurveyQ sq = new SurveyQ();

sq.Mailid = (i+1).ToString();

sq.Mailtime = mi.ReceivedTime.ToString();

sq.QNo = QuestionListView.Items[j].SubItems[1].Text;

sq.Question = QuestionListView.Items[j].SubItems[2].Text;

sq.Qanswer = QuestionListView.Items[j].SubItems[3].Text;

SurveyList.Add(sq);

}

catch

{

log += "Mail:ID "+(i+1).ToString()+" didn't match the survey format. Can't export!\r\n";

}

//将生成的list输出到Excel

if (SurveyList.Count>0)

{

string[,] SurveyArray = new string[SurveyList.Count,5];

for (int i=0;i<SurveyList.Count;i++)

{

SurveyQ sq = SurveyList[i] as SurveyQ;

SurveyArray[i,0] = sq.Mailid;

SurveyArray[i,1] = sq.Mailtime;

SurveyArray[i,2] = sq.QNo;

SurveyArray[i,3] = sq.Question;

SurveyArray[i,4] = sq.Qanswer;

}

Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

Excel.Workbook workbook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];

//标题

Excel.Range titlerange = worksheet.get_Range("A1","E1");

titlerange.Font.Bold = true;

titlerange.Value2 = new string[5]

{"Mail ID","Receive Time","Question No.","Question","Answer"};

//正文

Excel.Range workrange = worksheet.get_Range("A2","E"+(SurveyList.Count+1).ToString());

workrange.Value2 = SurveyArray;

excelApp.Visible = true;

titlerange = null;

workrange = null;

worksheet = null;

workbook = null;

excelApp = null;

}

if (log!=string.Empty)

MessageBox.Show(log);

else

MessageBox.Show("Export successfully!");

ExportBtn.Enabled = true;

ConnectButton.Enabled = true;

}

catch

{

MessageBox.Show("Can't Export!");

}

else

{

MessageBox.Show("Didn't connect to Outlook!");

}

private void QuitBtn_Click(object sender, System.EventArgs e)

{

Application.Exit();

}

internal class SurveyQ

{

public string Mailid;

public string Mailtime;

public string QNo;

public string Question;

public string Qanswer;

public SurveyQ()

{

Mailid = "";

Mailtime = "";

QNo = "";

Question = "";

Qanswer = "";

}

转载于:https://www.cnblogs.com/abei108/archive/2006/06/06/418883.html

weixin_30567471

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
使用C#将Outlook中的邮件内容分解并导入到Excel中

昨天接到一个需求，要从Outlook2003中读取某类用户调查邮件，将正文中用户所填的问题和答案进行分析，将内容导入Excel中。今天花了一天时间写好了，很简单的功能，代码贴出来给需要的人参考吧。代码如下：usingSystem;usingSystem.Drawing;usingSystem.Collections;usingSystem.ComponentModel;usingSy...
复制链接

扫一扫