Export datagridview to excel

Question

Hi guys, I was able to export datagridview data to excel. But the actual datagridview format was not exported, i.e. Font, color and space. So, is there a better way to export datagridview to excel, i.e. Not only data, but also appearance.

Example: -

Answer 1

Try exporting 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()); } 

Answer 2

Before writing code in the button_Click event, you must add a link to the Microsoft.Office.Interop.Excel object library.

Right-click on your project and select "Add Link." After that, go to the .NET tab and select and add Microsoft.Office.Interop.Excel.

Enter the code below in the button_Click event.

  // 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; } }