outlook - Outlook - 将电子邮件导出到 Excel - 每个文件夹作为新工作表 C#
问题描述
我有以下代码,可以将共享邮箱的每个文件夹中的电子邮件导出到 Excel 文件。目前,它为每个文件夹创建一个 Excel 文件,但我想将其更改为为每个文件夹创建一个工作表并将所有工作表合并到一个 Excel 文件中。
Outlook.Application application = Globals.ThisAddIn.Application;
Outlook.NameSpace ns = application.GetNamespace("MAPI");
Excel.Application oApp = null;
Excel.Workbook oWB = null;
Excel.Worksheet oSheet = null;
oApp = new Excel.Application();
oWB = oApp.Workbooks.Add();
string recipientName = "xxxxxx@XXXXXXX.com";
Outlook.Recipient recip = ns.CreateRecipient(recipientName);
recip.Resolve();
if (recip.Resolved)
{
Outlook.MAPIFolder folderContacts = ns.GetSharedDefaultFolder(recip, Outlook.OlDefaultFolders.olFolderInbox);
Outlook.Folder allFolder = folderContacts.Parent;
foreach (Outlook.Folder rfolder in allFolder.Folders)
{
if (rfolder.Name.Contains("In Progress"))
{
Outlook.UserProperties MailUserProperties = null;
Outlook.UserProperty MailUserProperty = null;
oSheet = (Excel.Worksheet)oWB.Worksheets.get_Item(1);
oSheet.Name = rfolder.Name;
Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
DateTime startDate = new DateTime(2019, 10, 1);
DateTime lastDate = new DateTime(2019, 10, 5);
string filter = "[ReceivedTime] >= '" + startDate.ToString("g") + "' AND [ReceivedTime] <= '" + lastDate.ToString("g") + "'";
Outlook.Items restrictedMails = rfolder.Items.Restrict(filter);
int row = 2;
int column = 1;
Excel.Range range = (Excel.Range)oSheet.Cells[row, column];
oSheet.Cells[1, 1] = "Received Date";
oSheet.Cells[1, 2] = "Sender";
oSheet.Cells[1, 3] = "Subject";
oSheet.Cells[1, 4] = "Category";
try
{
for (int i = 1; i <= restrictedMails.Count; i++)
{
Outlook.MailItem mail = restrictedMails[i];
MailUserProperties = mail.UserProperties;
range.Cells[i, 1] = mail.ReceivedTime;
range.Cells[i, 2] = mail.Sender;
range.Cells[i, 3] = mail.Subject;
range.Cells[i, 4] = mail.Categories;
}
oSheet.UsedRange.Columns.AutoFit();
}
catch (System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex.ToString());
}
}
}
SaveFileDialog saveFileDialogue = new SaveFileDialog();
}
先感谢您。
解决方案
您只需要使用for
循环而不是foreach
:
Outlook.Application application = Globals.ThisAddIn.Application;
Outlook.NameSpace ns = application.GetNamespace("MAPI");
Excel.Application oApp = null;
Excel.Workbook oWB = null;
Excel.Worksheet oSheet = null;
oApp = new Excel.Application();
oWB = oApp.Workbooks.Add();
string recipientName = "xxxxxx@XXXXXXX.com";
Outlook.Recipient recip = ns.CreateRecipient(recipientName);
recip.Resolve();
if (recip.Resolved)
{
Outlook.MAPIFolder folderContacts = ns.GetSharedDefaultFolder(recip, Outlook.OlDefaultFolders.olFolderInbox);
Outlook.Folder allFolder = folderContacts.Parent;
Outlook.Folders allFolders = allFolder.Folders;
for(int i =1; i<= allFolders.Count; i++)
{
Outlook.Folder rfolder = allFolders[i];
if (rfolder.Name.Contains("In Progress"))
{
Outlook.UserProperties MailUserProperties = null;
Outlook.UserProperty MailUserProperty = null;
oSheet = (Excel.Worksheet)oWB.Worksheets.Add();
' or just check whether such worksheet exists before adding a new one
oSheet.Name = rfolder.Name;
Excel.Range last = oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
DateTime startDate = new DateTime(2019, 10, 1);
DateTime lastDate = new DateTime(2019, 10, 5);
string filter = "[ReceivedTime] >= '" + startDate.ToString("g") + "' AND [ReceivedTime] <= '" + lastDate.ToString("g") + "'";
Outlook.Items restrictedMails = rfolder.Items.Restrict(filter);
int row = 2;
int column = 1;
Excel.Range range = (Excel.Range)oSheet.Cells[row, column];
oSheet.Cells[1, 1] = "Received Date";
oSheet.Cells[1, 2] = "Sender";
oSheet.Cells[1, 3] = "Subject";
oSheet.Cells[1, 4] = "Category";
try
{
for (int i = 1; i <= restrictedMails.Count; i++)
{
Outlook.MailItem mail = restrictedMails[i];
MailUserProperties = mail.UserProperties;
range.Cells[i, 1] = mail.ReceivedTime;
range.Cells[i, 2] = mail.Sender;
range.Cells[i, 3] = mail.Subject;
range.Cells[i, 4] = mail.Categories;
}
oSheet.UsedRange.Columns.AutoFit();
}
catch (System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex.ToString());
}
}
}
SaveFileDialog saveFileDialogue = new SaveFileDialog();
}
推荐阅读
- vue.js - vue/nuxt在哪里导入文件js
- c# - WordEditor 从字符串插入 HTML
- javascript - 如何根据条件在 JSX 中为键提供变量的值
- c# - SerialPort.DiscarInBuffer():是否应该花一些时间让操作完成?
- django - django, celery:为每个用户创建工作者
- websphere-7 - WebSphere v7.0 不启动 CREATE_LISTENER_FAILED_4
- android - Android Studio 网络监视器关于 3.5 的问题
- vue.js - 无法从 JSPDF 创建 PDF
- vb.net - 运行 System.Security.Cryptography.ECDsa.Create(ECCurve 曲线)时出现 VB.NET 异常
- java - 发送 GET 请求时无法将类型“java.lang.String”的值转换为所需类型“java.util.Date”