Problems when exporting DataTable to Excel

Question

Problems when exporting DataTable to Excel

Navigation

#1 by (0 votes)

0

using System;
using System.Net;
using System.Linq;
using System.Data;
using System.Net.Mail;
using ClosedXML.Excel;
using System.Collections.Generic;

namespace Productos_Sin_Parametro_Ax
{

 class Program
  {

    static void Main(string[] args)
     {
        Program a = new Program();
        a.sendEmail("Productos sin parámetros en Axapta");
     }

    public void sendEmail(string asunto)
    {
        WMSOLEEntities db = new WMSOLEEntities();
        var values        = db.VW_PRODUCTOS_SIN_PARAMETROS.ToList();

        SmtpClient smtp = new SmtpClient();

        SmtpClient clienteSmtp = new SmtpClient();
        string emisor          = string.Empty;
        string password        = string.Empty;

        emisor   = "[email protected]";
        password = "Ole123Ole123";

        MailMessage mensaje = new MailMessage();
        mensaje.From        = new MailAddress(emisor);
        mensaje.Bcc.Add(new MailAddress("[email protected]"));
        //mensaje.Bcc.Add(new MailAddress("[email protected]"));
        //mensaje.To.Add(new MailAddress("[email protected]"));
        //mensaje.To.Add(new MailAddress("[email protected]"));
        mensaje.Subject    = asunto;
        mensaje.IsBodyHtml = true;

        List<VW_PRODUCTOS_SIN_PARAMETROS> contentList = new List<VW_PRODUCTOS_SIN_PARAMETROS>();
        List<Object> contentListToExcel = new List<Object>();

        foreach (var item in values)
        {
            contentListToExcel.Add(new
            {
                Codigo         = item.CODIGO,
                Descripcion    = item.DESCRIPCION,
                Unidad_Empaque = item.UND_EMP,
                Existencia     = item.EXISTENCIA
            });
        }

        Program b = new Program();
        b.ToDataTable(contentListToExcel);

        clienteSmtp.Host = "184.154.228.10";
        clienteSmtp.Port = 2525;
        clienteSmtp.EnableSsl = false;
        clienteSmtp.UseDefaultCredentials = false;
        clienteSmtp.Credentials = new NetworkCredential(emisor, password);
        clienteSmtp.EnableSsl = false;

        if (values.Count() > 0)
        {
            try
            {
                //clienteSmtp.Send(mensaje);
                Console.WriteLine("Correo enviado");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error al enviar el correo" + ex.Message);
            }
        }
        else
        {
            Console.WriteLine("No existen productos");
        }

    }

    public DataTable ToDataTable(List<Object> list)
    {
        DataTable MethodResult = null;

        DataTable dt = new DataTable();
        dt.Columns.Add("CODIGO");
        dt.Columns.Add("DESCRIPCION");
        dt.Columns.Add("UNIDAD EMPAQUE");
        dt.Columns.Add("EXISTENCIA");

        foreach (var s in list)
        {
            DataRow dr = dt.NewRow();
            dr[0] = s;
            dt.Rows.Add(dr);
        }

        dt.AcceptChanges();

        MethodResult = dt;

        Program c    = new Program();
        var filename = "Productos sin parámetros ";
        c.ToExcelFile(dt, filename);

        return MethodResult;

    }

    public bool ToExcelFile(DataTable dt, string filename)
    {
        bool Success = false;

        XLWorkbook wb = new XLWorkbook();

        wb.Worksheets.Add(dt, "Sheet 1");

        if (filename.Contains("."))
        {
            int IndexOfLastFullStop = filename.LastIndexOf('.');

            filename = filename.Substring(0, IndexOfLastFullStop) + ".xlsx";
        }

        filename = filename + ".xlsx";

        wb.SaveAs(filename);

        Success = true;

        return Success;
    }

  }
}

c# datatable excel

asked by Hommy De Jesús 16.10.2018 в 16:39

source

1 answer

Make a multi-line text create div that father of 2 of the 4 brothers

score 0 · Accepted Answer

because that's exactly what you do in your code, "in column 0 put the complete object":

DataRow dr = dt.NewRow();
dr[0] = s;
dt.Rows.Add(dr);

not because the object has the same name in its properties as the columns means that it will do it automatically, you must put in each column the value you want, something like this:

 foreach (var s in list)
    {
        DataRow dr = dt.NewRow();
        dr["CODIGO"] = s.Codigo;
        dr["DESCRIPCION"]= s.Descripcion;
        dr["UNIDAD EMPAQUE"]= s.Unidad_Empaque;
        dr["EXISTENCIA"]= s.Existencia;
        dt.Rows.Add(dr);
    }