DataGridView导出数据到EXCEL

最新推荐文章于 2025-05-09 14:47:18 发布

原创最新推荐文章于 2025-05-09 14:47:18 发布 · 75 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种将DataGridView中的数据导出到Excel的方法，并提供了完整的C#代码实现。该方法适用于.NET平台，能够处理多种数据类型，并确保空值被替换为NULL。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在项目中要用到将DATAGRIDVIEW中的数据导出到EXCEL，先贴出代码如下，

         /// <summary>
        /// 导出到EXCEL
        /// </summary>
        /// <param name="gridView">要导出的DATAGRIDVIEW </param>
        public static void SaveAsExcel(DataGridView gridView)
        {
            //导出到execl
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FileName = DateTime.Now.ToString("yyyyMMddhhmmss");
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                //saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                //saveFileDialog.ShowDialog();
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    string strName = saveFileDialog.FileName;
                    if (strName.Length != 0)
                    {
                        System.Reflection.Missing miss = System.Reflection.Missing.Value;
                        Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                        excel.Application.Workbooks.Add(true); ;
                        excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
                        if (excel == null)
                        {
                            MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                        Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                        Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                        sheet.Name = "test";

                        //int m = 0, n = 0;
                        //生成列名称   这里i是从1开始的因为我第0列是个隐藏列ID 没必要写进去
                        for (int i = 1; i < gridView.ColumnCount; i++)
                        {

excel.Cells[1, i] = gridView.Columns[i].HeaderText.ToString();

}

                        //填充数据
                        for (int i = 0; i < gridView.RowCount; i++)
                        {

                            //j也是从1开始原因如上每个人需求不一样
                            for (int j = 1; j < gridView.ColumnCount; j++)
                            {
                                if (string.IsNullOrEmpty(gridView[j, i].Value.ToString()))
                                {
                                    gridView[j, i].Value = "NULL";
                                }
                                if (gridView[j, i].Value.GetType() == typeof(string))
                                {
                                    string s = gridView[j, i].Value.ToString().Trim();
                                    excel.Cells[i + 2, j] = "'" + s;
                                    continue;
                                }
                                else
                                {
                                    excel.Cells[i + 2, j] = gridView[j, i].Value.ToString();
                                }
                            }
                        }

                        sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                        book.Close(false, miss, miss);
                        books.Close();
                        excel.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

GC.Collect();
MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        //System.Diagnostics.Process.Start(strName);//打开导出的EXCEL
                    }
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }

}