Eliminar saltos de línea en celdas Excel

1.  Seleccionamos el rango de celdas en donde deseamos quitar los saltos de linea.
2.  Pulsamos Ctrl + B para entrar en el cuadro de diálogo Buscar y reemplazar. 
3.  En la ficha Reemplazar y en el apartado Buscar, pulsamos Ctrl + J para insertar el código de salto de línea. En Reemplazar con, escribimos un espacio o dejamos en blanco. 
4. Terminamos pulsando el botón Reemplazar todos y cerramos el cuadro de diálogo.

o

Con una fórmula: SUSTITUIR y CARACTER

Cuando queramos conservar las celdas originales y copiarlas en otro rango en una sola fila, usaremos esta fórmula:


=SUSTITUIR(B2;CARACTER(10);"  ")    

Exportar Lista Objeto a Excel C#

public void ExportToExcel<T>(List<T> list)

    {

        int columnCount = 0;

        DateTime StartTime = DateTime.Now;

        StringBuilder rowData = new StringBuilder();

        PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        rowData.Append("<Row ss:StyleID=\"s62\">");

        foreach (PropertyInfo p in properties)

        {

            if (p.PropertyType.Name != "EntityCollection`1" && p.PropertyType.Name != "EntityReference`1" && p.PropertyType.Name != p.Name)

            {

                columnCount++;

                rowData.Append("<Cell><Data ss:Type=\"String\">" + p.Name + "</Data></Cell>");

            }

            else

                break;

        }

        rowData.Append("</Row>");

        foreach (T item in list)

        {

            rowData.Append("<Row>");

            for (int x = 0; x < columnCount; x++) //each (PropertyInfo p in properties)

            {

                object o = properties[x].GetValue(item, null);

                string value = o == null ? "" : o.ToString();

                rowData.Append("<Cell><Data ss:Type=\"String\">" + value + "</Data></Cell>");

            }

            rowData.Append("</Row>");

        }

        var sheet = @"<?xml version=""1.0""?>

<?mso-application progid=""Excel.Sheet""?>

<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""

xmlns:o=""urn:schemas-microsoft-com:office:office""

xmlns:x=""urn:schemas-microsoft-com:office:excel""

xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""

xmlns:html=""http://www.w3.org/TR/REC-html40"">

<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">

<Author>MSADMIN</Author>

<LastAuthor>MSADMIN</LastAuthor>

<Created>2011-07-12T23:40:11Z</Created>

<Company>Microsoft</Company>

<Version>12.00</Version>

</DocumentProperties>

<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">

<WindowHeight>6600</WindowHeight>

<WindowWidth>12255</WindowWidth>

<WindowTopX>0</WindowTopX>

<WindowTopY>60</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

<Style ss:ID=""Default"" ss:Name=""Normal"">

<Alignment ss:Vertical=""Bottom""/>

<Borders/>

<Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>

<Interior/>

<NumberFormat/>

<Protection/>

</Style>

<Style ss:ID=""s62"">

<Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""

ss:Bold=""1""/>

</Style>

</Styles>

<Worksheet ss:Name=""Sheet1"">

<Table ss:ExpandedColumnCount=""" + (properties.Count() + 1) + @""" ss:ExpandedRowCount=""" + (list.Count() + 1) + @""" x:FullColumns=""1""

x:FullRows=""1"" ss:DefaultRowHeight=""15"">

" + rowData.ToString() + @"

</Table>

<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">

<PageSetup>

<Header x:Margin=""0.3""/>

<Footer x:Margin=""0.3""/>

<PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>

</PageSetup>

<Print>

<ValidPrinterInfo/>

<HorizontalResolution>300</HorizontalResolution>

<VerticalResolution>300</VerticalResolution>

</Print>

<Selected/>

<Panes>

<Pane>

<Number>3</Number>

<ActiveCol>2</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name=""Sheet2"">

<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""

x:FullRows=""1"" ss:DefaultRowHeight=""15"">

</Table>

<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">

<PageSetup>

<Header x:Margin=""0.3""/>

<Footer x:Margin=""0.3""/>

<PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>

</PageSetup>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name=""Sheet3"">

<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1""

x:FullRows=""1"" ss:DefaultRowHeight=""15"">

</Table>

<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">

<PageSetup>

<Header x:Margin=""0.3""/>

<Footer x:Margin=""0.3""/>

<PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>

</PageSetup>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>";

        System.Diagnostics.Debug.Print(StartTime.ToString() + " - " + DateTime.Now);

        System.Diagnostics.Debug.Print((DateTime.Now - StartTime).ToString());

        string attachment = "attachment; filename=Report.xml";

        HttpContext.Current.Response.ClearContent();

        HttpContext.Current.Response.AddHeader("content-disposition", attachment);

        HttpContext.Current.Response.Write(sheet);

        HttpContext.Current.Response.ContentType = "application/ms-excel";

        HttpContext.Current.Response.End();

    }

USO DEL METODO:

  ExportToExcel(TuLista);

Leer Archivo Excel utilizando LinqToExcel

Se descarga el nuget  https://www.nuget.org/packages/LinqToExcel/

Ejemplo de implementacion:

        public int LeerDocumentoExcel(ref List<ComplementoPagoImportadoExcel> LCPIE, string RutaArchivo, out string MensajeError)

        {

            try

            {

                MensajeError = string.Empty;

                var book = new ExcelQueryFactory(RutaArchivo);

                var resultado = (from row in book.Worksheet("Hoja1")

                                 let item = new ComplementoPagoImportadoExcel

                                 {

                                     //ENCABEZADO DE DOCUMENTO

                                     Serie = row["SERIE"].Cast<string>(),

                                     Cliente = row["CLIENTE"].Cast<string>(),

                                     Bodega = row["BODEGA"].Cast<string>(),

                                     Fecha = row["FECHA"].Cast<DateTime>(),

                                     FormaPago = row["FORMAPAGO"].Cast<int>(),

                                     Expedido = row["EXPEDIDO"].Cast<string>(),

                                     FechaPago = row["FECHA DE PAGO"].Cast<DateTime>(),

                                     Moneda = row["MONEDA"].Cast<string>(),

                                     TipoCambio = row["TIPO DE CAMBIO"].Cast<int>(),

                                     CondicionesPago = row["CONDICIONES PAGO"].Cast<string>(),

                                     Referencia = row["REFERENCIA"].Cast<string>(),

                                     ExcentoIva = row["EXCENTO IVA"].Cast<string>(),

                                     TazaIva = row["TASA IVA"].Cast<decimal>(),

                                     TazarIva = row["TASAR IVA"].Cast<decimal>(),

                                     TipoRelacion = row["TIPO DE RELACION "].Cast<string>(),

                                     UsoDeCFDI = row["USO DE CFDI"].Cast<string>(),

                                     Notas = row["NOTAS"].Cast<string>(),

                                     Confirmacion = row["CONFIRMACIÓN"].Cast<string>(),

                                     MedioPago = row["MEDIO DE PAGO"].Cast<string>(),

                                     //COMPLEMENTO DE PAGO

                                     FechaPagoCP = row["FECHA DE PAGO"].Cast<DateTime>(),

                                     HoraPago = row["HORA PAGO"].Cast<DateTime>(),

                                     MonedaPago = row["MONEDA PAGO"].Cast<string>(),

                                     TipoPago = row["TIPO DE PAGO"].Cast<string>(),

                                     FormaPagoCP = row["FORMA DE PAGO"].Cast<string>(),

                                     Monto = row["MONTO"].Cast<decimal>(),

                                     NumeroOpera = row["NUMERO OPERACIÓN"].Cast<string>(),

                                     RFCEmisor = row["Rfc Emisor:"].Cast<string>(),

                                     NoBanco = row["Nom Banco:"].Cast<string>(),

                                     CuentaOrdenante = row["Cuenta Ordenante:"].Cast<string>(),

                                     RfcBeneficiario = row["Rfc Beneficiario:"].Cast<string>(),

                                     CuentaBeneficiaria = row["Cuenta Beneficiario:"].Cast<string>(),

                                     TipoCadenaPago = row["Tipo Cadena Pago:"].Cast<string>(),

                                     CertificadoPago = row["Certificado de Pago:"].Cast<string>(),

                                     CadenaPago = row["Cadena Pago:"].Cast<string>(),

                                     SelloPago = row["Sello Pago:"].Cast<string>(),

                                     //DETALLE COMPLEMENTO PAGO

                                     SerieDCP = row["SERIE"].Cast<string>(),

                                     FolioDCP = row["FOLIO"].Cast<string>(),

                                     UUIDDCP = row["UUID"].Cast<string>(),

                                     MonedaDCP = row["MONEDA"].Cast<string>(),

                                     TipoCambioDCP = row["TIPO CAMBIO"].Cast<int>(),

                                     ParcialidadCDP = row["PARCIALIDAD"].Cast<int>(),

                                     SaldoAnteriorDCP = row["SALDO ANTERIOR"].Cast<decimal>(),

                                     ImportePagadoDCP = row["IMPORTE PAGADO"].Cast<decimal>(),

                                     SaldoDCP = row["SALDO"].Cast<decimal>()

                                 }

                                 //where (item.Serie != null) || (item.Cliente != null) || (item.Bodega != null)

                                 select item).ToList();

                var aux = (from x in resultado

                           where (x.Serie != null) || (x.Cliente != null) || (x.Bodega != null)

                           select x).ToList();

                book.Dispose();

                LCPIE = aux;

                return 0;

            }

            catch (Exception e)

            {

                MensajeError = e.Message;

                return -1;

            }

        }