export datatable to csv

export datatable to csv

You could go through the rows and columns like this.

void Main()
{
	var dt = new DataTable();
	dt.Columns.Add("Id", typeof(int));
	dt.Columns.Add("Name", typeof(string));
	
	var row = dt.NewRow();
	row["Id"] = 1;
	row["Name"] = "one";
	dt.Rows.Add(row);

	row = dt.NewRow();
	row["Id"] = 2;
	row["Name"] = "two";
	dt.Rows.Add(row);
	
	using (var writer = new StringWriter())
	using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
	{		
		foreach (DataColumn dc in dt.Columns)
		{			
			csv.WriteField(dc.ColumnName);
		}
		csv.NextRecord();
		
		foreach (DataRow dr in dt.Rows)
		{			
			foreach (DataColumn dc in dt.Columns)
			{
				csv.WriteField(dr[dc]);
			}
			csv.NextRecord();
		}

		writer.ToString().Dump();
	}
}

Or you could use a DataReader and to it like this.

void Main()
{
	var dt = new DataTable();
	dt.Columns.Add("Id", typeof(int));
	dt.Columns.Add("Name", typeof(string));
	
	var row = dt.NewRow();
	row["Id"] = 1;
	row["Name"] = "one";
	dt.Rows.Add(row);

	row = dt.NewRow();
	row["Id"] = 2;
	row["Name"] = "two";
	dt.Rows.Add(row);
	
	using (var writer = new StringWriter())
	using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
	{
		using (var reader = dt.CreateDataReader())
		{
			for (var i = 0; i < reader.FieldCount; i++)
			{
				csv.WriteField(reader.GetName(i));
			}
			csv.NextRecord();
			
			while (reader.Read())
			{
				csv.WriteField(reader.GetFieldValue<int>("Id"));
				csv.WriteField(reader.GetFieldValue<string>("Name"));
				csv.NextRecord();
			}
		}

		writer.ToString().Dump();
	}
}

If there are better ways, let me know. I'm not too familiar with data tables.

 

datatable转换为csv对应的字节流

  public static byte[] DataTableToCsvBytes(DataTable dt)
        {
            if (null == dt)
                return Array.Empty<byte>();
            using (var memory = new MemoryStream())
            {
                using (var writer = new StreamWriter(memory))
                {
                    var config = new CsvConfiguration(CultureInfo.InvariantCulture);
                    config.SanitizeForInjection = true;
                    config.InjectionEscapeCharacter = '\'';
                    using (var csv = new CsvWriter(writer, config))
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            csv.WriteField(dc.ColumnName);
                        }
                        csv.NextRecord();
                        foreach (DataRow dr in dt.Rows)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                csv.WriteField(dr[dc]);
                            }
                            csv.NextRecord();
                        }

                        writer.Flush();
                    }
                }
                return memory.ToArray();
            }
        }