using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using System.Text;
using NPOI.HPSF;
using NPOI.SS.Formula.Functions;
using Telerik.Web.UI;

namespace VRS.Util
{
    public partial class Import : BasePage
    {

        DataService.BLL.admin_user_role bllAUR = new DataService.BLL.admin_user_role();
        DataService.BLL.admin_config bll_config = new DataService.BLL.admin_config();
        DataService.BLL.base_major bll_major = new DataService.BLL.base_major();
        DataService.BLL.pro_type_manage bll_type_manage = new DataService.BLL.pro_type_manage();

        DataService.BLL.admin_user bll_user = new DataService.BLL.admin_user();
        DataService.BLL.admin_school bll_school = new DataService.BLL.admin_school();
        protected override void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                var user = DataService.BLL.admin_user.load_login();
                var user_login = bll_user.GetModel(user.user_id);
                if (null != user_login && user_login.role_id == "0")
                {
                    tr_school.Visible = true;
                    BindSchool(dp_school, "");
                }
            }
        }

        protected void btnSure_Click(object sender, EventArgs e)
        {
            var user = DataService.BLL.admin_user.load_login();
            var user_login = bll_user.GetModel(user.user_id);
            var school_id = "";
            if (null != user_login )
            {
                if (user_login.role_id == "0")
                {
                    if (string.IsNullOrEmpty(dp_school.SelectedValue))
                    {
                        RadAjaxManager1.Alert("请选择学校！");
                        return;
                    }
                    school_id = dp_school.SelectedValue;
                }
                else
                {
                    school_id = user_login.school_id;
                }
            }

            if (fileUpload.UploadedFiles.Count > 0)
            {
                string dirPath = Server.MapPath("~/Upload/");
                if (!Directory.Exists(dirPath))
                    Directory.CreateDirectory(dirPath);
                string fileName = Guid.NewGuid().ToString() + fileUpload.UploadedFiles[0].GetExtension();
                string savePath = dirPath + fileName;
                fileUpload.UploadedFiles[0].SaveAs(savePath);
                string cmd = Request.Params["cmd"];
                string msg = "上传成功！";
                bool state = false;
               
                switch (cmd)
                {
                    /*
                    case "ImportUsers"://导入用户
                        state = ImportUsers(savePath);
                        break;
                    case "ImportStudents"://导入学生
                        state = ImportStudents(savePath, out msg);
                        break;

                    case "ImportXueyuan"://导入学员
                        state = ImportXueyuan(savePath, out msg);
                        break;
                    */

                    case "ImportSchoolStudents"://导入学校学生
                        state = ImportSchoolStudents(savePath, school_id, out msg);
                        if (File.Exists(savePath))
                        {
                            File.Delete(savePath);
                        }
                        break;
                }

                //if (state)
                //msg = "上传成功！";

                //msg = "上传成功，导入失败！";
                RadAjaxManager1.ResponseScripts.Add("alert('" + msg + "');CloseAndRebind();");
            }
            else
            {
                RadAjaxManager1.Alert("请选择excel文件，再点击上传！");
            }
        }

        /// <summary>
        /// 导入教员
        /// </summary>
        /// <param name="filePath"></param>
        private bool ImportUsers(string filePath)
        {
            try
            {
                var table = ExcelToDataTable(filePath, "", 0, true, false);

                if (null != table)
                {
                    DataService.BLL.admin_user bll = new DataService.BLL.admin_user();
                    for (int i = 0, len = table.Rows.Count; i < len; i++)
                    {
                        var row = table.Rows[i];
                        var userId = row["账号"].ToString();
                        var model = new DataService.Model.admin_user();
                        var isExists = false;
                        if (bll.ExistsByJobNumber(userId))
                        {
                            model = bll.GetModelByJobNumber(userId);
                            isExists = true;
                        }

                        //姓名、账号、性别、手机号码、备注
                        model.real_name = row["姓名"].ToString();
                        model.sex = row["性别"].ToString();
                        model.mobile = row["手机号码"].ToString();
                        model.r1 = row["备注"].ToString();

                        if (isExists)
                        {
                            bll.Update(model);
                        }
                        else
                        {
                            model.user_id = BasePage.GetNewId("AU");
                            model.password = BasePage.GetMD5("12345"); //默认12345
                            model.login_name = row["账号"].ToString();
                            model.job_number = row["账号"].ToString();
                            model.create_time = DateTime.Now;
                            var config = bll_config.GetModelList(" is_use =1 ").FirstOrDefault();
                            var app_key = config.app_key;
                            model.r2 = app_key;

                            bll.Add(model);

                            DataService.Model.admin_user_role model_user_role = new DataService.Model.admin_user_role();
                            model_user_role.user_role_id = BasePage.GetNewId("AUR");
                            model_user_role.user_id = model.user_id;
                            model_user_role.role_id = new DataService.BLL.admin_role().GetModelList(string.Format(" role_name = '教员' ")).First().role_id; //教员

                            bllAUR.Add(model_user_role);

                        }
                    }
                    return true;
                }
            }
            catch { }

            return false;
        }

        /// <summary>
        /// 导入学生 $$$$
        /// </summary>
        /// <param name="filePath"></param>
        private bool ImportStudents(string filePath, out string msg)
        {
            try
            {
                msg = "上传成功";
                var table = ExcelToDataTable(filePath, "", 0, true, false);

                if (null != table && table.Rows.Count > 1)
                {
                    var list_dep_excel = new List<string>();
                    var dep = string.Empty;

                    #region ===检查上传的字段====
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        var row = table.Rows[i];
                        dep = row["部门"].ToString();
                        if (!string.IsNullOrWhiteSpace(dep))
                        {
                            if (!list_dep_excel.Contains(dep.Trim()))
                            {
                                list_dep_excel.Add(dep.Trim());
                            }
                        }
                        else
                        {
                            msg = "存在专业为空的记录，请检查上传文件";
                            return false;
                        }


                        #region 检查性别
                        var sex_name = row["性别"].ToString();
                        if (string.IsNullOrWhiteSpace(sex_name))
                        {
                            msg = "存在性别为空的记录，请检查上传文件";
                            return false;
                        }
                        #endregion

                        #region 身份证号
                        var id_card = row["身份证号"].ToString();
                        if (string.IsNullOrWhiteSpace(id_card))
                        {
                            msg = "存在身份证号为空的记录，请检查上传文件";
                            return false;
                        }
                        if (id_card.Length == 18 && !BasePage.Is_valid_idcard18(id_card))
                        {
                            msg = "18位身份证号码格式错误！错误身份证号： " + id_card;
                            return false;
                        }
                        #endregion

                        #region 检查姓名
                        var real_name = row["姓名"].ToString();
                        if (string.IsNullOrWhiteSpace(real_name))
                        {
                            msg = "存在姓名为空的记录，请检查上传文件";
                            return false;
                        }
                        #endregion
                    }
                    #endregion

                    var major_list = bll_type_manage.GetModelList(string.Format(" parent_id = 'sign_dep' "));
                    List<string> error_major = new List<string>();
                    foreach (var item in list_dep_excel)
                    {
                        var model = major_list.FirstOrDefault(s => s.type_name == item);
                        if (null == model)
                        {
                            error_major.Add(item);
                        }
                    }

                    if (error_major.Count > 0)
                    {
                        msg = "请先在部门管理模块配置部门，以下部门不存在：" + string.Join(",", error_major.ToArray());
                        return false;
                    }

                    int insertCount = 0;
                    int updateCount = 0;

                    DataService.BLL.admin_user bll = new DataService.BLL.admin_user();
                    for (int i = 0, len = table.Rows.Count; i < len; i++)
                    {
                        var row = table.Rows[i];
                        var id_card = row["身份证号"].ToString().Trim();
                        var model = new DataService.Model.admin_user();
                        var isExists = false;
                        if (bll.ExistsByIdcard(id_card))
                        {
                            model = bll.GetModelByIdcard(id_card);
                            isExists = true;
                        }

                        //姓名	性别	 身份证号  部门
                        model.real_name = row["姓名"].ToString().Trim();
                        model.sex = row["性别"].ToString();



                        if (isExists)
                        {
                            if (bll.Update(model))
                            {
                                updateCount++;
                            }
                        }
                        else
                        {
                            //学号不让修改
                            model.user_id = BasePage.GetNewId("USER");
                            model.password = BasePage.GetMD5("12345"); //默认12345
                            model.create_time = DateTime.Now;
                            model.id_card = id_card;
                            if (bll.Add(model))
                            {
                                insertCount++;
                            }
                        }
                    }

                    msg = "上传成功";
                    if (insertCount > 0)
                    {
                        msg += ",添加记录数：" + insertCount;
                    }
                    if (updateCount > 0)
                    {
                        msg += ",修改记录数：" + updateCount;
                    }
                    return true;
                }
                else
                {
                    msg = "上传失败：表格内容为空";
                    return false;
                }
            }
            catch (Exception ex)
            {
                msg = "上传失败：" + ex.Message;
                return false;
            }
        }



        /// <summary>
        /// 导入学员 $$$$
        /// </summary>
        /// <param name="filePath"></param>
        private bool ImportXueyuan(string filePath, out string msg)
        {
            try
            {
                msg = "上传成功";
                var table = ExcelToDataTable(filePath, "", 0, true, false);

                var user = DataService.BLL.admin_user.load_login();
                var userModel = bll_user.GetModel(user.user_id);

                if (null != table && table.Rows.Count > 1)
                {
                    var list_dep_excel = new List<string>();
                    var dep = string.Empty;

                    #region ===检查上传的字段====
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        var row = table.Rows[i];
                        #region
                        /*
                        dep = row["部门"].ToString();
                        if (!string.IsNullOrWhiteSpace(dep))
                        {
                            if (!list_dep_excel.Contains(dep.Trim()))
                            {
                                list_dep_excel.Add(dep.Trim());
                            }
                        }
                        else
                        {
                            msg = "存在专业为空的记录，请检查上传文件";
                            return false;
                        }
                        */
                        #endregion
                        #region 检查性别
                        /*
                        var sex_name = row["性别"].ToString();
                        if (string.IsNullOrWhiteSpace(sex_name))
                        {
                            msg = "存在性别为空的记录，请检查上传文件";
                            return false;
                        }
                        */
                        #endregion

                        #region 身份证号
                        /*
                        var id_card = row["身份证号"].ToString();
                        if (string.IsNullOrWhiteSpace(id_card))
                        {
                            msg = "存在身份证号为空的记录，请检查上传文件";
                            return false;
                        }
                        if (id_card.Length == 18 && !BasePage.Is_valid_idcard18(id_card))
                        {
                            msg = "18位身份证号码格式错误！错误身份证号： " + id_card;
                            return false;
                        }
                        */
                        #endregion

                        #region 检查姓名
                        var real_name = row["姓名"].ToString();
                        if (string.IsNullOrWhiteSpace(real_name))
                        {
                            msg = "存在姓名为空的记录，请检查上传文件";
                            return false;
                        }
                        #endregion

                        #region 登录账号

                        var id_card = row["登录账号"].ToString();
                        if (string.IsNullOrWhiteSpace(id_card))
                        {
                            msg = "存在登录账号为空的记录，请检查上传文件";
                            return false;
                        }
                        if (id_card.Length < 5)
                        {
                            msg = "登录账号的长度小于5！请检查登录账号： " + id_card;
                            return false;
                        }
                        /*
                        if (bll_user.ExistsByIdcard(id_card))
                        {
                            msg = "登录账号已经存在！请检查登录账号： " + id_card;
                            return false;
                        }
                        */
                        #endregion

                        #region 手机号码

                        var mobile = row["手机号码"].ToString();
                        if (string.IsNullOrWhiteSpace(mobile))
                        {
                            msg = "存在手机号码为空的记录，请检查上传文件";
                            return false;
                        }
                        if (!BasePage.IsMobile(mobile))
                        {
                            msg = "手机号码格式不正确，请检查手机号：" + mobile;
                            return false;
                        }
                        #endregion

                    }
                    #endregion

                    #region
                    /*
                    var major_list = bll_type_manage.GetModelList(string.Format(" parent_id = 'sign_dep' "));
                    List<string> error_major = new List<string>();
                    foreach (var item in list_dep_excel)
                    {
                        var model = major_list.FirstOrDefault(s => s.type_name == item);
                        if (null == model)
                        {
                            error_major.Add(item);
                        }
                    }

                    if (error_major.Count > 0)
                    {
                        msg = "请先在部门管理模块配置部门，以下部门不存在：" + string.Join(",", error_major.ToArray());
                        return false;
                    }
                    */

                    #endregion

                    int insertCount = 0;
                    int updateCount = 0;

                    DataService.BLL.admin_user bll = new DataService.BLL.admin_user();
                    for (int i = 0, len = table.Rows.Count; i < len; i++)
                    {
                        var row = table.Rows[i];
                        var id_card = row["登录账号"].ToString().Trim();
                        var model = new DataService.Model.admin_user();
                        var isExists = false;
                        if (bll.ExistsByIdcard(id_card))
                        {
                            model = bll.GetModelByIdcard(id_card);
                            isExists = true;
                        }

                        //姓名	性别	 身份证号  部门
                        model.real_name = row["姓名"].ToString().Trim();
                        //model.sex = row["性别"].ToString();
                        //model.dep_name = row["部门"].ToString();
                        model.mobile = row["手机号码"].ToString();


                        if (isExists)
                        {
                            if (bll.Update(model))
                            {
                                updateCount++;
                            }
                        }
                        else
                        {
                            //学号不存在，进行新增
                            model.user_id = BasePage.GetNewId("USER");
                            model.password = BasePage.GetMD5("12345"); //默认12345
                            model.create_time = DateTime.Now;
                            model.id_card = id_card;
                            model.role_id = bll_user.getStudentRoleId();
                            //model.province_id = userModel.province_id;
                            //model.province = userModel.province;
                            //model.city_id = userModel.city_id;
                            //model.city = userModel.city;
                            //model.unit_id = userModel.unit_id;
                            //model.unit_name = userModel.unit_name;

                            if (bll.Add(model))
                            {
                                insertCount++;
                            }
                        }
                    }

                    msg = "上传成功";
                    if (insertCount > 0)
                    {
                        msg += ",添加记录数：" + insertCount;
                    }
                    if (updateCount > 0)
                    {
                        msg += ",修改记录数：" + updateCount;
                    }
                    return true;
                }
                else
                {
                    msg = "上传失败：表格内容为空";
                    return false;
                }
            }
            catch (Exception ex)
            {
                msg = "上传失败：" + ex.Message;
                return false;
            }
        }


        /// <summary>
        /// 导入学校学生 $$$$
        /// </summary>
        /// <param name="filePath"></param>
        private bool ImportSchoolStudents(string filePath,string school_id, out string msg)
        {
            try
            {
                msg = "上传成功";
                /*
                var user = DataService.BLL.admin_user.load_login();
                var user_login = bll_user.GetModel(user.user_id);
                if (user_login.role_id=="0")
                {
                    msg = "上传失败，请用老师角色上传文件！";
                    return false;
                }
                */
                var table = ExcelToDataTable(filePath, "", 0, true, false);
                if (null != table && table.Rows.Count >=1)
                {
                    var list_dep_excel = new List<string>();
                    var dep = string.Empty;
                    List<string> list_login_name = new List<string>();
                    #region ===检查上传的字段====
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        var row = table.Rows[i];
                        #region
                        var grade = row["班级"].ToString();
                        if (string.IsNullOrWhiteSpace(grade))
                        {
                            msg = "存在班级为空的记录，请检查上传文件";
                            return false;
                        }
                        #endregion

                        #region 检查姓名
                        var real_name = row["姓名"].ToString();
                        if (string.IsNullOrWhiteSpace(real_name))
                        {
                            msg = "存在姓名为空的记录，请检查上传文件";
                            return false;
                        }
                        #endregion

                        #region 登录账号

                        var id_card = row["登录账号"].ToString();
                        if (string.IsNullOrWhiteSpace(id_card))
                        {
                            msg = "存在登录账号为空的记录，请检查上传文件";
                            return false;
                        }
                        if (id_card.Length < 5)
                        {
                            msg = "登录账号的长度小于5！请检查登录账号： " + id_card;
                            return false;
                        }

                        var login_name = row["登录账号"].ToString().Trim();
                        list_login_name.Add(login_name);

                        /*
                        if (bll_user.ExistsByIdcard(id_card))
                        {
                            msg = "登录账号已经存在！请检查登录账号： " + id_card;
                            return false;
                        }
                        */
                        #endregion

                        #region 手机号码

                        var mobile = row["手机号码"].ToString();
                        if (!string.IsNullOrWhiteSpace(mobile) && !BasePage.IsMobile(mobile))
                        {
                            msg = "手机号码格式不正确，请检查手机号：" + mobile;
                            return false;
                        }
                        #endregion
                    }
                    #endregion

                    #region 判断学校数量上限  2 学生 、1 老师 限制提示
                    var array_login_name = list_login_name.ToArray();
                    List<string> list = new List<string>();
                    foreach (var item in array_login_name)
                    {
                        list.Add("'" + item + "'");
                    }
                    int exist_count = 0;
                    if (list.Count > 0)
                    {
                        var id_list = string.Join(",", list.ToArray());
                        string where = string.Format("login_name  in ({0}) ", id_list);
                        exist_count = bll_user.GetModelList(where).Count;
                    }
                    var prepare_count = list.Count - exist_count;
                    if (prepare_count > 0)
                    {
                        var school = bll_school.GetModel(school_id);
                        var role_student_id = bll_user.getStudentRoleId();
                        var role_teacher_id = bll_user.getTeacherRoleId();
                        if (ddp_role.SelectedValue == role_student_id) //=========导入学生=========
                        {
                            if (!string.IsNullOrEmpty(school.r2) && school.r2 != "-1")
                            {
                                var limit_number = int.Parse(school.r2);
                             
                                //只统计已启用用户$$$
                            
                                var exist_number = bll_user.GetStartedUsers(school_id, role_student_id);
                                if ((exist_number + prepare_count) > limit_number)
                                {
                                    //msg = string.Format("当前学校学生已达上限{0},导入失败！", limit_number);
                                    msg = string.Format("当前学校学生已达上限,导入失败！");
                                    return false;
                                }
                            }
                        }
                        else  if (ddp_role.SelectedValue == role_teacher_id) //========导入老师======
                         {
                            if (!string.IsNullOrEmpty(school.r1) && school.r1 != "-1")
                            {
                                var limit_number = int.Parse(school.r1);
                              
                                //只统计已启用用户$$$
                            
                                var exist_number = bll_user.GetStartedUsers(school_id, role_teacher_id);
                                if ((exist_number + prepare_count) > limit_number)
                                {
                                    //msg = string.Format("当前学校老师已达上限{0},导入失败！", limit_number);
                                    msg = string.Format("当前学校老师已达上限,导入失败！");
                                    return false;
                                }
                            }
                        }
                    }
                    #endregion

                    int insertCount = 0;
                    int updateCount = 0;

                    List<DataService.Model.admin_user> list_Insert = new List<DataService.Model.admin_user>();
                    List< DataService.Model.admin_user > list_update = new List<DataService.Model.admin_user>();

                    DataService.BLL.admin_user bll = new DataService.BLL.admin_user();
                    for (int i = 0, len = table.Rows.Count; i < len; i++)
                    {
                        var row = table.Rows[i];
                        var login_name = row["登录账号"].ToString().Trim();
                        var model = new DataService.Model.admin_user();
                        var isExists = false;

                        // var user_exists = bll.GetModelBylogin_name(user_login.school_id, login_name);
                        var user_exists = bll.GetModelBylogin_name(login_name);
                        if (null != user_exists)
                        {
                            model = user_exists;
                            isExists = true;
                        }

                        //班级 姓名   手机号码  登录账号(不变)
                        model.grade = row["班级"].ToString();
                        model.real_name = row["姓名"].ToString().Trim();
                        model.job_number = row["学号"].ToString();
                        model.mobile = row["手机号码"].ToString();
                        if (isExists)
                        {
                            /*
                            if (bll.Update(model))
                            {
                                updateCount++;
                            }
                            */
                            list_update.Add(model);
                            updateCount++;
                        }
                        else
                        {
                            //string password = GetString(6);//随机
                            //string password = "12345"; //默认
                            string password = "12345";
                            var random_value = dp_random.SelectedValue;
                            if (random_value == "1")
                            {
                                 password = GetString6();//随机
                            }
                            model.user_id = BasePage.GetNewId("USER");
                            model.password = BasePage.GetMD5(password); //默认12345
                            model.create_time = DateTime.Now;
                            model.login_name = login_name; //登录账号
                            model.school_id = school_id;
                            //model.role_id = bll_user.getStudentRoleId();
                            model.role_id = ddp_role.SelectedValue;
                            //model.r6 = password;//随机

                            #region $$$$ 只保存学生密码
                            //model.r6 = password;
                            if (model.role_id == "2")
                            {
                                model.r6 = password;
                            }
                            #endregion

                            #region 其它角色只能是12345
                            if (model.role_id != "2")
                            {
                                var password_en = EncryptionAndDecryption.EncryptByLgzn("12345");
                                model.password = password_en;
                            }
                            #endregion

                            /*
                            if (bll.Add(model))
                            {
                                insertCount++;
                            }
                            */
                            list_Insert.Add(model);
                            insertCount++;
                        }
                    }
                    var total = bll.BatchAdd_Update(list_Insert, list_update);
                    msg = "上传成功";
                    if (total == (insertCount + updateCount))
                    {
                        if (insertCount > 0)
                        {
                            msg += ",添加记录数：" + insertCount;
                        }
                        if (updateCount > 0)
                        {
                            msg += ",修改记录数：" + updateCount;
                        }
                    }
                    return true;
                }
                else
                {
                    msg = "上传失败：表格内容为空";
                    return false;
                }
            }
            catch (Exception ex)
            {
                msg = "上传失败：" + ex.Message;
                return false;
            }
        }

        //批量导入的时候生成随机密码，注意随机密码去除大写的i以及小写的L
        public string GetString_old(int count)
        {
            //string t62 = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
            string t62 = "0123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ"; //去除了两个字母
            long ticks = DateTime.Now.Ticks;

            string gen = "";
            int ind = 0;
            while (ind < count)
            {
                byte low = (byte)((ticks >> ind * 6) & 61);
                gen += t62[low];
                ind++;
            }
            return gen;
        }

        public string GetString(int count)
        {
            var d = new Random(BitConverter.ToInt32(Guid.NewGuid().ToByteArray(), 0));
            var array = new string[] {  "2", "3", "4", "5", "6", "7", "8", "a", "b", "d", "e", "f", "h", "m", "n", "t", "y" };
            var new_array = array.OrderBy(s => Guid.NewGuid()).ToArray();
            var sb = new StringBuilder();
            for(int i=0; i< count;i++)
            {
                sb.Append(new_array[i]);
            }
            return sb.ToString();
        }

        public string GetString6()
        {
            var d = new Random(BitConverter.ToInt32(Guid.NewGuid().ToByteArray(), 0));
            var array1 = new string[] { "a", "b", "d", "e", "f", "h", "m", "n", "t", "y" };
            var array2 = new string[] { "2", "3", "4", "5", "6", "7", "8" };
            var new_array1 = array1.OrderBy(s => Guid.NewGuid()).ToArray();
            var new_array2 = array2.OrderBy(s => Guid.NewGuid()).ToArray();
            var temp = new_array1[1] + new_array1[2] + new_array1[3] 
                            + new_array2[1] + new_array2[2] + new_array2[3];
            return temp;
        }



        public static DataTable ExcelToDataTable(string filePath, string sheetName, int startRow, bool isFirstRowColumn, bool cshz)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int cellCount = 0;
            //  int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        if (sheetName != null)
                        {
                            sheet = workbook.GetSheet(sheetName);
                            if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                            {
                                if (cshz)
                                {
                                    return null;
                                }
                                sheet = workbook.GetSheetAt(0);
                            }
                        }
                        else
                        {
                            sheet = workbook.GetSheetAt(0);
                        }
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(startRow);//第一行
                                int rowindex = 0;　　　　　　　　　　　　　　　　　　　　//搜索空行,并跳过
                                while (firstRow == null && rowindex < rowCount)
                                {
                                    rowindex++;
                                    firstRow = sheet.GetRow(rowindex);
                                }　　　　　　　　　　　　　　　　　　　　//如果全为空行则返回null值
                                if (rowindex == rowCount) return null;

                                cellCount = firstRow.LastCellNum;//列数
                                startRow = firstRow.RowNum;
                                //  构建datatable的列
                                if (isFirstRowColumn)
                                {
                                    //如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.ToString() != null)
                                            {
                                                column = new DataColumn(cell.ToString());
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                    startRow++;
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //   填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;
                                    cellCount = row.LastCellNum;　//全文行之间的列数不一样的话,继续添加列
                                    if (cellCount > dataTable.Columns.Count)
                                    {
                                        for (int c = dataTable.Columns.Count; c < cellCount; c++)
                                        {
                                            column = new DataColumn("column" + (c + 1));
                                            dataTable.Columns.Add(column);
                                        }
                                    }
                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //  CellType(Unknown = -1, Numeric = 0, String = 1, Formula = 2, Blank = 3, Boolean = 4, Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //  对时间格式（2015.12.5、2015 / 12 / 5、2015 - 12 - 5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="path">当前 FileStream 对象将封装的文件的相对路径或绝对路径。</param>
        public static void DateTableToExcel(DataTable dt, string path)
        {
            MemoryStream stream = (MemoryStream)ExcelTools.RenderDataTableToExcel(dt);
            byte[] buffer = stream.ToArray();
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buffer, 0, buffer.Length);
                fs.Flush();
            }

            /*
              MemoryStream memoryStream = new MemoryStream();
                byte[] buffer = new byte[length];
                int i;
                //将字节逐个放入到Byte中
                while ((i = stream.Read(buffer, 0, buffer.Length)) > 0)
                {
                    memoryStream.Write(buffer, 0, i);
                }
                result = memoryStream.ToArray();//文件流Byte，需要文件流可直接return，不需要下面的保存代码
                memoryStream.Close();
             */
        }

        public static byte[]  DateTableToExcelBytes(DataTable dt)
        {
            MemoryStream stream = (MemoryStream)ExcelTools.RenderDataTableToExcel(dt);
            byte[] buffer = stream.ToArray();
            return buffer;
        }

            /// <summary>
            /// 
            /// </summary>
            /// <param name="dt">DataTable</param>
            /// <param name="path">当前 FileStream 对象将封装的文件的相对路径或绝对路径。</param>
            public static void DateTableToExcelWithTitle(DataTable dt, DataTable source, string path, string title)
        {
            MemoryStream stream = (MemoryStream)ExcelTools.RenderDataTableToExcelWithTitle(dt, source, title);
            byte[] buffer = stream.ToArray();
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buffer, 0, buffer.Length);
                fs.Flush();
            }
        }
    }

    public static class ExcelTools
    {
        public static HSSFWorkbook workbook;

        public static void InitializeWorkbook()
        {
            ////create a entry of DocumentSummaryInformation
            if (workbook == null)
                workbook = new HSSFWorkbook();
        }
        #region
        /// <summary>
        /// 将DataTable转成Stream输出.
        /// </summary>
        /// <param name="SourceTable">The source table.</param>
        /// <returns></returns>
        public static Stream RenderDataTableToExcel(DataTable SourceTable)
        {
            workbook = new HSSFWorkbook();
            InitializeWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);

            // handling header.
            foreach (DataColumn column in SourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                foreach (DataColumn column in SourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }

        /// <summary>
        /// 将DataTable转成Stream输出.
        /// </summary>
        /// <param name="SourceTable">The source table.</param>
        /// <returns></returns>
        public static Stream RenderDataTableToExcelWithTitle(DataTable SourceTable, DataTable source, string title)
        {
            workbook = new HSSFWorkbook();
            InitializeWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();


            HSSFRow titleRow = (HSSFRow)sheet.CreateRow(0);
            var cell_title = titleRow.CreateCell(0);
            var titleStyle = createTitleStyle(workbook);
            cell_title.CellStyle = titleStyle;
            cell_title.SetCellValue(title);

            var cell_style = createCellStyle(workbook);
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
            // handling header.
            foreach (DataColumn column in SourceTable.Columns)
            {
                var cell_head = headerRow.CreateCell(column.Ordinal);
                cell_head.CellStyle = cell_style;
                cell_head.SetCellValue(column.ColumnName);
            }
            // handling value.
            int rowIndex = 2;

            foreach (DataRow row in SourceTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in SourceTable.Columns)
                {
                    var cell_data = dataRow.CreateCell(column.Ordinal);
                    cell_data.CellStyle = cell_style;
                    cell_data.SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }

            #region 成绩分数段统计
            // 不及格 60分以下
            decimal man_dn60 = 0;

            //及格 60-69
            decimal man_bt60_70 = 0;

            //中等 70-79
            decimal man_bt70_80 = 0;

            //良好 80-89
            decimal man_bt80_90 = 0;

            //优秀 90-100
            decimal man_up90 = 0;

            //总计多少分
            decimal total_score = 0;
            //总计多少人
            decimal total_man = 0;

            VRS.Management.VSAT.ResultStudentStatistics.compute_score(source, out man_dn60, out man_bt60_70, out man_bt70_80, out man_bt80_90, out man_up90, out total_score, out total_man);

            var avg_score = Math.Round(total_score / total_man, 1);
            var avg_score_string = avg_score.ToString();

            decimal bjg_rate = Math.Round(man_dn60 * 100 / total_man, 1);
            decimal jg_rate = Math.Round(man_bt60_70 * 100 / total_man, 1);
            decimal zd_rate = Math.Round(man_bt70_80 * 100 / total_man, 1);
            decimal lh_rate = Math.Round(man_bt80_90 * 100 / total_man, 1);
            decimal yx_rate = Math.Round(man_up90 * 100 / total_man, 1);

            rowIndex++;
            var row_index_cjtj = rowIndex;
            var headerRow_cjtj = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head_cjtj = headerRow_cjtj.CreateCell(0);
            cell_head_cjtj.CellStyle = cell_style;
            cell_head_cjtj.SetCellValue("成绩统计");

            rowIndex++;
            var row_pjcj = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head1 = row_pjcj.CreateCell(0);
            cell_head1.CellStyle = cell_style;
            cell_head1.SetCellValue("平均成绩");
            var cell_head2 = row_pjcj.CreateCell(1);
            cell_head2.CellStyle = cell_style;
            cell_head2.SetCellValue(avg_score_string);

            //优秀 良好 中等 及格 不及格
            //优秀
            rowIndex++;
            var row_yx = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head_yx = row_yx.CreateCell(0);
            cell_head_yx.CellStyle = cell_style;
            cell_head_yx.SetCellValue("优秀");

            var cell_head_yx_1 = row_yx.CreateCell(1);
            cell_head_yx_1.CellStyle = cell_style;
            cell_head_yx_1.SetCellValue(man_up90.ToString());

            var cell_head_yx_2 = row_yx.CreateCell(2);
            cell_head_yx_2.CellStyle = cell_style;
            cell_head_yx_2.SetCellValue(yx_rate + "%");

            //良好
            rowIndex++;
            var row_lh = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head_lh = row_lh.CreateCell(0);
            cell_head_lh.CellStyle = cell_style;
            cell_head_lh.SetCellValue("良好");

            var cell_head_lh_1 = row_lh.CreateCell(1);
            cell_head_lh_1.CellStyle = cell_style;
            cell_head_lh_1.SetCellValue(man_bt80_90.ToString());

            var cell_head_lh_2 = row_lh.CreateCell(2);
            cell_head_lh_2.CellStyle = cell_style;
            cell_head_lh_2.SetCellValue(lh_rate + "%");

            //中等
            rowIndex++;
            var row_zd = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head_zd = row_zd.CreateCell(0);
            cell_head_zd.CellStyle = cell_style;
            cell_head_zd.SetCellValue("中等");

            var cell_head_zd_1 = row_zd.CreateCell(1);
            cell_head_zd_1.CellStyle = cell_style;
            cell_head_zd_1.SetCellValue(man_bt70_80.ToString());

            var cell_head_zd_2 = row_zd.CreateCell(2);
            cell_head_zd_2.CellStyle = cell_style;
            cell_head_zd_2.SetCellValue(zd_rate + "%");

            //及格
            rowIndex++;
            var row_jg = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head_jg = row_jg.CreateCell(0);
            cell_head_jg.CellStyle = cell_style;
            cell_head_jg.SetCellValue("及格");

            var cell_head_jg_1 = row_jg.CreateCell(1);
            cell_head_jg_1.CellStyle = cell_style;
            cell_head_jg_1.SetCellValue(man_bt60_70.ToString());

            var cell_head_jg_2 = row_jg.CreateCell(2);
            cell_head_jg_2.CellStyle = cell_style;
            cell_head_jg_2.SetCellValue(jg_rate + "%");

            //不及格
            rowIndex++;
            var row_bjg = (HSSFRow)sheet.CreateRow(rowIndex);
            var cell_head_bjg = row_bjg.CreateCell(0);
            cell_head_bjg.CellStyle = cell_style;
            cell_head_bjg.SetCellValue("不及格");

            var cell_head_bjg_1 = row_bjg.CreateCell(1);
            cell_head_bjg_1.CellStyle = cell_style;
            cell_head_bjg_1.SetCellValue(man_dn60.ToString());

            var cell_head_bjg_2 = row_bjg.CreateCell(2);
            cell_head_bjg_2.CellStyle = cell_style;
            cell_head_bjg_2.SetCellValue(bjg_rate + "%");



            #endregion



            if (SourceTable.Rows.Count >= 2)
            {
                var length = sheet.GetRow(1).LastCellNum;
                for (var j = 1; j < length; j++)
                {
                    sheet.AutoSizeColumn(j);
                }
            }

            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, SourceTable.Columns.Count - 1);
            RegionUtil.SetBorderTop(1, region, sheet);
            RegionUtil.SetBorderLeft(1, region, sheet);
            RegionUtil.SetBorderRight(1, region, sheet);
            sheet.AddMergedRegion(region);

            //成绩统计行
            CellRangeAddress region_cjtj = new CellRangeAddress(row_index_cjtj, row_index_cjtj, 0, 2);
            RegionUtil.SetBorderTop(1, region_cjtj, sheet);
            RegionUtil.SetBorderLeft(1, region_cjtj, sheet);
            RegionUtil.SetBorderRight(1, region_cjtj, sheet);
            sheet.AddMergedRegion(region_cjtj);

            CellRangeAddress region_pjcj = new CellRangeAddress(row_index_cjtj + 1, row_index_cjtj + 1, 1, 2);
            RegionUtil.SetBorderTop(1, region_pjcj, sheet);
            RegionUtil.SetBorderLeft(1, region_pjcj, sheet);
            RegionUtil.SetBorderRight(1, region_pjcj, sheet);
            sheet.AddMergedRegion(region_pjcj);

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

        /**

    * 设置单元格的边框（细）且为红色

    * @param workbook

    * @param cellnum

    * @return

    */

        public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook)
        {
            HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
            //设置上下左右四个边框宽度
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

            //设置上下左右四个边框颜色
            style.TopBorderColor = HSSFColor.Black.Index;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor = HSSFColor.Black.Index;
            style.RightBorderColor = HSSFColor.Black.Index;

            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;

            return style;
        }

        public static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook)
        {
            HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;

            // 生成一个字体
            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontHeight = 300;
            font.Color = HSSFColor.Black.Index;
            font.IsBold = true;
            font.FontName = "宋体";

            // 把字体 应用到当前样式
            style.SetFont(font);
            return style;
        }



        public static void WriteSteamToFile(MemoryStream ms, string FileName)
        {
            FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();

            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();

            data = null;
            ms = null;
            fs = null;
        }
        public static void WriteSteamToFile(byte[] data, string FileName)
        {
            FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write);
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
            data = null;
            fs = null;
        }
        public static Stream WorkBookToStream(HSSFWorkbook InputWorkBook)
        {
            MemoryStream ms = new MemoryStream();
            InputWorkBook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }
        public static HSSFWorkbook StreamToWorkBook(Stream InputStream)
        {
            HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream);
            return WorkBook;
        }
        public static HSSFWorkbook MemoryStreamToWorkBook(MemoryStream InputStream)
        {
            HSSFWorkbook WorkBook = new HSSFWorkbook(InputStream as Stream);
            return WorkBook;
        }
        public static MemoryStream WorkBookToMemoryStream(HSSFWorkbook InputStream)
        {
            //Write the stream data of workbook to the root directory
            MemoryStream file = new MemoryStream();
            InputStream.Write(file);
            return file;
        }
        public static Stream FileToStream(string FileName)
        {
            FileInfo fi = new FileInfo(FileName);
            if (fi.Exists == true)
            {
                FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read);
                return fs;
            }
            else return null;
        }
        public static Stream MemoryStreamToStream(MemoryStream ms)
        {
            return ms as Stream;
        }
        #endregion
        #region

        /// <summary>
        /// 将DataTable转成Workbook(自定资料形态)输出.
        /// </summary>
        /// <param name="SourceTable">The source table.</param>
        /// <returns></returns>
        public static HSSFWorkbook RenderDataTableToWorkBook(DataTable SourceTable)
        {
            workbook = new HSSFWorkbook();
            InitializeWorkbook();
            MemoryStream ms = new MemoryStream();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);

            // handling header.
            foreach (DataColumn column in SourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in SourceTable.Rows)
            {
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                foreach (DataColumn column in SourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }
            return workbook;
        }

        /// <summary>
        /// 将DataTable资料输出成Excel.
        /// </summary>
        /// <param name="SourceTable">The source table.</param>
        /// <param name="FileName">Name of the file.</param>
        public static void RenderDataTableToExcel(DataTable SourceTable, string FileName)
        {
            MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream;
            WriteSteamToFile(ms, FileName);
        }

        /// <summary>
        /// 從位元读取取资料到DataTable.
        /// </summary>
        /// <param name="ExcelFileStream">The excel file stream.</param>
        /// <param name="SheetName">Name of the sheet.</param>
        /// <param name="HeaderRowIndex">Index of the header row.</param>
        /// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex, bool HaveHeader)
        {
            workbook = new HSSFWorkbook(ExcelFileStream);
            InitializeWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(SheetName);

            DataTable table = new DataTable();

            HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
                DataColumn column = new DataColumn(ColumnName);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;
            int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
            for (int i = RowStart; i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = (HSSFRow)sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 從位元流读取资料到DataTable.
        /// </summary>
        /// <param name="ExcelFileStream">The excel file stream.</param>
        /// <param name="SheetIndex">Index of the sheet.</param>
        /// <param name="HeaderRowIndex">Index of the header row.</param>
        /// <param name="HaveHeader">if set to <c>true</c> [have header].</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, bool HaveHeader)
        {
            workbook = new HSSFWorkbook(ExcelFileStream);
            InitializeWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex);

            DataTable table = new DataTable();

            HSSFRow headerRow = (HSSFRow)sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                string ColumnName = (HaveHeader == true) ? headerRow.GetCell(i).StringCellValue : "f" + i.ToString();
                DataColumn column = new DataColumn(ColumnName);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;
            int RowStart = (HaveHeader == true) ? sheet.FirstRowNum + 1 : sheet.FirstRowNum;
            for (int i = RowStart; i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = (HSSFRow)sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }

                table.Rows.Add(dataRow);
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
        #endregion
    }
}