public ActionResult UploadHRFile() { string pathError = Path.Combine(Server.MapPath("~/Assets/HRTemplates/"), "aaa.txt"); try { string foldername = "TmpHRDocuments"; string strQuery = ""; strQuery = "SELECT CAST(0 as INT) stt_dong, CAST('' as varchar(max)) file_name_link, CAST('' as varchar(max)) as khoa, CAST('' as nvarchar(max)) as month, CAST('' as nvarchar(max)) as email, CAST('' as nvarchar(max)) as file_name_sent, * FROM hrdocument WHERE 1 = 0"; DataSet dsResult = _SysModel.GetDataSetQuery(strQuery); DataTable result = dsResult.Tables[0]; foreach (string file in Request.Files) { string fileName = Guid.NewGuid().ToString() + Request.Files[file].FileName.Substring(Request.Files[file].FileName.LastIndexOf('.')); var path = Path.Combine(Server.MapPath("~/Assets/" + foldername), fileName); if (!Directory.Exists(Server.MapPath("~/Assets/" + foldername))) { Directory.CreateDirectory(Server.MapPath("~/Assets/" + foldername)); } Request.Files[file].SaveAs(path); DataSet ds = _SysModel.ExcelToDataSet(path, pathError); System.IO.File.Delete(path); strQuery = "SELECT user_name, duong_dan, duong_dan_email, emailsetting_email, emailsetting_pass, emailsetting_host, emailsetting_port, e_mail FROM v_userinfo WHERE user_name IN ("; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (ds.Tables[0].Rows[i]["user_name"].ToString().Trim() != "") { if (i > 0) { strQuery += ","; } strQuery += "'" + ds.Tables[0].Rows[i]["user_name"].ToString() + "'"; } } strQuery += ")"; DataSet dstemplate = _SysModel.GetDataSetQuery(strQuery); DataTable template = dstemplate.Tables[0]; int stt_dong = 1; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow _data = ds.Tables[0].Rows[i]; for (int j = 0; j < template.Rows.Count; j++) { if (ds.Tables[0].Rows[i]["user_name"].ToString().ToUpper() == template.Rows[j]["user_name"].ToString().ToUpper()) { fileName = Guid.NewGuid().ToString() + ".pdf"; var fileSource = Path.Combine(Server.MapPath("~/Assets/HRTemplates/"), template.Rows[j]["duong_dan"].ToString().Substring(template.Rows[j]["duong_dan"].ToString().LastIndexOf('/') + 1)); var dotIndex = fileSource.LastIndexOf('.'); var extension = fileSource.Substring(dotIndex).ToLower(); var fileDest = Path.Combine(Server.MapPath("~/Assets/HRTemplates/"), Guid.NewGuid().ToString() + extension); System.IO.File.Copy(fileSource, fileDest, true); //replate template string[] findTexts = new string[ds.Tables[0].Columns.Count]; string[] replaceWithTexts = new string[ds.Tables[0].Columns.Count]; for (int k = 0; k < ds.Tables[0].Columns.Count; k++) { findTexts[k] = "{" + ds.Tables[0].Columns[k].ColumnName + "}"; if (ds.Tables[0].Columns[k].DataType == typeof(string)) { replaceWithTexts[k] = _data[ds.Tables[0].Columns[k].ColumnName].ToString(); } if (ds.Tables[0].Columns[k].DataType == typeof(double)) { replaceWithTexts[k] = Convert.ToDecimal(_data[ds.Tables[0].Columns[k].ColumnName]).ToString("#,0"); } } _SysModel.FindAndReplace(fileDest, findTexts, replaceWithTexts); Session["HRDocumentfindTexts"] = findTexts; Session["HRDocumentreplaceWithTexts"] = replaceWithTexts; dotIndex = fileDest.LastIndexOf('.'); extension = fileDest.Substring(dotIndex + 1).ToLower(); _SysModel.ConvertToPDF(fileDest, Server.MapPath("~/Assets/" + foldername + @"/") + fileName, extension, pathError); System.IO.File.Delete(fileDest); DataRow dr = result.NewRow(); dr["stt_dong"] = stt_dong; stt_dong++; dr["hr_id"] = Guid.NewGuid().ToString(); dr["file_name_link"] = "download"; dr["file_name"] = @"/Assets/" + foldername + @"/" + fileName; dr["user_name"] = template.Rows[j]["user_name"].ToString(); dr["user_id0"] = Session["UserLoginUser_id"]; dr["datetime0"] = DateTime.Now; dr["user_id2"] = Session["UserLoginUser_id"]; dr["datetime2"] = DateTime.Now; dr["branch_id"] = Session["UserLoginBranch_id"]; dr["khoa"] = ds.Tables[0].Rows[i]["key"].ToString(); dr["month"] = ds.Tables[0].Rows[i]["month"].ToString(); dr["file_name_sent"] = ds.Tables[0].Rows[i]["staff_code"].ToString().Trim() + " - " + _SysModel.RemoveSign4VietnameseString(ds.Tables[0].Rows[i]["comment"].ToString().Trim()) + "-" + ds.Tables[0].Rows[i]["month"].ToString() + ".pdf"; dr["email"] = template.Rows[j]["e_mail"].ToString().ToUpper(); result.Rows.Add(dr); } } } Session["HRDocument"] = _SysModel.ConvertTableToDynamic(dsResult); Session["HRDocumentTemplate"] = _SysModel.ConvertTableToDynamic(dstemplate); } return PartialView("HRDocumentGrid", (List)Session["HRDocument"]); } catch (Exception ex) { if (!System.IO.File.Exists(pathError)) { System.IO.File.Create(pathError).Dispose(); using (TextWriter tw = new StreamWriter(pathError)) { tw.WriteLine(ex.ToString()); } } else { using (TextWriter tw = new StreamWriter(pathError)) { tw.WriteLine(ex.ToString()); } } return PartialView("HRDocumentGrid", null); } } public void FindAndReplace(string file, string[] findTexts, string[] replaceWithTexts) { DevExpress.XtraRichEdit.RichEditDocumentServer _word = new DevExpress.XtraRichEdit.RichEditDocumentServer(); DevExpress.XtraRichEdit.DocumentFormat _format = DevExpress.XtraRichEdit.DocumentFormat.OpenXml; if (file.EndsWith("doc")) _format = DevExpress.XtraRichEdit.DocumentFormat.Doc; _word.LoadDocument(file, _format); for(int i = 0;i< findTexts.Length;i++) { string findText = findTexts[i]; string replaceWithText = replaceWithTexts[i]; DevExpress.XtraRichEdit.API.Native.DocumentRange[] dd = _word.Document.FindAll(findText, DevExpress.XtraRichEdit.API.Native.SearchOptions.CaseSensitive, _word.Document.Range); foreach (DevExpress.XtraRichEdit.API.Native.DocumentRange drange in dd) { _word.Document.InsertText(drange.Start, replaceWithText); _word.Document.Delete(drange); } } _word.SaveDocument(file, _format); } public void FindAndReplaceExcel(string file, string[] findTexts, string[] replaceWithTexts, DataTable tbDetail1, DataTable tbDetail2) { Workbook workbook = new Workbook(); workbook.LoadDocument(file); workbook.Calculate(); Worksheet worksheet = workbook.Worksheets[0]; workbook.Worksheets.ActiveWorksheet = worksheet; SearchOptions options = new SearchOptions(); options.SearchBy = SearchBy.Columns; options.SearchIn = SearchIn.Values; //options.MatchEntireCellContents = true; // Find all cells containing today's date and paint them light-green. for (int i = 0; i < findTexts.Length; i++) { string findText = findTexts[i]; string replaceWithText = replaceWithTexts[i]; IEnumerable searchResult = worksheet.Search(findText, options); foreach (Cell cell in searchResult) { string value = cell.Value.TextValue; cell.Value = value.Replace(findText, replaceWithText); } } //Detail IEnumerable searchResult2 = worksheet.Search("{Detail}", options); if (searchResult2.Count() == 1) { Cell cell = searchResult2.ElementAt(0); int rowindex = cell.RowIndex; int colcount = worksheet.GetUsedRange().ColumnCount; for (int j = 0; j < tbDetail1.Rows.Count; j++) { DataRow dr = tbDetail1.Rows[j]; worksheet.Rows.Insert(rowindex + j + 1); for (int i = 0; i < colcount; i++) { string value = worksheet.Cells[rowindex, i].Value.TextValue; if (value == null || value == "") continue; int startindex = value.LastIndexOf("("); int endindex = value.LastIndexOf(")"); if (startindex == -1 || endindex == -1) continue; string field = value.Substring(startindex + 1, endindex - startindex - 1); if (field.StartsWith("Field")) { field = field.Substring(field.IndexOf(".") + 1); DataRow[] drs = tbDetail2.Select(string.Format("expense_id = '{0}' AND field_name = '{1}'", dr["expense_id"].ToString(), field)); if (drs.Length > 0) { worksheet.Cells[rowindex + j + 1, i].Value = drs[0]["field_value"].ToString(); } } else { if (tbDetail1.Columns.Contains(field)) { if (tbDetail1.Columns[field].DataType == typeof(double) || tbDetail1.Columns[field].DataType == typeof(decimal)) { if (dr[field] != DBNull.Value) { worksheet.Cells[rowindex + j + 1, i].Value = CellValue.FromObject(Convert.ToDecimal(dr[field])); //worksheet.Cells[rowindex + j + 1, i].NumberFormat = "#####"; } } else if (tbDetail1.Columns[field].DataType == typeof(DateTime)) { if (dr[field] != DBNull.Value) worksheet.Cells[rowindex + j + 1, i].Value = Convert.ToDateTime(dr[field]).ToString("dd/MM/yyyy"); } else { worksheet.Cells[rowindex + j + 1, i].Value = dr[field].ToString(); } } } } } worksheet.Rows.Remove(rowindex); worksheet.Columns.Remove(0); //B? các kí hieu ? header //for (int i = 0; i < colcount; i++) //{ // string value = worksheet.Cells[rowindex - 1, i].Value.TextValue; // if (value == null || value == "") // break; // int breakline = value.LastIndexOf("\n"); // if (breakline > -1) // worksheet.Cells[rowindex - 1, i].Value = value.Substring(0, breakline); //} } workbook.SaveDocument(file); }