C#中将DataGridView中查询出来的数据使用DataTable导出为Excel表格(通过NPOI)

1.首先在当前项目中添加引用NPOI.dll

2.在项目中添加类ExportExcel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Collections;
using System.Data;
using System.Windows.Forms;
using NPOI;
using NPOI.Util;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Util;
using NPOI.SS.UserModel;


namespace 测试
{
    public class ExportExcel
    {
        static IWorkbook hssfworkbook;
        #region 导出Excel
        /// <summary>
        /// 使用DataTable导出数据
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="FileName">文件名</param>
        /// <param name="msg">提示消息</param>
        public static void DtToExcel(DataTable dt, string FileName)
        {
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    SaveFileDialog dlg = new SaveFileDialog();//申明保存对话框
                    dlg.Filter = "Excel文件|*.xls";
                    dlg.FileName = FileName;//默认文件名称
                    if (dlg.ShowDialog() == DialogResult.Cancel)
                        return;
                    dlg.InitialDirectory = Directory.GetCurrentDirectory();//返回文件路径
                    string filename = dlg.FileName;//输出的文件名称
                    if (filename.Trim() == " ")//验证strFileName是否为空或值无效
                    { return; }
                    hssfworkbook = new HSSFWorkbook();
                    ISheet sheet = hssfworkbook.CreateSheet("Sheet1");

                    int colscount = dt.Columns.Count;//定义表格内数据的列数               
                    ArrayList arr = new ArrayList();//储存头信息
                    ICellStyle cstyle = hssfworkbook.CreateCellStyle();

                    IRow rowHeader = sheet.CreateRow(0);//创建列头数据
                    int displayColumnsCount = 0;//用于处理隐藏列头不被显示         

                    HSSFFont hssffont = hssfworkbook.CreateFont() as HSSFFont;
                    hssffont.FontHeightInPoints = 11;

                    //设置单元格边框 
                    for (int i = 0; i < dt.Columns.Count; i++)//生成表头信息
                    {
                        ICell cell = rowHeader.CreateCell(displayColumnsCount);
                        cell.SetCellValue(dt.Columns[i].ToString());
                        sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
                        displayColumnsCount++;
                        arr.Add(dt.Columns[i].ToString());
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow irow = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count - 1; j++)
                        {
                            ICell cell = irow.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                    using (Stream stream = File.OpenWrite(filename))
                    {
                        hssfworkbook.Write(stream);
                        MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                else
                {
                    MessageBox.Show("没有数据可供导出", "提示");
                }
            }
            catch
            {
                MessageBox.Show("操作失败", "提示");
            }
        }
        #endregion

    }
}

3.添加一个button按钮btExport,添加事件btExport_Click

private void btExport_Click(object sender, EventArgs e)
        {
            if (this.dgvdata.Rows.Count == 0)
            {
                MessageBox.Show("未能查找到数据!!!","提示");
                return;
            }

            ExportExcel.DtToExcel(dt, "默认文件名");
        }

4.调试程序,点击button按钮,可以导出数据


NPOI下载地址:

没有更多推荐了,返回首页