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());
}
Let us go to the page load event, fetch employee data, and bind the Data GridView. Then, on the button click event handler, write the below code. Now, run the application. When we click on the "Export To CSV" button, it will ask where to save the file.
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With