1513 lines
63 KiB
C#
1513 lines
63 KiB
C#
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;
|
||
using VRS.Management.VSAT;
|
||
|
||
namespace VRS.Util
|
||
{
|
||
public partial class Import_Theory : 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();
|
||
|
||
DataService.BLL.pro_theory_base bll_base = new DataService.BLL.pro_theory_base();
|
||
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, "");
|
||
}
|
||
*/
|
||
|
||
excel_option.HRef = "~/Upload/Excel/选项题目模板.xls";
|
||
excel_tiankong.HRef = "~/Upload/Excel/填空题简答题模板.xls";
|
||
}
|
||
}
|
||
|
||
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 (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;
|
||
|
||
case "ImportTheory"://导入题目
|
||
|
||
var type = ddp_role.SelectedValue;
|
||
msg = "上传成功";
|
||
//单选、多选、判断题
|
||
if (type == "1")
|
||
{
|
||
state = ImportTheoryOption(savePath, out msg);
|
||
}
|
||
//填空、简答题
|
||
if (type == "2")
|
||
{
|
||
state = ImportTheoryTiankong(savePath, 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 检查姓名
|
||
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;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 手机号码
|
||
|
||
var mobile = row["手机号码"].ToString();
|
||
if (string.IsNullOrWhiteSpace(mobile))
|
||
{
|
||
msg = "存在手机号码为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
if (!BasePage.IsMobile(mobile))
|
||
{
|
||
msg = "手机号码格式不正确，请检查手机号：" + mobile;
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
}
|
||
#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();
|
||
|
||
|
||
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
|
||
|
||
|
||
|
||
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 = "";
|
||
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;//随机
|
||
|
||
|
||
|
||
|
||
|
||
/*
|
||
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;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取逗号分隔的答案
|
||
/// </summary>
|
||
/// <param name="commaAnswer"></param>
|
||
/// <returns></returns>
|
||
private int getAnswerCount(string commaAnswer)
|
||
{
|
||
var array = commaAnswer.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
|
||
int count = array.Length;
|
||
return count;
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 获取包含是的答案
|
||
/// </summary>
|
||
/// <param name="optionsWithAnswer"></param>
|
||
/// <returns></returns>
|
||
private int getAnswerYesCount(string optionsWithAnswer)
|
||
{
|
||
var array = optionsWithAnswer.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
|
||
var end1 = "(是)";
|
||
var end2 = "（是）";
|
||
int count = 0;
|
||
foreach (var item in array)
|
||
{
|
||
var value = item.Trim();
|
||
if (value.EndsWith(end1) || value.EndsWith(end2))
|
||
{
|
||
count++;
|
||
}
|
||
}
|
||
return count;
|
||
}
|
||
|
||
// obj.question = question;
|
||
//obj.kind = kind;
|
||
// obj.target = target;
|
||
//obj.answer = answer;
|
||
public class theory_base_add
|
||
{
|
||
public string question { get; set; }
|
||
public string kind { get; set; }
|
||
|
||
public string target { get; set; }
|
||
|
||
public string answer { get; set; }
|
||
|
||
public List<string> Items { get; set; }
|
||
|
||
public theory_base_add()
|
||
{
|
||
Items = new List<string>();
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 添加填空、简答题目
|
||
/// </summary>
|
||
/// <param name="filePath"></param>
|
||
/// <param name="msg"></param>
|
||
/// <returns></returns>
|
||
private bool ImportTheoryTiankong(string filePath, out string msg)
|
||
{
|
||
try
|
||
{
|
||
//知识领域 题目类型 题干 选项答案
|
||
List<theory_base_add> list_base = new List<theory_base_add>();
|
||
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_dsc = "行：" + (i + 1) + " ";
|
||
var row = table.Rows[i];
|
||
#region
|
||
var target = row["知识领域"].ToString().Trim();
|
||
if (string.IsNullOrWhiteSpace(target))
|
||
{
|
||
msg = row_dsc + "存在知识领域为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 检查题目类型
|
||
var kind = row["题目类型"].ToString().Trim();
|
||
if (string.IsNullOrWhiteSpace(kind))
|
||
{
|
||
msg = row_dsc + "存在题目类型为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
if (kind != "填空题" && kind != "简答题")
|
||
{
|
||
msg = row_dsc + "题目类型:" + kind + " 不在填空题、简答题中，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 题干
|
||
var question = row["题干"].ToString().Trim();
|
||
if (string.IsNullOrWhiteSpace(question))
|
||
{
|
||
msg = row_dsc + "存在题干为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 选项答案
|
||
var answer = row["选项答案"].ToString().Trim();
|
||
if (string.IsNullOrWhiteSpace(answer))
|
||
{
|
||
msg = row_dsc + "存在选项答案为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
|
||
string questionTK = "";
|
||
string answerTK = "";
|
||
if (kind == "填空题")
|
||
{
|
||
var line2 = "__";
|
||
questionTK = question.Replace("__________", line2)
|
||
.Replace("_________", line2)
|
||
.Replace("________", line2)
|
||
.Replace("_______", line2)
|
||
.Replace("______", line2)
|
||
.Replace("_____", line2)
|
||
.Replace("____", line2)
|
||
.Replace("___", line2);
|
||
|
||
var question_count = ExamTheoryBase_Add_Custom.CountSubstringOccurrences(questionTK, line2);
|
||
|
||
|
||
answerTK = answer.Replace("__________", line2)
|
||
.Replace("_________", line2)
|
||
.Replace("________", line2)
|
||
.Replace("_______", line2)
|
||
.Replace("______", line2)
|
||
.Replace("_____", line2)
|
||
.Replace("____", line2)
|
||
.Replace("___", line2);
|
||
var answer_array = answerTK.Split(new string[] { line2 }, StringSplitOptions.None);
|
||
if (question_count < 1)
|
||
{
|
||
msg = row_dsc + "填空题空格数数量:" + question_count + ",至少1个空格，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
if (question_count != answer_array.Length)
|
||
{
|
||
msg = row_dsc + string.Format("填空题空格数：{0} ,与答案数：{1} 不一致！", question_count, answer_array.Length);
|
||
return false;
|
||
}
|
||
}
|
||
|
||
var obj = new theory_base_add();
|
||
obj.question = question;
|
||
obj.kind = kind;
|
||
obj.target = target;
|
||
obj.answer = answer;
|
||
|
||
if (kind == "填空题")
|
||
{
|
||
obj.question = questionTK;
|
||
obj.answer = answerTK;
|
||
}
|
||
list_base.Add(obj);
|
||
#endregion
|
||
}
|
||
#endregion
|
||
|
||
List<DataService.Model.pro_theory_base> list_add = new List<DataService.Model.pro_theory_base>();
|
||
List<DataService.Model.pro_theory_base_item> list_add_items = new List<DataService.Model.pro_theory_base_item>();
|
||
|
||
var user = DataService.BLL.admin_user.load_login();
|
||
|
||
|
||
|
||
for (int i = 0; i < list_base.Count; i++)
|
||
{
|
||
var obj = list_base[i];
|
||
var model = new DataService.Model.pro_theory_base();
|
||
var id = BasePage.GetId();
|
||
model.id = id;
|
||
|
||
model.target = obj.target;
|
||
model.kind = obj.kind;
|
||
model.question = obj.question;
|
||
|
||
model.create_time = DateTime.Now;
|
||
model.create_by = user.user_id;
|
||
model.answer = obj.answer;
|
||
list_add.Add(model);
|
||
}
|
||
var total = bll_base.BatchAddBaseWithItems(list_add, list_add_items);
|
||
//var total = 1;
|
||
msg = "上传成功";
|
||
if (total > 0)
|
||
{
|
||
msg += ",添加记录数：" + total;
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
msg += ",数据添加失败！";
|
||
return false;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
msg = "上传失败：表格内容为空";
|
||
return false;
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
msg = "上传失败：" + ex.Message;
|
||
return false;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 添加选项题目
|
||
/// </summary>
|
||
/// <param name="filePath"></param>
|
||
/// <param name="msg"></param>
|
||
/// <returns></returns>
|
||
private bool ImportTheoryOption(string filePath, out string msg)
|
||
{
|
||
try
|
||
{
|
||
//知识领域 题目类型 题干 选项答案
|
||
List<theory_base_add> list_base = new List<theory_base_add>();
|
||
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_dsc = "行：" + (i + 1) + " ";
|
||
var row = table.Rows[i];
|
||
#region
|
||
var target = row["知识领域"].ToString();
|
||
if (string.IsNullOrWhiteSpace(target))
|
||
{
|
||
msg = row_dsc + "存在知识领域为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 检查题目类型
|
||
var kind = row["题目类型"].ToString();
|
||
if (string.IsNullOrWhiteSpace(kind))
|
||
{
|
||
msg = row_dsc + "存在题目类型为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
if (kind != "单选题" && kind != "判断题" && kind != "多选题")
|
||
{
|
||
msg = row_dsc + "题目类型:" + kind + " 不在单选题、判断题、多选题中，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 题干
|
||
var question = row["题干"].ToString();
|
||
if (string.IsNullOrWhiteSpace(question))
|
||
{
|
||
msg = row_dsc + "存在题干为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 选项答案
|
||
|
||
var answer = row["正确答案"].ToString();
|
||
if (string.IsNullOrWhiteSpace(answer))
|
||
{
|
||
msg = row_dsc + "存在正确答案为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
var list_items = new List<string>();
|
||
|
||
var optiona = row["选项A"].ToString().Trim();
|
||
var optionb = row["选项B"].ToString().Trim();
|
||
var optionc = row["选项C"].ToString().Trim();
|
||
var optiond = row["选项D"].ToString().Trim();
|
||
var optione = row["选项E"].ToString().Trim();
|
||
var optionf = row["选项F"].ToString().Trim();
|
||
var optiong = row["选项G"].ToString().Trim();
|
||
var optionh = row["选项H"].ToString().Trim();
|
||
var optioni = row["选项I"].ToString().Trim();
|
||
var optionj = row["选项J"].ToString().Trim();
|
||
|
||
if (!string.IsNullOrEmpty(optiona))
|
||
{
|
||
list_items.Add(optiona);
|
||
if (!string.IsNullOrEmpty(optionb))
|
||
{
|
||
list_items.Add(optionb);
|
||
if (!string.IsNullOrEmpty(optionc))
|
||
{
|
||
list_items.Add(optionc);
|
||
if (!string.IsNullOrEmpty(optiond))
|
||
{
|
||
list_items.Add(optiond);
|
||
if (!string.IsNullOrEmpty(optione))
|
||
{
|
||
list_items.Add(optione);
|
||
if (!string.IsNullOrEmpty(optionf))
|
||
{
|
||
list_items.Add(optionf);
|
||
if (!string.IsNullOrEmpty(optiong))
|
||
{
|
||
list_items.Add(optiong);
|
||
if (!string.IsNullOrEmpty(optionh))
|
||
{
|
||
list_items.Add(optionh);
|
||
if (!string.IsNullOrEmpty(optioni))
|
||
{
|
||
list_items.Add(optioni);
|
||
if (!string.IsNullOrEmpty(optionj))
|
||
{
|
||
list_items.Add(optionj);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
var total_count = list_items.Count;
|
||
var right_count = getAnswerCount(answer);
|
||
|
||
if (kind == "单选题" )
|
||
{
|
||
if (total_count < 2)
|
||
{
|
||
msg = row_dsc + "单选题数量:" + total_count + ",至少两个选项，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
if (right_count != 1)
|
||
{
|
||
msg = row_dsc + "单选题正确选项数量:" + right_count + ",不等1，请检查上传文件";
|
||
return false;
|
||
}
|
||
}
|
||
if (kind == "判断题")
|
||
{
|
||
if (total_count != 2)
|
||
{
|
||
msg = row_dsc + "判断题数量:" + total_count + ",只有两个选项，请检查上传文件";
|
||
return false;
|
||
}
|
||
if (right_count != 1)
|
||
{
|
||
msg = row_dsc + "判断题正确选项数量:" + right_count + ",不等1，请检查上传文件";
|
||
return false;
|
||
}
|
||
}
|
||
|
||
if (kind == "多选题" )
|
||
{
|
||
if (total_count < 2)
|
||
{
|
||
msg = row_dsc + "多选题数量:" + total_count + ",至少两个选项，请检查上传文件";
|
||
return false;
|
||
}
|
||
if (right_count < 2)
|
||
{
|
||
msg = row_dsc + "多选题正确选项数量:" + right_count + ",至少2个选项答案，请检查上传文件";
|
||
return false;
|
||
}
|
||
}
|
||
|
||
|
||
|
||
var obj = new theory_base_add();
|
||
obj.question = question;
|
||
obj.kind = kind;
|
||
obj.target = target;
|
||
obj.answer = answer;
|
||
obj.Items.AddRange(list_items);
|
||
list_base.Add(obj);
|
||
#endregion
|
||
}
|
||
#endregion
|
||
|
||
|
||
List<DataService.Model.pro_theory_base> list_add = new List<DataService.Model.pro_theory_base>();
|
||
List<DataService.Model.pro_theory_base_item> list_add_items = new List<DataService.Model.pro_theory_base_item>();
|
||
var user = DataService.BLL.admin_user.load_login();
|
||
|
||
Dictionary<int, string> dic = new Dictionary<int, string>();
|
||
string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
|
||
var length = alphabet.Length;
|
||
var now = DateTime.Now;
|
||
for (int i = 0; i < 26; i++)
|
||
{
|
||
dic.Add(i, alphabet.Substring(i, 1));
|
||
}
|
||
for (int i = 0; i < list_base.Count; i++)
|
||
{
|
||
var obj = list_base[i];
|
||
var model = new DataService.Model.pro_theory_base();
|
||
var id = BasePage.GetId();
|
||
model.id = id;
|
||
|
||
model.target = obj.target;
|
||
model.kind = obj.kind;
|
||
model.question = obj.question;
|
||
|
||
model.create_time = DateTime.Now;
|
||
model.create_by = user.user_id;
|
||
model.answer = obj.answer;
|
||
list_add.Add(model);
|
||
int index = 1;
|
||
var full_answer = "," + obj.answer;
|
||
for (int sub = 0; sub < obj.Items.Count; sub++)
|
||
{
|
||
var item = new DataService.Model.pro_theory_base_item();
|
||
item.theory_base_id = id;
|
||
item.id = BasePage.GetNextId(id, index++);
|
||
item.no = (sub + 1);
|
||
if (dic.ContainsKey(sub))
|
||
{
|
||
item.item_letter = dic[sub];
|
||
}
|
||
item.create_by = user.user_id;
|
||
item.create_time = now;
|
||
item.item = obj.Items[sub];
|
||
item.is_answer = 0;
|
||
if (full_answer.IndexOf(","+item.item_letter)>=0)
|
||
{
|
||
item.is_answer = 1;
|
||
}
|
||
list_add_items.Add(item);
|
||
}
|
||
}
|
||
var total = bll_base.BatchAddBaseWithItems(list_add, list_add_items);
|
||
//var total = 1;
|
||
msg = "上传成功";
|
||
if (total >0 )
|
||
{
|
||
//msg += ",添加记录数：" + total;
|
||
msg += ",添加记录数：" + list_add.Count;
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
msg += ",数据添加失败！" ;
|
||
return false;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
msg = "上传失败：表格内容为空";
|
||
return false;
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
msg = "上传失败：" + ex.Message;
|
||
return false;
|
||
}
|
||
}
|
||
|
||
#region
|
||
/// <summary>
|
||
/// 选项题目---旧有
|
||
/// </summary>
|
||
/// <param name="filePath"></param>
|
||
/// <param name="msg"></param>
|
||
/// <returns></returns>
|
||
private bool ImportTheoryOption_old(string filePath, out string msg)
|
||
{
|
||
try
|
||
{
|
||
//知识领域 题目类型 题干 选项答案
|
||
List<DataService.Model.pro_theory_base> list_base = new List<DataService.Model.pro_theory_base>();
|
||
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_dsc = "行：" + (i + 1) + " ";
|
||
var row = table.Rows[i];
|
||
#region
|
||
var target = row["知识领域"].ToString();
|
||
if (string.IsNullOrWhiteSpace(target))
|
||
{
|
||
msg = row_dsc + "存在知识领域为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 检查题目类型
|
||
var kind = row["题目类型"].ToString();
|
||
if (string.IsNullOrWhiteSpace(kind))
|
||
{
|
||
msg = row_dsc + "存在题目类型为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
if (kind != "单选题" && kind != "判断题" && kind != "多选题")
|
||
{
|
||
msg = row_dsc + "题目类型:" + kind + " 不在单选题、判断题、多选题中，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 题干
|
||
var question = row["题干"].ToString();
|
||
if (string.IsNullOrWhiteSpace(question))
|
||
{
|
||
msg = row_dsc + "存在题干为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 选项答案
|
||
|
||
var answer = row["选项答案"].ToString();
|
||
if (string.IsNullOrWhiteSpace(answer))
|
||
{
|
||
msg = row_dsc + "存在选项答案为空的记录，请检查上传文件";
|
||
return false;
|
||
}
|
||
var yesCount = getAnswerYesCount(answer);
|
||
|
||
if (kind == "单选题" && yesCount != 1)
|
||
{
|
||
msg = row_dsc + "单选题正确选项数量:" + yesCount + ",不等1，请检查上传文件";
|
||
return false;
|
||
}
|
||
if (kind == "判断题" && yesCount != 1)
|
||
{
|
||
msg = row_dsc + "判断题正确选项数量:" + yesCount + ",不等1，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
if (kind == "多选题" && yesCount < 2)
|
||
{
|
||
msg = row_dsc + "多选题正确选项数量:" + yesCount + ",至少2个选项答案，请检查上传文件";
|
||
return false;
|
||
}
|
||
|
||
var obj = new DataService.Model.pro_theory_base();
|
||
obj.question = question;
|
||
obj.kind = kind;
|
||
obj.target = target;
|
||
obj.answer = answer;
|
||
list_base.Add(obj);
|
||
#endregion
|
||
}
|
||
#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>();
|
||
|
||
List<DataService.Model.pro_theory_base> list_add = new List<DataService.Model.pro_theory_base>();
|
||
|
||
var user = DataService.BLL.admin_user.load_login();
|
||
for (int i = 0; i < list_base.Count; i++)
|
||
{
|
||
var obj = list_base[i];
|
||
var model = new DataService.Model.pro_theory_base();
|
||
var id = BasePage.GetId();
|
||
model.id = id;
|
||
|
||
model.target = obj.target;
|
||
model.kind = obj.kind;
|
||
model.question = obj.question;
|
||
|
||
model.create_time = DateTime.Now;
|
||
model.create_by = user.user_id;
|
||
|
||
|
||
//model.answer = string.Join(",", list_answer);
|
||
list_add.Add(model);
|
||
}
|
||
//var total = bll.BatchAdd_Update(list_Insert, list_update);
|
||
var total = 1;
|
||
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;
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
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();
|
||
}
|
||
|
||
|
||
}
|
||
|
||
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();
|
||
}
|
||
}
|
||
}
|
||
|
||
} |