Export csv

Hi experts,

.net framework 1.1
ASP.Net - C#

I would like the write a function for exporting a datatable to csv file. The csv file will be pop up for user to choose "open" or "save". However, I'm not exporting the data to csv directly. I need to:

1) add a custom column, say, between column 10 and 11 of the datatable, add 1 column and assign a value to the column to each row myself.
2) use another function to format some of the columns

I totally have no idea about this, would anyone give me a hand?

Thanks!!!
kennysflauAsked:
Who is Participating?
 
monarch_ilhanConnect With a Mentor Commented:
You can not add column to a specific postion. There is no Insert function of Datatable Columns collection.
So it will be added just after last column of datatable.

the below code, adds a new column to tha datatable (dt) and in foreach row iteration, we set new column value and change another column value by using its original value and adding another column data value.


DataColumn col=new DataColumn("MyCustomColumn");
dt.Columns.Add(col);

foreach (DataRow row in dt.Rows)
{
  row["MyCustomColumn"]="My new value";
 row["AnotherColumn"]=row["AnotherColumn"].ToString()+"-"+row["AnotherColumn2"].ToString();
}

After all,

To export this as csv


            StreamWriter sw = new StreamWriter(Server.MapPath("MyOutputFolderDir/") + "test.csv");
            StringBuilder sbColumns = new StringBuilder();
 
            foreach (DataColumn col in dt.Columns)
            {
                sbColumns.Append(col.ColumnName).Append(",");
                
            }
            
            sw.WriteLine(sbColumns.ToString().TrimEnd(','));
            
            
 
            foreach (DataRow drow in dt.Rows)
            {
                StringBuilder sbRows = new StringBuilder();
                foreach (DataColumn col in dt.Columns)
                {
                    sbRows.Append(drow[col.ColumnName] == DBNull.Value ? "" : drow[col.ColumnName].ToString()).Append(",");
                }
                sw.WriteLine(sbRows.ToString().TrimEnd(','));
 
            }
            sw.Close();
            FileInfo fi = new FileInfo(Server.MapPath("MyOutputFolderDir/") + "test.csv");
            Response.Clear();
 
            Response.AddHeader("Content-Disposition", "attachment; filename=" + fi.Name);
 
            Response.AddHeader("Content-Length", fi.Length.ToString());
 
            Response.ContentType = "application/vnd.ms-excel";
 
            Response.WriteFile(fi.FullName);
 
            Response.End();

Open in new window

0
 
kennysflauAuthor Commented:
Is it possible that not to save the file on the server but just directly send to the client?
0
 
kennysflauAuthor Commented:
I've amended it so that it doesn't create a file on the server.
MemoryStream stream = new MemoryStream();
StreamWriter sw = new StreamWriter(stream);
StringBuilder sbColumns = new StringBuilder();
 
foreach (DataColumn col in dt.Columns)
{
	sbColumns.Append(col.ColumnName).Append(",");
	
}
 
sw.WriteLine(sbColumns.ToString().TrimEnd(','));
 
 
 
foreach (DataRow drow in dt.Rows)
{
	StringBuilder sbRows = new StringBuilder();
	foreach (DataColumn col in dt.Columns)
	{
		sbRows.Append(drow[col.ColumnName] == DBNull.Value ? "" : drow[col.ColumnName].ToString()).Append(",");
	}
	sw.WriteLine(sbRows.ToString().TrimEnd(','));
 
}
sw.Flush();
sw.Close();
 
byte[] byteArray = stream.ToArray();
stream.Flush();
stream.Close();
 
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=test.csv");
Response.AddHeader("Content-Length", byteArray.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(byteArray);
Response.Flush();
Response.End();

Open in new window

0
All Courses

From novice to tech pro — start learning today.