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