using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Estsh.Web.Util;
using Estsh.Web.Service;
using System.Collections;
using Estsh.Web.Models;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;

/***************************************************************************************************
 * 
 * 作者：王勇
 * 创建时间：2013.04.15
 * 贵阳
 * *************************************************************************************************/
namespace Estsh.Core.Web.Controllers
{
    /// <summary>
    /// 客户订单维护
    /// </summary>
    public class PartMasterGYController : Controller
    {
        private PartMasterGYService service = new PartMasterGYService();

        //
        // GET: /PartMaster/
        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 获取列表数据
        /// </summary>
        /// <param name="PartMasterName">菜单名称</param>
        /// <param name="pager">分页</param>
        /// <param name="direction">排序方式</param>
        /// <param name="sort">排序列</param>
        /// <returns></returns>
        public ActionResult getPartMasterListByPage(String part_type, String part_no, String model_name, Pager pager, String direction, String sort)
        {
            if (String.IsNullOrEmpty(part_type))
            {
                return null;
            }
            Hashtable result = new Hashtable();
            result.Add("pager.pageNo", pager.pageNo);
            Hashtable dataHt = this.service.getPartMasterListByPage(part_type, part_no, model_name, pager, direction, sort);
            result.Add("rows", dataHt["dataList"]);
            result.Add("pager.totalRows", dataHt["totalCount"]);
            result.Add("sort", sort);
            result.Add("direction", direction);
            return Json(result);
        }

        /// <summary>
        /// 获取下拉列表数据
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartMaster()
        {
            Hashtable result = new Hashtable();
            ArrayList PartMasterList = this.service.getSelectPartMaster();
            result.Add("list", PartMasterList);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <returns></returns>
        public ActionResult savePartMaster(string part_type)
        {
            Hashtable result = new Hashtable();

            if (string.IsNullOrEmpty(part_type) || part_type == "null")
            {
                //result.Add("message", "系统异常，请重新提交。");
                //return Json(result);
                part_type = "0";
            }

            String editType = Request["editType"].ToString();

            String part_id = Request["part_id"].ToString();
            //标签一
            String part_spec = Request["part_spec"].ToString();
            String part_no = Request["part_no"].ToString();
            String partType = part_type;
            String part_location = Request["enum_value"].ToString();
            String model_id = Request["model_id"].ToString();
            String version = Request["version"].ToString();
            String cust_part_no = Request["cust_part_no"].ToString();
            String mfger_part_no = Request["mfger_part_no"].ToString();
            String vendor_part_no = Request["vendor_part_no"].ToString();
            String erp_part_no = Request["erp_part_no"].ToString();
            String uom = Request["uom"].ToString();
            String part_class = Request["part_class"].ToString();
            String part_no_3c = Request["part_no_3c"].ToString();

            //标签二
            String label_file = Request["label_file"].ToString();
            String label_type = Request["label_type"].ToString();
            String locate_id = Request["locate_id"].ToString();
            String default_box_qty = "0";
            if (string.IsNullOrEmpty(Request["default_box_qty"].ToString()))
            {
                default_box_qty = "0";
            }
            else
            {
                default_box_qty = Request["default_box_qty"].ToString();
            }
            String default_reel_qty = "0";
            if (string.IsNullOrEmpty(Request["default_reel_qty"].ToString()))
            {
                default_reel_qty = "0";
            }
            else
            {
                default_reel_qty = Request["default_reel_qty"].ToString();
            }
            String default_set_qty = "0";
            if (string.IsNullOrEmpty(Request["default_set_qty"].ToString()))
            {
                default_set_qty = "0";
            }
            else
            {
                default_set_qty = Request["default_set_qty"].ToString();
            }
            String min_pack_qty = "0";
            if (string.IsNullOrEmpty(Request["min_pack_qty"].ToString()))
            {
                min_pack_qty = "0";
            }
            else
            {
                min_pack_qty = Request["min_pack_qty"].ToString();
            }
            String min_stock_qty = "0";
            if (string.IsNullOrEmpty(Request["min_stock_qty"].ToString()))
            {
                min_stock_qty = "0";
            }
            else
            {
                min_stock_qty = Request["min_stock_qty"].ToString();
            }
            String max_stock_qty = "0";
            if (string.IsNullOrEmpty(Request["max_stock_qty"].ToString()))
            {
                max_stock_qty = "0";
            }
            else
            {
                max_stock_qty = Request["max_stock_qty"].ToString();
            }
            String indate = Request["indate"].ToString();
            //String is_sort = "0";
            //if (string.IsNullOrEmpty(Request["is_sort"].ToString()))
            //{
            //    is_sort = "0";
            //}
            //else
            //{
            //    is_sort = Request["is_sort"].ToString();
            //}
            //String sort_qty = "0";
            //if (string.IsNullOrEmpty(Request["sort_qty"].ToString()))
            //{
            //    sort_qty = "0";
            //}
            //else
            //{
            //    sort_qty = Request["sort_qty"].ToString();
            //}
            //String sort_type = Request["sort_type"].ToString();
            String batch_control = Request["batch_control"].ToString();
            String split_flag = Request["split_flag"].ToString();
            String over_request = Request["over_request"].ToString();
            String indate_flag = Request["indate_flag"].ToString();


            //标签三
            String burnin_time = Request["burnin_time"].ToString();
            String subboard_qty = "0";
            if (string.IsNullOrEmpty(Request["subboard_qty"].ToString()))
            {
                subboard_qty = "0";
            }
            else
            {
                subboard_qty = Request["subboard_qty"].ToString();
            }
            String UPC_code = Request["UPC_code"].ToString();
            String UCC_code = Request["UCC_code"].ToString();
            String EAN_code = Request["EAN_code"].ToString();

            Hashtable htParams = new Hashtable();
            htParams.Add("@part_id", part_id);
            //标签一
            htParams.Add("@part_spec", part_spec);
            htParams.Add("@part_no", part_no);
            htParams.Add("@part_type", partType);
            htParams.Add("@part_location", part_location);
            htParams.Add("@model_id", model_id);
            htParams.Add("@version", version);
            htParams.Add("@cust_part_no", cust_part_no);
            htParams.Add("@mfger_part_no", mfger_part_no);
            htParams.Add("@vendor_part_no", vendor_part_no);
            htParams.Add("@erp_part_no", erp_part_no);
            htParams.Add("@uom", uom == "" ? "EA" : uom);
            htParams.Add("@part_class", part_class);
            htParams.Add("@part_no_3c", part_no_3c);

            //标签二
            htParams.Add("@label_file", label_file);
            htParams.Add("@label_type", label_type);
            htParams.Add("@locate_id", locate_id);
            htParams.Add("@default_box_qty", default_box_qty);
            htParams.Add("@default_reel_qty", default_reel_qty);
            htParams.Add("@default_set_qty", default_set_qty);
            htParams.Add("@min_pack_qty", min_pack_qty);
            htParams.Add("@min_stock_qty", min_stock_qty);
            htParams.Add("@max_stock_qty", max_stock_qty);
            htParams.Add("@indate", indate);
            //htParams.Add("@is_sort", is_sort);
            //htParams.Add("@sort_qty", sort_qty);
            //htParams.Add("@sort_type", sort_type);
            htParams.Add("@batch_control", batch_control);
            htParams.Add("@split_flag", split_flag);
            htParams.Add("@over_request", over_request);
            htParams.Add("@indate_flag", indate_flag);

            //标签三
            htParams.Add("@burnin_time", burnin_time);
            htParams.Add("@subboard_qty", subboard_qty);
            htParams.Add("@UPC_code", UPC_code);
            htParams.Add("@UCC_code", UCC_code);
            htParams.Add("@EAN_code", EAN_code);


            String message = "";
            if (label_type.Equals("1"))
            {
                if (!vendor_part_no.Trim().Equals("") && vendor_part_no != null && !mfger_part_no.Trim().Equals("") && mfger_part_no!=null)
                {
                    if (editType != null && editType.Trim().Equals("edit"))
                    {
                        try
                        {
                            UserInfo user = (UserInfo)Session["loginedUser"];
                            htParams.Add("@update_userid", user.empId);
                            this.service.updatePartMaster(htParams);
                            message = "修改成功";
                        }
                        catch (Exception e)
                        {
                            message = "修改失败！";
                        }
                    }
                    else
                    {
                        try
                        {
                            UserInfo user = (UserInfo)Session["loginedUser"];
                            htParams.Add("@create_userid", user.empId);
                            this.service.savePartMaster(htParams);
                            message = "添加成功";
                        }
                        catch (Exception e)
                        {
                            message = "添加失败！";
                        }
                    }
                }else{
                    message = "操作失败，对焊接总成进行操作需要填写供应商零件号和制造商零件号";
                }
            }
            else
            {
                if (editType != null && editType.Trim().Equals("edit"))
                {
                    try
                    {
                        UserInfo user = (UserInfo)Session["loginedUser"];
                        htParams.Add("@update_userid", user.empId);
                        this.service.updatePartMaster(htParams);
                        message = "修改成功";
                    }
                    catch (Exception e)
                    {
                        message = "修改失败！";
                    }
                }
                else
                {
                    try
                    {
                        UserInfo user = (UserInfo)Session["loginedUser"];
                        htParams.Add("@create_userid", user.empId);
                        this.service.savePartMaster(htParams);
                        message = "添加成功";
                    }
                    catch (Exception e)
                    {
                        message = "添加失败！";
                    }
                }
            }

            result.Add("message", message);
            return Json(result);
        }

        /// <summary>
        /// 查看详情
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public ActionResult getPartMaster(String PartMaster_id)
        {
            ArrayList PartMasterInfo = this.service.getPartMaster(PartMaster_id);
            Hashtable htPartMasterInfo = (Hashtable)PartMasterInfo[0];
            ViewData.Add("part_id", htPartMasterInfo["part_id"]);
            ViewData.Add("cust_order", htPartMasterInfo["cust_order"]);
            ViewData.Add("ship_unit", htPartMasterInfo["ship_unit"]);

            return View("~/Views/PartMasterManage/viewPartMaster.aspx");
        }

        ///// <summary>
        ///// 获取part_type
        ///// </summary>
        ///// <param name="ruid"></param>
        ///// <returns></returns>
        //public ActionResult getPartMaster_part_type(string part_type)
        //{
        //    //string part_type = Request["part_type"];

        //    ViewData.Add("part_type", part_type);
        //    return View("~/Views/PartMaster/ListPartMaster.aspx");

        //}

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public ActionResult editPartMaster(String partId)
        {
            ArrayList PartMasterInfo = this.service.getPartMaster(partId);
            Hashtable htPartMasterInfo = (Hashtable)PartMasterInfo[0];
            ViewData.Add("editType", "edit");
            ViewData.Add("part_id", partId);
            ViewData.Add("part_spec", htPartMasterInfo["part_spec"]);
            ViewData.Add("part_no", htPartMasterInfo["part_no"]);
            ViewData.Add("part_type", htPartMasterInfo["part_type"]);
            ViewData.Add("enum_value", htPartMasterInfo["part_location"]);
            ViewData.Add("model_id", htPartMasterInfo["model_id"]);
            ViewData.Add("version", htPartMasterInfo["version"]);
            ViewData.Add("cust_part_no", htPartMasterInfo["cust_part_no"]);
            ViewData.Add("mfger_part_no", htPartMasterInfo["mfger_part_no"]);
            ViewData.Add("vendor_part_no", htPartMasterInfo["vendor_part_no"]);
            ViewData.Add("erp_part_no", htPartMasterInfo["erp_part_no"]);
            ViewData.Add("uom", htPartMasterInfo["uom"]);
            ViewData.Add("label_file", htPartMasterInfo["label_file"]);
            ViewData.Add("label_type", htPartMasterInfo["label_type"]);
            ViewData.Add("locate_id", htPartMasterInfo["locate_id"]);
            ViewData.Add("default_box_qty", htPartMasterInfo["default_box_qty"]);
            ViewData.Add("default_reel_qty", htPartMasterInfo["default_reel_qty"]);
            ViewData.Add("default_set_qty", htPartMasterInfo["default_set_qty"]);
            ViewData.Add("min_pack_qty", htPartMasterInfo["min_pack_qty"]);
            ViewData.Add("min_stock_qty", htPartMasterInfo["min_stock_qty"]);
            ViewData.Add("max_stock_qty", htPartMasterInfo["max_stock_qty"]);
            ViewData.Add("indate", htPartMasterInfo["indate"]);
            //ViewData.Add("is_sort", htPartMasterInfo["is_sort"]);
            //ViewData.Add("sort_qty", htPartMasterInfo["sort_qty"]);
            //ViewData.Add("sort_type", htPartMasterInfo["sort_type"]);
            ViewData.Add("batch_control", htPartMasterInfo["batch_control"]);
            ViewData.Add("split_flag", htPartMasterInfo["split_flag"]);
            ViewData.Add("over_request", htPartMasterInfo["over_request"]);
            ViewData.Add("indate_flag", htPartMasterInfo["indate_flag"]);
            ViewData.Add("rule_set", htPartMasterInfo["rule_set"]);
            ViewData.Add("route_id", htPartMasterInfo["route_id"]);
            ViewData.Add("burnin_time", htPartMasterInfo["burnin_time"]);
            ViewData.Add("subboard_qty", htPartMasterInfo["subboard_qty"]);
            ViewData.Add("upc_code", htPartMasterInfo["upc_code"]);
            ViewData.Add("ucc_code", htPartMasterInfo["ucc_code"]);
            ViewData.Add("ean_code", htPartMasterInfo["ean_code"]);
            ViewData.Add("part_class", htPartMasterInfo["part_class"]);
            ViewData.Add("part_no_3c", htPartMasterInfo["part_no_3c"]);

            return View("~/Views/PartMasterGY/EditPartMaster.aspx");
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public ActionResult editPartMaster2(String part_type)
        {


            ViewData.Add("part_type", part_type);


            return View("~/Views/PartMasterGY/EditPartMaster.aspx");
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ActionResult deletePartMaster(String ids)
        {
            string part_id = ids.Substring(0, ids.Length - 1);
            int delCount = 0;
            try
            {
                delCount = this.service.deletePartMaster(part_id);
            }
            catch (Exception e)
            {
                delCount = -1;
            }
            Hashtable result = new Hashtable();
            result.Add("status", delCount);
            return Json(result);
        }

        /// <summary>
        /// 获取车型数据
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartMaster_Model_name()
        {
            Hashtable result = new Hashtable();
            ArrayList PartMasterList = this.service.getSelectPartMaster_Model_name();
            result.Add("list", PartMasterList);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 座椅位置
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartLocation()
        {
            Hashtable result = new Hashtable();
            ArrayList PartLocation = this.service.getSelectPartLocation();
            result.Add("list", PartLocation);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 获取单位数据
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartMaster_Enum()
        {
            Hashtable result = new Hashtable();
            ArrayList PartMasterList = this.service.getSelectPartMaster_Enum();
            result.Add("list", PartMasterList);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 获取默认库位数据
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartMaster_Locate()
        {
            Hashtable result = new Hashtable();
            ArrayList PartMasterList = this.service.getSelectPartMaster_Locate();
            result.Add("list", PartMasterList);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 获取条码规则数据
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartMaster_TMGZ()
        {
            Hashtable result = new Hashtable();
            ArrayList PartMasterList = this.service.getSelectPartMaster_TMGZ();
            result.Add("list", PartMasterList);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 获取工艺流程数据
        /// </summary>
        /// <rehuoturns></returns>
        public ActionResult getSelectPartMaster_Route()
        {
            Hashtable result = new Hashtable();
            ArrayList PartMasterList = this.service.getSelectPartMaster_Route();
            result.Add("list", PartMasterList);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <returns></returns>
        public ActionResult partImport()
        {
            String partType = Request["partType"].ToString();
            Hashtable result = new Hashtable();
            HttpPostedFileBase userDataFile = Request.Files[0];
            if (userDataFile == null)
            {
                return null;
            }
            //用户ID
            UserInfo user = (UserInfo)Session["loginedUser"];

            result = service.ReadExcelFile(userDataFile.InputStream, partType, user.empId);
            return Json(result);
        }

        public ActionResult uploadFile(String partType)
        {
            ViewData.Add("partType", partType);

            return View("~/Views/PartMasterGY/uploadFile.aspx");
        }


        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="txtOrderNo"></param>
        /// <param name="sort"></param>
        /// <param name="direction"></param>
        /// <param name="isPage"></param>
        /// <returns></returns>
        public ActionResult ExportPartMaster(String part_type, String part_no, String model_name, Pager pager, String sort, String direction, String isPage)
        {

            Boolean paging = false;
            if (isPage == null || "".Equals(isPage))
            {
                paging = false;
            }
            else
            {
                if ("1".Equals(isPage))
                {
                    paging = true;
                }
                else
                {
                    paging = false;
                }
            }
            DataTable dataHt = this.service.getTableListByPage(part_type, part_no, model_name, pager, direction, sort, paging);
            HSSFWorkbook workbook = new HSSFWorkbook();
            Stream outputStream = Response.OutputStream;
            string Excel_Name = "未命名";
            if (part_type == "1")
            {
                Excel_Name = "总成零件号";
            }
            else if (part_type == "0")
            {
                Excel_Name = "原材料零件号";
            }
            else if (part_type == "2")
            {
                Excel_Name = "配置零件号";
            }

            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(Excel_Name + "明细");
            try
            {
                if (workbook != null)
                {
                    HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
                    headRow.CreateCell(0).SetCellValue(Excel_Name);
                    //headRow.CreateCell(1).SetCellValue("零件号");
                    headRow.CreateCell(1).SetCellValue("描述");
                    headRow.CreateCell(2).SetCellValue("位置");
                    headRow.CreateCell(3).SetCellValue("零件类型");
                    headRow.CreateCell(4).SetCellValue("配置名称");
                    headRow.CreateCell(5).SetCellValue("标签文件名");
                    headRow.CreateCell(6).SetCellValue("标签类型");
                    headRow.CreateCell(7).SetCellValue("ERP零件号");
                    headRow.CreateCell(8).SetCellValue("条码规则名称");
                    headRow.CreateCell(9).SetCellValue("单位");
                    headRow.CreateCell(10).SetCellValue("默认流程");
                    headRow.CreateCell(11).SetCellValue("默认货位");
                    headRow.CreateCell(12).SetCellValue("UPC编号");
                    headRow.CreateCell(13).SetCellValue("UCC编号");
                    headRow.CreateCell(14).SetCellValue("EAN编码");
                    headRow.CreateCell(15).SetCellValue("烧机时间");
                    headRow.CreateCell(16).SetCellValue("版本");
                    headRow.CreateCell(17).SetCellValue("3C零件号");
                    headRow.CreateCell(18).SetCellValue("供应商零件号");
                    headRow.CreateCell(19).SetCellValue("制造商零件号");
                    headRow.CreateCell(20).SetCellValue("联板数量");
                    headRow.CreateCell(21).SetCellValue("物料是否可拆分");
                    headRow.CreateCell(22).SetCellValue("是否按批次管控");
                    headRow.CreateCell(23).SetCellValue("最小包装数量");
                    headRow.CreateCell(24).SetCellValue("安全库存数量");
                    headRow.CreateCell(25).SetCellValue("满库数量");
                    headRow.CreateCell(26).SetCellValue("是否管控有效期");
                    headRow.CreateCell(27).SetCellValue("有效期");
                    headRow.CreateCell(28).SetCellValue("QC抽样计划编号");
                    headRow.CreateCell(29).SetCellValue("是否允许超发");
                    headRow.CreateCell(30).SetCellValue("默认的每箱数量");
                    headRow.CreateCell(31).SetCellValue("默认的每卷数量");
                    headRow.CreateCell(32).SetCellValue("默认的每批数量");
                    headRow.CreateCell(33).SetCellValue("客户零件号");
                    headRow.CreateCell(34).SetCellValue("线边安全库存");
                    headRow.CreateCell(35).SetCellValue("供货方式");
                    headRow.CreateCell(36).SetCellValue("结算方式");
                    headRow.CreateCell(37).SetCellValue("复检周期");
                    headRow.CreateCell(38).SetCellValue("上架方式");
                    headRow.CreateCell(39).SetCellValue("包装尺寸");
                    headRow.CreateCell(40).SetCellValue("供应商ID");
                    headRow.CreateCell(41).SetCellValue("是否分组");
                    headRow.CreateCell(42).SetCellValue("分组数量");
                    headRow.CreateCell(43).SetCellValue("分组类型");
                    headRow.CreateCell(44).SetCellValue("色号");
                    headRow.CreateCell(45).SetCellValue("描述2");
                    headRow.CreateCell(46).SetCellValue("条码打印份数");
                    headRow.CreateCell(47).SetCellValue("零件评估类");
                    headRow.CreateCell(48).SetCellValue("是否可用");
                    //headRow.CreateCell(47).SetCellValue("客户名称");
                    //headRow.CreateCell(48).SetCellValue("ABC分类");
                    //headRow.CreateCell(49).SetCellValue("物料组");
                    //headRow.CreateCell(50).SetCellValue("单位1");
                    //headRow.CreateCell(51).SetCellValue("换算关系1");
                    //headRow.CreateCell(52).SetCellValue("单位2");
                    //headRow.CreateCell(53).SetCellValue("换算关系2");
                    //headRow.CreateCell(54).SetCellValue("单位3");
                    //headRow.CreateCell(55).SetCellValue("换算关系3");
                    //headRow.CreateCell(56).SetCellValue("厂区");
                    //headRow.CreateCell(57).SetCellValue("ERP物料控制员编号");
                    //headRow.CreateCell(58).SetCellValue("零件标准体积");
                    //headRow.CreateCell(59).SetCellValue("靠背零件号");
                    //headRow.CreateCell(60).SetCellValue("车型");
                    //headRow.CreateCell(61).SetCellValue("采购单项次");
                    //headRow.CreateCell(62).SetCellValue("线边看板一次领料量");
                    //headRow.CreateCell(63).SetCellValue("是否校验库存");
                    //headRow.CreateCell(64).SetCellValue("发泡厂零件重量上限值");
                    //headRow.CreateCell(65).SetCellValue("发泡厂零件重量下限值");
                    //headRow.CreateCell(66).SetCellValue("发泡厂保存物料分类");
                    //headRow.CreateCell(66).SetCellValue("MES拉动收货标志");
                    //headRow.CreateCell(67).SetCellValue("MES拉动收货的供货厂区");
                    //headRow.CreateCell(68).SetCellValue("MES拉动收货库存地");

                }

                for (int i = 0; i < dataHt.Rows.Count; i++)
                {
                    int row = i + 1;
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row);

                    dataRow.CreateCell(0).SetCellValue(dataHt.Rows[i]["part_no"].ToString());
                    dataRow.CreateCell(1).SetCellValue(dataHt.Rows[i]["part_spec"].ToString());
                    dataRow.CreateCell(2).SetCellValue(dataHt.Rows[i]["enum_desc"].ToString());
                    dataRow.CreateCell(3).SetCellValue(dataHt.Rows[i]["part_type"].ToString());
                    dataRow.CreateCell(4).SetCellValue(dataHt.Rows[i]["model_id"].ToString());
                    dataRow.CreateCell(5).SetCellValue("");
                    dataRow.CreateCell(6).SetCellValue(dataHt.Rows[i]["label_type"].ToString());
                    dataRow.CreateCell(7).SetCellValue("");
                    dataRow.CreateCell(8).SetCellValue(dataHt.Rows[i]["rule_set"].ToString());
                    dataRow.CreateCell(9).SetCellValue(dataHt.Rows[i]["uom"].ToString());
                    dataRow.CreateCell(10).SetCellValue(dataHt.Rows[i]["route_name"].ToString());
                    dataRow.CreateCell(11).SetCellValue(dataHt.Rows[i]["locate_id"].ToString());
                    dataRow.CreateCell(12).SetCellValue(dataHt.Rows[i]["upc_code"].ToString());
                    dataRow.CreateCell(13).SetCellValue(dataHt.Rows[i]["ucc_code"].ToString());
                    dataRow.CreateCell(14).SetCellValue(dataHt.Rows[i]["ean_code"].ToString());
                    dataRow.CreateCell(15).SetCellValue(dataHt.Rows[i]["burnin_time"].ToString());
                    dataRow.CreateCell(16).SetCellValue(dataHt.Rows[i]["version"].ToString());
                    dataRow.CreateCell(17).SetCellValue(dataHt.Rows[i]["cust_part_no"].ToString());
                    dataRow.CreateCell(18).SetCellValue(dataHt.Rows[i]["vendor_part_no"].ToString());
                    dataRow.CreateCell(19).SetCellValue(dataHt.Rows[i]["mfger_part_no"].ToString());
                    dataRow.CreateCell(20).SetCellValue(dataHt.Rows[i]["subboard_qty"].ToString());
                    dataRow.CreateCell(21).SetCellValue(dataHt.Rows[i]["split_flag"].ToString());
                    dataRow.CreateCell(22).SetCellValue(dataHt.Rows[i]["batch_control"].ToString());
                    dataRow.CreateCell(23).SetCellValue(dataHt.Rows[i]["min_pack_qty"].ToString());
                    dataRow.CreateCell(24).SetCellValue(dataHt.Rows[i]["min_stock_qty"].ToString());
                    dataRow.CreateCell(25).SetCellValue(dataHt.Rows[i]["max_stock_qty"].ToString());
                    dataRow.CreateCell(26).SetCellValue(dataHt.Rows[i]["indate_flag"].ToString());
                    dataRow.CreateCell(27).SetCellValue(dataHt.Rows[i]["Indate"].ToString());
                    dataRow.CreateCell(28).SetCellValue(dataHt.Rows[i]["sampling_plan_id"].ToString());
                    dataRow.CreateCell(29).SetCellValue(dataHt.Rows[i]["over_request"].ToString());
                    dataRow.CreateCell(30).SetCellValue(dataHt.Rows[i]["default_box_qty"].ToString());
                    dataRow.CreateCell(31).SetCellValue(dataHt.Rows[i]["default_reel_qty"].ToString());
                    dataRow.CreateCell(32).SetCellValue(dataHt.Rows[i]["default_set_qty"].ToString());
                    dataRow.CreateCell(33).SetCellValue(dataHt.Rows[i]["part_no_3c"].ToString());
                    dataRow.CreateCell(34).SetCellValue(dataHt.Rows[i]["wip_min_stock_qty"].ToString());
                    dataRow.CreateCell(35).SetCellValue(dataHt.Rows[i]["Supply_type"].ToString());
                    dataRow.CreateCell(36).SetCellValue(dataHt.Rows[i]["Settle_type"].ToString());
                    dataRow.CreateCell(37).SetCellValue(dataHt.Rows[i]["Recheck_cycle"].ToString());
                    dataRow.CreateCell(38).SetCellValue(dataHt.Rows[i]["Instock_type"].ToString());
                    dataRow.CreateCell(39).SetCellValue(dataHt.Rows[i]["Package_size"].ToString());
                    dataRow.CreateCell(40).SetCellValue(dataHt.Rows[i]["Vendor_id"].ToString());
                    //dataRow.CreateCell(41).SetCellValue(dataHt.Rows[i]["is_sort"].ToString());
                    //dataRow.CreateCell(42).SetCellValue(dataHt.Rows[i]["sort_qty"].ToString());
                    //dataRow.CreateCell(43).SetCellValue(dataHt.Rows[i]["sort_type"].ToString());
                    dataRow.CreateCell(44).SetCellValue(dataHt.Rows[i]["part_class"].ToString());
                    dataRow.CreateCell(45).SetCellValue("");
                    dataRow.CreateCell(46).SetCellValue("");
                    dataRow.CreateCell(47).SetCellValue("");
                    dataRow.CreateCell(48).SetCellValue("");

                }

                Response.Clear();
                workbook.Write(outputStream);

                Response.Buffer = true;
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + Excel_Name + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.Flush();
            }
            catch (Exception e)
            {
            }
            finally
            {
                workbook = null;
            }

            return null;
        }
    }
}