什么是的dataGridView行导出到Excel或到SQL Server数据库的最快方法 [英] What is the fastest way to export dataGridView rows to Excel or into an SQL Server database

问题描述

什么是出口在460328范围内的DataGridView行的最快方法 - 800328到Excel或与出使用Microsoft Office互操作的互操作是对系统资源相当缓慢和沉重的SQL Server数据库的表？

What is the fastest way to export DataGridView rows in the range of 460328 - 800328 to Excel or into an SQL Server database table with out using Microsoft office interop as interop is quite slow and heavy on system resources?

推荐答案

有关导出到Excel，如果你不使用基于2007或2010的XML，互操作几乎是唯一途径去。因为它的声誉，虽然它不是那么糟糕。我将列出一些解决方案。

For exporting to Excel, if you aren't using the XML based 2007 or 2010, Interop is pretty much the only way to go. It's not as bad as it's reputation though. I'll list a few solutions.

首先添加Microsoft.Office.Interop。 Excel的组件引用到项目中。这应该是项目的.NET选项卡下 - >添加引用。
using语句添加到您的形式：

First add a Microsoft.Office.Interop.Excel component reference to your project. This should be under the .NET tab in Project -> Add Reference. add the using statement to your form:

使用Excel =的Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;

添加一个按钮控件，并添加此代码，以它的机身：

add a button control, and add this code to it's body:

    private void btnExport_Click(object sender, EventArgs e)
    {

        Excel.Application app = new Excel.Application();
        app.Visible = true;
        Excel.Workbook wb = app.Workbooks.Add(1);
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
        // changing the name of active sheet
        ws.Name = "Exported from gridview";

        ws.Rows.HorizontalAlignment = HorizontalAlignment.Center;
        // storing header part in Excel
        for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
        {
            ws.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
        }


        // storing Each row and column value to excel sheet
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
            }
        }

        // sizing the columns
        ws.Cells.EntireColumn.AutoFit();

        // save the application
        wb.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // Exit from the application
       app.Quit();
    }
}

2 - 到SQL Server

这不需要互操作。为了方便使用，通过你的清单对象执行插入事件。如果你有设置表符合您的网格视图的列，这很容易。在这里，我使用一个存储过程。

2 - to SQL Server

This requires no interop. For ease of use, pass your List object to the event executing the inserts. If you have tables set up to correspond to your grid view column, it's easy. Here, I use a sproc.

    private void btnToSQL_Click(object sender, EventArgs e)
    {
        string connStr = @"Data Source=(local)\sqlexpress;Initial Catalog=rTALIS;Integrated Security=True";
        var cn = new SqlConnection(connStr);
        var cm = new SqlCommand("exec usp_InsertRecord", cn);
        cm.CommandType = System.Data.CommandType.StoredProcedure;
        try
        {
            cn.Open();
            foreach (Row r in rows)
            {
                cm.Parameters.Clear();
                cm.Parameters.AddWithValue("@Number1", r.Number1);
                cm.Parameters.AddWithValue("@Number2", r.Number2);
                cm.Parameters.AddWithValue("@Number3", r.Number3);
                cm.Parameters.AddWithValue("@Number4", r.Number4);
                cm.Parameters.AddWithValue("@Number5", r.Number5);
                cm.Parameters.AddWithValue("@Number6", r.Number6);
                cm.Parameters.AddWithValue("@Number7", r.Number7);
                cm.Parameters.AddWithValue("@Date1", r.Date1);
                cm.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            cn.Close();
        }
    }

让我知道如果我需要调整这个你。在最初的例子，我有表行数=新名单（）;在Form_Load方法中声明。这个工作对于解决方案，但它的范围现在是太有限了。我已经移动它/伸到类，从而在可以在任何地方的形式（具体btnToSQL_Click）上被调用。我曾评论它列如下：

Let me know if I need to tweak this for you. In the original example, I had List rows = new List(); declared in the form_Load method. This worked for that solution, but it's scope is now too limited. I have moved it up/out into the class, so that in can be called anywhere on the form (specifically btnToSQL_Click). I have commented it out below:

    List<Row> rows = new List<Row>();

    private void Form1_Load(object sender, EventArgs e)
    {
        //var rows = new List<Row>();  //limited scope
        var sr = new StreamReader(@"C:\so_test.txt");
        while (!sr.EndOfStream)
        {
            string s = sr.ReadLine();
            if (!String.IsNullOrEmpty(s.Trim()))
            {
                rows.Add(new Row(s));
            }
        }
        sr.Close();
        dataGridView1.DataSource = rows;
    }

这篇关于什么是的dataGridView行导出到Excel或到SQL Server数据库的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！