/// <summary> /// 設定表頁的列寬自適應 /// </summary> /// <param name="sheet"></param> private void SetColumnAuto(ref Aspose.Cells.Worksheet sheet) { Aspose.Cells.Cells cells = sheet.Cells; //获取页面最大列数 int columnCount = cells.MaxColumn + 1; //获取页面最大行数 int rowCount = cells.MaxRow; for (int col = 0; col < columnCount; col++) { sheet.AutoFitColumn(col, 0, rowCount); } for (int col = 0; col < columnCount; col++) { int pixel = cells.GetColumnWidthPixel(col) + 10; if (pixel > 255) { cells.SetColumnWidthPixel(col, 255); } else { cells.SetColumnWidthPixel(col, pixel); } } }
protected void btnUpdate_Click(object sender, EventArgs e) { if (upctrlPsn.UploadedFiles[0].IsValid && upctrlPsn.HasFile) { commonFunc.SaveFile(upctrlPsn, this); string savePath = commonFunc.GetSavePath(upctrlPsn, this); Aspose.Cells.Workbook xlsPsn = new Aspose.Cells.Workbook(savePath); //xlsPsn.Open(savePath); Aspose.Cells.Cells cellsPsn = xlsPsn.Worksheets[0].Cells; var dtTemp = cellsPsn.ExportDataTable(0, 0, cellsPsn.MaxDataRow + 1, cellsPsn.MaxDataColumn + 1); ListBox lstUpdated = new ListBox(); for (int i = 1; i < dtTemp.Rows.Count; i++) { if (commonFunc.VerifyNumber(dtTemp.Rows[i][0].ToString().Trim())) { lstUpdated.Items.Add(dtTemp.Rows[i][0].ToString().Trim()); } } foreach (ListItem item in lstUpdated.Items) { if (!xlstPerson.Items.Contains(item)) { xlstPerson.Items.Add(item); } } xlblPersonNumber.Text = xlstPerson.Items.Count.ToString(); JSHelper.Alert(UpdatePanel2, this, "上传成功!"); } }
private bool CheckFile(string fileName) { bool result = false; try { Aspose.Cells.Workbook workbookName = new Aspose.Cells.Workbook(fileName); int sheetCount = workbookName.Worksheets.Count; if (sheetCount == 1) { string sheetName = workbookName.Worksheets[0].Name; if (sheetName == "批量导入词条模板") { Aspose.Cells.Cells cellsName = workbookName.Worksheets[0].Cells; int minDataColumn = cellsName.MinDataColumn; int maxDataColumn = cellsName.MaxDataColumn; if (cellsName[0, minDataColumn].StringValue.Trim() == "词条" && cellsName[0, maxDataColumn].StringValue.Trim() == "解读") { result = true; } } } } catch (Exception ex) { } return(result); }
/// <summary> /// 读取excel文件导入DataTable(不依赖office com组件) /// 作者:佳烽 /// 日期:2014-4-24 /// </summary> /// <param name="strFileName">excel文件</param> /// <param name="exportColumnName">是否导出列名</param> /// <returns></returns> public static DataTable ReadExcelToDataTable(String strFileName, bool exportColumnName = true) { Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(strFileName); Aspose.Cells.Worksheet sheet = book.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; return(cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, exportColumnName)); }
private void btnExport_Click(object sender, EventArgs e) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Worksheets[0].Name = "評量輸入狀況檢視清單"; Aspose.Cells.Cells cs = wb.Worksheets[0].Cells; wb.Worksheets[0].Cells.Columns[0].Width = 40; wb.Worksheets[0].Cells.Columns[1].Width = 20; wb.Worksheets[0].Cells.Columns[2].Width = 10; wb.Worksheets[0].Cells.Columns[3].Width = 10; cs[0, 0].PutValue("課程名稱"); cs[0, 1].PutValue("授課老師"); cs[0, 2].PutValue("Midterm"); cs[0, 3].PutValue("Final"); int index = 1; foreach (DataGridViewRow row in dgv.Rows) { if (row.IsNewRow) { continue; } string course_name = row.Cells[colCourse.Index].Value + ""; string teacher_name = row.Cells[colTeacher.Index].Value + ""; string midterm = row.Cells[colExam1.Index].Value + ""; string final = row.Cells[colExam2.Index].Value + ""; cs[index, 0].PutValue(course_name); cs[index, 1].PutValue(teacher_name); cs[index, 2].PutValue(midterm); cs[index, 3].PutValue(final); index++; } //wb.Worksheets[0].AutoFitColumns(); SaveFileDialog save = new SaveFileDialog(); save.Title = "另存新檔"; save.FileName = "評量輸入狀況檢視清單.xls"; save.Filter = "Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*"; if (save.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { wb.Save(save.FileName, Aspose.Cells.SaveFormat.Excel97To2003); System.Diagnostics.Process.Start(save.FileName); } catch { MessageBox.Show("檔案儲存失敗"); } } }
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error) { error = ""; try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; foreach (DataRow row in datatable.Rows) { nRow++; try { for (int i = 0; i < datatable.Columns.Count; i++) { if (row[i].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[i]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, i, mstream); } else { cells[nRow, i].PutValue(row[i]); } } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } workbook.Save(filepath); return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
private void BindCheckItem(ref Aspose.Cells.Worksheet sheet, string FormNo, string Bu) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()]; NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region//獲取主表資訊 DataTable dtMaster = oStandard.GetPrelaunchInconformity(FormNo); if (dtMaster.Rows.Count > 0) { int templateIndex = 1; //模板row起始位置 int insertIndexEnCounter = templateIndex + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1); cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行 string url = "http://icm651.liteon.com/WF_PrelaunchReport/"; for (int i = 0; i < dtMaster.Rows.Count; i++) { DataRow dr = dtMaster.Rows[i]; string FileName = dr["FileName"].ToString(); cells[i + templateIndex, 0].PutValue(dr["ID"].ToString()); cells[i + templateIndex, 1].PutValue(dr["Dept"].ToString()); cells[i + templateIndex, 2].PutValue(dr["CheckItem"].ToString()); cells[i + templateIndex, 3].PutValue(dr["Description"].ToString()); cells[i + templateIndex, 4].PutValue(dr["Status"].ToString()); cells[i + templateIndex, 5].PutValue(dr["Remark"].ToString()); cells[i + templateIndex, 6].PutValue(dr["Suggestion"].ToString()); cells[i + templateIndex, 7].PutValue(dr["CompleteDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompleteDate"].ToString()).ToString("yyyy/MM/dd") : dr["CompleteDate"].ToString()); cells[i + templateIndex, 8].PutValue(dr["UpateUser"].ToString()); cells[i + templateIndex, 9].PutValue(dr["UpdateTime"].ToString().Length > 0 ? Convert.ToDateTime(dr["UpdateTime"].ToString()).ToString("yyyy/MM/dd") : dr["UpdateTime"].ToString()); if (!string.IsNullOrEmpty(FileName)) { string destFileName = url + dr["AttacheFile"].ToString(); style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; cells[i + templateIndex, 10].PutValue(FileName); cells[i + templateIndex, 10].SetStyle(style); sheet.Hyperlinks.Add(i + templateIndex, 10, 1, 1, destFileName); cells.Merge(i + templateIndex, 10, 1, 3); } } } #endregion }
/// <summary> /// 填充頁面數據 /// </summary> /// <param name="sheet">worksheet</param> /// <param name="docno">試產主單號</param> /// <param name="subdocno">試產從單號</param> private void BindExcel(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; //string logoPath = Page.Server.MapPath("") + "\\log.png"; //sheet.Pictures.Add(0, 0, 4, 10, logoPath); NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region//獲取主表資訊 DataTable dtMaster = oStandard.GetDFXInconformity(DocNo, "", ""); //string xmlReason = string.Empty; //string xmlPMC = string.Empty; //string xmlResult = string.Empty; //string xmlReasonDetail = string.Empty; if (dtMaster.Rows.Count > 0) { int templateIndexDFX = 5; //模板row起始位置 int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1); cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行 for (int i = 0; i < dtMaster.Rows.Count; i++) { DataRow dr = dtMaster.Rows[i]; cells[i + templateIndexDFX, 1].PutValue(dr["ItemType"].ToString()); cells[i + templateIndexDFX, 2].PutValue(dr["Item"].ToString()); cells[i + templateIndexDFX, 3].PutValue(dr["Location"].ToString()); cells[i + templateIndexDFX, 4].PutValue(dr["Requirements"].ToString()); cells[i + templateIndexDFX, 5].PutValue(""); cells[i + templateIndexDFX, 6].PutValue(dr["Compliance"].ToString()); cells[i + templateIndexDFX, 7].PutValue(dr["PriorityLevel"].ToString()); cells[i + templateIndexDFX, 8].PutValue(dr["MaxPoints"].ToString()); cells[i + templateIndexDFX, 9].PutValue(dr["DFXPoints"].ToString()); cells[i + templateIndexDFX, 10].PutValue(dr["Comments"].ToString()); cells[i + templateIndexDFX, 11].PutValue(dr["Actions"].ToString()); cells[i + templateIndexDFX, 12].PutValue(dr["CompletionDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["CompletionDate"].ToString()).ToString("yyyy/MM/dd"):dr["CompletionDate"].ToString()); cells[i + templateIndexDFX, 13].PutValue(dr["Tracking"].ToString()); cells[i + templateIndexDFX, 14].PutValue(dr["Remark"].ToString()); cells[i + templateIndexDFX, 15].PutValue(dr["WriteDept"].ToString()); } } #endregion }
private void BindPFMA(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; //string logoPath = Page.Server.MapPath("") + "\\log.png"; //sheet.Pictures.Add(0, 0, 4, 10, logoPath); NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region//獲取主表資訊 DataTable dtMaster = oStandard.GetFMEAInconformity(DocNo, "", "", ""); if (dtMaster.Rows.Count > 0) { int templateIndexDFX = 6; //模板row起始位置 int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1); cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行 for (int i = 0; i < dtMaster.Rows.Count; i++) { DataRow dr = dtMaster.Rows[i]; cells[i + templateIndexDFX, 1].PutValue(dr["Item"].ToString()); cells[i + templateIndexDFX, 2].PutValue(dr["Stantion"].ToString()); cells[i + templateIndexDFX, 3].PutValue(dr["Source"].ToString()); cells[i + templateIndexDFX, 4].PutValue(dr["Source"].ToString()); cells[i + templateIndexDFX, 5].PutValue(dr["PotentialFailureMode"].ToString()); cells[i + templateIndexDFX, 6].PutValue(dr["Loess"].ToString()); cells[i + templateIndexDFX, 8].PutValue(dr["Loess"].ToString()); cells[i + templateIndexDFX, 7].PutValue(dr["Sev"].ToString()); cells[i + templateIndexDFX, 8].PutValue(dr["Occ"].ToString()); cells[i + templateIndexDFX, 9].PutValue(dr["DET"].ToString()); cells[i + templateIndexDFX, 10].PutValue(dr["RPN"].ToString()); cells[i + templateIndexDFX, 11].PutValue(dr["PotentialFailure"].ToString()); cells[i + templateIndexDFX, 12].PutValue(dr["TargetCompletionDate"].ToString().Length > 0 ? Convert.ToDateTime(dr["TargetCompletionDate"].ToString()).ToString("yyyy/MM/dd") : dr["TargetCompletionDate"].ToString()); cells[i + templateIndexDFX, 13].PutValue(dr["ActionsTaken"].ToString()); cells[i + templateIndexDFX, 14].PutValue(dr["ResultsSev"].ToString()); cells[i + templateIndexDFX, 15].PutValue(dr["ResultsOcc"].ToString()); cells[i + templateIndexDFX, 16].PutValue(dr["ResultsDet"].ToString()); cells[i + templateIndexDFX, 17].PutValue(dr["ResultsRPN"].ToString()); cells[i + templateIndexDFX, 18].PutValue(dr["WriteDept"].ToString()); } } #endregion }
private void BindHomePage(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; //string logoPath = Page.Server.MapPath("") + "\\log.png"; //sheet.Pictures.Add(0, 0, 4, 10, logoPath); NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region//獲取主表資訊 DataTable dtMaster = oStandard.GetCLCAInconformity(DocNo, "", ""); if (dtMaster.Rows.Count > 0) { int templateIndexDFX = 7; //模板row起始位置 int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1); cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行 for (int i = 0; i < dtMaster.Rows.Count; i++) { DataRow dr = dtMaster.Rows[i]; cells[i + templateIndexDFX, 1].PutValue(dr["PROCESS"].ToString()); cells[i + templateIndexDFX, 2].PutValue(dr["CTQ"].ToString()); cells[i + templateIndexDFX, 3].PutValue(dr["CONTROL_TYPE"].ToString()); cells[i + templateIndexDFX, 4].PutValue(dr["ACT"].ToString()); cells[i + templateIndexDFX, 5].PutValue(dr["RESULT"].ToString()); cells[i + templateIndexDFX, 6].PutValue(dr["DESCRIPTION"].ToString()); cells[i + templateIndexDFX, 7].PutValue(dr["ROOT_CAUSE"].ToString()); cells[i + templateIndexDFX, 8].PutValue(dr["D"].ToString()); cells[i + templateIndexDFX, 9].PutValue(dr["M"].ToString()); cells[i + templateIndexDFX, 10].PutValue(dr["P"].ToString()); cells[i + templateIndexDFX, 11].PutValue(dr["E"].ToString()); cells[i + templateIndexDFX, 12].PutValue(dr["W"].ToString()); cells[i + templateIndexDFX, 13].PutValue(dr["O"].ToString()); cells[i + templateIndexDFX, 14].PutValue(dr["TEMPORARY_ACTION"].ToString()); cells[i + templateIndexDFX, 15].PutValue(dr["CORRECTIVE_PREVENTIVE_ACTION"].ToString()); cells[i + templateIndexDFX, 16].PutValue(dr["COMPLETE_DATE"].ToString().Length > 0 ? Convert.ToDateTime(dr["COMPLETE_DATE"].ToString()).ToString("yyyy/MM/dd") : dr["COMPLETE_DATE"].ToString()); cells[i + templateIndexDFX, 17].PutValue(dr["IMPROVEMENT_STATUS"].ToString()); } } #endregion }
//使用Aspose.Cells.dll, 可以不依靠Microsoft Excel也能灵活读写数据, 提供等同与Excel的功能 //避免实际实施过程中office版本问题造成过多的Bug //且能读取csv格式 /// <summary> /// 使用Aspose.Cells.dll, 可以不依靠Microsoft Excel也能灵活读写数据, 提供等同与Excel的功能 /// </summary> /// <param name="fileFullPath"></param> /// <param name="HDR">表格内是否包含列名</param> /// <returns></returns> public static System.Data.DataTable ExcelToDataTableByAspose(string fileFullPath, bool HDR) { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fileFullPath); Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = worksheet.Cells; //该方法得到的表格中,如果存在单元格内容第一个字符为0的纯数字字符串会忽略0 System.Data.DataTable dataTable = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, HDR); //该方法得到的表格中,如果存在单元格内容为10个以上的纯数字会将其变成科学记数法 //DataTable dataTable2= cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, HDR); workbook = null; worksheet = null; cells = null; GC.Collect(); return(dataTable); }
private void BindIssuesList(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu, string Building, string DocNo) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; //string logoPath = Page.Server.MapPath("") + "\\log.png"; //sheet.Pictures.Add(0, 0, 4, 10, logoPath); NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region//獲取主表資訊 DataTable dtMaster = oStandard.GetIssuesInconformity(DocNo, "", ""); if (dtMaster.Rows.Count > 0) { int templateIndexDFX = 6; //模板row起始位置 int insertIndexEnCounter = templateIndexDFX + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtMaster.Rows.Count - 1); cells.CopyRows(cells, templateIndexDFX, insertIndexEnCounter, dtMaster.Rows.Count - 1); //複製模板row格式至新行 for (int i = 0; i < dtMaster.Rows.Count; i++) { DataRow dr = dtMaster.Rows[i]; cells[i + templateIndexDFX, 1].PutValue(dr["Items"].ToString()); cells[i + templateIndexDFX, 2].PutValue(dr["STATION"].ToString()); cells[i + templateIndexDFX, 3].PutValue(dr["ISSUE_DESCRIPTION"].ToString()); cells[i + templateIndexDFX, 4].PutValue(dr["FILE_PATH"].ToString()); cells[i + templateIndexDFX, 5].PutValue(dr["ISSUE_LOSSES"].ToString()); cells[i + templateIndexDFX, 6].PutValue(dr["TEMP_MEASURE"].ToString()); cells[i + templateIndexDFX, 7].PutValue(dr["IMPROVE_MEASURE"].ToString()); cells[i + templateIndexDFX, 8].PutValue(dr["CURRENT_STATUS"].ToString()); cells[i + templateIndexDFX, 9].PutValue(dr["TRACKING"].ToString()); cells[i + templateIndexDFX, 10].PutValue(dr["REMARK"].ToString()); } } #endregion }
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); }
protected void btnSure_Click(object sender, EventArgs e) { if (upctrlPsn.UploadedFiles[0].IsValid && upctrlPsn.HasFile) { commonFunc.SaveFile(upctrlPsn, this); string savePath = commonFunc.GetSavePath(upctrlPsn, this); Aspose.Cells.Workbook xlsPsn = new Aspose.Cells.Workbook(savePath); //xlsPsn.Open(savePath); Aspose.Cells.Cells cellsPsn = xlsPsn.Worksheets[0].Cells; var dtTemp = cellsPsn.ExportDataTable(0, 0, cellsPsn.MaxDataRow + 1, cellsPsn.MaxDataColumn + 1); User usersCrud = new User(); SF_User user = new SF_User(); if (ddlUserGroup.SelectedValue != "-1" && ddlRole.SelectedValue != "-1") { int usercount = 0; int usercount2 = 0; string userName = ""; user.PassWord = SecurityEncryption.MD5("123456", 32); int roleID = 0; roleID = Convert.ToInt32(ddlRole.SelectedValue); user.UserGroupID = Convert.ToInt32(ddlUserGroup.SelectedValue); for (int i = 1; i < dtTemp.Rows.Count; i++) { user.UserName = dtTemp.Rows[i][0].ToString().Trim(); user.PersonNumber = dtTemp.Rows[i][0].ToString().Trim(); if (!usersCrud.UserExists(user.UserName)) { int n = (int)usersCrud.CreateUser(user); if (n >= 1) { if (roleID != 0) { usersCrud.AddUserRole(n, roleID); } } usercount++; } else { if (cellsPsn.Columns.Count >= 3 && dtTemp.Rows[i][3].ToString().Trim() != "") { SF_User user2 = usersCrud.GetUserModel(user.UserName); user2.UserName = dtTemp.Rows[i][3].ToString().Trim(); int n2 = (int)usersCrud.UpdateUser(user2); if (n2 >= 1) { usercount2++; } } userName += user.UserName + ","; } } commonFunc.DeleteFile(savePath); JSHelper.Alert(string.Format("共添加{0}个用户!共更新{1}个用户!", usercount, usercount2), this); } else { JSHelper.Alert("初始化信息请填写完整!", this); } } }
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 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 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); }
private async void ExcuteImpData(string token) { int dbCatograyCount = 0; int dbLawClauseCount = 0; int dbWordsCount = 0; int dbWordsRelationCount = 0; int dbCatograyTotalCount = 0; int dbLawClauseTotalCount = 0; int dbWordsTotalCount = 0; int dbWordsRelationTotalCount = 0; Task task = Task.Run(() => { try { Aspose.Cells.Workbook workbookName = new Aspose.Cells.Workbook(strFileName); int sheetCount = workbookName.Worksheets.Count; for (int iSheet = 0; iSheet < sheetCount; iSheet++) { string sheetName = workbookName.Worksheets[iSheet].Name; if (sheetName == "Type表") { Aspose.Cells.Cells cellsName1 = workbookName.Worksheets[iSheet].Cells; int minDataRow = cellsName1.MinDataRow; int minDataColumn = cellsName1.MinDataColumn; int maxDataRow = cellsName1.MaxDataRow; int maxDataColumn = cellsName1.MaxDataColumn; int typeId = 0; int typeName = 0; for (int j = minDataColumn; j < maxDataColumn + 1; j++) { string s = cellsName1[minDataRow, j].StringValue.Trim(); if (s == "Type ID") { typeId = j; } else if (s == "Type Name") { typeName = j; } } //导入数据 for (int i = minDataRow + 1; i < maxDataRow + 1; i++) { try { WordsCategoryInfo info = new WordsCategoryInfo(); info.typeId = cellsName1[i, typeId].StringValue.Trim(); info.typeName = cellsName1[i, typeName].StringValue.Trim(); APIService service = new APIService(); service.ImpWordsCategoryData(info, token); dbCatograyTotalCount++; } catch (Exception ex) { dbCatograyCount++; } Dispatcher.Invoke(() => { DBCatograyCount.Text = dbCatograyCount.ToString() + "( 累计" + dbCatograyTotalCount + "条数据)"; }); } } else if (sheetName == "LawClause") { Aspose.Cells.Cells cellsName1 = workbookName.Worksheets[iSheet].Cells; int minDataRow = cellsName1.MinDataRow; int minDataColumn = cellsName1.MinDataColumn; int maxDataRow = cellsName1.MaxDataRow; int maxDataColumn = cellsName1.MaxDataColumn; int clauseID = 0; int decription = 0; int typeId = 0; for (int j = minDataColumn; j < maxDataColumn + 1; j++) { string s = cellsName1[minDataRow, j].StringValue.Trim(); if (s == "Clause ID") { clauseID = j; } else if (s == "decription") { decription = j; } else if (s == "Type ID") { typeId = j; } } //导入数据 for (int i = minDataRow + 1; i < maxDataRow + 1; i++) { try { LawClauseInfo info = new LawClauseInfo(); info.clauseId = cellsName1[i, clauseID].StringValue.Trim(); info.description = cellsName1[i, decription].StringValue.Trim(); info.typeId = cellsName1[i, typeId].StringValue.Trim(); APIService service = new APIService(); service.ImpLawClauseData(info, token); dbLawClauseTotalCount++; } catch (Exception ex) { dbLawClauseCount++; } Dispatcher.Invoke(() => { DBLawClauseCount.Text = dbLawClauseCount.ToString() + "( 累计" + dbLawClauseTotalCount + "条数据)"; }); } } else if (sheetName == "Word") { Aspose.Cells.Cells cellsName1 = workbookName.Worksheets[iSheet].Cells; int minDataRow = cellsName1.MinDataRow; int minDataColumn = cellsName1.MinDataColumn; int maxDataRow = cellsName1.MaxDataRow; int maxDataColumn = cellsName1.MaxDataColumn; int wordID = 0; int name = 0; int sensitive = 0; int clauseID = 0; for (int j = minDataColumn; j < maxDataColumn + 1; j++) { string s = cellsName1[minDataRow, j].StringValue.Trim(); if (s == "Word ID") { wordID = j; } else if (s == "name") { name = j; } else if (s == "sensitive") { sensitive = j; clauseID = sensitive + 1; } } //导入词数据 for (int i = minDataRow + 1; i < maxDataRow + 1; i++) { try { WordsInfo info = new WordsInfo(); info.wordId = cellsName1[i, wordID].StringValue.Trim(); info.name = cellsName1[i, name].StringValue.Trim(); info.sensitive = cellsName1[i, sensitive].StringValue.Trim() == "Y" ? true : false; info.official = true; info.comment = ""; APIService service = new APIService(); service.ImpWordsData(info, token); dbWordsTotalCount++; } catch (Exception ex) { dbWordsCount++; } Dispatcher.Invoke(() => { DBWordsCount.Text = dbWordsCount.ToString() + "( 累计" + dbWordsTotalCount + "条数据)"; }); } //导入关系数据 for (int i = minDataRow + 1; i < maxDataRow + 1; i++) { try { WordsRelationInfo info = new WordsRelationInfo(); info.clauseIds = new List <string>(); info.wordId = cellsName1[i, wordID].StringValue.Trim(); for (int j = clauseID; j < maxDataColumn + 1; j++) { string clauseId = cellsName1[i, j].StringValue.Trim(); if (!string.IsNullOrEmpty(clauseId)) { if (!info.clauseIds.Contains(clauseId)) { info.clauseIds.Add(clauseId); } } } APIService service = new APIService(); service.ImpWordsRelationData(info, token); dbWordsRelationTotalCount++; } catch (Exception ex) { dbWordsRelationCount++; } Dispatcher.Invoke(() => { DBWordsRelationCount.Text = dbWordsRelationCount.ToString() + "( 累计" + dbWordsRelationTotalCount + "条数据)"; }); } } } } catch (Exception ex) { } Dispatcher.Invoke(() => { BusyGrid.Visibility = Visibility.Collapsed; }); }); await task; }
/// <summary> /// students方法 /// </summary> public void students() { HttpPostedFile postedFile = context.Request.Files["Filedata"]; string ext = context.Request["ext"].ToString(); string author = ""; string school = ""; try { author = Functions.tostr(context.Request["ctemen"].ToString()); } catch { author = ""; } try { school = Functions.tostr(context.Request["school"].ToString()); } catch { school = ""; } string filepath = context.Server.MapPath("/"); filepath += @"\upload\cache"; if (!Directory.Exists(filepath)) { Directory.CreateDirectory(filepath); } filepath += @"\cache_" + DateTime.Now.Millisecond + "." + ext; SqlConnection conn = new SqlConnection(Functions.connstr()); if (conn.State != ConnectionState.Open) { conn.Open(); } try { postedFile.SaveAs(filepath); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filepath); Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells; DataTable dt = cells.ExportDataTable(1, 0, cells.MaxRow, stuColums.Length); DataTable dt1 = new DataTable(); for (int i = 0; i < stuColums.Length; i++) { dt1.Columns.Add(stuColums[i]); } int counter = 0; for (int i = counter; i < dt.Rows.Count; i++) { if ((dt.Rows[i][0] != null && dt.Rows[i][0].ToString() != "") && (dt.Rows[i][4] != null && dt.Rows[i][4].ToString() != "")) { dt1.Rows.Add(dt.Rows[i].ItemArray); dt1.Rows[i - counter]["bs_id"] = Functions.getprimarykey("ae_a.dbo.busi_students", conn); dt1.Rows[i - counter]["bs_createmen"] = author == "" ? "系统导入" : (author + "-导入"); dt1.Rows[i - counter]["bs_createtime"] = DateTime.Now; dt1.Rows[i - counter]["bs_sclname"] = school; } else { counter++; } } using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(Functions.connstr())) { bcp.DestinationTableName = "ae_a.dbo.busi_students"; for (int i = 0; i < stuColums.Length; i++) { bcp.ColumnMappings.Add(stuColums[i], stuColums[i]); } bcp.WriteToServer(dt1); } context.Response.Write("OK"); } catch (Exception e) { context.Response.Write("-1," + e.Message); } File.Delete(filepath); context.Response.End(); }
public static bool DataSetToExcel(System.Data.DataSet data, string filepath, out string error) { error = ""; try { if (data == null || data.Tables.Count < 1) { error = "DataTableToExcel:datatable 为空"; return(false); } Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); workbook.Worksheets.RemoveAt(0); for (int i = 0; i < data.Tables.Count; i++) { var datatable = data.Tables[i]; Aspose.Cells.Worksheet sheet = workbook.Worksheets.Add(data.Tables[i].TableName); Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; try { for (int j = 0; j < datatable.Columns.Count; j++) { cells[nRow, j].PutValue(datatable.Columns[j].ColumnName); //Aspose.Cells.Style style = new Aspose.Cells.Style(); //style.Font.IsBold = true; //style.Font.Size = 11; //cells[nRow, j].SetStyle(style); } } catch (System.Exception e) { error = error + " DataSetToExcel: " + e.Message; } foreach (DataRow row in datatable.Rows) { nRow++; try { for (int j = 0; j < datatable.Columns.Count; j++) { if (row[j].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[j]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, j, mstream); } else if (row[j].GetType().ToString() == "System.DateTime") { var time = Convert.ToDateTime(row[j]); if (time == DateTime.MinValue || time == DateTime.MaxValue || time == DateTime.Parse("1900-01-01 00:00:00")) { cells[nRow, j].PutValue(string.Empty); } else { cells[nRow, j].PutValue(time.ToString("yyyy-MM-dd HH:mm:ss")); } //cells[nRow, j].PutValue(time); } else { cells[nRow, j].PutValue(row[j]); } } } catch (System.Exception e) { error = error + " DataSetToExcel: " + e.Message; } } } workbook.Save(filepath); return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
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); }
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 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> /// 导出DataTable到Excel中 /// </summary> /// <param name="datatable"></param> /// <param name="filepath"></param> /// <param name="error"></param> /// <returns></returns> public static bool DataTableToExcel(DataTable datatable, string filepath, out string error) { error = ""; try { if (datatable == null) { error = "DataTableToExcel:datatable 为空"; return(false); } Aspose.Cells.License license = new Aspose.Cells.License(); string licenseFile = ConfigurationManager.AppSettings["BASEDIRECTORY"].ToString() + "\\bin\\celllic.lic"; license.SetLicense(licenseFile); Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); Aspose.Cells.Worksheet sheet = workbook.Worksheets[0]; Aspose.Cells.Cells cells = sheet.Cells; int nRow = 0; for (byte i = 0; i < datatable.Columns.Count; i++) { cells[nRow, i].PutValue(datatable.Columns[i]); } Aspose.Cells.Range w; w = cells.CreateRange(nRow, 0, 1, datatable.Columns.Count); foreach (DataRow row in datatable.Rows) { nRow++; try { for (int i = 0; i < datatable.Columns.Count; i++) { if (row[i].GetType().ToString() == "System.Drawing.Bitmap") { //------插入图片数据------- System.Drawing.Image image = (System.Drawing.Image)row[i]; MemoryStream mstream = new MemoryStream(); image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg); sheet.Pictures.Add(nRow, i, mstream); } else { cells[nRow, i].PutValue(row[i]); } } } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; } } if (filepath.IndexOf(".xls") > -1) { workbook.Save(filepath); } else { workbook.Save(filepath, Aspose.Cells.FileFormatType.CSV); } return(true); } catch (System.Exception e) { error = error + " DataTableToExcel: " + e.Message; return(false); } }
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 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()); } }
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数据格式不正确,请下载标准模板重新填写!"); } }
private void BindHomePage(ref Aspose.Cells.Worksheet sheet, string caseID, string Bu) { //page 格式設定 SetStyle(ref sheet, Aspose.Cells.PageOrientationType.Landscape); Aspose.Cells.Cells cells = sheet.Cells; NPIMgmt oMgmt = new NPIMgmt("CZ", Bu); NPI_Standard oStandard = oMgmt.InitialLeaveMgmt(); #region 主檔資訊 DataTable dt = oStandard.GetPrelaunchMaster(caseID); if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; sheet.Replace("{FormNo}", dr["PilotRunNO"].ToString()); sheet.Replace("{Model}", dr["Model"].ToString()); sheet.Replace("{Customer}", dr["Customer"].ToString()); sheet.Replace("{PCBRev}", dr["PCBInRev"].ToString()); sheet.Replace("{Rev}", dr["PLRev"].ToString()); sheet.Replace("{Date}", Convert.ToDateTime(dr["Date"].ToString()).ToString("yyyy/MM/dd")); sheet.Replace("{TPME}", dr["TP_ME"].ToString()); sheet.Replace("{TPEE}", dr["TP_EE"].ToString()); sheet.Replace("{TPPM}", dr["TP_PM"].ToString()); sheet.Replace("{PM}", dr["PM"].ToString()); sheet.Replace("{PCBORev}", dr["PCBOutRev"].ToString()); sheet.Replace("{Attection}", dr["Notes"].ToString()); sheet.Replace("{WorkOrder}", dr["WorkOrder"].ToString()); } DataTable dtResult = GetNPIManager(caseID); DataRow drResult = dtResult.Rows[0]; sheet.Replace("{FinalResult}", drResult["APPROVE_RESULT"].ToString() == "Y" ? "Pass" : "Fail"); #endregion #region 簽核資訊 DataTable dtApprover = oStandard.GetPrelaunchApproveResult(caseID); if (dtApprover.Rows.Count > 0) { int templateIndex = 17; //模板row起始位置 int insertIndexEnCounter = templateIndex + 1; //new row起始位置 cells.InsertRows(insertIndexEnCounter, dtApprover.Rows.Count - 1); cells.CopyRows(cells, templateIndex, insertIndexEnCounter, dtApprover.Rows.Count - 1); //複製模板row格式至新行 for (int i = 0; i < dtApprover.Rows.Count; i++) { DataRow dr = dtApprover.Rows[i]; cells[i + templateIndex, 0].PutValue(dr["STEP_NAME"].ToString()); cells[i + templateIndex, 1].PutValue(dr["DEPT"].ToString()); cells[i + templateIndex, 2].PutValue(dr["HANDLER"].ToString()); cells[i + templateIndex, 3].PutValue(dr["APPROVE_TIME"].ToString().Length > 0 ? Convert.ToDateTime(dr["APPROVE_TIME"].ToString()).ToString("yyyy/MM/dd") : dr["APPROVE_TIME"].ToString()); cells[i + templateIndex, 4].PutValue(dr["APPROVE_RESULT"].ToString().Length > 0 ? dr["APPROVE_RESULT"].ToString() : "Y"); cells[i + templateIndex, 5].PutValue(dr["APPROVE_REMARK"].ToString()); } } #endregion }
/// <summary> /// 开始处理文件 /// </summary> private DataLayer.Model.FileReplace_File DoIt(DataLayer.Model.FileReplace_File model) { Stopwatch sw = new Stopwatch(); sw.Start(); int replaceCount = 0; Regex reg = null; List <string> strRemark = new List <string>(); model.IsDone = true; Aspose.Cells.Cell currentCell = null; Aspose.Cells.CellValueType currentCellType; string cellValue = string.Empty; bool tempBool; int tempInt; double tempDouble; DateTime tempDateTime; bool isCellReplaced = false; int cellMatchCount; bool isDefaultExt = defaultExt.Contains(model.ExtensionName); bool isExcelExt = excelExt.Contains(model.ExtensionName); bool isDocExt = docExt.Contains(model.ExtensionName); //bool isPPTExt = pptExt.Contains(model.ExtensionName); //bool isPdfExt = pdfExt.Contains(model.ExtensionName); bool isTxtFile = XCLNetTools.FileHandler.ComFile.IsTextFile(model.Path); bool isNeedCopy = !string.IsNullOrEmpty(this.txtOutPutPath.Text); string realPath = model.Path; //被操作的文件实际路径,如果没有指定输出目录,则为原路径,如果指定了输出目录,则为copy到输出目录中后的路径 string filetitle = XCLNetTools.FileHandler.ComFile.GetFileName(model.Path, false); //文件名,不含扩展名 string newFileTitle = string.Empty; if (!System.IO.File.Exists(model.Path)) { model.Remark = "文件不存在!"; model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.无需处理; return(model); } if (string.IsNullOrEmpty(model.ExtensionName)) { model.Remark = "无法确认文件类型!"; model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.无需处理; return(model); } try { #region 先处理替换文件名 for (int ruleIndex = 0; ruleIndex < this.dataGridRuleConfig.Rows.Count; ruleIndex++) { var ruleModel = XCLNetTools.Generic.ListHelper.DataRowToModel <DataLayer.Model.v_FileReplace_RuleConfig>(((DataRowView)dataGridRuleConfig.Rows[ruleIndex].DataBoundItem).Row); if (null == ruleModel || !ruleModel.IsFileName) { continue; } #region 是否启用正则替换 if (ruleModel.IsRegex) { reg = ruleModel.IsIgnoreCase ? new Regex(ruleModel.OldContent, RegexOptions.IgnoreCase) : new Regex(ruleModel.OldContent); } else { string newExpStr = ruleModel.IsWholeMatch ? string.Format(@"\b{0}\b", Regex.Escape(ruleModel.OldContent)) : Regex.Escape(ruleModel.OldContent); reg = ruleModel.IsIgnoreCase ? new Regex(newExpStr, RegexOptions.IgnoreCase) : new Regex(newExpStr); } #endregion 是否启用正则替换 #region 判断是否替换文件名 replaceCount = reg.Matches(filetitle).Count; strRemark.Add(string.Format("规则【{0}】文件名替换【{1}】处;", ruleModel.Name, replaceCount)); filetitle = reg.Replace(filetitle, ruleModel.NewContent); model.ProcessBlockCount += replaceCount; #endregion 判断是否替换文件名 } filetitle = string.Format("{0}{1}{2}", this.txtFileFirstName.Text, filetitle, this.txtFileLastName.Text); bool isFileNameChanged = !string.Equals(XCLNetTools.FileHandler.ComFile.GetFileName(model.Path, false), filetitle, StringComparison.OrdinalIgnoreCase); if (isNeedCopy) { if (isFileNameChanged) { realPath = XCLNetTools.FileHandler.ComFile.GetFileFolderPath(model.Path) + "\\" + filetitle + "." + model.ExtensionName; } realPath = realPath.Replace(this.openFileFolderPath.TrimEnd('\\'), this.txtOutPutPath.Text.TrimEnd('\\')); XCLNetTools.FileHandler.ComFile.CopyFile(model.Path, realPath); if (!System.IO.File.Exists(realPath)) { model.Remark = "复制到输出目录执行失败!"; model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理失败; return(model); } } else { if (isFileNameChanged) { newFileTitle = filetitle + "." + model.ExtensionName; this.pc.FileSystem.RenameFile(model.Path, newFileTitle); realPath = XCLNetTools.FileHandler.ComFile.ChangePathByFileName(realPath, newFileTitle); } } #endregion 先处理替换文件名 #region 替换文件内容 Aspose.Cells.Workbook wb = null; Aspose.Words.Document wordDocument = null; string textContent = null; for (int ruleIndex = 0; ruleIndex < this.dataGridRuleConfig.Rows.Count; ruleIndex++) { var ruleModel = XCLNetTools.Generic.ListHelper.DataRowToModel <DataLayer.Model.v_FileReplace_RuleConfig>(((DataRowView)dataGridRuleConfig.Rows[ruleIndex].DataBoundItem).Row); if (null == ruleModel || !ruleModel.IsFileContent) { continue; } #region 验证扩展名及是否为文本文件 if (!isDefaultExt && !isTxtFile && ruleModel.IsFileContent) { //非aspose能处理的文件,且非文本文件,则不能替换内容! strRemark.Add(string.Format("规则【{0}】不支持替换该文件的内容!", ruleModel.Name)); continue; } #endregion 验证扩展名及是否为文本文件 #region 是否启用正则替换 if (ruleModel.IsRegex) { reg = ruleModel.IsIgnoreCase ? new Regex(ruleModel.OldContent, RegexOptions.IgnoreCase) : new Regex(ruleModel.OldContent); } else { string newExpStr = ruleModel.IsWholeMatch ? string.Format(@"\b{0}\b", Regex.Escape(ruleModel.OldContent)) : Regex.Escape(ruleModel.OldContent); reg = ruleModel.IsIgnoreCase ? new Regex(newExpStr, RegexOptions.IgnoreCase) : new Regex(newExpStr); } #endregion 是否启用正则替换 #region 开始替换文件内容 if (isDefaultExt) { if (isExcelExt) { #region 处理excel文件 if (null == wb) { wb = new Aspose.Cells.Workbook(realPath); } for (int i = 0; i < wb.Worksheets.Count; i++) { Aspose.Cells.Cells sheetCells = wb.Worksheets[i].Cells; for (int cellsRowIndex = 0; cellsRowIndex < sheetCells.MaxDataRow + 1; cellsRowIndex++) { for (int cellsColumn = 0; cellsColumn < sheetCells.MaxDataColumn + 1; cellsColumn++) { currentCell = sheetCells[cellsRowIndex, cellsColumn]; if (currentCell.IsFormula && this.ckExcelOptionIsKeepFormula.Checked) { continue; } cellValue = Convert.ToString(currentCell.Value); if (string.IsNullOrEmpty(cellValue)) { continue; } cellMatchCount = reg.Matches(cellValue).Count; if (cellMatchCount == 0) { continue; } var cellCharacters = currentCell.GetCharacters(); cellValue = reg.Replace(cellValue, ruleModel.NewContent); currentCellType = this.ckExcelOptionIsKeepDataFormat.Checked ? currentCell.Type : Aspose.Cells.CellValueType.IsString; switch (currentCellType) { case Aspose.Cells.CellValueType.IsBool: if (bool.TryParse(cellValue, out tempBool)) { currentCell.PutValue(tempBool); isCellReplaced = true; } break; case Aspose.Cells.CellValueType.IsDateTime: if (DateTime.TryParse(cellValue, out tempDateTime)) { currentCell.PutValue(tempDateTime); isCellReplaced = true; } break; case Aspose.Cells.CellValueType.IsNumeric: if (int.TryParse(cellValue, out tempInt)) { currentCell.PutValue(tempInt); isCellReplaced = true; } else { if (double.TryParse(cellValue, out tempDouble)) { currentCell.PutValue(tempDouble); isCellReplaced = true; } } break; default: currentCell.PutValue(cellValue); isCellReplaced = true; break; } if (isCellReplaced) { if (null != cellCharacters && cellCharacters.Length > 0) { currentCell.SetCharacters(cellCharacters); } replaceCount += cellMatchCount; } } } } #endregion 处理excel文件 } else if (isDocExt) { #region 处理word if (null == wordDocument) { wordDocument = new Aspose.Words.Document(realPath); } var finder = new FindReplaceOptions(); replaceCount = wordDocument.Range.Replace(reg, ruleModel.NewContent, finder); #endregion 处理word } //else if (isPPTExt) //{ // #region 处理PPT // Aspose.Slides.Pptx.PresentationEx pptPres = new Aspose.Slides.Pptx.PresentationEx(realPath); // #endregion //} //else if (isPdfExt) //{ // #region 处理pdf文件 // Aspose.Pdf.Kit.PdfContentEditor pdfEditor = new Aspose.Pdf.Kit.PdfContentEditor(); // pdfEditor.BindPdf(realPath); // pdfEditor.ReplaceText(this.txtOldValue.Text, this.txtNew.Text); // pdfEditor.Save(realPath); // #endregion //} } else { #region 处理文本文件 if (null == textContent) { textContent = System.IO.File.ReadAllText(realPath, System.Text.Encoding.Default) ?? ""; } replaceCount = reg.Matches(textContent).Count; textContent = reg.Replace(textContent, ruleModel.NewContent); #endregion 处理文本文件 } strRemark.Add(string.Format("规则【{0}】文件内容替换【{1}】处;", ruleModel.Name, replaceCount)); #endregion 开始替换文件内容 this.SetTextLogValue(string.Format("正在处理文件【{0}】,应用规则【{1}】", model.FileName, ruleModel.Name)); model.ProcessBlockCount += replaceCount; } if (null != wb) { if (this.ckExcelOptionIsKeepFormula.Checked) { wb.CalculateFormula(); } wb.Save(realPath); } if (null != wordDocument) { wordDocument.Save(realPath); } if (null != textContent) { System.IO.File.WriteAllText(realPath, textContent, System.Text.Encoding.Default); } #endregion 替换文件内容 if (strRemark.Count > 0) { model.Remark = string.Join(";", strRemark.ToArray()); } model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理成功; } catch (Exception e) { model.ProcessState = (int)DataLayer.Common.DataEnum.FileReplace_File_ProcessStateEnum.处理失败; model.Remark = e.Message; } finally { sw.Stop(); model.ProcessDuration = (int)sw.Elapsed.TotalSeconds; } if (string.IsNullOrWhiteSpace(model.Remark)) { this.SetTextLogValue(string.Format("文件【{0}】处理完毕", model.FileName)); } else { this.SetTextLogValue(string.Format("文件【{0}】处理完毕({1})", model.FileName, model.Remark)); } return(model); }
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); }