在Winform中使用NPOI第三方组件导出Excel

最近使用NPOI导出Datagridview数据。。。。。。

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 System.Data.SqlClient;using DLZY;using NPOI;using NPOI.POIFS;using NPOI.HSSF;using NPOI.HPSF;using NPOI.Util;using NPOI.HSSF.UserModel;using NPOI.POIFS.FileSystem;using System.IO;using NPOI.SS.UserModel;using Model;using DAL;namespace 收入查询{    public partial class Form2 : Form    {        public Form2()        {            InitializeComponent();        }        private void Form2_Load(object sender, EventArgs e)        {        }        private void Bind()        {            SqlConnection conn = SqlHelper.Conn(); //连接数据库            //string sql = string.Format("select * from Income_Note where IncomeType ='{0}' and  IncomeTime   between '{1}' and '{2}'", srtype.Text, Convert.ToDateTime(kstime.Text), Convert.ToDateTime(jstime.Text));            string sql = "select * from Income_Note where IncomeType =@IncomeType and  IncomeTime  between  @Start  and @End ";            SqlDataAdapter ad = new SqlDataAdapter(sql, conn); //提交查询命令            ad.SelectCommand.Parameters.AddWithValue("@IncomeType", srtype.Text);            ad.SelectCommand.Parameters.AddWithValue("@Start", Convert.ToDateTime(kstime.Text));            ad.SelectCommand.Parameters.AddWithValue("@End", Convert.ToDateTime(jstime.Text));            DataTable dt = new DataTable(); //新建表            ad.Fill(dt);            srdata.DataSource = dt;// 显示收入查询返回的dt表结果到。。。        }        private void button1_Click(object sender, EventArgs e)        {            if (srtype.Text.Trim() != string.Empty)            {                Bind();            }            else            {                MessageBox.Show("信息有误，请输入正确的的值！");            }        }        private void button3_Click(object sender, EventArgs e)        {             Form3 frm3= new Form3();            frm3.ShowDialog();        }        private void button4_Click(object sender, EventArgs e)        {            if (srdata.SelectedRows.Count == 0)            {                MessageBox.Show("没有选择有效行");                return;            }            else if (MessageBox.Show("你确定要删除吗？", "警告", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)            {                                    SqlConnection conn = SqlHelper.Conn();                    conn.Open();                    string sql = "delete from Income_Note where IncomeId=@id";                    SqlCommand comm = new SqlCommand(sql, conn);                    comm.Parameters.AddWithValue("id", srdata.SelectedRows[0].Cells["colid"].Value.ToString());                    comm.ExecuteNonQuery();                    conn.Close();                    Bind();                    MessageBox.Show("删除成功");            }            else            {                return;            }                  }        private void button2_Click(object sender, EventArgs e)        {            this.Close();        }        private void btnExport_Click(object sender, EventArgs e)        {            //ExportTOExcel(srdata);            SaveFileDialog sdfexport = new SaveFileDialog();            sdfexport.Filter = "Excel文件|*.xls";            if (sdfexport.ShowDialog() == DialogResult.No)            {                return;            }            string filename = sdfexport.FileName;            HSSFWorkbook workbook = new HSSFWorkbook();            ISheet sheet = workbook.CreateSheet("支出数据");            IRow rowHeader = sheet.CreateRow(0);            rowHeader.CreateCell(0, CellType.STRING).SetCellValue("ID");            rowHeader.CreateCell(1, CellType.STRING).SetCellValue("用户姓名");            rowHeader.CreateCell(2, CellType.STRING).SetCellValue("收入方式");            rowHeader.CreateCell(3, CellType.STRING).SetCellValue("收入时间");            rowHeader.CreateCell(4, CellType.STRING).SetCellValue("收入金额");            rowHeader.CreateCell(5, CellType.STRING).SetCellValue("备注");            sheet.SetColumnWidth(3, 20 * 256);                                  string sql = "select * from Income_Note where IncomeType =@IncomeType and  IncomeTime  between  @Start  and @End ";            List<SqlParameter> paramsList = new List<SqlParameter>();            paramsList.Add(new SqlParameter("@IncomeType", srtype.Text));            paramsList.Add(new SqlParameter("@Start", Convert.ToDateTime(kstime.Text)));            paramsList.Add(new SqlParameter("@End", Convert.ToDateTime(jstime.Text)));            Income_Note[] incomenotes = new IncomeNoteDAL().Search(sql, paramsList);                       for (int i = 0; i < incomenotes.Length; i++)            {                Income_Note incomenote = incomenotes[i];                IRow row = sheet.CreateRow(i + 1);                row.CreateCell(0, CellType.NUMERIC).SetCellValue(incomenote.IncomeId);                row.CreateCell(1, CellType.STRING).SetCellValue(incomenote.UserName);                row.CreateCell(2, CellType.STRING).SetCellValue(incomenote.IncomeType);                ICellStyle styledate = workbook.CreateCellStyle();                IDataFormat format = workbook.CreateDataFormat();                styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");                                ICell cellDate = row.CreateCell(3, CellType.NUMERIC);                                cellDate.CellStyle = styledate;                cellDate.SetCellValue(incomenote.IncomeTime);                               HSSFCellStyle cellStyle2 = (HSSFCellStyle)workbook.CreateCellStyle();                 HSSFDataFormat format2 = (HSSFDataFormat)workbook.CreateDataFormat();                 //cellStyle2.DataFormat = format2.GetFormat("¥#,##0");                cellStyle2.DataFormat = format2.GetFormat("￥#,##0;￥-#,##0");                                HSSFCell cellMoney = (HSSFCell)row.CreateCell(4, CellType.NUMERIC);                cellMoney.CellStyle = cellStyle2;                cellMoney.SetCellValue(incomenote.IncomeMoney.ToString());                                row.CreateCell(5, CellType.STRING).SetCellValue(incomenote.IncomeNote);            }            using (Stream stream = File.OpenWrite(filename))            {                workbook.Write(stream);            }        }    }}

截图如下所示:

导出xsl如下: