public Result FileList(string etid = "", string fileclass = "")
{
//if (string.IsNullOrEmpty(etid)) { return new Result("-1", "格式不正确"); }
//if (string.IsNullOrEmpty(fileclass)) { return new Result("-1", "格式不正确"); }
BW.Web.ContractLogic.BLL.Con_ContractBLL bll = new BW.Web.ContractLogic.BLL.Con_ContractBLL();
DataTable dt = bll.Query(string.Format(@"select ID,FileUrl,RealName,CONVERT(nvarchar(10),CreateDT,120)CreateDT,(select Name from m_Staff where Id=CreateUserID)CreateUserID from dbo.FileCenter"));// where ID in (select FiledID from dbo.ET_TaskFiled where ETID={0} and ETClass={1})", etid, fileclass));
return(new Result(dt));
}
public Result ExcelOPT(string ExcelFile)
{
string strSQL = @"select Id from MyTest where 1=1 ";
//获得列表记录总数
BW.Web.ContractLogic.BLL.Con_ContractBLL bll = new BW.Web.ContractLogic.BLL.Con_ContractBLL();
DataTable dt = bll.Query(strSQL);
string filename = @"D:\File\201802\b89b8f03-8545-45b0-abc8-63ce261697a3.xls";
GetInfo(filename);
return(new Result(dt));
}
public Result List(string page = "", string rows = "", string sort = "", string order = "", string Title = "", string Time = "")
{
int intpage;
if (!int.TryParse(page, out intpage))
{
return(new Result("-1", "page格式不正确"));
}
int introws;
if (!int.TryParse(rows, out introws))
{
return(new Result("-1", "rows格式不正确"));
}
#region 验证输入值是否正确
DateTime dateTime;
string strSQL = @"select * from MyTest where 1=1 ";
if (!string.IsNullOrEmpty(Title))
{
strSQL += "AND Title Like'%" + Title + "%'";
}
if (!string.IsNullOrEmpty(Time))
{
if (!DateTime.TryParse(Time, out dateTime))
{
return(new Result("-1", "Time格式不正确"));
}
strSQL += "AND Time ='" + dateTime + "'";
}
#endregion
//获得列表记录总数
BW.Web.ContractLogic.BLL.Con_ContractBLL bll = new BW.Web.ContractLogic.BLL.Con_ContractBLL();
DataTable dt = bll.Query(strSQL);
//DataTable mydt1 = dt.SortPage(intpage + 1, introws, SortName, SortOrder);
DataTable mydt1 = dt.SortPage(0, 20, sort, order);
#region 返回值
return(new Result(new
{
total = dt.Rows.Count,
rows = mydt1
}));
#endregion
}
public Result Search()
{
string Field = "";
var body = HttpHelper.GetHttpContent();//获取body内容
JObject jo;
jo = (JObject)JsonConvert.DeserializeObject(body);
Field = StringHelper.SqlFilter(jo["Field"]?.ToString());
if (Field == "")
{
return(new Result("-1", "参数不能为空"));
}
string strSQL = @"select Id from MyTest where 1=1 ";
//获得列表记录总数
BW.Web.ContractLogic.BLL.Con_ContractBLL bll = new BW.Web.ContractLogic.BLL.Con_ContractBLL();
DataTable dt = bll.Query(strSQL);
return(new Result(dt));
}
public HttpResponseMessage Export(string body)
{
#region 验证输入值是否有效
string page = "";
string size = "";
string sort = "";
string order = "";
if (body.Trim() != "")
{
//解析body内容
JObject jo;
try
{
//解析body内容
jo = (JObject)JsonConvert.DeserializeObject(body);
}
catch (Exception e)
{
return(new Result("-1", "json格式不正确!"));
}
page = StringHelper.SqlFilter(jo["page"]?.ToString());
if (string.IsNullOrEmpty(page))
{
return(new Result("-1", "page不能为空!"));
}
size = StringHelper.SqlFilter(jo["rows"]?.ToString());
if (string.IsNullOrEmpty(size))
{
return(new Result("-1", "size不能为空!"));
}
sort = StringHelper.SqlFilter(jo["sort"]?.ToString());
if (string.IsNullOrEmpty(sort))
{
return(new Result("-1", "sort不能为空!"));
}
order = StringHelper.SqlFilter(jo["order"]?.ToString());
if (string.IsNullOrEmpty(order))
{
return(new Result("-1", "order不能为空!"));
}
//ContractNo = StringHelper.SqlFilter(jo["ContractNo"]?.ToString());
}
#endregion
#region 验证输入值是否正确
int intpage = 0;
int intsize = 10;
if (!int.TryParse(page, out intpage))
{
return(new Result("-1", "page格式不正确"));
}
if (intpage < 0)
{
return(new Result("-1", "page不能小于0"));
}
if (!int.TryParse(size, out intsize))
{
return(new Result("-1", "size格式不正确"));
}
#endregion
#region 获取返回值
DataTable dt = null;
DataTable mydt1 = new DataTable();
string strSQL = @"select * from MyTest where 1=1 ";
//获得列表记录总数
BW.Web.ContractLogic.BLL.Con_ContractBLL bll = new BW.Web.ContractLogic.BLL.Con_ContractBLL();
dt = bll.Query(strSQL);
mydt1 = dt.SortPage(intpage, intsize, sort, order);
#endregion
#region 设置显示列名,显示顺序
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("我的测试" + DateTime.Now.ToString("yyyyMMddHHmmss"));
IRow rowHeader = sheet1.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("序号");
rowHeader.CreateCell(1).SetCellValue("标题");
rowHeader.CreateCell(2).SetCellValue("内容");
rowHeader.CreateCell(3).SetCellValue("日期");
//生成excel内容
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["ID"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["Title"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["Content"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["Time"].ToString());
}
for (int i = 0; i < 10; i++)
{
sheet1.AutoSizeColumn(i);
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
file.Seek(0, SeekOrigin.Begin);
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new StreamContent(file);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
result.Content.Headers.ContentDisposition.FileName = InputHelper.StrToUTF("我的测试" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
return(result);
#endregion
}
