将DataGridView控件中的数据导出到Excel

最新推荐文章于 2020-03-29 17:37:59 发布

dz45693

最新推荐文章于 2020-03-29 17:37:59 发布

阅读量2.8k

点赞数

分类专栏： Office 文章标签： excel microsoft library exception object string

本文链接：https://blog.csdn.net/ma_jiang/article/details/4799951

版权

Office 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

将DataGridView控件中的数据导出到Excel需要引用Excel动态链接库(Microsoft Excel 9.0 Object Library),这样就可以通过C#灵活控制Excel。添加引用如下：
在开发环境的菜单栏中选择"项目|添加引用"，在弹出添加引用窗体需选择COM选项卡，然后选择"Microsoft Excel 9.0 Object Library"或相应版本组件名称，单击确定。主要代码如下：

     public void ExportDataGridView(DataGridView gridView)
        {
            SaveFileDialog sfile = new SaveFileDialog();
            sfile.AddExtension = true;
            sfile.DefaultExt = ".xls";
            sfile.Filter = "(*.xls)|*.xls";
            if (sfile.ShowDialog() == DialogResult.OK)
            {
                Excel.ApplicationClass ex = new Excel.ApplicationClass();
                Excel.WorkbookClass wb = (Excel.WorkbookClass)ex.Workbooks.Add(System.Reflection.Missing.Value);
                try
                {
                    int i = 0;
                    int j = 0;
                    int k = 0;

                    for (; j < gridView.ColumnCount; j++)
                    {

                        if (gridView.Columns[j].Width > 0)
                        {
                            ex.Cells[1, j + 1 - k] = gridView.Columns[j].HeaderText;
                            for (i = 0; i < gridView.RowCount; i++)
                            {
                                ex.Cells[i + 2, j + 1 - k] = Convert.ToString(gridView.Rows[i].Cells[j].Value);
                                //ex.Cells.ColumnWidth = 15;
                            }

                        }
                        else
                        {
                            k++;

                        }

                    }
                    wb.Saved = true;
                    ex.ActiveWorkbook.SaveCopyAs(sfile.FileName);
                    // ex.Quit();
                    MessageBox.Show("导出成功！", "友情提示");
                }
                catch (Exception) { }
                finally
                {
                    ex.Quit();
                }

            }
        }
        public bool ExportDataGridview(DataGridView gridView, bool isShowExcle)
        {
            if (gridView.Rows.Count == 0)
                return false;
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            //生成字段名称
            for (int i = 0; i < gridView.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < gridView.RowCount - 1; i++)
            {
                for (int j = 0; j < gridView.ColumnCount; j++)
                {
                    if (gridView[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + gridView[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }