public ActionResult MachineToolExcel(MaToolSearchModel model) { model.PageSize = 1000; var totalCount = 0; var result = MaterialBusiness.SearchMaToolPageList(model, out totalCount).ToList(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("Equipment/Fixture Name"); row1.CreateCell(1).SetCellValue("Equipment No"); row1.CreateCell(2).SetCellValue("Fixture No"); row1.CreateCell(3).SetCellValue("Type"); row1.CreateCell(4).SetCellValue("Serial Number"); row1.CreateCell(5).SetCellValue("Quantity"); row1.CreateCell(6).SetCellValue("Manufactured Date"); row1.CreateCell(7).SetCellValue("power"); row1.CreateCell(8).SetCellValue("Outline Dimension"); row1.CreateCell(9).SetCellValue("Ability"); row1.CreateCell(10).SetCellValue("Pressure air"); row1.CreateCell(11).SetCellValue("Cooling water"); row1.CreateCell(12).SetCellValue("Incoming Date"); row1.CreateCell(13).SetCellValue("Remark"); for (int i = 0; i < result.Count(); i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(result[i].BMEquipmentName); rowtemp.CreateCell(1).SetCellValue(result[i].BMEquipmentNo); rowtemp.CreateCell(2).SetCellValue(result[i].BMFixtureNo); rowtemp.CreateCell(3).SetCellValue(result[i].BMType); rowtemp.CreateCell(4).SetCellValue(result[i].BMSerialNumber); rowtemp.CreateCell(5).SetCellValue(Convert.ToInt32(result[i].BMQuantity)); rowtemp.CreateCell(6).SetCellValue(result[i].BMManufacturedDate); rowtemp.CreateCell(7).SetCellValue(result[i].BMPower); rowtemp.CreateCell(8).SetCellValue(result[i].BMOutlineDimension); rowtemp.CreateCell(9).SetCellValue(result[i].BMAbility); rowtemp.CreateCell(10).SetCellValue(Convert.ToDouble(result[i].BMNeedPressureAir)); rowtemp.CreateCell(11).SetCellValue(Convert.ToDouble(result[i].BMNeedCoolingWater)); rowtemp.CreateCell(12).SetCellValue(result[i].BMIncomingDate); rowtemp.CreateCell(12).SetCellValue(result[i].BMRemarks); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); var exportFileName = string.Format("{0}{1}.xls", "MaterialToolInfo", DateTime.Now.ToString("yyyyMMddHHmmss")); return(File(ms, "application/vnd.ms-excel", exportFileName)); }
/// <summary> /// 查询设备和工具 /// </summary> /// <param name="model"></param> /// <returns></returns> public ActionResult MachineToolSearch(MaToolSearchModel model) { var totalCount = 0; var result = MaterialBusiness.SearchMaToolPageList(model, out totalCount); var page = new Page(totalCount, model.CurrentPage); var resultModel = new MaToolSearchResultModel { Models = result, Page = page }; return(View("MachineSearch", resultModel)); }
/// <summary> /// /// </summary> /// <param name="param"></param> /// <param name="totalCount"></param> /// <returns></returns> public List <MaToolModel> SearchPageList(MaToolSearchModel param, out int totalCount) { var list = new List <MaToolModel>(); var selectSql = new StringBuilder(); var countSql = new StringBuilder(); var whereSql = new StringBuilder(); whereSql.Append(" WHERE 1 = 1 AND BMIsValid = 1"); if (param.Classification > 0) { whereSql.Append(string.Format(" AND BMClassification = {0}", param.Classification)); } if (!string.IsNullOrWhiteSpace(param.MachineName)) { whereSql.Append(string.Format(" AND BMEquipmentName Like N'%{0}%'", param.MachineName)); } if (!string.IsNullOrWhiteSpace(param.EquipmentNo)) { whereSql.Append(string.Format(" AND BMEquipmentNo Like N'%{0}%'", param.EquipmentNo)); } if (!string.IsNullOrWhiteSpace(param.Type)) { whereSql.Append(string.Format(" AND BMType Like N'%{0}%'", param.Type)); } if (!string.IsNullOrWhiteSpace(param.FixtureNo)) { whereSql.Append(string.Format(" AND BMFixtureNo Like N'%{0}%'", param.FixtureNo)); } selectSql.Append(string.Format(@" SELECT newTable.* FROM ( SELECT TOP ( {0} * {1} ) ROW_NUMBER() OVER ( ORDER BY BMOperateTime DESC) RowNum ,[Id] ,[BMEquipmentName] ,[BMClassification] ,[BMEquipmentNo] ,[BMFixtureNo] ,[BMType] ,[BMSerialNumber] ,[BMQuantity] ,[BMManufacturedDate] ,[BMPower] ,[BMOutlineDimension] ,[BMAbility] ,[BMNeedPressureAir] ,[BMNeedCoolingWater] ,[BMIncomingDate] ,[BMRemarks] ,[BMIsValid] ,[BMCreateUserNo] ,[BMCreateUserName] ,[BMCreateTime] ,[BMOperateUserNo] ,[BMOperateUserName] ,[BMOperateTime] FROM {2} with(NOLOCK) {3} ORDER BY BMOperateTime DESC) newTable WHERE newTable.RowNum > ( ( {0} - 1 ) * {1} ) ", param.CurrentPage, param.PageSize, tableName, whereSql.ToString())); countSql.Append(string.Format(@"SELECT COUNT(1) FROM {0} with(NOLOCK) {1} ", tableName, whereSql.ToString())); var ds = ExecuteDataSet(CommandType.Text, selectSql.ToString()); totalCount = ExecuteCount(CommandType.Text, countSql.ToString()); if (ds != null && ds.Tables.Count > 0) { DataTable dt = new DataTable(); dt = ds.Tables[0]; list = DataConvertHelper.DataTableToList <MaToolModel>(dt); } return(list); }
/// <summary> /// 查询设备和工具 /// </summary> /// <param name="param"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <MaToolModel> SearchMaToolPageList(MaToolSearchModel param, out int totalCount) { var result = _matoolDal.SearchPageList(param, out totalCount); return(result); }