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

昨天接到一个需求，要从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

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 = "";

}

posted on 2006-06-06 17:26 Gao.Steven 阅读(2241) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Gao.Steven

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

导航

公告