Linq,企业类库,EXECL生成,Execl chart的一些基本操作记录.(一)

一个小任务.一个星期启动一次.在服务器一上把当前星期的一些数据分组统计把数据插入服务器二.然后生成这个星期并集合前N个星期的资料生成execl和exexl chart.

因为服务器一上的数据不少,一个星期可能有30W笔资料.在这里用到企业类库的DAAB,后面分组统计等相关操作当然用Linq了.服务器二的数据表用Linq to sql生成相关类.因为是自动一个星期跑一次.异常出错就要记录还要email通知相关人等.异常发生后就记录.我用如下方法.第一次用.感觉写的肯定有问题.日志记录也用的是企业类库的日志模块.(这里有个问题,我同时用二个文本记录老不成功,不知怎么回事.还有Mail发送那块也没配成功.郁闷)

 1        static void Main()
 2        {
 3            //Process[] myProcesses = Process.GetProcessesByName("EXCEL.EXE");
 4            //foreach (Process p in myProcesses)
 5            //    p.Close();
 6            Application.EnableVisualStyles();
 7            Application.SetCompatibleTextRenderingDefault(false);
 8            Application.ThreadException += new System.Threading.ThreadExceptionEventHandler(Application_ThreadException);
 9            AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);
10            Application.Run(new Form1());
11
12        }
13
14        static void CurrentDomain_UnhandledException(object sender, UnhandledExceptionEventArgs e)
15        {
16            string str = e.ExceptionObject.ToString();
17            ReportData.Common.LogError(str);
18            ReportData.Common.SendMail(str, "-------报表出现错误");
19            Application.Exit();
20        }
21        //早于CurrentDomain_UnhandledException发生.
22        static void Application_ThreadException(object sender, System.Threading.ThreadExceptionEventArgs e)
23        {
24            string str = e.Exception.Message;         
25            ReportData.Common.LogError(str);
26            ReportData.Common.SendMail(str, "--------报表出现错误");
27            Application.Exit();
28            
29        }

 然后有一些公共操作记录一下,前N个星期的N个就放在配置文件里.而报表的性态.还有生成的文件的路径因为一些分散
位置用.也放在里面.

  1    public static class Common
  2    {
  3        public static double RmbToDollar
  4        {
  5            get
  6            {
  7                string rmbToDollar = ConfigurationManager.AppSettings["RmbToDollar"];
  8                double f = 6.8;
  9                double.TryParse(rmbToDollar, out f);
 10                return f;
 11            }
 12        }
 13        public static int WeekNum
 14        {
 15            get
 16            {
 17                string weeknum = ConfigurationManager.AppSettings["WeekNum"];
 18                int w = 10;
 19                int.TryParse(weeknum, out w);
 20                return w;
 21            }
 22        }
 23        public static XlChartType xlChartType
 24        {
 25            get
 26            {
 27                string xtype = ConfigurationManager.AppSettings["XlChartType"];
 28                if (xtype == "xlColumnClustered")
 29                    return XlChartType.xlColumnClustered;
 30                else if (xtype == "xl3DBarClustered")
 31                    return XlChartType.xl3DBarClustered;
 32                else if (xtype == "xl3DLine")
 33                    return XlChartType.xl3DLine;
 34                else
 35                    return XlChartType.xlLineMarkers;
 36            }
 37        }
 38        public static int Week(DateTime time)
 39        {
 40            //int today = time.DayOfYear;
 41            //return today / 7 + 1;
 42            GregorianCalendar gc = new GregorianCalendar();
 43            return  gc.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);            
 44        }
 45        private static string customPath = string.Empty;
 46        public static string CustomPath
 47        {
 48            get
 49            {
 50                if (string.IsNullOrEmpty(customPath))
 51                    customPath = Directory.GetCurrentDirectory() + @"\" + DateTime.Now.ToString("yyyy-MM-dd") + "Custom.xls";
 52                return customPath;
 53            }
 54            private set
 55            {
 56                customPath = value;
 57            }
 58        }
 59        private static string statusPath = string.Empty;
 60        public static string StatusPath
 61        {
 62            get
 63            {
 64                if (string.IsNullOrEmpty(statusPath))
 65                    statusPath = Directory.GetCurrentDirectory() + @"\" + DateTime.Now.ToString("yyyy-MM-dd") + "Status.xls";
 66                return statusPath;
 67            }
 68            private set
 69            {
 70                statusPath = value;
 71            }
 72        }
 73        private static string bPreCentPath = string.Empty;
 74        public static string BPreCentPath
 75        {
 76            get
 77            {
 78                if (string.IsNullOrEmpty(bPreCentPath))
 79                    bPreCentPath = Directory.GetCurrentDirectory() + @"\" + DateTime.Now.ToString("yyyy-MM-dd") + "BPerCent.xls";
 80                return bPreCentPath;
 81            }
 82            private set
 83            {
 84                bPreCentPath = value;
 85            }
 86        }
 87        public static DateTime GetStartDate(DateTime date)
 88        {
 89            DateTime now = date;
 90            now = now.AddDays(-7);
 91            switch (now.DayOfWeek)
 92            {
 93                case DayOfWeek.Monday:
 94                    break;
 95                case DayOfWeek.Tuesday:
 96                    now = now.AddDays(-1);
 97                    break;
 98                case DayOfWeek.Wednesday:
 99                    now = now.AddDays(-2);
100                    break;
101                case DayOfWeek.Thursday:
102                    now = now.AddDays(-3);
103                    break;
104                case DayOfWeek.Friday:
105                    now = now.AddDays(-4);
106                    break;
107                case DayOfWeek.Saturday:
108                    now = now.AddDays(-5);
109                    break;
110                case DayOfWeek.Sunday:
111                    now = now.AddDays(-6);
112                    break;
113            }
114            now = now.AddHours(-now.Hour);
115            now = now.AddMinutes(-now.Minute);
116            now = now.AddSeconds(-now.Second);
117            now = now.AddMilliseconds(-now.Millisecond);
118            return now;
119        }
120        public static void LogError(string error)
121        {            
122            LogEntry logerror = new LogEntry();
123            logerror.Message = error;
124            logerror.Categories.Add("Category");
125            logerror.Categories.Add("Eamil");
126            logerror.Title = "在KPRCS里有错识产生,请检察!";            
127            logerror.TimeStamp = DateTime.Now;
128            logerror.Severity = System.Diagnostics.TraceEventType.Error;
129            logerror.Priority = 2;
130            Logger.Write(logerror);
131        }
132        public static void LogCommon(string common)
133        {
134            LogEntry logerror = new LogEntry();
135            logerror.Message = common;
136            logerror.Categories.Add("Category");
137            logerror.Title = "发生一个情况";           
138            logerror.TimeStamp = DateTime.Now;
139            logerror.Severity = System.Diagnostics.TraceEventType.Information;
140            logerror.Priority = 2;
141            Logger.Write(logerror);
142        }
143
144
145
146        public static void SendMail(string body,string subject)
147        {
148            MailMessage mailobj = new MailMessage();
149            mailobj.From = new MailAddress("---------------", "-----------");
150            mailobj.To.Add("----------");
151            mailobj.Subject = subject;
152            mailobj.Body = body;
153            mailobj.Priority = MailPriority.Normal;
154            mailobj.IsBodyHtml = true;
155            SmtpClient smtp = new SmtpClient("--------");
156            smtp.UseDefaultCredentials = false;
157          //  smtp.Credentials = new System.Net.NetworkCredential("-----", "-----");
158            smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
159            smtp.Send(mailobj);
160        }
161        public static void SendMail(List<string> tonames)
162        {
163            tonames.Add("------");
164            tonames.Add("------");
165            MailMessage mailobj = new MailMessage();
166            mailobj.From = new MailAddress("---------", "----------");            
167            foreach (string toname in tonames)
168            {
169                string name = toname + "@compal.com";
170                mailobj.To.Add(name);
171            }
172            //mailobj.To.Add("------------");
173            mailobj.Attachments.Add(new Attachment(CustomPath));
174            mailobj.Attachments.Add(new Attachment(StatusPath));
175            mailobj.Attachments.Add(new Attachment(BPreCentPath));
176            mailobj.Subject = "--------本月报表产生如下";
177            mailobj.Body = "Dear all:以上附件为--------本月的报表.";
178            mailobj.Priority = MailPriority.Normal;
179            mailobj.IsBodyHtml = true;
180            SmtpClient smtp = new SmtpClient("----------");
181            smtp.UseDefaultCredentials = false;
182            //  smtp.Credentials = new System.Net.NetworkCredential("KPRCS", "KPRCS");
183            smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
184            try
185            {
186                smtp.Send(mailobj);
187                LogCommon("已正确发送邮件,本星期数据已经OK!");
188            }
189            catch
190            {
191                SendMail("数据没有成功发送", "KPRCS报表发生问题,请查看.");
192            }
193        }
194    }

 怕自己忘记了,记录一下.这代码我看着想吐.
下一步,因为其中的数据是根据当前年的星期数取的,我封装一下.

 1    public class WeekInYear
 2    {
 3        public int? WeekNum;
 4        public int? Year;
 5
 6        public static IComparer<WeekInYear> sort()
 7        {
 8            return (IComparer<WeekInYear>)new WeekInYearHelper();
 9        }
10        public override string ToString()
11        {
12            return "WK:" + WeekNum + "-" + Year;
13        }
14        public static WeekInYear operator -(WeekInYear self, int weekNum)
15        {
16            int nWeek = 365 / 7 + 1;
17            if (weekNum > nWeek)
18                weekNum = nWeek;
19            WeekInYear n = new WeekInYear { Year = self.Year, WeekNum = self.WeekNum };
20            if (self.WeekNum > weekNum)
21            {
22                n.WeekNum = n.WeekNum - weekNum;
23                return n;
24            }
25            DateTime i = DateTime.Now;
26            i = i.AddYears(self.Year.Value - i.Year - 1);
27            if (self.WeekNum == weekNum)
28            {
29                n.WeekNum = nWeek;
30                n.Year = n.Year - 1;
31            }
32            else
33            {
34                n.Year = n.Year - 1;
35                n.WeekNum = nWeek - (weekNum - self.WeekNum);
36            }
37            return n;
38        }
39    }
40    public class WeekInYearHelper : IComparer<WeekInYear>
41    {
42        public int Compare(WeekInYear self, WeekInYear other)
43        {
44            if (self.Year > other.Year)
45                return 1;
46            else if (self.Year == other.Year)
47            {
48                if (self.WeekNum > other.WeekNum)
49                    return 1;
50                else if (self.WeekNum == other.WeekNum)
51                    return 0;
52                else
53                    return -1;
54            }
55            else
56                return -1;
57        }
58    }

其是下一个类,我是想让其支持在Linq中的排序.可惜这步没有做好.想是是另一个方法.其中我重载减号.让他可以减星期数.
这个因为我没怎么用,只是实验下重载符号.
这些做完后,我开始从服务器一里读入数据.

 1        public void GetTable()
 2        {
 3            DateTime start = Common.GetStartDate(DateTime.Now);
 4            DateTime end = start.AddDays(7);
 5            int weeknum = Common.Week(start);
 6            int year = start.Year;
 7            wy = new WeekInYear { WeekNum = weeknum, Year = year };
 8            //throw new Exception("测试一下错误");      
 9            if (db.ReportSum.Count((ReportSum report) => report.Year == year && report.WeekNum == weeknum) > 0)
10            {
11                BuildExel();
12                return;
13            }
14            //数据来源
15            Database dbE = DatabaseFactory.CreateDatabase("ConnectionString");
16            //-----本地服务器
17            Database dbL = DatabaseFactory.CreateDatabase("ConnectionStringLocal");
18            Common.LogCommon("开始读入数据");
19            string query = "select * from ------ where BUDAT>='" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and BUDAT<'" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";
20            List<ReportD> reports = new List<ReportD>();
21            using (IDataReader rd = dbE.ExecuteReader(CommandType.Text, query.ToString()))
22            {
23                while (rd.Read())
24                {
25                    ReportD report = new ReportD();
26                    report.Year = year;
27                    report.WeekNum = weeknum;
28                    report.Plant = rd["WERKS"].ToString();
29                    report.Status = ConRecode(rd);
30                    report.Amount = ConAmount(rd);
31                    report.MaterialPN = rd["MATNR"].ToString();
32                    report.FromLoc = rd["LGORT"].ToString();
33                    report.ToLoc = rd["UMLGO"].ToString();
34                    string sbu = "select Customer from --------- where Material='" + report.MaterialPN + "' and Plant='" + report.Plant + "'";
35                    object customer = dbL.ExecuteScalar(CommandType.Text, sbu);
36                    if (customer != null)
37                        report.Customer = customer.ToString();
38                    reports.Add(report);
39                }
40            }
41            InsertTable(reports);
42        }

 上面的数据因为我读出时还要和服务器二里一些数据进行交互,中间我本是想用Linq to sql,但是这个时间不知是怎么回事.
用上面的代码30W行数据读出来大约10分钟左右.而我把基中要和服务器二交互的相关代码换成Linq to sql(就是 
        string sbu = "select Customer from ------- where Material='" + report.MaterialPN + "' and Plant='" + report.Plant + "'";
                    object customer = dbL.ExecuteScalar(CommandType.Text, sbu);
                    if (customer != null)
                        report.Customer = customer.ToString();)这句换成Linq to sql很简洁,一句话.我非常喜欢,可是
然后读出来,差不多快20分钟,其中可能有些别的原因.但是最后我还是用的DAAB.
这些数据读出来后,我用Linq进行分组统计然后插入.如下所示

 1        public void InsertTable(List<ReportD> reports)
 2        {
 3            List<ReportSum> reportstatus = (from report in reports
 4                                            // where report.Status == "Return" || report.Status == "Resell" || report.Status == "Reuse"
 5                                            group report by new
 6                                            {
 7                                                report.Year,
 8                                                report.WeekNum,
 9                                                report.Plant,
10                                                report.Status
11                                            } into g
12                                            select new ReportSum
13                                            {
14                                                Year = g.Key.Year,
15                                                WeekNum = g.Key.WeekNum,
16                                                Plant = g.Key.Plant,
17                                                Status = g.Key.Status,
18                                                AmountForStatus = g.Sum(report => report.Amount)
19                                            }).ToList<ReportSum>();
20            List<ReportSum> reportcustom = (from report in reports
21                                            where report.Customer == null && report.Status == "Return"
22                                            group report by new
23                                            {
24                                                report.Year,
25                                                report.WeekNum,
26                                                report.Plant,
27                                                report.Customer
28                                            } into g
29                                            select new ReportSum
30                                            {
31                                                Year = g.Key.Year,
32                                                WeekNum = g.Key.WeekNum,
33                                                Plant = g.Key.Plant,
34                                                Customer = g.Key.Customer,
35                                                AmountForCustomer = g.Sum(report => report.Amount)
36                                            }).ToList<ReportSum>();
37            db.ReportSum.InsertAllOnSubmit(reportstatus);
38            db.ReportSum.InsertAllOnSubmit(reportcustom);
39            db.SubmitChanges();
40            Common.LogCommon("数据已经插入在新数据库中");
41            BuildExel();
42        }

 这些数据统计后生成报表.如下.这代码晕死,惨不忍睹.

  1        public void BuildExel2()
  2        {
  3            Application myExcel = new Application();
  4            myExcel.Application.DisplayAlerts = false;
  5            Workbook wbook = myExcel.Workbooks.Add(true);
  6            //  myExcel.Visible = true;
  7            //得到从现在到N(Common.WeekNum)个星期前要输出的数据
  8            var weaks = db.ReportSum.Select(p => new WeekInYear { WeekNum = p.WeekNum, Year = p.Year }).Distinct().OrderByDescending(p => p.Year).ThenByDescending(p => p.WeekNum).Take(Common.WeekNum);
  9            weaks = weaks.OrderBy(p => p.Year).ThenBy(p => p.WeekNum);
 10            WeekInYear end = weaks.OrderBy(p => p.Year).ThenBy(p => p.WeekNum).First();
 11            var dataAll = db.ReportSum.Where(p => p.Year >= end.Year && p.WeekNum >= end.WeekNum).OrderBy(p => p.Year).ThenBy(p => p.WeekNum);
 12            //得到所有的工厂.
 13            List<string> plants = (from report in dataAll select report.Plant).Distinct().ToList<string>();
 14            "填充数据"#region "填充数据"
 15            foreach (string plant in plants)
 16            {
 17                Worksheet wsheet = null;
 18                wsheet = (Worksheet)myExcel.Worksheets.get_Item(1);
 19                myExcel.Worksheets.Add(wsheet, Type.Missing, 1, Type.Missing);
 20                wsheet = (Worksheet)myExcel.Worksheets.get_Item(1);
 21                wsheet.Name = plant;
 22                //取得这个工厂的所有数据
 23                var data = dataAll.Where(p => p.Plant == plant);
 24                Range range = wsheet.get_Range(wsheet.Cells[1, 1], wsheet.Cells[2, 8]);
 25                range.Merge(0);
 26                range = wsheet.get_Range(wsheet.Cells[1, 1], wsheet.Cells[1, 1]);
 27                range.Value2 = "Detail Report -- Return";
 28                range.Font.Size = 15;
 29                range.HorizontalAlignment = Constants.xlCenter;
 30                range.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();
 31                wsheet.get_Range(wsheet.Cells[4, 1], wsheet.Cells[4, 1]).Value2 = plant;
 32                List<string> customs = data.Where(p => p.Customer != null).Select(p => p.Customer).Distinct().ToList<string>();
 33                int m = 4;
 34                "填充数据到当前的活动页"#region "填充数据到当前的活动页"
 35                foreach (string custom in customs)
 36                {
 37                    if (custom == null)
 38                        continue;
 39                    m++;
 40                    wsheet.get_Range(wsheet.Cells[m, 1], wsheet.Cells[m, 1]).Value2 = custom;
 41                    int n = 1;
 42                    foreach (WeekInYear week in weaks)
 43                    {
 44                        n++;
 45                        Range cell = wsheet.get_Range(wsheet.Cells[4, n], wsheet.Cells[4, n]);
 46                        cell.Value2 = week.ToString();
 47                        cell.EntireColumn.AutoFit();
 48                        try
 49                        {
 50                            var ammount = data.Where(p => p.WeekNum == week.WeekNum && p.Year == week.Year && p.Customer == custom).First().AmountForCustomer;
 51                            wsheet.get_Range(wsheet.Cells[m, n], wsheet.Cells[m, n]).Value2 = ammount;
 52                        }
 53                        catch
 54                        {
 55                            wsheet.get_Range(wsheet.Cells[m, n], wsheet.Cells[m, n]).Value2 = 0;
 56                        }
 57                        wsheet.get_Range(wsheet.Cells[4, 1], wsheet.Cells[4 + customs.Count, 1 + weaks.Count()]).Borders.LineStyle = 1;
 58                    }
 59                }
 60                wsheet.get_Range(wsheet.Cells[4 + customs.Count, 1], wsheet.Cells[4 + customs.Count, 1]).Value2 = plant + "Return";
 61                int i = 1;
 62                foreach (WeekInYear week in weaks)
 63                {
 64                    i++;
 65                    try
 66                    {
 67                        var ammount = data.Where(p => p.WeekNum == week.WeekNum && p.Year == week.Year && p.Customer != null).Sum(p => p.AmountForCustomer);
 68                        if (ammount == null)
 69                            throw new Exception();
 70                        wsheet.get_Range(wsheet.Cells[4 + customs.Count, i], wsheet.Cells[4 + customs.Count, i]).Value2 = ammount;
 71                    }
 72                    catch
 73                    {
 74                        wsheet.get_Range(wsheet.Cells[4 + customs.Count, i], wsheet.Cells[4 + customs.Count, i]).Value2 = 0;
 75                    }
 76                }
 77               // FillExel<string,string,string>(wsheet, weaks, customs, data, 4, 1, plant);
 78                #endregion
 79                "填充chart"#region "填充chart"
 80                Chart chart = myExcel.Charts.Add(Type.Missing, wsheet, Type.Missing, Type.Missing) as Chart;
 81                chart.ChartType = Common.xlChartType;
 82                Range chartrange = wsheet.get_Range(wsheet.Cells[4, 1], wsheet.Cells[4 + customs.Count, 1 + weaks.Count()]);
 83                chart.SetSourceData(chartrange, XlRowCol.xlRows);
 84                chart.HasTitle = true;
 85                chart.ChartTitle.Text = "Detail Report -- Return";
 86                chart.Location(XlChartLocation.xlLocationAsObject, plant);
 87                float top = 200;
 88                float.TryParse(wsheet.get_Range(wsheet.Cells[1, 1], wsheet.Cells[1, 1]).Top.ToString(), out top);
 89                wsheet.Shapes.Item(1).IncrementTop(top );
 90                wsheet.Shapes.Item(1).IncrementLeft(-450);
 91                //wsheet.Shapes.Item(1).ScaleHeight(200,Type.Missing,Type.Missing);
 92                // wsheet.Shapes.Item(1).ScaleWidth(300, Type.Missing, Type.Missing);
 93                #endregion
 94                // wsheet.get_Range(wsheet.Cells[3,2],wsheet[5,4]).Value2 = if
 95            }
 96            #endregion
 97            if (File.Exists(Common.CustomPath))
 98            {
 99                File.Delete(Common.CustomPath);
100            }
101            wbook.SaveAs(Common.CustomPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
102            myExcel.Quit();
103
104            myExcel = null;
105            GC.Collect();
106            BuildExel3();
107        }

这里我有点郁闷了,因为用Group up分组显示后,在execl里一般会用到横转列.在这里的三个报表差不多全是一样.
但是其中因为有用对象的属性比较.而不好抽象出来,加上时间急.就暂时没有理会.就造成的如下结果.相同的代码我写了三次.
后我在空余时间分析了一下,想写通用设计.如下篇.