private void SaveFile(MemoryStream stream, string filename, string format)
{
if (format == "xls")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(stream);
// 2017/8/22 穎驊依據高雄小組專案 [03-05][04+] EXCEL匯入格式可否修正為xlsx也可匯入？ 更改為新版 Aspose.Cells_201402 寫法
//wb.Save(filename, Aspose.Cells.FileFormatType.Excel2003);
wb.Save(filename, Aspose.Cells.SaveFormat.Excel97To2003);
}
else if (format == "xlsx")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(stream);
wb.Save(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx);
}
else if (format == "doc")
{
Aspose.Words.Document doc = new Aspose.Words.Document(stream);
doc.Save(filename, Aspose.Words.SaveFormat.Doc);
}
else if (format == "docx")
{
Aspose.Words.Document doc = new Aspose.Words.Document(stream);
doc.Save(filename, Aspose.Words.SaveFormat.Docx);
}
}
/// <summary>
/// Excel文件转换为DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
// 上傳模組
private void UploadTemplate(string fileName, ref bool uploadIndex, ref string uploadData)
{
FileStream fs = new FileStream(fileName, FileMode.Open);
byte[] tempBuffer = new byte[fs.Length];
fs.Read(tempBuffer, 0, tempBuffer.Length);
MemoryStream ms = new MemoryStream(tempBuffer);
try
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003);
wb = null;
}
catch
{
throw new Exception("此版學籍表範本限用相容於 Excel 2003 檔案。");
}
try
{
uploadData = Convert.ToBase64String(tempBuffer);
uploadIndex = true;
fs.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void SaveFile(MemoryStream stream, string filename, string format)
{
if (format == "xls")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(stream);
wb.Save(filename, Aspose.Cells.FileFormatType.Excel2003);
}
else if (format == "xlsx")
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(stream);
wb.Save(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx);
}
else if (format == "doc")
{
Aspose.Words.Document doc = new Aspose.Words.Document(stream);
doc.Save(filename, Aspose.Words.SaveFormat.Doc);
}
else if (format == "docx")
{
Aspose.Words.Document doc = new Aspose.Words.Document(stream);
doc.Save(filename, Aspose.Words.SaveFormat.Docx);
}
}
/// <summary>
/// 导出风险管控列表
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult ExportExcel(string queryJson)
{
try
{
HttpResponse resp = System.Web.HttpContext.Current.Response;
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
Pagination pagination = new Pagination();
pagination.p_kid = "";
pagination.p_fields = "'' num1,post,name,activitystep,case when isconventional=0 then '常规' else '非常规' end as isconventional,others as others1,b.fullname as dept1,to_char(a.createdate,'yyyy-MM-dd') as date1,'' num2,name as equname,areaname,case when isspecialequ=0 then '是' else '否' end as isspecialequ,others as others2，b.fullname as dept2,to_char(a.createdate,'yyyy-MM-dd') as date2,type";
pagination.p_tablename = "bis_baselisting a left join base_department b on a.createuserdeptcode=b.encode";
pagination.conditionJson = "1=1";
pagination.page = 1;
pagination.rows = 100000;
if (user.RoleName.Contains("公司级") || user.RoleName.Contains("厂级"))
{
pagination.conditionJson += " and a.CREATEUSERORGCODE ='" + user.OrganizeCode + "'";
}
else
{
pagination.conditionJson += " and a.createuserdeptcode ='" + user.DeptCode + "'";
}
var data = baselistingbll.GetPageListJson(pagination, queryJson);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/作业活动及设备设施清单导出模板.xls"));
if (data.Rows.Count > 0)
{
DataTable dt1 = data.Select("type=0").Count() > 0 ? data.Select("type=0").CopyToDataTable() : new DataTable();
if (dt1.Rows.Count > 0)
{
for (int i = 0; i < dt1.Rows.Count; i++)
{
dt1.Rows[i]["num1"] = i + 1;
}
wb.Worksheets[0].Cells.ImportDataTable(dt1, false, 1, 0, data.Rows.Count, 7);
}
DataTable dt2 = data.Select("type=1").Count() > 0 ? data.Select("type=1").CopyToDataTable() : new DataTable();
if (dt2.Rows.Count > 0)
{
dt2.Columns.Remove("post"); dt2.Columns.Remove("name"); dt2.Columns.Remove("activitystep"); dt2.Columns.Remove("isconventional"); dt2.Columns.Remove("others1");
dt2.Columns.Remove("dept1"); dt2.Columns.Remove("date1"); dt2.Columns.Remove("num1");
for (int i = 0; i < dt2.Rows.Count; i++)
{
dt2.Rows[i]["num2"] = i + 1;
}
wb.Worksheets[1].Cells.ImportDataTable(dt2, false, 1, 0, data.Rows.Count, 7);
}
}
wb.Save(Server.UrlEncode("作业活动及设备设施清单.xls"), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功。"));
}
catch (Exception ex)
{
return(Error(ex.ToString()));
}
}
private void Event_ImportFromExcelFile(string filename)
{
try
{
Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook();
this.AsposeWkb = wbk;
wbk.Open(filename);
if (workbook_idx == -1)
{
this.lb_workbooks.Items.Clear();
for (int i = 0; i < wbk.Worksheets.Count; i++)
{
this.lb_workbooks.Items.Add(wbk.Worksheets[i].Name);
}
}
}
finally
{
}
}
public static DataTable ImportExcelToGrid(string pstrFilename, int iColEnd, int iRowNumber)
{
string mstr_FileName = pstrFilename;
string mstr_PathFileName = mstr_FileName;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(mstr_FileName);
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
DataTable dt = new DataTable();
int iRowEnd = GetEndRow(ws, iRowNumber + 1);
try
{
for (int j = 0; j < iColEnd; j++)
{
string strDataColumn = ws.Cells[iRowNumber - 1, j].Value.ToString().Trim();
dt.Columns.Add(new DataColumn(strDataColumn, typeof(string)));
}
int i = iRowNumber;
for (; i < iRowEnd; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < iColEnd; j++)
{
string strValue = ws.Cells[i, j].Value == null ? "" : ws.Cells[i, j].Value.ToString();
dr[j] = strValue;
}
dt.Rows.Add(dr);
}
return(dt);
}
catch (Exception exp)
{
}
return(dt);
}
private string TransferFileToBase64String(string fileName)
{
FileStream fs = new FileStream(fileName, FileMode.Open);
byte[] tempBuffer = new byte[fs.Length];
fs.Read(tempBuffer, 0, tempBuffer.Length);
fs.Close();
MemoryStream ms = new MemoryStream(tempBuffer);
try
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003);
}
catch
{
throw new Exception("教學意見表樣版限用相容於 Excel 2003 檔案。");
}
try
{
return Convert.ToBase64String(tempBuffer);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string ImportLabor()
{
LaborprotectionBLL laborprotectionbll = new LaborprotectionBLL();
PostCache postCache = new PostCache();
PostBLL postBLL = new PostBLL();
DepartmentBLL departmentBLL = new DepartmentBLL();
//获取到已选数据
List <LaborprotectionEntity> laborlist = laborprotectionbll.GetLaborList();
var currUser = OperatorProvider.Provider.Current();
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
if (cells.MaxDataRow == 0)
{
message = "没有数据,请选择先填写模板在进行导入!";
return(message);
}
DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true);
int order = 1;
IList <LaborprotectionEntity> LaborList = new List <LaborprotectionEntity>();
IEnumerable <DepartmentEntity> deptlist = new DepartmentBLL().GetList();
OrganizeBLL orgbll = new OrganizeBLL();
//先获取到原始的一个编号
string no = laborprotectionbll.GetNo();
int ysno = Convert.ToInt32(no);
DataItemDetailBLL dataItemDetailBLL = new DataItemDetailBLL();
var dataitem = dataItemDetailBLL.GetDataItemListByItemCode("'LaborName'").ToList();
List <LaborprotectionEntity> insertpro = new List <LaborprotectionEntity>();
List <LaborinfoEntity> insertinfo = new List <LaborinfoEntity>();
//先获取人员
List <UserEntity> userlist =
new UserBLL().GetListForCon(it => it.IsPresence == "1" && it.Account != "System").ToList();
List <LaborequipmentinfoEntity> eqlist = new List <LaborequipmentinfoEntity>();
for (int i = 0; i < dt.Rows.Count; i++)
{
order = i;
string Name = dt.Rows[i]["名称"].ToString();
string Model = dt.Rows[i]["型号"].ToString();
string Type = dt.Rows[i]["类型"].ToString();
string DeptName = dt.Rows[i]["使用部门"].ToString();
string OrgName = dt.Rows[i]["使用单位"].ToString();
string PostName = dt.Rows[i]["使用岗位"].ToString().Trim();
string Unit = dt.Rows[i]["劳动防护用品单位"].ToString().Trim();
string Time = dt.Rows[i]["使用期限"].ToString().Trim();
string TimeType = dt.Rows[i]["使用期限单位"].ToString().Trim();
string deptId = "", deptCode = "", PostId = "";
//---****值存在空验证*****--
if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(Unit) || string.IsNullOrEmpty(DeptName) || string.IsNullOrEmpty(OrgName) || string.IsNullOrEmpty(PostName))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入.";
error++;
continue;
}
//验证机构是不是和自己一个机构
DepartmentEntity org = deptlist.Where(it => it.FullName == OrgName).FirstOrDefault();
if (org == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行使用单位名称不存在,未能导入.";
error++;
continue;
}
//如果导入的机构id和本人的机构id不一致
if (org.DepartmentId != currUser.OrganizeId)
{
falseMessage += "</br>" + "第" + (i + 2) + "行使用单位不是导入者的单位,未能导入.";
error++;
continue;
}
//验证所填部门是否存在
var deptFlag = false;
var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.FullName == DeptName).FirstOrDefault();
if (entity1 == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行使用部门不存在,未能导入.";
error++;
deptFlag = true;
break;
}
else
{
deptId = entity1.DepartmentId;
deptCode = entity1.EnCode;
}
//var deptFlag = false;
//var array = DeptName.Split('/');
//for (int j = 0; j < array.Length; j++)
//{
// if (j == 0)
// {
// if (currUser.RoleName.Contains("省级"))
// {
// var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity1 == null)
// {
// falseMessage += "</br>" + "第" + (i + 2) + "行部门不存在,未能导入.";
// error++;
// deptFlag = true;
// break;
// }
// else
// {
// deptId = entity1.DepartmentId;
// deptCode = entity1.EnCode;
// }
// }
// else
// {
// var entity = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "厂级" && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity == null)
// {
// entity = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "部门" && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity == null)
// {
// entity = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "承包商" && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity == null)
// {
// falseMessage += "</br>" + "第" + (i + 2) + "行部门不存在,未能导入.";
// error++;
// deptFlag = true;
// break;
// }
// else
// {
// deptId = entity.DepartmentId;
// deptCode = entity.EnCode;
// }
// }
// else
// {
// deptId = entity.DepartmentId;
// deptCode = entity.EnCode;
// }
// }
// else
// {
// deptId = entity.DepartmentId;
// deptCode = entity.EnCode;
// }
// }
// }
// else if (j == 1)
// {
// var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "专业" && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity1 == null)
// {
// entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity1 == null)
// {
// falseMessage += "</br>" + "第" + (i + 2) + "行专业/班组不存在,未能导入.";
// error++;
// deptFlag = true;
// break;
// }
// else
// {
// deptId = entity1.DepartmentId;
// deptCode = entity1.EnCode;
// }
// }
// else
// {
// deptId = entity1.DepartmentId;
// deptCode = entity1.EnCode;
// }
// }
// else
// {
// var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString()).FirstOrDefault();
// if (entity1 == null)
// {
// falseMessage += "</br>" + "第" + (i + 2) + "行班组不存在,未能导入.";
// error++;
// deptFlag = true;
// break;
// }
// else
// {
// deptId = entity1.DepartmentId;
// deptCode = entity1.EnCode;
// }
// }
//}
if (deptFlag)
{
continue;
}
//检验所填岗位是否属于其公司或者部门
if (string.IsNullOrEmpty(deptId) || deptId == "undefined")
{
//所属公司
RoleEntity data = postCache.GetList(orgId, "true").OrderBy(x => x.SortCode).Where(a => a.FullName == PostName).FirstOrDefault();
if (data == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行岗位不属于该公司,未能导入.";
error++;
continue;
}
}
else
{
//所属部门
//所属公司
RoleEntity data = postCache.GetList(orgId, deptId).OrderBy(x => x.SortCode).Where(a => a.FullName == PostName).FirstOrDefault();
if (data == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行岗位不属于该部门,未能导入.";
error++;
continue;
}
}
//--**验证岗位是否存在**--
RoleEntity re = postBLL.GetList().Where(a => a.FullName == PostName && a.OrganizeId == orgId).FirstOrDefault();
if (!(string.IsNullOrEmpty(deptId) || deptId == "undefined"))
{
re = postBLL.GetList().Where(a => a.FullName == PostName && a.OrganizeId == orgId && a.DeptId == deptId).FirstOrDefault();
if (re == null)
{
re = postBLL.GetList().Where(a =>
a.FullName == PostName && a.OrganizeId == orgId &&
a.Nature == departmentBLL.GetEntity(deptId).Nature).FirstOrDefault();
}
}
if (re == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行岗位有误,未能导入.";
error++;
continue;
}
else
{
PostId = re.RoleId;
}
LaborinfoEntity linfo = new LaborinfoEntity();
linfo.PostId = PostId;
linfo.DeptCode = deptCode;
linfo.DeptId = deptId;
linfo.DeptName = DeptName;
linfo.LaboroPerationTime = DateTime.Now;
linfo.LaboroPerationUserName = currUser.UserName;
linfo.Model = Model;
linfo.Name = Name;
linfo.OrgCode = currUser.OrganizeCode;
linfo.OrgId = currUser.OrganizeId;
linfo.OrgName = currUser.OrganizeName;
linfo.Type = Type;
if (Time == "" || !isInt(Time))
{
linfo.TimeNum = null;
}
else
{
linfo.TimeNum = Convert.ToInt32(Time);
linfo.TimeType = TimeType;
}
linfo.PostName = PostName;
linfo.Unit = Unit;
linfo.Create();
//如果已存在物品库中
LaborprotectionEntity lp = laborlist.Where(it => it.Name == Name).FirstOrDefault();
if (lp != null)
{
linfo.No = lp.No;
linfo.LId = lp.ID;
//如果库里有值 则使用库里的值
linfo.Type = linfo.Type;
linfo.TimeNum = lp.TimeNum;
linfo.TimeType = lp.TimeType;
}
else
{
LaborprotectionEntity newlp = new LaborprotectionEntity();
newlp.Create();
newlp.Name = Name;
newlp.No = ysno.ToString();
newlp.LaborOperationTime = DateTime.Now;
newlp.LaborOperationUserName = currUser.UserName;
newlp.Model = Model;
newlp.Type = Type;
newlp.Unit = Unit;
newlp.TimeNum = linfo.TimeNum;
newlp.TimeType = TimeType;
linfo.No = ysno.ToString();
linfo.LId = newlp.ID;
ysno++;
insertpro.Add(newlp);
}
int num = 0;
List <UserEntity> ulist = userlist.Where(it => it.DepartmentId == deptId && it.DutyId == PostId).ToList();
for (int j = 0; j < ulist.Count; j++)
{
//添加岗位关联人员
LaborequipmentinfoEntity eq = new LaborequipmentinfoEntity();
eq.UserName = ulist[j].RealName;
eq.AssId = linfo.ID;
eq.LaborType = 0;
eq.ShouldNum = 1;
num++;
eq.UserId = ulist[j].UserId;
if (linfo.Type == "衣服")
{
eq.Size = "L";
}
else if (linfo.Type == "鞋子")
{
eq.Size = "40";
}
else
{
eq.Size = "";
}
eq.Create();
eqlist.Add(eq);
}
linfo.ShouldNum = num;
insertinfo.Add(linfo);
}
laborinfobll.ImportSaveForm(insertinfo, insertpro, eqlist);
count = dt.Rows.Count;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
public static void Main()
{
//FISCA.Permission.Catalog cat = FISCA.Permission.RoleAclSource.Instance["教務作業"]["功能按鈕"];
//cat.Add(new FISCA.Permission.RibbonFeature("SHSchool.SHStaticRank2.Data", "計算固定排名(測試版)"));
var button = FISCA.Presentation.MotherForm.RibbonBarItems["教務作業", "批次作業/檢視"]["成績作業"]["計算固定排名"]["計算多學期成績固定排名(104學年度技職繁星)"];
button.Enable = FISCA.Permission.UserAcl.Current["SHSchool.SHStaticRank2.Data"].Executable;
button.Click += delegate
{
var conf = new StarTechnical();
conf.ShowDialog();
if (conf.DialogResult == System.Windows.Forms.DialogResult.OK)
{
CalcMutilSemeSubjectRank.OneClassCompleted += delegate
{
#region delegate
if (string.IsNullOrWhiteSpace(conf.Configure.Rank1Tag))
{
return;
}
#region 取得設定值 , MapRecord
string rank1tag = conf.Configure.Rank1Tag.Replace("[", "").Replace("]", "");
int subjectLimit = Math.Max(conf.Configure.useSubjectPrintList.Count, conf.Configure.SubjectLimit);
string path = Path.Combine(System.Windows.Forms.Application.StartupPath + "\\Reports",
CalcMutilSemeSubjectRank.FolderName);
string path_prefix = "E_群別";
string path_suffix = "報名表.xls";
FISCA.UDT.AccessHelper _AccessHelper = new FISCA.UDT.AccessHelper();
Dictionary <string, MapRecord> dmr;
try
{
dmr = _AccessHelper.Select <MapRecord>()
.FindAll(delegate(MapRecord mr) { return(!string.IsNullOrWhiteSpace(mr.student_tag)); })
.ToDictionary(x => x.student_tag, x => x);
}
catch (Exception)
{
return;
}
int seq;
Dictionary <string, Aspose.Cells.Workbook> wbs = new Dictionary <string, Aspose.Cells.Workbook>();
Dictionary <string, Aspose.Cells.Workbook> wbCheck = new Dictionary <string, Aspose.Cells.Workbook>();
Aspose.Cells.Workbook wb;
Aspose.Cells.Worksheet ws;
string fulltag;
string group_code;
string full_doc_path;
#endregion
foreach (DataRow student in CalcMutilSemeSubjectRank._table.Rows)
{
fulltag = student.Table.Columns.Contains("類別一分類") ? rank1tag + ":" + student["類別一分類"] : "";
//只處理有設定群別的類別
if (dmr.ContainsKey(fulltag))
{
#region 處理上傳表
group_code = dmr[fulltag].code1;
full_doc_path = Path.Combine(path, path_prefix + group_code + path_suffix);
try
{
#region 開啟xls檔案加入Dictionary,或新增
if (wbs.ContainsKey(group_code))
{
wb = wbs[group_code];
}
else if (File.Exists(full_doc_path))
{
wb = new Aspose.Cells.Workbook();
wb.Open(full_doc_path);
wbs.Add(group_code, wb);
}
else
{
wb = new Aspose.Cells.Workbook();
ws = wb.Worksheets[0];
#region 報表header初始化
ws.Cells[0, 0].PutValue("序號");
ws.Cells[0, 1].PutValue("學號");
ws.Cells[0, 2].PutValue("學生姓名");
ws.Cells[0, 3].PutValue("群別代碼");
ws.Cells[0, 4].PutValue("學制代碼");
ws.Cells[0, 5].PutValue("科(組)、學程名稱");
ws.Cells[0, 6].PutValue("班級名稱");
ws.Cells[0, 7].PutValue("學業平均成績科(組)、學程名次");
ws.Cells[0, 8].PutValue("學業平均成績群名次");
ws.Cells[0, 9].PutValue("專業及實習科目平均成績群名次");
ws.Cells[0, 10].PutValue("英文平均成績群名次");
ws.Cells[0, 11].PutValue("國文平均成績群名次");
ws.Cells[0, 12].PutValue("數學平均成績群名次");
#endregion
wbs.Add(group_code, wb);
}
#endregion
ws = wb.Worksheets[0];
seq = 1;//序號
while (!string.IsNullOrWhiteSpace("" + ws.Cells[seq, 0].Value))
{
seq++;
}
#region 填入資料
ws.Cells[seq, 0].PutValue("" + seq); //1.序號
ws.Cells[seq, 1].PutValue("" + student["學號"]); //2.學號
ws.Cells[seq, 2].PutValue("" + student["姓名"]); //3.學生姓名
ws.Cells[seq, 3].PutValue(group_code); //4.群別代碼
ws.Cells[seq, 4].PutValue(dmr[fulltag].code2); //5.學制代碼
ws.Cells[seq, 5].PutValue("" + student["科別"]); //6.科(組),學程名稱
ws.Cells[seq, 6].PutValue("" + student["班級"]); //7.班級名稱
ws.Cells[seq, 7].PutValue("" + student["學業原始平均科排名"]); //8.平均科排名
ws.Cells[seq, 8].PutValue("" + student["學業原始平均類別一排名"]); //9.學業平均成績群名次
ws.Cells[seq, 9].PutValue("" + student["篩選科目原始成績加權平均平均類別二排名"]); //10.專業及實習平均成績群名次
for (int i = 1; i <= subjectLimit; i++)
{
switch ("" + student["科目名稱" + i])
{
case "英文":
ws.Cells[seq, 10].PutValue("" + student["科目平均類別一排名" + i]); //11.英文平均成績群名次
break;
case "國文":
ws.Cells[seq, 11].PutValue("" + student["科目平均類別一排名" + i]); //12.國文平均成績群名次
break;
case "數學":
ws.Cells[seq, 12].PutValue("" + student["科目平均類別一排名" + i]); //13.數學平均群名次
break;
default:
break;
}
}
#endregion
wb = null;
}
catch (Exception)
{
#region save all opened excel
foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbs)
{
item.Value.Save(Path.Combine(path, path_prefix + item.Key + path_suffix), Aspose.Cells.FileFormatType.Excel97To2003);
}
#endregion
throw;
}
#endregion
#region 處理驗算表
group_code = dmr[fulltag].code1;
full_doc_path = Path.Combine(Path.Combine(path, path_prefix + "成績明細" + group_code + ".xls"));
try
{
#region 開啟xls檔案加入Dictionary,或新增
if (wbCheck.ContainsKey(group_code))
{
wb = wbCheck[group_code];
}
else if (File.Exists(full_doc_path))
{
wb = new Aspose.Cells.Workbook();
wb.Open(full_doc_path);
wbCheck.Add(group_code, wb);
}
else
{
wb = new Aspose.Cells.Workbook();
ws = wb.Worksheets[0];
#region 報表header初始化
ws.Cells[0, 0].PutValue("序號");
ws.Cells[0, 1].PutValue("學號");
ws.Cells[0, 2].PutValue("學生姓名");
ws.Cells[0, 3].PutValue("群別代碼");
ws.Cells[0, 4].PutValue("學制代碼");
ws.Cells[0, 5].PutValue("科(組)、學程名稱");
ws.Cells[0, 6].PutValue("班級名稱");
ws.Cells[0, 7].PutValue("類別一分類");
ws.Cells[0, 8].PutValue("類別二分類");
ws.Cells[0, 9].PutValue("學業原始平均成績");
ws.Cells[0, 10].PutValue("學業平均成績科(組)、學程名次");
ws.Cells[0, 11].PutValue("學業平均成績群名次");
ws.Cells[0, 12].PutValue("專業及實習科目平均成績");
ws.Cells[0, 13].PutValue("專業及實習科目平均成績群名次");
ws.Cells[0, 14].PutValue("英文科平均成績");
ws.Cells[0, 15].PutValue("英文平均成績群名次");
ws.Cells[0, 16].PutValue("國文科平均成績");
ws.Cells[0, 17].PutValue("國文平均成績群名次");
ws.Cells[0, 18].PutValue("數學科平均成績");
ws.Cells[0, 19].PutValue("數學平均成績群名次");
#endregion
wbCheck.Add(group_code, wb);
}
#endregion
ws = wb.Worksheets[0];
seq = 1;//序號
while (!string.IsNullOrWhiteSpace("" + ws.Cells[seq, 0].Value))
{
seq++;
}
#region 填入資料
ws.Cells[seq, 0].PutValue("" + seq); //1.序號
ws.Cells[seq, 1].PutValue("" + student["學號"]); //2.學號
ws.Cells[seq, 2].PutValue("" + student["姓名"]); //3.學生姓名
ws.Cells[seq, 3].PutValue(group_code); //4.群別代碼
ws.Cells[seq, 4].PutValue(dmr[fulltag].code2); //5.學制代碼
ws.Cells[seq, 5].PutValue("" + student["科別"]); //6.科(組),學程名稱
ws.Cells[seq, 6].PutValue("" + student["班級"]); //7.班級名稱
ws.Cells[seq, 7].PutValue("" + student["類別一分類"]);
ws.Cells[seq, 8].PutValue("" + student["類別二分類"]);
ws.Cells[seq, 9].PutValue("" + student["學業原始平均"]); //學業原始平均成績
ws.Cells[seq, 10].PutValue("" + student["學業原始平均科排名"]); //8.平均科排名
ws.Cells[seq, 11].PutValue("" + student["學業原始平均類別一排名"]); //9.學業平均成績群名次
ws.Cells[seq, 12].PutValue("" + student["篩選科目原始成績加權平均平均類別二"]); //專業及實習平均成績
ws.Cells[seq, 13].PutValue("" + student["篩選科目原始成績加權平均平均類別二排名"]); //10.專業及實習平均成績群名次
for (int i = 1; i <= subjectLimit; i++)
{
switch ("" + student["科目名稱" + i])
{
case "英文":
ws.Cells[seq, 14].PutValue("" + student["科目平均" + i]); //英文平均成績
ws.Cells[seq, 15].PutValue("" + student["科目平均類別一排名" + i]); //11.英文平均成績群名次
break;
case "國文":
ws.Cells[seq, 16].PutValue("" + student["科目平均" + i]); //國文平均成績
ws.Cells[seq, 17].PutValue("" + student["科目平均類別一排名" + i]); //12.國文平均成績群名次
break;
case "數學":
ws.Cells[seq, 18].PutValue("" + student["科目平均" + i]); //數學平均成績
ws.Cells[seq, 19].PutValue("" + student["科目平均類別一排名" + i]); //13.數學平均群名次
break;
default:
break;
}
}
#endregion
wb = null;
}
catch (Exception)
{
#region save all opened excel
foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbCheck)
{
item.Value.Save(Path.Combine(path, path_prefix + "成績明細" + item.Key + ".xls"), Aspose.Cells.FileFormatType.Excel97To2003);
}
#endregion
throw;
}
#endregion
}
}
#region save all opened excel
foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbs)
{
item.Value.Save(Path.Combine(path, path_prefix + item.Key + path_suffix), Aspose.Cells.FileFormatType.Excel97To2003);
}
#endregion
#region save all opened excel wbCheck
foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbCheck)
{
item.Value.Save(Path.Combine(path, path_prefix + "成績明細" + item.Key + ".xls"), Aspose.Cells.FileFormatType.Excel97To2003);
}
#endregion
wbs = null;
#endregion
};
CalcMutilSemeSubjectRank.Setup(conf.Configure);
}
};
}
public string ImportQuestion()
{
var user = OperatorProvider.Provider.Current();
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
string where = "";
if (user.RoleName.Contains("厂级"))
{
where += " a.ISROLE = '0' and a.CREATEUSERDEPTCODE ='" + user.DeptCode + "'";
}
else
{
where += " a.ISROLE = '1' and a.CREATEUSERDEPTCODE ='" + user.DeptCode + "'";
}
DataTable dtcontent = matrixsafecheckbll.GetInfoBySql("select ID, CODE,CONTENT from BIS_MATRIXCONTENT a where " + where);
if (dtcontent.Rows.Count == 0)
{
return("请先设置检查内容");
}
DataTable dtdept = matrixsafecheckbll.GetInfoBySql("select ID, code,dept,deptname,deptcode from BIS_MATRIXDEPT a where " + where);
if (dtdept.Rows.Count == 0)
{
return("请先设置检查部门");
}
DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, false);
dt.Rows.RemoveAt(0);
for (int i = 1; i < dt.Rows.Count; i++)
{
//检查日期
string checktime = dt.Rows[i][0].ToString();
//检查内容
string checkcontent = dt.Rows[i][1].ToString();
//检查部门
string checkdept = dt.Rows[i][2].ToString();
//检查人员
string checkuser = dt.Rows[i][3].ToString();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(checktime) && string.IsNullOrEmpty(checkcontent) && string.IsNullOrEmpty(checkdept) && string.IsNullOrEmpty(checkuser))
{
continue;
}
if (string.IsNullOrEmpty(checktime) || string.IsNullOrEmpty(checkcontent) || string.IsNullOrEmpty(checkdept))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入.";
error++;
continue;
}
MatrixsafecheckEntity entir = new MatrixsafecheckEntity();
entir.ISOVER = 1;
// 检查时间
try
{
if (!string.IsNullOrEmpty(checktime))
{
// 检查时间
DataTable dtcount = matrixsafecheckbll.GetInfoBySql("select id from bis_matrixsafecheck where checktime = to_date('" + DateTime.Parse(checktime).ToString("yyyy-MM-dd") + "', 'yyyy-MM-dd HH24:mi:ss') ");
if (dtcount.Rows.Count > 0)
{
falseMessage += "</br>" + "第" + (i + 2) + "行检查时间已存在,未能导入.";
error++;
continue;
}
else
{
entir.CHECKTIME = DateTime.Parse(DateTime.Parse(checktime).ToString("yyyy-MM-dd"));
}
}
}
catch
{
falseMessage += "</br>" + "第" + (i + 2) + "行检查时间有误,未能导入.";
error++;
continue;
}
// 检查内容
entir.CONTENT = checkcontent;
if (!string.IsNullOrEmpty(checkcontent))
{
var conarr = checkcontent.Split(',');
int contentresult = 0;
foreach (string continfo in conarr)
{
foreach (DataRow condr in dtcontent.Rows)
{
if (continfo == condr["CONTENT"].ToString())
{
if (entir.CONTENTID == "" || entir.CONTENTID == null)
{
entir.CONTENTID = condr["ID"].ToString();
entir.CONTENTNUM = condr["CODE"].ToString();
}
else
{
entir.CONTENTID += "," + condr["ID"].ToString();
entir.CONTENTNUM += "," + condr["CODE"].ToString();
}
contentresult++;
break;
}
}
}
if (contentresult != conarr.Length)
{
falseMessage += "</br>" + "第" + (i + 2) + "行检查内容有误,未能导入.";
error++;
continue;
}
}
// 检查部门
entir.CHECKDEPTNAME = checkdept;
if (!string.IsNullOrEmpty(checkdept))
{
var deptarr = checkdept.Split(',');
int checkdeptresult = 0;
foreach (string deptinfo in deptarr)
{
foreach (DataRow deptdr in dtdept.Rows)
{
if (deptinfo == deptdr["DEPTNAME"].ToString())
{
if (entir.CHECKDEPTSEL == "" || entir.CHECKDEPTSEL == null)
{
entir.CHECKDEPTSEL = deptdr["ID"].ToString();
entir.CHECKDEPT = deptdr["DEPT"].ToString();
entir.CHECKDEPTCODE = deptdr["DEPTCODE"].ToString();
entir.CHECKDEPTNUM = deptdr["CODE"].ToString();
}
else
{
entir.CHECKDEPTSEL += "," + deptdr["ID"].ToString();
entir.CHECKDEPT += "," + deptdr["DEPT"].ToString();
entir.CHECKDEPTCODE += "," + deptdr["DEPTCODE"].ToString();
entir.CHECKDEPTNUM += "," + deptdr["CODE"].ToString();
}
checkdeptresult++;
break;
}
}
}
if (checkdeptresult != deptarr.Length)
{
falseMessage += "</br>" + "第" + (i + 2) + "行检查部门有误,未能导入.";
error++;
continue;
}
}
// 检查人员
entir.CHECKUSERNAME = checkuser;
if (!string.IsNullOrEmpty(checkuser))
{
var userarr = checkuser.Split(',');
int checkuserresult = 0;
foreach (string userinfo in userarr)
{
DataTable userdt = matrixsafecheckbll.GetInfoBySql(" SELECT USERID,ACCOUNT,DEPARTMENTCODE,REALNAME FROM BASE_USER WHERE REALNAME = '" + userinfo + "' ");
if (userdt.Rows.Count == 0)
{
break;
}
else
{
if (entir.CHECKUSER == "" || entir.CHECKUSER == null)
{
entir.CHECKUSER = userdt.Rows[0]["USERID"].ToString();
entir.CHECKUSERCODE = userdt.Rows[0]["ACCOUNT"].ToString();
entir.CHECKUSERDEPT = userdt.Rows[0]["DEPARTMENTCODE"].ToString();
}
else
{
entir.CHECKUSER += "," + userdt.Rows[0]["USERID"].ToString();
entir.CHECKUSERCODE += "," + userdt.Rows[0]["ACCOUNT"].ToString();
entir.CHECKUSERDEPT = userdt.Rows[0]["DEPARTMENTCODE"].ToString();
}
checkuserresult++;
}
}
if (checkuserresult != userarr.Length)
{
falseMessage += "</br>" + "第" + (i + 2) + "行检查人员有误,未能导入.";
error++;
continue;
}
}
try
{
entir.ID = Guid.NewGuid().ToString();
matrixsafecheckbll.SaveForm(entir.ID, entir);
}
catch
{
falseMessage += "</br>" + "第" + (i + 2) + "行保存失败,未能导入.";
error++;
continue;
}
}
count = dt.Rows.Count - 1;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
public string ImportStandard(string treeId, string treeName, string treeCode)
{
try
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
int success = 0;
string message = "请选择文件格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
if (HttpContext.Request.Files.Count != 2)
{
return("请按正确的方式导入两个文件.");
}
HttpPostedFileBase file = HttpContext.Request.Files[0];
HttpPostedFileBase file2 = HttpContext.Request.Files[1];
if (string.IsNullOrEmpty(file.FileName) || string.IsNullOrEmpty(file2.FileName))
{
return(message);
}
Boolean isZip1 = file.FileName.Substring(file.FileName.IndexOf('.')).Contains("zip"); //第一个文件是否为Zip格式
Boolean isZip2 = file2.FileName.Substring(file2.FileName.IndexOf('.')).Contains("zip"); //第二个文件是否为Zip格式
if ((isZip1 || isZip2) == false || (isZip1 && isZip2) == true)
{
return(message);
}
string fileName1 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName1));
string fileName2 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file2.FileName);
file2.SaveAs(Server.MapPath("~/Resource/temp/" + fileName2));
string decompressionDirectory = Server.MapPath("~/Resource/decompression/") + DateTime.Now.ToString("yyyyMMddhhmmssfff") + "\\";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
if (isZip1)
{
UnZip(Server.MapPath("~/Resource/temp/" + fileName1), decompressionDirectory, "", true);
wb.Open(Server.MapPath("~/Resource/temp/" + fileName2));
}
else
{
UnZip(Server.MapPath("~/Resource/temp/" + fileName2), decompressionDirectory, "", true);
wb.Open(Server.MapPath("~/Resource/temp/" + fileName1));
}
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn, false);
for (int i = 0; i < dt.Rows.Count; i++)
{
SafeInstitutionEntity entity = new SafeInstitutionEntity();
entity.Id = Guid.NewGuid().ToString();
entity.FilesId = Guid.NewGuid().ToString();
//文件名称
string filename = dt.Rows[i][0].ToString();
//文件编号
string filecode = dt.Rows[i][1].ToString();
//发布单位
string issuedept = dt.Rows[i][4].ToString();
//发布时间
string releasedate = dt.Rows[i][5].ToString();
//修订时间
string revisedate = dt.Rows[i][6].ToString();
//实施时间
string carrydate = dt.Rows[i][7].ToString();
//备注
string Remark = dt.Rows[i][8].ToString();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(filename) || string.IsNullOrEmpty(filecode))
{
falseMessage += "</br>" + "第" + (i + 1) + "行值存在空,未能导入.";
error++;
continue;
}
bool conbool = false;
//正文附件路径
string[] filepaths = dt.Rows[i][2].ToString().Split(';');
var filepath = "";
for (int j = 0; j < filepaths.Length; j++)
{
filepath = filepaths[j];
if (string.IsNullOrEmpty(filepath))
{
continue;
}
string strPath = filepath.Substring(filepath.IndexOf('.'));
//---****文件格式验证*****--
if (!(strPath.Contains("doc") || strPath.Contains("docx") || strPath.Contains("pdf")))
{
falseMessage += "</br>" + "第" + (i + 1) + "行指定正文附件格式不正确,未能导入.";
error++;
conbool = true;
continue;
}
//---****文件是否存在验证*****--
if (!System.IO.File.Exists(decompressionDirectory + filepath))
{
falseMessage += "</br>" + "第" + (i + 1) + "行指定正文附件不存在,未能导入.";
error++;
conbool = true;
continue;
}
var fileinfo = new FileInfo(decompressionDirectory + filepath);
FileInfoEntity fileInfoEntity = new FileInfoEntity();
string fileguid = Guid.NewGuid().ToString();
fileInfoEntity.Create();
fileInfoEntity.RecId = entity.FilesId; //关联ID
fileInfoEntity.FileName = filepath;
fileInfoEntity.FilePath = "~/Resource/InstitutionSystem/" + fileguid + fileinfo.Extension;
fileInfoEntity.FileSize = (Math.Round(decimal.Parse(fileinfo.Length.ToString()) / decimal.Parse("1024"), 2)).ToString();//文件大小（kb）
fileInfoEntity.FileExtensions = fileinfo.Extension;
fileInfoEntity.FileType = fileinfo.Extension.Replace(".", "");
TransportRemoteToServer(Server.MapPath("~/Resource/InstitutionSystem/"), decompressionDirectory + filepath, fileguid + fileinfo.Extension);
fileinfobll.SaveForm("", fileInfoEntity);
}
if (conbool)
{
continue;
}
//正文附件路径
filepaths = dt.Rows[i][3].ToString().Split(';');
filepath = "";
for (int j = 0; j < filepaths.Length; j++)
{
filepath = filepaths[j];
if (string.IsNullOrEmpty(filepath))
{
continue;
}
string strPath = filepath.Substring(filepath.IndexOf('.'));
//---****文件格式验证*****--
if (!(strPath.Contains("doc") || strPath.Contains("docx") || strPath.Contains("pdf") || strPath.Contains("ppt") || strPath.Contains("xlsx") || strPath.Contains("xls") || strPath.Contains("png") || strPath.Contains("jpg") || strPath.Contains("jpeg")))
{
falseMessage += "</br>" + "第" + (i + 1) + "行指定附件格式不正确,未能导入.";
error++;
conbool = true;
continue;
}
//---****文件是否存在验证*****--
if (!System.IO.File.Exists(decompressionDirectory + filepath))
{
falseMessage += "</br>" + "第" + (i + 1) + "行指定附件不存在,未能导入.";
error++;
conbool = true;
continue;
}
var fileinfo = new FileInfo(decompressionDirectory + filepath);
FileInfoEntity fileInfoEntity = new FileInfoEntity();
string fileguid = Guid.NewGuid().ToString();
fileInfoEntity.Create();
fileInfoEntity.RecId = entity.Id; //关联ID
fileInfoEntity.FileName = filepath;
fileInfoEntity.FilePath = "~/Resource/InstitutionSystem/" + fileguid + fileinfo.Extension;
fileInfoEntity.FileSize = (Math.Round(decimal.Parse(fileinfo.Length.ToString()) / decimal.Parse("1024"), 2)).ToString();//文件大小（kb）
fileInfoEntity.FileExtensions = fileinfo.Extension;
fileInfoEntity.FileType = fileinfo.Extension.Replace(".", "");
TransportRemoteToServer(Server.MapPath("~/Resource/InstitutionSystem/"), decompressionDirectory + filepath, fileguid + fileinfo.Extension);
fileinfobll.SaveForm("", fileInfoEntity);
}
entity.FileName = filename;
entity.FileCode = filecode;
entity.IssueDept = issuedept;
entity.LawTypeId = treeId;
entity.LawTypeName = treeName;
entity.LawTypeCode = treeCode;
if (!string.IsNullOrEmpty(releasedate))
{
entity.ReleaseDate = Convert.ToDateTime(releasedate);
}
if (!string.IsNullOrEmpty(revisedate))
{
entity.ReviseDate = Convert.ToDateTime(revisedate);
}
if (!string.IsNullOrEmpty(carrydate))
{
entity.CarryDate = Convert.ToDateTime(carrydate);
}
entity.Remark = !string.IsNullOrEmpty(Remark) ? Remark : "";
try
{
safeinstitutionbll.SaveForm(entity.Id, entity);
success++;
}
catch
{
error++;
}
}
message = "共有" + dt.Rows.Count + "条记录,成功导入" + success + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
catch (Exception e)
{
return("导入的Excel数据格式不正确，请下载标准模板重新填写！");
}
}
public string ImportUsers(string applyId)
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无法操作此功能");
}
var currUser = OperatorProvider.Provider.Current();
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true);
StringBuilder sb = new StringBuilder("begin \r\n");
IList <ThreePeopleInfoEntity> list = new List <ThreePeopleInfoEntity>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
string userName = dr[0].ToString().Trim();
string idCard = dr[1].ToString().Trim();
string userType = dr[2].ToString().Trim();
if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(idCard) || string.IsNullOrEmpty(userType))
{
falseMessage += "</br>" + "第" + (i + 3) + "行值存在空,未能导入.";
error++;
continue;
}
//---****身份证正确验证*****--
if (!Regex.IsMatch(idCard, @"^(^d{15}$|^\d{18}$|^\d{17}(\d|X|x))$", RegexOptions.IgnoreCase))
{
falseMessage += "</br>" + "第" + (i + 3) + "行身份证号格式有误,未能导入.";
error++;
continue;
}
if (list.Count(t => t.IdCard == idCard) > 0)
{
falseMessage += "</br>" + "第" + (i + 3) + "行人员身份证信息已存在,未能导入.";
error++;
continue;
}
else
{
list.Add(new ThreePeopleInfoEntity
{
UserName = userName,
IdCard = idCard,
ApplyId = applyId,
OrgCode = currUser.OrganizeCode,
TicketType = userType
});
}
}
//sb.Append("end \r\n commit;");
if (dt.Rows.Count > 0)
{
CacheFactory.Cache().WriteCache(list, applyId, DateTime.Now.AddMinutes(30));
// new DepartmentBLL().ExecuteSql(sb.ToString());
}
count = dt.Rows.Count;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult ExportData(string queryJson)
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "a.Id as workid";
pagination.p_fields = "supervisestate,taskworktype,handtype,to_char(TaskWorkStartTime,'yyyy-mm-dd hh24:mi') || ' - ' || to_char(TaskWorkStartTime,'yyyy-mm-dd hh24:mi'),'' as taskdept,'' as taskworkplace,b.fullname,taskusername,'-' as timelong";
pagination.p_tablename = "bis_supervisetask a left join Base_Department b on a.steamid=b.departmentid";
pagination.conditionJson = "1=1";
pagination.sidx = "supervisestate asc,a.createdate";
pagination.sord = "desc";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (!user.IsSystem)
{
string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search");
if (!string.IsNullOrEmpty(authType))
{
switch (authType)
{
case "1":
pagination.conditionJson += " and a.createuserid='" + user.UserId + "'";
break;
case "2":
pagination.conditionJson += " and a.createuserdeptcode='" + user.DeptCode + "'";
break;
case "3": //本子部门
pagination.conditionJson += string.Format(" and ((a.steamid in(select departmentid from base_department where encode like '{0}%' or senddeptid='{1}') and supervisestate!='1') or a.createuserid='{2}')", user.DeptCode, user.DeptId, user.UserId);
break;
case "4":
pagination.conditionJson += " and ((a.createuserorgcode='" + user.OrganizeCode + "' and supervisestate!='1') or(a.createuserid='" + user.UserId + "'))";
break;
}
}
else
{
pagination.conditionJson += " and 0=1";
}
}
DataTable exportTable = supervisetaskbll.GetPageDataTable(pagination, queryJson);
foreach (DataRow item in exportTable.Rows)
{
if (item["supervisestate"].ToString() == "1")
{
item["supervisestate"] = "创建监督";
}
else if (item["supervisestate"].ToString() == "2")
{
item["supervisestate"] = "未监督";
}
else
{
item["supervisestate"] = "已监督";
}
var type = "";
if (!string.IsNullOrEmpty(item["taskworktype"].ToString()) && !string.IsNullOrEmpty(item["handtype"].ToString()))
{
type = item["taskworktype"].ToString() + "," + item["handtype"].ToString();
}
else
{
if (!string.IsNullOrEmpty(item["taskworktype"].ToString()))
{
type = item["taskworktype"].ToString();
}
if (!string.IsNullOrEmpty(item["handtype"].ToString()))
{
type = item["handtype"].ToString();
}
}
item["taskworktype"] = type;
var workdata = superviseworkinfobll.GetList(item["workid"].ToString());
string place = "", deptname = "";
foreach (var work in workdata)
{
place += work.WorkPlace + ",";
deptname += work.WorkDeptName + ",";
}
if (!string.IsNullOrEmpty(place))
{
item["taskworkplace"] = place.TrimEnd(',');
}
if (!string.IsNullOrEmpty(deptname))
{
item["taskdept"] = deptname.TrimEnd(',');
}
}
exportTable.Columns.Remove("workid");
exportTable.Columns.Remove("handtype");
exportTable.Columns.Remove("r");
// 确定导出文件名
string fileName = "高风险作业旁站监督信息";
HttpResponse resp = System.Web.HttpContext.Current.Response;
// 详细列表内容
string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/高风险作业旁站监督信息.xlsx"));
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[1, 1];
sheet.Cells.ImportDataTable(exportTable, false, 1, 0);
wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功!"));
}
public string ImportEle()
{
UserBLL userbll = new UserBLL();
NosaeleBLL nosaelebll = new NosaeleBLL();
int error = 0;
int sussceed = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
string filePath = Server.MapPath("~/Resource/temp/" + fileName);
file.SaveAs(filePath);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
if ((cells.MaxDataRow - 3) == 0)
{
message = "没有数据,请选择先填写模板在进行导入!";
return(message);
}
DataTable dt = cells.ExportDataTable(3, 0, (cells.MaxDataRow - 2), cells.MaxColumn + 1, true);
//DataTable dt = ExcelHelper.ExcelImport(filePath);
for (int i = 0; i < dt.Rows.Count; i++)
{
object[] vals = dt.Rows[i].ItemArray;
if (IsEndRow(vals) == true)
{
break;
}
var msg = "";
if (Validate(i, vals, userbll, nosaelebll, out msg) == true)
{
var entity = GenEntity(vals, userbll, nosaelebll);
nosaareabll.SaveForm(entity.ID, entity);
sussceed++;
}
else
{
falseMessage += "第" + (i + 1) + "行" + msg + "<br/>";
error++;
}
}
count = dt.Rows.Count;
message = "共有" + count + "条记录,成功导入" + sussceed + "条，失败" + error + "条";
message += "<br/>" + falseMessage;
//删除临时文件
System.IO.File.Delete(filePath);
}
return(message);
}
// 上傳模組
private void UploadTemplate(string fileName, ref bool uploadIndex, ref string uploadData)
{
FileStream fs = new FileStream(fileName, FileMode.Open);
byte[] tempBuffer = new byte[fs.Length];
fs.Read(tempBuffer, 0, tempBuffer.Length);
MemoryStream ms = new MemoryStream(tempBuffer);
try
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003);
wb = null;
}
catch
{
throw new Exception("此版學籍表範本限用相容於 Excel 2003 檔案。");
}
try
{
uploadData = Convert.ToBase64String(tempBuffer);
uploadIndex = true;
fs.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public string ImportQuestion(string keyvalue)
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, false);
for (int i = 1; i < dt.Rows.Count; i++)
{
//序号
string sortnum = dt.Rows[i][0].ToString();
//发现问题
string findquestion = dt.Rows[i][1].ToString();
//整改措施
string actioncontent = dt.Rows[i][2].ToString();
//责任部门
string dutydept = dt.Rows[i][3].ToString();
//责任人
string dutyusername = dt.Rows[i][4].ToString();
//要求完成时间
string finishdate = dt.Rows[i][5].ToString();
//验收人
string acceptuser = dt.Rows[i][6].ToString();
//整改完成情况
string actionresult = dt.Rows[i][7].ToString();
//实际完成时间
string actualdate = dt.Rows[i][8].ToString();
//备注
string beizhu = dt.Rows[i][9].ToString();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(findquestion) || string.IsNullOrEmpty(actioncontent) || string.IsNullOrEmpty(dutydept) || string.IsNullOrEmpty(dutyusername) || string.IsNullOrEmpty(finishdate) || string.IsNullOrEmpty(acceptuser))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入.";
error++;
continue;
}
if (actionresult == "已完成" && string.IsNullOrEmpty(actualdate))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值,整改完成情况为已完成时必须填实际完成时间.";
error++;
continue;
}
FivesafetycheckauditEntity pe = new FivesafetycheckauditEntity();
pe.ID = Guid.NewGuid().ToString();
pe.FINDQUESTION = findquestion;
pe.ACTIONCONTENT = actioncontent;
pe.CHECKPASS = "******";
pe.CHECKID = keyvalue;
pe.DUTYDEPT = dutydept;
if (!string.IsNullOrEmpty(dutydept))
{
string[] ar = dutydept.Split('/');
//int resultdept = 0;
DataTable deptentity = null;
string deptid = "";
foreach (string arstr in ar)
{
if (deptid == "")
{
deptentity = fivesafetycheckbll.GetInfoBySql("select departmentid,encode from base_department where fullname = '" + arstr + "' ");
}
else
{
deptentity = fivesafetycheckbll.GetInfoBySql("select departmentid,encode from base_department where PARENTID = '" + deptid + "' and fullname = '" + arstr + "' ");
}
if (deptentity.Rows.Count == 0)
{
//resultdept = 1;
break;
}
else
{
deptid = deptentity.Rows[0]["departmentid"].ToString();
}
}
//var deptentity = fivesafetycheckbll.GetInfoBySql("select departmentid,encode from base_department where fullname = '" + dutydept + "' ");
if (deptentity.Rows.Count > 0)
{
pe.DUTYDEPTCODE = deptentity.Rows[0]["encode"].ToString();
pe.DUTYDEPTID = deptentity.Rows[0]["departmentid"].ToString();
}
else
{
// 如果查询不到部门，智能提醒检查 刘畅
string stdept = fivesafetycheckbll.GetDeptByName(dutydept);
if (stdept != "" && stdept != null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行责任部门未在系统中查到,智能识别判断您输入的可能是<" + stdept + ">.";
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行责任部门未在系统中查到,未能导入.";
}
error++;
continue;
}
}
pe.DUTYUSERNAME = dutyusername;
if (!string.IsNullOrEmpty(dutyusername))
{
if (dutyusername.IndexOf('/') > -1)
{
var deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + dutyusername.Split('/')[0] + "' ");
if (deptentity.Rows.Count == 1)
{
pe.DUTYUSERID = deptentity.Rows[0]["userid"].ToString();
}
else if (deptentity.Rows.Count > 1)
{
deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + dutyusername.Split('/')[0] + "' and mobile='" + dutyusername.Split('/')[1] + "' ");
if (deptentity.Rows[0]["mobile"].ToString() == dutyusername.Split('/')[1])
{
pe.DUTYUSERID = deptentity.Rows[0]["userid"].ToString();
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行责任人未在系统中查到,未能导入.";
error++;
continue;
}
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行责任人未在系统中查到,未能导入.";
error++;
continue;
}
}
else
{
var deptentity = fivesafetycheckbll.GetInfoBySql("select userid from base_user where realname = '" + dutyusername + "' ");
if (deptentity.Rows.Count > 0)
{
pe.DUTYUSERID = deptentity.Rows[0]["userid"].ToString();
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行责任人未在系统中查到,未能导入.";
error++;
continue;
}
}
}
pe.ACCEPTUSER = acceptuser;
if (!string.IsNullOrEmpty(acceptuser))
{
if (acceptuser.IndexOf('/') > -1)
{
var deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + acceptuser.Split('/')[0] + "' ");
if (deptentity.Rows.Count == 1)
{
pe.ACCEPTUSERID = deptentity.Rows[0]["userid"].ToString();
}
else if (deptentity.Rows.Count > 1)
{
deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + acceptuser.Split('/')[0] + "' and mobile='" + acceptuser.Split('/')[1] + "' ");
if (deptentity.Rows[0]["mobile"].ToString() == acceptuser.Split('/')[1])
{
pe.ACCEPTUSERID = deptentity.Rows[0]["userid"].ToString();
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行验收人未在系统中查到,未能导入.";
error++;
continue;
}
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行验收人未在系统中查到,未能导入.";
error++;
continue;
}
}
else
{
var deptentity = fivesafetycheckbll.GetInfoBySql("select userid from base_user where realname = '" + acceptuser + "' ");
if (deptentity.Rows.Count > 0)
{
pe.ACCEPTUSERID = deptentity.Rows[0]["userid"].ToString();
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行验收人未在系统中查到,未能导入.";
error++;
continue;
}
}
}
try
{
if (!string.IsNullOrEmpty(finishdate))
{
pe.FINISHDATE = DateTime.Parse(DateTime.Parse(finishdate).ToString("yyyy-MM-dd"));
}
}
catch
{
falseMessage += "</br>" + "第" + (i + 2) + "行要求完成时间有误,未能导入.";
error++;
continue;
}
try
{
if (!string.IsNullOrEmpty(actualdate))
{
pe.ACTUALDATE = DateTime.Parse(DateTime.Parse(finishdate).ToString("yyyy-MM-dd"));
}
}
catch
{
falseMessage += "</br>" + "第" + (i + 2) + "行实际完成时间有误,未能导入.";
error++;
continue;
}
//完成情况
if (!string.IsNullOrEmpty(actionresult))
{
if (actionresult == "已完成")
{
pe.ACTIONRESULT = "0";
pe.CHECKPASS = "******";
pe.ACCEPTREUSLT = "0";
}
else if (actionresult == "未完成")
{
pe.ACTIONRESULT = "1";
pe.ACTUALDATE = null;
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行整改完成情况有误,未能导入.";
error++;
continue;
}
}
//备注
if (beizhu.Length > 2000)
{
falseMessage += "</br>" + "第" + (i + 2) + "行备注文本过长,未能导入.";
error++;
continue;
}
else
{
pe.BEIZHU = beizhu;
}
try
{
fivesafetycheckauditbll.SaveForm(pe.ID, pe);
}
catch
{
falseMessage += "</br>" + "第" + (i + 2) + "行保存失败,未能导入.";
error++;
continue;
}
}
count = dt.Rows.Count - 1;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
public static void main()
{
FISCA.Presentation.RibbonBarItem item1 = FISCA.Presentation.MotherForm.RibbonBarItems["教務作業", "資料統計"];
item1["報表"].Size = FISCA.Presentation.RibbonBarButton.MenuButtonSize.Large;
item1["報表"]["高中職學校班級及學生概況"].Enable = UserAcl.Current["SH.School.SchoolStatistics"].Executable;
item1["報表"]["高中職學校班級及學生概況"].Click += delegate
{
//運用Aspose元件來新增活頁簿
Aspose.Cells.Workbook ScoreWorkBook = new Aspose.Cells.Workbook();
System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog();
saveFileDialog.FileName = "高中職學校班級及學生概況.xls";
saveFileDialog.AddExtension = true;
saveFileDialog.DefaultExt = "xls";
string filename = (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) ? saveFileDialog.FileName : "";
// ScoreWorkBook.Open(System.Windows.Forms.Application.StartupPath + "\\Customize\\高中職學校班級及學生概況.xlt");
ScoreWorkBook.Open(new System.IO.MemoryStream(Properties.Resources.高中職學校班級及學生概況));
Program.ErrorList.Clear();
#region 普通科
ClassStatistics ClassSat = new ClassStatistics();
ClassSat.StartStatistics(0);
ScoreWorkBook.Worksheets[0].Name = "高中職學校班級及學生概況-普通科";
ScoreWorkBook.Worksheets[0].Cells[4, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolCode);
//一年級班級數總計
ScoreWorkBook.Worksheets[0].Cells[7, 5].PutValue(ClassSat.Level1Count24);
ScoreWorkBook.Worksheets[0].Cells[7, 7].PutValue(ClassSat.Level1Count34);
ScoreWorkBook.Worksheets[0].Cells[7, 9].PutValue(ClassSat.Level1Count44);
ScoreWorkBook.Worksheets[0].Cells[7, 11].PutValue(ClassSat.Level1Count54);
ScoreWorkBook.Worksheets[0].Cells[7, 13].PutValue(ClassSat.Level1Count55);
//二年級班級數總計
ScoreWorkBook.Worksheets[0].Cells[8, 5].PutValue(ClassSat.Level2Count24);
ScoreWorkBook.Worksheets[0].Cells[8, 7].PutValue(ClassSat.Level2Count34);
ScoreWorkBook.Worksheets[0].Cells[8, 9].PutValue(ClassSat.Level2Count44);
ScoreWorkBook.Worksheets[0].Cells[8, 11].PutValue(ClassSat.Level2Count54);
ScoreWorkBook.Worksheets[0].Cells[8, 13].PutValue(ClassSat.Level2Count55);
//三年級班級數總計
ScoreWorkBook.Worksheets[0].Cells[9, 5].PutValue(ClassSat.Level3Count24);
ScoreWorkBook.Worksheets[0].Cells[9, 7].PutValue(ClassSat.Level3Count34);
ScoreWorkBook.Worksheets[0].Cells[9, 9].PutValue(ClassSat.Level3Count44);
ScoreWorkBook.Worksheets[0].Cells[9, 11].PutValue(ClassSat.Level3Count54);
ScoreWorkBook.Worksheets[0].Cells[9, 13].PutValue(ClassSat.Level3Count55);
//四年級班級數總計
ScoreWorkBook.Worksheets[0].Cells[10, 5].PutValue(ClassSat.Level4Count24);
ScoreWorkBook.Worksheets[0].Cells[10, 7].PutValue(ClassSat.Level4Count34);
ScoreWorkBook.Worksheets[0].Cells[10, 9].PutValue(ClassSat.Level4Count44);
ScoreWorkBook.Worksheets[0].Cells[10, 11].PutValue(ClassSat.Level4Count54);
ScoreWorkBook.Worksheets[0].Cells[10, 13].PutValue(ClassSat.Level4Count55);
//一年級學生數總計
ScoreWorkBook.Worksheets[0].Cells[15, 5].PutValue(ClassSat.SR15C5);
ScoreWorkBook.Worksheets[0].Cells[15, 6].PutValue(ClassSat.SR15C6);
ScoreWorkBook.Worksheets[0].Cells[15, 7].PutValue(ClassSat.SR15C7);
ScoreWorkBook.Worksheets[0].Cells[15, 8].PutValue(ClassSat.SR15C8);
ScoreWorkBook.Worksheets[0].Cells[15, 9].PutValue(ClassSat.SR15C9);
ScoreWorkBook.Worksheets[0].Cells[15, 10].PutValue(ClassSat.SR15C10);
ScoreWorkBook.Worksheets[0].Cells[15, 11].PutValue(ClassSat.SR15C11);
ScoreWorkBook.Worksheets[0].Cells[15, 12].PutValue(ClassSat.SR15C12);
ScoreWorkBook.Worksheets[0].Cells[15, 13].PutValue(ClassSat.SR15C13);
ScoreWorkBook.Worksheets[0].Cells[15, 14].PutValue(ClassSat.SR15C14);
ScoreWorkBook.Worksheets[0].Cells[16, 5].PutValue(ClassSat.SR16C5);
ScoreWorkBook.Worksheets[0].Cells[16, 6].PutValue(ClassSat.SR16C6);
ScoreWorkBook.Worksheets[0].Cells[16, 7].PutValue(ClassSat.SR16C7);
ScoreWorkBook.Worksheets[0].Cells[16, 8].PutValue(ClassSat.SR16C8);
ScoreWorkBook.Worksheets[0].Cells[16, 9].PutValue(ClassSat.SR16C9);
ScoreWorkBook.Worksheets[0].Cells[16, 10].PutValue(ClassSat.SR16C10);
ScoreWorkBook.Worksheets[0].Cells[16, 11].PutValue(ClassSat.SR16C11);
ScoreWorkBook.Worksheets[0].Cells[16, 12].PutValue(ClassSat.SR16C12);
ScoreWorkBook.Worksheets[0].Cells[16, 13].PutValue(ClassSat.SR16C13);
ScoreWorkBook.Worksheets[0].Cells[16, 14].PutValue(ClassSat.SR16C14);
//二年級學生數總計
ScoreWorkBook.Worksheets[0].Cells[17, 5].PutValue(ClassSat.SR17C5);
ScoreWorkBook.Worksheets[0].Cells[17, 6].PutValue(ClassSat.SR17C6);
ScoreWorkBook.Worksheets[0].Cells[17, 7].PutValue(ClassSat.SR17C7);
ScoreWorkBook.Worksheets[0].Cells[17, 8].PutValue(ClassSat.SR17C8);
ScoreWorkBook.Worksheets[0].Cells[17, 9].PutValue(ClassSat.SR17C9);
ScoreWorkBook.Worksheets[0].Cells[17, 10].PutValue(ClassSat.SR17C10);
ScoreWorkBook.Worksheets[0].Cells[17, 11].PutValue(ClassSat.SR17C11);
ScoreWorkBook.Worksheets[0].Cells[17, 12].PutValue(ClassSat.SR17C12);
ScoreWorkBook.Worksheets[0].Cells[17, 13].PutValue(ClassSat.SR17C13);
ScoreWorkBook.Worksheets[0].Cells[17, 14].PutValue(ClassSat.SR17C14);
ScoreWorkBook.Worksheets[0].Cells[18, 5].PutValue(ClassSat.SR18C5);
ScoreWorkBook.Worksheets[0].Cells[18, 6].PutValue(ClassSat.SR18C6);
ScoreWorkBook.Worksheets[0].Cells[18, 7].PutValue(ClassSat.SR18C7);
ScoreWorkBook.Worksheets[0].Cells[18, 8].PutValue(ClassSat.SR18C8);
ScoreWorkBook.Worksheets[0].Cells[18, 9].PutValue(ClassSat.SR18C9);
ScoreWorkBook.Worksheets[0].Cells[18, 10].PutValue(ClassSat.SR18C10);
ScoreWorkBook.Worksheets[0].Cells[18, 11].PutValue(ClassSat.SR18C11);
ScoreWorkBook.Worksheets[0].Cells[18, 12].PutValue(ClassSat.SR18C12);
ScoreWorkBook.Worksheets[0].Cells[18, 13].PutValue(ClassSat.SR18C13);
ScoreWorkBook.Worksheets[0].Cells[18, 14].PutValue(ClassSat.SR18C14);
//三年級學生數總計
ScoreWorkBook.Worksheets[0].Cells[19, 5].PutValue(ClassSat.SR19C5);
ScoreWorkBook.Worksheets[0].Cells[19, 6].PutValue(ClassSat.SR19C6);
ScoreWorkBook.Worksheets[0].Cells[19, 7].PutValue(ClassSat.SR19C7);
ScoreWorkBook.Worksheets[0].Cells[19, 8].PutValue(ClassSat.SR19C8);
ScoreWorkBook.Worksheets[0].Cells[19, 9].PutValue(ClassSat.SR19C9);
ScoreWorkBook.Worksheets[0].Cells[19, 10].PutValue(ClassSat.SR19C10);
ScoreWorkBook.Worksheets[0].Cells[19, 11].PutValue(ClassSat.SR19C11);
ScoreWorkBook.Worksheets[0].Cells[19, 12].PutValue(ClassSat.SR19C12);
ScoreWorkBook.Worksheets[0].Cells[19, 13].PutValue(ClassSat.SR19C13);
ScoreWorkBook.Worksheets[0].Cells[19, 14].PutValue(ClassSat.SR19C14);
ScoreWorkBook.Worksheets[0].Cells[20, 5].PutValue(ClassSat.SR20C5);
ScoreWorkBook.Worksheets[0].Cells[20, 6].PutValue(ClassSat.SR20C6);
ScoreWorkBook.Worksheets[0].Cells[20, 7].PutValue(ClassSat.SR20C7);
ScoreWorkBook.Worksheets[0].Cells[20, 8].PutValue(ClassSat.SR20C8);
ScoreWorkBook.Worksheets[0].Cells[20, 9].PutValue(ClassSat.SR20C9);
ScoreWorkBook.Worksheets[0].Cells[20, 10].PutValue(ClassSat.SR20C10);
ScoreWorkBook.Worksheets[0].Cells[20, 11].PutValue(ClassSat.SR20C11);
ScoreWorkBook.Worksheets[0].Cells[20, 12].PutValue(ClassSat.SR20C12);
ScoreWorkBook.Worksheets[0].Cells[20, 13].PutValue(ClassSat.SR20C13);
ScoreWorkBook.Worksheets[0].Cells[20, 14].PutValue(ClassSat.SR20C14);
//四年級學生數總計
ScoreWorkBook.Worksheets[0].Cells[21, 5].PutValue(ClassSat.SR21C5);
ScoreWorkBook.Worksheets[0].Cells[21, 6].PutValue(ClassSat.SR21C6);
ScoreWorkBook.Worksheets[0].Cells[21, 7].PutValue(ClassSat.SR21C7);
ScoreWorkBook.Worksheets[0].Cells[21, 8].PutValue(ClassSat.SR21C8);
ScoreWorkBook.Worksheets[0].Cells[21, 9].PutValue(ClassSat.SR21C9);
ScoreWorkBook.Worksheets[0].Cells[21, 10].PutValue(ClassSat.SR21C10);
ScoreWorkBook.Worksheets[0].Cells[21, 11].PutValue(ClassSat.SR21C11);
ScoreWorkBook.Worksheets[0].Cells[21, 12].PutValue(ClassSat.SR21C12);
ScoreWorkBook.Worksheets[0].Cells[21, 13].PutValue(ClassSat.SR21C13);
ScoreWorkBook.Worksheets[0].Cells[21, 14].PutValue(ClassSat.SR21C14);
ScoreWorkBook.Worksheets[0].Cells[22, 5].PutValue(ClassSat.SR22C5);
ScoreWorkBook.Worksheets[0].Cells[22, 6].PutValue(ClassSat.SR22C6);
ScoreWorkBook.Worksheets[0].Cells[22, 7].PutValue(ClassSat.SR22C7);
ScoreWorkBook.Worksheets[0].Cells[22, 8].PutValue(ClassSat.SR22C8);
ScoreWorkBook.Worksheets[0].Cells[22, 9].PutValue(ClassSat.SR22C9);
ScoreWorkBook.Worksheets[0].Cells[22, 10].PutValue(ClassSat.SR22C10);
ScoreWorkBook.Worksheets[0].Cells[22, 11].PutValue(ClassSat.SR22C11);
ScoreWorkBook.Worksheets[0].Cells[22, 12].PutValue(ClassSat.SR22C12);
ScoreWorkBook.Worksheets[0].Cells[22, 13].PutValue(ClassSat.SR22C13);
ScoreWorkBook.Worksheets[0].Cells[22, 14].PutValue(ClassSat.SR22C14);
//延修生學生數總計
ScoreWorkBook.Worksheets[0].Cells[23, 5].PutValue(ClassSat.SR23C5);
ScoreWorkBook.Worksheets[0].Cells[23, 6].PutValue(ClassSat.SR23C6);
ScoreWorkBook.Worksheets[0].Cells[23, 7].PutValue(ClassSat.SR23C7);
ScoreWorkBook.Worksheets[0].Cells[23, 8].PutValue(ClassSat.SR23C8);
ScoreWorkBook.Worksheets[0].Cells[23, 9].PutValue(ClassSat.SR23C9);
ScoreWorkBook.Worksheets[0].Cells[23, 10].PutValue(ClassSat.SR23C10);
ScoreWorkBook.Worksheets[0].Cells[23, 11].PutValue(ClassSat.SR23C11);
ScoreWorkBook.Worksheets[0].Cells[23, 12].PutValue(ClassSat.SR23C12);
ScoreWorkBook.Worksheets[0].Cells[23, 13].PutValue(ClassSat.SR23C13);
ScoreWorkBook.Worksheets[0].Cells[23, 14].PutValue(ClassSat.SR23C14);
ScoreWorkBook.Worksheets[0].Cells[24, 5].PutValue(ClassSat.SR24C5);
ScoreWorkBook.Worksheets[0].Cells[24, 6].PutValue(ClassSat.SR24C6);
ScoreWorkBook.Worksheets[0].Cells[24, 7].PutValue(ClassSat.SR24C7);
ScoreWorkBook.Worksheets[0].Cells[24, 8].PutValue(ClassSat.SR24C8);
ScoreWorkBook.Worksheets[0].Cells[24, 9].PutValue(ClassSat.SR24C9);
ScoreWorkBook.Worksheets[0].Cells[24, 10].PutValue(ClassSat.SR24C10);
ScoreWorkBook.Worksheets[0].Cells[24, 11].PutValue(ClassSat.SR24C11);
ScoreWorkBook.Worksheets[0].Cells[24, 12].PutValue(ClassSat.SR24C12);
ScoreWorkBook.Worksheets[0].Cells[24, 13].PutValue(ClassSat.SR24C13);
ScoreWorkBook.Worksheets[0].Cells[24, 14].PutValue(ClassSat.SR24C14);
#endregion
#region 職業科
ClassStatistics ClassSat1 = new ClassStatistics();
ClassSat1.StartStatistics(1);
ScoreWorkBook.Worksheets[1].Name = "高中職學校班級及學生概況-職業科";
ScoreWorkBook.Worksheets[1].Cells[4, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolCode);
//一年級班級數總計
ScoreWorkBook.Worksheets[1].Cells[7, 5].PutValue(ClassSat1.Level1Count24);
ScoreWorkBook.Worksheets[1].Cells[7, 7].PutValue(ClassSat1.Level1Count34);
ScoreWorkBook.Worksheets[1].Cells[7, 9].PutValue(ClassSat1.Level1Count44);
ScoreWorkBook.Worksheets[1].Cells[7, 11].PutValue(ClassSat1.Level1Count54);
ScoreWorkBook.Worksheets[1].Cells[7, 13].PutValue(ClassSat1.Level1Count55);
//二年級班級數總計
ScoreWorkBook.Worksheets[1].Cells[8, 5].PutValue(ClassSat1.Level2Count24);
ScoreWorkBook.Worksheets[1].Cells[8, 7].PutValue(ClassSat1.Level2Count34);
ScoreWorkBook.Worksheets[1].Cells[8, 9].PutValue(ClassSat1.Level2Count44);
ScoreWorkBook.Worksheets[1].Cells[8, 11].PutValue(ClassSat1.Level2Count54);
ScoreWorkBook.Worksheets[1].Cells[8, 13].PutValue(ClassSat1.Level2Count55);
//三年級班級數總計
ScoreWorkBook.Worksheets[1].Cells[9, 5].PutValue(ClassSat1.Level3Count24);
ScoreWorkBook.Worksheets[1].Cells[9, 7].PutValue(ClassSat1.Level3Count34);
ScoreWorkBook.Worksheets[1].Cells[9, 9].PutValue(ClassSat1.Level3Count44);
ScoreWorkBook.Worksheets[1].Cells[9, 11].PutValue(ClassSat1.Level3Count54);
ScoreWorkBook.Worksheets[1].Cells[9, 13].PutValue(ClassSat1.Level3Count55);
//四年級班級數總計
ScoreWorkBook.Worksheets[1].Cells[10, 5].PutValue(ClassSat1.Level4Count24);
ScoreWorkBook.Worksheets[1].Cells[10, 7].PutValue(ClassSat1.Level4Count34);
ScoreWorkBook.Worksheets[1].Cells[10, 9].PutValue(ClassSat1.Level4Count44);
ScoreWorkBook.Worksheets[1].Cells[10, 11].PutValue(ClassSat1.Level4Count54);
ScoreWorkBook.Worksheets[1].Cells[10, 13].PutValue(ClassSat1.Level4Count55);
//一年級學生數總計
ScoreWorkBook.Worksheets[1].Cells[15, 5].PutValue(ClassSat1.SR15C5);
ScoreWorkBook.Worksheets[1].Cells[15, 6].PutValue(ClassSat1.SR15C6);
ScoreWorkBook.Worksheets[1].Cells[15, 7].PutValue(ClassSat1.SR15C7);
ScoreWorkBook.Worksheets[1].Cells[15, 8].PutValue(ClassSat1.SR15C8);
ScoreWorkBook.Worksheets[1].Cells[15, 9].PutValue(ClassSat1.SR15C9);
ScoreWorkBook.Worksheets[1].Cells[15, 10].PutValue(ClassSat1.SR15C10);
ScoreWorkBook.Worksheets[1].Cells[15, 11].PutValue(ClassSat1.SR15C11);
ScoreWorkBook.Worksheets[1].Cells[15, 12].PutValue(ClassSat1.SR15C12);
ScoreWorkBook.Worksheets[1].Cells[15, 13].PutValue(ClassSat1.SR15C13);
ScoreWorkBook.Worksheets[1].Cells[15, 14].PutValue(ClassSat1.SR15C14);
ScoreWorkBook.Worksheets[1].Cells[16, 5].PutValue(ClassSat1.SR16C5);
ScoreWorkBook.Worksheets[1].Cells[16, 6].PutValue(ClassSat1.SR16C6);
ScoreWorkBook.Worksheets[1].Cells[16, 7].PutValue(ClassSat1.SR16C7);
ScoreWorkBook.Worksheets[1].Cells[16, 8].PutValue(ClassSat1.SR16C8);
ScoreWorkBook.Worksheets[1].Cells[16, 9].PutValue(ClassSat1.SR16C9);
ScoreWorkBook.Worksheets[1].Cells[16, 10].PutValue(ClassSat1.SR16C10);
ScoreWorkBook.Worksheets[1].Cells[16, 11].PutValue(ClassSat1.SR16C11);
ScoreWorkBook.Worksheets[1].Cells[16, 12].PutValue(ClassSat1.SR16C12);
ScoreWorkBook.Worksheets[1].Cells[16, 13].PutValue(ClassSat1.SR16C13);
ScoreWorkBook.Worksheets[1].Cells[16, 14].PutValue(ClassSat1.SR16C14);
//二年級學生數總計
ScoreWorkBook.Worksheets[1].Cells[17, 5].PutValue(ClassSat1.SR17C5);
ScoreWorkBook.Worksheets[1].Cells[17, 6].PutValue(ClassSat1.SR17C6);
ScoreWorkBook.Worksheets[1].Cells[17, 7].PutValue(ClassSat1.SR17C7);
ScoreWorkBook.Worksheets[1].Cells[17, 8].PutValue(ClassSat1.SR17C8);
ScoreWorkBook.Worksheets[1].Cells[17, 9].PutValue(ClassSat1.SR17C9);
ScoreWorkBook.Worksheets[1].Cells[17, 10].PutValue(ClassSat1.SR17C10);
ScoreWorkBook.Worksheets[1].Cells[17, 11].PutValue(ClassSat1.SR17C11);
ScoreWorkBook.Worksheets[1].Cells[17, 12].PutValue(ClassSat1.SR17C12);
ScoreWorkBook.Worksheets[1].Cells[17, 13].PutValue(ClassSat1.SR17C13);
ScoreWorkBook.Worksheets[1].Cells[17, 14].PutValue(ClassSat1.SR17C14);
ScoreWorkBook.Worksheets[1].Cells[18, 5].PutValue(ClassSat1.SR18C5);
ScoreWorkBook.Worksheets[1].Cells[18, 6].PutValue(ClassSat1.SR18C6);
ScoreWorkBook.Worksheets[1].Cells[18, 7].PutValue(ClassSat1.SR18C7);
ScoreWorkBook.Worksheets[1].Cells[18, 8].PutValue(ClassSat1.SR18C8);
ScoreWorkBook.Worksheets[1].Cells[18, 9].PutValue(ClassSat1.SR18C9);
ScoreWorkBook.Worksheets[1].Cells[18, 10].PutValue(ClassSat1.SR18C10);
ScoreWorkBook.Worksheets[1].Cells[18, 11].PutValue(ClassSat1.SR18C11);
ScoreWorkBook.Worksheets[1].Cells[18, 12].PutValue(ClassSat1.SR18C12);
ScoreWorkBook.Worksheets[1].Cells[18, 13].PutValue(ClassSat1.SR18C13);
ScoreWorkBook.Worksheets[1].Cells[18, 14].PutValue(ClassSat1.SR18C14);
//三年級學生數總計
ScoreWorkBook.Worksheets[1].Cells[19, 5].PutValue(ClassSat1.SR19C5);
ScoreWorkBook.Worksheets[1].Cells[19, 6].PutValue(ClassSat1.SR19C6);
ScoreWorkBook.Worksheets[1].Cells[19, 7].PutValue(ClassSat1.SR19C7);
ScoreWorkBook.Worksheets[1].Cells[19, 8].PutValue(ClassSat1.SR19C8);
ScoreWorkBook.Worksheets[1].Cells[19, 9].PutValue(ClassSat1.SR19C9);
ScoreWorkBook.Worksheets[1].Cells[19, 10].PutValue(ClassSat1.SR19C10);
ScoreWorkBook.Worksheets[1].Cells[19, 11].PutValue(ClassSat1.SR19C11);
ScoreWorkBook.Worksheets[1].Cells[19, 12].PutValue(ClassSat1.SR19C12);
ScoreWorkBook.Worksheets[1].Cells[19, 13].PutValue(ClassSat1.SR19C13);
ScoreWorkBook.Worksheets[1].Cells[19, 14].PutValue(ClassSat1.SR19C14);
ScoreWorkBook.Worksheets[1].Cells[20, 5].PutValue(ClassSat1.SR20C5);
ScoreWorkBook.Worksheets[1].Cells[20, 6].PutValue(ClassSat1.SR20C6);
ScoreWorkBook.Worksheets[1].Cells[20, 7].PutValue(ClassSat1.SR20C7);
ScoreWorkBook.Worksheets[1].Cells[20, 8].PutValue(ClassSat1.SR20C8);
ScoreWorkBook.Worksheets[1].Cells[20, 9].PutValue(ClassSat1.SR20C9);
ScoreWorkBook.Worksheets[1].Cells[20, 10].PutValue(ClassSat1.SR20C10);
ScoreWorkBook.Worksheets[1].Cells[20, 11].PutValue(ClassSat1.SR20C11);
ScoreWorkBook.Worksheets[1].Cells[20, 12].PutValue(ClassSat1.SR20C12);
ScoreWorkBook.Worksheets[1].Cells[20, 13].PutValue(ClassSat1.SR20C13);
ScoreWorkBook.Worksheets[1].Cells[20, 14].PutValue(ClassSat1.SR20C14);
//四年級學生數總計
ScoreWorkBook.Worksheets[1].Cells[21, 5].PutValue(ClassSat1.SR21C5);
ScoreWorkBook.Worksheets[1].Cells[21, 6].PutValue(ClassSat1.SR21C6);
ScoreWorkBook.Worksheets[1].Cells[21, 7].PutValue(ClassSat1.SR21C7);
ScoreWorkBook.Worksheets[1].Cells[21, 8].PutValue(ClassSat1.SR21C8);
ScoreWorkBook.Worksheets[1].Cells[21, 9].PutValue(ClassSat1.SR21C9);
ScoreWorkBook.Worksheets[1].Cells[21, 10].PutValue(ClassSat1.SR21C10);
ScoreWorkBook.Worksheets[1].Cells[21, 11].PutValue(ClassSat1.SR21C11);
ScoreWorkBook.Worksheets[1].Cells[21, 12].PutValue(ClassSat1.SR21C12);
ScoreWorkBook.Worksheets[1].Cells[21, 13].PutValue(ClassSat1.SR21C13);
ScoreWorkBook.Worksheets[1].Cells[21, 14].PutValue(ClassSat1.SR21C14);
ScoreWorkBook.Worksheets[1].Cells[22, 5].PutValue(ClassSat1.SR22C5);
ScoreWorkBook.Worksheets[1].Cells[22, 6].PutValue(ClassSat1.SR22C6);
ScoreWorkBook.Worksheets[1].Cells[22, 7].PutValue(ClassSat1.SR22C7);
ScoreWorkBook.Worksheets[1].Cells[22, 8].PutValue(ClassSat1.SR22C8);
ScoreWorkBook.Worksheets[1].Cells[22, 9].PutValue(ClassSat1.SR22C9);
ScoreWorkBook.Worksheets[1].Cells[22, 10].PutValue(ClassSat1.SR22C10);
ScoreWorkBook.Worksheets[1].Cells[22, 11].PutValue(ClassSat1.SR22C11);
ScoreWorkBook.Worksheets[1].Cells[22, 12].PutValue(ClassSat1.SR22C12);
ScoreWorkBook.Worksheets[1].Cells[22, 13].PutValue(ClassSat1.SR22C13);
ScoreWorkBook.Worksheets[1].Cells[22, 14].PutValue(ClassSat1.SR22C14);
//延修生學生數總計
ScoreWorkBook.Worksheets[1].Cells[23, 5].PutValue(ClassSat1.SR23C5);
ScoreWorkBook.Worksheets[1].Cells[23, 6].PutValue(ClassSat1.SR23C6);
ScoreWorkBook.Worksheets[1].Cells[23, 7].PutValue(ClassSat1.SR23C7);
ScoreWorkBook.Worksheets[1].Cells[23, 8].PutValue(ClassSat1.SR23C8);
ScoreWorkBook.Worksheets[1].Cells[23, 9].PutValue(ClassSat1.SR23C9);
ScoreWorkBook.Worksheets[1].Cells[23, 10].PutValue(ClassSat1.SR23C10);
ScoreWorkBook.Worksheets[1].Cells[23, 11].PutValue(ClassSat1.SR23C11);
ScoreWorkBook.Worksheets[1].Cells[23, 12].PutValue(ClassSat1.SR23C12);
ScoreWorkBook.Worksheets[1].Cells[23, 13].PutValue(ClassSat1.SR23C13);
ScoreWorkBook.Worksheets[1].Cells[23, 14].PutValue(ClassSat1.SR23C14);
ScoreWorkBook.Worksheets[1].Cells[24, 5].PutValue(ClassSat1.SR24C5);
ScoreWorkBook.Worksheets[1].Cells[24, 6].PutValue(ClassSat1.SR24C6);
ScoreWorkBook.Worksheets[1].Cells[24, 7].PutValue(ClassSat1.SR24C7);
ScoreWorkBook.Worksheets[1].Cells[24, 8].PutValue(ClassSat1.SR24C8);
ScoreWorkBook.Worksheets[1].Cells[24, 9].PutValue(ClassSat1.SR24C9);
ScoreWorkBook.Worksheets[1].Cells[24, 10].PutValue(ClassSat1.SR24C10);
ScoreWorkBook.Worksheets[1].Cells[24, 11].PutValue(ClassSat1.SR24C11);
ScoreWorkBook.Worksheets[1].Cells[24, 12].PutValue(ClassSat1.SR24C12);
ScoreWorkBook.Worksheets[1].Cells[24, 13].PutValue(ClassSat1.SR24C13);
ScoreWorkBook.Worksheets[1].Cells[24, 14].PutValue(ClassSat1.SR24C14);
#endregion
#region 綜合高中
ClassStatistics ClassSat2 = new ClassStatistics();
ClassSat2.StartStatistics(2);
ScoreWorkBook.Worksheets[2].Name = "高中職學校班級及學生概況-綜合高中";
ScoreWorkBook.Worksheets[2].Cells[4, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolCode);
//一年級班級數總計
ScoreWorkBook.Worksheets[2].Cells[7, 5].PutValue(ClassSat2.Level1Count24);
ScoreWorkBook.Worksheets[2].Cells[7, 7].PutValue(ClassSat2.Level1Count34);
ScoreWorkBook.Worksheets[2].Cells[7, 9].PutValue(ClassSat2.Level1Count44);
ScoreWorkBook.Worksheets[2].Cells[7, 11].PutValue(ClassSat2.Level1Count54);
ScoreWorkBook.Worksheets[2].Cells[7, 13].PutValue(ClassSat2.Level1Count55);
//二年級班級數總計
ScoreWorkBook.Worksheets[2].Cells[8, 5].PutValue(ClassSat2.Level2Count24);
ScoreWorkBook.Worksheets[2].Cells[8, 7].PutValue(ClassSat2.Level2Count34);
ScoreWorkBook.Worksheets[2].Cells[8, 9].PutValue(ClassSat2.Level2Count44);
ScoreWorkBook.Worksheets[2].Cells[8, 11].PutValue(ClassSat2.Level2Count54);
ScoreWorkBook.Worksheets[2].Cells[8, 13].PutValue(ClassSat2.Level2Count55);
//三年級班級數總計
ScoreWorkBook.Worksheets[2].Cells[9, 5].PutValue(ClassSat2.Level3Count24);
ScoreWorkBook.Worksheets[2].Cells[9, 7].PutValue(ClassSat2.Level3Count34);
ScoreWorkBook.Worksheets[2].Cells[9, 9].PutValue(ClassSat2.Level3Count44);
ScoreWorkBook.Worksheets[2].Cells[9, 11].PutValue(ClassSat2.Level3Count54);
ScoreWorkBook.Worksheets[2].Cells[9, 13].PutValue(ClassSat2.Level3Count55);
//四年級班級數總計
ScoreWorkBook.Worksheets[2].Cells[10, 5].PutValue(ClassSat2.Level4Count24);
ScoreWorkBook.Worksheets[2].Cells[10, 7].PutValue(ClassSat2.Level4Count34);
ScoreWorkBook.Worksheets[2].Cells[10, 9].PutValue(ClassSat2.Level4Count44);
ScoreWorkBook.Worksheets[2].Cells[10, 11].PutValue(ClassSat2.Level4Count54);
ScoreWorkBook.Worksheets[2].Cells[10, 13].PutValue(ClassSat2.Level4Count55);
//一年級學生數總計
ScoreWorkBook.Worksheets[2].Cells[15, 5].PutValue(ClassSat2.SR15C5);
ScoreWorkBook.Worksheets[2].Cells[15, 6].PutValue(ClassSat2.SR15C6);
ScoreWorkBook.Worksheets[2].Cells[15, 7].PutValue(ClassSat2.SR15C7);
ScoreWorkBook.Worksheets[2].Cells[15, 8].PutValue(ClassSat2.SR15C8);
ScoreWorkBook.Worksheets[2].Cells[15, 9].PutValue(ClassSat2.SR15C9);
ScoreWorkBook.Worksheets[2].Cells[15, 10].PutValue(ClassSat2.SR15C10);
ScoreWorkBook.Worksheets[2].Cells[15, 11].PutValue(ClassSat2.SR15C11);
ScoreWorkBook.Worksheets[2].Cells[15, 12].PutValue(ClassSat2.SR15C12);
ScoreWorkBook.Worksheets[2].Cells[15, 13].PutValue(ClassSat2.SR15C13);
ScoreWorkBook.Worksheets[2].Cells[15, 14].PutValue(ClassSat2.SR15C14);
ScoreWorkBook.Worksheets[2].Cells[16, 5].PutValue(ClassSat2.SR16C5);
ScoreWorkBook.Worksheets[2].Cells[16, 6].PutValue(ClassSat2.SR16C6);
ScoreWorkBook.Worksheets[2].Cells[16, 7].PutValue(ClassSat2.SR16C7);
ScoreWorkBook.Worksheets[2].Cells[16, 8].PutValue(ClassSat2.SR16C8);
ScoreWorkBook.Worksheets[2].Cells[16, 9].PutValue(ClassSat2.SR16C9);
ScoreWorkBook.Worksheets[2].Cells[16, 10].PutValue(ClassSat2.SR16C10);
ScoreWorkBook.Worksheets[2].Cells[16, 11].PutValue(ClassSat2.SR16C11);
ScoreWorkBook.Worksheets[2].Cells[16, 12].PutValue(ClassSat2.SR16C12);
ScoreWorkBook.Worksheets[2].Cells[16, 13].PutValue(ClassSat2.SR16C13);
ScoreWorkBook.Worksheets[2].Cells[16, 14].PutValue(ClassSat2.SR16C14);
//二年級學生數總計
ScoreWorkBook.Worksheets[2].Cells[17, 5].PutValue(ClassSat2.SR17C5);
ScoreWorkBook.Worksheets[2].Cells[17, 6].PutValue(ClassSat2.SR17C6);
ScoreWorkBook.Worksheets[2].Cells[17, 7].PutValue(ClassSat2.SR17C7);
ScoreWorkBook.Worksheets[2].Cells[17, 8].PutValue(ClassSat2.SR17C8);
ScoreWorkBook.Worksheets[2].Cells[17, 9].PutValue(ClassSat2.SR17C9);
ScoreWorkBook.Worksheets[2].Cells[17, 10].PutValue(ClassSat2.SR17C10);
ScoreWorkBook.Worksheets[2].Cells[17, 11].PutValue(ClassSat2.SR17C11);
ScoreWorkBook.Worksheets[2].Cells[17, 12].PutValue(ClassSat2.SR17C12);
ScoreWorkBook.Worksheets[2].Cells[17, 13].PutValue(ClassSat2.SR17C13);
ScoreWorkBook.Worksheets[2].Cells[17, 14].PutValue(ClassSat2.SR17C14);
ScoreWorkBook.Worksheets[2].Cells[18, 5].PutValue(ClassSat2.SR18C5);
ScoreWorkBook.Worksheets[2].Cells[18, 6].PutValue(ClassSat2.SR18C6);
ScoreWorkBook.Worksheets[2].Cells[18, 7].PutValue(ClassSat2.SR18C7);
ScoreWorkBook.Worksheets[2].Cells[18, 8].PutValue(ClassSat2.SR18C8);
ScoreWorkBook.Worksheets[2].Cells[18, 9].PutValue(ClassSat2.SR18C9);
ScoreWorkBook.Worksheets[2].Cells[18, 10].PutValue(ClassSat2.SR18C10);
ScoreWorkBook.Worksheets[2].Cells[18, 11].PutValue(ClassSat2.SR18C11);
ScoreWorkBook.Worksheets[2].Cells[18, 12].PutValue(ClassSat2.SR18C12);
ScoreWorkBook.Worksheets[2].Cells[18, 13].PutValue(ClassSat2.SR18C13);
ScoreWorkBook.Worksheets[2].Cells[18, 14].PutValue(ClassSat2.SR18C14);
//三年級學生數總計
ScoreWorkBook.Worksheets[2].Cells[19, 5].PutValue(ClassSat2.SR19C5);
ScoreWorkBook.Worksheets[2].Cells[19, 6].PutValue(ClassSat2.SR19C6);
ScoreWorkBook.Worksheets[2].Cells[19, 7].PutValue(ClassSat2.SR19C7);
ScoreWorkBook.Worksheets[2].Cells[19, 8].PutValue(ClassSat2.SR19C8);
ScoreWorkBook.Worksheets[2].Cells[19, 9].PutValue(ClassSat2.SR19C9);
ScoreWorkBook.Worksheets[2].Cells[19, 10].PutValue(ClassSat2.SR19C10);
ScoreWorkBook.Worksheets[2].Cells[19, 11].PutValue(ClassSat2.SR19C11);
ScoreWorkBook.Worksheets[2].Cells[19, 12].PutValue(ClassSat2.SR19C12);
ScoreWorkBook.Worksheets[2].Cells[19, 13].PutValue(ClassSat2.SR19C13);
ScoreWorkBook.Worksheets[2].Cells[19, 14].PutValue(ClassSat2.SR19C14);
ScoreWorkBook.Worksheets[2].Cells[20, 5].PutValue(ClassSat2.SR20C5);
ScoreWorkBook.Worksheets[2].Cells[20, 6].PutValue(ClassSat2.SR20C6);
ScoreWorkBook.Worksheets[2].Cells[20, 7].PutValue(ClassSat2.SR20C7);
ScoreWorkBook.Worksheets[2].Cells[20, 8].PutValue(ClassSat2.SR20C8);
ScoreWorkBook.Worksheets[2].Cells[20, 9].PutValue(ClassSat2.SR20C9);
ScoreWorkBook.Worksheets[2].Cells[20, 10].PutValue(ClassSat2.SR20C10);
ScoreWorkBook.Worksheets[2].Cells[20, 11].PutValue(ClassSat2.SR20C11);
ScoreWorkBook.Worksheets[2].Cells[20, 12].PutValue(ClassSat2.SR20C12);
ScoreWorkBook.Worksheets[2].Cells[20, 13].PutValue(ClassSat2.SR20C13);
ScoreWorkBook.Worksheets[2].Cells[20, 14].PutValue(ClassSat2.SR20C14);
//四年級學生數總計
ScoreWorkBook.Worksheets[2].Cells[21, 5].PutValue(ClassSat2.SR21C5);
ScoreWorkBook.Worksheets[2].Cells[21, 6].PutValue(ClassSat2.SR21C6);
ScoreWorkBook.Worksheets[2].Cells[21, 7].PutValue(ClassSat2.SR21C7);
ScoreWorkBook.Worksheets[2].Cells[21, 8].PutValue(ClassSat2.SR21C8);
ScoreWorkBook.Worksheets[2].Cells[21, 9].PutValue(ClassSat2.SR21C9);
ScoreWorkBook.Worksheets[2].Cells[21, 10].PutValue(ClassSat2.SR21C10);
ScoreWorkBook.Worksheets[2].Cells[21, 11].PutValue(ClassSat2.SR21C11);
ScoreWorkBook.Worksheets[2].Cells[21, 12].PutValue(ClassSat2.SR21C12);
ScoreWorkBook.Worksheets[2].Cells[21, 13].PutValue(ClassSat2.SR21C13);
ScoreWorkBook.Worksheets[2].Cells[21, 14].PutValue(ClassSat2.SR21C14);
ScoreWorkBook.Worksheets[2].Cells[22, 5].PutValue(ClassSat2.SR22C5);
ScoreWorkBook.Worksheets[2].Cells[22, 6].PutValue(ClassSat2.SR22C6);
ScoreWorkBook.Worksheets[2].Cells[22, 7].PutValue(ClassSat2.SR22C7);
ScoreWorkBook.Worksheets[2].Cells[22, 8].PutValue(ClassSat2.SR22C8);
ScoreWorkBook.Worksheets[2].Cells[22, 9].PutValue(ClassSat2.SR22C9);
ScoreWorkBook.Worksheets[2].Cells[22, 10].PutValue(ClassSat2.SR22C10);
ScoreWorkBook.Worksheets[2].Cells[22, 11].PutValue(ClassSat2.SR22C11);
ScoreWorkBook.Worksheets[2].Cells[22, 12].PutValue(ClassSat2.SR22C12);
ScoreWorkBook.Worksheets[2].Cells[22, 13].PutValue(ClassSat2.SR22C13);
ScoreWorkBook.Worksheets[2].Cells[22, 14].PutValue(ClassSat2.SR22C14);
//延修生學生數總計
ScoreWorkBook.Worksheets[2].Cells[23, 5].PutValue(ClassSat2.SR23C5);
ScoreWorkBook.Worksheets[2].Cells[23, 6].PutValue(ClassSat2.SR23C6);
ScoreWorkBook.Worksheets[2].Cells[23, 7].PutValue(ClassSat2.SR23C7);
ScoreWorkBook.Worksheets[2].Cells[23, 8].PutValue(ClassSat2.SR23C8);
ScoreWorkBook.Worksheets[2].Cells[23, 9].PutValue(ClassSat2.SR23C9);
ScoreWorkBook.Worksheets[2].Cells[23, 10].PutValue(ClassSat2.SR23C10);
ScoreWorkBook.Worksheets[2].Cells[23, 11].PutValue(ClassSat2.SR23C11);
ScoreWorkBook.Worksheets[2].Cells[23, 12].PutValue(ClassSat2.SR23C12);
ScoreWorkBook.Worksheets[2].Cells[23, 13].PutValue(ClassSat2.SR23C13);
ScoreWorkBook.Worksheets[2].Cells[23, 14].PutValue(ClassSat2.SR23C14);
ScoreWorkBook.Worksheets[2].Cells[24, 5].PutValue(ClassSat2.SR24C5);
ScoreWorkBook.Worksheets[2].Cells[24, 6].PutValue(ClassSat2.SR24C6);
ScoreWorkBook.Worksheets[2].Cells[24, 7].PutValue(ClassSat2.SR24C7);
ScoreWorkBook.Worksheets[2].Cells[24, 8].PutValue(ClassSat2.SR24C8);
ScoreWorkBook.Worksheets[2].Cells[24, 9].PutValue(ClassSat2.SR24C9);
ScoreWorkBook.Worksheets[2].Cells[24, 10].PutValue(ClassSat2.SR24C10);
ScoreWorkBook.Worksheets[2].Cells[24, 11].PutValue(ClassSat2.SR24C11);
ScoreWorkBook.Worksheets[2].Cells[24, 12].PutValue(ClassSat2.SR24C12);
ScoreWorkBook.Worksheets[2].Cells[24, 13].PutValue(ClassSat2.SR24C13);
ScoreWorkBook.Worksheets[2].Cells[24, 14].PutValue(ClassSat2.SR24C14);
#endregion
try
{
if (Program.ErrorList.Count > 0)
{
int rowIdx = 1;
foreach (string str in Program.ErrorList)
{
ScoreWorkBook.Worksheets[3].Cells[rowIdx, 0].PutValue(str);
rowIdx++;
}
System.Windows.Forms.MessageBox.Show("產生過程有發生問題，請到工作表Error檢視。");
}
if (Program.ErrorList.Count == 0)
{
ScoreWorkBook.Worksheets.RemoveAt(3);
}
ScoreWorkBook.Save(filename);
System.Diagnostics.Process.Start(filename);
}
catch
{
System.Windows.Forms.MessageBox.Show("指定路徑無法存取。", "建立檔案失敗", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
}
};
//權限設定
Catalog permission = RoleAclSource.Instance["教務作業"]["功能按鈕"];
permission.Add(new RibbonFeature("SH.School.SchoolStatistics", "高中職學校班級及學生概況"));
}
public string ImportData()
{
try
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
var currUser = OperatorProvider.Provider.Current();
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = new DataTable();
if (cells.MaxDataRow > 1)
{
dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true);
#region 作业活动类
for (int i = 0; i < dt.Rows.Count; i++)
{
//用于数据验证填报部门
string deptlist = dt.Rows[i]["填报单位"].ToString().Trim();
string controlDept = currUser.DeptName; //管控部门
string controlDeptId = currUser.DeptId; //管控部门
string controlDeptCode = currUser.DeptCode; //管控部门
//岗位（工种）
string Post = dt.Rows[i]["岗位（工种）"].ToString().Trim();
string PostId = string.Empty;
//作业活动
string Name = dt.Rows[i]["作业活动"].ToString().Trim();
//活动步骤
string ActivityStep = dt.Rows[i]["活动步骤"].ToString().Trim();
//常规/非常规
string IsConventional = dt.Rows[i]["常规/非常规"].ToString().Trim();
//其他
string Others = dt.Rows[i]["其他"].ToString().Trim();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(ActivityStep) || string.IsNullOrEmpty(IsConventional) || string.IsNullOrWhiteSpace(Post))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入.";
error++;
continue;
}
var p1 = string.Empty;
var p2 = string.Empty;
bool isSkip = false;
//验证所填部门是否存在
if (!string.IsNullOrWhiteSpace(deptlist))
{
var array = deptlist.Split('/');
for (int j = 0; j < array.Length; j++)
{
if (j == 0)
{
var entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "厂级" && x.FullName == array[j].ToString()).FirstOrDefault();
if (entity == null)
{
entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "部门" && x.FullName == array[j].ToString()).FirstOrDefault();
if (entity == null)
{
entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "承包商" && x.FullName == array[j].ToString()).FirstOrDefault();
if (entity == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入.";
error++;
isSkip = true;
break;
}
else
{
controlDept = entity.FullName;
controlDeptId = entity.DepartmentId;
controlDeptCode = entity.EnCode;
p1 = entity.DepartmentId;
}
}
else
{
controlDept = entity.FullName;
controlDeptId = entity.DepartmentId;
controlDeptCode = entity.EnCode;
p1 = entity.DepartmentId;
}
}
else
{
controlDept = entity.FullName;
controlDeptId = entity.DepartmentId;
controlDeptCode = entity.EnCode;
p1 = entity.DepartmentId;
}
}
else if (j == 1)
{
var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "专业" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault();
if (entity1 == null)
{
entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault();
if (entity1 == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入.";
error++;
isSkip = true;
break;
}
else
{
controlDept = entity1.FullName;
controlDeptId = entity1.DepartmentId;
controlDeptCode = entity1.EnCode;
p2 = entity1.DepartmentId;
}
}
else
{
controlDept = entity1.FullName;
controlDeptId = entity1.DepartmentId;
controlDeptCode = entity1.EnCode;
p2 = entity1.DepartmentId;
}
}
else
{
var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "班组" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p2).FirstOrDefault();
if (entity1 == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入.";
error++;
isSkip = true;
break;
}
else
{
controlDept = entity1.FullName;
controlDeptId = entity1.DepartmentId;
controlDeptCode = entity1.EnCode;
}
}
}
}
if (isSkip)
{
continue;
}
//验证岗位是不是在部门里面
var controldept = departmentBLL.GetEntity(controlDeptId);
RoleEntity re = new RoleEntity();
if (controldept.Nature == "厂级")
{
re = postBLL.GetList().Where(a => (a.FullName == Post && a.OrganizeId == orgId)).FirstOrDefault();
}
else
{
re = postBLL.GetList().Where(a => (a.FullName == Post && a.OrganizeId == orgId && a.DeptId == controlDeptId)).FirstOrDefault();
}
if (re == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行岗位有误,未能导入.";
error++;
continue;
}
else
{
PostId = re.RoleId;
}
//---****判断系统是否已经存在该作业活动、活动步骤的数据*****--
Expression <Func <BaseListingEntity, bool> > condition = t => t.Name == Name && t.ActivityStep == ActivityStep && t.Type == 0 && t.PostId == PostId;
if (baselistingbll.GetList(condition).Count() > 0)
{
falseMessage += "</br>" + "第" + (i + 2) + "行数据已经存在于系统中，无需添加.";
error++;
continue;
}
BaseListingEntity Listingentity = new BaseListingEntity();
Listingentity.Name = Name;
Listingentity.ActivityStep = ActivityStep;
Listingentity.IsConventional = IsConventional == "常规" ? 0 : 1;
Listingentity.Others = Others;
Listingentity.CreateUserDeptCode = string.IsNullOrWhiteSpace(deptlist) ? currUser.DeptCode : controlDeptCode;
Listingentity.ControlsDept = controlDept;
Listingentity.ControlsDeptId = controlDeptId;
Listingentity.ControlsDeptCode = controlDeptCode;
Listingentity.Type = 0;
Listingentity.Post = Post;
Listingentity.PostId = PostId;
condition = t => t.Name == Name && !(t.AreaName == null || t.AreaName.Trim() == "");
var defualt = baselistingbll.GetList(condition).ToList().FirstOrDefault();
Listingentity.AreaName = defualt == null ? "" : defualt.AreaName;
Listingentity.AreaId = defualt == null ? "" : defualt.AreaId;
Listingentity.AreaCode = defualt == null ? "" : defualt.AreaCode;
Listingentity.CreateDate = DateTime.Now.AddSeconds(i);
baselistingbll.SaveForm("", Listingentity);
}
count = dt.Rows.Count;
message = "作业活动类共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage + "</br>";
#endregion
}
else
{
message = "作业活动类没有数据。</br>";
}
error = 0;
falseMessage = "";
cells = wb.Worksheets[1].Cells;
if (cells.MaxDataRow > 1)
{
#region 设备设施类
dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true);
for (int i = 0; i < dt.Rows.Count; i++)
{
//用于数据验证填报部门
string deptlist = dt.Rows[i]["填报单位"].ToString().Trim();
string controlDept = currUser.DeptName; //管控部门
string controlDeptId = currUser.DeptId; //管控部门
string controlDeptCode = currUser.DeptCode; //管控部门
//设备名称
string Name = dt.Rows[i]["设备名称"].ToString().Trim();
//所在地点
string arealist = dt.Rows[i]["所在地点"].ToString().Trim();
//是否特种设备
string IsSpecialEqu = dt.Rows[i]["是否特种设备"].ToString().Trim();
//其他
string Others = dt.Rows[i]["其他"].ToString().Trim();
string AreaName = string.Empty; //所在地点名称
string AreaId = string.Empty; //所在地点Id
string AreaCode = string.Empty; //所在地点Code
//---****值存在空验证*****--
if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(arealist) || string.IsNullOrEmpty(IsSpecialEqu))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入.";
error++;
continue;
}
var p1 = string.Empty;
var p2 = string.Empty;
bool isSkip = false;
//验证所填部门是否存在
if (!string.IsNullOrWhiteSpace(deptlist))
{
var array = deptlist.Split('/');
for (int j = 0; j < array.Length; j++)
{
if (j == 0)
{
var entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "厂级" && x.FullName == array[j].ToString()).FirstOrDefault();
if (entity == null)
{
entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "部门" && x.FullName == array[j].ToString()).FirstOrDefault();
if (entity == null)
{
entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "承包商" && x.FullName == array[j].ToString()).FirstOrDefault();
if (entity == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入.";
error++;
isSkip = true;
break;
}
else
{
controlDept = entity.FullName;
controlDeptId = entity.DepartmentId;
controlDeptCode = entity.EnCode;
p1 = entity.DepartmentId;
}
}
else
{
controlDept = entity.FullName;
controlDeptId = entity.DepartmentId;
controlDeptCode = entity.EnCode;
p1 = entity.DepartmentId;
}
}
else
{
controlDept = entity.FullName;
controlDeptId = entity.DepartmentId;
controlDeptCode = entity.EnCode;
p1 = entity.DepartmentId;
}
}
else if (j == 1)
{
var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "专业" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault();
if (entity1 == null)
{
entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault();
if (entity1 == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入.";
error++;
isSkip = true;
break;
}
else
{
controlDept = entity1.FullName;
controlDeptId = entity1.DepartmentId;
controlDeptCode = entity1.EnCode;
p2 = entity1.DepartmentId;
}
}
else
{
controlDept = entity1.FullName;
controlDeptId = entity1.DepartmentId;
controlDeptCode = entity1.EnCode;
p2 = entity1.DepartmentId;
}
}
else
{
var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "班组" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p2).FirstOrDefault();
if (entity1 == null)
{
falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入.";
error++;
isSkip = true;
break;
}
else
{
controlDept = entity1.FullName;
controlDeptId = entity1.DepartmentId;
controlDeptCode = entity1.EnCode;
}
}
}
}
if (isSkip)
{
continue;
}
//验证所在地点（区域）
var disItem = new DistrictBLL().GetListForCon(x => x.DistrictName == arealist && x.OrganizeId == currUser.OrganizeId).FirstOrDefault();
if (disItem != null)
{
AreaId = disItem.DistrictID;
AreaCode = disItem.DistrictCode;
AreaName = disItem.DistrictName;
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行所在地点信息与系统内置的区域不一致,未能导入.";
error++;
continue;
}
//---****判断系统是否已经存在该设备名称、所在地点的数据*****--
Expression <Func <BaseListingEntity, bool> > condition = t => t.Name == Name && t.AreaId == AreaId && t.Type == 1;
if (baselistingbll.GetList(condition).Count() > 0)
{
falseMessage += "</br>" + "第" + (i + 2) + "行数据已经存在于系统中，无需添加.";
error++;
continue;
}
BaseListingEntity Listingentity = new BaseListingEntity();
Listingentity.Name = Name;
Listingentity.AreaCode = AreaCode;
Listingentity.AreaId = AreaId;
Listingentity.AreaName = AreaName;
Listingentity.IsSpecialEqu = IsSpecialEqu == "是" ? 0 : 1;
Listingentity.Others = Others;
Listingentity.CreateUserDeptCode = string.IsNullOrWhiteSpace(deptlist) ? currUser.DeptCode : controlDeptCode;
Listingentity.ControlsDept = controlDept;
Listingentity.ControlsDeptId = controlDeptId;
Listingentity.ControlsDeptCode = controlDeptCode;
Listingentity.Type = 1;
Listingentity.CreateDate = DateTime.Now.AddSeconds(i);
baselistingbll.SaveForm("", Listingentity);
}
count = dt.Rows.Count;
message += "设备设施类共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
#endregion
}
else
{
message += "设备设施类没有数据。</br>";
}
}
return(message);
}
catch (Exception ex)
{
return(ex.ToString());
}
}
/// <summary>
/// 预知风险训练库导出详情
/// </summary>
/// <param name="keyValue"></param>
/// <returns></returns>
public ActionResult ExportData(string queryJson, string fileName)
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
//string path = "~/Resource/Temp";
string fName = "作业安全分析库" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/危险预知训练数据库导入模版.xlsx"));
var queryParam = queryJson.ToJObject();
//var riskType = queryParam["riskType"].ToString();
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 10000000;
pagination.sidx = "t.createdate,t.id";
pagination.sord = "desc";
pagination.p_tablename = "bis_risktrainlib t left join bis_risktrainlibdetail d on d.workid = t.id";
pagination.p_kid = "t.id";
pagination.p_fields = @"t.worktask,t.risklevel,t.worktype,t.workpost,t.workdes,
t.resources,t.workarea,d.process,d.atrisk,d.controls";
pagination.conditionJson = "1=1";
if (!user.IsSystem)
{
//根据当前用户对模块的权限获取记录
string where = new AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value, "t.createuserdeptcode", "t.createuserorgcode");
if (!string.IsNullOrEmpty(where))
{
pagination.conditionJson += " and " + where;
}
}
var data = risktrainlibbll.GetPageListJson(pagination, queryJson);
var cells = wb.Worksheets[0].Cells;
int Colnum = data.Columns.Count;
int Rownum = data.Rows.Count;
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum - 1; k++)
{
if (k == 0)
{
cells[2 + i, k].PutValue(i + 1);
}
else
{
cells[2 + i, k].PutValue(data.Rows[i][k].ToString());
}
}
}
int q = 0;
int RowOrder = 0;
int m = 1;
for (int i = 0; i < data.Rows.Count; i = q)
{
RowOrder = data.Select(string.Format("id='{0}'", data.Rows[i]["id"].ToString())).ToList().Count;
cells.Merge(2 + q, 0, RowOrder, 1);
cells.Merge(2 + q, 1, RowOrder, 1);
cells.Merge(2 + q, 2, RowOrder, 1);
cells.Merge(2 + q, 3, RowOrder, 1);
cells.Merge(2 + q, 4, RowOrder, 1);
cells.Merge(2 + q, 5, RowOrder, 1);
cells.Merge(2 + q, 6, RowOrder, 1);
cells.Merge(2 + q, 7, RowOrder, 1);
cells[2 + q, 0].PutValue(m);
m++;
q += RowOrder;
}
HttpResponse resp = System.Web.HttpContext.Current.Response;
System.Threading.Thread.Sleep(400);
wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
return(Success("导出成功。", fName));
}
public string ImportTrainLib()
{
try
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
string orgId = OperatorProvider.Provider.Current().OrganizeId;
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName), file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")?Aspose.Cells.FileFormatType.Excel2007Xlsx:Aspose.Cells.FileFormatType.Excel2003);
var sheet = wb.Worksheets[0];
if (sheet.Cells[1, 1].StringValue != "工作任务" || sheet.Cells[1, 2].StringValue != "风险等级" || sheet.Cells[1, 3].StringValue != "作业类型" ||
sheet.Cells[1, 4].StringValue != "作业岗位" || sheet.Cells[1, 5].StringValue != "任务描述" || sheet.Cells[1, 6].StringValue != "资源准备" ||
sheet.Cells[1, 7].StringValue != "作业区域" || sheet.Cells[1, 8].StringValue != "工序" || sheet.Cells[1, 9].StringValue != "潜在危险" || sheet.Cells[1, 10].StringValue != "防范措施")
{
return(message);
}
var RiskLibList = new List <RisktrainlibEntity>();
var RiskDetailList = new List <RisktrainlibdetailEntity>();
var date = DateTime.Now;
for (int i = 2; i <= sheet.Cells.MaxDataRow; i++)
{
var entity = new RisktrainlibEntity();
entity.Create();
entity.WorkTask = sheet.Cells[i, 1].StringValue;
entity.RiskLevel = sheet.Cells[i, 2].StringValue;
switch (entity.RiskLevel)
{
case "重大风险":
entity.RiskLevelVal = "1";
break;
case "较大风险":
entity.RiskLevelVal = "2";
break;
case "一般风险":
entity.RiskLevelVal = "3";
break;
case "低风险":
entity.RiskLevelVal = "4";
break;
default:
break;
}
entity.WorkType = sheet.Cells[i, 3].StringValue;
entity.WorkPost = sheet.Cells[i, 4].StringValue;
entity.WorkDes = sheet.Cells[i, 5].StringValue;
entity.Resources = sheet.Cells[i, 6].StringValue;
entity.WorkArea = sheet.Cells[i, 7].StringValue;
entity.DataSources = "2";
if (string.IsNullOrEmpty(sheet.Cells[i, 1].StringValue))
{
entity.ID = RiskLibList[i - 1 - 2].ID;
}
RiskLibList.Add(entity);
}
for (int i = 2; i <= sheet.Cells.MaxDataRow; i++)
{
var dentity = new RisktrainlibdetailEntity();
if (sheet.Cells[i, 8].StringValue.Length > 1000)
{
falseMessage += "</br>" + "第" + (i + 1) + "行工序字符长度超长,未能导入.";
error++;
continue;
}
if (sheet.Cells[i, 9].StringValue.Length > 1000)
{
falseMessage += "</br>" + "第" + (i + 1) + "行存在风险字符长度超长,未能导入.";
error++;
continue;
}
if (sheet.Cells[i, 10].StringValue.Length > 1000)
{
falseMessage += "</br>" + "第" + (i + 1) + "行管理措施字符长度超长,未能导入.";
error++;
continue;
}
dentity.Process = sheet.Cells[i, 8].StringValue;
dentity.AtRisk = sheet.Cells[i, 9].StringValue;
dentity.Controls = sheet.Cells[i, 10].StringValue;
dentity.Create();
dentity.WorkId = RiskLibList[i - 2].ID;
RiskDetailList.Add(dentity);
}
RiskLibList = RiskLibList.Where(x => x.WorkTask != "").ToList();
for (int i = 0; i < RiskLibList.Count; i++)
{
if (!string.IsNullOrWhiteSpace(RiskLibList[i].WorkType))
{
var data = new DataItemDetailBLL().GetDataItemListByItemCode("'StatisticsType'");
string worktype = string.Empty;
var list = RiskLibList[i].WorkType.Split(',');
for (int k = 0; k < list.Length; k++)
{
var entity = data.Where(x => x.ItemName == list[k].Trim()).FirstOrDefault();
if (entity != null)
{
if (string.IsNullOrWhiteSpace(worktype))
{
RiskLibList[i].WorkTypeCode += entity.ItemValue + ",";
worktype += entity.ItemName + ",";
}
else
{
if (!worktype.Contains(entity.ItemName))
{
RiskLibList[i].WorkTypeCode += entity.ItemValue + ",";
worktype += entity.ItemName + ",";
}
}
}
//if (entity != null)
//{
// RiskLibList[i].WorkTypeCode += entity.ItemValue + ",";
//}
//else
//{
// RiskLibList[i].WorkType.Replace(list[k], "");
//}
}
if (!string.IsNullOrWhiteSpace(RiskLibList[i].WorkTypeCode))
{
RiskLibList[i].WorkTypeCode = RiskLibList[i].WorkTypeCode.Substring(0, RiskLibList[i].WorkTypeCode.Length - 1);
RiskLibList[i].WorkType = worktype.Substring(0, worktype.Length - 1);
}
}
if (string.IsNullOrWhiteSpace(RiskLibList[i].WorkArea))
{
//falseMessage += "</br>" + "第" + (i+1) + "行区域为空,未能导入.";
//error++;
//continue;
}
else
{
DistrictEntity disEntity = districtBLL.GetDistrict(orgId, RiskLibList[i].WorkArea);
if (disEntity == null)
{
//电厂没有该区域则不赋值
RiskLibList[i].WorkArea = "";
}
else
{
RiskLibList[i].WorkAreaId = disEntity.DistrictID;
}
}
}
risktrainlibbll.InsertImportData(RiskLibList, RiskDetailList);
//risktrainlibbll.InsertRiskTrainLib(RiskLibList);
//risktrainlibdetailbll.InsertRiskTrainDetailLib(RiskDetailList);
count = RiskDetailList.Count;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
//DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow, cells.MaxColumn + 1, true);
}
return(message);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//return null;
}
/// <summary>
/// 风险清单导出(重大风险)
/// </summary>
/// <param name="queryJson"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public ActionResult ExportExcel(string queryJson, string fileName)
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
//string path = "~/Resource/Temp";
string fName = "安全风险清单" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
var IsGdxy = new DataItemDetailBLL().GetDataItemListByItemCode("'VManager'").ToList();
if (IsGdxy.Count > 0)
{
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/风险措施库导出模板.xls"));
}
else
{
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/风险措施库通用导出模板.xls"));
}
var queryParam = queryJson.ToJObject();
var riskType = queryParam["riskType"].ToString();
var IndexState = queryParam["IndexState"].ToString();
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search");
if (string.IsNullOrWhiteSpace(riskType))
{
DataSet ds = new DataSet();
List <string> typeList = new List <string>()
{
"作业", "设备", "区域", "管理", "岗位", "工器具及危化品"
};
for (int i = 0; i < typeList.Count; i++)
{
DataTable exportTable = new DataTable();
exportTable = riskbll.GetPageExportList(queryJson, typeList[i], authType, IndexState);
exportTable.TableName = i.ToString();
ds.Tables.Add(exportTable);
for (int j = 0; j < wb.Worksheets.Count; j++)
{
if (wb.Worksheets[j].Name == typeList[i])
{
wb.Worksheets[j].Cells.ImportDataTable(exportTable, false, 2, 0);
}
}
}
//AsposeExcelHelper.ExecuteResultX(ds, path, typeList, fName);
}
else
{
DataTable exportTable = riskbll.GetPageExportList(queryJson, riskType, authType, IndexState);
for (int j = 0; j < wb.Worksheets.Count; j++)
{
if (wb.Worksheets[j].Name == riskType)
{
wb.Worksheets[j].Cells.ImportDataTable(exportTable, false, 2, 0);
}
}
}
HttpResponse resp = System.Web.HttpContext.Current.Response;
System.Threading.Thread.Sleep(400);
wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
//wb.Save(Server.UrlEncode(fName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功。", fName));
}
public ActionResult ExportQD(string keyValue)
{
//获取会议基本信息
ConferenceEntity cr = conferencebll.GetEntity(keyValue);
////获取会议签到人员信息
//List<Object> data = conferenceuserbll.GetSignTable(keyValue);
////签到
//List<Object> dataQD = data[0] as List<Object>;
////未签到
//List<Object> dataWQD = data[1] as List<Object>;
////请假
//List<Object> dataQ = data[2] as List<Object>;
string path = Request.PhysicalApplicationPath;
int index = 0;
string fileName = HttpUtility.UrlDecode("安全会议签到") + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //定义导出文件名，一定记得编码
try
{
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
wk.Open(Server.MapPath("~/Resource/ExcelTemplate/安全会议签到表模板.xlsx")); //加载模板
Aspose.Cells.Worksheet sheet = wk.Worksheets[0];
Aspose.Cells.Style style = wk.Styles[wk.Styles.Add()];
style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //文字居中
style.IsTextWrapped = true; //自动换行
style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
string sql = string.Format(" select t.USERNAME,o.DEPTNAME,t.PHOTOURL,t.issign,t.REVIEWSTATE from BIS_ConferenceUser t left join v_userinfo o on t.userid=o.userid where t.ConferenceID='{0}'", keyValue);
DataTable dt = specialequipmentbll.SelectData(sql);
//参会人员集合
var listC = dt;
//签到人员集合
var SdUser = listC.Select(" issign='0'");
//未签到人员集合
var YdUser = listC.Select(" issign<>'0' and REVIEWSTATE<>'2'");
//请假人数
var QUser = listC.Select(" REVIEWSTATE='2'");
sheet.Cells[1, 2].PutValue(cr.ConferenceName); //会议名称
sheet.Cells[1, 4].PutValue(cr.Locale); //会议地点
sheet.Cells[2, 2].PutValue(cr.CompereDept); //召开部门
sheet.Cells[2, 4].PutValue(cr.Compere); //主持人
sheet.Cells[3, 2].PutValue(cr.Content); //主要议题
if (SdUser.Length == 0)
{
sheet.Cells[5, 1].PutValue("无");
}
else
{
sheet.Cells[5 + SdUser.Length, 0].PutValue("已签到人数");
sheet.Cells.Merge(5, 0, SdUser.Length, 1);
foreach (DataRow item in SdUser)
{
index++;
sheet.Cells[4 + index, 1].PutValue(index);
sheet.Cells[4 + index, 2].PutValue(item[0].ToString()); //参会人员
sheet.Cells[4 + index, 4].PutValue(item[1].ToString()); //所属部门
if (!string.IsNullOrEmpty(item[2].ToString()))
{
string[] u = item[2].ToString().Split('/');
string url = path + "/" + u[1] + "/" + u[2] + "/" + u[3] + "/" + u[4] + "/" + u[5];//签名
FileStream fs = new FileStream(url, FileMode.Open);
int imgIndex = sheet.Pictures.Add(4 + index, 3, fs, 120, 30);
fs.Close();
sheet.Cells.SetColumnWidth(5, 20);
sheet.Cells.SetRowHeight(4 + index, 44);
sheet.Pictures[imgIndex].Width = 139;
sheet.Pictures[imgIndex].Height = 50;
sheet.Pictures[imgIndex].Top = 3;
sheet.Pictures[imgIndex].Left = 6;
}
}
}
int num = SdUser.Length == 0 ? 1 : SdUser.Length;
int s = SdUser.Length == 0 ? 1 : SdUser.Length;
if (index > 0)
{
s = 0;
}
if (YdUser.Length == 0)
{
sheet.Cells[5 + num, 0].PutValue("未签到人数");
sheet.Cells[5 + num, 1].PutValue("无");
}
else
{
sheet.Cells[5 + num, 0].PutValue("未签到人数");
sheet.Cells.Merge(5 + num, 0, YdUser.Length, 1);
foreach (DataRow item in YdUser)
{
index++;
sheet.Cells[4 + s + index, 1].PutValue(index);
sheet.Cells[4 + s + index, 2].PutValue(item[0].ToString());
sheet.Cells[4 + s + index, 4].PutValue(item[1].ToString());
sheet.Cells[4 + s + index, 3].PutValue("");
}
}
s = YdUser.Length == 0 ? 1 : YdUser.Length;
int qj = s + SdUser.Length == 0 ? 1 : s + SdUser.Length;
qj = SdUser.Length == 0 ? qj + 1 : qj;
int qjNum = 0;
if (QUser.Length > 0)
{
sheet.Cells[5 + qj, 0].PutValue("请假人数");
sheet.Cells.Merge(5 + qj, 0, QUser.Length, 1);
foreach (DataRow dr in QUser)
{
sheet.Cells[5 + qj + qjNum, 1].PutValue(index++ + 1);
sheet.Cells[5 + qj + qjNum, 2].PutValue(dr[0].ToString());
sheet.Cells[5 + qj + qjNum, 4].PutValue(dr[1].ToString());
sheet.Cells[5 + qj + qjNum, 3].PutValue("");
qjNum++;
}
}
else
{
qjNum += 1;
sheet.Cells[5 + qj, 0].PutValue("请假人数");
sheet.Cells[5 + qj, 1].PutValue("无");
}
Aspose.Cells.Range r = sheet.Cells.CreateRange(5, 0, qj + qjNum, 5);
r.Style = style;
HttpResponse resp = System.Web.HttpContext.Current.Response;
wk.Save(HttpUtility.UrlEncode(fileName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInExcel, resp);
return(ToJsonResult("0"));
}
catch (Exception ex)
{
string env = Config.GetValue("Environment");
string msg = env == "dev" ? ex.Message : "对不起,系统出错了";
return(ToJsonResult("" + msg + ""));
}
}
public string ImportData(string OrganizeId, string orgName)
{
try
{
int error = 0;
string orgId = OperatorProvider.Provider.Current().OrganizeId;
if (!string.IsNullOrEmpty(OrganizeId))
{
orgId = OrganizeId;
}
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true);
//DataTable dt = ExcelHelper.ExcelImport(Server.MapPath("~/Resource/temp/" + fileName));
int order = 1;
var deptBll = new DepartmentBLL();
for (int i = 1; i < dt.Rows.Count; i++)
{
order = i;
//区域名称
string areaName = dt.Rows[i][0].ToString().Trim();
if (string.IsNullOrWhiteSpace(areaName))
{
falseMessage += "</br>" + "第" + (i + 2) + "行区域名称为空,未能导入.";
error++;
continue;
}
//上级区域名称
string parentName = dt.Rows[i][1].ToString().Trim();
//管控部门名称
string deptName = dt.Rows[i][2].ToString().Trim();
if (string.IsNullOrWhiteSpace(deptName))
{
falseMessage += "</br>" + "第" + (i + 2) + "行管控部门名称为空,未能导入.";
error++;
continue;
}
//区域负责人
string dutyUser = dt.Rows[i][3].ToString().Trim();
if (string.IsNullOrWhiteSpace(dutyUser))
{
falseMessage += "</br>" + "第" + (i + 2) + "行区域负责人名称为空,未能导入.";
error++;
continue;
}
DistrictEntity area = new DistrictEntity();
area.DistrictName = areaName;
area.ChargeDept = deptName;
area.DisreictChargePerson = dutyUser;
area.OrganizeId = OrganizeId;
area.BelongCompany = orgName;
//验证上级区域名称并获取相关信息
if (!string.IsNullOrWhiteSpace(parentName))
{
DataTable dtArea = deptBll.GetDataTable(string.Format("select DistrictId from BIS_DISTRICT where DistrictName='{0}' and organizeid='{1}'", parentName, OrganizeId));
if (dtArea.Rows.Count > 0)
{
area.ParentID = dtArea.Rows[0][0].ToString();
dtArea = deptBll.GetDataTable(string.Format("select count(1) from BIS_DISTRICT where DistrictName='{0}' and organizeid='{1}' and parentid='{2}'", areaName, OrganizeId, area.ParentID));
if (dtArea.Rows[0][0].ToString() != "0")
{
falseMessage += "</br>" + "第" + (i + 2) + "行区域信息已经存在,未能导入.已存在区域信息：" + areaName;
error++;
continue;
}
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行上级区域名称与系统区域不匹配,未能导入.错误的区域信息：" + parentName;
error++;
continue;
}
}
else
{
area.ParentID = "0";
DataTable dtArea = deptBll.GetDataTable(string.Format("select count(1) from BIS_DISTRICT where DistrictName='{0}' and organizeid='{1}' and parentid='0'", areaName, OrganizeId));
if (dtArea.Rows[0][0].ToString() != "0")
{
falseMessage += "</br>" + "第" + (i + 2) + "行区域信息已经存在,未能导入.错误的区域信息：" + areaName;
error++;
continue;
}
}
//验证管控部门并获取相关信息
if (!string.IsNullOrWhiteSpace(deptName))
{
DataTable dtDept = deptBll.GetDataTable(string.Format("select encode,departmentid,MANAGER,MANAGERID,OuterPhone from BASE_DEPARTMENT where fullname='{0}' and organizeid='{1}'", deptName, OrganizeId));
if (dtDept.Rows.Count > 0)
{
area.ChargeDeptCode = dtDept.Rows[0][0].ToString();
area.ChargeDeptID = dtDept.Rows[0][1].ToString();
area.DeptChargePerson = dtDept.Rows[0][2].ToString();
area.DeptChargePersonID = dtDept.Rows[0][3].ToString();
area.LinkTel = dtDept.Rows[0][4].ToString();
}
else
{
falseMessage += "</br>" + "第" + (i + 2) + "行管控部门名称与系统部门名称不匹配,错误的部门信息：" + deptName;
error++;
continue;
}
}
//验证区域负责人并获取相关信息
if (!string.IsNullOrWhiteSpace(dutyUser))
{
DataTable dtUsers = deptBll.GetDataTable(string.Format("select account from BASE_user where organizeid='{1}' and realname in('{0}') ", dutyUser.Replace(",", "','"), OrganizeId));
StringBuilder sb = new StringBuilder();
foreach (DataRow dr in dtUsers.Rows)
{
sb.AppendFormat("{0},", dr[0].ToString());
}
string users = sb.ToString().TrimEnd(',');
area.DisreictChargePersonID = sb.ToString().TrimEnd(',');
if (dutyUser.Split(',').Length != users.Split(',').Length)
{
falseMessage += "</br>" + "第" + (i + 2) + "行区域负责人信息与系统中人员不匹配，错误信息：" + dutyUser;
error++;
}
}
try
{
bis_districtbll.SaveForm("", area);
}
catch (Exception ex)
{
falseMessage += "</br>" + "第" + (i + 2) + "行数据插入出现异常，错误信息：" + Newtonsoft.Json.JsonConvert.SerializeObject(ex);
error++;
}
}
count = dt.Rows.Count - 1;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
catch (Exception ex)
{
return(Newtonsoft.Json.JsonConvert.SerializeObject(ex));
}
}
public static DataTable importRosterExcel2(string Filename, int iColNumber, int iRowNumber)
{
string mes1 = "";
string mes2 = "";
string mstr_FileName = Filename;
string mstr_PathFileName = mstr_FileName;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(mstr_FileName);
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
CultureInfo ci = CultureInfo.CreateSpecificCulture("en-US");
string DTformat = ci.DateTimeFormat.ShortDatePattern;
//MessageBox.Show(ci.DateTimeFormat.ShortDatePattern);
DataTable dt = new DataTable();
dt.TableName = ws.Name;
int iColEnd = GetEndCol(ws);
int iRowEnd = GetEndRow(ws, iRowNumber + 4);
try
{
for (int j = 0; j < iColEnd; j++)
{
if (ws.Cells[iRowNumber, j].Value == null)
{
dt.Columns.Add(new DataColumn());
}
else
{
object cName = ws.Cells[iRowNumber - 1, j].Value;
object value = ws.Cells[iRowNumber, j].Value;
if (value.GetType() == typeof(DateTime))
{
dt.Columns.Add(new DataColumn(cName.ToString().Replace("\r\n", "").Replace("\n", "").Trim(), typeof(DateTime)));
}
else if (value.GetType() == typeof(double))
{
dt.Columns.Add(new DataColumn(cName.ToString().Replace("\r\n", "").Replace("\n", "").Trim(), typeof(decimal)));
}
else
{
dt.Columns.Add(new DataColumn(cName.ToString().Replace("\r\n", "").Replace("\n", "").Trim(), typeof(string)));
}
}
}
int i = iRowNumber + 3;
for (; i < iRowEnd; i++)
{
DataRow dr = dt.NewRow();
for (int j = 0; j < iColEnd; j++)
{
object value = ws.Cells[i, j].Value;
if (value == null)
{
dr[j] = DBNull.Value;
}
else
{
string[] s = value.ToString().Trim().Split(new string[] { " _ " }, StringSplitOptions.None);
string str = value.ToString().Contains(" _ ") ? s[s.Length - 1] : value.ToString().Trim();
mes1 = dt.Columns[j].ColumnName;
mes2 = (i + 1).ToString();
if (dt.Columns[j].DataType == typeof(DateTime))
{
try
{
dr[j] = DateTime.ParseExact(value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString(DTformat, CultureInfo.InvariantCulture);
}
catch (Exception)
{
try
{
DateTime creationDate;
string[] sDate = value.ToString().Split('/');
string sDateTime = sDate[1] + '/' + sDate[0] + '/' + sDate[2];
creationDate = Convert.ToDateTime(sDateTime);
dr[j] = creationDate;
}
catch (Exception ex)
{
dr[j] = Convert.ToDateTime(value.ToString());
}
}
//DateTime creationDate;
//if (DateTime.TryParseExact(value.ToString(), DTformat,
// CultureInfo.InvariantCulture, DateTimeStyles.None,
// out creationDate))
//{
// dr[j] = creationDate;
//}
//else
//{
// string[] sDate = value.ToString().Split('/');
// string sDateTime = sDate[1] + '/' + sDate[0] + '/' + sDate[2];
// creationDate = Convert.ToDateTime(sDateTime);
// dr[j] = creationDate;
//}
//dr[j] = string.Format("{0:" + DTformat + "}", value);
//dr[j] = DateTime.ParseExact(value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString(DTformat, CultureInfo.InvariantCulture);
//DateTime.ParseExact(value.ToString(), DTformat, null);
}
else if (dt.Columns[j].DataType == typeof(decimal))
{
dr[j] = Convert.ToInt32(str);
}
else
{
dr[j] = str;
}
}
}
dt.Rows.Add(dr);
}
return(dt);
}
catch (Exception ex)
{
ex.HelpLink = "Sai ở cột [" + mes1 + "], dòng [" + mes2 + "]";
//MessageBox.Show(ex.Message);
//Util.Log.Instance.WriteExceptionLog(ex, "importExcel");
throw (ex);
}
return(null);
}
private DataTable BuildDataTableFromFileExcel(System.IO.Stream dataStream)
{
var infoTb = BuildNewtable();
try
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Cells.lic");
Aspose.Cells.Workbook document = new Aspose.Cells.Workbook();
document.Open(dataStream);
if (document.Worksheets[0].Cells.Rows.Count <= 1) return infoTb;
for (var i = 1; i < document.Worksheets[0].Cells.Rows.Count; i++)
{
if (string.IsNullOrEmpty(document.Worksheets[0].Cells[i, 0].Value.ToString())) continue;
var row = infoTb.NewRow();
row["WorkingAccId"] = document.Worksheets[0].Cells[i, 3].Value;
row["PaymentMethod"] = document.Worksheets[0].Cells[i, 0].Value;
row["CreditAmount"] = String.Format(CultureInfo.InvariantCulture,
"{0:0,0}", document.Worksheets[0].Cells[i, 5].Value);
row["CreditAccount"] = string.Format("{0}-{1}-{2}", document.Worksheets[0].Cells[i, 4].Value, document.Worksheets[0].Cells[i, 3].Value, document.Worksheets[0].Cells[i, 2].Value);
row["Exist"] = string.Empty;
infoTb.Rows.Add(row);
}
UpdateDataInfo(infoTb);
}
catch (Exception ex)
{
labelNoResults.Text = "Wrong Selected Company Payment file";
}
return infoTb;
}
private void btnXuatDanhSach_Click(object sender, EventArgs e)
{
if (grvSinhVien.DataRowCount > 0)
{
CreateWaitDialog("Đang xuất dữ liệu, xin vui lòng chờ.", "Xuất dữ liệu");
try
{
MemoryStream ms = new MemoryStream(Resources.DanhSachSinhVien);
var book = new Aspose.Cells.Workbook();
book.Open(ms);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;
var fields = new string[] { "MaSinhVien", "HoVaTen", "NgaySinh", "NoiSinh", "ThuongTru", "KhenThuong", "KyLuat" };
cells["A2"].PutValue("Danh sách sinh viên " + pDM_LopInfo.TenLop + (cboDanhSachHienThi.SelectedIndex == 1 ? " (đã bị xóa tên)" : ""));
var rIndex = 5;
for (var i = 0; i < dtSinhVien.Rows.Count; i++)
{
if (i < dtSinhVien.Rows.Count - 2)
{
cells.InsertRow(rIndex + i + 1);
}
cells["A" + (rIndex + i)].PutValue(i + 1);
for (var j = 0; j < fields.Length; j++)
{
cells[rIndex + i - 1, j + 1].PutValue(dtSinhVien.Rows[i][fields[j]]);
}
}
SaveFileDialog sDlg = new SaveFileDialog();
sDlg.Title = "Lưu file kết xuất";
sDlg.InitialDirectory = Application.StartupPath;
sDlg.FileName = "DanhSachSinhVien_" + DateTime.Now.ToString("yyyyMMddhhmm") + ".xls";
if (sDlg.ShowDialog() != DialogResult.Cancel)
{
book.Save(sDlg.FileName, Aspose.Cells.FileFormatType.Excel2003);
Process.Start(sDlg.FileName);
}
}
catch (Exception ex)
{
ThongBaoLoi("Có lỗi khi xuất dữ liệu. " + ex.Message);
}
finally
{
CloseWaitDialog();
}
}
else
{
ThongBao("Chưa có danh sách sinh viên.");
}
}
public string ImportLabor()
{
var currUser = OperatorProvider.Provider.Current();
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
if (cells.MaxDataRow == 0)
{
message = "没有数据,请选择先填写模板在进行导入!";
return(message);
}
DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true);
int order = 1;
IList <LaborprotectionEntity> LaborList = new List <LaborprotectionEntity>();
//先获取到原始的一个编号
string no = laborprotectionbll.GetNo();
int ysno = Convert.ToInt32(no);
for (int i = 0; i < dt.Rows.Count; i++)
{
order = i;
string Name = dt.Rows[i]["劳动防护用品名称"].ToString();
string Unit = dt.Rows[i]["劳动防护用品单位"].ToString();
string Model = dt.Rows[i]["型号"].ToString();
string Type = dt.Rows[i]["类型"].ToString();
string TimeNum = dt.Rows[i]["使用期限时间"].ToString();
string TimeType = dt.Rows[i]["使用期限单位"].ToString();
string Note = dt.Rows[i]["使用说明"].ToString().Trim();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(Unit))
{
falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入.";
error++;
continue;
}
LaborprotectionEntity ue = new LaborprotectionEntity();
ue.Name = Name;
ue.Unit = Unit;
ue.Model = Model;
ue.Type = Type;
if (TimeNum != "")
{
ue.TimeNum = Convert.ToInt32(TimeNum);//工号
}
ue.TimeType = TimeType;
ue.Note = Note;
ue.No = ysno.ToString();
//下一条编号增加
ysno++;
ue.LaborOperationUserName = currUser.UserName;
ue.LaborOperationTime = DateTime.Now;
try
{
laborprotectionbll.SaveForm("", ue);
}
catch
{
error++;
}
}
count = dt.Rows.Count;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
public string ImportSuppliesData()
{
int error = 0;
string message = "请选择格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
count = 0;
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName), file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx") ? Aspose.Cells.FileFormatType.Excel2007Xlsx : Aspose.Cells.FileFormatType.Excel2003);
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
//判断表头是否正确,以免使用错误模板
var sheet = wb.Worksheets[0];
if (sheet.Cells[2, 0].StringValue != "物资类型" || sheet.Cells[2, 1].StringValue != "物资名称" || sheet.Cells[2, 2].StringValue != "规格型号" || sheet.Cells[2, 3].StringValue != "数量" ||
sheet.Cells[2, 4].StringValue != "单位" || sheet.Cells[2, 5].StringValue != "存放区域" || sheet.Cells[2, 6].StringValue != "存放地点" || sheet.Cells[2, 7].StringValue != "主要功能")
{
return(message);
}
//List<SuppliesEntity> slist = new List<SuppliesEntity>();
for (int i = 3; i <= sheet.Cells.MaxDataRow; i++)
{
count++;
SuppliesEntity entity = new SuppliesEntity();
if (string.IsNullOrWhiteSpace(sheet.Cells[i, 1].StringValue))
{
falseMessage += "</br>" + "第" + (i + 1) + "行物资名称不能为空,未能导入.";
error++;
continue;
}
if (string.IsNullOrWhiteSpace(sheet.Cells[i, 3].StringValue))
{
falseMessage += "</br>" + "第" + (i + 1) + "行数量不能为空,未能导入.";
error++;
continue;
}
entity.SUPPLIESTYPENAME = sheet.Cells[i, 0].StringValue;
var item = new DataItemDetailBLL().GetDataItemListByItemCode("'MAE_SUPPLIESTYPE'").Where(x => x.ItemName == entity.SUPPLIESTYPENAME).ToList().FirstOrDefault();
if (item != null)
{
entity.SUPPLIESTYPE = item.ItemValue;
}
entity.SUPPLIESNAME = sheet.Cells[i, 1].StringValue;
entity.Models = sheet.Cells[i, 2].StringValue;
entity.NUM = Convert.ToInt32(sheet.Cells[i, 3].StringValue);
entity.SUPPLIESUNTILNAME = sheet.Cells[i, 4].StringValue;
var itemUnit = new DataItemDetailBLL().GetDataItemListByItemCode("'MAE_SUPPLIESUNTIL'").Where(x => x.ItemName == entity.SUPPLIESUNTILNAME).ToList().FirstOrDefault();
if (itemUnit != null)
{
entity.SUPPLIESUNTIL = itemUnit.ItemValue;
}
if (!string.IsNullOrWhiteSpace(sheet.Cells[i, 5].StringValue))
{
DistrictEntity district = districtbll.GetDistrict(user.OrganizeId, sheet.Cells[i, 5].StringValue);
if (district == null)
{
falseMessage += "</br>" + "第" + (i + 1) + "行区域不是系统内区域,未能导入.";
error++;
continue;
}
else
{
entity.WorkAreaCode = district.DistrictCode;
entity.WorkAreaName = district.DistrictName;
}
}
entity.STORAGEPLACE = sheet.Cells[i, 6].StringValue;
entity.MAINFUN = sheet.Cells[i, 7].StringValue;
entity.CREATEDATE = DateTime.Now;
entity.DEPARTID = user.DeptId;
entity.DEPARTNAME = user.DeptName;
entity.USERID = user.UserId;
entity.USERNAME = user.UserName;
entity.SUPPLIESCODE = suppliesbll.GetMaxCode();
//entity.Create();
suppliesbll.SaveForm(entity.ID, entity);
var entityInorOut = new InoroutrecordEntity
{
USERID = entity.USERID,
USERNAME = entity.USERNAME,
DEPARTID = entity.DEPARTID,
DEPARTNAME = entity.DEPARTNAME,
INOROUTTIME = DateTime.Now,
SUPPLIESCODE = entity.SUPPLIESCODE,
SUPPLIESTYPE = entity.SUPPLIESTYPE,
SUPPLIESTYPENAME = entity.SUPPLIESTYPENAME,
SUPPLIESNAME = entity.SUPPLIESNAME,
SUPPLIESUNTIL = entity.SUPPLIESUNTIL,
SUPPLIESUNTILNAME = entity.SUPPLIESUNTILNAME,
NUM = entity.NUM,
STORAGEPLACE = entity.STORAGEPLACE,
MOBILE = entity.MOBILE,
SUPPLIESID = entity.ID,
STATUS = 0
};
var inoroutrecordbll = new InoroutrecordBLL();
inoroutrecordbll.SaveForm("", entityInorOut);
//slist.Add(entity);
}
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条。";
if (error > 0)
{
message += "</br>" + falseMessage;
}
}
return(message);
}
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="queryJson"></param>
/// <returns></returns>
public ActionResult ExportData(string queryJson)
{
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.p_kid = "a.Id";
pagination.p_fields = "c.itemname as applystatename,b.itemname as worktype,workplace,workcontent,to_char(workstarttime,'yyyy-mm-dd hh24:mi') || ' - '||to_char(workendtime,'yyyy-mm-dd hh24:mi'),applyusername,applydeptname,to_char(a.createdate,'yyyy-mm-dd hh24:mi')";
pagination.p_tablename = " bis_highriskapply a left join base_dataitemdetail b on a.worktype=b.itemvalue and b.itemid =(select itemid from base_dataitem where itemcode='WorkType') left join base_dataitemdetail c on a.applystate=c.itemvalue and c.itemid =(select itemid from base_dataitem where itemcode='WorkStatus')";
pagination.conditionJson = "1=1";
pagination.sidx = "a.createdate";
pagination.sord = "desc";
Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current();
if (!user.IsSystem)
{
string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search");
if (!string.IsNullOrEmpty(authType))
{
switch (authType)
{
case "1":
pagination.conditionJson += " and applyuserid='" + user.UserId + "'";
break;
case "2":
pagination.conditionJson += " and ApplyDeptCode='" + user.DeptCode + "'";
break;
case "3": //本子部门
pagination.conditionJson += string.Format(" and ApplyDeptCode in(select encode from base_department where encode like '{0}%' or senddeptid='{1}')", user.DeptCode, user.DeptId);
break;
case "4":
pagination.conditionJson += " and a.createuserorgcode='" + user.OrganizeCode + "'";
break;
}
}
else
{
pagination.conditionJson += " and 0=1";
}
}
DataTable exportTable = highriskapplybll.GetPageDataTable(pagination, queryJson);
exportTable.Columns.Remove("id");
exportTable.Columns.Remove("r");
// 确定导出文件名
string fileName = "高风险作业许可申请信息";
HttpResponse resp = System.Web.HttpContext.Current.Response;
// 详细列表内容
string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/高风险作业许可申请信息.xlsx"));
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[1, 1];
sheet.Cells.ImportDataTable(exportTable, false, 1, 0);
wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp);
return(Success("导出成功!"));
}
public string ImportStandard(string standardtype, string categorycode)
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
string message = "请选择文件格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
if (HttpContext.Request.Files.Count != 2)
{
return("请按正确的方式导入两个文件.");
}
HttpPostedFileBase file = HttpContext.Request.Files[0];
HttpPostedFileBase file2 = HttpContext.Request.Files[1];
if (string.IsNullOrEmpty(file.FileName) || string.IsNullOrEmpty(file2.FileName))
{
return(message);
}
Boolean isZip1 = file.FileName.Substring(file.FileName.IndexOf('.')).Contains("zip"); //第一个文件是否为Zip格式
Boolean isZip2 = file2.FileName.Substring(file2.FileName.IndexOf('.')).Contains("zip"); //第二个文件是否为Zip格式
if ((isZip1 || isZip2) == false || (isZip1 && isZip2) == true)
{
return(message);
}
string fileName1 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName1));
string fileName2 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file2.FileName);
file2.SaveAs(Server.MapPath("~/Resource/temp/" + fileName2));
string decompressionDirectory = Server.MapPath("~/Resource/decompression/") + DateTime.Now.ToString("yyyyMMddhhmmssfff") + "\\";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
if (isZip1)
{
UnZip(Server.MapPath("~/Resource/temp/" + fileName1), decompressionDirectory, "", true);
wb.Open(Server.MapPath("~/Resource/temp/" + fileName2));
}
else
{
UnZip(Server.MapPath("~/Resource/temp/" + fileName2), decompressionDirectory, "", true);
wb.Open(Server.MapPath("~/Resource/temp/" + fileName1));
}
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTable(2, 0, cells.MaxDataRow - 1, cells.MaxColumn + 1, false);
for (int i = 1; i < dt.Rows.Count; i++)
{
//文件名称
string filename = dt.Rows[i][0].ToString();
//文件路径
string filepath = dt.Rows[i][1].ToString();
//相应元素
string relevantelement = "";
string relevantelementname = "";
string relevantelementid = "";
//实施日期
string carrydate = "";
if (standardtype == "1" || standardtype == "2" || standardtype == "3" || standardtype == "4" || standardtype == "5" || standardtype == "6")
{
relevantelement = dt.Rows[i][2].ToString();
carrydate = dt.Rows[i][3].ToString();
}
//文学字号
string dispatchcode = "";
//颁布部门
string publishdept = "";
if (standardtype == "6")
{
dispatchcode = dt.Rows[i][4].ToString();
publishdept = dt.Rows[i][5].ToString();
}
string dutyid = "";
string dutyName = "";
//---****值存在空验证*****--
if (string.IsNullOrEmpty(filename) || string.IsNullOrEmpty(filepath))
{
falseMessage += "</br>" + "第" + (i + 3) + "行值存在空,未能导入.";
error++;
continue;
}
//---****文件格式验证*****--
if (!(filepath.Substring(filepath.IndexOf('.')).Contains("doc") || filepath.Substring(filepath.IndexOf('.')).Contains("docx") || filepath.Substring(filepath.IndexOf('.')).Contains("pdf")))
{
falseMessage += "</br>" + "第" + (i + 3) + "行附件格式不正确,未能导入.";
error++;
continue;
}
//---****文件是否存在验证*****--
if (!System.IO.File.Exists(decompressionDirectory + filepath))
{
falseMessage += "</br>" + "第" + (i + 3) + "行附件不存在,未能导入.";
error++;
continue;
}
//--**验证岗位是否存在 * *--
int startnum = 4;
if (standardtype == "1" || standardtype == "2" || standardtype == "3" || standardtype == "4" || standardtype == "5")
{
startnum = 4;
}
else if (standardtype == "6")
{
startnum = 6;
}
else if (standardtype == "7" || standardtype == "8" || standardtype == "9")
{
startnum = 2;
}
for (int j = startnum; j < dt.Columns.Count; j++)
{
if (!dt.Rows[i][j].IsEmpty())
{
foreach (var item in dt.Rows[i][j].ToString().Split(','))
{
DepartmentEntity dept = DepartmentBLL.GetList().Where(t => t.OrganizeId == orgId && t.FullName == dt.Rows[0][j].ToString()).FirstOrDefault();
if (dept == null)
{
continue;
}
RoleEntity re = postBLL.GetList().Where(a => a.FullName == item.ToString() && a.OrganizeId == orgId && a.DeleteMark == 0 && a.EnabledMark == 1 && a.DeptId == dept.DepartmentId).FirstOrDefault();
if (re == null)
{
//falseMessage += "</br>" + "第" + (i + 3) + "行岗位有误,未能导入.";
//error++;
continue;
}
else
{
dutyid += re.RoleId + ",";
dutyName += re.FullName + ",";
}
}
}
}
dutyid = dutyid.Length > 0 ? dutyid.Substring(0, dutyid.Length - 1) : "";
dutyName = dutyName.Length > 0 ? dutyName.Substring(0, dutyName.Length - 1) : "";
StandardsystemEntity standard = new StandardsystemEntity();
try
{
if (!string.IsNullOrEmpty(carrydate))
{
standard.CARRYDATE = DateTime.Parse(DateTime.Parse(carrydate).ToString("yyyy-MM-dd"));
}
}
catch
{
falseMessage += "</br>" + "第" + (i + 3) + "行时间有误,未能导入.";
error++;
continue;
}
if (!string.IsNullOrEmpty(relevantelement))
{
foreach (var item in relevantelement.Split(','))
{
ElementEntity re = elementBLL.GetList("").Where(a => a.NAME == item.ToString()).FirstOrDefault();
if (re == null)
{
//falseMessage += "</br>" + "第" + (i + 2) + "行相应元素有误,未能导入.";
//error++;
continue;
}
else
{
relevantelementname += re.NAME + ",";
relevantelementid += re.ID + ",";
}
}
}
relevantelementname = string.IsNullOrEmpty(relevantelementname) ? "" : relevantelementname.Substring(0, relevantelementname.Length - 1);
relevantelementid = string.IsNullOrEmpty(relevantelementid) ? "" : relevantelementid.Substring(0, relevantelementid.Length - 1);
standard.FILENAME = filename;
standard.STATIONID = dutyid;
standard.STATIONNAME = dutyName;
standard.RELEVANTELEMENTNAME = relevantelementname;
standard.RELEVANTELEMENTID = relevantelementid;
standard.DISPATCHCODE = dispatchcode;
standard.PUBLISHDEPT = publishdept;
standard.STANDARDTYPE = standardtype;
standard.CATEGORYCODE = categorycode;
standard.CONSULTNUM = 0;
standard.ID = Guid.NewGuid().ToString();
var fileinfo = new FileInfo(decompressionDirectory + filepath);
FileInfoEntity fileInfoEntity = new FileInfoEntity();
string fileguid = Guid.NewGuid().ToString();
fileInfoEntity.Create();
fileInfoEntity.RecId = standard.ID; //关联ID
fileInfoEntity.FileName = filepath;
fileInfoEntity.FilePath = "~/Resource/StandardSystem/" + fileguid + fileinfo.Extension;
fileInfoEntity.FileSize = (Math.Round(decimal.Parse(fileinfo.Length.ToString()) / decimal.Parse("1024"), 2)).ToString();//文件大小（kb）
fileInfoEntity.FileExtensions = fileinfo.Extension;
fileInfoEntity.FileType = fileinfo.Extension.Replace(".", "");
TransportRemoteToServer(Server.MapPath("~/Resource/StandardSystem/"), decompressionDirectory + filepath, fileguid + fileinfo.Extension);
fileinfobll.SaveForm("", fileInfoEntity);
try
{
standardsystembll.SaveForm(standard.ID, standard);
}
catch
{
error++;
}
}
count = dt.Rows.Count - 1;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
/// <summary>
/// 產生匯入檔
/// </summary>
private void ExportInputFile()
{
List<ReportStudentData> ExportData = new List<ReportStudentData>();
List<string> SIDList = (from x in Students select x.StudentID).ToList();
// 父母資料
List<JHParentRecord> ParentRecList = JHParent.SelectByStudentIDs(SIDList);
// 地址
List<JHAddressRecord> AddressRecList = JHAddress.SelectByStudentIDs(SIDList);
// 電話
List<JHPhoneRecord> PhoneRecList = JHPhone.SelectByStudentIDs(SIDList);
List<SLearningDomainParser> allsems = new List<SLearningDomainParser>();
allsems.Add(new SLearningDomainParser(1, 1));
allsems.Add(new SLearningDomainParser(1, 2));
allsems.Add(new SLearningDomainParser(2, 1));
allsems.Add(new SLearningDomainParser(2, 2));
allsems.Add(new SLearningDomainParser(3, 1));
allsems.Add(new SLearningDomainParser(3, 2));
allsems.Add(new SLearningDomainParser(7, 1));
allsems.Add(new SLearningDomainParser(7, 2));
allsems.Add(new SLearningDomainParser(8, 1));
allsems.Add(new SLearningDomainParser(8, 2));
allsems.Add(new SLearningDomainParser(9, 1));
allsems.Add(new SLearningDomainParser(9, 2));
//// 取得 UDT 對照
//List<JointAdmissionModule.DAL.UserDefData_StudTypeWeight> udtList = DAL.UDTTransfer.GetDataFromUDT_StudTypeWeight().Where(x => x.SchoolType == "高中").ToList();
//// 建立特種身分代碼對照表
//Dictionary<string, string> specCodeDict = new Dictionary<string, string>();
//foreach (JointAdmissionModule.DAL.UserDefData_StudTypeWeight dd in udtList)
// if (!specCodeDict.ContainsKey(dd.JoinStudType))
// specCodeDict.Add(dd.JoinStudType, dd.JoinStudTypeCode);
// 資料轉換
foreach (ReportStudent stud in Students)
{
// 是否已有年排名資料
List<DAL.UserDefData> uddList = new List<JointAdmissionModule.DAL.UserDefData>();
if (_StudRankData.ContainsKey(stud.StudentID))
uddList = _StudRankData[stud.StudentID];
ReportStudentData rsd = new ReportStudentData();
// 班級
if (stud.Class != null)
rsd.ClassName = stud.Class.Name;
// 座號
rsd.SeatNo = stud.SeatNo;
// 姓名
rsd.Name = stud.Name;
// 家長姓名
foreach (JHParentRecord rec in ParentRecList.Where(x => x.RefStudentID == stud.StudentID))
{
rsd.ParentName = rec.CustodianName;
}
foreach (JHPhoneRecord rec in PhoneRecList.Where(x => x.RefStudentID == stud.StudentID))
{
// 電話
rsd.Phone = rec.Contact;
// 手機
rsd.CellPhone = rec.Cell;
}
// 生日
DateTime dt;
if (DateTime.TryParse(stud.Birthday, out dt))
{
rsd.Birthday = dt;
}
// 性別
if (stud.Gender == "男")
rsd.GenderCode = "1";
if (stud.Gender == "女")
rsd.GenderCode = "2";
// 身分證字號
rsd.IDNumber = stud.IDNumber;
foreach (JHAddressRecord rec in AddressRecList.Where(x => x.RefStudentID == stud.StudentID))
{
// 郵區號
rsd.ZipCode = rec.MailingZipCode;
// 聯絡地址
rsd.Address = rec.MailingCounty + rec.MailingTown + rec.MailingDistrict + rec.MailingArea + rec.MailingDetail;
}
//string code = "";
// 特種身分(原民未持1、原民認證2、身心障礙3、其他4)
if (!string.IsNullOrEmpty(stud.SpcStudTypeName))
{
rsd.SpceTypeCode = 4;
if (stud.SpcStudTypeName == StudSpcName1)
rsd.SpceTypeCode = 2;
if (stud.SpcStudTypeName == StudSpcName2)
rsd.SpceTypeCode = 1;
if (stud.SpcStudTypeName == StudSpcName6)
rsd.SpceTypeCode = 3;
}
PlaceCollection Places = stud.Places.NS("年排名");
// 這寫法主要判斷當名次在不同年級時可能會讀到錯誤資料
//if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { }
//else
foreach (SLearningDomainParser semsIndex in allsems)
{
if ((semsIndex.Grade == 1 || semsIndex.Grade == 7) && semsIndex.sm == 1)
{
// 一上名次
// 一上名次百分比
string placeKey = semsIndex.Name;
if (Places.Contains(placeKey))
{
if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { }
else
{
rsd.Rank11 = Places[placeKey].Level;
rsd.RankPercent11 = Places[placeKey].Percentage;
// 當有加分即時運算
if (stud.AddWeight.HasValue)
{
int Level = 1,Percentage=1;
List<Place> PList = new List<Place>();
decimal sc = Places[placeKey].Score * stud.AddWeight.Value;
if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear))
{
if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey))
{
PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList();
if (PList.Count > 0)
{
PList.OrderBy(x => x.Score);
Level = PList[0].Level;
Percentage = PList[0].Percentage;
}
rsd.Rank11 = Level;
rsd.RankPercent11 = Percentage;
}
}
}
}
}
}
if ((semsIndex.Grade == 1 || semsIndex.Grade == 7) && semsIndex.sm == 2)
{
// 一下名次
// 一下名次百分比
string placeKey = semsIndex.Name;
if (Places.Contains(placeKey))
{
if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { }
else
{
rsd.Rank12 = Places[placeKey].Level;
rsd.RankPercent12 = Places[placeKey].Percentage;
// 當有加分即時運算
if (stud.AddWeight.HasValue)
{
int Level = 1, Percentage = 1;
List<Place> PList = new List<Place>();
decimal sc = Places[placeKey].Score * stud.AddWeight.Value;
if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear))
{
if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey))
{
PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList();
if (PList.Count > 0)
{
PList.OrderBy(x => x.Score);
Level = PList[0].Level;
Percentage = PList[0].Percentage;
}
rsd.Rank12 = Level;
rsd.RankPercent12 = Percentage;
}
}
}
}
}
}
if ((semsIndex.Grade == 2 || semsIndex.Grade == 8) && semsIndex.sm == 1)
{
// 二上名次
// 二上名次百分比
string placeKey = semsIndex.Name;
if (Places.Contains(placeKey))
{
if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { }
else
{
rsd.Rank21 = Places[placeKey].Level;
rsd.RankPercent21 = Places[placeKey].Percentage;
// 當有加分即時運算
if (stud.AddWeight.HasValue)
{
int Level = 1, Percentage = 1;
List<Place> PList = new List<Place>();
decimal sc = Places[placeKey].Score * stud.AddWeight.Value;
if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear))
{
if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey))
{
PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList();
if (PList.Count > 0)
{
PList.OrderBy(x => x.Score);
Level = PList[0].Level;
Percentage = PList[0].Percentage;
}
}
rsd.Rank21 = Level;
rsd.RankPercent21 = Percentage;
}
}
}
}
}
if ((semsIndex.Grade == 2 || semsIndex.Grade == 8) && semsIndex.sm == 2)
{
// 二下名次
// 二下名次百分比
string placeKey = semsIndex.Name;
if (Places.Contains(placeKey))
{
if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { }
else
{
rsd.Rank22 = Places[placeKey].Level;
rsd.RankPercent22 = Places[placeKey].Percentage;
// 當有加分即時運算
if (stud.AddWeight.HasValue)
{
int Level = 1, Percentage = 1;
List<Place> PList = new List<Place>();
decimal sc = Places[placeKey].Score * stud.AddWeight.Value;
if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear))
{
if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey))
{
PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList();
if (PList.Count > 0)
{
PList.OrderBy(x => x.Score);
Level = PList[0].Level;
Percentage = PList[0].Percentage;
}
rsd.Rank22 = Level;
rsd.RankPercent22 = Percentage;
}
}
}
}
}
}
if ((semsIndex.Grade == 3 || semsIndex.Grade == 9) && semsIndex.sm == 1)
{
// 三上名次
// 三上名次百分比
string placeKey = semsIndex.Name;
if (Places.Contains(placeKey))
{
if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { }
else
{
rsd.Rank31 = Places[placeKey].Level;
rsd.RankPercent31 = Places[placeKey].Percentage;
// 當有加分即時運算
if (stud.AddWeight.HasValue)
{
int Level = 1, Percentage = 1;
List<Place> PList = new List<Place>();
decimal sc = Places[placeKey].Score * stud.AddWeight.Value;
if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear))
{
if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey))
{
PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList();
if (PList.Count > 0)
{
PList.OrderBy(x => x.Score);
Level = PList[0].Level;
Percentage = PList[0].Percentage;
}
}
rsd.Rank31 = Level;
rsd.RankPercent31 = Percentage;
}
}
}
}
}
}
if (Places.Contains("學習領域"))
{
string placeKey = "學習領域";
// 五學期名次
rsd.AvgRank5 = Places[placeKey].Level;
// 五學期名次百分比
rsd.AvgRankPercent5 = Places[placeKey].Percentage;
// 當有加分即時運算
if (stud.AddWeight.HasValue)
{
int Level = 1, Percentage = 1;
List<Place> PList = new List<Place>();
decimal sc = Places[placeKey].Score * stud.AddWeight.Value;
if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear))
{
placeKey = "學期總平均";
if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey))
{
PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList();
if (PList.Count > 0)
{
PList.OrderBy(x => x.Score);
Level = PList[0].Level;
Percentage = PList[0].Percentage;
}
}
rsd.AvgRank5 = Level;
rsd.AvgRankPercent5 = Percentage;
}
}
}
// 處理已有年排名(在UDT有存資料，填入 UDT 資料)
if (uddList.Count > 0 && stud.LastEnterSemester.HasValue)
{
foreach (DAL.UserDefData udd in uddList)
{
// 七上
if ((udd.GradeYear == 1 || udd.GradeYear == 7) && udd.Semester == 1)
{
rsd.Rank11 = udd.GradeRank;
rsd.RankPercent11 = udd.GradeRankPercent;
// 特種身分加分
if (stud.AddWeight.HasValue)
{
rsd.Rank11 = udd.GradeRankAdd;
rsd.RankPercent11 = udd.GradeRankPercentAdd;
}
}
// 七下
if ((udd.GradeYear == 1 || udd.GradeYear == 7) && udd.Semester == 2)
{
rsd.Rank12 = udd.GradeRank;
rsd.RankPercent12 = udd.GradeRankPercent;
// 特種身分加分
if (stud.AddWeight.HasValue)
{
rsd.Rank12 = udd.GradeRankAdd;
rsd.RankPercent12 = udd.GradeRankPercentAdd;
}
}
// 八上
if ((udd.GradeYear==2 ||udd.GradeYear==8) && udd.Semester ==1)
{
rsd.Rank21 = udd.GradeRank;
rsd.RankPercent21 = udd.GradeRankPercent;
// 特種身分加分
if (stud.AddWeight.HasValue)
{
rsd.Rank21 = udd.GradeRankAdd;
rsd.RankPercent21 = udd.GradeRankPercentAdd;
}
}
// 八下
if ((udd.GradeYear == 2 || udd.GradeYear == 8) && udd.Semester == 2)
{
rsd.Rank22 = udd.GradeRank;
rsd.RankPercent22 = udd.GradeRankPercent;
// 特種身分加分
if (stud.AddWeight.HasValue)
{
rsd.Rank22 = udd.GradeRankAdd;
rsd.RankPercent22 = udd.GradeRankPercentAdd;
}
}
// 九上
if ((udd.GradeYear == 3 || udd.GradeYear == 9) && udd.Semester == 1)
{
rsd.Rank31 = udd.GradeRank;
rsd.RankPercent31 = udd.GradeRankPercent;
// 特種身分加分
if (stud.AddWeight.HasValue)
{
rsd.Rank31 = udd.GradeRankAdd;
rsd.RankPercent31 = udd.GradeRankPercentAdd;
}
}
}
}
// 判斷轉入生，清空之前名次
if (stud.LastEnterGradeYear.HasValue && stud.LastEnterSchoolyear.HasValue )
{
int grYear = 0;
// 先判斷年級
if (stud.LastEnterGradeYear >= 6)
{
grYear = stud.LastEnterGradeYear.Value - 6;
}
else
{
grYear = stud.LastEnterGradeYear.Value;
}
// 判斷udd有輸入資料
if (grYear == 3 && stud.LastEnterSemester == 1)
{
bool clear11 = true, clear12 = true, clear21 = true, clear22 = true;
foreach (DAL.UserDefData udd in uddList)
{
if (udd.GradeYear == 1 && udd.Semester == 1)
clear11 = false;
if (udd.GradeYear == 1 && udd.Semester == 2)
clear12 = false;
if (udd.GradeYear == 2 && udd.Semester == 1)
clear21 = false;
if (udd.GradeYear == 2 && udd.Semester == 2)
clear22 = false;
}
if (clear11)
{
rsd.Rank11 = null;
rsd.RankPercent11 = null;
}
if (clear12)
{
rsd.Rank12 = null;
rsd.RankPercent12 = null;
}
if (clear21)
{
rsd.Rank21 = null;
rsd.RankPercent21 = null;
}
if (clear22)
{
rsd.Rank22 = null;
rsd.RankPercent22 = null;
}
}
if (grYear == 2 && stud.LastEnterSemester == 2)
{
bool clear11 = true, clear12 = true, clear21 = true;
foreach (DAL.UserDefData udd in uddList)
{
if (udd.GradeYear == 1 && udd.Semester == 1)
clear11 = false;
if (udd.GradeYear == 1 && udd.Semester == 2)
clear12 = false;
if (udd.GradeYear == 2 && udd.Semester == 1)
clear21 = false;
}
if (clear11)
{
rsd.Rank11 = null;
rsd.RankPercent11 = null;
}
if (clear12)
{
rsd.Rank12 = null;
rsd.RankPercent12 = null;
}
if (clear21)
{
rsd.Rank21 = null;
rsd.RankPercent21 = null;
}
}
if (grYear == 2 && stud.LastEnterSemester == 1)
{
bool clear11 = true, clear12 = true;
foreach (DAL.UserDefData udd in uddList)
{
if (udd.GradeYear == 1 && udd.Semester == 1)
clear11 = false;
if (udd.GradeYear == 1 && udd.Semester == 2)
clear12 = false;
}
if (clear11)
{
rsd.Rank11 = null;
rsd.RankPercent11 = null;
}
if (clear12)
{
rsd.Rank12 = null;
rsd.RankPercent12 = null;
}
}
if (grYear == 1 && stud.LastEnterSemester == 2)
{
bool clear11 = true;
foreach (DAL.UserDefData udd in uddList)
{
if (udd.GradeYear == 1 && udd.Semester == 1)
clear11 = false;
}
if (clear11)
{
rsd.Rank11 = null;
rsd.RankPercent11 = null;
}
}
// 當沒有輸入全部清空
if (uddList.Count == 0)
{
if (grYear == 3 && stud.LastEnterSemester == 1)
{
rsd.Rank22 = null;
rsd.Rank21 = null;
rsd.Rank12 = null;
rsd.Rank11 = null;
rsd.RankPercent22 = null;
rsd.RankPercent21 = null;
rsd.RankPercent12 = null;
rsd.RankPercent11 = null;
}
if (grYear == 2 && stud.LastEnterSemester == 2)
{
rsd.Rank21 = null;
rsd.Rank12 = null;
rsd.Rank11 = null;
rsd.RankPercent21 = null;
rsd.RankPercent12 = null;
rsd.RankPercent11 = null;
}
if (grYear == 2 && stud.LastEnterSemester == 1)
{
rsd.Rank12 = null;
rsd.Rank11 = null;
rsd.RankPercent12 = null;
rsd.RankPercent11 = null;
}
if (grYear == 1 && stud.LastEnterSemester == 2)
{
rsd.Rank11 = null;
rsd.RankPercent11 = null;
}
}
}
rsd.UpdateDate3 = stud.TransUpdateDateStr;
ExportData.Add(rsd);
}
// 印報表
if (ExportData.Count > 0)
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources._100高雄高中多元入學匯入檔));
Aspose.Cells.Worksheet wst = wb.Worksheets[0];
int RowIdx = 1;
foreach (ReportStudentData rsd in ExportData)
{
// 班級 0
wst.Cells[RowIdx, 0].PutValue(rsd.ClassName);
// 座號 1
wst.Cells[RowIdx, 1].PutValue(string.Format("{0:00}",rsd.SeatNo));
// 姓名 2
wst.Cells[RowIdx, 2].PutValue(rsd.Name);
// 家長姓名 3
wst.Cells[RowIdx, 3].PutValue(rsd.ParentName);
if (rsd.Birthday.HasValue)
{
// 年 4
wst.Cells[RowIdx, 4].PutValue((rsd.Birthday.Value.Year -1911));
// 月 5
wst.Cells[RowIdx, 5].PutValue(rsd.Birthday.Value.Month);
// 日 6
wst.Cells[RowIdx, 6].PutValue(rsd.Birthday.Value.Day);
}
// 性別 7
wst.Cells[RowIdx, 7].PutValue(rsd.GenderCode);
// 身分證字號 8
wst.Cells[RowIdx, 8].PutValue(rsd.IDNumber);
// 郵區號 9
wst.Cells[RowIdx, 9].PutValue(rsd.ZipCode);
// 聯絡地址 10
wst.Cells[RowIdx, 10].PutValue(rsd.Address);
// 電話 11
wst.Cells[RowIdx, 11].PutValue(rsd.Phone);
// 手機 12
wst.Cells[RowIdx, 12].PutValue(rsd.CellPhone);
// 特種身分(原民未持1、原民認證2、身心障礙3、其他4) 13
if(rsd.SpceTypeCode.HasValue)
wst.Cells[RowIdx, 13].PutValue(rsd.SpceTypeCode.Value);
else
wst.Cells[RowIdx, 13].PutValue("");
// 一上名次 14
if(rsd.Rank11.HasValue)
wst.Cells[RowIdx, 15].PutValue(rsd.Rank11.Value);
// 一上名次百分比 15
if(rsd.RankPercent11.HasValue )
wst.Cells[RowIdx, 16].PutValue(rsd.RankPercent11.Value);
// 一下名次 16
if(rsd.Rank12.HasValue)
wst.Cells[RowIdx, 17].PutValue(rsd.Rank12.Value);
// 一下名次百分比 17
if(rsd.RankPercent12.HasValue)
wst.Cells[RowIdx, 18].PutValue(rsd.RankPercent12.Value);
// 二上名次 18
if(rsd.Rank21.HasValue )
wst.Cells[RowIdx, 19].PutValue(rsd.Rank21.Value);
// 二上名次百分比 19
if(rsd.RankPercent21.HasValue )
wst.Cells[RowIdx, 20].PutValue(rsd.RankPercent21.Value);
// 二下名次 20
if(rsd.Rank22.HasValue)
wst.Cells[RowIdx, 21].PutValue(rsd.Rank22.Value);
// 二下名次百分比 21
if(rsd.RankPercent22.HasValue)
wst.Cells[RowIdx, 22].PutValue(rsd.RankPercent22.Value);
// 三上名次 22
if(rsd.Rank31.HasValue)
wst.Cells[RowIdx, 23].PutValue(rsd.Rank31.Value);
// 三上名次百分比 23
if(rsd.RankPercent31.HasValue )
wst.Cells[RowIdx, 24].PutValue(rsd.RankPercent31.Value);
// 五學期名次 24
if(rsd.AvgRank5.HasValue )
wst.Cells[RowIdx, 25].PutValue(rsd.AvgRank5.Value);
// 五學期名次百分比 25
if(rsd.AvgRankPercent5.HasValue )
wst.Cells[RowIdx, 26].PutValue(rsd.AvgRankPercent5.Value);
// 是否是轉學生1是，否空白
if(!string.IsNullOrEmpty(rsd.UpdateDate3))
wst.Cells[RowIdx, 27].PutValue("1");
RowIdx++;
}
string FileName = "高中(高雄)用國中匯入檔.xls";
// 儲存檔案
try
{
string path = Path.Combine(System.Windows.Forms.Application.StartupPath, "Reports");
DirectoryInfo dir = new DirectoryInfo(path);
if (!dir.Exists) dir.Create();
path = Path.Combine(path, FileName);
if (File.Exists(path))
{
int i = 1;
while (true)
{
string newPath = Path.GetDirectoryName(path) + "\\" + Path.GetFileNameWithoutExtension(path) + (i++) + Path.GetExtension(path);
if (!File.Exists(newPath))
{
path = newPath;
break;
}
}
}
wb.Save(path, Aspose.Cells.FileFormatType.Excel2003);
if (MsgBox.Show(FileName + "產生完成，是否立刻開啟？", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
System.Diagnostics.Process.Start(path);
}
}
catch (Exception ex)
{
MsgBox.Show("儲存失敗");
}
}
}
private void btnImport_Click(object sender, EventArgs e)
{
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
try
{
wb.Open(this.textBoxX1.Text);
Aspose.Cells.Worksheet ws = wb.Worksheets[0];
int rowIndex = 1;
int totalCount = 0;
while (ws.Cells[rowIndex, 0].Value != null && !string.IsNullOrWhiteSpace(ws.Cells[rowIndex, 0].Value.ToString()))
{
rowIndex += 1;
totalCount += 1;
}
this.progressBarX1.Maximum = totalCount;
rowIndex = 1;
AccessHelper ah = new AccessHelper();
while (ws.Cells[rowIndex, 0].Value != null && !string.IsNullOrWhiteSpace(ws.Cells[rowIndex, 0].Value.ToString()))
{
string ntu_sys_no = GetCellValue(ws.Cells[rowIndex, 0]);
string tea_name = GetCellValue(ws.Cells[rowIndex, 1]);
string tea_eng_name = GetCellValue(ws.Cells[rowIndex, 2]);
string tea_account = GetCellValue(ws.Cells[rowIndex, 7]);
string tea_email = GetCellValue(ws.Cells[rowIndex, 8]);
string tea_office_telno = GetCellValue(ws.Cells[rowIndex, 12]);
string unit = GetCellValue(ws.Cells[rowIndex, 15]);
//string emp_no = ws.Cells[rowIndex, 5].Value.ToString();
K12.Data.TeacherRecord tea = new K12.Data.TeacherRecord();
tea.Name = tea_name;
tea.TALoginName = tea_account;
tea.Email = tea_email;
string newTID = K12.Data.Teacher.Insert(tea);
//K12.Data.Teacher.Update(tea);
UDT.TeacherExtVO udtTe = new UDT.TeacherExtVO();
udtTe.EnglishName = tea_eng_name;
udtTe.TeacherID = int.Parse(newTID);
udtTe.NtuSystemNo = ntu_sys_no;
//udtTe.EmployeeNo = emp_no;
udtTe.OtherPhone = tea_office_telno;
udtTe.MajorWorkPlace = unit;
List<ActiveRecord> rec = new List<ActiveRecord>();
rec.Add(udtTe);
ah.SaveAll(rec);
rowIndex += 1;
this.labelX1.Text = string.Format("{0} / {1} ", rowIndex.ToString(), totalCount.ToString());
this.progressBarX1.Value = rowIndex;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
private void btnKetXuat_Click(object sender, EventArgs e)
{
if (grvDanhSachSinhVien.DataRowCount == 0)
{
ThongBao("Không có dữ liêu.");
return;
}
DataTable dtTemp = dtSinhVien.GetChanges();
if (dtTemp != null)
{
ThongBao("Dữ liệu đã bị thay đổi. Hay lưu lại trước khi kết xuất.");
return;
}
CreateWaitDialog("Đang xuất dữ liệu, xin vui lòng chờ.", "Xuất dữ liệu");
try
{
MemoryStream ms = new MemoryStream(Resources.DanhSachMienGiam);
var book = new Aspose.Cells.Workbook();
book.Open(ms);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;
cells["A2"].PutValue("Danh sách đối tượng miễn giảm lớp " + pDM_LopInfo.TenLop);
var rIndex = 5;
var svFilter = dtSinhVien.Select("IDDM_DoiTuongMienGiam > 0");
for (var i = 0; i < svFilter.Length; i++)
{
if (i < svFilter.Length - 2)
{
cells.InsertRow(rIndex + i + 1);
}
cells["A" + (rIndex + i)].PutValue(i + 1);
cells["B" + (rIndex + i)].PutValue(svFilter[i]["MaSinhVien"]);
cells["C" + (rIndex + i)].PutValue(svFilter[i]["HoVaTen"]);
cells["D" + (rIndex + i)].PutValue(svFilter[i]["NgaySinh"]);
cells["E" + (rIndex + i)].PutValue(dicDoiTuongMienGiam[(int)svFilter[i]["IDDM_DoiTuongMienGiam"]]);
}
SaveFileDialog sDlg = new SaveFileDialog();
sDlg.Title = "Lưu file kết xuất";
sDlg.InitialDirectory = Application.StartupPath;
sDlg.FileName = "DanhSachMienGiam_" + DateTime.Now.ToString("yyyyMMddhhmm") + ".xls";
if (sDlg.ShowDialog() != DialogResult.Cancel)
{
book.Save(sDlg.FileName, Aspose.Cells.FileFormatType.Excel2003);
Process.Start(sDlg.FileName);
}
}
catch (Exception ex)
{
ThongBaoLoi("Có lỗi khi xuất dữ liệu. " + ex.Message);
}
finally
{
CloseWaitDialog();
}
}
public string ImportTrainPlan()
{
var user = OperatorProvider.Provider.Current();
if (user.IsSystem)
{
return("超级管理员无此操作权限");
}
string message = "请选择格式正确的文件再导入!";
try
{
int error = 0;
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
var currUser = OperatorProvider.Provider.Current();
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
wb.Open(Server.MapPath("~/Resource/temp/" + fileName));
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
if (cells.MaxDataRow <= 1)
{
return("共有0条记录,成功导入0条");
}
DataTable dt = cells.ExportDataTable(1, 1, cells.MaxDataRow, cells.MaxColumn + 1, true);
int order = 1;
List <SafeTrainPlanEntity> list = new List <SafeTrainPlanEntity>();
for (int i = 0; i < dt.Rows.Count; i++)
{
order = i;
//培训项目
string projectName = dt.Rows[i]["培训项目"].ToString().Trim();
//培训内容
string content = dt.Rows[i]["培训内容"].ToString().Trim();
//培训对象
string participants = dt.Rows[i]["培训对象"].ToString().Trim();
//培训时间
string trainDate = dt.Rows[i]["培训时间"].ToString().Trim();
//责任部门
string dutyDeptName = dt.Rows[i]["责任部门"].ToString().Trim();
//责任人
string dutyUserName = dt.Rows[i]["责任人"].ToString().Trim();
//执行人/监督人
string executeUserName = dt.Rows[i]["执行人/监督人"].ToString().Trim();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(projectName) || string.IsNullOrEmpty(content) || string.IsNullOrEmpty(participants) || string.IsNullOrEmpty(trainDate) ||
string.IsNullOrEmpty(dutyDeptName) || string.IsNullOrEmpty(dutyUserName) || string.IsNullOrEmpty(executeUserName))
{
falseMessage += "</br>" + "第" + (i + 1) + "行值存在空,未能导入.";
error++;
continue;
}
//验证所填部门是否存在 电厂整个组织机构
var entity = departmentBLL.GetList()
.Where(t => t.EnCode.StartsWith(currUser.OrganizeCode) && t.EnCode != currUser.OrganizeCode && t.FullName == dutyDeptName)
.FirstOrDefault();
string deptCode = string.Empty, deptId = string.Empty;
if (entity == null)
{
falseMessage += "</br>" + "第" + (i + 1) + "行部门信息不存在,未能导入.";
error++;
continue;
}
else
{
deptId = entity.DepartmentId;
deptCode = entity.EnCode;
}
string dutyUserId = string.Empty;
string executeUserId = string.Empty;
//验证责任人是否存在责任部门下
var uentity = userBLL.GetList().Where(t => t.RealName.Equals(dutyUserName) && t.DepartmentId == entity.DepartmentId).FirstOrDefault();
if (uentity == null)
{
falseMessage += "</br>" + "第" + (i + 1) + "行责任人不存在,未能导入.";
error++;
continue;
}
else
{
dutyUserId = uentity.UserId;
}
//验证执行人/监督人是否存在责任部门下
uentity = userBLL.GetList().Where(t => t.RealName.Equals(executeUserName) && t.DepartmentId == entity.DepartmentId).FirstOrDefault();
if (uentity == null)
{
falseMessage += "</br>" + "第" + (i + 1) + "行执行人/监督人不存在,未能导入.";
error++;
continue;
}
else
{
executeUserId = uentity.UserId;
}
//验证日期格式
DateTime?trainDate1 = null;
try
{
//yyyy-MM或yyyy.MM
trainDate = trainDate.Replace(".", "-");
if (trainDate.Split('-').Length != 2 || trainDate.Split('-')[0].Length != 4)
{
falseMessage += "</br>" + "第" + (i + 1) + "行培训时间格式不正确,未能导入.";
error++;
continue;
}
trainDate1 = Convert.ToDateTime(trainDate);
}
catch (Exception ex)
{
falseMessage += "</br>" + "第" + (i + 1) + "行培训时间格式不正确,未能导入.";
error++;
continue;
}
SafeTrainPlanEntity safeTrainPlan = new SafeTrainPlanEntity()
{
Id = Guid.NewGuid().ToString(),
ProjectName = projectName,
TrainDate = trainDate1,
TrainContent = content,
Participants = participants,
DepartmentId = deptId,
DepartmentName = dutyDeptName,
DepartmentCode = deptCode,
DutyUserId = dutyUserId,
DutyUserName = dutyUserName,
ExecuteUserId = executeUserId,
ExecuteUserName = executeUserName,
ProcessState = 0
};
//数据查重 根据培训项目、培训内容、培训对象、培训时间以及责任部门
bool flag = safeTrainPlanBLL.CheckDataExists(safeTrainPlan);
if (flag)
{
//数据重复
falseMessage += "</br>" + "第" + (i + 1) + "行数据已存在,未能导入.";
error++;
}
else
{
safeTrainPlan.Create();
list.Add(safeTrainPlan);
}
}
if (list.Count() > 0)
{
//保存
safeTrainPlanBLL.InsertSafeTrainPlan(list);
}
count = dt.Rows.Count;
message = "共有" + count + "条记录,成功导入" + (count - error) + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
}
catch (Exception ex)
{
return("导入失败！");
}
return(message);
}