在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如下: