#region 导出Excel /// <summary> /// 导出Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_GetExcel_Click(object sender, EventArgs e) { System.Data.DataTable dt = GetDataTable(); int rowNumber = dt.Rows.Count;//不包括字段名 int columnNumber = dt.Columns.Count; int colIndex = 0; bool isShowExcle = false; if (rowNumber == 0) { return; } #region NPOI //2、创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ICellStyle style = excel.CreateCellStyle();//设置样式 style.FillForegroundColor = HSSFColor.Yellow.Index;//设置颜色 style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; ;//居中 //设置边框 style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.ICellStyle stylered = excel.CreateCellStyle();//设置样式 stylered.FillForegroundColor = HSSFColor.Red.Index;//设置颜色 stylered.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; NPOI.SS.UserModel.ICellStyle stylegr = excel.CreateCellStyle();//设置样式 stylegr.FillForegroundColor = HSSFColor.Green.Index;//设置颜色 stylegr.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //添加一个sheet NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet("Sheet1"); //给sheet1添加标题行 NPOI.SS.UserModel.IRow head = sheet.CreateRow(0); head.Height = 30 * 20; //行高为30 sheet.SetColumnWidth(0, 15 * 256); //第4列的列宽为13 head.CreateCell(0).SetCellValue("序号"); head.CreateCell(1).SetCellValue("车牌号"); head.CreateCell(2).SetCellValue("所属OEM"); head.CreateCell(3).SetCellValue("发动机型号"); head.CreateCell(4).SetCellValue("发动机编号"); head.CreateCell(5).SetCellValue("发动机型号（采集）"); head.CreateCell(6).SetCellValue("ESN(采集)"); head.CreateCell(7).SetCellValue("VIN码(采集)"); head.CreateCell(8).SetCellValue("信贷状态"); head.CreateCell(9).SetCellValue("设备状态"); head.CreateCell(10).SetCellValue("电子围栏设置"); //为表头添加样式 head.Cells[0].CellStyle = style; head.Cells[1].CellStyle = style; head.Cells[2].CellStyle = style; head.Cells[3].CellStyle = style; head.Cells[4].CellStyle = style; head.Cells[5].CellStyle = style; head.Cells[6].CellStyle = style; head.Cells[7].CellStyle = style; head.Cells[8].CellStyle = style; head.Cells[9].CellStyle = style; head.Cells[10].CellStyle = style; //将数据逐步写入sheet1各个行 int num = 0; for (int i = 0; i < dt.Rows.Count; i++) { int a = i; NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1); row.Height = 20 * 20; //行高为20 sheet.SetColumnWidth(i, 15 * 256); //第i列的列宽为15 row.CreateCell(0).SetCellValue(dt.Rows[i]["Vcl_ID"].ToString()); row.CreateCell(1).SetCellValue(dt.Rows[i]["Vcl_No"].ToString()); row.CreateCell(2).SetCellValue(dt.Rows[i]["Organ_Name"].ToString()); row.CreateCell(3).SetCellValue(dt.Rows[i]["Vcl_EngineType"].ToString()); row.CreateCell(4).SetCellValue(dt.Rows[i]["Vcl_EngineNo"].ToString()); row.CreateCell(5).SetCellValue(dt.Rows[i]["MsgTk_EgnMode"].ToString()); row.CreateCell(6).SetCellValue(dt.Rows[i]["MsgTk_ProductSerialNo"].ToString()); row.CreateCell(7).SetCellValue(dt.Rows[i]["MsgTk_VINCode"].ToString()); string strCreditStatus = dt.Rows[i]["CWI_IsEffective"].ToString(); string Texts; if (strCreditStatus == "1") Texts = "逾期"; else Texts = "正常"; row.CreateCell(8).SetCellValue(Texts); //绑定车辆状态 string strMsgL_L1Schedule = dt.Rows[i]["MsgLS_L1Schedule"].ToString(); string strMsgL_L2Schedule = dt.Rows[i]["MsgLS_L2Schedule"].ToString(); string strMsgL_L3Schedule = dt.Rows[i]["MsgLS_L3Schedule"].ToString(); string strMsgL_ID = dt.Rows[i]["MsgLS_ID"].ToString(); //锁车设置ID string strMsgLR_ID = dt.Rows[i]["MsgLR_ID"].ToString(); //锁车设置回复ID string strCLA_MsgLRpt_ID = dt.Rows[i]["CLA_MsgLRpt_ID"].ToString(); //锁车报告ID string strMsgL_SysCS_ID = dt.Rows[i]["MsgLS_SysCS_ID"].ToString(); //锁车设置指令状态 string strSoftEdition = dt.Rows[i]["Tmnl_SoftEdition"].ToString(); string Text; if (string.IsNullOrEmpty(strMsgL_ID) || string.IsNullOrEmpty(strSoftEdition)) Text = "未设置"; else { if (!string.IsNullOrEmpty(strMsgL_L1Schedule)) Text = "一级锁车"; else if (!string.IsNullOrEmpty(strMsgL_L2Schedule)) Text = "二级锁车"; else if (!string.IsNullOrEmpty(strMsgL_L3Schedule)) Text = "三级锁车"; else Text = "解车"; if (!string.IsNullOrEmpty(strCLA_MsgLRpt_ID)) Text += "已执行"; else if (!string.IsNullOrEmpty(strMsgLR_ID)) Text += "已接收"; else { Text += "已发送"; if (strMsgL_SysCS_ID == "2") Text += "（送出）"; else if (strMsgL_SysCS_ID == "4") Text += "（超时）"; else if (strMsgL_SysCS_ID == "5") Text += "（取消）"; else if (strMsgL_SysCS_ID == "6") Text += "（未成功）"; } } row.CreateCell(9).SetCellValue(Text); string BoundSet; string strBoundSet = dt.Rows[i]["BoundSet"].ToString(); if (string.IsNullOrEmpty(strBoundSet)) BoundSet = "设置"; else if (strBoundSet == "1") BoundSet = "设置"; else BoundSet = "取消"; row.CreateCell(10).SetCellValue(BoundSet); } // 写入到客户端 string fileName = DateTime.Now.ToString("yyyy-MM-dd") + "导出数据.xls"; // 文件名称 string filePath = Path.Combine(HttpRuntime.AppDomainAppPath, "UploadFiles") + "\" + fileName; // 4.生成文件 FileStream file = new FileStream(filePath, FileMode.Create); excel.Write(file); file.Close(); Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); Response.ContentType = "application/ms-excel"; Response.WriteFile(filePath); Response.Flush(); Response.End(); #endregion } #endregion
使用NPOI插件，需要自行下载相关引用