MES-WEB-API.git

			@@ -14,6 +14,8 @@
			using NPOI.SS.UserModel;
			using Newtonsoft.Json;
			using Newtonsoft.Json.Converters;
			using System.Web;
			using System.Net.Http.Headers;

			namespace WebAPI.Controllers.SCGL
			{
			@@ -47,8 +49,10 @@
			sWhere = " where 1=1" + sWhere;
			}

			count = new SQLHelper.ClsCN().RunProcReturn("select hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere, "h_v_PackUnionBillList").Tables[0].Rows.Count;
			string sql = string.Format(@"select top " + pageSize + " hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from(select row_number() over (order by hmainid desc) as RowNumber,hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere + ") as A where RowNumber >" + pageSize + " *(" + pageNum + "-1)");
			DataSet ds0 = oCn.RunProcReturn("select count(*) from h_v_PackUnionBillList " + sWhere, "h_v_PackUnionBillList");
			count = ds0.Tables[0].Rows.Count;
			//string sql = string.Format(@"select top " + pageSize + " hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from(select row_number() over (order by hmainid desc) as RowNumber,hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere + ") as A where RowNumber >" + pageSize + " *(" + pageNum + "-1)");
			string sql = string.Format(@"select hmainid,日期,单据号,箱号条形码,净重,毛重,皮重,生产组织,部门,操作员,客户标签号,物料代码,物料名称,计量单位代码,计量单位名称,数量,制单人,制单日期 from h_v_PackUnionBillList " + sWhere + " order by hmainid offset ((" + pageNum + "-1)*" + pageSize + ") rows fetch next " + pageSize + " rows only;");
			ds = new SQLHelper.ClsCN().RunProcReturn(sql, "h_v_PackUnionBillList");
			string aa = ds.Tables[0].Columns[0].ToString();

			@@ -249,7 +253,7 @@
			{
			try
			{
			ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain order by hmainid desc", "h_v_Sc_StationInBillListMain");
			ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain"+sWhere+ "order by hmainid desc", "h_v_Sc_StationInBillListMain");
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "返回记录成功！";
			@@ -269,81 +273,71 @@
			#endregion

			#region 标准工时导出Excel表格数据

			[Route("Sc_PackUnionBill/Sc_StationInBillSetExcel")]
			[HttpGet]
			public object Sc_StationInBillSetExcel()
			public HttpResponseMessage Sc_StationInBillSetExcel()
			{
			try
			{
			ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain order by hmainid desc", "h_v_Sc_StationInBillListMain");
			IWorkbook book = new HSSFWorkbook();
			ISheet sheet = book.CreateSheet("sheet1");
			IRow row1 = sheet.CreateRow(0);
			row1.CreateCell(0).SetCellValue("进站生产订单号");
			row1.CreateCell(1).SetCellValue("流转卡号");
			row1.CreateCell(2).SetCellValue("进出站所用时间");
			row1.CreateCell(3).SetCellValue("工时");
			row1.CreateCell(4).SetCellValue("不良数量");
			row1.CreateCell(5).SetCellValue("报废数量");
			row1.CreateCell(6).SetCellValue("进站数量");
			row1.CreateCell(7).SetCellValue("出站数量");
			row1.CreateCell(8).SetCellValue("出站时间");
			row1.CreateCell(9).SetCellValue("进站时间");
			row1.CreateCell(10).SetCellValue("制单人");
			row1.CreateCell(11).SetCellValue("制单日期");
			for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
			{
			IRow row = sheet.CreateRow(i + 1);
			row.CreateCell(0).SetCellValue(ds.Tables[0].Rows[i]["进站生产订单号"].ToString());
			row.CreateCell(1).SetCellValue(ds.Tables[0].Rows[i]["流转卡号"].ToString());
			row.CreateCell(2).SetCellValue(ds.Tables[0].Rows[i]["进出站所用时间"].ToString());
			row.CreateCell(3).SetCellValue(ds.Tables[0].Rows[i]["工时"].ToString());
			row.CreateCell(4).SetCellValue(ds.Tables[0].Rows[i]["不良数量"].ToString());
			row.CreateCell(5).SetCellValue(ds.Tables[0].Rows[i]["报废数量"].ToString());
			row.CreateCell(6).SetCellValue(ds.Tables[0].Rows[i]["进站数量"].ToString());
			row.CreateCell(7).SetCellValue(ds.Tables[0].Rows[i]["出站数量"].ToString());
			row.CreateCell(8).SetCellValue(ds.Tables[0].Rows[i]["出站时间"].ToString());
			row.CreateCell(9).SetCellValue(ds.Tables[0].Rows[i]["进站时间"].ToString());
			row.CreateCell(10).SetCellValue(ds.Tables[0].Rows[i]["制单人"].ToString());
			row.CreateCell(11).SetCellValue(ds.Tables[0].Rows[i]["制单日期"].ToString());
			}
			string filename = "C:/Users/Administrator/Desktop/标准工时表" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
			MemoryStream BookStream = new MemoryStream();
			book.Write(BookStream);
			BookStream.Seek(0,SeekOrigin.Begin);
			byte[] buffer = BookStream.ToArray();
			using (FileStream fs = new FileStream(filename, FileMode.CreateNew, FileAccess.Write))
			{
			try
			{
			fs.Write(buffer, 0, buffer.Length);
			fs.Flush();
			}
			catch
			{
			HSSFWorkbook workbook = new HSSFWorkbook();
			ISheet sheet = workbook.CreateSheet("sheet1");
			IRow row = sheet.CreateRow(0);


			}
			finally
			{
			fs.Dispose();
			BookStream.Dispose();
			}
			}
			objJsonResult.code = "1";
			objJsonResult.count = 1;
			objJsonResult.Message = "成功导出表格至桌面";
			objJsonResult.data = null;
			return objJsonResult;
			}
			catch (Exception ex)
			row.CreateCell(0).SetCellValue("进站生产订单号");
			row.CreateCell(1).SetCellValue("流转卡号");
			row.CreateCell(2).SetCellValue("进出站所用时间");
			row.CreateCell(3).SetCellValue("工时");
			row.CreateCell(4).SetCellValue("不良数量");
			row.CreateCell(5).SetCellValue("报废数量");
			row.CreateCell(6).SetCellValue("进站数量");
			row.CreateCell(7).SetCellValue("出站数量");
			row.CreateCell(8).SetCellValue("出站时间");
			row.CreateCell(9).SetCellValue("进站时间");
			row.CreateCell(10).SetCellValue("制单人");
			row.CreateCell(11).SetCellValue("制单日期");
			//精确控制列宽
			sheet.SetColumnWidth(1, 5000);
			sheet.SetColumnWidth(2, 5000);
			sheet.SetColumnWidth(3, 5000);
			sheet.SetColumnWidth(4, 5000);
			sheet.SetColumnWidth(5, 5000);
			sheet.SetColumnWidth(6, 5000);
			sheet.SetColumnWidth(7, 5000);
			sheet.SetColumnWidth(8, 5000);
			sheet.SetColumnWidth(9, 5000);
			sheet.SetColumnWidth(10, 5000);

			DataSet ds = oCn.RunProcReturn("select * from h_v_Sc_StationInBillListMain order by hmainid desc", "h_v_Sc_StationInBillListMain");
			for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
			{
			objJsonResult.code = "0";
			objJsonResult.count = 0;
			objJsonResult.Message = "导出失败" + ex.ToString();
			objJsonResult.data = null;
			return objJsonResult;
			IRow row1 = sheet.CreateRow(i + 1);
			row1.CreateCell(0).SetCellValue(ds.Tables[0].Rows[i]["进站生产订单号"].ToString());
			row1.CreateCell(1).SetCellValue(ds.Tables[0].Rows[i]["流转卡号"].ToString());
			row1.CreateCell(2).SetCellValue(ds.Tables[0].Rows[i]["进出站所用时间"].ToString());
			row1.CreateCell(3).SetCellValue(ds.Tables[0].Rows[i]["工时"].ToString());
			row1.CreateCell(4).SetCellValue(ds.Tables[0].Rows[i]["不良数量"].ToString());
			row1.CreateCell(5).SetCellValue(ds.Tables[0].Rows[i]["报废数量"].ToString());
			row1.CreateCell(6).SetCellValue(ds.Tables[0].Rows[i]["进站数量"].ToString());
			row1.CreateCell(7).SetCellValue(ds.Tables[0].Rows[i]["出站数量"].ToString());
			row1.CreateCell(8).SetCellValue(ds.Tables[0].Rows[i]["出站时间"].ToString());
			row1.CreateCell(9).SetCellValue(ds.Tables[0].Rows[i]["进站时间"].ToString());
			row1.CreateCell(10).SetCellValue(ds.Tables[0].Rows[i]["制单人"].ToString());
			row1.CreateCell(11).SetCellValue(ds.Tables[0].Rows[i]["制单日期"].ToString());
			}
			System.IO.MemoryStream ms = new System.IO.MemoryStream();
			workbook.Write(ms);
			ms.Position = 0;

			var response = new HttpResponseMessage(HttpStatusCode.OK);
			response.Content = new StreamContent(ms);

			response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
			var fileName = "标准工时_.xls";

			response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
			{
			FileName = System.Web.HttpUtility.UrlEncode(fileName)
			};
			return response;
			}

			#endregion