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()); }8 Answers
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.
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...
4 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(""); }Found the problem, the coding was fine but i had an empty cell that gave the problem.
1Your 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);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);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++)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); } }
}
