C#将数据库数据导出到Excel & 将Excel文件导入到数据库

最新推荐文章于 2024-05-20 20:35:54 发布

༺Aslan༻

最新推荐文章于 2024-05-20 20:35:54 发布

阅读量2k

点赞数

分类专栏： C#初学笔记

本文链接：https://blog.csdn.net/XueJiaoKui/article/details/124674681

版权

c# microsoft 开发语言

C#初学笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1、读取Excel数据并写入到SQLServer数据库中；

2、将SQLServer数据库中的数据写入到Excel表中；

以下部分程序代码：

public void printAll(DataGridView dgv)
        {           
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xlsx)|*.xlsx";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                if (strName.Length != 0)
                {
                    //没有数据的话就不往下执行  
                    if (dgv.Rows.Count == 0)
                          return;
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = true;//若是true，则在导出的时候会显示EXcel界面。  
                    if (excel == null)
                    {
                        MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Workbooks books = excel.Workbooks;
                    Workbook book = books.Add(miss);
                    Worksheet sheet = (Worksheet)book.ActiveSheet;
                    sheet.Name = "Sheet1";
                    //生成Excel中列头名称  
                    for (int i = 0; i < dgv.Columns.Count; i++)
                    {
                        if (dgv.Columns[i].Visible == true)
                        {
                            excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
                        }

                    }
                    //把DataGridView当前页的数据保存在Excel中  
                   
                    sheet.SaveAs(strName, miss, miss, miss, miss, miss, 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);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "导出失败!");
            }
        }

完整代码程序及配套测试数据库数据如下：

https://download.csdn.net/download/XueJiaoKui/85331183https://download.csdn.net/download/XueJiaoKui/85331183