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 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 static void WriteXlsx(string file, string[][] contents) { IWorkbook book = null; if (file.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase)) { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } ISheet sheet = book.CreateSheet("Sheet1"); for (int i = 0; i < contents.Length; i++) { var row = sheet.CreateRow(i); for (int k = 0; k < contents[i].Length; k++) { var cell = row.CreateCell(k, CellType.String); cell.SetCellValue(contents[i][k]); } } using (FileStream fs = new FileStream(file, FileMode.Create)) { book.Write(fs); } }
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 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")); }
public static void WriteXlsx(string file, Dictionary <string, string[][]> sheetDatas) { IWorkbook book = null; if (file.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase)) { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } foreach (var pair in sheetDatas) { ISheet sheet = book.CreateSheet(pair.Key); for (int i = 0; i < pair.Value.Length; i++) { var row = sheet.CreateRow(i); for (int k = 0; k < pair.Value[i].Length; k++) { var cell = row.CreateCell(k, CellType.String); cell.SetCellValue(pair.Value[i][k]); } } using (FileStream fs = new FileStream(file, FileMode.Create)) { book.Write(fs); } } }
private void createWorkSheetPeriodRow(NPOI.HSSF.UserModel.HSSFWorkbook workbook, NPOI.HSSF.UserModel.HSSFSheet workSheet, DateTime periodFromDate, DateTime periodToDate) { // create header styles NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleLeft = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); NPOI.HSSF.UserModel.HSSFFont HeaderFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont(); HeaderFont.Boldweight = 900; HeaderFont.FontHeightInPoints = 16; HeaderStyleLeft.SetFont(HeaderFont); NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); HeaderStyleCenter.CloneStyleFrom(HeaderStyleLeft); HeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; NPOI.HSSF.UserModel.HSSFCellStyle HeaderStyleRight = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle(); HeaderStyleRight.CloneStyleFrom(HeaderStyleLeft); HeaderStyleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; // Create header row NPOI.HSSF.UserModel.HSSFRow HeaderRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0); NPOI.HSSF.UserModel.HSSFCell HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(5); HeaderCell.SetCellValue(periodFromDate.ToString("dd-MMM-yy")); HeaderCell.CellStyle = HeaderStyleRight; HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(6); HeaderCell.SetCellValue("~"); HeaderCell.CellStyle = HeaderStyleCenter; HeaderCell = (NPOI.HSSF.UserModel.HSSFCell)HeaderRow.CreateCell(7); HeaderCell.SetCellValue(periodToDate.ToString("dd-MMM-yy")); HeaderCell.CellStyle = HeaderStyleLeft; }
public ICellStyle Cellstyle() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); IFont font = book.CreateFont(); font.FontName = "宋体"; //font.setFontHeightInPoints((short)10); font.FontHeightInPoints = 10; // 普通单元格样式 ICellStyle style = book.CreateCellStyle(); style.SetFont(font); //style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中 //style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 //style.setWrapText(true); //style.setLeftBorderColor(HSSFColor.BLACK.index); //style.setBorderLeft((short)1); //style.setRightBorderColor(HSSFColor.BLACK.index); //style.setBorderRight((short)1); //style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 //style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色. //style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色. return(style); }
public FileResult Export(string OrderCode, string StoreId, string Tel, string StartDate, string EndDate, string Status) { using (DBContext db = new DBContext()) { var query = db.Order.AsQueryable(); Store store = UserContext.store; StoreId = store == null ? StoreId : store.ID; query = SetQuery(query, OrderCode, StoreId, Tel, StartDate, EndDate, Status); //获取list数据 var list = query.OrderByDescending(q => q.SubmitTime).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("采购单位"); row1.CreateCell(2).SetCellValue("负责人"); row1.CreateCell(3).SetCellValue("联系电话"); row1.CreateCell(4).SetCellValue("下单时间"); row1.CreateCell(5).SetCellValue("订单金额"); row1.CreateCell(6).SetCellValue("订单状态"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { switch (list[i].Status) { case OrderStatus.BeforeSend: status = "待发货"; break; case OrderStatus.BeforeSubmit: status = "待提交"; break; case OrderStatus.Reject: status = "驳回"; break; case OrderStatus.Sended: status = "已发货"; break; } NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].OrderCode); rowtemp.CreateCell(1).SetCellValue(list[i].StoreName); rowtemp.CreateCell(2).SetCellValue(list[i].Creator); rowtemp.CreateCell(3).SetCellValue(list[i].Tel); rowtemp.CreateCell(4).SetCellValue(list[i].SubmitTime.ToString()); rowtemp.CreateCell(5).SetCellValue(list[i].Paid.ToString()); rowtemp.CreateCell(6).SetCellValue(status); } DateTime now = DateTime.Now; // 写入到客户端 return(ExportExcel(book, now.ToString("yyMMddHHmmssfff"))); } }
public FileResult Export(string StoreId, string StartDate, string EndDate, int pi = 1) { using (DBContext db = new DBContext()) { var storeQuery = db.Store.AsQueryable(); if (!string.IsNullOrEmpty(StoreId)) { storeQuery = storeQuery.Where(q => q.ID.Equals(StoreId)); } var orderQuery = db.Order.AsQueryable(); DateTime now = DateTime.Now; //不选择开始日期默认为本月1号 DateTime start = string.IsNullOrEmpty(StartDate) ? DateTime.Parse(string.Format("{0}/{1}/{2}", now.Year.ToString(), now.Month.ToString(), "01")) : DateTime.Parse(StartDate); DateTime end = string.IsNullOrEmpty(EndDate) ? now : DateTime.Parse(EndDate).AddDays(1); if (start > end) { DateTime temp = DateTime.MinValue; temp = end; end = start; start = temp; } orderQuery = orderQuery.Where(q => q.SubmitTime.CompareTo(start) > 0 && q.SubmitTime.CompareTo(end) < 0); var list = (from q in storeQuery join o in orderQuery on q.ID equals o.StoreId into o_join from os in o_join.DefaultIfEmpty() group new { q.StoreName, os.Paid } by new { q.StoreName } into s select new PSJE() { StoreName = s.Key.StoreName, Pay = s.Sum(p => p.Paid == null ? 0 : p.Paid) }).OrderByDescending(q => q.Pay).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("销售额"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].StoreName); rowtemp.CreateCell(1).SetCellValue(list[i].Pay.ToString()); } // 写入到客户端 return(ExportExcel(book, now.ToString("yyMMddHHmmssfff"))); } }
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)); }
/// <summary> /// 模板导入到NPOI Workbook中 /// </summary> /// <param name="dataSourceFilePath">数据源路经</param> /// <returns></returns> private NPOI.HSSF.UserModel.HSSFWorkbook InitializeWorkbook(string dataSourceFilePath) { try { NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = null; System.IO.FileStream file = new System.IO.FileStream(dataSourceFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read); if (null == file) { return(hssfworkbook); } hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(file); if (null == hssfworkbook) { return(hssfworkbook); } //create a entry of DocumentSummaryInformation NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "test"; hssfworkbook.DocumentSummaryInformation = dsi; //create a entry of SummaryInformation NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation(); si.Subject = "test"; hssfworkbook.SummaryInformation = si; return(hssfworkbook); } catch (Exception ex) { return(null); throw new Exception(ex.ToString()); } }
/* * 作用:将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); }
internal static NPOI.SS.UserModel.IWorkbook CreateWorkbookFromDictionary(string sheetName, Dictionary <string, string> values) { if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException("sheetName"); } if (values == null) { throw new ArgumentNullException("values"); } NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); var worksheet = workbook.CreateSheet(sheetName); int column = 0; int row = 0; foreach (var pair in values) { var currentRow = worksheet.CreateRow(row); var leftCell = currentRow.CreateCell(column); leftCell.SetCellValue(pair.Key); var rightCell = currentRow.CreateCell(column + 1); rightCell.SetCellValue(pair.Value); row++; } return(workbook); }
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 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 匯出客戶銀行資訊() { //建立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")); }
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 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 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(); }
/// <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)); }
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 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 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(); }
/// <summary> /// IQC 导出Excel 数据流 /// </summary> /// <param name="dataSource">数据源</param> /// <returns></returns> public System.IO.MemoryStream ExportPrintToExcel(List <SampleItemsIqcRecordModel> dataSource) { try { //数据为Null时返回数值 System.IO.MemoryStream stream = new System.IO.MemoryStream(); if (dataSource == null || dataSource.Count == 0) { return(stream); } string filePath = System.IO.Path.GetFullPath(PrintSampleModel(dataSource)); NPOI.HSSF.UserModel.HSSFWorkbook workbook = InitializeWorkbook(filePath); if (workbook == null) { return(null); } NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); sheet.ForceFormulaRecalculation = true; //保存 Store(dataSource); return(stream); } catch (Exception ex) { throw new Exception(ex.ToString()); } }
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> /// 通过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; } } }
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 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 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 Export(string OrderCode, string StoreId, string Tel, string StartDate, string EndDate, string Status) { using (DBContext db = new DBContext()) { var query = db.Order.AsQueryable(); Store store = UserContext.store; StoreId = store == null ? StoreId : store.ID; query = SetQuery(query, OrderCode, StoreId, Tel, StartDate, EndDate, Status); //获取list数据 var list = query.OrderByDescending(q => q.SubmitTime).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("采购单位"); row1.CreateCell(2).SetCellValue("负责人"); row1.CreateCell(3).SetCellValue("联系电话"); row1.CreateCell(4).SetCellValue("下单时间"); row1.CreateCell(5).SetCellValue("订单金额"); row1.CreateCell(6).SetCellValue("订单状态"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { switch (list[i].Status) { case OrderStatus.BeforeSend: status = "待发货"; break; case OrderStatus.BeforeSubmit: status = "待提交"; break; case OrderStatus.Reject: status = "驳回"; break; case OrderStatus.Sended: status = "已发货"; break; } NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].OrderCode); rowtemp.CreateCell(1).SetCellValue(list[i].StoreName); rowtemp.CreateCell(2).SetCellValue(list[i].Creator); rowtemp.CreateCell(3).SetCellValue(list[i].Tel); rowtemp.CreateCell(4).SetCellValue(list[i].SubmitTime.ToString()); rowtemp.CreateCell(5).SetCellValue(list[i].Paid.ToString()); rowtemp.CreateCell(6).SetCellValue(status); } DateTime now = DateTime.Now; // 写入到客户端 return ExportExcel(book, now.ToString("yyMMddHHmmssfff")); } }
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 FileResult ExportDetail(string storeId, string StartDate, string EndDate, string storeType) { using (DBContext db = new DBContext()) { var orderQuery = db.Order.AsQueryable(); orderQuery = SetQuery(orderQuery, storeId, StartDate, EndDate); var storeQuery = db.Store.AsQueryable(); if (!string.IsNullOrEmpty(storeType)) { StoreType type = (StoreType)Convert.ToInt16(storeType); storeQuery = storeQuery.Where(q => q.StoreType == type); } var list = (from q in db.OrderItem join o in orderQuery on q.OrderId equals o.ID join t in storeQuery on o.StoreId equals t.ID group q by new { o.StoreId, o.StoreName, q.ProductName, q.ProductCode } into s orderby new { s.Key.StoreName, s.Key.ProductName } select new PSMX() { StoreName = s.Key.StoreName, ProductName = s.Key.ProductName, ProductCode = s.Key.ProductCode, ProductNumber = s.Sum(p => p.RealNumber) }).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("商品名称"); row1.CreateCell(2).SetCellValue("商品编号"); row1.CreateCell(3).SetCellValue("采购总数"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].StoreName); rowtemp.CreateCell(1).SetCellValue(list[i].ProductName); rowtemp.CreateCell(2).SetCellValue(list[i].ProductCode); rowtemp.CreateCell(3).SetCellValue(list[i].ProductNumber); } DateTime now = DateTime.Now; // 写入到客户端 return ExportExcel(book, now.ToString("yyMMddHHmmssfff")); } }
public FileResult ExportNumber(string ProductName, string StartDate, string EndDate) { using (DBContext db = new DBContext()) { var itemQuery = db.OrderItem.AsQueryable(); var orderQuery = db.Order.AsQueryable(); orderQuery = GetOrderQuery(orderQuery, StartDate, EndDate); if (!string.IsNullOrEmpty(ProductName)) { itemQuery = itemQuery.Where(q => q.ProductName.Equals(ProductName)); } var list = (from q in itemQuery join o in orderQuery on q.OrderId equals o.ID group q by new { q.ProductName, q.ProductCode } into s select new CXSL() { ProductName = s.Key.ProductName, ProductCode = s.Key.ProductCode, ProductNumber = s.Sum(p => p.RealNumber) }).OrderByDescending(q => q.ProductNumber).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("商品编号"); row1.CreateCell(2).SetCellValue("数量"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].ProductName); rowtemp.CreateCell(1).SetCellValue(list[i].ProductCode); rowtemp.CreateCell(2).SetCellValue(list[i].ProductNumber); } DateTime now = DateTime.Now; // 写入到客户端 return ExportExcel(book, now.ToString("yyMMddHHmmssfff")); } }
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 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成功"; } }
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 } }
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 } }
public FileResult Export(string StoreId, string StartDate, string EndDate, int pi = 1) { using (DBContext db = new DBContext()) { var storeQuery = db.Store.AsQueryable(); if (!string.IsNullOrEmpty(StoreId)) { storeQuery = storeQuery.Where(q => q.ID.Equals(StoreId)); } var orderQuery = db.Order.AsQueryable(); DateTime now = DateTime.Now; //不选择开始日期默认为本月1号 DateTime start = string.IsNullOrEmpty(StartDate) ? DateTime.Parse(string.Format("{0}/{1}/{2}", now.Year.ToString(), now.Month.ToString(), "01")) : DateTime.Parse(StartDate); DateTime end = string.IsNullOrEmpty(EndDate) ? now : DateTime.Parse(EndDate).AddDays(1); if (start > end) { DateTime temp = DateTime.MinValue; temp = end; end = start; start = temp; } orderQuery = orderQuery.Where(q => q.SubmitTime.CompareTo(start) > 0 && q.SubmitTime.CompareTo(end) < 0); var list = (from q in storeQuery join o in orderQuery on q.ID equals o.StoreId into o_join from os in o_join.DefaultIfEmpty() group new { q.StoreName, os.Paid } by new { q.StoreName } into s select new PSJE() { StoreName = s.Key.StoreName, Pay = s.Sum(p => p.Paid == null ? 0 : p.Paid) }).OrderByDescending(q => q.Pay).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("销售额"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].StoreName); rowtemp.CreateCell(1).SetCellValue(list[i].Pay.ToString()); } // 写入到客户端 return ExportExcel(book, now.ToString("yyMMddHHmmssfff")); } }
public FileResult Export(string Province, string City, string Country, string StoreId, string StartDate, string EndDate, int pi = 1) { using (DBContext db = new DBContext()) { var storeQuery = db.Store.AsQueryable(); if (!string.IsNullOrEmpty(Province)) { storeQuery = storeQuery.Where(q => q.Province.Equals(Province)); } if (!string.IsNullOrEmpty(City)) { storeQuery = storeQuery.Where(q => q.City.Equals(City)); } if (!string.IsNullOrEmpty(Country)) { storeQuery = storeQuery.Where(q => q.Country.Equals(Country)); } if (!string.IsNullOrEmpty(StoreId)) { storeQuery = storeQuery.Where(q => q.ID.Equals(StoreId)); } var offQuery = db.OfflineSell.AsQueryable(); var olQuery = db.AppOrder.AsQueryable(); DateTime now = DateTime.Now; //不选择开始日期默认为本月1号 DateTime start = string.IsNullOrEmpty(StartDate) ? DateTime.Parse(string.Format("{0}/{1}/{2}", now.Year.ToString(), now.Month.ToString(), "01")) : DateTime.Parse(StartDate); DateTime end = string.IsNullOrEmpty(EndDate) ? now : DateTime.Parse(EndDate).AddDays(1); if (start > end) { DateTime temp = DateTime.MinValue; temp = end; end = start; start = temp; } offQuery = offQuery.Where(q => q.XFRQ.CompareTo(start) > 0 && q.XFRQ.CompareTo(end) < 0 && q.bFinish == Finish.YJS); olQuery = olQuery.Where(q => q.CreateTime.CompareTo(start) > 0 && q.CreateTime.CompareTo(end) < 0 && q.Status == 5); var list = (from q in storeQuery join off in offQuery on q.ID equals off.StationID into off_join from o in off_join.DefaultIfEmpty() join ol in olQuery on q.ID equals ol.StoreId into ol_join from l in ol_join.DefaultIfEmpty() group new { q.StoreName, o.JE, l.Payable } by new { q.StoreName } into s select new PSJE() { StoreName = s.Key.StoreName, Pay = s.Sum(p => (p.JE == null ? 0 : p.JE) + (p.Payable == null ? 0 : p.Payable)) }).OrderByDescending(q => q.Pay).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("营业额"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].StoreName); rowtemp.CreateCell(1).SetCellValue(list[i].Pay.ToString()); } // 写入到客户端 return ExportExcel(book, now.ToString("yyMMddHHmmssfff")); } }
public FileResult ExportStore(string storeId, string StartDate, string EndDate, string storeType) { using (DBContext db = new DBContext()) { var orderQuery = db.Order.AsQueryable(); orderQuery = SetQuery(orderQuery, storeId, StartDate, EndDate); var storeQuery = db.Store.AsQueryable(); if (!string.IsNullOrEmpty(storeType)) { StoreType type = (StoreType)Convert.ToInt16(storeType); storeQuery = storeQuery.Where(q => q.StoreType == type); } var list = (from q in orderQuery join t in storeQuery on q.StoreId equals t.ID group q by new { q.StoreId, q.StoreName } into s select new PSJE() { StoreName = s.Key.StoreName, Pay = s.Sum(p => p.Paid) }).OrderByDescending(q => q.Pay).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("金额"); string status = string.Empty; //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].StoreName); rowtemp.CreateCell(1).SetCellValue(list[i].Pay.ToString()); } DateTime now = DateTime.Now; // 写入到客户端 return ExportExcel(book, now.ToString("yyMMddHHmmssfff")); } }
// 送货合单查询报表导出 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 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 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 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 string readCellExcel(string filePath, string isheetname, int irow, int icolumn) { string result = ""; try { using (StreamReader input = new StreamReader(filePath)) { NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream)); if (null == workbook) { result = ""; } NPOI.SS.UserModel.IFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook); NPOI.SS.UserModel.DataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN")); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname); NPOI.SS.UserModel.IRow row = sheet.GetRow(irow); result = dataFormatter.FormatCellValue(row.Cells[icolumn], formulaEvaluator); /*foreach (NPOI.SS.UserModel.ISheet sheet in workbook) { foreach (NPOI.SS.UserModel.IRow row in sheet) { foreach (NPOI.SS.UserModel.ICell cell in row) { string value = dataFormatter.FormatCellValue(cell, formulaEvaluator); } } }*/ } } catch { } return result; }
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; }
/// <summary> /// Excel转table 导入 Michaux 20160531 /// </summary> /// <param name="file"></param> /// <returns></returns> public DataTable ImportExcelToDataTable(HttpPostedFileBase file,Dictionary<string,ExcelFormatter> formatColumn=null) { var datatable = new DataTable(); if (file.FileName.IndexOf("xlsx") > -1) { NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { //if (formatColumn!=null && formatColumn.ContainsKey(firstRow.GetCell(j).StringCellValue)) //{ //} var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } else { NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream); var sheet = Upfile.GetSheetAt(0); var firstRow = sheet.GetRow(0); var buffer = new byte[file.ContentLength]; for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++) { datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string)); } for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow datarow = datatable.NewRow(); var row = sheet.GetRow(i); if (row == null) { continue; } bool con = true; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { var cell = row.GetCell(j); if (cell == null) { datarow[j] = ""; continue; } switch (cell.CellType) { case CellType.Numeric: datarow[j] = cell.NumericCellValue; break; case CellType.String: datarow[j] = cell.StringCellValue; break; case CellType.Blank: datarow[j] = ""; break; case CellType.Formula: switch (row.GetCell(j).CachedFormulaResultType) { case CellType.String: string strFORMULA = row.GetCell(j).StringCellValue; if (strFORMULA != null && strFORMULA.Length > 0) { datarow[j] = strFORMULA.ToString(); } else { datarow[j] = null; } break; case CellType.Numeric: datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: datarow[j] = ""; break; } break; default: con = false; break; } if (!con) { break; } } if (con) { datatable.Rows.Add(datarow); } } } #region 清除最后的空行 for (int i = datatable.Rows.Count - 1; i > 0; i--) { bool isnull = true; for (int j = 0; j < datatable.Columns.Count; j++) { if (datatable.Rows[i][j] != null) { if (datatable.Rows[i][j].ToString() != "") { isnull = false; break; } } } if (isnull) { datatable.Rows[i].Delete(); } } #endregion return datatable; }
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 string readCellExcel(string filePath, string isheetname, int irow, int icolumn) { string result = ""; try { using (StreamReader input = new StreamReader(filePath)) { NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream)); if (null == workbook) { result = ""; } NPOI.HSSF.UserModel.HSSFFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook); NPOI.HSSF.UserModel.HSSFDataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN")); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname); NPOI.SS.UserModel.IRow row = sheet.GetRow(irow); if (row != null) { short minColIndex = row.FirstCellNum; short maxColIndex = row.LastCellNum; if (icolumn >= minColIndex || icolumn <= maxColIndex) { NPOI.SS.UserModel.ICell cell = row.GetCell(icolumn); if (cell != null) { result = dataFormatter.FormatCellValue(cell, formulaEvaluator); } } } } } catch (Exception ex) { string test = ex.Message; } return result; }
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"); }
// 送货批单明细报表导出 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"); }
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 bool OpenExcel() { bool isSuccess = false; try { FileStream fileStream = new FileStream(excelpath, FileMode.Open, FileAccess.ReadWrite); excelWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream); isSuccess = true; } catch (Exception) { } return isSuccess; }
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"); }