C# DataGridView导出Excel示例

来源：互联网发布：mac怎么全屏浏览图片编辑：程序博客网时间：2024/05/05 15:19

首先添加引用，目前我机器上的Office版本是2010，根据版本不同选择的组件名称也会不同；

示例：

窗体代码：

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;namespace Export2Excel{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        private void Form1_Load(object sender, EventArgs e)        {            dataGridView1.Rows.Add("张三", 12);            dataGridView1.Rows.Add("李四", 9);        }        private void btnExport_Click(object sender, EventArgs e)        {          ExcelReport.Export(dataGridView1, "员工统计报表", "D:\\1.xlsx");        }    }}

ExcelReport.cs 代码:

using System.Windows.Forms;using System;using System.Diagnostics;using System.Threading;public struct ExportInfo{    public string Title;    public string ToPath;    public DataGridView DataSource;}public class ExcelReport{    #region Export    /// <summary>    /// 导出Excel    /// </summary>    /// <param name="dgv">表格</param>    /// <param name="title">报表标题</param>    /// <param name="toPath">导出文件地址</param>    public static void Export(DataGridView dgv,string title, string toPath)    {        ExportInfo ei;        ei.Title = title;        ei.ToPath = toPath;        ei.DataSource = dgv;        //此处用线程是防止导出大量数据出现程序无响应        Thread MyThread = new Thread(new ParameterizedThreadStart(MyExportThread));        MyThread.Start(ei);    }    #endregion    #region MyExportThread    private static void MyExportThread(object args)    {        ExportInfo ei = (ExportInfo)args;        //建立Excel应用(写Excel Sheet之前)        Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();        oExcel.UserControl = false;        Microsoft.Office.Interop.Excel.WorkbookClass wb = (Microsoft.Office.Interop.Excel.WorkbookClass)oExcel.Workbooks.Add(System.Reflection.Missing.Value);        try        {            //写入标题            oExcel.Cells[1, 4] = ei.Title;            Microsoft.Office.Interop.Excel.Range a = (Microsoft.Office.Interop.Excel.Range)oExcel.get_Range(oExcel.Cells[1, 4], oExcel.Cells[1, 4]);            a.Font.Bold = true;//字体粗体            a.Font.Size = 20;            //写入列标头            for (int x = 0; x < ei.DataSource.Columns.Count; x++)            {                oExcel.Cells[3, x + 1] = ei.DataSource.Columns[x].HeaderText;                Microsoft.Office.Interop.Excel.Range r = oExcel.get_Range(oExcel.Cells[3, x + 1], oExcel.Cells[3, x + 1]);                r.RowHeight = 20;                r.Font.Size = 9;                r.Font.ColorIndex = 2;                r.Interior.ColorIndex = 45;                r.Borders.LineStyle = 1;                r.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;            }            //写入数据            for (int i = 0; i < ei.DataSource.Rows.Count; i++)            {                for (int c = 0; c < ei.DataSource.Columns.Count; c++)                {                    oExcel.Cells[i + 4, c + 1] = ei.DataSource.Rows[i].Cells[c].Value;                }            }            //绘制边框,设置字体大小            Microsoft.Office.Interop.Excel.Range r1 = oExcel.get_Range(oExcel.Cells[4, 1], oExcel.Cells[ei.DataSource.Rows.Count + 3, ei.DataSource.Columns.Count]);            r1.Font.Size = 9;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlLineStyleNone;            r1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;            r1.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;            wb.Saved = true; //保存工作簿            MessageBox.Show("数据成功导出。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);        }        catch (Exception Ex)        {            MessageBox.Show("导出数据失败。\r\n异常:" + Ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);        }        finally        {            oExcel.ActiveWorkbook.SaveCopyAs(ei.ToPath);            oExcel.Quit();            System.GC.Collect();            foreach (Process p in Process.GetProcesses())            {                if (p.ProcessName == "EXCEL")                {                    p.Kill();                    break;                }            }        }    }    #endregion}

导出结果：

0 0