简单的方法来DataGridView的导出到Excel [英] Simple way to Export DataGridView to Excel

问题描述

我试图复制Datagrideview数据到Excel和我使用的代码

Hi I am trying to copy Datagrideview data to Excel and I am using the code

public static void ExportToExcel(DataGridView dgView)
{
    Microsoft.Office.Interop.Excel.Application excelApp = null;
    try
    {
        // instantiating the excel application class
        object misValue = System.Reflection.Missing.Value;
        excelApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook currentWorkbook = excelApp.Workbooks.Add(Type.Missing);
        Microsoft.Office.Interop.Excel.Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)currentWorkbook.ActiveSheet;
        currentWorksheet.Columns.ColumnWidth = 18;
        if (dgView.Rows.Count > 0)
        {
            currentWorksheet.Cells[1, 1] = DateTime.Now.ToString("s");
            int i = 1;
            foreach (DataGridViewColumn dgviewColumn in dgView.Columns)
            {
                // Excel work sheet indexing starts with 1
                currentWorksheet.Cells[2, i] = dgviewColumn.Name;
                ++i;
            }
            Microsoft.Office.Interop.Excel.Range headerColumnRange = currentWorksheet.get_Range("A2", "G2");
            headerColumnRange.Font.Bold = true;
            headerColumnRange.Font.Color = 0xFF0000;
            //headerColumnRange.EntireColumn.AutoFit();
            int rowIndex = 0;
            for (rowIndex = 0; rowIndex < dgView.Rows.Count; rowIndex++)
            {
                DataGridViewRow dgRow = dgView.Rows[rowIndex];
                for (int cellIndex = 0; cellIndex < dgRow.Cells.Count; cellIndex++)
                {
                    currentWorksheet.Cells[rowIndex + 3, cellIndex + 1] = dgRow.Cells[cellIndex].Value;
                }
            }
            Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range("A1", "G" + (rowIndex + 1).ToString());
            fullTextRange.WrapText = true;
            fullTextRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
        }
        else
        {
            string timeStamp = DateTime.Now.ToString("s");
            timeStamp = timeStamp.Replace(':', '-');
            timeStamp = timeStamp.Replace("T", "__");
            currentWorksheet.Cells[1, 1] = timeStamp;
            currentWorksheet.Cells[1, 2] = "No error occured";
        }
        using (SaveFileDialog exportSaveFileDialog = new SaveFileDialog())
        {
            exportSaveFileDialog.Title = "Select Excel File";
            exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx";

            if (DialogResult.OK == exportSaveFileDialog.ShowDialog())
            {
                string fullFileName = exportSaveFileDialog.FileName;
                // currentWorkbook.SaveCopyAs(fullFileName);
                // indicating that we already saved the workbook, otherwise call to Quit() will pop up
                // the save file dialogue box

                currentWorkbook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, misValue);
                currentWorkbook.Saved = true;
                MessageBox.Show("Exported successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (excelApp != null)
        {
            excelApp.Quit();
        }
    }
}

不过，因为有超过200个000的记录，它的花费很长的时间来出口。是否有一个更快的方法来做到这一点？

But as there is over 200 000 records, its taking a long time to export. Is there a faster way to do this?

推荐答案

试试这个代码。它比正常的互操作方法快，也将其转换为CSV它可以很容易地用Excel读

Try this code. It's faster than the normal interop methods, also it converts into CSV which can be read easily by excel.

int cols;
//open file 
StreamWriter wr = new StreamWriter("GB STOCK.csv");

//determine the number of columns and write columns to file 
cols = dgvStock.Columns.Count;
for (int i = 0; i < cols - 1; i++)
{ 
    wr.Write(dgvStock.Columns[i].Name.ToString().ToUpper() + ",");
} 
wr.WriteLine();

//write rows to excel file
for (int i = 0; i < (dgvStock.Rows.Count - 1); i++)
{ 
    for (int j = 0; j < cols; j++)
    { 
        if (dgvStock.Rows[i].Cells[j].Value != null)
        {
            wr.Write(dgvStock.Rows[i].Cells[j].Value + ",");
        }
        else 
        {
            wr.Write(",");
        }
    }

    wr.WriteLine();
}

//close file
wr.Close();

这篇关于简单的方法来DataGridView的导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！