//将datatable导出到excel public static ReturnMsg ExportList(ref MemoryStream ms, IList list, int type = 0, string path = "") { DataTable dt; try { dt = ToDataTable(list);//将数据源转换为datatable类型 //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); int i = 0; foreach (DataColumn column in dt.Columns) { row1.CreateCell(i).SetCellValue(column.ColumnName); i++; } int j = 0; foreach (DataRow dr in dt.Rows) { i = 0; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(j + 1); for (; i < dt.Columns.Count; i++) { rowtemp.CreateCell(i).SetCellValue(dr[i].ToString()); } j++; } if (type == 0) { MemoryStream ms1 = new MemoryStream(); book.Write(ms1); ms1.Seek(0, SeekOrigin.Begin); ms = ms1; } else { FileStream fs = new FileStream(path, FileMode.Create); book.Write(fs); fs.Close(); ms = null; } return(new ReturnMsg() { code = 0, msg = "" }); } catch (Exception ex) { ms = null; return(new ReturnMsg() { code = 1, msg = "导出异常:" + ex.Message }); } }
public void WriteExcel(DataTable dt, string filePath, string zhuangtai) { if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); } } if (zhuangtai == "是") { // 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } book = null; } } else { System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", filePath)); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); } //end } }
/// <summary> /// 获取不动产的办件步骤信息 /// </summary> /// <returns></returns> public void GetBdcStepInfo(string fileName) { BDCInfo4WWWDal dal = new BDCInfo4WWWDal(); DataTable dt = dal.GetBDCStepInfo(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("不动产办理步骤信息"); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("业务编号"); row.CreateCell(1).SetCellValue("查询密码"); row.CreateCell(2).SetCellValue("通知人"); row.CreateCell(3).SetCellValue("流程步骤"); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(i + 1); row1.CreateCell(0).SetCellValue(dt.Rows[i]["SLBH"].ToString()); row1.CreateCell(1).SetCellValue(dt.Rows[i]["CXMM"].ToString()); row1.CreateCell(2).SetCellValue(dt.Rows[i]["TZRXM"].ToString()); row1.CreateCell(3).SetCellValue(dt.Rows[i]["StepName"].ToString()); } } using (FileStream stm = File.OpenWrite(fileName)) { book.Write(stm); } }
public void getExcel(DataTable dt) { string path = ConfigHelper.GetConfigString("AttachmentPath"); if (path.StartsWith("~")) { path = Server.MapPath(path); } if (!path.EndsWith("\\")) { path = path + "\\"; } path += "ImportExcelSVM\\Download\\"; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string name = ""; if (MCSTabControl1.SelectedIndex == 0) { name = "零售商导购协议导出"; } else { name = "零售商返利协议导出"; } path += name; NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = book.CreateSheet(name); IRow row = sheet.CreateRow(0); //创建表头 for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } //创建内容 for (int i = 0; i < dt.Rows.Count; i++) { IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } //写入到客户端 FileStream file = new FileStream(path, FileMode.Create); book.Write(file); file.Close(); book = null; sheet = null; Downloadfile(path, "Export-" + name + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".xls"); }
private void SaveExcel(List <FileProperty> flist) { saveFileDialog1.OverwritePrompt = true; saveFileDialog1.AddExtension = true; saveFileDialog1.DefaultExt = ".xls"; saveFileDialog1.AutoUpgradeEnabled = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { toolStripStatusLabel1.Text = "正在生成Excel文件"; string path = saveFileDialog1.FileName; NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workBook.CreateSheet("Sheet1"); NPOI.SS.UserModel.Row row1 = sheet.CreateRow(0); row1.CreateCell(0).SetCellValue("Title"); row1.CreateCell(1).SetCellValue("FilePath"); row1.CreateCell(2).SetCellValue("Description"); row1.CreateCell(3).SetCellValue("Tags"); row1.CreateCell(4).SetCellValue("Price"); row1.CreateCell(5).SetCellValue("CateId"); row1.CreateCell(6).SetCellValue("Test"); for (int i = 0; i < flist.Count; i++) { NPOI.SS.UserModel.Row r = sheet.CreateRow(i + 1); r.CreateCell(0).SetCellValue(flist[i].Name); r.CreateCell(1).SetCellValue(flist[i].Path); } using (FileStream fs = new FileStream(path, FileMode.Create)) { workBook.Write(fs); } toolStripStatusLabel1.Text = "生成Excel成功"; } }
public FileResult ExportOverview() { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Overview"); //获取list数据 var overview = repo.All().ToList(); //List<TB_STUDENTINFOModel> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("客戶名稱"); row1.CreateCell(1).SetCellValue("聯絡人數量"); row1.CreateCell(2).SetCellValue("銀行帳戶數量"); //将数据逐步写入sheet1各个行 for (int i = 0; i < overview.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(overview[i].客戶名稱.ToString()); rowtemp.CreateCell(1).SetCellValue(overview[i].聯絡人數量.ToString()); rowtemp.CreateCell(2).SetCellValue(overview[i].銀行帳戶數量.ToString()); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "CustomersOverView.xls")); }
public void SaveToExcel(DataTable dt, string filePath) { if (filePath.IsNotEmpty() && null != dt && dt.Rows.Count > 0) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = book.CreateSheet(dt.TableName); IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { IRow dataRow = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { dataRow.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); } } // 写入到客户端 using (MemoryStream ms = new MemoryStream()) { book.Write(ms); using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } book = null; } } }
public ActionResult ExportClientCouponCode(int parentId, int isBind, string channel) { CompanyClientManager manager = new CompanyClientManager(); var result = manager.SelectCouponCodeByParentId(parentId, isBind); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); var fileName = channel + DateTime.Now.ToString("yyyy_MM_dd_HHmm") + ".xls"; row1.CreateCell(0).SetCellValue("渠道"); row1.CreateCell(1).SetCellValue("活动券码"); row1.CreateCell(2).SetCellValue("手机号"); row1.CreateCell(3).SetCellValue("创建时间"); if (result != null && result.Any()) { for (var i = 0; i < result.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(channel); rowtemp.CreateCell(1).SetCellValue(result[i].CouponCode); rowtemp.CreateCell(2).SetCellValue(result[i].Telephone); rowtemp.CreateCell(3).SetCellValue(result[i].CreatedTime.ToString()); } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", fileName)); }
public FileResult ExportCustomers() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Customers"); NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); var customers = repo.All().ToList(); row1.CreateCell(0).SetCellValue("客戶名稱"); row1.CreateCell(1).SetCellValue("統一編號"); row1.CreateCell(2).SetCellValue("電話"); row1.CreateCell(3).SetCellValue("傳真"); row1.CreateCell(4).SetCellValue("地址"); row1.CreateCell(5).SetCellValue("電子郵件"); for (int i = 0; i < customers.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(customers[i].客戶名稱.ToString()); rowtemp.CreateCell(1).SetCellValue(customers[i].統一編號.ToString()); rowtemp.CreateCell(2).SetCellValue(customers[i].電話.ToString()); rowtemp.CreateCell(3).SetCellValue(customers[i].傳真.ToString()); rowtemp.CreateCell(4).SetCellValue(customers[i].地址.ToString()); rowtemp.CreateCell(5).SetCellValue(customers[i].Email.ToString()); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "Customers.xls")); }
public static void WriteExcel(DataGridView dg, string filePath) { if (!string.IsNullOrEmpty(filePath) && dg != null && dg.Rows.Count > 0) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("DG"); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dg.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dg.Columns[i].HeaderText); } for (int i = 0; i < dg.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dg.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dg[j, i].Value)); } } // 写入到客户端. using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } book = null; } MessageBox.Show("導出成功:" + filePath, "Info", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public void ExportDataToExcelByWeb(System.Data.DataTable dt, string FileName) { string[] headerList = new string[dt.Columns.Count]; string[] headerCode = new string[dt.Columns.Count];//对应表头的字段 for (int i = 0; i < dt.Columns.Count; i++) { headerList.SetValue(dt.Columns[i].ColumnName, i); headerCode.SetValue(dt.Columns[i].ColumnName, i); } //如果是linq查询的列表,需要转datatable NPOI.HSSF.UserModel.HSSFWorkbook book = ExportExcel.Export(dt, headerList, headerCode); // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); string UserAgent = Request.ServerVariables["http_user_agent"].ToLower(); if (UserAgent.IndexOf("firefox") == -1)//火狐浏览器 { FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8); } Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", FileName)); Response.ContentType = "application/vnd.ms-excel"; Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); }
public FileResult 匯出客戶銀行資訊() { //建立Excel文件 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //新增sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //取得匯出資料List List <客戶銀行資訊> dataList = repo客戶銀行資訊.All().ToList(); //给sheet1添加第一行的標題列 NPOI.SS.UserModel.IRow row1 = sheet1.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("客戶名稱"); //將資料逐筆寫入 for (int i = 0; i < dataList.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dataList[i].銀行名稱.ToString()); rowtemp.CreateCell(1).SetCellValue(dataList[i].銀行代碼.ToString()); rowtemp.CreateCell(2).SetCellValue(dataList[i].分行代碼.ToString()); rowtemp.CreateCell(3).SetCellValue(dataList[i].帳戶名稱.ToString()); rowtemp.CreateCell(4).SetCellValue(dataList[i].帳戶號碼.ToString()); rowtemp.CreateCell(5).SetCellValue(dataList[i].客戶資料.客戶名稱.ToString()); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "客戶銀行資訊清單.xls")); }
/// <summary> /// 通过NPOI导出excel /// </summary> /// <param name="dt"></param> /// <param name="filePath"></param> public static void WriteExcel(DataTable dt, string filePath) { if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); } } // 写入到客户端 using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } book = null; } } }
protected static MemoryStream GetMS(List <FishEntity.CompanyEntity> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.HeightInPoints = 26; CreateCell(row, 0, "客户市场需求预测表", CellType.String, cellStyle); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12)); row = sheet.CreateRow(1); cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold); CreateCell(row, 0, "客户编号", CellType.String, cellStyle); CreateCell(row, 1, "客户名称", CellType.String, cellStyle); CreateCell(row, 2, "类别", CellType.String, cellStyle); CreateCell(row, 3, "综合等级", CellType.String, cellStyle); CreateCell(row, 4, "需求量等级", CellType.String, cellStyle); CreateCell(row, 5, "活跃程度", CellType.String, cellStyle); CreateCell(row, 6, "忠诚度", CellType.String, cellStyle); CreateCell(row, 7, "主要产品", CellType.String, cellStyle); CreateCell(row, 8, "业务员", CellType.String, cellStyle); CreateCell(row, 9, "联系人", CellType.String, cellStyle); CreateCell(row, 10, "最近联系日期", CellType.String, cellStyle); CreateCell(row, 11, "最近周预估", CellType.String, cellStyle); CreateCell(row, 12, "最近月预估", CellType.String, cellStyle); if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.CompanyEntity model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.code); CreateCell(row, 1, model.fullname); CreateCell(row, 2, model.type); CreateCell(row, 3, model.generallevel); CreateCell(row, 4, model.requiredlevel); CreateCell(row, 5, model.managestandard); CreateCell(row, 6, model.activelevel); CreateCell(row, 7, model.products); CreateCell(row, 8, model.salesman); CreateCell(row, 9, model.linkman); CreateCell(row, 10, model.currentlink); CreateCell(row, 11, model.currentweekestimate); CreateCell(row, 12, model.currentmonthestimate); # endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
/// <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)); }
private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName) { NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(sheetName); NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); foreach (DataColumn column in sourceTable.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return(ms); }
public FileResult getFile() { List <Person> list = Option.getAwardInfo(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("qq"); row1.CreateCell(1).SetCellValue("昵称"); row1.CreateCell(2).SetCellValue("奖品"); row1.CreateCell(3).SetCellValue("有效发言次数"); //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].qq); rowtemp.CreateCell(1).SetCellValue(list[i].nickname); rowtemp.CreateCell(2).SetCellValue(list[i].prize); rowtemp.CreateCell(3).SetCellValue(list[i].speakNum); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "获奖表.xls")); }
/* * 作用:将DataTable数据表转Excel的方法,数据存在内存中,返回内存流 * 1、创建workbook * 2、用workbook创建sheet * 3、用sheet创建row * 4、用row创建cell * 5、给这个cell设置值 * 6、将整个workbook写入流 * * ***/ public System.IO.MemoryStream DataTableToExcel(DataTable dt) { int rows = dt.Rows.Count; //数据表格的总行数 int cols = dt.Columns.Count; //数据表格的总列数 //1、创建book对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //2、创建sheet对象 var sheet = book.CreateSheet("sheet名称"); //3、创建row(行)对象 //sheet.CreateRow(0) for (int r = 0; r < rows; r++) { var row = sheet.CreateRow(r); for (int c = 0; c < cols; c++) { //4、创建col(列)对象 var col = row.CreateCell(c); //5、为这行这列填充数据 col.SetCellValue(Convert.ToString(dt.Rows[r][c])); } } //6、将整个book写入流 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); return(ms); }
public FileResult ExportExcelForChargeSSR(List <StdSumReport> list) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("时间"); row1.CreateCell(1).SetCellValue("使用气量"); //row1.CreateCell(2).SetCellValue("充值金额"); //将数据逐步写入sheet1各个行 //var list = new List<FMModel>(); for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].dt.ToString()); rowtemp.CreateCell(1).SetCellValue(list[i].span.ToString()); //rowtemp.CreateCell(2).SetCellValue(list[i].sumMoney.ToString()); } MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string dateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string fileName = "用量统计查询" + dateTime + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
private void ExportToFile() { if (DialogResult.OK == this.folderBrowserDialog1.ShowDialog()) { var path = this.folderBrowserDialog1.SelectedPath; NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); var sheet = workbook.CreateSheet(this.dbObject.Name); var headerRow = sheet.CreateRow(0); for (var i = 0; i < this.dtDataResult.Columns.Count; i++) { headerRow.CreateCell(i).SetCellValue(this.dtDataResult.Columns[i].ColumnName); } for (var i = 0; i < this.dtDataResult.Rows.Count; i++) { var newRow = sheet.CreateRow(i + 1); for (var j = 0; j < this.dtDataResult.Columns.Count; j++) { newRow.CreateCell(j).SetCellValue(this.dtDataResult.Rows[i][j].ToString()); } } var filePath = Path.Combine(path, this.dbObject.Name + ".xls"); using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.Read)) { workbook.Write(fs); } } }
public FileResult ExportData(string keyword) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow row1 = sheet1.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("帳戶號碼"); var i = 0; var data = bankRepo.Where(keyword).ToList(); foreach (var item in data) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(item.客戶資料.客戶名稱); rowtemp.CreateCell(1).SetCellValue(item.銀行名稱); rowtemp.CreateCell(2).SetCellValue(item.銀行代碼); rowtemp.CreateCell(3).SetCellValue(Convert.ToString(item.分行代碼)); rowtemp.CreateCell(4).SetCellValue(item.帳戶名稱); rowtemp.CreateCell(5).SetCellValue(Convert.ToString(item.帳戶號碼)); i++; } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "客戶銀行資訊.xls")); }
public FileResult Export(string id) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建Excel文件的对象 NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //给sheet1添加第一行的头部标题 row1.CreateCell(0).SetCellValue("角色"); row1.CreateCell(1).SetCellValue("帐号"); row1.CreateCell(2).SetCellValue("姓名"); row1.CreateCell(3).SetCellValue("过期时间"); row1.CreateCell(4).SetCellValue("状态"); List <int> rids = new List <int>(); string[] ids = id.Split('_'); foreach (var d in ids) { if (!string.IsNullOrWhiteSpace(d)) { rids.Add(int.Parse(d)); } } List <VModel.SyUserManager.Grid> list = Bll.SyUserBll.ExportByRoleIds(rids); int i = 1; foreach (var m in list) { NPOI.SS.UserModel.IRow r = sheet1.CreateRow(i); if (m.RoleNames != null && m.RoleNames.Count > 0) { string roleName = ""; foreach (var name in m.RoleNames) { roleName += name + ","; } if (roleName != "") { roleName = roleName.Substring(0, roleName.Length - 1); } r.CreateCell(0).SetCellValue(roleName); } else { r.CreateCell(0).SetCellValue(""); } r.CreateCell(1).SetCellValue(m.Account); r.CreateCell(2).SetCellValue(m.Name); r.CreateCell(3).SetCellValue(Common.Function.ConvertDate(m.ExpiresTime)); r.CreateCell(4).SetCellValue(Common.Dict.UserState.GetVal(m.IsEnabled)); i++; } System.IO.MemoryStream ms = new System.IO.MemoryStream();// 写入到客户端 book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); string fileName = "角色包含用户表.xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public FileResult ExportData() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("客戶名稱"); row1.CreateCell(1).SetCellValue("聯絡人數量"); row1.CreateCell(2).SetCellValue("銀行帳戶數量"); var i = 0; foreach (var item in db.CustomView.AsEnumerable()) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(item.客戶名稱); rowtemp.CreateCell(1).SetCellValue(item.聯絡人數量.ToString()); rowtemp.CreateCell(2).SetCellValue(item.銀行帳戶數量.ToString()); i++; } MemoryStream ms = new MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "ReadCustomView.xls")); }
public static void ExportXLS(DataTable dt, string[] columns, string fileName) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1"); NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(0); for (int i = 0; i < columns.Length; i++) { row0.CreateCell(i).SetCellValue(columns[i]); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < columns.Length; j++) { row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName)); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public FileResult Export(int appId) { //获取list数据 var checkList = AppSettingBusiness.GetAppSettings(appId); //db.InfoTables.Where(r => r.ProjectName != null).Select(r => new { r.ProjectName, r.InfoTypes, r.field, r.fieldtxt }).ToList(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("配置键"); row1.CreateCell(1).SetCellValue("配置值"); //....N行 //将数据逐步写入sheet1各个行 for (int i = 0; i < checkList.Count(); i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(checkList[i].ConfigKey.ToString()); rowtemp.CreateCell(1).SetCellValue(checkList[i].ConfigValue.ToString()); //....N行 } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyMMddHHmmssfff"); string fileName = "配置" + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public FileResult ExportBanks() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Banks"); NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); var contactors = repo.All().ToList(); row1.CreateCell(0).SetCellValue("銀行名稱"); row1.CreateCell(1).SetCellValue("銀行代碼"); row1.CreateCell(2).SetCellValue("分行代碼"); row1.CreateCell(3).SetCellValue("帳戶名稱"); row1.CreateCell(4).SetCellValue("帳戶號碼"); row1.CreateCell(5).SetCellValue("客戶名稱"); for (int i = 0; i < contactors.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(contactors[i].銀行名稱.ToString()); rowtemp.CreateCell(1).SetCellValue(contactors[i].銀行代碼.ToString()); rowtemp.CreateCell(2).SetCellValue(contactors[i].分行代碼.ToString()); rowtemp.CreateCell(3).SetCellValue(contactors[i].帳戶名稱.ToString()); rowtemp.CreateCell(4).SetCellValue(contactors[i].帳戶號碼.ToString()); rowtemp.CreateCell(5).SetCellValue(contactors[i].客戶資料.客戶名稱.ToString()); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "Banks.xls")); }
public FileResult AgreementDetailListExportExcel(long accountId, int enumOrderTypeId, DateTime?startDate, DateTime?endDate) { var queryModel = new AccountQuery() { StartDate = startDate, EndDate = endDate.HasValue ? endDate.Value.AddDays(1) : endDate, AccountId = accountId, PageSize = int.MaxValue, PageNo = 1 }; QueryPageModel <DTO.AccountMetaModel> pageModelMetaInfo = AccountApplication.GetAccountMeta(queryModel); var mode = pageModelMetaInfo.Models.ToList().Select(e => new DTO.AccountMetaModel { AccountId = e.Id, Id = e.Id, EndDate = e.EndDate, StartDate = e.StartDate, MetaKey = e.MetaKey, MetaValue = e.MetaValue, DateRange = e.StartDate.ToString("yyyy-MM-dd") + " 至 " + e.EndDate.ToString("yyyy-MM-dd") }).ToList(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 //给sheet1添加第一行的头部标题 IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("类型"); row1.CreateCell(1).SetCellValue("营销类型"); row1.CreateCell(2).SetCellValue("费用"); row1.CreateCell(3).SetCellValue("服务周期"); sheet1.SetColumnWidth(0, 550 * 5); sheet1.SetColumnWidth(1, 550 * 20); sheet1.SetColumnWidth(2, 550 * 8); sheet1.SetColumnWidth(3, 550 * 15); //将数据逐步写入sheet1各个行 for (int i = 0; i < mode.Count(); i++) { IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue("营销服务费"); rowtemp.CreateCell(1).SetCellValue(mode[i].MetaKey); rowtemp.CreateCell(2).SetCellValue(mode[i].MetaValue); rowtemp.CreateCell(3).SetCellValue(mode[i].DateRange); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", "结算详情-营销服务费列表.xls")); }
public FileResult ExportExcelForCustomerData(List <CustomerViewRes> list) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.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("自定义编号1"); row1.CreateCell(11).SetCellValue("自定义编号2"); row1.CreateCell(12).SetCellValue("自定义编号3"); row1.CreateCell(13).SetCellValue("备注"); row1.CreateCell(14).SetCellValue("建档时间"); row1.CreateCell(15).SetCellValue("最后编辑时间"); row1.CreateCell(16).SetCellValue("操作员名称"); //将数据逐步写入sheet1各个行 //var list = new List<FMModel>(); for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].customerNo); rowtemp.CreateCell(1).SetCellValue(list[i].customerName); rowtemp.CreateCell(2).SetCellValue(list[i].customerType); rowtemp.CreateCell(3).SetCellValue(list[i].useStateName); rowtemp.CreateCell(4).SetCellValue(list[i].factoryName); rowtemp.CreateCell(5).SetCellValue(list[i].meterTypeName); rowtemp.CreateCell(6).SetCellValue(list[i].telNo); rowtemp.CreateCell(7).SetCellValue(list[i].mobileNo); rowtemp.CreateCell(8).SetCellValue(list[i].estateName); rowtemp.CreateCell(9).SetCellValue(list[i].address); rowtemp.CreateCell(10).SetCellValue(list[i].defineNo1); rowtemp.CreateCell(11).SetCellValue(list[i].defineNo2); rowtemp.CreateCell(12).SetCellValue(list[i].defineNo3); rowtemp.CreateCell(13).SetCellValue(list[i].remark); rowtemp.CreateCell(14).SetCellValue(list[i].buildTime); rowtemp.CreateCell(15).SetCellValue(list[i].editTime); rowtemp.CreateCell(16).SetCellValue(list[i].Operator); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string dateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string fileName = "客户数据查询" + dateTime + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public FileResult ExportExcelForMeterData(List <OneFLMeterDataViewRes> list) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.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("剩余金额"); row1.CreateCell(12).SetCellValue("剩余气量"); row1.CreateCell(13).SetCellValue("供电电压"); row1.CreateCell(14).SetCellValue("表具状态"); row1.CreateCell(15).SetCellValue("阀门状态"); row1.CreateCell(16).SetCellValue("采集时间"); //将数据逐步写入sheet1各个行 //var list = new List<FMModel>(); for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].LoginStateMsg); rowtemp.CreateCell(1).SetCellValue(list[i].deviceNo); rowtemp.CreateCell(2).SetCellValue(list[i].communicateNo); rowtemp.CreateCell(3).SetCellValue(list[i].customerName); rowtemp.CreateCell(4).SetCellValue(list[i].address); rowtemp.CreateCell(5).SetCellValue(list[i].StdSum); rowtemp.CreateCell(6).SetCellValue(list[i].WorkSum); rowtemp.CreateCell(7).SetCellValue(list[i].StdFlow); rowtemp.CreateCell(8).SetCellValue(list[i].WorkFlow); rowtemp.CreateCell(9).SetCellValue(list[i].Temperature); rowtemp.CreateCell(10).SetCellValue(list[i].Pressure); rowtemp.CreateCell(11).SetCellValue(list[i].RemainMoney); rowtemp.CreateCell(12).SetCellValue(list[i].RemainVolume); rowtemp.CreateCell(13).SetCellValue(list[i].PowerVoltage); rowtemp.CreateCell(14).SetCellValue(list[i].FMStateMsg); rowtemp.CreateCell(15).SetCellValue(list[i].ValveStateMsg); rowtemp.CreateCell(16).SetCellValue(list[i].InstantTime); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); string dateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string fileName = "实时数据查询" + dateTime + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
protected static MemoryStream GetMS(List <FishEntity.CallRecordsEntity> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.HeightInPoints = 26; CreateCell(row, 0, "通话记录表", CellType.String, cellStyle); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12)); row = sheet.CreateRow(1); cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold); CreateCell(row, 0, "记录单号", CellType.String, cellStyle); CreateCell(row, 1, "客户名称", CellType.String, cellStyle); CreateCell(row, 2, "联系人", CellType.String, cellStyle); CreateCell(row, 3, "移动电话", CellType.String, cellStyle); CreateCell(row, 4, "固定电话", CellType.String, cellStyle); CreateCell(row, 5, "客户等级", CellType.String, cellStyle); CreateCell(row, 6, "日期", CellType.String, cellStyle); CreateCell(row, 7, "沟通内容", CellType.String, cellStyle); CreateCell(row, 8, "品质要求", CellType.String, cellStyle); CreateCell(row, 9, "主要产品", CellType.String, cellStyle); CreateCell(row, 10, "估计周用量", CellType.String, cellStyle); CreateCell(row, 11, "估计月用量", CellType.String, cellStyle); CreateCell(row, 12, "地址", CellType.String, cellStyle); if (list != null && list.Count > 0) { int rowidx = 1; foreach (FishEntity.CallRecordsEntity model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.code); CreateCell(row, 1, model.customer); CreateCell(row, 2, model.linkman); CreateCell(row, 3, model.mobile); CreateCell(row, 4, model.telephone); CreateCell(row, 5, model.customerlevel); CreateCell(row, 6, model.currentdate.Value.ToString("yyyy-MM-dd")); CreateCell(row, 7, model.communicatecontent); CreateCell(row, 8, model.requiredquantity); CreateCell(row, 9, model.products); CreateCell(row, 10, model.weekestimate); CreateCell(row, 11, model.monthestimate); CreateCell(row, 12, model.address); # endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
void CreateExcel(object sender) { try { string filePath = Application.StartupPath + "\\DailyPOSSales.xls"; if (System.IO.File.Exists(filePath)) { File.Delete(filePath); } NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Daily POS Sales"); //第1行 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); ICell fistCell = row.CreateCell(0); fistCell.SetCellValue("Daily POS Sales"); ICellStyle firstRowStyle = workbook.CreateCellStyle(); firstRowStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; IFont firstRowFont = workbook.CreateFont(); firstRowFont.FontName = "Tahoma"; firstRowFont.FontHeightInPoints = 20; firstRowFont.Color = HSSFColor.Blue.Index; firstRowFont.Boldweight = short.MaxValue; firstRowStyle.SetFont(firstRowFont); fistCell.CellStyle = firstRowStyle; row.HeightInPoints = 25; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15)); using (FileStream fs = File.OpenWrite(filePath)) { workbook.Write(fs); } } catch (Exception exception) { MessageBox.Show(exception.Message); } finally { //this.Invoke(new Action(() => //{ // (sender as Button).Enabled = true; //})); } }
public ActionResult ExportMsg() { List<String> list = new List<String>(); List<String> listName = new List<String>(); #region 初始化数据 //得到选中项 这是比较笨的方法,有机会要改进哈 if (Request.Form["StuNum"] != null) { list.Add("StuNum"); listName.Add("学号"); } if (Request.Form["StuName"] != null) { list.Add("StuName"); listName.Add("姓名"); } if (Request.Form["Gender"] != null) { list.Add("Gender"); listName.Add("性别"); } if (Request.Form["QQNum"] != null) { list.Add("QQNum"); listName.Add("QQ号"); } if (Request.Form["Email"] != null) { list.Add("Email"); listName.Add("电子邮箱"); } if (Request.Form["Birthday"] != null) { list.Add("Birthday"); listName.Add("生日"); } if (Request.Form["Class"] != null) { list.Add("Class"); listName.Add("班级"); } if (Request.Form["TelephoneNumber"] != null) { list.Add("TelephoneNumber"); listName.Add("电话号码"); } if (Request.Form["Department"] != null) { list.Add("Department"); listName.Add("部门"); } if (Request.Form["StudyGuideNumber"] != null) { list.Add("StudyGuideNumber"); listName.Add("指导学长学姐"); } if (Request.Form["JoinTime"] != null) { list.Add("JoinTime"); listName.Add("加入时间"); } if (Request.Form["Major"] != null) { list.Add("Major"); listName.Add("主修"); } if (Request.Form["Counseloer"] != null) { list.Add("Counselor"); listName.Add("辅导员"); } if (Request.Form["HeadTeacher"] != null) { list.Add("HeadTeacher"); listName.Add("班主任"); } if (Request.Form["UndergraduateTutor"] != null) { list.Add("UndergraduateTutor"); listName.Add("毕业导师"); } if (Request.Form["HomPhoneNumber"] != null) { list.Add("HomPhoneNumber"); listName.Add("家庭电话"); } if (Request.Form["FamilyAddress"] != null) { list.Add("FamilyAddress"); listName.Add("家庭地址"); } if (Request.Form["Sign"] != null) { list.Add("Sign"); listName.Add("个性签名"); } int depart=0; if (!string.IsNullOrEmpty((Request.Form["depart"]))) { depart = Convert.ToInt32(Request.Form["depart"]); } if (list.Count == 0) { return Content("<script>alert('您没有选择任何项喔~~!');window.location='/PersonalManger/CheckMember/index'</script>"); } DataTable dt = OperateContext.Current.BLLSession.IMemberInformationBLL.GetPartData(list,depart); //如果选择了部门那么导出时要将数字转为部门名 int count = 0; //如果选择的指导学长学姐要将学号转换为姓名,下面变量是的得到导出的学号在list列表里的顺序 int guideCount=0; if (Request.Form["Department"] != null) { for (int i = 0; i < list.Count; i++) { if (list[i] == "Department") { count = i; } } } if (Request.Form["StudyGuideNumber"] != null) { for (int i = 0; i<list.Count; i++) { if (list[i] == "StudyGuideNumber") { guideCount = i; } } } #endregion NPOI.HSSF.UserModel.HSSFWorkbook workbook= new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.HSSF.UserModel.HSSFSheet sheet = workbook.CreateSheet("第一页") as NPOI.HSSF.UserModel.HSSFSheet; NPOI.HSSF.UserModel.HSSFRow hr = sheet.CreateRow(0) as NPOI.HSSF.UserModel.HSSFRow; for(int i=0;i<listName.Count;i++) { hr.CreateCell(i).SetCellValue(listName[i]); } for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++) { NPOI.HSSF.UserModel.HSSFRow hro = sheet.CreateRow(rowIndex + 1) as NPOI.HSSF.UserModel.HSSFRow; for (int colIndex = 0; colIndex < listName.Count; colIndex++) { string str=""; if (colIndex == count&&count!=0) { if (dt.Rows[rowIndex][colIndex].ToString() == "10001") { str = "NET应用开发部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10002") { str = "安卓应用开发部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10003") { str = "硬件编程技术部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10004") { str = "系统编程技术部"; } if (dt.Rows[rowIndex][colIndex].ToString() == "10007") { str = "暂未加入部门"; } hro.CreateCell(colIndex).SetCellValue(str); } else { if (colIndex == guideCount && guideCount != 0) { string num = dt.Rows[rowIndex][colIndex].ToString(); if (!string.IsNullOrEmpty(num)) { string name = OperateContext.Current.BLLSession.IMemberInformationBLL.GetListBy(u => u.StuNum == num).FirstOrDefault().StuName; hro.CreateCell(colIndex).SetCellValue(name); } } else { hro.CreateCell(colIndex).SetCellValue(dt.Rows[rowIndex][colIndex].ToString()); } } } } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel"); }
private void SaveExcel(List<FileProperty> flist) { saveFileDialog1.OverwritePrompt = true; saveFileDialog1.AddExtension = true; saveFileDialog1.DefaultExt = ".xls"; saveFileDialog1.AutoUpgradeEnabled = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { toolStripStatusLabel1.Text = "正在生成Excel文件"; string path = saveFileDialog1.FileName; NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = workBook.CreateSheet("Sheet1"); NPOI.SS.UserModel.Row row1 = sheet.CreateRow(0); row1.CreateCell(0).SetCellValue("Title"); row1.CreateCell(1).SetCellValue("FilePath"); row1.CreateCell(2).SetCellValue("Description"); row1.CreateCell(3).SetCellValue("Tags"); row1.CreateCell(4).SetCellValue("Price"); row1.CreateCell(5).SetCellValue("CateId"); row1.CreateCell(6).SetCellValue("Test"); for(int i=0;i<flist.Count;i++) { NPOI.SS.UserModel.Row r = sheet.CreateRow(i + 1); r.CreateCell(0).SetCellValue(flist[i].Name); r.CreateCell(1).SetCellValue(flist[i].Path); } using (FileStream fs = new FileStream(path, FileMode.Create)) { workBook.Write(fs); } toolStripStatusLabel1.Text = "生成Excel成功"; } }
public ActionResult MaterialExport(string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zStatus, string dStatus, string wStatus, string queryDescribe) { List<ImOrExMaterialDefine> list = new List<ImOrExMaterialDefine>(); NPOI.SS.UserModel.IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); try { ISheet sheet = wb.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 20 * 256); sheet.SetColumnWidth(1, 13 * 256); sheet.SetColumnWidth(2, 30 * 256); sheet.SetColumnWidth(3, 15 * 256); sheet.SetColumnWidth(4, 15 * 256); sheet.SetColumnWidth(5, 15 * 256); int iRow = 0; NPOI.SS.UserModel.IRow titleRow = sheet.CreateRow(0); titleRow.Height = 20 * 20; for (int i = 0; i < ImOrExMaterialDefine.GetList().Count; i++) { titleRow.CreateCell(i).SetCellValue(ImOrExMaterialDefine.DisplayText(ImOrExMaterialDefine.GetList()[i].ToString())); } YnBaseDal.YnPage ynPage = new YnBaseDal.YnPage(); List<AscmMaterialItem> listAscmMaterialItem = AscmMaterialItemService.GetInstance().GetList(ynPage, "", "", queryWord, queryType, queryStarDocnumber, queryEndDocnumber, zStatus, dStatus, wStatus, queryDescribe); if (listAscmMaterialItem != null && listAscmMaterialItem.Count > 0) { ImOrExMaterialDefine titleExportMaterial = new ImOrExMaterialDefine(); List<string> titleCols = ImOrExMaterialDefine.GetList(); titleExportMaterial.materialDocnumber = ImOrExMaterialDefine.DisplayText(titleCols[0]); titleExportMaterial.wipSupplyType = ImOrExMaterialDefine.DisplayText(titleCols[1]); titleExportMaterial.materialDescription = ImOrExMaterialDefine.DisplayText(titleCols[2]); titleExportMaterial.zMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[3]); titleExportMaterial.dMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[4]); titleExportMaterial.wMtlCategoryStatus = ImOrExMaterialDefine.DisplayText(titleCols[5]); titleExportMaterial.rowNumber = iRow++; list.Add(titleExportMaterial); foreach (AscmMaterialItem ascmMaterialItem in listAscmMaterialItem) { ImOrExMaterialDefine contentExportMaterial = new ImOrExMaterialDefine(); contentExportMaterial.materialDocnumber = ascmMaterialItem.docNumber; contentExportMaterial.wipSupplyType = AscmMaterialItem.WipSupplyTypeDefine.DisplayText(ascmMaterialItem.wipSupplyType); contentExportMaterial.materialDescription = ascmMaterialItem.description; contentExportMaterial.zMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.zMtlCategoryStatus); contentExportMaterial.dMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.dMtlCategoryStatus); contentExportMaterial.wMtlCategoryStatus = MtlCategoryStatusDefine.DisplayText(ascmMaterialItem.wMtlCategoryStatus); contentExportMaterial.rowNumber = iRow++; list.Add(contentExportMaterial); } } if (list != null && list.Count > 0) { foreach (ImOrExMaterialDefine ExportMaterial in list) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(ExportMaterial.rowNumber); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(ExportMaterial.materialDocnumber); row.CreateCell(1).SetCellValue(ExportMaterial.wipSupplyType); row.CreateCell(2).SetCellValue(ExportMaterial.materialDescription); row.CreateCell(3).SetCellValue(ExportMaterial.zMtlCategoryStatus); row.CreateCell(4).SetCellValue(ExportMaterial.dMtlCategoryStatus); row.CreateCell(5).SetCellValue(ExportMaterial.wMtlCategoryStatus); } } } catch (Exception ex) { throw ex; } byte[] buffer = new byte[] { }; using (System.IO.MemoryStream stream = new MemoryStream()) { wb.Write(stream); buffer = stream.GetBuffer(); } return File(buffer, "application/vnd.ms-excel", "备料形式维护.xls"); }
private void RelatorioGerencial_GerarArquivo(System.Data.DataSet dados, String path, String fileName, String template) { #region Declaração de variáveis Boolean sucesso = false; System.IO.FileStream file = null; System.IO.FileStream fileTemplate = null; NPOI.HSSF.UserModel.HSSFWorkbook xlPackage = null; #endregion try { #region Inicialização de objetos fileTemplate = new System.IO.FileStream(path + template, System.IO.FileMode.Open, System.IO.FileAccess.Read); #endregion #region Gera o arquivo do relatório xlPackage = new NPOI.HSSF.UserModel.HSSFWorkbook(fileTemplate); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[0], "Consolidado Geral"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[1], "Abertura de Pastas (Workflow)"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[2], "Abertura de Pastas DC"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[3], "Bloqueado Atual"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[4], "Workflow de Documentos"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[5], "Documentos Vinculados a Pasta"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[6], "Audiência por Data"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[7], "SS com Pasta por data Upload"); this.RelatorioGerencial_PreencherDados(xlPackage, dados.Tables[8], "DC Prazo por Usuário x Cliente"); file = new System.IO.FileStream(path + fileName, System.IO.FileMode.Create); xlPackage.Write(file); file.Close(); #endregion } catch (Exception ex) { #region Tratamento de erro throw new Exception("MMAA.Pedidos.Web.WS.Pedido.RelatorioGerencial_GerarArquivo: " + ex.Message); #endregion } }
private void Relatorio_GerarArquivo(System.Data.DataSet dados, String path, String fileName, String template) { #region Declaração de variáveis Boolean sucesso = false; System.IO.FileStream file = null; System.IO.FileStream fileTemplate = null; NPOI.HSSF.UserModel.HSSFWorkbook xlPackage = null; #endregion try { #region Inicialização de objetos fileTemplate = new System.IO.FileStream(path + template, System.IO.FileMode.Open, System.IO.FileAccess.Read); #endregion #region Gera o arquivo do relatório xlPackage = new NPOI.HSSF.UserModel.HSSFWorkbook(fileTemplate); this.Relatorio_PreencherDados(xlPackage, dados.Tables[0], "Processos"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[1], "Pedidos"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[2], "Escritório"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[3], "Risco x Valor"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[4], "Tipo Especialidade x Valor"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[5], "Escritório x Valor"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[6], "Top 50 Processos"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[7], "Top 20 Assuntos"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[8], "Diferença ICMS-SP x Selic"); this.Relatorio_PreencherDados(xlPackage, dados.Tables[9], "Processos Decaídos"); file = new System.IO.FileStream(path + fileName, System.IO.FileMode.Create); xlPackage.Write(file); file.Close(); #endregion } catch (Exception ex) { #region Tratamento de erro throw new Exception("MMAA.Pedidos.Web.WS.Pedido.Relatorio_GerarArquivo: " + ex.Message); #endregion } }
public ActionResult ExportWipDiscreteJobs(string queryWord, string queryStartDate, string queryEndDate, string queryStartJobDate, string queryEndJobDate, string queryType, string queryPerson) { string userName = string.Empty; if (User.Identity.IsAuthenticated) { userName = User.Identity.Name; } IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 20 * 256); sheet.SetColumnWidth(1, 12 * 256); sheet.SetColumnWidth(2, 18 * 256); sheet.SetColumnWidth(3, 60 * 256); sheet.SetColumnWidth(4, 8 * 256); sheet.SetColumnWidth(5, 8 * 256); sheet.SetColumnWidth(6, 30 * 256); sheet.SetColumnWidth(7, 15 * 256); int iRow = 0; IRow titleRow = sheet.CreateRow(iRow); titleRow.Height = 20 * 20; titleRow.CreateCell(0).SetCellValue("作业号"); titleRow.CreateCell(1).SetCellValue("作业日期"); titleRow.CreateCell(2).SetCellValue("装配件"); titleRow.CreateCell(3).SetCellValue("装配件描述"); titleRow.CreateCell(4).SetCellValue("需求数"); titleRow.CreateCell(5).SetCellValue("生产线"); titleRow.CreateCell(6).SetCellValue("备注"); titleRow.CreateCell(7).SetCellValue("上线时间"); string userRole = AscmUserInfoService.GetInstance().GetUserRoleName(userName); string userLogisticsClass = AscmUserInfoService.GetInstance().GetUserLogisticsName(userName); string whereOther = "", whereQueryWord = ""; if (userRole.IndexOf("物流班长") > -1 || userRole.IndexOf("物流组长") > -1) { string ids_userId = AscmAllocateRuleService.GetInstance().GetLogisticsRankerName(userLogisticsClass, userName); if (!string.IsNullOrEmpty(ids_userId)) whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids_userId, "workerId"); whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (userRole.IndexOf("排产员") > -1) { whereQueryWord = "workerId = '" + userName + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (userRole.IndexOf("领料员") > -1) { string ids_userId = AscmAllocateRuleService.GetInstance().GetLogisticsRankerName(userLogisticsClass, userName, true); if (!string.IsNullOrEmpty(ids_userId)) whereQueryWord = AscmCommonHelperService.GetInstance().IsJudgeListCount(ids_userId, "workerId"); whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } if (string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate) && string.IsNullOrEmpty(queryStartJobDate) && string.IsNullOrEmpty(queryEndJobDate)) throw new Exception("请选择导出日期!"); //上传日期 if (!string.IsNullOrEmpty(queryStartDate) && !string.IsNullOrEmpty(queryEndDate)) { queryStartDate = queryStartDate + " 00:00:00"; queryEndDate = queryEndDate + " 23:59:59"; whereQueryWord = "time >= '" + queryStartDate + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); whereQueryWord = "time <= '" + queryEndDate + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (!string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate)) { whereQueryWord = "time like '" + queryStartDate + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (string.IsNullOrEmpty(queryStartDate) && string.IsNullOrEmpty(queryEndDate)) { whereQueryWord = "time like '" + queryEndDate + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } //作业日期 if (!string.IsNullOrEmpty(queryStartJobDate) && !string.IsNullOrEmpty(queryEndJobDate)) { queryStartJobDate = queryStartJobDate + " 00:00:00"; queryEndJobDate = queryEndJobDate + " 23:59:59"; whereQueryWord = "time >= '" + queryStartJobDate + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); whereQueryWord = "time <= '" + queryEndJobDate + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (!string.IsNullOrEmpty(queryStartJobDate) && string.IsNullOrEmpty(queryEndJobDate)) { whereQueryWord = "time like '" + queryStartJobDate + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (string.IsNullOrEmpty(queryStartJobDate) && !string.IsNullOrEmpty(queryEndJobDate)) { whereQueryWord = "time like '" + queryEndJobDate + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } if (!string.IsNullOrEmpty(queryType)) { whereQueryWord = "identificationId = " + queryType; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } if (!string.IsNullOrEmpty(queryPerson)) { whereQueryWord = "workerId = '" + queryPerson + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } whereQueryWord = "status > 0"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); try { List<AscmDiscreteJobs> listAscmDiscreteJobs = AscmDiscreteJobsService.GetInstance().GetList(null, "", "", queryWord, whereOther, false); if (listAscmDiscreteJobs != null && listAscmDiscreteJobs.Count > 0) { foreach (AscmDiscreteJobs ascmDiscreteJobs in listAscmDiscreteJobs) { iRow++; IRow row = sheet.CreateRow(iRow); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(ascmDiscreteJobs.jobId); row.CreateCell(1).SetCellValue(ascmDiscreteJobs.jobDate); row.CreateCell(2).SetCellValue(ascmDiscreteJobs.jobInfoId); row.CreateCell(3).SetCellValue(ascmDiscreteJobs.jobDesc); row.CreateCell(4).SetCellValue(ascmDiscreteJobs.count); row.CreateCell(5).SetCellValue(ascmDiscreteJobs.lineAndSequence); row.CreateCell(6).SetCellValue(ascmDiscreteJobs.tip); row.CreateCell(7).SetCellValue(ascmDiscreteJobs.onlineTime); } } } catch (Exception ex) { YnBaseClass2.Helper.LogHelper.GetLog().Error("导出失败(Export AscmDiscreteJobs)", ex); throw ex; } byte[] buffer = new byte[] { }; using (System.IO.MemoryStream stream = new MemoryStream()) { wb.Write(stream); buffer = stream.GetBuffer(); } string fileName = DateTime.Now.ToString("yyyyMMhh") + "排产单.xls"; return File(buffer, "application/vnd.ms-excel", fileName); }
public ActionResult MaterialExport(string queryWord, string queryType, string queryStarDocnumber, string queryEndDocnumber, string zmtlCategoryStatus, string dmtlCategoryStatus, string wmtlCategoryStatus, string queryDescribe) { string userName = string.Empty; if (User.Identity.IsAuthenticated) { userName = User.Identity.Name; } IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 20 * 256); sheet.SetColumnWidth(1, 13 * 256); sheet.SetColumnWidth(2, 30 * 256); sheet.SetColumnWidth(3, 15 * 256); sheet.SetColumnWidth(4, 15 * 256); sheet.SetColumnWidth(5, 15 * 256); int iRow = 0; IRow titleRow = sheet.CreateRow(iRow); titleRow.Height = 20 * 20; titleRow.CreateCell(0).SetCellValue("物料编码"); titleRow.CreateCell(1).SetCellValue("供应类型"); titleRow.CreateCell(2).SetCellValue("物料描述"); titleRow.CreateCell(3).SetCellValue("总装备料形式"); titleRow.CreateCell(4).SetCellValue("电装备料形式"); titleRow.CreateCell(5).SetCellValue("其他备料形式"); try { string whereOther = "", whereQueryWord = ""; whereQueryWord = "wipSupplyType < 4"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); if (!string.IsNullOrEmpty(queryType)) { whereQueryWord = "wipSupplyType = " + queryType; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } if (!string.IsNullOrEmpty(queryStarDocnumber) && !string.IsNullOrEmpty(queryEndDocnumber)) { if (queryStarDocnumber == queryEndDocnumber) { whereQueryWord = "docNumber like '" + queryStarDocnumber + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else { whereQueryWord = "docNumber >= '" + queryStarDocnumber + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); whereQueryWord = "docNumber <= '" + queryEndDocnumber + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } } else if (!string.IsNullOrEmpty(queryStarDocnumber) && string.IsNullOrEmpty(queryEndDocnumber)) { whereQueryWord = "docNumber like '" + queryStarDocnumber + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else if (string.IsNullOrEmpty(queryStarDocnumber) && !string.IsNullOrEmpty(queryEndDocnumber)) { whereQueryWord = "docNumber like '" + queryEndDocnumber + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } else { whereQueryWord = "docNumber like '20%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } if (!string.IsNullOrEmpty(zmtlCategoryStatus)) { if (zmtlCategoryStatus != "qb") { if (zmtlCategoryStatus != "kz") whereQueryWord = "zMtlCategoryStatus = '" + zmtlCategoryStatus + "'"; else whereQueryWord = "zMtlCategoryStatus is null"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } } if (!string.IsNullOrEmpty(dmtlCategoryStatus)) { if (dmtlCategoryStatus != "qb") { if (dmtlCategoryStatus != "kz") whereQueryWord = "dMtlCategoryStatus = '" + dmtlCategoryStatus + "'"; else whereQueryWord = "dMtlCategoryStatus is null"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } } if (!string.IsNullOrEmpty(wmtlCategoryStatus)) { if (wmtlCategoryStatus != "qb") { if (wmtlCategoryStatus != "kz") whereQueryWord = "wMtlCategoryStatus = '" + wmtlCategoryStatus + "'"; else whereQueryWord = "wMtlCategoryStatus is null"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } } if (string.IsNullOrEmpty(zmtlCategoryStatus) && string.IsNullOrEmpty(dmtlCategoryStatus) && string.IsNullOrEmpty(wmtlCategoryStatus)) { whereQueryWord = "isFlag = 0"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } if (!string.IsNullOrEmpty(queryDescribe)) { whereQueryWord = "description like '%" + queryDescribe + "%'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereQueryWord); } List<AscmMaterialItem> list = AscmMaterialItemService.GetInstance().GetList(null, "", "", "", whereOther, false); if (list != null && list.Count > 0) { foreach (AscmMaterialItem ascmMateiralItem in list) { iRow++; IRow row = sheet.CreateRow(iRow); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(ascmMateiralItem.docNumber); row.CreateCell(1).SetCellValue(ascmMateiralItem.wipSupplyTypeCn); row.CreateCell(2).SetCellValue(ascmMateiralItem.description); row.CreateCell(3).SetCellValue(ascmMateiralItem._zMtlCategoryStatus); row.CreateCell(4).SetCellValue(ascmMateiralItem._dMtlCategoryStatus); row.CreateCell(5).SetCellValue(ascmMateiralItem._wMtlCategoryStatus); } } } catch (Exception ex) { YnBaseClass2.Helper.LogHelper.GetLog().Error("导出失败(Export AscmMaterialItem)", ex); throw ex; } byte[] buffer = new byte[] { }; using (System.IO.MemoryStream stream = new MemoryStream()) { wb.Write(stream); buffer = stream.GetBuffer(); } return File(buffer, "application/vnd.ms-excel", "备料形式维护.xls"); }
private void Relatorio_GerarArquivoProcesso(System.Data.DataSet dados, String path, String fileName, String template) { #region Declaração de variáveis Boolean sucesso = false; System.IO.FileStream file = null; System.IO.FileStream fileTemplate = null; NPOI.HSSF.UserModel.HSSFWorkbook xlPackage = null; #endregion try { #region Inicialização de objetos fileTemplate = new System.IO.FileStream(path + template, System.IO.FileMode.Open, System.IO.FileAccess.Read); #endregion #region Gera o arquivo do relatório xlPackage = new NPOI.HSSF.UserModel.HSSFWorkbook(fileTemplate); this.Relatorio_PreencherDados(xlPackage, dados.Tables[0], "Processos"); file = new System.IO.FileStream(path + fileName, System.IO.FileMode.Create); xlPackage.Write(file); file.Close(); #endregion } catch (Exception ex) { #region Tratamento de erro throw new Exception("MMAA.Pedidos.Web.WS.Pedido.Relatorio_GerarArquivoProcesso: " + ex.Message); #endregion } }
public FileResult Export() { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 List<客戶聯絡人> data = repo.Get客戶聯絡人().ToList(); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("職稱"); row1.CreateCell(1).SetCellValue("姓名"); row1.CreateCell(2).SetCellValue("Email"); row1.CreateCell(3).SetCellValue("手機"); row1.CreateCell(4).SetCellValue("電話"); row1.CreateCell(5).SetCellValue("客戶名稱"); //将数据逐步写入sheet1各个行 for (int i = 0; i < data.Count(); i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(data[i].職稱); rowtemp.CreateCell(1).SetCellValue(data[i].姓名); rowtemp.CreateCell(2).SetCellValue(data[i].Email); rowtemp.CreateCell(3).SetCellValue(data[i].手機); rowtemp.CreateCell(4).SetCellValue(data[i].電話); rowtemp.CreateCell(5).SetCellValue(data[i].客戶資料.客戶名稱); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel", "客戶聯絡人_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); }
public FileResult Export清單() { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 List<vw客戶清單> data = repo客戶清單.All().ToList(); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("客戶名稱"); row1.CreateCell(1).SetCellValue("聯絡人數量"); row1.CreateCell(2).SetCellValue("銀行帳戶數量"); //将数据逐步写入sheet1各个行 for (int i = 0; i < data.Count(); i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(data[i].客戶名稱); rowtemp.CreateCell(1).SetCellValue(data[i].聯絡人數量.ToString()); rowtemp.CreateCell(2).SetCellValue(data[i].銀行帳戶數量.ToString()); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel", "客戶清單_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); }
public static MemoryStream RenderToExcel(List<Models.Beans.Contract> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); #region head row NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(font); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; //row.RowStyle = style; row.Height = 26 * 20; CreateCell(row, 0, "存放位置", CellType.String, style); CreateCell(row, 1, "合同号", CellType.String, style); CreateCell(row, 2, "序号", CellType.String, style); CreateCell(row, 3, "项目编号", CellType.String, style); CreateCell(row, 4, "项目名称", CellType.String, style); CreateCell(row, 5, "项目负责人", CellType.String, style); CreateCell(row, 6, "联系方式", CellType.String, style); CreateCell(row, 7, "分管部门", CellType.String, style); CreateCell(row, 8, "分包名称", CellType.String, style); CreateCell(row, 9, "分包预算(万元)", CellType.String, style); CreateCell(row, 10, "招标编号", CellType.String, style); CreateCell(row, 11, "招标公司", CellType.String, style); CreateCell(row, 12, "开标时间", CellType.String, style); CreateCell(row, 13, "付款方式", CellType.String, style); CreateCell(row, 14, "中标公司名称", CellType.String, style); CreateCell(row, 15, "联系人", CellType.String, style); CreateCell(row, 16, "手机号码", CellType.String, style); CreateCell(row, 17, "中标金额(万元)", CellType.String, style); CreateCell(row, 18, "签合同日期", CellType.String, style); CreateCell(row, 19, "交货时间", CellType.String, style); CreateCell(row, 20, "验收情况", CellType.String, style); CreateCell(row, 21, "进度", CellType.String, style); CreateCell(row, 22, "支付全款", CellType.String, style); CreateCell(row, 23, "押款", CellType.String, style); CreateCell(row, 24, "退款", CellType.String, style); CreateCell(row, 25, "标签", CellType.String, style); #endregion if (list != null && list.Count > 0) { int rowidx = 0; foreach (Models.Beans.Contract model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.contractplace); CreateCell(row, 1, model.contractnum); CreateCell(row, 2, model.seq); CreateCell(row, 3, model.projectnum); CreateCell(row, 4, model.projectname); CreateCell(row, 5, model.projectmanager); CreateCell(row, 6, model.tel); CreateCell(row, 7, model.depart); CreateCell(row, 8, model.packageName); CreateCell(row, 9, model.packageBudget); CreateCell(row, 10, model.tendarNum); CreateCell(row, 11, model.tendarCompany); CreateCell(row, 12, model.tendarStartTime); CreateCell(row, 13, model.paymethod); CreateCell(row, 14, model.bcompany); CreateCell(row, 15, model.linker); CreateCell(row, 16, model.phone); CreateCell(row, 17, model.money); CreateCell(row, 18, model.signingdate); CreateCell(row, 19, model.deliveryTime); CreateCell(row, 20, model.inspection); CreateCell(row, 21, model.progress); CreateCell(row, 22, model.isPayAll); CreateCell(row, 23, model.isArmoured); CreateCell(row, 24, model.isRefund); CreateCell(row, 25, model.contractrfid); #endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; }
// 送货合单查询报表导出 public ActionResult DeliveryBatSumExport(int? supplierId, string queryWord, string startCreateTime, string endCreateTime, string status, int? driverId, string batchSumBarCode) { IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 16 * 256); sheet.SetColumnWidth(1, 14 * 256); sheet.SetColumnWidth(2, 26 * 256); sheet.SetColumnWidth(3, 10 * 256); sheet.SetColumnWidth(4, 17 * 256); sheet.SetColumnWidth(5, 17 * 256); sheet.SetColumnWidth(6, 10 * 256); sheet.SetColumnWidth(7, 12 * 256); sheet.SetColumnWidth(8, 10 * 256); sheet.SetColumnWidth(9, 17 * 256); sheet.SetColumnWidth(10, 17 * 256); sheet.SetColumnWidth(11, 17 * 256); IRow titleRow = sheet.CreateRow(0); titleRow.Height = 20 * 20; titleRow.CreateCell(0).SetCellValue("合单号"); titleRow.CreateCell(1).SetCellValue("司机编号"); titleRow.CreateCell(2).SetCellValue("供应商名称"); titleRow.CreateCell(3).SetCellValue("状态"); titleRow.CreateCell(4).SetCellValue("预约开始时间"); titleRow.CreateCell(5).SetCellValue("预约最后时间"); titleRow.CreateCell(6).SetCellValue("总数量"); titleRow.CreateCell(7).SetCellValue("容器绑定数量"); titleRow.CreateCell(8).SetCellValue("确认人"); titleRow.CreateCell(9).SetCellValue("确认时间"); titleRow.CreateCell(10).SetCellValue("到厂时间"); titleRow.CreateCell(11).SetCellValue("接收时间"); string fileDownloadName = "供应商送货合单查询报表"; string whereOther = "", whereSupplier = "", whereStatus = ""; string whereStartCreateTime = "", whereEndCreateTime = "", whereDriver = ""; if (supplierId.HasValue) whereSupplier = " supplierId=" + supplierId.Value; if (!string.IsNullOrEmpty(status)) { foreach (string itemStatus in status.Split(',')) { if (!string.IsNullOrEmpty(whereStatus)) whereStatus += " or "; whereStatus += " status='" + itemStatus + "'"; } } if (driverId.HasValue) whereDriver = " driverId=" + driverId.Value; DateTime dtStartCreateTime, dtEndCreateTime; if (!string.IsNullOrEmpty(startCreateTime) && DateTime.TryParse(startCreateTime, out dtStartCreateTime)) whereStartCreateTime = "createTime>='" + dtStartCreateTime.ToString("yyyy-MM-dd 00:00:00") + "'"; if (!string.IsNullOrEmpty(endCreateTime) && DateTime.TryParse(endCreateTime, out dtEndCreateTime)) whereEndCreateTime = "createTime<'" + dtEndCreateTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00") + "'"; whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereSupplier); whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereStatus); whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereDriver); whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereStartCreateTime); whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, whereEndCreateTime); if (batchSumBarCode != null && batchSumBarCode.Trim() != "") whereOther = YnBaseClass2.Helper.StringHelper.SqlWhereAndAdd(whereOther, "barcode='" + batchSumBarCode.Trim() + "'"); List<AscmDeliBatSumMain> list = AscmDeliBatSumMainService.GetInstance().GetList(null, string.Empty, string.Empty, queryWord, whereOther); if (list != null) { int rowIndex = 0; foreach (AscmDeliBatSumMain deliBatSumMain in list) { IRow row = sheet.CreateRow(++rowIndex); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(deliBatSumMain.docNumber); row.CreateCell(1).SetCellValue(deliBatSumMain.driverSn); row.CreateCell(2).SetCellValue(deliBatSumMain.supplierName); row.CreateCell(3).SetCellValue(deliBatSumMain.statusCn); row.CreateCell(4).SetCellValue(deliBatSumMain.appointmentStartTimeShow); row.CreateCell(5).SetCellValue(deliBatSumMain.appointmentEndTimeShow); row.CreateCell(6).SetCellValue(deliBatSumMain.totalNumber.ToString()); row.CreateCell(7).SetCellValue(deliBatSumMain.containerBindNumber.ToString()); row.CreateCell(8).SetCellValue(deliBatSumMain.confirmor); row.CreateCell(9).SetCellValue(deliBatSumMain._confirmTime); row.CreateCell(10).SetCellValue(deliBatSumMain._toPlantTime); row.CreateCell(11).SetCellValue(deliBatSumMain._acceptTime); } } byte[] buffer = new byte[] { }; using (System.IO.MemoryStream stream = new System.IO.MemoryStream()) { wb.Write(stream); buffer = stream.GetBuffer(); } return File(buffer, "application/vnd.ms-excel", fileDownloadName + ".xls"); }
public static MemoryStream RenderToExcel(List<Bean.ArchiveBean> list) { MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1"); #region head row NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); ICellStyle style = workbook.CreateCellStyle(); IFont font = workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(font); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; //row.RowStyle = style; row.Height = 26 * 20; CreateCell(row, 0, "序号", CellType.String, style); CreateCell(row, 1, "责任人", CellType.String, style); CreateCell(row, 2, "文件题目", CellType.String, style); CreateCell(row, 3, "页数", CellType.String, style); CreateCell(row, 4, "编号", CellType.String, style); CreateCell(row, 5, "备注", CellType.String, style); #endregion if (list != null && list.Count > 0) { int rowidx = 0; foreach (Bean.ArchiveBean model in list) { #region row rowidx++; row = sheet.CreateRow(rowidx); CreateCell(row, 0, model.idx); CreateCell(row, 1, model.manager); CreateCell(row, 2, model.title); CreateCell(row, 3, model.pages); CreateCell(row, 4, model.number); CreateCell(row, 5, model.remark); #endregion } } workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; }
// 送货批单明细报表导出 public ActionResult DeliveryBatDetailExport(int? mainId) { IWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); sheet.SetColumnWidth(0, 16 * 256); sheet.SetColumnWidth(1, 12 * 256); sheet.SetColumnWidth(2, 16 * 256); sheet.SetColumnWidth(3, 30 * 256); sheet.SetColumnWidth(4, 17 * 256); sheet.SetColumnWidth(5, 12 * 256); sheet.SetColumnWidth(6, 12 * 256); sheet.SetColumnWidth(7, 10 * 256); sheet.SetColumnWidth(8, 12 * 256); sheet.SetColumnWidth(9, 10 * 256); sheet.SetColumnWidth(10, 12 * 256); IRow titleRow = sheet.CreateRow(0); titleRow.Height = 20 * 20; titleRow.CreateCell(0).SetCellValue("批送货单号"); titleRow.CreateCell(1).SetCellValue("批条码号"); titleRow.CreateCell(2).SetCellValue("物料编码"); titleRow.CreateCell(3).SetCellValue("物料描述"); titleRow.CreateCell(4).SetCellValue("生成日期"); titleRow.CreateCell(5).SetCellValue("出货子库"); titleRow.CreateCell(6).SetCellValue("收货子库"); titleRow.CreateCell(7).SetCellValue("状态"); titleRow.CreateCell(8).SetCellValue("送货地点"); titleRow.CreateCell(9).SetCellValue("总数量"); titleRow.CreateCell(10).SetCellValue("容器绑定数量"); string fileDownloadName = "送货批单明细报表"; if (mainId.HasValue) { List<AscmDeliBatSumDetail> list = AscmDeliBatSumDetailService.GetInstance().GetListByMainId(mainId.Value); if (list != null) { int rowIndex = 0; foreach (AscmDeliBatSumDetail deliBatSumDetail in list) { IRow row = sheet.CreateRow(++rowIndex); row.Height = 20 * 20; row.CreateCell(0).SetCellValue(deliBatSumDetail.batchDocNumber); row.CreateCell(1).SetCellValue(deliBatSumDetail.batchBarCode); row.CreateCell(2).SetCellValue(deliBatSumDetail.materialDocNumber); row.CreateCell(3).SetCellValue(deliBatSumDetail.materialDescription); row.CreateCell(4).SetCellValue(deliBatSumDetail.batchCreateTime); row.CreateCell(5).SetCellValue(deliBatSumDetail.batchSupperWarehouse); row.CreateCell(6).SetCellValue(deliBatSumDetail.batchWarehouseId); row.CreateCell(7).SetCellValue(deliBatSumDetail.batchStatusCn); row.CreateCell(8).SetCellValue(deliBatSumDetail.batchWipLine); row.CreateCell(9).SetCellValue(deliBatSumDetail.totalNumber.ToString()); row.CreateCell(10).SetCellValue(deliBatSumDetail.containerBindNumber.ToString()); } } } byte[] buffer = new byte[] { }; using (System.IO.MemoryStream stream = new System.IO.MemoryStream()) { wb.Write(stream); buffer = stream.GetBuffer(); } return File(buffer, "application/vnd.ms-excel", fileDownloadName + ".xls"); }