//得到excel文件流 private System.IO.Stream ExcelStream(string machineIds) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); MemoryStream file = new MemoryStream(); foreach (string machineId in machineIds.Split('^')) { int nowRow = 0; IFullfilBill ifullFill = new TunnelInfoService(); DataTable dtProduct = ifullFill.ExportByProduct(machineId); DataTable dtTunnel = ifullFill.ExportByTunnel(machineId); ISheet sheet1 = hssfworkbook.CreateSheet(machineId); IRow rowHeader = sheet1.CreateRow(nowRow); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 0, 3)); //生成excel标题 rowHeader.CreateCell(0).SetCellValue("补货单 导出时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //第二行 nowRow += 1; ICommon icommon = new CommonService(); List <CommonDic> lstCommon = icommon.GetMachineNameById(machineId); sheet1.CreateRow(nowRow).CreateCell(0).SetCellValue("机器编号:" + lstCommon[0].Id + lstCommon[0].Name); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 0, 3)); nowRow = nowRow + 1; IRow titleRow = sheet1.CreateRow(nowRow); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 0, 1)); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 2, 3)); titleRow.CreateCell(0).SetCellValue("商品名称"); titleRow.CreateCell(2).SetCellValue("缺货数"); for (int i = 0; i < dtProduct.Rows.Count; i++) { nowRow = nowRow + 1; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(nowRow); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 0, 1)); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 2, 3)); rowtemp.CreateCell(0).SetCellValue(dtProduct.Rows[i]["wares_name"].ToString()); rowtemp.CreateCell(2).SetCellValue(dtProduct.Rows[i]["currmissing"].ToString()); sheet1.AutoSizeColumn(i); } nowRow = nowRow + 1; sheet1.CreateRow(nowRow); sheet1.AddMergedRegion(new CellRangeAddress(nowRow, nowRow, 0, 3)); nowRow = nowRow + 1; NPOI.SS.UserModel.IRow secondRowTitle = sheet1.CreateRow(nowRow); secondRowTitle.CreateCell(0).SetCellValue("货道号"); secondRowTitle.CreateCell(1).SetCellValue("商品名称"); secondRowTitle.CreateCell(2).SetCellValue("满货容量"); secondRowTitle.CreateCell(3).SetCellValue("缺货数"); for (int i = 0; i < dtTunnel.Rows.Count; i++) { nowRow = nowRow + 1; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(nowRow); rowtemp.CreateCell(0).SetCellValue(dtTunnel.Rows[i]["tunnel_id"].ToString()); rowtemp.CreateCell(1).SetCellValue(dtTunnel.Rows[i]["wares_name"].ToString()); rowtemp.CreateCell(2).SetCellValue(dtTunnel.Rows[i]["max_puts"].ToString()); rowtemp.CreateCell(3).SetCellValue(dtTunnel.Rows[i]["curr_missing"].ToString()); sheet1.AutoSizeColumn(i); } hssfworkbook.Write(file); file.Seek(0, SeekOrigin.Begin); } //var list = dc.v_bs_dj_bbcdd1.Where(eps).ToList(); //生成excel内容 //for (int i = 0; i < list.Count; i++) //{ // NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); // rowtemp.CreateCell(0).SetCellValue(list[i].bh_user); // rowtemp.CreateCell(1).SetCellValue(list[i].rq.Value.ToString("yyyy-MM-dd HH:mm:dd")); // rowtemp.CreateCell(2).SetCellValue(list[i].bh_khdd); // rowtemp.CreateCell(3).SetCellValue(list[i].re_name); // rowtemp.CreateCell(4).SetCellValue(list[i].re_tel); // rowtemp.CreateCell(5).SetCellValue(list[i].re_fulladdress); // rowtemp.CreateCell(6).SetCellValue(list[i].bm_kdgs); // rowtemp.CreateCell(7).SetCellValue(list[i].kddh); // rowtemp.CreateCell(8).SetCellValue((int)list[i].sl_total); // rowtemp.CreateCell(9).SetCellValue(list[i].mc_state_dd); //} return(file); //return File(file, "application/vnd.ms-excel", "保税订单.xls"); }