首页 文章

如何在按钮点击时立即将dataGridView数据导出到Excel？

提问于
浏览 次
34

我的数据网格视图中有10k行和15列 . 我想将此数据导出到excel表单o按钮 . 我已经尝试过以下代码 .

private void btExport_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);        
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   
        app.Visible = true;
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;                  
        for(int i=1;i<dataGridView1.Columns.Count+1;i++)
        {
             worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
        }    
        for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
        {
            for(int j=0;j<dataGridView1.Columns.Count;j++)
            {
                if (dataGridView1.Rows[i].Cells[j].Value != null)
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
                else
                {
                    worksheet.Cells[i + 2, j + 1] = "";
                }
            }
        }
    }

这对我有用，但 it is taking lots of time to 完成导出过程 .

Is it possible to export from dataGridView (with 10k rows)to excel instantly on a button click?

除此之外，当我尝试将所有dataGridview内容复制到剪贴板然后手动将其粘贴到Excel工作表时，它几乎立即发生 .

So is there a way to copy all dataGridView cells to clip board and paste it to excel sheet(with cell formatting) on a button click?

我有如下复制到剪贴板的代码，但我不知道如何通过打开它将其粘贴到新的Excel工作表中 .

private void copyAllToolStripMenuItem_Click(object sender, EventArgs e)
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }

请帮忙举个例子 . 我是C＃的新手 .

c# datagridview copy export-to-excel export-to-csv

11 回答

  • 3

    我通过简单的复制和粘贴方法解决了这个问题我不知道这是最好的方法，但对我而言，它的效果很好，而且几乎是即时的 . 这是我的代码 .

    private void copyAlltoClipboard()
    {
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }

    谢谢 .

    回复于
  • 3

    这是一个很好的问题，我很惊讶找到一个清晰而完整的答案是多么困难，我发现的大部分答案都是sudo-code或者不是100％完成 .

    我能够创建一个完整的解决方案，根据Jake的答案将数据从我的DataGridView复制并保存到excel文件，所以我发布了我的完整解决方案，希望它可以帮助其他新来者像我这样的c＃:)

    首先，您需要在项目中使用 Microsoft.Office.Interop.Excel 引用 . 有关如何添加它，请参见MSDN .

    我的代码：

    using Excel = Microsoft.Office.Interop.Excel;

private void btnExportToExcel_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.Filter = "Excel Documents (*.xls)|*.xls";
    sfd.FileName = "Inventory_Adjustment_Export.xls";
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        // Copy DataGridView results to clipboard
        copyAlltoClipboard();

        object misValue = System.Reflection.Missing.Value;
        Excel.Application xlexcel = new Excel.Application();

        xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
        Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
        Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        // Format column D as text before pasting results, this was required for my data
        Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
        rng.NumberFormat = "@";

        // Paste clipboard results to worksheet range
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);

        // For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
        // Delete blank column A and select cell A1
        Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
        delRng.Delete(Type.Missing);
        xlWorkSheet.get_Range("A1").Select();

        // Save the excel file under the captured location from the SaveFileDialog
        xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlexcel.DisplayAlerts = true;
        xlWorkBook.Close(true, misValue, misValue);
        xlexcel.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlexcel);

        // Clear Clipboard and DataGridView selection
        Clipboard.Clear();
        dgvItems.ClearSelection();

        // Open the newly saved excel file
        if (File.Exists(sfd.FileName))
            System.Diagnostics.Process.Start(sfd.FileName);
    }
}

private void copyAlltoClipboard()
{
    dgvItems.SelectAll();
    DataObject dataObj = dgvItems.GetClipboardContent();
    if (dataObj != null)
        Clipboard.SetDataObject(dataObj);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}
    回复于
  • 6

    我不打算偷@Jake和@ Cornelius的答案，所以我尝试编辑它 . 但它被拒绝了 . 无论如何，我必须指出的唯一改进是关于在粘贴后避免excel中的额外空白列 . 添加一行 dataGridView1.RowHeadersVisible = false; 会隐藏所谓的"Row Header"，它出现在DataGridView的最左侧，因此当您执行 dataGridView1.SelectAll(); 时，它不会被选中并复制到剪贴板

    private void copyAlltoClipboard()
    {
        //to remove the first blank column from datagridview
        dataGridView1.RowHeadersVisible = false;
        dataGridView1.SelectAll();
        DataObject dataObj = dataGridView1.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void button3_Click_1(object sender, EventArgs e)
    {
        copyAlltoClipboard();
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkBook = xlexcel.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
        CR.Select();
        xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);          
    }
    回复于
  • 2
    using Excel = Microsoft.Office.Interop.Excel;


private void btnExportExcel_Click(object sender, EventArgs e)
{
    try
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        excel.Visible = true;
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
        int StartCol = 1;
        int StartRow = 1;
        int j = 0, i = 0;

        //Write Headers
        for (j = 0; j < dgvSource.Columns.Count; j++)
        {
            Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow, StartCol + j];
            myRange.Value2 = dgvSource.Columns[j].HeaderText;
        }

        StartRow++;

        //Write datagridview content
        for (i = 0; i < dgvSource.Rows.Count; i++)
        {
            for (j = 0; j < dgvSource.Columns.Count; j++)
            {
                try
                {
                    Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[StartRow + i, StartCol + j];
                    myRange.Value2 = dgvSource[j, i].Value == null ? "" : dgvSource[j, i].Value;
                }
                catch
                {
                    ;
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}
    回复于
  • 1

    Interop很慢并且有其他问题，使用剪贴板似乎是不可扩展的 . 以下是另外两种方法

    我将从ClosedXML开始 .

    回复于
  • 60

    最好用的是使用closedxml.codeplex.com Library.Refer it @ https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=Documentation

    var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GetTheDataTable();//Refer documentation


wb.Worksheets.Add(dt);

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"FileName.xlsx\"");

using (var ms = new System.IO.MemoryStream()) {
    wb.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);
    ms.Close();
}

Response.End();
    回复于
  • 23

    我喜欢杰克的解决方案 . 通过执行以下操作可以解决没有标头的问题

    xlWorkSheet.Cells[1, 1] = "Header 1";
xlWorkSheet.Cells[1, 2] = "Header 2";
xlWorkSheet.Cells[1, 3] = "Header 3";

    当然，只有你知道 Headers 应该提前是什么才有效 .

    回复于
  • 1

    这就是我用于我的gridview，尝试将它用于你的数据，它完美地工作：

    GridView1.AllowPaging = false;
        GridView1.DataBind();

        StringBuilder sb = new StringBuilder();

        for (int k = 0; k < GridView1.Columns.Count; k++)
        {
            //add separator
            sb.Append(GridView1.Columns[k].HeaderText+";");

        }


        //append new line
        sb.Append("\r\n");
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            for (int k = 0; k < GridView1.Columns.Count; k++)
            {
                sb.Append(GridView1.Rows[i].Cells[k].Text+";");
            }
            sb.AppendLine();
        }
    回复于
  • 8

    这个答案是第一个问题，为什么需要这么多时间，它提供了一个替代解决方案，用于将DataGridView导出到Excel .

    MS Office Interop很慢，甚至Microsoft也不建议在服务器端使用Interop，也不能用于导出大型Excel文件 . 有关详细信息，请参阅Microsoft的观点why not to use OLE Automation .

    Interop以XLS文件格式（旧的Excel 97-2003文件格式）保存Excel文件，并且对Office 2003的支持已结束 . Microsoft Excel使用Office 2007发布了XLSX文件格式，并建议使用OpenXML SDK而不是Interop . 但是XLSX文件实际上并不是那么快，并且不能很好地处理大型Excel文件，因为它们基于XML文件格式 . 这就是为什么Microsoft还发布了Office 2007的XLSB文件格式，这是推荐用于大型Excel文件的文件格式 . 它是二进制格式 . 因此，最好和最快的解决方案是保存XLSB文件 .

    您可以使用此C# Excel library来保存XLSB文件，但它也支持XLS和XLSX文件格式 .

    请参阅以下代码示例，作为将DataGridView导出到Excel的替代方法：

    // Create a DataSet and add the DataTable of DataGridView 
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)dataGridView);
//or ((DataTable)dataGridView.DataSource).Copy() to create a copy

// Export Excel file 
ExcelDocument workbook = new ExcelDocument();
workbook.easy_WriteXLSBFile_FromDataSet(filePath, dataSet, 
     new EasyXLS.ExcelAutoFormat(EasyXLS.Constants.Styles.AUTOFORMAT_EASYXLS1), 
     "Sheet1");

    如果还需要导出DataGridView的格式，请检查此代码示例如何export datagridview to Excel in C# .

    回复于
  • 0

    此行仅适用于Windows窗体上的DataGridView控件：

    DataObject dataObj = dataGridView1.GetClipboardContent();

    这个问题解决了同样的问题，但是对于WPF的DataGrid控件框架：

    private void copyDataGridContentToClipboard()
    {
        datagridGrupeProductie.SelectAll();
        datagridGrupeProductie.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;

        ApplicationCommands.Copy.Execute(null, datagridGrupeProductie);
        datagridGrupeProductie.UnselectAll();
    }


    private void rightClickGrupeProductie_Click(object sender, RoutedEventArgs e)
    {
        copyDataGridContentToClipboard();
        Microsoft.Office.Interop.Excel.Application excelApp;
        Microsoft.Office.Interop.Excel.Workbook excelWkbk;
        Microsoft.Office.Interop.Excel.Worksheet excelWksht;
        object misValue = System.Reflection.Missing.Value;
        excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.Visible = true;
        excelWkbk = excelApp.Workbooks.Add(misValue);
        excelWksht = (Microsoft.Office.Interop.Excel.Worksheet)excelWkbk.Worksheets.get_Item(1);
        Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)excelWksht.Cells[1, 1];
        CR.Select();
        excelWksht.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
    }
    回复于
  • 0

    如果您的DataGridView的 RightToLeft 设置为 Yes ，那么您的数据将反向复制 . 因此，您应该使用以下代码正确复制数据 .

    private void copyAlltoClipboard()
{
    dgvItems.RightToLeft = RightToLeft.No;
    dgvItems.SelectAll();
    DataObject dataObj = dgvItems.GetClipboardContent();
    if (dataObj != null)
        Clipboard.SetDataObject(dataObj);
    dgvItems.RightToLeft = RightToLeft.Yes;
}
    回复于

相关问题