第四天 -- 《2014-07-19 三层架构》 -- 多条件查询、Excel文件第三方组件基本操作

一、上午《01、回顾》--《06、实现查询功能》

1、实现多条件查询

　　可以在UI层根据用户选择了哪些条件，将条件参数放到字典Dictionary<string,object>里面传递到BLL层，最后传到DAL层可以根据字典里面包含的内容，拼接SQL语句并且创建SqlParameter参数。具体例子见下：

//UI层点击查询按钮后，根据用户选择的多个条件，组织一个字典。
private void btnSearch_Click(object sender, EventArgs e)
{//点击查询按钮后，根据多条件查询学员
    Dictionary<string, string> dics = new Dictionary<string, string>();
    if (cboSearchClass.Text != "请选择")
    {//如果用户选择某个班级作为条件之一。
        dics.Add("cname", cboSearchClass.Text);
    }
    if (!string.IsNullOrEmpty(txtSearchName.Text.Trim()))
    {//如果用户输入了一个学员名作为模糊查询条件。
        dics.Add("pcname", txtSearchName.Text.Trim());
    }
    //还有其他一些多条件，此处不一一写出。
 
    this.dgvList.DataSource = pm.SearchPersonList(dics, false);//调用BLL层查询方法。
} 


//BLL层查询方法
public List<MODEL.Person> SearchPersonList(Dictionary<string, string> dics, bool isdel)
{//isdel参数为true表示只查询已删除的记录。isdel为false时表示只查询没删的记录。
    return ps.SearchPersonList(dics, isdel);//调用DAL层查询方法
} 


//DAL层多条件查询方法
public List<MODEL.Person> SearchPersonList(Dictionary<string, string> dics, bool isdel)           
{
    string sql = "select pid,pcid,cname,ptype,ploginname,pcname,ppyname,ppwd,pgender,pemail,pareas,pisdel,paddtime from person inner join classes on person.pcid=classes.cid where pisdel=@isdel";                
   
    List<SqlParameter> ps = new List<SqlParameter>();//多条件             
    ps.Add(new SqlParameter("isdel", isdel));
   
    //下面代码就是根据用户指定的查询多条件，进行sql拼接，sql参数添加等等。
    if(dics.ContainsKey("cname"))
    {
        sql += "  and cname=@cname";
        ps.Add(new SqlParameter("cname",dics["cname"]));
    }
    if(dics.ContainsKey("pcname"))
    {
        sql += " and pcname like @pcname";
        //模糊查询的%不是在sql语句中创建，而是在参数中添加
        ps.Add(new SqlParameter("pcname","%" + dics["pcname"] + "%" ));
    }

    DataTable dt = SqlHelper.ExecuteTable(sql, ps.ToArray());
    List<MODEL.Person> lists = null;
    //判断有没有数据行
    if (dt.Rows.Count > 0)
    {
        lists = new List<MODEL.Person>();
        //遍历表，将表的每一行数据转换为对应的实体对象
        foreach (DataRow row in dt.Rows)
        {
            //每一行数据就对应着一个对象
            MODEL.Person temp = new MODEL.Person();
            //调用方法，将当前数据行转换为peron对象
            PersonRowToPersonObject(row, temp);
            //将对象添加到集合中
            lists.Add(temp);
        }
    }
    return lists;
}

 

 

 

 

二、下午《01、什么是NPOI》--《03、实现excel数据的导入》

1、NPOI基本使用代码示例

//新建Excel文件，导出实体对象到文件
private void btnOutput_Click(object sender, EventArgs e)
{//
    //1.创建工作薄，因为数据是写在工作表中的，而工作表是属于工作薄的
    HSSFWorkbook workbook = new HSSFWorkbook();
    //2.创建工作表 下面这个句代码表示 在  workbook创建一张工作表
    //HSSFSheet sheet = new HSSFSheet(workbook);
    HSSFSheet sheet = workbook.CreateSheet("classes");//也可以这样写，表示由workbook创建一个新sheet
    //3.遍历数据源，Dgv控件的每一行数据
    #region 循环遍历，添加工作薄中的工作表的行数据
    for (int i = 0; i < this.dgvClassList.Rows.Count; i++)
    {
        //遍历控件的每一行，获取绑定项(即实体对象)
        MODEL.Classes cla = this.dgvClassList.Rows[i].DataBoundItem as MODEL.Classes;
        //4.创建工作行
        HSSFRow row = sheet.CreateRow(i);
        //5.1创建第一列
        HSSFCell cell1 = row.CreateCell(0);
        //5.2为当前列赋值
        cell1.SetCellValue(cla.Cid);

        //创建第二列
        HSSFCell cell2 = row.CreateCell(1);
        cell2.SetCellValue(cla.Cname);

        //创建第三列
        HSSFCell cell3 = row.CreateCell(2);
        cell3.SetCellValue(cla.Ccount);

        //创建第四列
        HSSFCell cell4 = row.CreateCell(3);
        cell4.SetCellValue(cla.Cimg);

        //创建第五列
        HSSFCell cell5 = row.CreateCell(4);
        cell5.SetCellValue(cla.Cisdel);

        //创建第六列
        HSSFCell cell6 = row.CreateCell(5);
        cell6.SetCellValue(cla.CaddTime.ToString());
    }
    #endregion
    //将生成的工作薄写入物理文件
    using (FileStream fs = new FileStream(txtOutPut.Text.Trim(), FileMode.Create))
    {
        workbook.Write(fs);
        MessageBox.Show("ok");
    }
} 

//点击按钮，将Excel中数据导入到实体集合，最后绑定到表格控件上。
private void btnInput_Click(object sender, EventArgs e)
{
     5     List<MODEL.Classes> lists = new List<MODEL.Classes>();
    using (FileStream fs = new FileStream(txtInput.Text.Trim(), FileMode.Open))
    {
        //1.通过读取的数据创建工作薄。这个工作薄中当然有工作表
        HSSFWorkbook workbook = new HSSFWorkbook(fs);
        //2.从工作薄中取出工作表
        HSSFSheet sheet = workbook.GetSheet("classes");
        //3.遍历工作表的行
        for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
        {
            //可以取出一行数据
            HSSFRow row = sheet.GetRow(i);

            //每一行数据对应着一个对象
            MODEL.Classes cla = new MODEL.Classes();
            //取出第1列的单元格，通过强类型的方法获取这个单元中的值，将值 转换为double类型
            cla.Cid = (int)(row.GetCell(0).NumericCellValue);
            cla.Cname = row.GetCell(1).StringCellValue;
            cla.Ccount = (int)(row.GetCell(2).NumericCellValue);
            cla.Cimg = row.GetCell(3).StringCellValue;
            cla.Cisdel = row.GetCell(4).BooleanCellValue;
            cla.CaddTime = Convert.ToDateTime(row.GetCell(5).StringCellValue);
            lists.Add(cla);
        }
        this.dgvClassList.DataSource = lists;
    }
}

又如下例子：将DataTable写到Excel文件

public static void WriteExcel(DataTable dt, string filePath)
{
    if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
    {
        NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();//工作簿
        NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);//页

        NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);//先创建第一行，将列名写进去
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
        }
        
        //再循环之后的每一行
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {//循环每一个单元格
                row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));//都以字符串格式写入单元格
            }
        }
        //将生成的工作薄写入物理文件
        using (FileStream fs = new FileStream(filePath, FileMode.Create))
        {
            book.Write(fs);
            MessageBox.Show("ok");
        }
       
    }
}

 

2、EEPlus基本使用代码示例（注意索引都以1开始，比如Excel表格第一个单元格是[1,1]）

//从Excel文件读取数据
ICollection<Student> students = new List<Student>();
using (FileStream stream = File.OpenRead(fileName, FileMode.Open))
{
    ExcelPackage package = new ExcelPackage(stream);

    ExcelWorksheet sheet = package.Workbook.Worksheets[1];
    
    if (sheet == null)
    {//如果不存在指定sheet，报错
        MessageBox.Show("Excel format error!");
        return;
    }
    if (!sheet.Cells[1, 1].Value.Equals("Name") ||
         !sheet.Cells[1, 2].Value.Equals("Age") ||
         !sheet.Cells[1, 3].Value.Equals("Gender") ||
         !sheet.Cells[1, 4].Value.Equals("English Score") ||
         !sheet.Cells[1, 5].Value.Equals("Math Score"))
    {//如果不存在指定列名，报错
        MessageBox.Show("Excel format error!");
        return;
    }
   
    int lastRow = sheet.Dimension.End.Row;//最后一行（但有可能被用户按delete键删掉了单元格内容，）
    while (sheet.Cells[lastRow, 1].Value == null)
    {//通过循环，找到真正有内容（首列）的最后一行。
        lastRow--;
    }

    for (int i = 2; i <= lastRow; i++)
    {
        students.Add(new Student
        {
            Name = sheet.Cells[i, 1].Value.ToString(),
            Age = int.Parse(sheet.Cells[i, 2].Value.ToString()),
            Gender = (Gender)Enum.Parse(typeof(Gender), sheet.Cells[i, 3].Value.ToString()),
            EnglishScore = int.Parse(sheet.Cells[i, 4].Value.ToString()),
            MathScore = int.Parse(sheet.Cells[i, 5].Value.ToString())

        });
    }
}

 

using (FileStream stream = new FileStream(fileName, FileMode.Create))
{
    ExcelPackage package = new ExcelPackage(stream);

    package.Workbook.Worksheets.Add("Students");
    ExcelWorksheet sheet = package.Workbook.Worksheets[1];

    #region write header
    sheet.Cells[1, 1].Value = "Name";
    sheet.Cells[1, 2].Value = "Age";
    sheet.Cells[1, 3].Value = "Gender";
    sheet.Cells[1, 4].Value = "English Score";
    sheet.Cells[1, 5].Value = "Math Score";
    sheet.Cells[1, 6].Value = "Average Score";

    using (ExcelRange range = sheet.Cells[1, 1, 1, 6])
    {//设置列头的风格
        range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//实线
        range.Style.Fill.BackgroundColor.SetColor(Color.Gray);//底色
        range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thick;//边框线为细线
        range.Style.Border.Bottom.Color.SetColor(Color.Black);//下边框颜色
        range.AutoFitColumns(4);//设置列宽
    }
    #endregion

    #region write content
    int pos = 2;
    IEnumerable students = dataGrid.ItemsSource;//从控件上获取实体对象集合
    foreach (Student s in students)
    {
        sheet.Cells[pos, 1].Value = s.Name;
        sheet.Cells[pos, 2].Value = s.Age;
        sheet.Cells[pos, 3].Value = s.Gender;
        sheet.Cells[pos, 4].Value = s.EnglishScore;
        sheet.Cells[pos, 5].Value = s.MathScore;
        sheet.Cells[pos, 6].FormulaR1C1 = "AVERAGE(RC[-1], RC[-2])";//还可以设置公式。让Excel自动求平均值。棒棒哒

        if (s.MathScore > 90 && s.EnglishScore > 90)
        {
            using (ExcelRange range = sheet.Cells[pos, 1, pos, 6])
            {
                range.Style.Font.Color.SetColor(Color.Blue);//还可以根据实体属性值，设置字体颜色
            }
        }
        else if (s.MathScore < 80 && s.EnglishScore < 80)
        {
            using (ExcelRange range = sheet.Cells[pos, 1, pos, 6])
            {
                range.Style.Font.Color.SetColor(Color.Red);
            }
        }

        using (ExcelRange range = sheet.Cells[pos, 1, pos, 6])
        {
            range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            range.Style.Border.Bottom.Color.SetColor(Color.Black);
            range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;//设置区域左对齐
        }

        pos++;
    }
    #endregion

    package.Save();
}

 

 

 

3、Excel文件操作注意事项

（1）电话号码写入

Excel中保存电话号码需要注意加’，Excel中会把看起来像数字的都保存成数字，加上’以后就强制保存为字符串文本。