Exporting datagridview to csv file

Question

I'm working on a application which will export my DataGridView called scannerDataGridView to a csv file.

Found some example code to do this, but can't get it working. Btw my datagrid isn't databound to a source.

When i try to use the Streamwriter to only write the column headers everything goes well, but when i try to export the whole datagrid including data i get an exeption trhown.

System.NullReferenceException: Object reference not set to an instance of an object. at Scanmonitor.Form1.button1_Click(Object sender, EventArgs e)

Here is my Code, error is given on the following line:

dataFromGrid = dataFromGrid + ',' + dataRowObject.Cells[i].Value.ToString();

//csvFileWriter = StreamWriter
//scannerDataGridView = DataGridView   

private void button1_Click(object sender, EventArgs e)
{
    string CsvFpath = @"C:\scanner\CSV-EXPORT.csv";
    try
    {
        System.IO.StreamWriter csvFileWriter = new StreamWriter(CsvFpath, false);

        string columnHeaderText = "";

        int countColumn = scannerDataGridView.ColumnCount - 1;

        if (countColumn >= 0)
        {
            columnHeaderText = scannerDataGridView.Columns[0].HeaderText;
        }

        for (int i = 1; i <= countColumn; i++)
        {
            columnHeaderText = columnHeaderText + ',' + scannerDataGridView.Columns[i].HeaderText;
        }


        csvFileWriter.WriteLine(columnHeaderText);

        foreach (DataGridViewRow dataRowObject in scannerDataGridView.Rows)
        {
            if (!dataRowObject.IsNewRow)
            {
                string dataFromGrid = "";

                dataFromGrid = dataRowObject.Cells[0].Value.ToString();

                for (int i = 1; i <= countColumn; i++)
                {
                    dataFromGrid = dataFromGrid + ',' + dataRowObject.Cells[i].Value.ToString();

                    csvFileWriter.WriteLine(dataFromGrid);
                }
            }
        }


        csvFileWriter.Flush();
        csvFileWriter.Close();
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.ToString());
    }

At what line is the exception thrown? Also you can use a foreach to go through the columns of each row as well: foreach (DataGridViewCell cell in dataRowObject.Cells), unless there is a specific reason you'd prefer the normal for loop? — Dan, Mar 30 '12 at 13:26
Hi PandaNL, I have deleted my answer. I still believe that offsending line should read `object value = dataRowObject.Cells[i].Value; dataFromGrid = dataFromGrid + ',' + (value ?? string.Empty).ToString()); ` — Nikola Markovinović, Mar 30 '12 at 19:34
There's no need to flush() before close(). Dispose() calls Close() and it calls flush(). — Behrooz, Feb 10 '14 at 10:23

score 40 · Answer 1 · edited Jan 30 '14 at 01:09

40

LINQ FTW!

var sb = new StringBuilder();

var headers = dataGridView1.Columns.Cast<DataGridViewColumn>();
sb.AppendLine(string.Join(",", headers.Select(column => "\"" + column.HeaderText + "\"").ToArray()));

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    var cells = row.Cells.Cast<DataGridViewCell>();
    sb.AppendLine(string.Join(",", cells.Select(cell => "\"" + cell.Value + "\"").ToArray()));
}

And indeed, c.Value.ToString() will throw on null value, while c.Value will correctly convert to an empty string.

edited Jan 30 '14 at 01:09

Troy Chard

103
4

answered Apr 30 '13 at 08:45

Jonathan

6,939
4
44
61

1

I was getting an error with the original code and had to make one edit: headers.Select(column => "\"" + column.HeaderText + "\""); headers.Select(column => "\"" + column.HeaderText + "\"").ToArray(); – B H May 26 '13 at 03:00
I found the same thing as BH (and submitted the edit). That aside, this is a great answer! – Troy Chard Jan 30 '14 at 01:08
Also, I had a (separate) problem where the first column used a special "RowIndex" property which (oddly) wasn't setting the cell value. Fixed by grabbing the "cell.FormattedValue" instead of the cell.Value for the first column. sb.AppendLine( string.Join(",", cells.Select( cell => "\"" + (cell.ColumnIndex == 0 ? cell.FormattedValue : cell.Value) + "\"" ).ToArray())); – Troy Chard Jan 30 '14 at 01:15
Use `CurrentCulture.TextInfo.ListSeparator` ìnstead of `","` – nixda Aug 12 '16 at 09:07
How do you check to see if any of the cells in the cast row are null? – JohnP Dec 10 '21 at 15:24
`if (cells.Any(cell => cell.Value == null)) { /* there's a null! */ }` – Jonathan Dec 12 '21 at 07:40

Adam White · Answer 2 · 2016-12-01T03:39:34.873

A little known feature of the DataGridView is the ability to programmatically select some or all of the DataGridCells, and send them to a DataObject using the method DataGridView.GetClipboardContent(). Whats the advantage of this then?

A DataObject doesn't just store an object, but rather the representation of that object in various different formats. This is how the Clipboard is able to work its magic; it has various formats stored and different controls/classes can specify which format they wish to accept. In this case, the DataGridView will store the selected cells in the DataObject as a tab-delimited text format, a CSV format, or as HTML (*).

The contents of the DataObject can be retrieved by calling the DataObject.GetData() or DataObject.GetText() methods and specifying a predefined data format enum. In this case, we want the format to be TextDataFormat.CommaSeparatedValue for CSV, then we can just write that result to a file using System.IO.File class.

(*) Actually, what it returns is not, strictly speaking, HTML. This format will also contain a data header that you were not expecting. While the header does contain the starting position of the HTML, I just discard anything above the HTML tag like myString.Substring(IndexOf("<HTML>"));.

Observe the following code:

void SaveDataGridViewToCSV(string filename)
{        
    // Choose whether to write header. Use EnableWithoutHeaderText instead to omit header.
    dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
    // Select all the cells
    dataGridView1.SelectAll();
    // Copy selected cells to DataObject
    DataObject dataObject = dataGridView1.GetClipboardContent();
    // Get the text of the DataObject, and serialize it to a file
    File.WriteAllText(filename, dataObject.GetText(TextDataFormat.CommaSeparatedValue));
}

Now, isn't that better? Why re-invent the wheel?

Hope this helps...

I've seen this ugly solution circulating on the net. Please don't use it! First of all, it doesn't work when `SelectionMode = Single`. Secondly, using the clipboard as a temporary storage is plain out crazy. Some other application could interfere and replace the content between the calls to `SetDataObject` and `GetText`. — l33t, May 19 '16 at 22:53
@l33t Thanks. You are correct, in that this is a total hack, an abuse of the clipboard, and absolutely should not be relied upon in production code. I didn't really consider the implications of other applications interfering with the clipboard, but it is a valid concern. Apparently there is a WIN32 API call that can lock the clipboard, but wont name it here because that would be going from bad, to worse, to totally off the rails with insanity. Although this code worked for me for my needs (prototyping a POC) in retrospect, I agree with l33t here in that this hack should never actually be used. — Adam White, Jun 01 '16 at 22:55
This currently seems like the best answer as `.GetClipboardContent` uses the cell formatted value instead of the cell value and also handles few rare edge cases that the other answers don't, such as values containing `,`, `"`, `\t`, `\r`, `\n` or cells that are currently being edited. For anyone that doesn't want to use `.GetClipboardContent`, I would recommend looking for solution that handles the delimiter and escape characters mentioned above. — Slai, Nov 28 '16 at 15:41

score 4 · Answer 3 · answered Sep 06 '17 at 10:08

      Please check this code.its working fine  

          try
               {
            //Build the CSV file data as a Comma separated string.
            string csv = string.Empty;

            //Add the Header row for CSV file.
            foreach (DataGridViewColumn column in dataGridView1.Columns)
            {
                csv += column.HeaderText + ',';
            }
            //Add new line.
            csv += "\r\n";

            //Adding the Rows

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                foreach (DataGridViewCell cell in row.Cells)
                {
                    if (cell.Value != null)
                    {
                        //Add the Data rows.
                        csv += cell.Value.ToString().TrimEnd(',').Replace(",", ";") + ',';
                    }
                    // break;
                }
                //Add new line.
                csv += "\r\n";
            }

            //Exporting to CSV.
            string folderPath = "C:\\CSV\\";
            if (!Directory.Exists(folderPath))
            {
                Directory.CreateDirectory(folderPath);
            }
            File.WriteAllText(folderPath + "Invoice.csv", csv);
            MessageBox.Show("");
        }
        catch
        {
            MessageBox.Show("");
        }

score 2 · Answer 4 · edited Sep 27 '15 at 15:32

2

Your code was almost there... But I made the following corrections and it works great. Thanks for the post.

Error:

string[] output = new string[dgvLista_Apl_Geral.RowCount + 1];

Correction:

string[] output = new string[DGV.RowCount + 1];

Error:

System.IO.File.WriteAllLines(filename, output, System.Text.Encoding.UTF8);

Correction:

System.IO.File.WriteAllLines(sfd.FileName, output, System.Text.Encoding.UTF8);

edited Sep 27 '15 at 15:32

Rory McCrossan

331,213
40
305
339

answered Sep 27 '15 at 15:06

GSIT Coder

21
1

1

I've updated it in in my code. I was a little bit lazy and just copy pasted it here (straight from the software it was using). – Jul 25 '16 at 18:45

score 2 · Accepted Answer · answered Apr 02 '12 at 07:33

2

Found the problem, the coding was fine but i had an empty cell that gave the problem.

answered Apr 02 '12 at 07:33

PandaNL

828
5
18
40

7

! coding defensively != fine – B. Clay Shannon-B. Crow Raven Jun 29 '12 at 23:52

score 1 · Answer 6 · answered Mar 15 '13 at 17:18

The line "csvFileWriter.WriteLine(dataFromGrid);" should be moved down one line below the closing bracket, else you'll get a lot of repeating results:

for (int i = 1; i <= countColumn; i++)
{
dataFromGrid = dataFromGrid + ',' + dataRowObject.Cells[i].Value.ToString();
}
csvFileWriter.WriteLine(dataFromGrid);

score 0 · Answer 7 · answered Aug 06 '17 at 01:03

0

I think this is the correct for your SaveToCSV function : ( otherwise Null ...)

 for (int i = 0; i < columnCount; i++)

Not :

 for (int i = 1; (i - 1) < DGV.RowCount; i++)

answered Aug 06 '17 at 01:03

threefx

81
1
8

score 0 · Answer 8 · answered Jul 10 '22 at 12:01

This is what I been using in my projects:

void export_csv(string file, DataGridView grid)
{
    using (StreamWriter csv = new StreamWriter(file, false))
    {
        int totalcolms = grid.ColumnCount;
        foreach (DataGridViewColumn colm in grid.Columns) csv.Write(colm.HeaderText + ',');
        csv.Write('\n');
        string data = "";
        foreach (DataGridViewRow row in grid.Rows)
        {
            if (row.IsNewRow) continue;
            data = "";
            for (int i = 0; i < totalcolms; i++)
            {
                data += (row.Cells[i].Value ?? "").ToString() + ',';
            }
            if (data != string.Empty) csv.WriteLine(data);
        }
    }

}

Exporting datagridview to csv file

8 Answers8

Linked