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 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")); }
/// <summary> /// 获取不动产的办件步骤信息 /// </summary> /// <returns></returns> public void GetBdcStepInfo(string fileName) { BDCInfo4WWWDal dal = new BDCInfo4WWWDal(); DataTable dt = dal.GetBDCStepInfo(); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("不动产办理步骤信息"); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("业务编号"); row.CreateCell(1).SetCellValue("查询密码"); row.CreateCell(2).SetCellValue("通知人"); row.CreateCell(3).SetCellValue("流程步骤"); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(i + 1); row1.CreateCell(0).SetCellValue(dt.Rows[i]["SLBH"].ToString()); row1.CreateCell(1).SetCellValue(dt.Rows[i]["CXMM"].ToString()); row1.CreateCell(2).SetCellValue(dt.Rows[i]["TZRXM"].ToString()); row1.CreateCell(3).SetCellValue(dt.Rows[i]["StepName"].ToString()); } } using (FileStream stm = File.OpenWrite(fileName)) { book.Write(stm); } }
public static void FullSheet(NPOI.SS.UserModel.ISheet iSheet, System.Data.DataTable dataTable, bool firstRowIsColumnHead) { if (iSheet == null) { throw new AggregateException("参数NPOI.SS.UserModel.ISheet对象为null。"); } NPOI.SS.UserModel.IRow iRow = iSheet.CreateRow(0); for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++) { NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex); iCell.SetCellValue(dataTable.Columns[dataTableColumnIndex].ColumnName); } int startRowIndex = 0; if (firstRowIsColumnHead) { startRowIndex = 1; } for (int dataTableRowIndex = 0; dataTableRowIndex < dataTable.Rows.Count; dataTableRowIndex++) { iRow = iSheet.CreateRow(dataTableRowIndex + startRowIndex); for (int dataTableColumnIndex = 0; dataTableColumnIndex < dataTable.Columns.Count; dataTableColumnIndex++) { NPOI.SS.UserModel.ICell iCell = iRow.CreateCell(dataTableColumnIndex); SetCellValue(iCell, dataTable.Rows[dataTableRowIndex][dataTableColumnIndex]); } } }
/// <summary> /// 把数据写入到工作表 /// </summary> /// <param name="workbook"></param> /// <param name="lst"></param> /// <returns></returns> public void InsertByColName(NPOI.SS.UserModel.ISheet sheet, IEnumerable <T> lst, int startrow = 0) { if (sheet == null) { throw new ArgumentException("sheet不能为Null"); } if (lst == null) { throw new ArgumentException("value不能为Null"); } if (dictMapHandlerByColName == null) { throw new ArgumentException("必须指定列名称和model之间的映射"); } int colIndex = 0; int rowIndex = startrow; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex++); foreach (var kv in dictMapHandlerByColName) { row.CreateCell(colIndex++).SetCellValue(kv.Key); } foreach (var value in lst) { row = sheet.CreateRow(rowIndex++); colIndex = 0; foreach (var kv in dictMapHandlerByColName) { //dictMapHandler[kv.Value].SetValueToCell(value, row.CreateCell(kv.Key)); kv.Value(row.CreateCell(colIndex++), value); } } }
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(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)); }
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt) { NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.Caption); index++; } // 添加数据 for (int i = 0; i < dt.Rows.Count; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dt.Rows[i][item].ToString()); index++; } } }
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"))); } }
/// <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 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)); }
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 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)); }
private void btnOrderReport2_Click(object sender, EventArgs e) { if (dGV_reportAll.Rows.Count == 0) { MessageBox.Show("当前无数据导出!请先刷新今日订货信息,确认当前有无当日订货信息。"); } else { string Date = DateTime.Now.ToString("yyyy-MM-dd"); SaveFileDialog s1 = new SaveFileDialog(); s1.Title = "请选择要导出的位置"; s1.Filter = "Excel文件|*.xls"; s1.FileName = dTP_oStartTime.Value.ToShortDateString().Replace('/', '-') + "至" + dTP_oEndTime.Value.ToShortDateString().Replace('/', '-') + "历史订货信息"; if (s1.ShowDialog() == DialogResult.OK) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(Date); 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("订货时间"); for (int i = 0; i < dGV_reportAll.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1); rowTemp.CreateCell(0).SetCellValue(i + 1); rowTemp.CreateCell(1).SetCellValue(dGV_reportAll.Rows[i].Cells[0].Value.ToString()); rowTemp.CreateCell(2).SetCellValue(dGV_reportAll.Rows[i].Cells[1].Value.ToString()); rowTemp.CreateCell(3).SetCellValue(dGV_reportAll.Rows[i].Cells[2].Value.ToString()); rowTemp.CreateCell(4).SetCellValue(dGV_reportAll.Rows[i].Cells[3].Value.ToString()); } FileStream orderRepo = File.OpenWrite(s1.FileName.ToString()); try { book.Write(orderRepo); orderRepo.Seek(0, SeekOrigin.Begin); MessageBox.Show("导出成功!", "", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception) { MessageBox.Show("导出失败!", "", MessageBoxButtons.OK, MessageBoxIcon.Error); throw; } finally { if (orderRepo != null) { orderRepo.Close(); } } } } }
public void DataExportToExecl() { OrganizeInfoDal dal = new OrganizeInfoDal(); DbSession dbSession = new DbSession(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet var _data = dal.GetEntities(u => true).ToList(); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("排名"); row1.CreateCell(1).SetCellValue("CardID"); row1.CreateCell(2).SetCellValue("姓名"); row1.CreateCell(3).SetCellValue("手机"); row1.CreateCell(4).SetCellValue("职位"); row1.CreateCell(5).SetCellValue("所在公司"); row1.CreateCell(6).SetCellValue("创建时间"); //将数据逐步写入sheet1各个行 for (int i = 0; i < _data.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(i + 1); rowtemp.CreateCell(1).SetCellValue(_data[i].OrganizeInfoShowName); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); var buf = ms.ToArray(); using (FileStream fs = new FileStream("test.xls", FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } }
public FileResult Excel2() { MyElectrCheck_DBDataContext mdc = new MyElectrCheck_DBDataContext(); List <DataEliminate> list = new List <DataEliminate>(); if (Session["solvelist"] != null) { //将session转为集合 list = (List <DataEliminate>)Session["solvelist"]; } 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("缺陷级别"); row1.CreateCell(6).SetCellValue("缺陷类型"); row1.CreateCell(7).SetCellValue("消缺时间"); row1.CreateCell(8).SetCellValue("发现时间"); row1.CreateCell(9).SetCellValue("缺陷描述"); for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].solveTaskCode); rowtemp.CreateCell(1).SetCellValue(list[i].solveTaskName); rowtemp.CreateCell(2).SetCellValue(list[i].lineCode); if (list[i].isBug == 1) { rowtemp.CreateCell(3).SetCellValue("有"); } else { rowtemp.CreateCell(3).SetCellValue("无"); } rowtemp.CreateCell(4).SetCellValue(list[i].bugLevelName); rowtemp.CreateCell(5).SetCellValue(list[i].bugLevelName); rowtemp.CreateCell(6).SetCellValue(list[i].bugTypeName); rowtemp.CreateCell(7).SetCellValue(list[i].finishTime.ToString()); rowtemp.CreateCell(8).SetCellValue(list[i].discoverTime.ToString()); rowtemp.CreateCell(9).SetCellValue(list[i].bugDesc); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); Book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyMMddHHmmssff"); string fileName = "查询结果" + dateTime + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public string ExportCusConfig() { JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name); string sql = QueryCondition_customsconfig(); sql += " order by a.entrusttypecode,a.busiitemcode asc"; IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式 iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; DataTable dt_count = DBMgr.GetDataTable("select count(1) from (" + sql + ") a"); int WebDownCount = Convert.ToInt32(ConfigurationManager.AppSettings["WebDownCount"]); if (Convert.ToInt32(dt_count.Rows[0][0]) > WebDownCount) { return("{success:false,WebDownCount:" + WebDownCount + "}"); } DataTable dt = DBMgr.GetDataTable(sql); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个导出成功sheet NPOI.SS.UserModel.ISheet sheet_S; string filename = "业务细项.xls"; sheet_S = book.CreateSheet("业务细项"); NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0); row1.CreateCell(0).SetCellValue("业务类别编号"); row1.CreateCell(1).SetCellValue("业务类别名称"); row1.CreateCell(2).SetCellValue("业务细项编号"); row1.CreateCell(3).SetCellValue("业务细项名称"); row1.CreateCell(4).SetCellValue("创建时间"); row1.CreateCell(5).SetCellValue("是否启用"); row1.CreateCell(6).SetCellValue("创建人"); row1.CreateCell(7).SetCellValue("启禁用人"); for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["ENTRUSTTYPECODE"].ToString()); rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["ENTRUSTTYPENAME"].ToString()); rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["BUSIITEMCODE"].ToString()); rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["BUSIITEMNAME"].ToString()); rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["STARTTIME"].ToString()); rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["ENABLE"].ToString() == "1" ? "是" : "否"); rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["CREATEUSERNAME"].ToString()); rowtemp.CreateCell(7).SetCellValue(dt.Rows[i]["ENABLEUSERNAME"].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); return(Extension.getPathname(filename, book)); }
/// <summary> /// 导出excel /// </summary> /// <param name="response"></param> /// <param name="dt"></param> /// <param name="fileName"></param> /// <param name="sheetname"></param> public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.IFont font = book.CreateFont(); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "微软雅黑"; style.SetFont(font); for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.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++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } } } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls")); response.ContentType = "application/vnd.ms-excel"; //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
private static int createCell(NPOI.SS.UserModel.IRow row, int col, float?nullableFloat) { if (nullableFloat.HasValue) { row.CreateCell(col++).SetCellValue(nullableFloat.Value); } else { row.CreateCell(col++).SetCellValue(""); } return(col); }
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 string ExportFileRecoginze() { JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name); string sql = QueryConditionFileRecoginze(); sql = sql + " order by lf.TIMES desc"; IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式 iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; DataTable dt_count = DBMgr.GetDataTable("select count(1) from (" + sql + ") a"); int WebDownCount = Convert.ToInt32(ConfigurationManager.AppSettings["WebDownCount"]); if (Convert.ToInt32(dt_count.Rows[0][0]) > WebDownCount) { return("{success:false,WebDownCount:" + WebDownCount + "}"); } DataTable dt = DBMgr.GetDataTable(sql); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个导出成功sheet NPOI.SS.UserModel.ISheet sheet_S; string filename = "文件关联.xls"; sheet_S = book.CreateSheet("文件关联"); NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0); row1.CreateCell(0).SetCellValue("关联状态"); row1.CreateCell(1).SetCellValue("订单编号"); row1.CreateCell(2).SetCellValue("客户编号"); row1.CreateCell(3).SetCellValue("创建时间"); row1.CreateCell(4).SetCellValue("创建人"); for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["STATUS"].ToString()); rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["ORDERCODE"].ToString()); rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["CUSNO"].ToString()); rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["TIMES"].ToString()); rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["USERNAME"].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); return(Extension.getPathname(filename, book)); }
private void exportData() { string sql = @"SELECT * FROM cusdoc.sys_customer order by id desc"; DataTable dt = DBMgr.GetDataTable(sql); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个导出成功sheet NPOI.SS.UserModel.ISheet sheet_S = book.CreateSheet("客商信息"); NPOI.SS.UserModel.IRow row1 = sheet_S.CreateRow(0); row1.CreateCell(0).SetCellValue("代码"); row1.CreateCell(1).SetCellValue("海关代码"); row1.CreateCell(2).SetCellValue("国检代码"); row1.CreateCell(3).SetCellValue("中文名称"); row1.CreateCell(4).SetCellValue("中文简称"); row1.CreateCell(5).SetCellValue("中文地址"); row1.CreateCell(6).SetCellValue("英文名称"); row1.CreateCell(7).SetCellValue("英文地址"); row1.CreateCell(8).SetCellValue("是否启用"); row1.CreateCell(9).SetCellValue("备注"); for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet_S.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["CODE"].ToString()); rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["HSCODE"].ToString()); rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["CIQCODE"].ToString()); rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["NAME"].ToString()); rowtemp.CreateCell(4).SetCellValue(dt.Rows[i]["CHINESEABBREVIATION"].ToString()); rowtemp.CreateCell(5).SetCellValue(dt.Rows[i]["CHINESEADDRESS"].ToString()); rowtemp.CreateCell(6).SetCellValue(dt.Rows[i]["ENGLISHNAME"].ToString()); rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["ENGLISHADDRESS"].ToString()); rowtemp.CreateCell(8).SetCellValue(dt.Rows[i]["ENABLED"].ToString() == "1" ? "是" : "否"); rowtemp.CreateCell(9).SetCellValue(dt.Rows[i]["REMARK"].ToString()); } try { // 输出Excel string filename = "客商信息.xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(filename))); Response.Clear(); MemoryStream ms = new MemoryStream(); book.Write(ms); Response.BinaryWrite(ms.GetBuffer()); Response.End(); } catch (Exception e) { Console.WriteLine(e.Message); } }
public static MemoryStream ExportAuditLog(int zoneTableId, DateTime?startTime, DateTime?endTime, string logType, string detail) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 //List<AuditLogEntity> list = new List<AuditLogEntity>(); //for (int i = 0; i < 50; i++) //{ // AuditLogEntity en = new AuditLogEntity(); // en.ID = i; // en.LogType = "App"; // en.Detail = "detail" + i; // en.LogTime = DateTime.Now; // en.LogOperator = "Michael.he"; // list.Add(en); //} List <AuditLogEntity> list = AuditLogAccess.GetList(zoneTableId, startTime, endTime, logType, detail); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("Log Type"); row1.CreateCell(1).SetCellValue("Detail"); row1.CreateCell(2).SetCellValue("Log Time"); row1.CreateCell(3).SetCellValue("Log Operator"); var notesStyle = book.CreateCellStyle(); notesStyle.WrapText = true;//设置换行这个要先设置 //将数据逐步写入sheet1各个行 for (int i = 0; i < list.Count; i++) { var rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(list[i].LogType.ToString()); rowtemp.CreateCell(1).SetCellValue(list[i].Detail.Replace("<br />", "\n")); rowtemp.Cells[1].CellStyle = notesStyle; rowtemp.CreateCell(2).SetCellValue(list[i].LogTime.ToString(CultureInfo.InvariantCulture)); rowtemp.CreateCell(3).SetCellValue(list[i].LogOperator); } sheet1.SetColumnWidth(1, 160 * 256); sheet1.SetColumnWidth(2, 20 * 256); // 写入到客户端 var ms = new MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(ms); }
public static void export(List <Comment> comments) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("users"); // 第一列 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); string[] headers = new string[] { "昵称", "邮箱", "评价星级", "排名", "个人主页" }; for (int i = 0; i < headers.Length; i++) { row.CreateCell(i).SetCellValue(headers[i]); } for (int i = 0; i < comments.Count; i++) { Comment c = comments[i]; // 第二列 NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); row2.CreateCell(0).SetCellValue(c.NickName); row2.CreateCell(1).SetCellValue(c.Email); row2.CreateCell(2).SetCellValue(c.Star); row2.CreateCell(3).SetCellValue(c.Rank); row2.CreateCell(4).SetCellValue(c.Profile); } SaveFileDialog dialog = new SaveFileDialog(); dialog.Filter = "Excel文件(*.xls)|*.xls"; if (dialog.ShowDialog() == DialogResult.OK) { string fileName = dialog.FileName; try { FileStream stream = new FileStream(fileName, FileMode.Create); using (stream) { book.Write(stream); } } catch (Exception e) { MessageBox.Show("保存文件失败!"); } } }
/// <summary> /// 设置样式 /// </summary> private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style) { NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.ICell newCell = null; for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.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++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } else if (strColDataType.Equals("System.DateTime")) { DateTime dateV; DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV); newCell = row2.CreateCell(j); newCell.SetCellValue(dateV); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-m-d"); newCell.CellStyle = cellStyle; } } } }
public FileResult Export(int 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("角色"); bool enable = id == 1 ? true : false; List <VModel.SyUserManager.Grid> list = Bll.SyUserBll.ExportManager(enable); int i = 1; foreach (var m in list) { NPOI.SS.UserModel.IRow r = sheet1.CreateRow(i); r.CreateCell(0).SetCellValue(m.Account); r.CreateCell(1).SetCellValue(m.Name); r.CreateCell(2).SetCellValue(Common.Function.ConvertDate(m.ExpiresTime)); r.CreateCell(3).SetCellValue(Common.Dict.UserState.GetVal(m.IsEnabled)); 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(4).SetCellValue(roleName); } else { r.CreateCell(4).SetCellValue(""); } i++; } System.IO.MemoryStream ms = new System.IO.MemoryStream();// 写入到客户端 book.Write(ms); ms.Seek(0, System.IO.SeekOrigin.Begin); string fileName = enable == true ? "当前管理员用户.xls" : "历史管理员用户.xls"; return(File(ms, "application/vnd.ms-excel", UrlEcode.ToHexString(fileName))); }
/// <summary> /// NPOI方式导出excel /// 直接导出 /// </summary> /// <returns></returns> public FileResult ExportStu2() { string schoolname = "401"; //创建Excel文件的对象 HSSFWorkbook book = new HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //假数据(真实数据需要从数据库中获取) List <Compture> listRainInfo = new List <Compture>() { new Compture() { PCName = "pc1", UserName = "******" }, new Compture() { PCName = "pc2", UserName = "******" }, new Compture() { PCName = "pc3", UserName = "******" }, new Compture() { PCName = "pc4", UserName = "******" }, }; //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("电脑号"); row1.CreateCell(1).SetCellValue("姓名"); //将数据逐步写入sheet1各个行 for (int i = 0; i < listRainInfo.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].PCName.ToString()); rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].UserName.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 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 Stream ExportToExecl() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet var orgData = CurrentDal.GetEntities(u => u.OrganizeInfoManageId != null).AsQueryable(); //获取list数据,也可以分页获取数据,以获得更高效的性能 var _data = orgData.ToList(); NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("志愿者团队ID"); row1.CreateCell(1).SetCellValue("团队登录名"); row1.CreateCell(2).SetCellValue("团队名称"); row1.CreateCell(3).SetCellValue("负责人"); row1.CreateCell(4).SetCellValue("联系方式"); row1.CreateCell(5).SetCellValue("邮箱"); row1.CreateCell(6).SetCellValue("活动数目"); row1.CreateCell(7).SetCellValue("创建时间"); row1.CreateCell(8).SetCellValue("最后登录时间"); row1.CreateCell(9).SetCellValue("团队状态"); //将数据逐步写入sheet1各个行 for (int i = 0; i < _data.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(_data[i].OrganizeInfoID); rowtemp.CreateCell(1).SetCellValue(_data[i].OrganizeInfoLoginId); rowtemp.CreateCell(2).SetCellValue(_data[i].OrganizeInfoShowName); rowtemp.CreateCell(3).SetCellValue(_data[i].OrganizeInfoPeople); rowtemp.CreateCell(4).SetCellValue(_data[i].OrganizeInfoPhone); rowtemp.CreateCell(5).SetCellValue(string.IsNullOrEmpty(_data[i].OrganizeInfoEmail) ? "无" : _data[i].OrganizeInfoEmail); rowtemp.CreateCell(6).SetCellValue(_data[i].ActivityCount.ToString()); rowtemp.CreateCell(7).SetCellValue(_data[i].CreateTime.ToString()); rowtemp.CreateCell(8).SetCellValue(_data[i].OrganizeInfoLastTime.ToString()); rowtemp.CreateCell(9).SetCellValue(_data[i].Status == 0 ? "正常" : _data[i].Status == 1 ? "删除" : _data[i].Status == 2 ?"审核中": "无效"); } System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(ms); }
public FileResult ExportExcel(string dateBegin, string dateEnd) { string Condition = " and 1=1"; if (!string.IsNullOrEmpty(dateBegin)) { Condition += " and PatiInBillItem.CreateDate >= '" + DateTime.Parse(dateBegin).ToString("yyyy-MM-dd 00:00:00") + "'"; } if (!string.IsNullOrEmpty(dateEnd)) { Condition += " and PatiInBillItem.CreateDate <= '" + DateTime.Parse(dateEnd).ToString("yyyy-MM-dd 23:59:59") + "'"; } IList <UserBillTotal> billTotal = new List <UserBillTotal>(); DataTable lstBillItem = billitemLogic.GetPersonBillTypeAmount(Condition, 9999, 1, "", ""); if (lstBillItem != null && lstBillItem.Rows.Count > 0) { foreach (DataRow drNew in lstBillItem.Rows) { UserBillTotal item = new UserBillTotal(); item.BillDate = drNew[0].ToString(); item.TotalAmount = decimal.Parse(drNew[1].ToString()); billTotal.Add(item); } } //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //获取list数据 // List<modelList> listRainInfo = m_BLL.GetSchoolListAATQ(schoolname); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("就餐日期"); row1.CreateCell(1).SetCellValue("金额"); //将数据逐步写入sheet1各个行 for (int i = 0; i < billTotal.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(billTotal[i].BillDate.ToString()); rowtemp.CreateCell(1).SetCellValue(billTotal[i].TotalAmount.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")); }