一：例子截图：
二：NPOI截图
三：EmployeeListWindow.cs代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using HRMSys.DAL;
using HRMSys.Model;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO; namespace HYMSys.UI.EmployeeMgr
{
public partial class EmployeeListWindow : Form
{
public EmployeeListWindow()
{
InitializeComponent();
}
/// <summary>
/// 加载所有信息
/// </summary>
public EmployeeList[] load()
{
EmployeeDAL de = new EmployeeDAL(); return de.ListAll(); } /// <summary>
/// 添加员工
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolsb_add_Click(object sender, EventArgs e)
{
EmployeeEditWindow edit = new EmployeeEditWindow();
edit.IsAdd = true;
edit.ShowDialog(); if (edit.IsLoad == true)
{
dataGridView1.DataSource = load();
} }
/// <summary>
/// 删除员工
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolsb_delete_Click(object sender, EventArgs e)
{
Guid id=(Guid)dataGridView1.CurrentRow.Cells[].Value;
string name=(string)dataGridView1.CurrentRow.Cells[].Value;//这里的cell是相对于list员工操作窗口上name这列,即使第二行
if (MessageBox.Show("真的要删除---"+name+"---吗?","警告!",MessageBoxButtons.OKCancel)==DialogResult.OK)
{
EmployeeDAL dal = new EmployeeDAL();
dal.DeleteById(id);
dataGridView1.DataSource = load();
} }
/// <summary>
/// 修改员工信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void toolsb_edit_Click(object sender, EventArgs e)
{
EmployeeEditWindow edit = new EmployeeEditWindow();
edit.EditId = (Guid)dataGridView1.CurrentRow.Cells[].Value;
edit.IsAdd = false;
edit.ShowDialog();
if (edit.IsLoad == true)
{
dataGridView1.DataSource = load();
} }
/// <summary>
/// 自动加载所有员工信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void EmployeeListWindow_Load(object sender, EventArgs e)
{
DepartmentDAL dal = new DepartmentDAL();
cb_depart.DataSource = dal.ListAll();
cb_depart.DisplayMember = "Name";
cb_depart.ValueMember = "Id";
//cb_depart.SelectedValue= dtp_indate.Value = DateTime.Today.AddMonths(-);
dtp_enddate.Value = DateTime.Today; dataGridView1.DataSource= load(); }
/// <summary>
/// 综合查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
List<string> wherelist = new List<string>();
List<SqlParameter> parameter = new List<SqlParameter>();
if (ckb_name.Checked == true)
{
wherelist.Add("Name=@Name");
parameter.Add(new SqlParameter("@Name",tb_name.Text)); }
if (ckb_indate.Checked == true)
{
wherelist.Add("InDate>=@InDateStart and InDate<=@InDateEnd");
parameter.Add(new SqlParameter("@InDateStart",dtp_indate.Value));
parameter.Add(new SqlParameter("@InDateEnd",dtp_enddate.Value));
}
if (ckb_depart.Checked == true)
{
wherelist.Add("DepartmentId=@DepartmentId");
parameter.Add(new SqlParameter("@DepartmentId",cb_depart.SelectedValue));
} string whereSql=string.Join(" and ",wherelist);
string Sql="select * from T_Employee";
if (whereSql.Length > )
{
Sql = Sql + " where " + whereSql;
}
EmployeeDAL dal = new EmployeeDAL();
dataGridView1.DataSource= dal.SearchBySome(Sql, parameter); }
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsb_outputExcel_Click(object sender, EventArgs e)
{
SaveFileDialog saFi = new SaveFileDialog();//打开保存对话框
saFi.Filter = "Excel文件|*.xls";//设置文件类型
if (saFi.ShowDialog() != DialogResult.OK)//如果未打开对话框,返回
{
return;
}
string path = saFi.FileName;//得到文件的路径
HSSFWorkbook workbook = new HSSFWorkbook();//创建一个Excel文档
ISheet sheet = workbook.CreateSheet("员工操作");//创建一个Excel的页 IRow rowheader = sheet.CreateRow();//创建一个行,作为行头号
rowheader.CreateCell(, CellType.STRING).SetCellValue("姓名");//第一行第一列显示姓名
rowheader.CreateCell(, CellType.STRING).SetCellValue("工号");
rowheader.CreateCell(, CellType.STRING).SetCellValue("入职日期"); EmployeeList[] list = (EmployeeList[])dataGridView1.DataSource;//得到datagridview的数据源,Excel的写入文件
for (int i = ; i < list.Length; i++)//将写入的数据与Excel的单元格对应
{
EmployeeList li = list[i];//得到数据组的单条信息
IRow row = sheet.CreateRow(i + );//创建一行
row.CreateCell(, CellType.STRING).SetCellValue(li.Name);//将这行的第一列填上姓名
row.CreateCell(, CellType.STRING).SetCellValue(li.Number); //存入date这种格式的转换
ICellStyle datestyle = workbook.CreateCellStyle();//创建一个单元格样式
IDataFormat dateformamt = workbook.CreateDataFormat();//创建一个数据的固定格式 datestyle.DataFormat = dateformamt.GetFormat("yyyy\"年\"m\"月\"d\"日\"");//日期的样式采用这种数据格式
//存日期的单元格指定他的格式
ICell indate = row.CreateCell(, CellType.NUMERIC);
indate.CellStyle = datestyle; indate.SetCellValue(li.InDate);//给存日期的单元指定数据 }
//Excel写入数据流
using(Stream stream=File.OpenWrite(path))//创建一个写入流
{
workbook.Write(stream);//Excel写入数据流
} }
}
}
四：通过microsoft.visual basic.dll来导出
a.添加引用
b.调出excel,由于这个方法每次运行都会开一个进程，太占资源，没上面方法好用，这里不再做过多的介绍
c.
