Select separated by semicolon to xls

Question

I have the following code:

var cmd = @"SELECT
                  mt.Id + ';' + mt.Nome + ';' + mt.Cidade AS Resultado
            FROM MinhaTable mt";

var objectContext = ((IObjectContextAdapter)db).ObjectContext;
                    var result = objectContext.ExecuteStoreQuery<string>(cmd).ToList<string>();

                    FileStreamResult fileStreamResult = null;

                    using(var stream = new MemoryStream())
                    {
                        using(var csvWriter = new StreamWriter(stream, Encoding.UTF8))
                        {
                            foreach (var line in result)
                            {
                                csvWriter.WriteLine(line);
                            }
                            csvWriter.Flush();
                        }
                        fileStreamResult = new FileStreamResult(new MemoryStream(stream.ToArray()), "text/plain");
                    }

                    if (fileStreamResult == null)
                    {
                        throw new Exception("Não foi possível gerar o arquivo!");
                    }

                    var grid = new GridView();
                    grid.DataSource = fileStreamResult;
                    grid.DataBind();

                    Response.ClearContent();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment; filename=" + provaOnline.Descricao + "-" + provaOnline.DataLiberacaoInicial.ToString("dd/MM/yyyy HH-mm") + ".xls");
                    Response.ContentType = "application/ms-excel";

                    Response.Charset = "";
                    StringWriter sw = new StringWriter();
                    HtmlTextWriter htw = new HtmlTextWriter(sw);

                    grid.RenderControl(htw);

                    Response.Output.Write(sw.ToString());
                    Response.Flush();
                    Response.End();

                    return View("PrintRelExcel");

As you can see, my select returns a List<string> with values separated by a semicolon:

[1] "1;José;São Paulo"
[2] "2;Maria;São Paulo"

The goal is to turn them into one Excel as follows:

     A      B          C
  +----+--------+-----------+
1 | 1  | José   | São Paulo |
2 | 2  | Maria  | São Paulo |
  +----+--------+-----------+

But on the line I use:

grid.DataSource = fileStreamResult;

An error is generated:

Invalid data source type. It must be an Ilistsource, Ienumerable or Idatasource.

This happens, because as the own Exception already says, DataSource can only receive the above types, and fileStreamResult does not include.

Is there any other alternative to generating mine .xls ??

Answer 1

If the goal is to generate an XLS or XLSX, that which you are trying doesn’t make sense. The correct is use a Nuget package that helps you generate an Excel:

PM> Install-Package EPPlus

I rode a Helper that makes this export:

using MeuProjeto.Common.Ferramentas;
using MeuProjeto.Common.Models;
using MeuProjeto.Models;
using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.Mvc;

namespace MeuProjeto.Helpers
{
    public static class ExcelHelper
    {
        public static byte[] ExportarListaParaExcel<T>(IEnumerable<T> lista, String tituloPlanilha = "Exportação para Excel")
            where T: class, IEntidade, new()
        {
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "Eu mesmo";
                excelPackage.Workbook.Properties.Title = tituloPlanilha;
                var sheet = excelPackage.Workbook.Worksheets.Add(tituloPlanilha);
                sheet.Name = tituloPlanilha;

                var properties = ReflectionUtils.ExtrairPropertiesDeObjeto(new T()).Where(p => !p.Name.EndsWith("Id")).ToList();
                var titulos = properties.Select(p => p.AtributoDisplayName() != null ? p.AtributoDisplayName().DisplayName : p.Name).ToArray();
                var i = 1;
                foreach (var titulo in titulos)
                {
                    sheet.Cells[1, i++].Value = titulo;
                }

                var rowIndex = 2;
                foreach (var item in lista)
                {
                    var col = 1;
                    foreach (var prop in properties.Where(p => !p.Name.EndsWith("Id")))
                    {
                        var val = item.GetType().GetProperty(prop.Name).GetValue(item, null);
                        String str;

                        if (val == null)
                        {
                            str = "";
                        }
                        else if (val.GetType().IsPrimitive || val.GetType() == typeof(Decimal) || val.GetType() == typeof(String) || val.GetType() == typeof(DateTime))
                        {
                            str = val.ToString();
                        }
                        else
                        {
                            str = val.GetType().GetProperty("Nome").GetValue(val, null).ToString();
                        }
                        sheet.Cells[rowIndex, col++].Value = str ?? "";
                    }

                    rowIndex++;
                }

                return excelPackage.GetAsByteArray();
            }
        }

        public static byte[] ExportarViewModelParaExcel(object viewModel, String tituloPlanilha = "Exportação para Excel")
        {
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "Eu mesmo";
                excelPackage.Workbook.Properties.Title = tituloPlanilha;

                foreach (var objeto in ReflectionUtils.ExtrairPropertiesDeObjeto(viewModel).ToList())
                {
                    var sheet = excelPackage.Workbook.Worksheets.Add(objeto.Name);
                    sheet.Name = objeto.Name;

                    if (objeto.PropertyType.GetInterfaces().Any(t => t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>)))
                    {
                        // É uma lista
                        var lista = (IEnumerable)objeto.GetValue(viewModel, null);
                        if (lista == null) continue;
                        var fields = lista.GetType().GetGenericArguments()[0].GetFields(BindingFlags.Public | BindingFlags.Instance);
                        var titulos = fields.Select(p => p.AtributoDisplayName() != null ? p.AtributoDisplayName().DisplayName : p.Name).ToArray();
                        var i = 1;
                        foreach (var titulo in titulos)
                        {
                            sheet.Cells[1, i++].Value = titulo;
                        }

                        var rowIndex = 2;
                        foreach (var item in lista)
                        {
                            var col = 1;
                            foreach (var field in fields)
                            {
                                var val = item.GetType().GetField(field.Name).GetValue(item);
                                String str;

                                if (val == null)
                                {
                                    str = "";
                                }
                                else if (val.GetType().IsPrimitive || val.GetType() == typeof(Decimal) || val.GetType() == typeof(String) || val.GetType() == typeof(DateTime))
                                {
                                    str = val.ToString();
                                }
                                else
                                {
                                    str = val.GetType().GetField("Nome").GetValue(val).ToString();
                                }
                                sheet.Cells[rowIndex, col++].Value = str ?? "";
                            }

                            rowIndex++;
                        }
                    }
                    else
                    {
                        // É um objeto
                        var obj = objeto.GetValue(viewModel, null);
                        var fields = obj.GetType().GetFields(BindingFlags.Public | BindingFlags.Instance);
                        var titulos = fields.Select(p => new {
                            Titulo = p.AtributoDisplayName() != null ? p.AtributoDisplayName().DisplayName : p.Name, 
                            Valor = p.GetValue(obj)
                        }).ToList();

                        var i = 1;
                        foreach (var titulo in titulos)
                        {
                            sheet.Cells[i, 1].Value = titulo.Titulo;
                            sheet.Cells[i++, 2].Value = titulo.Valor;
                        }
                    }
                }

                return excelPackage.GetAsByteArray();
            }
        }
    }
}

You also need to add this static class here somewhere in your project:

public static class ReflectionUtils 
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="objeto"></param>
    /// <returns></returns>
    public static IEnumerable<PropertyInfo> ExtrairPropertiesDeObjeto(Object objeto)
    {
        return objeto.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public |
                               BindingFlags.NonPublic | BindingFlags.FlattenHierarchy);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="property"></param>
    /// <returns></returns>
    public static DisplayNameAttribute ExtrairAtributoDisplayNameDeProperty(PropertyInfo property) 
    {
        var atributo = (DisplayNameAttribute)property.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault();
        return atributo;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="field"></param>
    /// <returns></returns>
    public static DisplayNameAttribute ExtrairAtributoDisplayNameDeField(FieldInfo field)
    {
        var atributo = (DisplayNameAttribute)field.GetCustomAttributes(typeof(DisplayNameAttribute), false).FirstOrDefault();
        return atributo;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="property"></param>
    /// <returns></returns>
    public static DisplayNameAttribute AtributoDisplayName(this PropertyInfo property)
    {
        return ExtrairAtributoDisplayNameDeProperty(property);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="field"></param>
    /// <returns></returns>
    public static DisplayNameAttribute AtributoDisplayName(this FieldInfo field)
    {
        return ExtrairAtributoDisplayNameDeField(field);
    }
}

Use:

public ActionResult ExportarParaExcel() 
{
    var lista = /* Faça a sua seleção aqui */
    return File(ExcelHelper.ExportarListaParaExcel<MeuModel>(lista), System.Net.Mime.MediaTypeNames.Application.Octet, "teste.xlsx");
}

Answer 2

You should then perform the query by following the SQL standards, and then based on the result of your query format for csv or other form desired.

Searching I found even examples: Export SQL Server Data to CSV (English)

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;


public partial class CS : System.Web.UI.Page
{
    protected void ExportCSV(object sender, EventArgs e)
    {
        string constr =     ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM     Customers"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);

                        //Build the CSV file data as a Comma separated     string.
                        string csv = string.Empty;

                        foreach (DataColumn column in dt.Columns)
                        {
                            //Add the Header row for CSV file.
                            csv += column.ColumnName + ',';
                        }

                        //Add new line.
                        csv += "\r\n";

                        foreach (DataRow row in dt.Rows)
                        {
                            foreach (DataColumn column in dt.Columns)
                            {
                                //Add the Data rows.
                                csv +=     row[column.ColumnName].ToString().Replace(",", ";") + ',';
                            }

                            //Add new line.
                            csv += "\r\n";
                        }

                        //Download the CSV file.
                        Response.Clear();
                        Response.Buffer = true;
                        Response.AddHeader("content-disposition",     "attachment;filename=SqlExport.csv");
                        Response.Charset = "";
                        Response.ContentType = "application/text";
                        Response.Output.Write(csv);
                        Response.Flush();
                        Response.End();
                    }
                }
            }
        }
    }
}

And on the page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="CS" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Button Text="Export" OnClick="ExportCSV" runat="server" />
    </form>
</body>
</html>