/// <summary>
/// DefectCode 导出
/// </summary>
/// <param name="searchModel"></param>
/// <returns></returns>
public FileResult DefectCodeExcel(DefectCodeSearchModel searchModel)
{
searchModel.PageSize = 1000;
var totalCount = 0;
var result = CodeBusiness.DefectCodeSearchResult(searchModel, 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("Code Type");
row1.CreateCell(1).SetCellValue("No");
row1.CreateCell(2).SetCellValue("Code No");
row1.CreateCell(3).SetCellValue("Code Name(English)");
row1.CreateCell(4).SetCellValue("Code Name(Chinese)");
for (int i = 0; i < result.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(result[i].BDCodeType);
rowtemp.CreateCell(1).SetCellValue(result[i].BDCodeNo);
rowtemp.CreateCell(2).SetCellValue(result[i].BDCode);
rowtemp.CreateCell(3).SetCellValue(result[i].BDCodeNameEn);
rowtemp.CreateCell(4).SetCellValue(result[i].BDCodeNameCn);
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
var exportFileName = string.Format("{0}{1}.xls", "DefectCodeInfo", DateTime.Now.ToString("yyyyMMddHHmmss"));
return(File(ms, "application/vnd.ms-excel", exportFileName));
}
public IEnumerable <CodeDefectModel> SearchCodeDefectPageList(DefectCodeSearchModel param, out int totalCount)
{
var list = new List <CodeDefectModel>();
var selectSql = new StringBuilder();
var countSql = new StringBuilder();
var whereSql = new StringBuilder();
whereSql.Append(" WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(param.code))
{
whereSql.Append(string.Format(" AND (BDCodeNameEn like '%{0}%' OR BDCodeNameCn like N'%{0}%')", param.code));
}
if (!string.IsNullOrEmpty(param.codetype))
{
whereSql.Append(string.Format(" AND BDCodeType like '%{0}%'", param.codetype));
}
selectSql.Append(string.Format(@"
SELECT newTable.*
FROM (
SELECT TOP ( {0} * {1} )
ROW_NUMBER() OVER ( ORDER BY BDCodeType , BDCodeNo desc) RowNum
,[Id]
,[BDCodeNameEn]
,[BDCodeNameCn]
,[BDCodeType]
,[BDCode]
,[BDCodeNo]
,[BDIsValid]
,[BDCreateUserNo]
,[BDCreateUserName]
,[BDCreateTime]
,[BDOperateUserNo]
,[BDOperateUserName]
,[BDOperateTime]
FROM {2} with(NOLOCK) {3}
ORDER BY BDCodeType , BDCodeNo 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 <CodeDefectModel>(dt);
}
return(list);
}
public ActionResult DefectCodeSearchResult(DefectCodeSearchModel searchModel)
{
var totalCount = 0;
var result = CodeBusiness.DefectCodeSearchResult(searchModel, out totalCount);
var page = new Page(totalCount, searchModel.CurrentPage);
var model = new DefectCodeSearchResultModel
{
Models = result,
Page = page
};
return(View(model));
}
public static IEnumerable <CodeDefectModel> DefectCodeSearchResult(DefectCodeSearchModel searchModel, out int totalCount)
{
var date = _defecrcodeDAL.SearchCodeDefectPageList(searchModel, out totalCount);
return(date);
}
