如何使用c＃在现有excel文件中添加datagridview

Hello well I have this problem,The code only it can create a new excle file but I want to export my datagrid in a existing excel file. Any help is appreciated.

您好我有这个问题,该代码只能创建一个新的excle文件,但我想在现有的excel文件中导出我的数据网格。任何帮助表示赞赏。

     try
        {
            if (DataGridView1.DataSource != null)
            {
                SaveFileDialog fichero = new SaveFileDialog();
                fichero.Filter = "Excel (*.xls)|*.xls";
                if (fichero.ShowDialog() == DialogResult.OK)
                {
                    Microsoft.Office.Interop.Excel.Application aplicacion;
                    Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
                    Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
                    aplicacion = new Microsoft.Office.Interop.Excel.Application();
                    libros_trabajo = aplicacion.Workbooks.Add();
                    hoja_trabajo = (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);

                    //exportar cabeceras DataGridView1
                    for (int i = 1; i <= this.DataGridView1.Columns.Count; i++)
                    {
                        hoja_trabajo.Cells[1, i] = this.DataGridView1.Columns[i - 1].HeaderText;
                    }

                    //Recorremos el DataGridView rellenando la hoja de trabajo con los datos
                    for (int i = 0; i < this.DataGridView1.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < this.DataGridView1.Columns.Count; j++)
                        {
                            hoja_trabajo.Cells[i + 2, j + 1] = this.DataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
                    }

                    libros_trabajo.SaveAs(fichero.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
                    libros_trabajo.Close(true);
                    aplicacion.Quit();
                }
            }
        }

1 个解决方案

#1

Pertinent to your task, use the underlying DataTable

与您的任务相关,使用基础DataTable

DataTable dt = DataGridView1.DataSource as DataTable;

and export data from that DataTable dt to Excel using the following procedure, which utilizes Microsoft.Office.Interop.Excel object library:

并使用以下过程将DataTable dt中的数据导出到Excel,该过程使用Microsoft.Office.Interop.Excel对象库:

/// <summary>
/// export DataTable to Excel (C#)
/// </summary>
internal static void Export2Excel(DataTable dataTable)
{
    object misValue = System.Reflection.Missing.Value;
    Microsoft.Office.Interop.Excel.Application _appExcel = null;
    Microsoft.Office.Interop.Excel.Workbook _excelWorkbook = null;
    Microsoft.Office.Interop.Excel.Worksheet _excelWorksheet = null;
    try
    {
        // excel app object
        _appExcel = new Microsoft.Office.Interop.Excel.Application();

        // excel workbook object added to app
        _excelWorkbook = _appExcel.Workbooks.Add(misValue);

        _excelWorksheet = _appExcel.ActiveWorkbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

        // column names row (range obj)
        Microsoft.Office.Interop.Excel.Range _columnsNameRange;
        _columnsNameRange = _excelWorksheet.get_Range("A1", misValue).get_Resize(1, dataTable.Columns.Count);

        // column names array to be assigned to _columnNameRange
        string[] _arrColumnNames = new string[dataTable.Columns.Count];

        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            // array of column names
            _arrColumnNames[i] = dataTable.Columns[i].ColumnName;
        }

        // assign array to column headers range, make 'em bold
        _columnsNameRange.set_Value(misValue, _arrColumnNames);
        _columnsNameRange.Font.Bold = true;

        // populate data content row by row
        for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)
        {
            _excelWorksheet.Range["A2"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =
            dataTable.Rows[Idx].ItemArray;
        }

        // Autofit all Columns in the range
        _columnsNameRange.Columns.EntireColumn.AutoFit();
    }
    catch { throw; }
}

just pass dt as an argument. Optionally, upon data export completion you can save the Workbook and Quit Excel app. Furthermore, as mentioned in the comments thread, you can open the existing Excel file (e.g. "MyExcelFile") and export data to that file, or just re-write the existing file by saving the newly created Workbook (i.e. _excelWorkbook) under the old file name.

只是将dt作为参数传递。 (可选)在数据导出完成后,您可以保存工作簿和退出Excel应用程序。此外,如评论主题中所述,您可以打开现有的Excel文件(例如“MyExcelFile”)并将数据导出到该文件,或者只需通过将旧创建的工作簿(即_excelWorkbook)保存在旧文件下来重写现有文件文件名。

The core idea of proposed solution is to run data export procedure on the underlying DataTable rather than DataGridView and export entire data Row at once (instead of cell-by-cell) for better performance.

提出的解决方案的核心思想是在底层DataTable而不是DataGridView上运行数据导出过程,并立即导出整个数据行(而不是逐个单元格)以获得更好的性能。

Hope this may help.

希望这可能有所帮助。

#1