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

问题描述

我正在尝试将DataGridView数据复制到Excel，我使用这个代码：

  public static void ExportToExcel（DataGridView dgView ）
 {
 Microsoft.Office.Interop.Excel.Application excelApp = null; 
 
 try 
 {
 //实例化excel应用程序类
对象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（dgView.Columns中的DataGridViewColumn dgviewColumn）
 {
 // Excel工作表索引以1 
开始。currentWorksheet.Cells [2，i] = dgviewColumn。名称; 
 ++ 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。细胞[cellIndex] .Value; 
} 
} 
 
 Microsoft.Office.Interop.Excel.Range fullTextRange = currentWorksheet.get_Range（A1，G+（rowIndex + 1）.ToString（） ）; 
 fullTextRange.WrapText = true; 
 fullTextRange.Horizo​​ntalAlignment = 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] =没有错误发生; 
} 
 
使用（SaveFileDialog exportSaveFileDialog = new SaveFileDialog（））
 {
 exportSaveFileDialog.Title =选择Excel文件; 
 exportSaveFileDialog.Filter =Microsoft Office Excel工作簿（*。xlsx）| * .xlsx; 
 
 if（DialogResult.OK == exportSaveFileDialog.ShowDialog（））
 {
 string fullFileName = exportSaveFileDialog.FileName; 
 // currentWorkbook.SaveCopyAs（fullFileName）; 
 //表示我们已经保存了工作簿，否则调用Quit（）将弹出
 //保存文件对话框
 
 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（导出成功，导出到Excel，MessageBoxButtons.OK，MessageBoxIcon.Information）; 
} 
} 
} 
 catch（Exception ex）
 {
 MessageBox.Show（ex.Message，Exception，MessageBoxButtons.OK，MessageBoxIcon 。错误）; 
} 
 finally 
 {
 if（excelApp！= null）
 {
 excelApp.Quit（）; 
} 
} 
} 
  

但是因为有超过200 000条记录，花了很长时间才能出口。有没有更快的方法来实现？

解决方案

尝试这段代码。它比正常的互操作方法快，也转换成CSV，可以通过excel轻松读取。

  int cols; 
 //打开文件
 StreamWriter wr = new StreamWriter（GB STOCK.csv）; 
 
 //确定列数并写入文件
 cols = dgvStock.Columns.Count; （int i = 0; i< cols  -  1; i ++）
 {
 wr.Write（dgvStock.Columns [i] .Name.ToString（）。ToUpper（）+ ，）; 
} 
 wr.WriteLine（）; 
 
 //将行写入excel文件
 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] .Cell [j] .Value +，）; 
} 
 else 
 {
 wr.Write（，）; 
} 
} 
 
 wr.WriteLine（）; 
} 
 
 //关闭文件
 wr.Close（）; 
  

I am trying to copy DataGridView data to Excel and I am using this 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();
        }
    }
}

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

解决方案

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屋！