using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DIXWeb.Entity.DIX;
using DIXWeb.DAL;

namespace DIXWeb.Business.DIX
{
    public class ToExcelTemplate:ExcelBaseNPOI
    {
        public ToExcelTemplate() { }
        public ToExcelTemplate(string filePath, string sheetName = "") : base(filePath, sheetName) { }

        public static string FileName
        {
            get { return "ToDoList模板"; }
        }

        #region 重写样式 字典
        protected override Dictionary<string, ICellStyle> createCellStyles(IWorkbook wb)
        {
            //return base.createCellStyles(wb);
            Dictionary<string, ICellStyle> styles = new Dictionary<string, ICellStyle>();
            IDataFormat df = wb.CreateDataFormat();
            NPOI.SS.UserModel.IFont MsFont_9_Black_Font = wb.CreateFont();
            MsFont_9_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
            SetFontHeight(MsFont_9_Black_Font, 9);
            MsFont_9_Black_Font.FontName = "微软雅黑";

            NPOI.SS.UserModel.IFont MsFont_9_Blue_Font = wb.CreateFont();
            MsFont_9_Blue_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
            MsFont_9_Blue_Font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            SetFontHeight(MsFont_9_Blue_Font, 9);
            MsFont_9_Blue_Font.FontName = "微软雅黑";

            NPOI.SS.UserModel.IFont MsFont_9_Red_Font = wb.CreateFont();
            MsFont_9_Red_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
            MsFont_9_Red_Font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
            SetFontHeight(MsFont_9_Red_Font, 9);
            MsFont_9_Red_Font.FontName = "微软雅黑";


            NPOI.SS.UserModel.ICellStyle cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            styles.Add("微黑_9_V中H中_换行_全框细", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            styles.Add("微黑_9_V中H右_换行_全框细", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Blue_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            styles.Add("微黑_9_V中H左_换行_全框细_蓝字", cellStyle);


            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = true;  //上锁
            styles.Add("微黑_9_V中H中_换行_全框细_上锁", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Red_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            styles.Add("微黑_红9_V中H中_换行_全框细", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
            styles.Add("微黑_9_V中H中_换行_全框细_蓝背景", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = true;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
            styles.Add("微黑_9_V中H中_换行_全框细_蓝背景_上锁", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Blue_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = true;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
            styles.Add("微黑_9蓝_V中H中_换行_全框细_蓝背景_上锁", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BrightGreen.Index;
            styles.Add("微黑_9_V中H右_换行_全框细_绿背景", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
            styles.Add("微黑_9_V中H中_换行_全框细_柠檬黄背景", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
            styles.Add("微黑_9_V中H右_换行_全框细_柠檬黄背景", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = true;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
            styles.Add("微黑_9_V中H中_换行_全框细_柠檬黄背景_上锁", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (false);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.DataFormat = (df.GetFormat("m-d h:mm"));
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index;
            styles.Add("微黑_9_V中H中_不换行_全框细_柠檬黄背景_日期", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (true);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
            styles.Add("微黑_9_V中H中_换行_全框细_淡蓝背景", cellStyle);


            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_9_Black_Font);
            cellStyle.WrapText = (false);
            cellStyle.IsLocked = false;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index;
            cellStyle.DataFormat = (df.GetFormat("m-d h:mm"));
            styles.Add("微黑_9_V中H中_不换行_全框细_淡蓝背景_日期", cellStyle);


            NPOI.SS.UserModel.IFont Bold_13_Black_Font = wb.CreateFont();
            Bold_13_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            Bold_13_Black_Font.FontHeight = 13 * 20;      //字体高度，XLSX下有字体大小问题
            //Bold_15_Black_Font.FontHeightInPoints = 10; //字体高度，XLS下有大小问题
            Bold_13_Black_Font.FontName = "黑体";

            NPOI.SS.UserModel.IFont Bold_15_Black_Font = wb.CreateFont();
            Bold_15_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            Bold_15_Black_Font.FontHeight = 15 * 20;      //字体高度，XLSX下有字体大小问题
            //Bold_15_Black_Font.FontHeightInPoints = 10; //字体高度，XLS下有大小问题
            Bold_15_Black_Font.FontName = "黑体";

            NPOI.SS.UserModel.IFont MsFont_12_Black_Font = wb.CreateFont();
            MsFont_12_Black_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
            SetFontHeight(MsFont_12_Black_Font, 12);
            MsFont_12_Black_Font.FontName = "微软雅黑";

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(Bold_13_Black_Font);
            //cellStyle.WrapText = (false);//换行
            styles.Add("Bold_13_Black_Font", cellStyle);

            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(Bold_15_Black_Font);
            //cellStyle.WrapText = (false);
            styles.Add("Bold_15_Black_Font", cellStyle);


            cellStyle = CreateBorderedStyle(wb, BorderStyle.Thin);
            cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cellStyle.SetFont(MsFont_12_Black_Font);
            //cellStyle.WrapText = (true);//换行
            cellStyle.IsLocked = true;
            styles.Add("微黑_12_V中H中_全框细_上锁", cellStyle);

            return styles;
        }
        #endregion

        #region 下载模板
        public bool ExportTemplate(List<ExcelEntity> recordList)
        {
            #region Sheet基本设置
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet(ExcelMainSheetName);
            Dictionary<String, ICellStyle> styles = createCellStyles(hssfworkbook);
            if (IsXlsx == false)
                sheet.TabColorIndex = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
            NPOI.SS.UserModel.IPrintSetup printSetup = sheet.PrintSetup;
            sheet.DisplayGridlines = false;
            SetDefaultRowHeight(sheet, 28.0F);

            sheet.FitToPage = (true);
            sheet.HorizontallyCenter = (true);  //设置Sheet缩放
            sheet.SetZoom(82, 100);              // 100 percent magnification
            SetColumnWidth(sheet, 00, 25.00);
            SetColumnWidth(sheet, 01, 25.00);
            SetColumnWidth(sheet, 02, 25.00);
            SetColumnWidth(sheet, 03, 25.00);
            SetColumnWidth(sheet, 04, 25.00);
            SetColumnWidth(sheet, 05, 25.00);
            SetColumnWidth(sheet, 06, 25.00);
            SetColumnWidth(sheet, 07, 25.00);
            SetColumnWidth(sheet, 08, 25.00);
            SetColumnWidth(sheet, 09, 25.00);
            SetColumnWidth(sheet, 10, 25.00);

            sheet.SetColumnHidden(24, true);
            #endregion

            AddDropDownDataValidation(sheet, 1, 65535, 7, 7, new string[] { "量试", "量产", "实验" });

            IRow curRow = sheet.CreateRow(0);
            ICell curCel = null;
            SetRowHeight(curRow, 33);//第一行行高
            #region 第一行 列头
            curCel = curRow.CreateCell(0);
            SetColumnWidth(sheet, 00, 25.00);
            curCel.SetCellValue("OPID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(1);
            SetColumnWidth(sheet, 01, 15.00);
            curCel.SetCellValue("判定结果");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(2);
            SetColumnWidth(sheet, 02, 15.00);
            curCel.SetCellValue("线别");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(3);
            SetColumnWidth(sheet, 03, 15.00);
            curCel.SetCellValue("机台");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(4);
            SetColumnWidth(sheet, 04, 15.00);
            curCel.SetCellValue("机种");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(5);
            SetColumnWidth(sheet, 05, 20.00);
            curCel.SetCellValue("工位");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(6);
            SetColumnWidth(sheet, 06, 15.00);
            curCel.SetCellValue("拍照部位");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(7);
            SetColumnWidth(sheet, 07, 25.00);
            curCel.SetCellValue("画像链接");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(8);
            SetColumnWidth(sheet, 08, 30.00);
            curCel.SetCellValue("图片保存时间");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(9);
            SetColumnWidth(sheet, 09, 10.00);
            curCel.SetCellValue("拓展1");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(10);
            SetColumnWidth(sheet, 10, 10.00);
            curCel.SetCellValue("拓展2");
            curCel.CellStyle = styles["Bold_13_Black_Font"];
            #endregion

            int row = 1;
            int col = 0;
            foreach (ExcelEntity item in recordList)
            {
                col = 0;//重新初始化
                curRow = sheet.CreateRow(row++);

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.OPID);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.Judge);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.Line);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.MtNo);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.Model);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.WorkStation);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.Part);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.FileName);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];
                HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
                link.Address = item.FileName;
                curCel.Hyperlink = link;

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.CreateTime);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.Extend1);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];

                curCel = curRow.CreateCell(col++);
                curCel.SetCellValue(item.Extend2);
                curCel.CellStyle = styles["微黑_12_V中H中_全框细_上锁"];
            }

            return WriteToFile(this.ExcelFilePath);
        }


        public bool ExportTemplate()
        {
            #region Sheet基本设置
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet(ExcelMainSheetName);
            Dictionary<String, ICellStyle> styles = createCellStyles(hssfworkbook);
            if (IsXlsx == false)
                sheet.TabColorIndex = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
            NPOI.SS.UserModel.IPrintSetup printSetup = sheet.PrintSetup;
            sheet.DisplayGridlines = false;
            SetDefaultRowHeight(sheet, 28.0F);

            sheet.FitToPage = (true);
            sheet.HorizontallyCenter = (true);  //设置Sheet缩放
            sheet.SetZoom(82, 100);              // 100 percent magnification
            SetColumnWidth(sheet, 00, 25.00);
            SetColumnWidth(sheet, 01, 25.00);
            SetColumnWidth(sheet, 02, 25.00);
            SetColumnWidth(sheet, 03, 25.00);
            SetColumnWidth(sheet, 04, 25.00);
            SetColumnWidth(sheet, 05, 25.00);
            SetColumnWidth(sheet, 06, 25.00);
            SetColumnWidth(sheet, 07, 25.00);
            SetColumnWidth(sheet, 08, 25.00);
            SetColumnWidth(sheet, 09, 25.00);
            SetColumnWidth(sheet, 10, 25.00);

            sheet.SetColumnHidden(24, true);
            #endregion

            string[] orgArr = GetOrg();
            string[] iotArr = GetInspectObjectType();
            string[] iaArr = GetInspectArea();
            string[] itArr = GetInspectType();
            string[] ifArr = GetInspectFrequency();
            string[] isArr = GetInspectStatu();
            string InspectObjectIsNeedStr = System.Configuration.ConfigurationManager.AppSettings["InspectObjectIsNeedStr"].ToString();
            string[] iNArr = InspectObjectIsNeedStr.Split(',');
            AddDropDownDataValidation(sheet, 1, 65535, 0, 0, orgArr);
            AddDropDownDataValidation(sheet, 1, 65535, 1, 1, iotArr);
            AddDropDownDataValidation(sheet, 1, 65535, 4, 4, iaArr);
            AddDropDownDataValidation(sheet, 1, 65535, 6, 6, itArr);
            AddDropDownDataValidation(sheet, 1, 65535, 7, 7, ifArr);
            AddDropDownDataValidation(sheet, 1, 65535, 8, 8, isArr);
            AddDropDownDataValidation(sheet, 1, 65535, 9, 9, iNArr);

            IRow curRow = sheet.CreateRow(0);
            ICell curCel = null;
            SetRowHeight(curRow, 33);//第一行行高
            #region 第一行 列头
            curCel = curRow.CreateCell(0);
            SetColumnWidth(sheet, 00, 15.00);
            curCel.SetCellValue("组织ID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(1);
            SetColumnWidth(sheet, 01, 25.00);
            curCel.SetCellValue("设备类型ID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(2);
            SetColumnWidth(sheet, 02, 15.00);
            curCel.SetCellValue("设备编号");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(3);
            SetColumnWidth(sheet, 03, 15.00);
            curCel.SetCellValue("设备名称");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(4);
            SetColumnWidth(sheet, 04, 25.00);
            curCel.SetCellValue("设备区域ID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(5);
            SetColumnWidth(sheet, 05, 15.00);
            curCel.SetCellValue("设备位置");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(6);
            SetColumnWidth(sheet, 06, 25.00);
            curCel.SetCellValue("点检周期ID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(7);
            SetColumnWidth(sheet, 07, 20.00);
            curCel.SetCellValue("点检频率ID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(8);
            SetColumnWidth(sheet, 08, 20.00);
            curCel.SetCellValue("设备状态ID");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(9);
            SetColumnWidth(sheet, 09, 30.00);
            curCel.SetCellValue("是否需要点检");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(10);
            SetColumnWidth(sheet, 10, 25.00);
            curCel.SetCellValue("导入日期");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            curCel = curRow.CreateCell(11);
            SetColumnWidth(sheet, 11, 25.00);
            curCel.SetCellValue("有效期(年)");
            curCel.CellStyle = styles["Bold_13_Black_Font"];

            //curCel = curRow.CreateCell(12);
            //SetColumnWidth(sheet, 12, 10.00);
            //curCel.SetCellValue("设备年限");
            //curCel.CellStyle = styles["Bold_13_Black_Font"];
            #endregion
            
            return WriteToFile(this.ExcelFilePath);
        }
        #endregion


        public string[] GetOrg() {
            List<string> list = new List<string>();
            using (DBContext db = new DBContext())
            {
                list = db.Database.SqlQuery<string>("select CONVERT(varchar,Id)+'>'+[Name] as text from DIX.dbo.OrgInfo with(nolock) where parentId = 0 order by Id asc ").ToList();
            }
            string[] arr = list.ToArray<string>();
            return arr;
        }

        public string[] GetInspectObjectType()
        {
            string GetInspectObjectTypeExcel = System.Configuration.ConfigurationManager.AppSettings["GetInspectObjectTypeExcel"].ToString();
            List<string> list = new List<string>();
            using (DBContext db = new DBContext())
            {
                list = db.Database.SqlQuery<string>("meta.p_GetInspectObjectTypeExcel '"+ GetInspectObjectTypeExcel + "' ").ToList();
            }
            string[] arr = list.ToArray<string>();
            return arr;
        }

        public string[] GetInspectArea()
        {
            List<string> list = new List<string>();
            using (DBContext db = new DBContext())
            {
                list = db.Database.SqlQuery<string>("select CONVERT(varchar,Id)+'>'+[Name] as text from DIX.meta.InspectArea with(nolock) order by Id asc ").ToList();
            }
            string[] arr = list.ToArray<string>();
            return arr;
        }

        public string[] GetInspectType()
        {
            List<string> list = new List<string>();
            using (DBContext db = new DBContext())
            {
                list = db.Database.SqlQuery<string>("select CONVERT(varchar,Id)+'>'+[Name] as text from DIX.meta.InspectType with(nolock) order by Id asc ").ToList();
            }
            string[] arr = list.ToArray<string>();
            return arr;
        }

        public string[] GetInspectFrequency()
        {
            List<string> list = new List<string>();
            using (DBContext db = new DBContext())
            {
                list = db.Database.SqlQuery<string>("select CONVERT(varchar,Id)+'>'+Frequency as text from DIX.meta.InspectFrequency with(nolock) order by Id asc ").ToList();
            }
            string[] arr = list.ToArray<string>();
            return arr;
        }

        public string[] GetInspectStatu()
        {
            List<string> list = new List<string>();
            using (DBContext db = new DBContext())
            {
                list = db.Database.SqlQuery<string>("select CONVERT(varchar,Id)+'>'+Status as text from DIX.meta.InspectObjectStatusInfo with(nolock) order by Id asc ").ToList();
            }
            string[] arr = list.ToArray<string>();
            return arr;
        }

    }
}