using System; using System.Linq; using System.Web.Mvc; using DSWeb.MvcShipping.DAL.MsCodeExcelExpFormatDAL; using DSWeb.MvcShipping.Models.MsCodeExcelExpFormat; using DSWeb.MvcShipping.Helper; using DSWeb.MvcShipping.Comm.Cookie; using System.Collections.Generic; using HcUtility.Comm; using HcUtility.Core; using System.IO; using Microsoft.Office.Interop.Excel; using DSWeb.MvcShipping.DAL.MsOpSeaeDAL; using DSWeb.EntityDA; using System.Data; namespace DSWeb.MvcShipping.Controllers { [JsonRequestBehavior] public class MsCodeExcelExpFormatController : Controller { // // GET: public ActionResult Index() { return View(); } // // GET: / public ActionResult Edit() { return View(); } // // GET： public ContentResult GetDataList(string condition) { var dataList = MsCodeExcelExpFormatDAL.GetDataList(condition, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"])); var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = dataList.ToList() }); return new ContentResult() { Content = json }; } // // GET：/TruckMng/MsWlTyreAcc/GetData/ public ContentResult GetData(string handle, string condition) { MsCodeExcelExpFormat head = null; if (handle == "edit") { var list = MsCodeExcelExpFormatDAL.GetDataList(condition, Convert.ToString(Session["USERID"]), Convert.ToString(Session["SHOWNAME"]), Convert.ToString(Session["COMPANYID"])); if (list.Count > 0) head = list[0]; } if (head == null) { head = new MsCodeExcelExpFormat(); } var json = JsonConvert.Serialize( new { Success = true, Message = "查询成功", data = head }); return new ContentResult() { Content = json }; } public ContentResult GetBodyList(string condition) { var dataList = MsCodeExcelExpFormatDAL.GetBodyList(condition); var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = dataList.ToList() }); return new ContentResult() { Content = json }; } public ContentResult Save(string opstatus, string data, string body, string bodywork) { var headData = JsonConvert.Deserialize(data); var bodyList = JsonConvert.Deserialize>(body); if (opstatus == "add") { headData.DbOperationType = DbOperationType.DbotIns; headData.CREATEUSER = Convert.ToString(Session["USERID"]); headData.CREATETIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); headData.CORPID = Convert.ToString(Session["COMPANYID"]); } else if (opstatus == "edit") { headData.DbOperationType = DbOperationType.DbotUpd; headData.ModelUIStatus = "E"; } else { headData.DbOperationType = DbOperationType.DbotDel; } headData.MODIFIEDUSER = Convert.ToString(Session["USERID"]); headData.MODIFIEDTIME = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); var modb = new ModelObjectDB(); modb.Save(headData); var OS_ID = headData.GID; DBResult result = MsCodeExcelExpFormatDAL.SaveDetail(headData, bodyList); var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message, Data = MsCodeExcelExpFormatDAL.GetData("GID='" + OS_ID + "'") }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } // // GET：/Import/Cargoinfo/Delete public ContentResult Delete(string data) { var headData = JsonConvert.Deserialize(data); var modb = new ModelObjectDB(); DBResult result = modb.Delete(headData); if (result.Success == true) { MsCodeExcelExpFormatDAL.DeleteDetail(headData); } var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } public ContentResult DeleteDetail(string GID) { DBResult result =MsCodeExcelExpFormatDAL.DeleteDetail(GID); var jsonRespose = new JsonResponse { Success = result.Success, Message = result.Message }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } #region 引入 EXCEL [HttpPost] public ContentResult ImportExcel() { var jsonRespose = new JsonResponse { Success = false, Message = "" }; if (Request.Files.Count != 1) { jsonRespose.Success = false; jsonRespose.Message = "请选择上传的文件"; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } var file = Request.Files["LoadExcel"]; var BSNO = Request.Form["bsno"]; if (file == null) { jsonRespose.Success = false; jsonRespose.Message = "上传文件发生未知错误，请重新上传"; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } var path = Server.MapPath("../../UploadFiles/MsOpFiles"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } var size = file.ContentLength; var name = Path.GetFileName(file.FileName); var usercode = Convert.ToString(Session["USERCODE"]); var partname = usercode + DateTime.Now.ToString("yyyyMMddHHmmssfff") + name; string filename = path + "\\" + partname; string filenameURL = "../../UploadFiles/MsOpFiles/"+ partname; if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } file.SaveAs(filename); if (!System.IO.File.Exists(filename)) { jsonRespose.Success = false; jsonRespose.Message = "上传文件发生未知错误，请重新上传"; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } try { var message = string.Empty; var isSucess = MsCodeExcelExpFormatDAL.UpdateFileData(filenameURL, partname, BSNO, Convert.ToString(Session["USERID"])); var head = MsCodeExcelExpFormatDAL.GetData("GID='" + BSNO + "'"); var json = JsonConvert.Serialize(new { success = isSucess.Success, Message = isSucess.Message, data = head }); return new ContentResult() { Content = json }; } catch (Exception) { jsonRespose.Success = false; jsonRespose.Message = "上传文件出错，请确认文件正确性"; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } } public string getPropertiesValue(T t,string fieldname) { string tStr =""; if (t == null) { return tStr; } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return tStr; } foreach (System.Reflection.PropertyInfo item in properties) { string name = item.Name; object value = item.GetValue(t, null); if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String")) { if (name == fieldname) tStr = value.ToString(); } } return tStr; } public string getEString(int strnum) { var result = ""; if (strnum == 1) result = "A"; if (strnum == 2) result = "B"; if (strnum == 3) result = "C"; if (strnum == 4) result = "D"; if (strnum == 5) result = "E"; if (strnum == 6) result = "F"; if (strnum == 7) result = "G"; if (strnum == 8) result = "H"; if (strnum == 9) result = "I"; if (strnum == 10) result = "J"; if (strnum == 11) result = "K"; if (strnum == 12) result = "L"; if (strnum == 13) result = "M"; if (strnum == 14) result = "N"; if (strnum == 15) result = "O"; if (strnum == 16) result = "P"; if (strnum == 17) result = "Q"; if (strnum == 18) result = "R"; if (strnum == 19) result = "S"; if (strnum == 20) result = "T"; if (strnum == 21) result = "U"; if (strnum == 22) result = "V"; if (strnum == 23) result = "W"; if (strnum == 24) result = "X"; if (strnum == 25) result = "Y"; if (strnum == 26) result = "Z"; return result; } public ContentResult SaveExcel(string bstype, string bsno, string fmid) { var excelfm = MsCodeExcelExpFormatDAL.GetData("GID='" + fmid + "'"); var excelfmdetail = MsCodeExcelExpFormatDAL.GetBodyList("TEMPLATEID='" + fmid + "'"); var head = MsOpSeaeDAL.GetData("BSNO='" + bsno + "'"); Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.ApplicationClass() T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA(); DataSet dsUserOp = T_ALL_DA.GetAllSQL("select top 1 * from [user] as u INNER JOIN user_baseinfo as b on u.GID=b.USERID where [SHOWNAME]='" + Convert.ToString(Session["SHOWNAME"]) + "'"); var path = Server.MapPath("../../ExcelExp"); var path2 = Server.MapPath("../../UploadFiles/MsOpFiles/"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string expfilename = path + "\\" + head.CUSTNO + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; string excelFilePath = path2 + excelfm.FILEPATH; var result = new DBResult(); if (System.IO.File.Exists(excelFilePath)) { if (excel == null) { result.Success = false; result.Message = "无法创建Excel对象,可能您的服务器计算机未安装Excel!！"; } else { Workbook workbook = excel.Workbooks.Open(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Worksheet mySheet = workbook.Sheets[1] as Worksheet; var cellvalu = ""; if (excelfmdetail != null) { foreach (var enumValue in excelfmdetail) { if (string.IsNullOrEmpty(enumValue.FIELDNAME)) cellvalu = ""; else cellvalu = getPropertiesValue(head, enumValue.FIELDNAME); if (cellvalu != "") { //if ((cellvalu.IndexOf("\r\n") > 0) || (cellvalu.IndexOf("\n") > 0)) //{ // cellvalu = cellvalu.Replace("\r\n", "\\"); // cellvalu = cellvalu.Replace("\n", "\\"); // cellvalu = cellvalu.Replace("\r", " "); // string[] StrList = cellvalu.Split('\\'); // for (var i = 0; i <= StrList.Length - 1; i++) // { // var tmpstr = mySheet.Cells.get_Range(getEString(enumValue.YP) + (enumValue.XP + i).ToString()).Value2; // object v11new = tmpstr; // if (v11new != null) // { // if (v11new.ToString() != "") // mySheet.Cells[enumValue.XP + i, enumValue.YP] = v11new + " " + StrList[i]; // else // mySheet.Cells[enumValue.XP + i, enumValue.YP] = StrList[i]; // } // else mySheet.Cells[enumValue.XP + i, enumValue.YP] = StrList[i]; // } //} //else { var tmpstr = mySheet.Cells.get_Range(getEString(enumValue.YP) + (enumValue.XP).ToString()).Value2; object v11new = tmpstr; if (v11new != null) { if (v11new.ToString() != "") mySheet.Cells[enumValue.XP, enumValue.YP] = v11new + " " + cellvalu; else mySheet.Cells[enumValue.XP, enumValue.YP] = cellvalu; } else { mySheet.Cells[enumValue.XP, enumValue.YP] = cellvalu; } //} } else { var FIXSTR = enumValue.FIXSTR; if (FIXSTR == "[USERNAME]") FIXSTR = Convert.ToString(Session["SHOWNAME"]); if (FIXSTR == "[USERTEL]" || FIXSTR == "[USEREMAIL]") { if (dsUserOp != null) { if (dsUserOp.Tables[0].Rows.Count > 0) { if (FIXSTR == "[USEREMAIL]") FIXSTR = dsUserOp.Tables[0].Rows[0]["EMAIL1"].ToString().Trim(); if (FIXSTR == "[USERTEL]") FIXSTR = dsUserOp.Tables[0].Rows[0]["OFFICEPHONE"].ToString().Trim(); } } } mySheet.Cells[enumValue.XP, enumValue.YP] = FIXSTR; } } } mySheet.SaveAs(expfilename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); workbook = null; excel.Quit(); excel = null; var filename = Path.GetFileName(expfilename); result.Data = "../../ExcelExp/" + filename; result.Success = true; result.Message = filename; } } else { result.Success = false; result.Message = "服务器EXCEL模板不存在,请上传！"; } var jsonRespose = new JsonResponse { Data = result.Data, Success = result.Success, Message = result.Message }; return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) }; } #endregion #region 参照部分 #endregion } }