Convert LINQ result to CSV or Excel

Question

Using a LINQ query I need to export to Excel when a WebApi method is called. I have built the LINQ query that will return the correct data, now I need it to export to .csv or Excel file format.

I have tried using MemoryStream and StreamWriter but I think I am just chasing my tail now.

[HttpGet]
[Route("Download")]
public Task<IActionResult> Download(int memberId)
{
    var results = (from violations in _db.tblMappViolations
                   where violations.MemberID == memberId
                   select new IncomingViolations
                       {
                           Contact = violations.ContactName,
                           Address = violations.str_Address,
                           City = violations.str_City,
                           State = violations.str_State,
                           Zip = violations.str_Zipcode,
                           Country = violations.str_Country,
                           Phone = violations.str_Phone,
                           Email = violations.str_Email,
                           Website = violations.str_WebSite,
                       }).FirstOrDefault();

    MemoryStream stream = new MemoryStream(results);
    StreamWriter writer = new StreamWriter(stream);    

    writer.Flush();
    stream.Position = 0;

    FileStreamResult response = File(stream, "application/octet-stream"); 
    response.FileDownloadName = "violations.csv";

    return response;
}

Answer 1

Here is how you can send CSV file to the user from server.

string attachment = "attachment; filename=MyCsvLol.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");

var sb = new StringBuilder();
// Add your data into stringbuilder
sb.Append(results.Contact);
sb.Append(results.Address);
sb.Append(results.City);
// and so on
HttpContext.Current.Response.Write(sb.ToString());

For Sending it from API

MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
// Write Your data here in writer
writer.Write("Hello, World!");
writer.Flush();
stream.Position = 0;

HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new StreamContent(stream);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "Export.csv" };
return result;

Update:-

public HttpResponseMessage Download()
{
    var results = (from violations in _db.tblMappViolations
       where violations.MemberID == memberId
       select new IncomingViolations
           {
               Contact = violations.ContactName,
               Address = violations.str_Address,
               City = violations.str_City,
               State = violations.str_State,
               Zip = violations.str_Zipcode,
               Country = violations.str_Country,
               Phone = violations.str_Phone,
               Email = violations.str_Email,
               Website = violations.str_WebSite,
           });
    var sb = new StringBuilder();
    MemoryStream stream = new MemoryStream();
    StreamWriter writer = new StreamWriter(stream);
    foreach(var tempResult in results)
    {
        sb.Append(tempResult.Contact+",");
        sb.Append(tempResult.Address+",");
        sb.Append(tempResult.City+",");
        sb.Append(tempResult.State+",");
        sb.Append(tempResult.Zip+",");
        sb.Append(tempResult.Country+",");
        sb.Append(tempResult.Phone+",");
        sb.Append(tempResult.Email+",");
        sb.Append(tempResult.Website+",");
        sb.Append(Enviroment.NewLine);
    }
    writer.Write(sb.ToString());
    writer.Flush();
    stream.Position = 0;

    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
    result.Content = new StreamContent(stream);
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/csv");
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "Export.csv" };

    return result;
}

Answer 2

First, to reuse the code in other areas, always create helper classes.

I adopted this method of converting list into a stream with headers as property names, if you want a file from this, essentially, I would just add another step to this:

STEP 1:

public static Stream ConvertToCSVStream<T>(IEnumerable<T> objects)
       {
          Type itemType = typeof(T);
          var properties = itemType.GetProperties();

        var mStream = new MemoryStream();
        StreamWriter sWriter = new StreamWriter(mStream);

        var values = objects.Select(o =>
        {
            return string.Join(",", properties.Select(p =>
            {
                var value = p.GetValue(o).ToString();
                if (!Regex.IsMatch(value, "[,\"\\r\\n]"))
                {
                    return value;
                }

                value = value.Replace("\"", "\"\"");

                return string.Format("\"{0}\"", value);

            })) + sWriter.NewLine;
        });

        var valuesInStrings = values.Aggregate((current, next) => current + next);
        try
        {
            sWriter.Write(string.Join(",", properties.Select(x => x.Name.Replace("_", " "))) + sWriter.NewLine);

            sWriter.Write(valuesInStrings);
        }
        catch (Exception e)
        {
            mStream.Close();
            throw e;
        }

        sWriter.Flush();
        mStream.Position = 0;

        return mStream;
    }

if your data is text, just convert it directly to a file result but if not, you must convert it to binary array and write it to stream, refer to this article for converting it to binary data, in our case, for csv, you could just use the FileStream result that you've implemented in a separate method:

STEP 2:

 public FileStreamResult CreateFile(MemoryStream mStream, string path, string name)
        {
            //set values, names, content type, etc 
            //return filestream
        }

or any other method you find better.

Answer 3

Save your result in a DataTable and then just use this

XLWorkbook workbook = new XLWorkbook();
DataTable table = GetYourTable();
workbook.Worksheets.Add(table);

And you should definitely use stream writer for this if you know which file its going to write to from the start, else stream reader and then stream writer.