/// <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);
}
