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
}
}
