How to export datagridview data in Excel?

Question

my export code

This code writes data to excel, but the content is embedded in cells. they are scattered.

public static void export_to_excel(DataGridView dgv, string excel_file) { int cols; //open file StreamWriter wr = new StreamWriter(excel_file); cols = dgv.Columns.Count; for (int i = 0; i < cols; i++) { wr.Write(dgv.Columns[i].HeaderText.ToString().ToUpper() + "\t"); } wr.WriteLine(); //write rows to excel file for (int i = 0; i < (dgv.Rows.Count - 1); i++) { for (int j = 0; j < cols; j++) { if (dgv.Rows[i].Cells[j].Value != null) wr.Write(dgv.Rows[i].Cells[j].Value + "\t"); else { wr.Write("\t"); } } wr.WriteLine(); } //close file wr.Close(); } 

Answer 1

I did this in VB.net and I am converting it to C # from http://www.developerfusion.com/tools/convert/vb-to-csharp/ , so you should test it.

VB.net Code:

  Imports System.Runtime.CompilerServices Imports Excel = Microsoft.Office.Interop.Excel Public Module ExcelMod <Extension()> _ Public Function ToExcel(ByVal grd As DataGridView, ByVal path As String, Optional ByRef exp As Exception = Nothing) As Boolean Dim res As Boolean = False exp = Nothing Dim xlApp As Excel.Application = Nothing Dim xlWorkBook As Excel.Workbook = Nothing Dim xlWorkSheet As Excel.Worksheet = Nothing Try Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture Dim misValue As Object = System.Reflection.Missing.Value Dim i As Integer Dim j As Integer xlApp = New Excel.ApplicationClass System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US") xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") Dim lastCol As Integer = 0 Dim lastRow As Integer = 0 For j = 0 To grd.ColumnCount - 1 If grd.Columns(j).Visible Then xlWorkSheet.Columns(lastCol + 1).ColumnWidth = CInt(grd.Columns(j).Width / 10) xlWorkSheet.Cells(1, lastCol + 1) = grd.Columns(j).HeaderText lastCol += 1 End If Next lastRow = 0 For i = 0 To grd.RowCount - 1 lastCol = 0 For j = 0 To grd.ColumnCount - 1 If grd.Columns(j).Visible AndAlso grd.Rows(i).Visible Then If grd(j, i).FormattedValue <> Nothing Then _ xlWorkSheet.Cells(lastRow + 2, lastCol + 1) = grd(j, i).FormattedValue.ToString() lastCol += 1 End If Next If grd.Rows(i).Visible Then lastRow += 1 Next xlWorkSheet.SaveAs(path) xlWorkBook.Close() xlApp.Quit() System.Threading.Thread.CurrentThread.CurrentCulture = oldCI res = True Catch ex As Exception exp = ex Finally If xlApp IsNot Nothing Then releaseObject(xlApp) If xlWorkBook IsNot Nothing Then releaseObject(xlWorkBook) If xlWorkSheet IsNot Nothing Then releaseObject(xlWorkSheet) End Try Return res End Function Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Module 

C # code:

 using Microsoft.VisualBasic; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.Runtime.CompilerServices; using Excel = Microsoft.Office.Interop.Excel; public static class ExcelMod { public static bool ToExcel(this DataGridView grd, string path, ref Exception exp = null) { bool res = false; exp = null; Excel.Application xlApp = null; Excel.Workbook xlWorkBook = null; Excel.Worksheet xlWorkSheet = null; try { System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; object misValue = System.Reflection.Missing.Value; int i = 0; int j = 0; xlApp = new Excel.ApplicationClass(); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = xlWorkBook.Sheets("sheet1"); int lastCol = 0; int lastRow = 0; for (j = 0; j <= grd.ColumnCount - 1; j++) { if (grd.Columns(j).Visible) { xlWorkSheet.Columns(lastCol + 1).ColumnWidth = Convert.ToInt32(grd.Columns(j).Width / 10); xlWorkSheet.Cells(1, lastCol + 1) = grd.Columns(j).HeaderText; lastCol += 1; } } lastRow = 0; for (i = 0; i <= grd.RowCount - 1; i++) { lastCol = 0; for (j = 0; j <= grd.ColumnCount - 1; j++) { if (grd.Columns(j).Visible && grd.Rows(i).Visible) { if (grd(j, i).FormattedValue != null) xlWorkSheet.Cells(lastRow + 2, lastCol + 1) = grd(j, i).FormattedValue.ToString(); lastCol += 1; } } if (grd.Rows(i).Visible) lastRow += 1; } xlWorkSheet.SaveAs(path); xlWorkBook.Close(); xlApp.Quit(); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; res = true; } catch (Exception ex) { exp = ex; } finally { if (xlApp != null) releaseObject(xlApp); if (xlWorkBook != null) releaseObject(xlWorkBook); if (xlWorkSheet != null) releaseObject(xlWorkSheet); } return res; } private static void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; } finally { GC.Collect(); } } } 

Answer 2

the reason they are scattered is because you use the tab delimiter (\ t), use the delimiter ",". To export only values, you must export the file as a csv file. Advantages: - Installation of Excel is not required. - No need for COM calls. (By the way, this makes your code one threading and not scalable for several processors / threads) - Excel can directly read csv files.

Export Code:

 public static void export_to_excelAsCsvFile(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()); }