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

2年前 (2022) 程序员胖胖胖虎阿

272 0 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();