using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using CY.BLL; using CY.Model; using CY.Infrastructure.Query; using CY.Infrastructure.Common; using CY.BLL.Sys; using CY.BLL.OA; using CY.BLL.EC; using CY.BLL.Inquiry; using CY.Model.Inquiry; using System.Drawing; using CY.Model.OA; using CY.SQLDAL; using System.Data.SqlClient; using CY.Infrastructure.Logging; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System.IO; namespace CY.WebForm.Pages.business { public partial class DeliverPlanDakaList : BasePage { OA_DriverRecordBLL _oA_DriverRecordBLL = null; OA_CarManageBll _OA_CarManageBll = null; //初始化 public DeliverPlanDakaList() { _oA_DriverRecordBLL = new OA_DriverRecordBLL(); _OA_CarManageBll = new OA_CarManageBll(); } protected void Page_Load(object sender, EventArgs e) { UCPager1.AspNetPager.PageChanged += AspNetPager1_PageChanged; if (!IsPostBack) { switch (Request["Target"]) { case "AcceptOrderAll": break; case "AcceptOrderAll2": break; default: InitialSelectData(); BindList(); return; } Response.End(); } } //加载查询条件 protected void InitialSelectData() { this.selCarId.DataSource = _OA_CarManageBll.SelectListByFirmId(CurrentUser.MemberId); this.selCarId.DataTextField = "CarNumber"; this.selCarId.DataValueField = "Keyid"; this.selCarId.DataBind(); this.selCarId.Items.Insert(0, new ListItem("全部", "")); this.txtCreatTimestart.Value = DateTime.Now.ToString("yyyy-MM-dd"); this.txtCreatTimeend.Value = DateTime.Now.ToString("yyyy-MM-dd"); } //分页事件 protected void AspNetPager1_PageChanged(object src, EventArgs e) { BindList(); } //查询事件 protected void btn_Search_Click(object sender, EventArgs e) { BindList(); } //绑定列表 private void BindList() { Pagination pa = new Pagination(); pa.PageSize = UCPager1.AspNetPager.PageSize; pa.PageIndex = UCPager1.AspNetPager.CurrentPageIndex; IEnumerable oA_DriverRecords = _oA_DriverRecordBLL.SelectModelPage(pa, this.txtCreatTimestart.Value, this.txtCreatTimeend.Value, this.selCarId.Value, this.txtCreater.Value, this.selClockType.Value ); Database DC = new Database(); try { List fileDataList = null; foreach (var oA_DriverRecord in oA_DriverRecords) { if (string.IsNullOrEmpty(oA_DriverRecord.MileageImg)) { oA_DriverRecord.MileageImgName = ""; } else { oA_DriverRecord.MileageImgName = ""; } string sqlStr = string.Format(@" select * from [dbo].[OA_attachment] where [OA_Id]='{0}' and [AttachmentType]=4 order by [CreateTime] ", oA_DriverRecord.Id); SqlCommand myCmd = new SqlCommand(sqlStr, DC.Connection); //SqlDataAdapter myDa = new SqlDataAdapter(myCmd); var a = myCmd.ExecuteReader(); //myDa.Dispose(); fileDataList = new List(); OA_CorporateClientsContract Contract; var i = 1; while (a.Read()) { //注意如何将每次读取到的记录添加到listbox1中（因为第一次做时出错了，嘿嘿） Contract = new OA_CorporateClientsContract(); // Contract.Keyid = a.GetInt32(0); //Contract.CorporateClientsid = a.GetGuid(1); //Contract.CorporateClientsName = a.GetString(4); Contract.FileName = a[4].ToString(); Contract.PageCode = i; Contract.FilePath = a.GetString(2); // Contract.Remark = a.GetString(6); i += 1; fileDataList.Add(Contract); } a.Close(); //关闭sqldatareader if (fileDataList.Count > 0) { oA_DriverRecord.ZhuangcheImg = ""; } else { oA_DriverRecord.ZhuangcheImg = ""; } sqlStr = string.Format(@" select * from [dbo].[OA_attachment] where [OA_Id]='{0}' and [AttachmentType]=5 order by [CreateTime] ", oA_DriverRecord.Id); myCmd = new SqlCommand(sqlStr, DC.Connection); //SqlDataAdapter myDa = new SqlDataAdapter(myCmd); a = myCmd.ExecuteReader(); //myDa.Dispose(); fileDataList = new List(); i = 1; while (a.Read()) { //注意如何将每次读取到的记录添加到listbox1中（因为第一次做时出错了，嘿嘿） Contract = new OA_CorporateClientsContract(); // Contract.Keyid = a.GetInt32(0); //Contract.CorporateClientsid = a.GetGuid(1); //Contract.CorporateClientsName = a.GetString(4); Contract.FileName = a[4].ToString(); Contract.PageCode = i; Contract.FilePath = a.GetString(2); // Contract.Remark = a.GetString(6); i += 1; fileDataList.Add(Contract); } a.Close(); //关闭sqldatareader if (fileDataList.Count > 0) { oA_DriverRecord.CarConditionImg = ""; } else { oA_DriverRecord.CarConditionImg = ""; } } } catch (Exception ee) { new Log4NetAdapter().Log("查找送货单图片出错：" + ee.Message); } finally { if (DC.Connection.State != System.Data.ConnectionState.Closed) DC.Connection.Close(); } this.RepClientList.DataSource = oA_DriverRecords; this.RepClientList.DataBind(); UCPager1.AspNetPager.RecordCount = pa.RecordCount; } //查询事件 protected void btn_Daochu_Click(object src, EventArgs e) { byte[] fileBytes = ExportToExcel(); if (fileBytes != null) { Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=打卡导出.xlsx"); Response.BinaryWrite(fileBytes); Response.End(); } else { // 处理导出失败的情况 Response.Write("导出失败，请稍后重试！"); } } public byte[] ExportToExcel() { byte[] bt = null; try { Pagination pa = new Pagination(); pa.PageSize = 1000; pa.PageIndex = 1; IEnumerable results = _oA_DriverRecordBLL.SelectModelPage(pa, this.txtCreatTimestart.Value, this.txtCreatTimeend.Value, this.selCarId.Value, this.txtCreater.Value, this.selClockType.Value ); // 创建工作簿 IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("导出数据"); // 创建表头 IRow headerRow = sheet.CreateRow(0); headerRow.CreateCell(0).SetCellValue("编号"); headerRow.CreateCell(1).SetCellValue("车辆"); headerRow.CreateCell(2).SetCellValue("驾驶员"); headerRow.CreateCell(3).SetCellValue("打卡时间"); headerRow.CreateCell(4).SetCellValue("打卡类型"); headerRow.CreateCell(5).SetCellValue("未装货情况"); headerRow.CreateCell(6).SetCellValue("车况"); headerRow.CreateCell(7).SetCellValue("备注"); headerRow.CreateCell(8).SetCellValue("里程(公里)"); // 添加数据 int rowIndex = 1; foreach (var oA_DriverRecord in results) { IRow row = sheet.CreateRow(rowIndex++); row.CreateCell(0).SetCellValue((rowIndex - 1).ToString()); row.CreateCell(1).SetCellValue(oA_DriverRecord.CarName); row.CreateCell(2).SetCellValue(oA_DriverRecord.Creater); row.CreateCell(3).SetCellValue(oA_DriverRecord.CreatTime.ToString("yyyy-MM-dd")); row.CreateCell(4).SetCellValue(oA_DriverRecord.ClockTypeName); row.CreateCell(5).SetCellValue(oA_DriverRecord.EmptyCause); row.CreateCell(6).SetCellValue(oA_DriverRecord.CarCondition); row.CreateCell(7).SetCellValue(oA_DriverRecord.Remark); row.CreateCell(8).SetCellValue(oA_DriverRecord.Mileage.ToString()); } // 将工作簿写入内存流 using (var ms = new MemoryStream()) { workbook.Write(ms); bt = ms.ToArray(); } } catch (Exception e) { bt = null; // 记录日志 } return bt; } } }