private void LoadMember() { //拼接查询条件 Dictionary <string, string> dic = new Dictionary <string, string>(); if (!string.IsNullOrWhiteSpace(txtId.Text)) { dic.Add("MId", txtId.Text.Trim()); } if (!string.IsNullOrWhiteSpace(txtPhone.Text)) { dic.Add("MPhone", txtPhone.Text.Trim()); } //调用查询方法,得到列表 MemberInfoBll miBll = new MemberInfoBll(); List <MemberInfo> list = miBll.GetList(dic); //控件赋值 if (list.Count > 0) { //查到会员信息,显示信息 MemberInfo mi = list[0]; lblMoney.Text = mi.MMoney.ToString(); lblTypeTitle.Text = mi.MTypeTitle; lblDiscount.Text = mi.MDiscount.ToString(); lblPayMoneyDiscount.Text = (decimal.Parse(lblPayMoney.Text) * decimal.Parse(lblDiscount.Text)).ToString(); } else { MessageBox.Show("信息有误,未查到此会员"); } }
private void GetMemberInfo() { MemberInfo mi = new MemberInfo(); if (!string.IsNullOrEmpty(textBox1.Text)) { mi.MId = Convert.ToInt32(textBox1.Text); } mi.MPhone = textBox2.Text; MemberInfoBll miBll = new MemberInfoBll(); var list = miBll.GetList(mi); if (list.Count == 1) { mi = list[0]; textBox1.Text = mi.MId.ToString(); textBox2.Text = mi.MPhone; lbyue.Text = mi.MMoney.ToString(); lbdengji.Text = mi.TypeTitle; lbzhekou.Text = mi.TypeDiscount.ToString(); //根据折扣,更新应付折扣金额的值 decimal payMoneyDiscount = mi.TypeDiscount * Convert.ToDecimal(lbxfjine.Text); lbysjine.Text = payMoneyDiscount.ToString(); } else { MessageBox.Show("会员信息有误,请核对!!!"); } }
private void LoadMember() { //Search Dictionary <string, string> dic = new Dictionary <string, string>(); if (txtId.Text != "") { dic.Add("mid", txtId.Text); } if (txtPhone.Text != "") { dic.Add("mPhone", txtPhone.Text); } MemberInfoBll miBll = new MemberInfoBll(); var list = miBll.Getlist(dic); if (list.Count > 0) { //Get MemberInfo MemberInfo mi = list[0]; lblMoney.Text = mi.MMoney.ToString(); lblTypeTitle.Text = mi.MTypeTitle; lblDiscount.Text = mi.MDiscount.ToString(); //Discount lblPayMoneyDiscount.Text = (Convert.ToDecimal(lblPayMoney.Text) * Convert.ToDecimal(lblDiscount.Text)).ToString(); } else { MessageBox.Show("Wrong info"); } }
private void LoadMember() { //根据会员编号和会员电话进行查询 Dictionary <string, string> dic = new Dictionary <string, string>(); if (txtId.Text != "") { dic.Add("mid", txtId.Text); } if (txtPhone.Text != "") { dic.Add("mPhone", txtPhone.Text); } MemberInfoBll miBll = new MemberInfoBll(); var list = miBll.GetList(dic); if (list.Count > 0) { //根据信息查到了会员 MemberInfo mi = list[0]; lblMoney.Text = mi.MMoney.ToString(); lblTypeTitle.Text = mi.MTypeTitle; lblDiscount.Text = mi.MDiscount.ToString(); //计算折扣价 lblPayMoneyDiscount.Text = (Convert.ToDecimal(lblPayMoney.Text) * Convert.ToDecimal(lblDiscount.Text)).ToString(); } else { MessageBox.Show("会员信息有误"); } }
private void btnEnter_Click(object sender, EventArgs e) { if (pictureBox1.Image != null) { MemberInfo mi = new MemberInfo(); MemberInfoBll miBll = new MemberInfoBll(); DialogResult result = MessageBox.Show("用户照片将进行修改,是否继续?", "提示", MessageBoxButtons.OKCancel); if (result == DialogResult.OK) { string fileName = "photo" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ff") + ".jpg"; mbitmap.Save(@"..\\img\\" + fileName, ImageFormat.Jpeg); string path = "..\\img\\" + fileName; mi.MPhoto = path; mi.MId = mid; mbitmap.Dispose(); if (miBll.UpdatePhoto(mi)) { UpdataPhotoEvent(); this.Close(); } } } else { MessageBox.Show("您还没有拍照"); } }
/// <summary> /// 获取会员信息 /// </summary> private void GetMemberInfo() { var mi = new MemberInfo(); if (!string.IsNullOrEmpty(txt_id.Text)) { mi.Mid = Convert.ToInt32(txt_id.Text); } if (!string.IsNullOrEmpty(txt_Phone.Text)) { mi.MPhone = txt_Phone.Text; } var miBll = new MemberInfoBll(); var list = miBll.GetList(mi); if (list.Count == 1) { //只有查询到一个信息 mi = list[0]; txt_id.Text = mi.Mid.ToString(); txt_Phone.Text = mi.MPhone; lbl_Money.Text = mi.MMoney.ToString(); lbl_TypeTitle.Text = mi.TyptTitle; lbl_Discount.Text = mi.TypeDiscount.ToString(); lbl_PayMoney.Text = (mi.TypeDiscount * Convert.ToDecimal(lbl_TotalMoney.Text)).ToString(); cb_kMoney.Checked = false; } else { MessageBox.Show("信息有误!请核对"); } }
private void LoadMember() { MemberInfo mi = new MemberInfo(); if (!string.IsNullOrEmpty(txtId.Text)) { mi.MId = Convert.ToInt32(txtId.Text); } mi.MPhone = txtPhone.Text; MemberInfoBll miBll = new MemberInfoBll(); List <MemberInfo> miList = miBll.GetList(mi); if (miList.Count == 1) { lblDiscount.Text = miList[0].MDiscount.ToString(); txtPhone.Text = miList[0].MPhone.ToString(); lblMoney.Text = miList[0].MMoney.ToString(); lblTypeTitle.Text = miList[0].MTitle; lblPayMoneyDiscount.Text = (Convert.ToDouble(lblPayMoney.Text) * Convert.ToDouble(lblDiscount.Text)).ToString(); } else { MessageBox.Show("会员编号不符,请重新输入"); } }
private void LoadPhoto() { MemberInfoBll miBll = new MemberInfoBll(); MemberInfo mi = new MemberInfo(); mi.MName = txtName.Text; mi.MCId = txtCId.Text; mi.MPhone = txtPhone.Text; var list = miBll.GetList(mi); if (list.Count == 1) { txtName.ReadOnly = true; txtPhone.ReadOnly = true; txtCId.ReadOnly = true; txtName.Text = list[0].MName; txtPhone.Text = list[0].MPhone; txtCId.Text = list[0].MCId; txtCount.Text = Convert.ToString(list[0].MCount); dtp2.Value = list[0].MEndDate; dtp3.Value = list[0].MDate; txtType.Text = list[0].TypeTitle; txtCBZ.Text = list[0].TypeBz; TimeSpan temsub = DateTime.Now.Subtract(Convert.ToDateTime(list[0].MEndDate)); string subdays = Math.Abs(Convert.ToInt32(temsub.Days)).ToString(); string lblstr = ""; if (DateTime.Now > Convert.ToDateTime(list[0].MEndDate)) { lblstr += list[0].MName.ToString() + "您好,对不起!您的会员卡已经过期:" + subdays + "天!"; lblred.ForeColor = Color.Red; } else { lblstr += list[0].MName.ToString() + "您好,欢迎您!您的会员卡还剩余:" + subdays + "天!"; lblred.ForeColor = Color.Black; } lblred.Text = lblstr; var path = list[0].MPhoto; if (!string.IsNullOrEmpty(path)) { pictureBox1.Image = Image.FromFile(path); } else { MessageBox.Show("显示错误或没有照片"); } } }
private void btnAdd_Click(object sender, EventArgs e) { mi.MStartDate = DateTime.Now; mi.MEndDate = temp; mi.MDate = DateTime.Now; if (!string.IsNullOrEmpty(txtName.Text) && !string.IsNullOrEmpty(txtPhone.Text) && !string.IsNullOrEmpty(txtType.Text) && !string.IsNullOrEmpty(txtAdress.Text) && !string.IsNullOrEmpty(txtCId.Text)) { if (txtPhone.Text.Length == 11) { if (pictureBox1.Image != null) { MemberInfoBll miBll = new MemberInfoBll(); DialogResult result = MessageBox.Show("用户信息核对完成,是否要注册?", "提示", MessageBoxButtons.OKCancel); LoadList(); if (result == DialogResult.OK) { string fileName = "photo" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ff") + ".jpg"; mbitmap.Save(@"..\\img\\" + fileName, ImageFormat.Jpeg); string path = "..\\img\\" + fileName; mi.MPhoto = path; mbitmap.Dispose(); if (miBll.Add(mi)) { MessageBox.Show("注册成功"); btnCancel_Click(null, null); } } } else { MessageBox.Show("您还没有拍照"); } } else { MessageBox.Show("手机号填写错误!"); } } else { MessageBox.Show("请将信息填写完整!"); } }
private void txtId_MouseLeave(object sender, EventArgs e) { MemberInfoBll bll = new MemberInfoBll(); try { MemberInfo model = bll.SelectMember(txtId.Text); lblMoney.Text = model.MMoney.ToString(); lblTypeTitle.Text = model.MType; lblDiscount.Text = model.MDiscount; double sum = money * (Convert.ToDouble(model.MDiscount)); lblPayMoneyDiscount.Text = sum.ToString(); } catch { MessageBox.Show("用户不存在"); } }
private void MemberPhotoList_Load(object sender, EventArgs e) { string mcid = Convert.ToString(Tag); MemberInfo miSreach = new MemberInfo(); miSreach.MCId = mcid; MemberInfoBll miBll = new MemberInfoBll(); var list = miBll.GetList(miSreach); if (list.Count == 1) { mid = list[0].MId; var path = list[0].MPhoto; if (!string.IsNullOrEmpty(path)) { pictureBox1.Image = System.Drawing.Image.FromFile(path); } } try { // 枚举所有视频输入设备 videoDevices = new FilterInfoCollection(FilterCategory.VideoInputDevice); if (videoDevices.Count == 0) { throw new ApplicationException(); } foreach (FilterInfo device in videoDevices) { txtCamera.Items.Add(device.Name); } txtCamera.SelectedIndex = 0; } catch (ApplicationException) { txtCamera.Items.Add("No local capture devices"); videoDevices = null; } }
private void GetMemberInfo() { if (txtMname.ReadOnly == false && txtMphone.ReadOnly == false)//这里必须确定txt框是可输入状态下,否则不是人为的改变,则不做查询。 { MemberInfo mi = new MemberInfo(); mi.MName = txtMname.Text; mi.MPhone = txtMphone.Text; MemberInfoBll miBll = new MemberInfoBll(); var list = miBll.GetList(mi); //当只查询出一个结果的时候 if (list.Count == 1) { //将会员编号和打折数记录下来 memberId = list[0].MId; discount = list[0].TypeDiscount; //将查询的结果显示出来 lblMoney.Text = list[0].MMoney.ToString(); lblTypeDiscount.Text = (list[0].TypeDiscount * 10).ToString() + "折"; lblType.Text = list[0].TypeTitle; //查询到结果之后,不许输入!并将正确结果显示 txtMname.ReadOnly = true; txtMphone.ReadOnly = true; txtMname.Text = list[0].MName; txtMphone.Text = list[0].MPhone; //计算实收金额,根据折扣更新实际收费 lblShouldMoney.Text = (Convert.ToDecimal(lblConsumeMoney.Text) * list[0].TypeDiscount).ToString(); payShouldOmoney = Convert.ToDecimal(lblShouldMoney.Text);//存下来,打折后应该实际收的钱。 //一旦查询到会员,就需要更新使用余额的状态 ckbMoney_CheckedChanged(null, null); //重新计算找零 txtRealIncome_Leave(null, null); } else { lblShouldMoney.Text = lblConsumeMoney.Text; } } }
private void button1_Click(object sender, EventArgs e) { if (lblred.ForeColor != Color.Red) { if (txtName.ReadOnly == true) { MemberInfoBll miBll = new MemberInfoBll(); MemberInfo mi = new MemberInfo(); mi.MCId = txtCId.Text; miBll.UpdateCount(mi); txtName.Text = ""; txtPhone.Text = ""; txtCId.Text = ""; txtCount.Text = ""; txtCBZ.Text = ""; txtType.Text = ""; txtName.ReadOnly = false; txtPhone.ReadOnly = false; txtCId.ReadOnly = false; pictureBox1.Image = Resources.psb; lblred.Text = "等待检测中"; lblred.ForeColor = Color.Black; } } else { DialogResult result = MessageBox.Show("该会员已经到期,确认通过吗?", "会员到期提示", MessageBoxButtons.OKCancel); if (result == DialogResult.OK) { if (txtName.ReadOnly == true) { MemberInfoBll miBll = new MemberInfoBll(); MemberInfo mi = new MemberInfo(); mi.MCId = txtCId.Text; miBll.UpdateCount(mi); txtName.Text = ""; txtPhone.Text = ""; txtCId.Text = ""; txtCount.Text = ""; txtCBZ.Text = ""; txtType.Text = ""; txtName.ReadOnly = false; txtPhone.ReadOnly = false; txtCId.ReadOnly = false; pictureBox1.Image = Resources.psb; lblred.Text = "等待检测中"; lblred.ForeColor = Color.Black; } } else { if (txtName.ReadOnly == true) { txtName.Text = ""; txtPhone.Text = ""; txtCId.Text = ""; txtCount.Text = ""; txtCBZ.Text = ""; txtType.Text = ""; txtName.ReadOnly = false; txtPhone.ReadOnly = false; txtCId.ReadOnly = false; pictureBox1.Image = Resources.psb; lblred.Text = "等待检测中"; lblred.ForeColor = Color.Black; } } } txtCId.Select(); }
private void btnxls_Click(object sender, EventArgs e) { DialogResult result = MessageBox.Show("点击【确定】将把所有会员信息导出为xls表格!", "会员信息导出提示!", MessageBoxButtons.OKCancel); if (result == DialogResult.OK) { #region 弹出保存窗口对话框,选择保存路径,创建工作本 /*******************************************************/ /*******************************************************/ /*******************************************************/ //弹出保存对话框 SaveFileDialog sfd = new SaveFileDialog(); sfd.Title = "请选择要保存的路径,默认为桌面"; sfd.InitialDirectory = @"C:\Users\Administrator\Desktop"; sfd.Filter = "xls表格文件|*.xls|所有文件|*.*"; sfd.ShowDialog(); //获得保存文件的路径 string path = sfd.FileName; if (path == "")//打开了对话框,但是没有选择地址 { return; } //创建工作本 HSSFWorkbook workbook = new HSSFWorkbook(); //如果用户选择了保存路径,则开始进行保存操作 #endregion #region 表格样式操作 //导出:将数据库中的数据,存储到一个excel中 //2、生成excel //生成workbook //生成sheet //遍历集合,生成行。(一个对象就是一行) //根据对象生成单元格,一个属性对应一个单元格 //样式表 /*****************************************************************************/ /******************表格样式操作*********************************************/ /*****************************************************************************/ /************/ //表头标题样式操作(水平垂直居中,黄色背景,字体颜色黑色,微软雅黑加粗,字体大小18) /*************/ var styleTitle = workbook.CreateCellStyle(); //style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//左对齐 //style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;//右对齐 styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中 //垂直居中 styleTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; // //字体样式设置 HSSFFont fontTitle = (HSSFFont)workbook.CreateFont(); fontTitle.FontName = "微软雅黑"; //字体 fontTitle.FontHeightInPoints = 18; //字号 fontTitle.Color = HSSFColor.Red.Index; //颜色 红色 fontTitle.IsBold = true; //加粗 // font.Underline = NPOI.SS.UserModel.FontUnderlineType.Double;//下划线 // font.IsStrikeout = true;//删除线 // font.IsItalic = true;//斜体 //将字体样式加入表头样式中 styleTitle.SetFont(fontTitle); //背景色设置 //styleTitle.FillBackgroundColor = HSSFColor.Yellow.Index;//背景色 styleTitle.FillForegroundColor = HSSFColor.Yellow.Index; //前景色 styleTitle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //填充方式 AltBars /****************/ //表格列头样式操作 /****************/ //表格列头样式操作(水平垂直居中,蓝色背景,字体颜色白色,黑体,字体大小16) var styleHeader = workbook.CreateCellStyle(); styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 styleHeader.FillForegroundColor = HSSFColor.Blue.Index; //前景色 styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //填充方式 AltBars HSSFFont fontHeader = (HSSFFont)workbook.CreateFont(); fontHeader.FontName = "黑体"; //字体 fontHeader.FontHeightInPoints = 12; //字号 fontHeader.Color = HSSFColor.White.Index; //颜色 fontHeader.IsBold = true; //加粗 styleHeader.SetFont(fontHeader); /*****************/ /****************/ /*****************/ //*表脚样式*// /*****************/ var styleFoot = workbook.CreateCellStyle(); styleFoot.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 styleFoot.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 styleFoot.FillForegroundColor = HSSFColor.Blue.Index; //前景色 styleFoot.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //填充方式 AltBars HSSFFont fontFoot = (HSSFFont)workbook.CreateFont(); fontFoot.FontName = "黑体"; //字体 fontFoot.FontHeightInPoints = 10; //字号 fontFoot.Color = HSSFColor.White.Index; //颜色 fontFoot.IsBold = true; //加粗 styleFoot.SetFont(fontFoot); /*****************/ /****************/ /*****************/ //*表格正文样式*// /*****************/ var styleBody = workbook.CreateCellStyle(); styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 HSSFFont fontBody = (HSSFFont)workbook.CreateFont(); fontBody.FontName = "宋体"; //字体 fontBody.FontHeightInPoints = 12; //字号 fontBody.Color = HSSFColor.Black.Index; //颜色 fontBody.IsBold = true; //加粗 styleBody.SetFont(fontBody); /*****************/ /****************/ /******************************************************************************/ /***************************表格样式操作***************************************/ /*****************************************************************************/ #endregion #region 会员信息存储 /***********************创建工作表,在一个工作本里面可以有多张表*************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ //1、查询数据 //保存人员信息表 MemberInfoBll miBll = new MemberInfoBll(); MemberInfo mi = new MemberInfo(); var listmiInfo = miBll.GetList(mi); //创建工作表 //NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("管理员"); ISheet sheet = workbook.CreateSheet("会员信息表"); //创建标题行 IRow row = sheet.CreateRow(0); //合并单元格 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 11)); ICell cellTitle = row.CreateCell(0); cellTitle.SetCellValue("阳光力美健身中心会员信息表"); row.HeightInPoints = 45;//表头标题行高 //应用样式 cellTitle.CellStyle = styleTitle; //创建列名称 IRow row1 = sheet.CreateRow(1); row1.HeightInPoints = 20;//表列头行高 //创建单元格 //设置名称 //应用样式 //设置列的宽度 //SetColumnWidth()方法有两个参数,第一个是列的索引,从0开始。注意第二个参数,是以1/256为单位。 ICell cellId = row1.CreateCell(0); cellId.SetCellValue("编号"); cellId.CellStyle = styleHeader; sheet.SetColumnWidth(0, 8 * 256); ICell cellName = row1.CreateCell(1); cellName.SetCellValue("姓名"); cellName.CellStyle = styleHeader; sheet.SetColumnWidth(1, 10 * 256); ICell cellNumber = row1.CreateCell(2); cellNumber.SetCellValue("性别"); cellNumber.CellStyle = styleHeader; sheet.SetColumnWidth(2, 6 * 256); ICell cellDepartment = row1.CreateCell(3); cellDepartment.SetCellValue("卡号"); cellDepartment.CellStyle = styleHeader; sheet.SetColumnWidth(3, 12 * 256); ICell cellDuty = row1.CreateCell(4); cellDuty.SetCellValue("类型"); cellDuty.CellStyle = styleHeader; sheet.SetColumnWidth(4, 10 * 256); ICell cellPhone = row1.CreateCell(5); cellPhone.SetCellValue("联系方式"); cellPhone.CellStyle = styleHeader; sheet.SetColumnWidth(5, 16 * 256); ICell cellQQ = row1.CreateCell(6); cellQQ.SetCellValue("会员住址"); cellQQ.CellStyle = styleHeader; sheet.SetColumnWidth(6, 18 * 256); ICell cellPAdd = row1.CreateCell(7); cellPAdd.SetCellValue("锻炼次数"); cellPAdd.CellStyle = styleHeader; sheet.SetColumnWidth(7, 12 * 256); ICell cellJoinTime = row1.CreateCell(8); cellJoinTime.SetCellValue("最近锻炼时间"); cellJoinTime.CellStyle = styleHeader; sheet.SetColumnWidth(8, 20 * 256); ICell cellLeaveTime = row1.CreateCell(9); cellLeaveTime.SetCellValue("办卡时间"); cellLeaveTime.CellStyle = styleHeader; sheet.SetColumnWidth(9, 20 * 256); ICell cellendTime = row1.CreateCell(10); cellendTime.SetCellValue("截止时间"); cellendTime.CellStyle = styleHeader; sheet.SetColumnWidth(10, 20 * 256); ICell cellbz = row1.CreateCell(11); cellbz.SetCellValue("备注"); cellbz.CellStyle = styleHeader; sheet.SetColumnWidth(11, 20 * 256); //遍历集合,生成行 int index = 2;//索引,0,1是表头。所以从2开始。 foreach (var item in listmiInfo) { //创建下面每行单元格的值 var row2 = sheet.CreateRow(index++); var cell0 = row2.CreateCell(0); cell0.SetCellValue(item.MId.ToString()); cell0.CellStyle = styleBody; var cell1 = row2.CreateCell(1); cell1.SetCellValue(item.MName); var cell2 = row2.CreateCell(2); cell2.SetCellValue(item.MSex); var cell3 = row2.CreateCell(3); cell3.SetCellValue(item.MCId); var cell4 = row2.CreateCell(4); cell4.SetCellValue(item.TypeTitle); var cell5 = row2.CreateCell(5); cell5.SetCellValue(item.MPhone); var cell6 = row2.CreateCell(6); cell6.SetCellValue(item.MAdress); var cell7 = row2.CreateCell(7); cell7.SetCellValue(item.MCount.ToString()); var cell8 = row2.CreateCell(8); cell8.SetCellValue(item.MDate.ToString()); var cell9 = row2.CreateCell(9); cell9.SetCellValue(item.MStartDate.ToString()); var cell10 = row2.CreateCell(10); cell10.SetCellValue(item.MEndDate.ToString()); var cell11 = row2.CreateCell(11); cell11.SetCellValue(item.MBZ); } /**表页脚**/ //创建标题行 IRow row3 = sheet.CreateRow(index); //合并单元格 sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 11)); ICell cellFoot = row3.CreateCell(0); cellFoot.SetCellValue("欢迎使用!如有建议或需求反馈可以联系梦雨客服(小雨)QQ:1517680389"); row3.HeightInPoints = 22;//表头标题行高 //应用样式 cellFoot.CellStyle = styleFoot; /**********/ /*********************************************/ /**************会员信息表*********************/ /*********************************************/ #endregion #region 写入文件保存 //是否成功标志 bool flag = false; try { FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write); workbook.Write(file); file.Dispose();//使用完要释放资源,或者使用using(){} flag = true; } catch { MessageBox.Show("保存出错!\n请关闭其他xls文件,再重试!"); flag = false; } /*******************************************************/ if (flag) { //操作完成,导出成功提示 MessageBox.Show("数据导出成功!导出路径如下:\n" + path + "\n敬请及时查收!"); } #endregion } }
private void btnXls_Click(object sender, EventArgs e) { ////因为这个数据导出可能出现意外,并且导出时间可能比较长,所以决定开一个线程去做 //开了线程之后发现无法弹出保存路径对话框,无奈,于是放弃 //Control.CheckForIllegalCrossThreadCalls = false; //Thread thOutputXls = new Thread(OutputXls); //thOutputXls.IsBackground = false; //thOutputXls.Start(); DialogResult result = MessageBox.Show("点击【确定】将把会员数据,菜品数据导出为xls表格!", "数据导出提示!", MessageBoxButtons.OKCancel); if (result == DialogResult.OK) { #region 弹出保存窗口对话框,选择保存路径,创建工作本 /*******************************************************/ /*******************************************************/ /*******************************************************/ //弹出保存对话框 SaveFileDialog sfd = new SaveFileDialog(); sfd.Title = "请选择要保存的路径,默认为桌面"; sfd.InitialDirectory = @"C:\Users\Administrator\Desktop"; sfd.Filter = "xls表格文件|*.xls|所有文件|*.*"; sfd.ShowDialog(); //获得保存文件的路径 string path = sfd.FileName; if (path == "")//打开了对话框,但是没有选择地址 { return; } //创建工作本 HSSFWorkbook workbook = new HSSFWorkbook(); //如果用户选择了保存路径,则开始进行保存操作 #endregion #region 表格样式操作 //导出:将数据库中的数据,存储到一个excel中 //2、生成excel //生成workbook //生成sheet //遍历集合,生成行。(一个对象就是一行) //根据对象生成单元格,一个属性对应一个单元格 //样式表 /*****************************************************************************/ /******************表格样式操作*********************************************/ /*****************************************************************************/ /************/ //表头标题样式操作(水平垂直居中,黄色背景,字体颜色黑色,微软雅黑加粗,字体大小18) /*************/ var styleTitle = workbook.CreateCellStyle(); //style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//左对齐 //style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;//右对齐 styleTitle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//居中 //垂直居中 styleTitle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; // //字体样式设置 HSSFFont fontTitle = (HSSFFont)workbook.CreateFont(); fontTitle.FontName = "微软雅黑"; //字体 fontTitle.FontHeightInPoints = 18; //字号 fontTitle.Color = HSSFColor.Red.Index; //颜色 红色 fontTitle.IsBold = true; //加粗 // font.Underline = NPOI.SS.UserModel.FontUnderlineType.Double;//下划线 // font.IsStrikeout = true;//删除线 // font.IsItalic = true;//斜体 //将字体样式加入表头样式中 styleTitle.SetFont(fontTitle); //背景色设置 //styleTitle.FillBackgroundColor = HSSFColor.Yellow.Index;//背景色 styleTitle.FillForegroundColor = HSSFColor.Yellow.Index; //前景色 styleTitle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //填充方式 AltBars /****************/ //表格列头样式操作 /****************/ //表格列头样式操作(水平垂直居中,蓝色背景,字体颜色白色,黑体,字体大小16) var styleHeader = workbook.CreateCellStyle(); styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 styleHeader.FillForegroundColor = HSSFColor.Blue.Index; //前景色 styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //填充方式 AltBars HSSFFont fontHeader = (HSSFFont)workbook.CreateFont(); fontHeader.FontName = "黑体"; //字体 fontHeader.FontHeightInPoints = 12; //字号 fontHeader.Color = HSSFColor.White.Index; //颜色 fontHeader.IsBold = true; //加粗 styleHeader.SetFont(fontHeader); /*****************/ /****************/ /*****************/ //*表脚样式*// /*****************/ var styleFoot = workbook.CreateCellStyle(); styleFoot.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 styleFoot.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 styleFoot.FillForegroundColor = HSSFColor.Blue.Index; //前景色 styleFoot.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; //填充方式 AltBars HSSFFont fontFoot = (HSSFFont)workbook.CreateFont(); fontFoot.FontName = "黑体"; //字体 fontFoot.FontHeightInPoints = 10; //字号 fontFoot.Color = HSSFColor.White.Index; //颜色 fontFoot.IsBold = true; //加粗 styleFoot.SetFont(fontFoot); /*****************/ /****************/ /*****************/ //*表格正文样式*// /*****************/ var styleBody = workbook.CreateCellStyle(); styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 HSSFFont fontBody = (HSSFFont)workbook.CreateFont(); fontBody.FontName = "宋体"; //字体 fontBody.FontHeightInPoints = 12; //字号 fontBody.Color = HSSFColor.Black.Index; //颜色 fontBody.IsBold = true; //加粗 styleBody.SetFont(fontBody); /*****************/ /****************/ /******************************************************************************/ /***************************表格样式操作***************************************/ /*****************************************************************************/ #endregion #region 会员信息表存储 /***********************创建工作表,在一个工作本里面可以有多张表*************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /*********************************************/ /**************会员信息表*********************/ /*********************************************/ //1、查询数据 //保存会员信息表 MemberInfoBll meiBll = new MemberInfoBll(); MemberInfo mei = new MemberInfo(); var listMemberInfo = meiBll.GetList(mei); //创建工作表 //NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("管理员"); ISheet sheet = workbook.CreateSheet("会员信息表"); //创建标题行 IRow row = sheet.CreateRow(0); //合并单元格 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5)); ICell cellTitle = row.CreateCell(0); cellTitle.SetCellValue("会员信息记录表"); row.HeightInPoints = 22;//表头标题行高 //应用样式 cellTitle.CellStyle = styleTitle; //创建列名称 IRow row1 = sheet.CreateRow(1); row1.HeightInPoints = 20;//表列头行高 //创建单元格 //设置名称 //应用样式 //设置列的宽度 //SetColumnWidth()方法有两个参数,第一个是列的索引,从0开始。注意第二个参数,是以1/256为单位。 ICell cellId = row1.CreateCell(0); cellId.SetCellValue("编号"); cellId.CellStyle = styleHeader; sheet.SetColumnWidth(0, 10 * 256); ICell cellName = row1.CreateCell(1); cellName.SetCellValue("会员名称"); cellName.CellStyle = styleHeader; sheet.SetColumnWidth(1, 20 * 256); ICell cellPhone = row1.CreateCell(2); cellPhone.SetCellValue("会员手机号"); cellPhone.CellStyle = styleHeader; sheet.SetColumnWidth(2, 25 * 256); ICell cellMoney = row1.CreateCell(3); cellMoney.SetCellValue("会员余额"); cellMoney.CellStyle = styleHeader; sheet.SetColumnWidth(3, 18 * 256); ICell cellType = row1.CreateCell(4); cellType.SetCellValue("会员类型"); cellType.CellStyle = styleHeader; sheet.SetColumnWidth(4, 20 * 256); ICell cellDiscount = row1.CreateCell(5); cellDiscount.SetCellValue("享受折扣"); cellDiscount.CellStyle = styleHeader; sheet.SetColumnWidth(5, 18 * 256); //遍历集合,生成行 int index = 2;//索引,0,1是表头。所以从2开始。 foreach (var item in listMemberInfo) { //创建下面每行单元格的值 var row2 = sheet.CreateRow(index++); var cell0 = row2.CreateCell(0); cell0.SetCellValue(item.MId); cell0.CellStyle = styleBody; var cell1 = row2.CreateCell(1); cell1.SetCellValue(item.MName); var cell2 = row2.CreateCell(2); cell2.SetCellValue(item.MPhone); var cell3 = row2.CreateCell(3); cell3.SetCellValue(item.MMoney.ToString()); var cell4 = row2.CreateCell(4); cell4.SetCellValue(item.TypeTitle); var cell5 = row2.CreateCell(5); cell5.SetCellValue((item.TypeDiscount * 10).ToString() + "折"); } /**表页脚**/ //创建标题行 IRow row3 = sheet.CreateRow(index); //合并单元格 sheet.AddMergedRegion(new CellRangeAddress(index, index, 0, 5)); ICell cellFoot = row3.CreateCell(0); cellFoot.SetCellValue("欢迎使用!如有建议或需求反馈可以联系梦雨客服QQ:1517680389"); row3.HeightInPoints = 22;//表头标题行高 //应用样式 cellFoot.CellStyle = styleFoot; /**********/ /*********************************************/ /**************会员信息表*********************/ /*********************************************/ #endregion #region 菜品信息表储存 /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /*********************************************/ /**************菜品信息表*********************/ /*********************************************/ //关于菜品信息表,需要显示基本的菜品信息,这里我是想加入每个菜品在一段时间内的点菜频率的 //感觉有了点菜频率统计,还是蛮使用 //只是一直没想出来更好的统计方案,暂时也就没有做,现在就只是单纯的输出吧 //1.查询数据 DishInfoBll diBll = new DishInfoBll(); DishInfo di = new DishInfo(); var listDishInfo = diBll.GetList(di); //2.创建工作表 ISheet sheetDish = workbook.CreateSheet("菜品信息表"); //3.创建标题行 IRow rowDish0 = sheetDish.CreateRow(0); //合并单元格 sheetDish.AddMergedRegion(new CellRangeAddress(0, 0, 0, 3)); ICell cellDishTitle = rowDish0.CreateCell(0); cellDishTitle.SetCellValue("菜品信息记录表"); rowDish0.HeightInPoints = 22;//表头标题行高 //应用样式 cellDishTitle.CellStyle = styleTitle; //4.创建列名称 IRow rowDish1 = sheetDish.CreateRow(1); rowDish1.HeightInPoints = 20;//表列头行高 //创建单元格 //设置名称 //应用样式 //设置列的宽度 //SetColumnWidth()方法有两个参数,第一个是列的索引,从0开始。注意第二个参数,是以1/256为单位。 ICell cellDishId = rowDish1.CreateCell(0); cellDishId.SetCellValue("编号"); cellDishId.CellStyle = styleHeader; sheetDish.SetColumnWidth(0, 10 * 256); ICell cellNameDishTitle = rowDish1.CreateCell(1); cellNameDishTitle.SetCellValue("菜品名称"); cellNameDishTitle.CellStyle = styleHeader; sheetDish.SetColumnWidth(1, 30 * 256); ICell cellDishType = rowDish1.CreateCell(2); cellDishType.SetCellValue("所属菜系"); cellDishType.CellStyle = styleHeader; sheetDish.SetColumnWidth(2, 25 * 256); ICell cellDishPrice = rowDish1.CreateCell(3); cellDishPrice.SetCellValue("菜品单价"); cellDishPrice.CellStyle = styleHeader; sheetDish.SetColumnWidth(3, 18 * 256); //遍历集合,生成行 int indexDish = 2;//索引,0,1是表头。所以从2开始。 foreach (var item in listDishInfo) { //创建下面每行单元格的值 var rowDish2 = sheetDish.CreateRow(indexDish++); var cell0 = rowDish2.CreateCell(0); cell0.SetCellValue(item.DId); cell0.CellStyle = styleBody; var cell1 = rowDish2.CreateCell(1); cell1.SetCellValue(item.DTitle); var cell2 = rowDish2.CreateCell(2); cell2.SetCellValue(item.TypeTitle); var cell3 = rowDish2.CreateCell(3); cell3.SetCellValue(item.DPrice.ToString() + "元"); } /**表页脚**/ //创建标题行 IRow rowDish3 = sheetDish.CreateRow(indexDish); //合并单元格 sheetDish.AddMergedRegion(new CellRangeAddress(indexDish, indexDish, 0, 3)); ICell cellDishFoot = rowDish3.CreateCell(0); cellDishFoot.SetCellValue("欢迎使用!如有建议或需求反馈可以联系梦雨客服QQ:1517680389"); rowDish3.HeightInPoints = 22;//表头标题行高 //应用样式 cellDishFoot.CellStyle = styleFoot; /*********************************************/ /**************菜品信息表*********************/ /*********************************************/ #endregion #region 点菜记录表存储 /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /*********************************************/ /**************点菜记录表*********************/ /*********************************************/ //这个主要是用来存储点菜记录的,我觉得每个酒店都应该记录一下,加入发生食品中毒等意外事件,可以查一查,做个参考啥的 //然后也能看出每天生意的大概情况,因为我自己的交接班功能,还没有一个整体方案,所以这个表还是可以参考的 //另外需要注意的是,如果客户先点击了缓存清理,那么只能可能就没有数据了。缓存清理会把所有已经付钱的订单全部删除。 //所以需要在缓存清理按钮上增加窗口弹出提示! //1.查询数据 OrderInfoBll oiBll = new OrderInfoBll(); var listOrderInfo = oiBll.GetOrderInfo(); //2.创建工作表 ISheet sheetOrder = workbook.CreateSheet("下单信息表"); //3.创建标题行 IRow rowOrder0 = sheetOrder.CreateRow(0); //合并单元格 sheetOrder.AddMergedRegion(new CellRangeAddress(0, 0, 0, 4)); ICell cellOrderTitle = rowOrder0.CreateCell(0); cellOrderTitle.SetCellValue("点菜下单信息记录表"); rowOrder0.HeightInPoints = 22;//表头标题行高 //应用样式 cellOrderTitle.CellStyle = styleTitle; //4.创建列名称 IRow rowOrder1 = sheetOrder.CreateRow(1); rowOrder1.HeightInPoints = 20;//表列头行高 //创建单元格 //设置名称 //应用样式 //设置列的宽度 //SetColumnWidth()方法有两个参数,第一个是列的索引,从0开始。注意第二个参数,是以1/256为单位。 ICell cellOrderId = rowOrder1.CreateCell(0); cellOrderId.SetCellValue("编号"); cellOrderId.CellStyle = styleHeader; sheetOrder.SetColumnWidth(0, 10 * 256); ICell cellOrderDate = rowOrder1.CreateCell(1); cellOrderDate.SetCellValue("下单时间"); cellOrderDate.CellStyle = styleHeader; sheetOrder.SetColumnWidth(1, 30 * 256); ICell cellOrderMember = rowOrder1.CreateCell(2); cellOrderMember.SetCellValue("会员姓名"); cellOrderMember.CellStyle = styleHeader; sheetOrder.SetColumnWidth(2, 15 * 256); ICell cellOrderPhone = rowOrder1.CreateCell(3); cellOrderPhone.SetCellValue("会员手机号"); cellOrderPhone.CellStyle = styleHeader; sheetOrder.SetColumnWidth(3, 25 * 256); ICell cellOrderMoney = rowOrder1.CreateCell(4); cellOrderMoney.SetCellValue("菜单总金额"); cellOrderMoney.CellStyle = styleHeader; sheetOrder.SetColumnWidth(4, 15 * 256); //遍历集合,生成行 int indexOrder = 2;//索引,0,1是表头。所以从2开始。 foreach (var item in listOrderInfo) { //创建下面每行单元格的值 var rowOrder2 = sheetOrder.CreateRow(indexOrder++); var cell0 = rowOrder2.CreateCell(0); cell0.SetCellValue(item.OId); cell0.CellStyle = styleBody; var cell1 = rowOrder2.CreateCell(1); cell1.SetCellValue(item.ODate.ToString()); var cell2 = rowOrder2.CreateCell(2); cell2.SetCellValue(item.MemberName); var cell3 = rowOrder2.CreateCell(3); cell3.SetCellValue(item.MemberPhone); var cell4 = rowOrder2.CreateCell(4); cell4.SetCellValue(item.OMoney.ToString() + "元"); } /**表页脚**/ //创建标题行 IRow rowOrder3 = sheetOrder.CreateRow(indexOrder); //合并单元格 sheetOrder.AddMergedRegion(new CellRangeAddress(indexOrder, indexOrder, 0, 4)); ICell cellOrderFoot = rowOrder3.CreateCell(0); cellOrderFoot.SetCellValue("欢迎使用!如有建议或需求反馈可以联系梦雨客服QQ:1517680389"); rowOrder3.HeightInPoints = 22;//表头标题行高 //应用样式 cellOrderFoot.CellStyle = styleFoot; /*********************************************/ /**************点菜记录表*********************/ /*********************************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ /******************************************************************************************************************************************/ #endregion #region 写入文件保存 //是否成功标志 bool flag = false; try { FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write); workbook.Write(file); file.Dispose();//使用完要释放资源,或者使用using(){} flag = true; } catch { MessageBox.Show("保存出错!\n请关闭其他xls文件,再重试!"); flag = false; } /*******************************************************/ if (flag) { //操作完成,导出成功提示 MessageBox.Show("数据导出成功!导出路径如下:\n" + path + "\n敬请及时查收!"); } #endregion } }