DataGridView导出到Excel

Question

我可以将datagridview的数据导出到Excel。但是实际的datagridview格式未被导出，如字体、颜色和间距等。所以，有没有最好的方法可以将datagridview导出到Excel，即不仅包括数据，还包括外观。

示例外观如下图：

- Nhuren

---

看起来你正在展示某种类型的报告。我建议你使用Crystal Reports或SSRS。它们内置了导出到其他格式的功能。如果你手动导出，你将不得不每次更改报告格式时修改导出函数。 - Eranga

Answer 1

尝试CSV导出。

private void ToCsV(DataGridView dGV, string filename)
    {
        string separator = ",";
        StringBuilder stOutput = new StringBuilder();
        // Export titles: 
        StringBuilder sHeaders = new StringBuilder();
        for (int j = 0; j < dGV.Columns.Count; j++)
        {
            sHeaders.Append(dGV.Columns[j].HeaderText);
            sHeaders.Append(separator);
        }
        stOutput.AppendLine(sHeaders.ToString());
        // Export data. 
        for (int i = 0; i < dGV.RowCount - 1; i++)
        {
            StringBuilder stLine = new StringBuilder();
            for (int j = 0; j < dGV.ColumnCount; j++)
            {
                stLine.Append(Convert.ToString(dGV[j, i].Value));
                stLine.Append(separator);
            }
            stOutput.AppendLine(stLine.ToString());
        }

        File.WriteAllText(filename, stOutput.ToString());
    } 

- Soner Gönül

---

我遇到了一个错误，提示为“'System.Windows.Forms.DataGridViewRowCollection'不包含名为'Cells'的定义”，你能帮我吗？ - Developer

这里有很多错误！dGV.Rows.Cells 应该是 dGV.Rows[i].Cells，而 for (int i = 0; i < dGV.RowCount - 1; i++) 应该是 for (int i = 0; i < dGV.RowCount; i++)。 - Lucas B

Answer 2

在编写button_Click事件的代码之前，您必须添加对Microsoft.Office.Interop.Excel对象库的引用。

右键单击您的项目并选择“添加引用”菜单。然后转到.NET选项卡，选择并添加Microsoft.Office.Interop.Excel。

在button_Click事件中编写以下代码。

            // button_Click event

           private void button11_Click(object sender, EventArgs e)
           {
                // creating Excel Application
                string fileName = String.Empty;
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                // creating new WorkBook within Excel application
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program
                app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                try
                {
                     //Fixed:(Microsoft.Office.Interop.Excel.Worksheet)
                     worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                     worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                     // changing the name of active sheet
                     worksheet.Name = "Exported from AMIT";
                     // storing header part in Excel
                     for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                     {
                          worksheet.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++)
                          {
                               worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                          }
                     }


                     // Save The Application
                     SaveFileDialog saveFileExcel = new SaveFileDialog();

                     saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*";
                     saveFileExcel.FilterIndex = 2;
                     saveFileExcel.RestoreDirectory = true;


                     if (saveFileExcel.ShowDialog() == DialogResult.OK)
                     {
                          fileName = saveFileExcel.FileName;
                          //Fixed-old code :11 para->add 1:Type.Missing
                          workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                     }
                     else
                     {
                           return;

                           // Exit from the application
                           //app.Quit();
                     }
               }
               catch (Exception)
               {
                     //Statement;
               }
               finally
               {
                     app.Quit();
                     workbook = null;
                     app = null;
               }
          }

- Amit Mohanty