NPOI 将DataGridView导出到Excel

导出为xls格式用HSSF，xlsx用XSSF。

1、类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;

public class ExportExcel
{
    public void GridToExcel(string fileName, DataGridView dgv)
    {
        if (dgv.Rows.Count == 0)
        {
            return;
        }
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "Excel 2003格式|*.xls";
        sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
        if (sfd.ShowDialog() != DialogResult.OK)
        {
            return;
        }
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(fileName);
        HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
        for (int i = 0; i < dgv.Columns.Count; i++)
        {
            HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String);
            headCell.SetCellValue(dgv.Columns[i].HeaderText);
        }
        for (int i = 0; i < dgv.Rows.Count; i++)
        {
            HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1);
            for (int j = 0; j < dgv.Columns.Count; j++)
            {
                HSSFCell cell = (HSSFCell)row.CreateCell(j);
                if (dgv.Rows[i].Cells[j].Value == null)
                {
                    cell.SetCellType(CellType.Blank);
                }
                else
                {
                    if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
                    {
                        cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value));
                    }
                    else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
                    {
                        cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
                    }
                    else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
                    {
                        cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value));
                    }
                    else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
                    {
                        cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
                    }
                    else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
                    {
                        cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
                    }
                    else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
                    {
                        cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
                    }
                }

            }

        }
        for (int i = 0; i < dgv.Columns.Count; i++)
        {
            sheet.AutoSizeColumn(i);
        }
        using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
        {
            wb.Write(fs);
        }
        MessageBox.Show("导出成功！","导出提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
    } 
}

2、调用

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace NPOITest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnDataSource_Click(object sender, EventArgs e)
        {
            MyDBase DB = new MyDBase(".","GoodLuck","sa","123456");
            DataSet DS = DB.GetRecordset("select * from View_s");
            dataGridView1.DataSource = DS.Tables[0];
            DB.DBClose();
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            ExportExcel Et = new ExportExcel();
            Et.GridToExcel("人员单位", dataGridView1);
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}