C_dataGridview导出excel

作者：张鹏22_981 | 来源：互联网 | 2023-10-16 11:02

C#DataGridView控件中数据导出到Excel方法一：usingSystem;usingSystem.Collections.Generic;usingSys

C# DataGridView控件中数据导出到Excel
方法一&＃xff1a;

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.Reflection;
using Microsoft.Office.Interop.Excel;

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

private void button1_Click(object sender, EventArgs e)
{

_DataGridView &＃61; this.dataGridView1;
ExportToExcel("www");

}

        private DataGridView _DataGridView;
        private string _FileName &＃61; "";

/ 构造函数
/

        / 拥用者窗体
        / DataGridView对象
            public Form1(Form ownerForm, DataGridView dataGridView)
            {

            _DataGridView &＃61; dataGridView;
            }

///

/// 导出到Excel
///

        /// 文件名,不需要带扩展名
        public void ExportToExcel(string pfileName)
        {
            _FileName &＃61; pfileName &＃61;&＃61; null ? "未命名" : pfileName.Trim();

            string fileName &＃61; ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls");
            if (fileName !&＃61; "")
            {
                try
                {
                    ExportTo(fileName);
                    OpenFile(fileName);
                }
                catch (System.Exception err)
                {
                    MessageBox.Show(err.Message);
                }
            }
        }

///

/// 导出到
///

        /// 文件名
        private void ExportTo(string fileName)
        {

            //
            //获取指定文件是否存在
            bool isExist &＃61; System.IO.File.Exists(fileName);
            //
            //定义一个缺少的object对象
            object oMis &＃61; System.Reflection.Missing.Value;

            //
            //定义一个Excel区域对象,用于保存选择的区域
            Microsoft.Office.Interop.Excel.Range selectRange;
            //
            //声明一Excel Application 对象
            Microsoft.Office.Interop.Excel.ApplicationClass App &＃61; null;
            //
            //声明一Excel Workbook 对象
            Microsoft.Office.Interop.Excel.Workbook wb &＃61; null;
            //
            //声明一Excel Worksheet 对象
            Microsoft.Office.Interop.Excel.Worksheet ws &＃61; null;

            //
            //将当前水标状态保存到临时变量中后将光标置为忙状态
            Cursor currentCursor &＃61; Cursor.Current;
            Cursor.Current &＃61; Cursors.WaitCursor;

            try
            {
                App &＃61; new Microsoft.Office.Interop.Excel.ApplicationClass();

                //
                //判断指定的文件是否存在
                if (isExist)
                {
                    //
                    //打开已存在的工作薄 Workbook
                    wb &＃61; App.Workbooks.Open(fileName, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis);
                    //新建工作表
                    ws &＃61; (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(oMis, oMis, 1, oMis);
                    //指定工作表名
                    ws.Name &＃61; this.Text &＃43; wb.Sheets.Count; //加 wb.Sheets.Count 防止重名
                }
                else
                {
                    //
                    //增加一工作薄 Workbook
                    wb &＃61; App.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                    //新建工作薄后默认有一个工作表,取得第一个工作表
                    ws &＃61; (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
                    //指定工作表名
                    ws.Name &＃61; this.Text;
                }

//
//增加一工作表 Worksheet

                //
                //声明两个变量记录Excle当前操作的行号与列号
                int rown &＃61; 0;
                int coln &＃61; 0;

                //
                //声明一变量用于记录当前DatagridView的总行数
                int colCount &＃61; _DataGridView.Columns.Count;

_DataGridView.SuspendLayout();

                //
                //将DataGirdView列头写入Excel中
                foreach (DataGridViewColumn dgvc in _DataGridView.Columns)
                {
                    selectRange &＃61; ws.get_Range(ws.Cells[rown &＃43; 1, &＃43;&＃43;coln], ws.Cells[rown &＃43; 1, coln]);
                    selectRange.Columns.ColumnWidth &＃61; dgvc.Width / 10;
                    selectRange.Interior.ColorIndex &＃61; 16;
                    selectRange.Interior.Pattern &＃61; Microsoft.Office.Interop.Excel.Constants.xlSolid;
                    selectRange.Font.ColorIndex &＃61; 2;
                    selectRange.HorizontalAlignment &＃61; Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    selectRange.VerticalAlignment &＃61; Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    selectRange.set_Item(1, 1, dgvc.HeaderText);
                }

                //
                //行号增加一
                rown&＃43;&＃43;;

                //
                //将DataGridView中所有的行数写入Excel中
                foreach (DataGridViewRow dgvr in _DataGridView.Rows)
                {
                    for (int i &＃61; 0; i                     {
                        selectRange &＃61; ws.get_Range(ws.Cells[rown &＃43; 1, i &＃43; 1], ws.Cells[rown &＃43; 1, i &＃43; 1]);
                        selectRange.set_Item(1, 1, dgvr.Cells[i].Value);
                    }
                    rown&＃43;&＃43;;
                }

                //
                //写入完成后将有数据的范围内设置其边框与内部线条
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight &＃61; 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight &＃61; 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight &＃61; 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight &＃61; 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight &＃61; 2;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight &＃61; 2;

                //
                //设置Wookbook为已保存状态
                wb.Saved &＃61; true;

                if (isExist)
                {
                    wb.Save();
                }
                else
                {
                    //
                    //将当前工作薄保存为指定的文件名
                    wb.SaveCopyAs(fileName);
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {

_DataGridView.ResumeLayout();

                wb.Close(false, null, null);
                //
                //退出Excel程序
                App.Quit();

                //
                //将当前光标更改回原来的状态
                Cursor.Current &＃61; currentCursor;
            }

}
///

/// 打开文件
///

        /// 文件名
        private void OpenFile(string fileName)
        {
            if (MessageBox.Show("你想打开这个文件吗?", "导出到...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) &＃61;&＃61; DialogResult.Yes)
            {
                try
                {
                    System.Diagnostics.Process process &＃61; new System.Diagnostics.Process();
                    process.StartInfo.FileName &＃61; fileName;
                    process.StartInfo.Verb &＃61; "Open";
                    process.StartInfo.WindowStyle &＃61; System.Diagnostics.ProcessWindowStyle.Normal;
                    process.Start();
                }
                catch
                {
                    MessageBox.Show(this, "你的计算机中未安装Excel,不能打开该文档!" , this.ProductName
                        , MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

///

/// 显示保存文件对话框,并返回选择的文件路径
///

        /// 对话框的标题
        /// 过滤器
        /// 选择的文件路径
        private string ShowSaveFileDialog(string title, string filter)
        {
            SaveFileDialog dlg &＃61; new SaveFileDialog();
            string name &＃61; _FileName;
            int n &＃61; name.LastIndexOf(".") &＃43; 1;
            if (n > 0) name &＃61; name.Substring(n, name.Length - n);
            dlg.Title &＃61; "导出到" &＃43; title;
            dlg.FileName &＃61; name;
            dlg.Filter &＃61; filter;
            if (dlg.ShowDialog() &＃61;&＃61; DialogResult.OK) return dlg.FileName;
            return "";
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“testDataSet2.chengji”中。您可以根据需要移动或移除它。
            this.chengjiTableAdapter.Fill(this.testDataSet2.chengji);

}
}
}

方法二&＃xff1a;

#region DataGridView数据显示到Excel
///

/// 打开Excel并将DataGridView控件中数据导出到Excel
///

/// DataGridView对象
/// 是否显示Excel界面
///
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel&＃61;Microsoft.Office.Interop.Excel;
///
///
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
    if (dgv.Rows.Count &＃61;&＃61; 0)
        return false;
    //建立Excel对象
    Excel.Application excel &＃61; new Excel.Application();
    excel.Application.Workbooks.Add(true);
    excel.Visible &＃61; isShowExcle;
    //生成字段名称
    for (int i &＃61; 0; i     {
        excel.Cells[1, i &＃43; 1] &＃61; dgv.Columns[i].HeaderText;
    }
    //填充数据
    for (int i &＃61; 0; i     {
        for (int j &＃61; 0; j         {
            if (dgv[j, i].ValueType &＃61;&＃61; typeof(string))
            {
                excel.Cells[i &＃43; 2, j &＃43; 1] &＃61; "&＃39;" &＃43; dgv[j, i].Value.ToString();
            }
            else
            {
                excel.Cells[i &＃43; 2, j &＃43; 1] &＃61; dgv[j, i].Value.ToString();
            }
        }
    }
    return true;
}
#endregion

方法三&＃xff1a;

#region DateGridView导出到csv格式的Excel
///

/// 常用方法&＃xff0c;列之间加\t&＃xff0c;一行一行输出&＃xff0c;此文件其实是csv文件&＃xff0c;不过默认可以当成Excel打开。
///

///
/// using System.IO;
///
///
private void DataGridViewToExcel(DataGridView dgv)
{
    SaveFileDialog dlg &＃61; new SaveFileDialog();
    dlg.Filter &＃61; "Execl files (*.xls)|*.xls";
    dlg.FilterIndex &＃61; 0;
    dlg.RestoreDirectory &＃61; true;
    dlg.CreatePrompt &＃61; true;
    dlg.Title &＃61; "保存为Excel文件";

    if (dlg.ShowDialog() &＃61;&＃61; DialogResult.OK)
    {
        Stream myStream;
        myStream &＃61; dlg.OpenFile();
        StreamWriter sw &＃61; new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
        string columnTitle &＃61; "";
        try
        {
            //写入列标题
            for (int i &＃61; 0; i             {
                if (i > 0)
                {
                    columnTitle &＃43;&＃61; "\t";
                }
                columnTitle &＃43;&＃61; dgv.Columns[i].HeaderText;
            }
            sw.WriteLine(columnTitle);

            //写入列内容
            for (int j &＃61; 0; j             {
                string columnValue &＃61; "";
                for (int k &＃61; 0; k                 {
                    if (k > 0)
                    {
                        columnValue &＃43;&＃61; "\t";
                    }
                    if (dgv.Rows[j].Cells[k].Value &＃61;&＃61; null)
                        columnValue &＃43;&＃61; "";
                    else
                        columnValue &＃43;&＃61; dgv.Rows[j].Cells[k].Value.ToString().Trim();
                }
                sw.WriteLine(columnValue);
            }
            sw.Close();
            myStream.Close();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        }
        finally
        {
            sw.Close();
            myStream.Close();
        }
    }
}
#endregion

方法四&＃xff1a;

#region DataGridView导出到Excel&＃xff0c;有一定的判断性
///

///方法&＃xff0c;导出DataGridView中的数据到Excel文件
///

///
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel&＃61;Microsoft.Office.Interop.Excel;
/// using System.Reflection;
///
/// DataGridView
public static void DataGridViewToExcel(DataGridView dgv)
{

#region 验证可操作性

    //申明保存对话框
    SaveFileDialog dlg &＃61; new SaveFileDialog();
    //默然文件后缀
    dlg.DefaultExt &＃61; "xls ";
    //文件后缀列表
    dlg.Filter &＃61; "EXCEL文件(*.XLS)|*.xls ";
    //默然路径是系统当前路径
    dlg.InitialDirectory &＃61; Directory.GetCurrentDirectory();
    //打开保存对话框
    if (dlg.ShowDialog() &＃61;&＃61; DialogResult.Cancel) return;
    //返回文件路径
    string fileNameString &＃61; dlg.FileName;
    //验证strFileName是否为空或值无效
    if (fileNameString.Trim() &＃61;&＃61; " ")
    { return; }
    //定义表格内数据的行数和列数
    int rowscount &＃61; dgv.Rows.Count;
    int colscount &＃61; dgv.Columns.Count;
    //行数必须大于0
    if (rowscount <&＃61; 0)
    {
        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    //列数必须大于0
    if (colscount <&＃61; 0)
    {
        MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    //行数不可以大于65536
    if (rowscount > 65536)
    {
        MessageBox.Show("数据记录数太多(最多不能超过65536条)&＃xff0c;不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    //列数不可以大于255
    if (colscount > 255)
    {
        MessageBox.Show("数据记录行数太多&＃xff0c;不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    //验证以fileNameString命名的文件是否存在&＃xff0c;如果存在删除它
    FileInfo file &＃61; new FileInfo(fileNameString);
    if (file.Exists)
    {
        try
        {
            file.Delete();
        }
        catch (Exception error)
        {
            MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            return;
        }
    }
    #endregion
    Excel.Application objExcel &＃61; null;
    Excel.Workbook objWorkbook &＃61; null;
    Excel.Worksheet objsheet &＃61; null;
    try
    {
        //申明对象
        objExcel &＃61; new Microsoft.Office.Interop.Excel.Application();
        objWorkbook &＃61; objExcel.Workbooks.Add(Missing.Value);
        objsheet &＃61; (Excel.Worksheet)objWorkbook.ActiveSheet;
        //设置EXCEL不可见
        objExcel.Visible &＃61; false;

        //向Excel中写入表格的表头
        int displayColumnsCount &＃61; 1;
        for (int i &＃61; 0; i <&＃61; dgv.ColumnCount - 1; i&＃43;&＃43;)
        {
            if (dgv.Columns[i].Visible &＃61;&＃61; true)
            {
                objExcel.Cells[1, displayColumnsCount] &＃61; dgv.Columns[i].HeaderText.Trim();
                displayColumnsCount&＃43;&＃43;;
            }
        }
        //设置进度条
        //tempProgressBar.Refresh();
        //tempProgressBar.Visible   &＃61;   true;
        //tempProgressBar.Minimum&＃61;1;
        //tempProgressBar.Maximum&＃61;dgv.RowCount;
        //tempProgressBar.Step&＃61;1;
        //向Excel中逐行逐列写入表格中的数据
        for (int row &＃61; 0; row <&＃61; dgv.RowCount - 1; row&＃43;&＃43;)
        {
            //tempProgressBar.PerformStep();

            displayColumnsCount &＃61; 1;
            for (int col &＃61; 0; col             {
                if (dgv.Columns[col].Visible &＃61;&＃61; true)
                {
                    try
                    {
                        objExcel.Cells[row &＃43; 2, displayColumnsCount] &＃61; dgv.Rows[row].Cells[col].Value.ToString().Trim();
                        displayColumnsCount&＃43;&＃43;;
                    }
                    catch (Exception)
                    {

}

                }
            }
        }
        //隐藏进度条
        //tempProgressBar.Visible   &＃61;   false;
        //保存文件
        objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
    }
    catch (Exception error)
    {
        MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }
    finally
    {
        //关闭Excel应用
        if (objWorkbook !&＃61; null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
        if (objExcel.Workbooks !&＃61; null) objExcel.Workbooks.Close();
        if (objExcel !&＃61; null) objExcel.Quit();

        objsheet &＃61; null;
        objWorkbook &＃61; null;
        objExcel &＃61; null;
    }
    MessageBox.Show(fileNameString &＃43; "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

#endregion
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
&＃61;

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
1using Excel &＃61; Microsoft.Office.Interop.Excel;方法
1将DataGridView控件中数据导出到Excel#region 将DataGridView控件中数据导出到Excel
2 /**

3 /// 将DataGridView控件中数据导出到Excel
4 ///

5        /// DataGridView对象
6        /// 是否显示Excel界面
7        ///
8        public bool ExportDataGridview(DataGridView gridView,bool isShowExcle)
9        {
10            if (gridView.Rows.Count &＃61;&＃61; 0)
11                return false;
12            //建立Excel对象
13            Excel.Application excel &＃61; new Excel.Application();
14            excel.Application.Workbooks.Add(true);
15            excel.Visible &＃61; isShowExcle;
16            //生成字段名称
17            for (int i &＃61; 0; i 18            {
19                excel.Cells[1, i &＃43; 1] &＃61; gridView.Columns[i].HeaderText;
20            }
21            //填充数据
22            for (int i &＃61; 0; i 23            {
24                for (int j &＃61; 0; j 25                {
26                    if (gridView[j, i].ValueType &＃61;&＃61; typeof(string))
27                    {
28                        excel.Cells[i &＃43; 2, j &＃43; 1] &＃61; "&＃39;" &＃43; gridView[j, i].Value.ToString();
29                    }
30                    else
31                    {
32                        excel.Cells[i &＃43; 2, j &＃43; 1] &＃61; gridView[j, i].Value.ToString();
33                    }
34                }
35            }
36            return true;
37        }
38        #endregion调用 1 private void btnExcel_Click(object sender,EventArgs e)

{

if (!oper.ExportDataGridview(dgvEquiment, true))
2 MessageBox.Show("表格中没有数据&＃xff0c;无法导出数据&＃xff01;", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}