导出Excel文件，npoi方式和通过microsoft.visual basic.dll
一：例子截图：
二：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[0].Value; string name=(string)dataGridView1.CurrentRow.Cells[2].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[0].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(-1); 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 > 0) { 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(0);//创建一个行,作为行头号 rowheader.CreateCell(0, CellType.STRING).SetCellValue("姓名");//第一行第一列显示姓名 rowheader.CreateCell(1, CellType.STRING).SetCellValue("工号"); rowheader.CreateCell(2, CellType.STRING).SetCellValue("入职日期"); EmployeeList[] list = (EmployeeList[])dataGridView1.DataSource;//得到datagridview的数据源,Excel的写入文件 for (int i = 0; i < list.Length; i++)//将写入的数据与Excel的单元格对应 { EmployeeList li = list[i];//得到数据组的单条信息 IRow row = sheet.CreateRow(i + 1);//创建一行 row.CreateCell(0, CellType.STRING).SetCellValue(li.Name);//将这行的第一列填上姓名 row.CreateCell(1, 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(2, 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.
