/// <summary>
        /// 导出终端列表到excel
        /// </summary>
        /// <param name="bll"></param>
        /// <param name="excel"></param>
        private void ExportTerminalsToExcel(ExcelHandlerBLL bll, TB_ExcelHandler excel)
        {
            string      source = "", data = "";
            Application app   = null;
            Workbook    book  = null;
            Worksheet   sheet = null;

            try
            {
                app         = new Application();
                book        = app.Workbooks.Open(EXCEL_PATH + EXCEL_TERMINALS);
                sheet       = (Worksheet)book.ActiveSheet;
                app.Visible = false;
                app.AlertBeforeOverwriting = false;
                app.DisplayAlerts          = false;

                using (var tbll = new TerminalBLL())
                {
                    using (var ebll = new EquipmentBLL())
                    {
                        int line = 2;
                        int cnt  = 0;
                        var n    = (int?)null;
                        var list = tbll.FindList(f => f.Delete == false);
                        foreach (var obj in list)
                        {
                            var x = line + cnt;
                            sheet.Cells[x, 1] = (cnt + 1);
                            sheet.Cells[x, 2] = obj.Number;
                            sheet.Cells[x, 3] = n == obj.Satellite ? "-" : obj.TB_Satellite.CardNo;
                            sheet.Cells[x, 4] = string.IsNullOrEmpty(obj.Firmware) ? "-" : obj.Firmware;
                            sheet.Cells[x, 5] = obj.Revision;
                            sheet.Cells[x, 6] = TerminalTypes.GetTerminalType(obj.Type.Value);
                            sheet.Cells[x, 7] = obj.ProductionDate.Value.ToString("yyyy/MM/dd");
                            var e = ebll.Find(d => d.Terminal == obj.id && d.Deleted == false);
                            sheet.Cells[x, 8] = null == e ? "-" : ebll.GetFullNumber(e);

                            var link = EverdigmUtils.GetOnlineStyle(obj.OnlineStyle, obj.OnlineTime, false);
                            link = link.Substring(link.IndexOf('>') + 1);
                            link = link.Substring(0, link.IndexOf('<'));
                            sheet.Cells[x, 9]  = string.IsNullOrEmpty(link) ? "-" : link;
                            sheet.Cells[x, 10] = null == obj.OnlineTime ? "-" : obj.OnlineTime.Value.ToString("yyyy/MM/dd HH:mm");
                            cnt++;
                        }
                    }
                }

                // 另存为别的
                var date = excel.CreateDate.Value.ToString("yyyyMMdd");
                var path = Path.Combine(WEB_PATH, "files\\xls\\", date);
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                source = path + "\\Terminals2Excel_" + excel.CreateDate.Value.ToString("yyyyMMdd") + ".xlsx";
                if (File.Exists(source))
                {
                    File.Delete(source);
                }
                book.SaveAs(source);
            }
            catch (Exception e)
            {
                data = e.StackTrace;
                ShowUnhandledMessage(format("{0}Terminal to Excel handler error: {1}{2}{3}", Now, e.Message, Environment.NewLine, e.StackTrace));
            }
            finally
            {
                // 关闭book
                if (null != book)
                {
                    book.Close();
                    book = null;
                }
                // 关闭application
                if (null != app)
                {
                    app.Quit();
                    app = null;
                }
                // 释放内存
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            var target = "../" + source.Replace(WEB_PATH, "").Replace("\\", "/");

            bll.Update(f => f.id == excel.id, act =>
            {
                act.Handled = true;
                act.Status  = (byte)(string.IsNullOrEmpty(data) ? 0 : 1);
                act.Target  = target;
                act.Data    = data;
            });
        }
        /// <summary>
        /// 导出设备列表到excel
        /// </summary>
        /// <param name="bll"></param>
        /// <param name="excel"></param>
        private void ExportEquipmentsToExcel(ExcelHandlerBLL bll, TB_ExcelHandler excel)
        {
            // data为保存出错时的异常数据
            string      source = "", data = "";
            Application app   = null;
            Workbook    book  = null;
            Worksheet   sheet = null;

            try
            {
                app         = new Application();
                book        = app.Workbooks.Open(EXCEL_PATH + EXCEL_EQUIPMENTS);
                sheet       = (Worksheet)book.ActiveSheet;
                app.Visible = false;
                app.AlertBeforeOverwriting = false;
                app.DisplayAlerts          = false;

                using (var ebll = new EquipmentBLL())
                {
                    int line = 3;
                    int cnt  = 0;
                    var n    = (int?)null;
                    var list = ebll.FindList(f => f.Deleted == false);
                    foreach (var obj in list)
                    {
                        var x = line + cnt;
                        sheet.Cells[x, 1] = (cnt + 1);
                        sheet.Cells[x, 2] = n == obj.Model ? "-" : obj.TB_EquipmentModel.TB_EquipmentType.Code;
                        sheet.Cells[x, 3] = n == obj.Model ? "-" : ebll.GetFullNumber(obj);
                        sheet.Cells[x, 4] = EverdigmUtils.GetEquipmentFunctional(obj.Functional.Value);
                        sheet.Cells[x, 5] = EquipmentBLL.GetRuntime(obj.Runtime + obj.InitializedRuntime, obj.CompensatedHours.Value);
                        sheet.Cells[x, 6] = ebll.GetEngineState(obj);
                        sheet.Cells[x, 7] = string.IsNullOrEmpty(obj.GpsAddress) ? "-" : obj.GpsAddress;
                        // 状态
                        sheet.Cells[x, 8] = obj.TB_EquipmentStatusName.Code;
                        // customer
                        sheet.Cells[x, 9]  = n == obj.Customer ? "-" : obj.TB_Customer.Code;
                        sheet.Cells[x, 10] = n == obj.Customer ? "-" : obj.TB_Customer.Name;
                        // 终端
                        var link = EverdigmUtils.GetOnlineStyle(obj.OnlineStyle, obj.OnlineTime, false);
                        link = link.Substring(link.IndexOf('>') + 1);
                        link = link.Substring(0, link.IndexOf('<'));
                        sheet.Cells[x, 11] = string.IsNullOrEmpty(link) ? "-" : link;
                        string alarm = ebll.GetAlarmStatus(obj.Alarm);
                        alarm = alarm.Substring(alarm.IndexOf("title=\"") + 7);
                        alarm = alarm.Substring(0, alarm.IndexOf('"'));
                        sheet.Cells[x, 12] = alarm.Contains("No") ? "-" : alarm;
                        sheet.Cells[x, 13] = null == obj.LastActionTime ? "-" : obj.LastActionTime.Value.ToString("yyyy/MM/dd HH:mm");
                        sheet.Cells[x, 14] = n == obj.Terminal ? "-" : obj.TB_Terminal.Number;
                        bool sat = n != obj.Terminal && n != obj.TB_Terminal.Satellite;
                        sheet.Cells[x, 15] = sat ? obj.TB_Terminal.TB_Satellite.CardNo : "-";
                        sheet.Cells[x, 16] = n == obj.Warehouse ? "-" : obj.TB_Warehouse.Name;
                        cnt++;
                    }
                }
                // 另存为别的
                var date = excel.CreateDate.Value.ToString("yyyyMMdd");
                var path = Path.Combine(WEB_PATH, "files\\xls\\", date);
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                source = path + "\\Equipments2Excel_" + excel.CreateDate.Value.ToString("yyyyMMdd") + ".xlsx";
                if (File.Exists(source))
                {
                    File.Delete(source);
                }
                book.SaveAs(source);
            }
            catch (Exception e)
            {
                data = e.StackTrace;
                ShowUnhandledMessage(format("{0}Equipment to Excel handler error: {1}{2}{3}", Now, e.Message, Environment.NewLine, e.StackTrace));
            }
            finally
            {
                // 关闭book
                if (null != book)
                {
                    book.Close();
                    book = null;
                }
                // 关闭application
                if (null != app)
                {
                    app.Quit();
                    app = null;
                }
                // 释放内存
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            var target = "../" + source.Replace(WEB_PATH, "").Replace("\\", "/");

            bll.Update(f => f.id == excel.id, act =>
            {
                act.Handled = true;
                act.Target  = target;
                act.Status  = (byte)(string.IsNullOrEmpty(data) ? 0 : 1);
                act.Data    = data;
            });
        }
Пример #3
0
        private void ExportWorkTimeToExcel(TB_ExcelHandler excel, ExcelHandlerBLL bll)
        {
            string      source = "", data = "";
            Application app   = null;
            Workbook    book  = null;
            Worksheet   sheet = null;

            try
            {
                app         = new Application();
                book        = app.Workbooks.Open(EXCEL_PATH + EXCEL_WORKTIME);
                sheet       = (Worksheet)book.ActiveSheet;
                app.Visible = false;
                app.AlertBeforeOverwriting = false;
                app.DisplayAlerts          = false;

                var equipment = excel.TB_Equipment.TB_EquipmentModel.Code + excel.TB_Equipment.Number;
                sheet.Name = equipment;
                // 所属公司
                string customer = "";
                if (excel.TB_Equipment.Customer == (int?)null)
                {
                    customer = "invalid";
                }
                else
                {
                    customer = excel.TB_Equipment.TB_Customer.Name;
                }
                sheet.Cells[3, 3] = customer;
                // 设备型号
                sheet.Cells[4, 3] = excel.TB_Equipment.TB_EquipmentModel.Name;
                // 设备号码
                sheet.Cells[5, 3] = excel.TB_Equipment.Number;
                // 设备出库日期
                string outdoor = "";
                if (null == excel.TB_Equipment.OutdoorTime)
                {
                    outdoor = "invalid";
                }
                else
                {
                    outdoor = excel.TB_Equipment.OutdoorTime.Value.ToString("yyyy/MM/dd");
                }
                sheet.Cells[6, 3] = outdoor;
                // 打印日期
                sheet.Cells[6, 9] = excel.CreateDate.Value.ToString("yyyy/MM/dd");
                // 出库类型
                sheet.Cells[7, 3] = excel.TB_Equipment.TB_EquipmentStatusName.Code;
                sheet.Cells[8, 3] = excel.TB_Equipment.GpsAddress;
                // 查询开始日期
                sheet.Cells[10, 3] = excel.StartDate;
                sheet.Cells[11, 3] = excel.EndDate;

                // 组织数据
                List <WorktimeChart> works = JsonConverter.ToObject <List <WorktimeChart> >(excel.Data);
                int    row = 0, cell = 1, page = 0, len = works.Count, count = 0;
                double total = 0.0, pcount = 0.0;
                for (int i = 0; i < len; i++)
                {
                    count++;
                    page = i / (pageSize);
                    var baseRow = page * pageRows;
                    cell = (i / pageCount) % 2 == 0 ? 1 : 6;
                    row  = baseRow + i % pageCount;
                    row += lineStart;
                    sheet.Cells[row, cell]     = count;
                    sheet.Cells[row, cell + 1] = works[i].date;
                    sheet.Cells[row, cell + 2] = works[i].y;
                    total  += works[i].y;
                    pcount += works[i].y;
                    // 如果运转时间等于0时,将上一条运转时间复制过来
                    if (works[i].min == 0 && i > 0)
                    {
                        works[i].min = works[i - 1].min;
                    }
                    sheet.Cells[row, cell + 3] = works[i].min / 60.0;
                    if (count % pageCount == 0)
                    {
                        sheet.Cells[row + 1, cell + 1] = "subtotal";
                        sheet.Cells[row + 1, cell + 2] = pcount;
                        Range range = sheet.Range[sheet.Cells[row + 1, cell], sheet.Cells[row + 1, cell + 3]];
                        range.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle    = XlLineStyle.xlContinuous;
                        range.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].Color        = XlRgbColor.rgbGray;
                        range.Cells.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                        pcount = 0.0;
                    }
                }
                // 总运转时间
                sheet.Cells[12, 3] = total;
                //sheet.Cells[12, 4] = works[0].add;

                // 最后一页中的统计
                sheet.Cells[row + 1, cell + 1] = "subtotal";
                sheet.Cells[row + 1, cell + 2] = pcount;
                Range last = sheet.Range[sheet.Cells[row + 1, cell], sheet.Cells[row + 1, cell + 3]];
                last.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].LineStyle    = XlLineStyle.xlContinuous;
                last.Cells.Borders.Item[XlBordersIndex.xlEdgeTop].Color        = XlRgbColor.rgbGray;
                last.Cells.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;

                // 另存为别的
                var date = excel.CreateDate.Value.ToString("yyyyMMdd");
                var path = Path.Combine(WEB_PATH, "files\\xls\\", date);
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                source = path + "\\Equipment operation report_" + equipment + excel.CreateDate.Value.ToString("_HHmmss") + ".xlsx";
                if (File.Exists(source))
                {
                    File.Delete(source);
                }
                book.SaveAs(source);
            }
            catch (Exception e)
            {
                data = e.StackTrace;
                ShowUnhandledMessage(format("{0}Worktime to Excel handler error: {1}{2}{3}", Now, e.Message, Environment.NewLine, e.StackTrace));
            }
            finally
            {
                // 关闭book
                if (null != book)
                {
                    book.Close();
                    book = null;
                }
                // 关闭application
                if (null != app)
                {
                    app.Quit();
                    app = null;
                }
                // 释放内存
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }

            var target = "../" + source.Replace(WEB_PATH, "").Replace("\\", "/");

            bll.Update(f => f.id == excel.id, act =>
            {
                act.Handled = true;
                act.Status  = (byte)(string.IsNullOrEmpty(data) ? 0 : 1);
                act.Target  = target;
                act.Data    = data;
            });
        }