C# 把datagridview控件上的表格输出到excel文件

 

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Reflection;
using System.IO;

namespace 在datatable上进行数据操作
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            //SetStyle(ControlStyles.OptimizedDoubleBuffer, true);//设置双缓冲
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.eventbinding();//事件绑定
            this.initdatagirdview();//把数据库载入到datagridview控件
            this.toolStripStatusLabel1.Text = "共"+(this.dataGridView1.Rows.Count-1).ToString()+"行";//减去列头的一行
        }


        public void eventbinding()
        {
            this.dataGridView1.ContextMenuStrip = this.contextMenuStrip1;
            this.FormClosing+=Form1_FormClosing;
            toolStripProgressBar1.Alignment = ToolStripItemAlignment.Right;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            System.Environment.Exit(0);//保证结束所有相关的线程
        }

        public  void initdatagirdview()
        {
                    //连接数据库并把表读出
                    SqlConnection conn = new SqlConnection("server=MAGICIANLYX\\SQLEXPRESS;database=AdventureWorks;uid=magicianlyx;pwd=13113772339;");
                    string s = "select * from AdventureWorks.Person.vStateProvinceCountryRegion";
                    SqlDataAdapter sda = new SqlDataAdapter(s, conn);
                    DataSet ds = new DataSet();
                    conn.Open();
                    sda.Fill(ds);
                    conn.Close();

                    //把数据表和datagridview控件关联起来


                    DataTable dt = ds.Tables[0];
                    this.dataGridView1.DataSource = dt;
                    this.datagridview_resize();//重新设置每列的宽度，使其填满窗体
            
            
            /*数据库操作
                    //删除数据表第一行
                    dt.Rows.RemoveAt(0);
                    //删除数据表第二行第一列
                    dt.Rows[1][1] = 1;
                    //在数据表第2行前插入一行空白
                    DataRow dr = dt.NewRow();
                    dt.Rows.InsertAt(dr, 1);
             * */
        }

        //重新计算即时每列的宽度
        public void datagridview_resize()
        {
            this.dataGridView1.Refresh();
            int width = (this.dataGridView1.Width - this.dataGridView1.TopLeftHeaderCell.Size.Width) / this.dataGridView1.Columns.Count;
            foreach (DataGridViewColumn dc in dataGridView1.Columns)
            {
                dc.Width = width;
            }
        }


        //重绘窗口宽度改变
        protected override void OnPaint(PaintEventArgs e)
        {
            this.datagridview_resize();
            base.OnPaint(e);
        }


        /// <summary>
        /// 保存为excel
        /// </summary>
        private void  保存为excelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.saveexcel();
        }


        public async void saveexcel()
        {
            if (File.Exists(@"C:\Users\Administrator\Documents\111.xls"))//当路径下存在文件时，删除，保证无异常
            {
                File.Delete(@"C:\Users\Administrator\Documents\111.xls");
            }

            
            
//——————————————————————————————核心代码部分

            TimeSpan t1 = new TimeSpan(DateTime.Now.Ticks);//保留实时时间，后期要计算时间差
            this.toolStripProgressBar1.Maximum = this.dataGridView1.Rows.Count-1;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelworkbook = excel.Application.Workbooks.Add(true);
            excel.Visible = false;//是否显示excel文本

            
            try
            {
                //异步执行，保证前台窗口程序无卡顿
                await Task.Run(() =>
                {
                    for (int i = 0; i < this.dataGridView1.Columns.Count; i++)//复制表格的列头
                    {
                        excel.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;//excel对象的行列索引是从1开始的
                        //datagridview的行列索引是从0开始的
                    }

                    for (int i = 0; i < this.dataGridView1.Rows.Count-1; i++)//减去列头的一行
                    {
                        for (int j = 0; j < this.dataGridView1.Columns.Count; j++)
                        {
                            if(this.dataGridView1.Rows[i + 1].Cells[j].Value==null)
                            {
                                excel.Cells[i + 2, j + 1] ="'  ";//当表格的单元格为空时，保留行并跳过
                                break;
                            }
                            else if (this.dataGridView1.Rows[i + 1].Cells[j].ValueType == typeof(string))
                            {
                                excel.Cells[i + 2, j + 1] = "'" + this.dataGridView1.Rows[i + 1].Cells[j].Value.ToString();
                            }

                            else 
                            {
                                excel.Cells[i + 2, j + 1] = this.dataGridView1.Rows[i + 1].Cells[j].Value.ToString();
                            }

                        }

                       this.toolStripProgressBar1.Value++;//进度条前进


                    }
                });
            }
            catch (Exception ex)
            {
            }
            finally
            {
                //保存xls表格
                excelworkbook.SaveAs("111.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                //释放资源
               if(excelworkbook!=null) 
                   excelworkbook.Close(Missing.Value, Missing.Value, Missing.Value);
               if (excel != null)
               {
                   excel.Workbooks.Close();
                   excel.Quit();
               }


//——————————————————————后续代码非核心代码
                
                //计算时间差
                TimeSpan t2 = new TimeSpan(DateTime.Now.Ticks);
                TimeSpan ts = t1.Subtract(t2).Duration();
                string hours = ts.Hours.ToString(), minutes = ts.Minutes.ToString(), seconds = ts.Seconds.ToString();
                if (ts.Hours < 10)
                {
                    hours = "0" + ts.Hours.ToString();
                }
                if (ts.Minutes < 10)
                {
                    minutes = "0" + ts.Minutes.ToString();
                }
                if (ts.Seconds < 10)
                {
                    seconds = "0" + ts.Seconds.ToString();
                }

               if( MessageBox.Show("花费时间\n" + hours + ":" + minutes + ":" + seconds, "完成")==DialogResult.OK)
               {
                   this.toolStripProgressBar1.Value = 0;
               }
            }
                
            
        }





    }
}

 

不上示例文件了，毕竟数据库是我自己的

 

 

主要是两个注意的点

①Microsoft.Office.Interop.Excel.Application对象excel的行列索引是从1开始，和datagridview从0开始索引区别开来，
②如果datagirdview单元的值是string类型时，后面加个 ‘     



开发时需要的Microsoft.Office.Interop.Excel.dll组件http://pan.baidu.com/s/1jGrPHrS

转载于:https://www.cnblogs.com/magicianlyx/p/4824343.html