读取Excel任务列表并显示在Outlook日历上

淡淡的烟草味﹌ 2022-01-10 07:23 336阅读 0赞

前几天，公司发了一个任务安排，时间不固定，但要求准时到，为了给自己加一个提醒，也为了回顾一下以前的技术，特做了一个Demo。

读取Excel就不多说了，代码很简单，但支持老版本Excel和的版本Excel。

代码如下：

public class ExcelConn
        {
            private string FilePath;
            private string m_filePath = string.Empty;
            private OleDbConnection conn;
    
            public ExcelConn(string filePath)
            {
                this.FilePath = filePath;
                string fileType = System.IO.Path.GetExtension(filePath);
                if (fileType == ".xls")
                {
                    conn = new OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ='" + this.FilePath + "';Extended Properties=Excel 8.0;");
                }
                else
                {
                    conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source ='" + this.FilePath + "';Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"");                
                }
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
            }
    
            private DataTable GetData(OleDbCommand cmd)
            {
                try
                {
                    if (cmd.Connection != null)
                    {
                        using (DataSet ds = new DataSet())
                        {
                            using (OleDbDataAdapter da = new OleDbDataAdapter())
                            {
                                da.SelectCommand = cmd;
                                da.Fill(ds);
                                return ds.Tables[0];
                            }
                        }
                    }
                    else
                    {
                        using (OleDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
                        {
                            try
                            {
                                cmd.Transaction = trans;
                                using (DataSet ds = new DataSet())
                                {
                                    using (OleDbDataAdapter da = new OleDbDataAdapter())
                                    {
                                        da.SelectCommand = cmd;
                                        da.SelectCommand.Connection = conn;
                                        da.Fill(ds);
                                        return ds.Tables[0];
                                    }
                                }
                            }
                            finally
                            {
                                trans.Commit();
                            }
                        }
                    }
                }
                finally
                { }
            }
    
            public DataSet GetDataSet(string sql)
            {
                try
                {
                    OleDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
                    try
                    {
                        using (OleDbCommand cmd = conn.CreateCommand())
                        {
                            cmd.Transaction = trans;
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sql;
                            using (DataSet ds = new DataSet())
                            {
                                using (OleDbDataAdapter da = new OleDbDataAdapter())
                                {
                                    da.SelectCommand = cmd;
                                    da.SelectCommand.Connection = conn;
                                    da.Fill(ds);
                                    return ds;
                                }
                            }
                        }
                    }
                    finally
                    {
                        trans.Commit();
                    }
                }
                finally
                {
                }
            }
    
            public void ExecuteNonQuery(string sql)
            {
                OleDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
                OleDbCommand cmd = new OleDbCommand(sql);
                cmd.Connection = conn;
                cmd.Transaction = trans;
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
    
            public object ExecuteScalar(OleDbCommand cmd)
            {
                try
                {
                    using (OleDbTransaction trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        object res = cmd.ExecuteScalar();
                        trans.Commit();
                        return res;
                    }
                }
                finally
                {
                }
            }
    
            public void Close()
            {
                conn.Close();
            }
    
            public DataSet ExcelToDS()
            {
                OleDbDataAdapter myCommand;
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
                DataSet ds = new DataSet();
                try
                {
                    int i = 0;
                    {
                        DataRow dr = schemaTable.Rows[i];
                        string strExcel = "select * from [" + dr["TABLE_NAME"].ToString().Trim() + "]";
                        myCommand = new OleDbDataAdapter(strExcel, conn);
                        myCommand.Fill(ds);
                    }
                }
                catch
                {
                }
                finally
                {
                    conn.Close();
                }
                return ds;
            }
        }

调用示例：DataTable dtExl = new Calendar.ExcelConn(@"d:\\桌面\\Temp\\net开发团队生产突击报名表(1).xlsx").ExcelToDS().Tables\[0\];

在OutLook上添加日历，其实就是添加一个约会。

新建约会也就是调用OutLook的ApplicationClass。

主要代码如下：

ApplicationClass oApp = new Microsoft.Office.Interop.Outlook.ApplicationClass();
    
                    //会议是约会的一种
                    AppointmentItem oItem = (AppointmentItem)oApp.CreateItem(OlItemType.olAppointmentItem);
                    oItem.MeetingStatus = OlMeetingStatus.olMeeting;
                    oItem.Subject = "生产突击";
                    oItem.Body = "内容";
                    oItem.Location = "地点";
                    //开始时间　
                    oItem.Start = DateTime.Now.AddDays(1);
                    //结束时间
                    oItem.End = DateTime.Now.AddDays(1).AddHours(4);
                    //提醒设置
                    oItem.ReminderSet = true;
                    oItem.ReminderMinutesBeforeStart = 5;
    
                    //是否全天事件
                    oItem.AllDayEvent = false;
    
                    oItem.BusyStatus = OlBusyStatus.olBusy;
    
                    //索引从1开始，而不是从0
                    //发件人的帐号信息
                    var acc = oApp.Session.Accounts;
                    oItem.SendUsingAccount = oApp.Session.Accounts[1];
    
                    //添加必选人
                    Recipient force = oItem.Recipients.Add("wufei@china.com");
                    force = oItem.Recipients.Add("zaijun@china.com");
                    force.Type = (int)OlMeetingRecipientType.olRequired;
                    ////添加可选人
                    //Recipient opt = oItem.Recipients.Add("mailuser3@p.mailserver.com");
                    //opt.Type = (int)OlMeetingRecipientType.olOptional;
                    ////添加会议发起者
                    //Recipient meetingSender = oItem.Recipients.Add("mailuser1@mailserver.com");
                    //meetingSender.Type = (int)OlMeetingRecipientType.olOrganizer;
    
                    oItem.Recipients.ResolveAll();
                    oItem.Send();

既读取了Excel，又能发约会，下面就是Excel中数据的筛选和调用了，相信难不倒各位，就不多说了。

至此，这个简单的Demo算是完成了，希望对大家有用。

转载于:https://www.cnblogs.com/ushou/p/3358961.html