Hola.
Tengo un código que me exporta datos a excel, y lo hace correctamente. El
problema viene cuando tengo que exportar datos numéricos. A pesar de que el
formato de la columna es numérico (orque así se lo indico a la celda al
ponerle el style) me marca un error de :"Número almacenado como texto". No
obstante, si yo introduzco manualmente datos, me los introduce correctamente
como numérico.
¿Cómo puedo hacer para que no me ocurra esto? ¿Es alguna configuración de mi
excel o es problema de código?
Adjunto mi código abajo.
Gracias por adelantado.
using System;
using System.Data;
using System.Web.UI;
namespace ExportExcel
{
/// <summary>
/// Descripción breve de ToDataTable.
/// </summary>
public class FromDataTable
{
private static string GenerarListadoExcel(DataTable dt)
{
string strListadoExcel = "";
//Generamos la cabecera
strListadoExcel = "<tr height >";
foreach (DataColumn dc in dt.Columns)
{
strListadoExcel = strListadoExcel + "<td class='detCabecera'>" + dc.Caption
+ "</td>";
}
strListadoExcel = strListadoExcel + "</tr>";
//Generamos el listado
if (dt.Rows.Count > 0)
{
//Recorremos todas los registros (filas)
foreach (DataRow dr in dt.Rows)
{
strListadoExcel = strListadoExcel + "<tr>";
//Recorremos todas las columnas
for (int i= 0; i< dt.Columns.Count;i++)
{
DataColumn columna = dt.Columns[i];
if (columna.DataType == typeof(integer))
strListadoExcel = strListadoExcel + "<td class='detCeldaNum'>" + dr[i] +
"</td>";
else
strListadoExcel = strListadoExcel + "<td class='detCelda'>" + dr[i] +
"</td>";
}
strListadoExcel = strListadoExcel + "</tr>";
}
}
return strListadoExcel;
}
public static void GenerarExcel(DataTable dt, string nombreExcel, Page
pagina)
{
string sDate = DateTime.Now.ToString("yyyy-MM-ddThh:mm:ssZ");
string strPageBreaks = "" +
" <x:PageBreaks>" +
" <x:RowBreaks>";
string strListadoExcel = GenerarListadoExcel(dt);
//Preparamos el Response
pagina.Response.Clear();
pagina.Response.ContentEncoding = System.Text.Encoding.Default;
pagina.Response.ContentType = "application/vnd.ms-excel";
pagina.Response.AddHeader("Content-Disposition", "attachment;filename=" +
nombreExcel + ".xls");
//Imprimimos el Response
pagina.Response.Write(
"<html xmlns:v=\"urn:schemas-microsoft-com:vml\"" +
"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\"" +
"xmlns=\"
http://www.w3.org/TR/REC-html40\">" +
"<head>" +
"<meta http-equiv=Content-Type content=\"text/html; charset=iso8859-15\">" +
"<meta name=ProgId content=Excel.Sheet>" +
"<meta name=Generator content=\"Microsoft Excel 11\">" +
"<link rel=File-List href=\"Libro1_archivos/filelist.xml\">" +
"<link rel=Edit-Time-Data href=\"Libro1_archivos/editdata.mso\">" +
"<link rel=OLE-Object-Data href=\"Libro1_archivos/oledata.mso\">" +
"<!--[if !mso]>" +
"<style>" +
"v\\:* {behavior:url(#default#VML);}" +
"o\\:* {behavior:url(#default#VML);}" +
"x\\:* {behavior:url(#default#VML);}" +
".shape {behavior:url(#default#VML);}" +
"</style>" +
"<![endif]--><!--[if gte mso 9]><xml>" +
" <o:DocumentProperties>" +
" <o:Author>BGC</o:Author>" +
" <o:LastAuthor>.</o:LastAuthor>" +
" <o:LastPrinted>" + sDate + "</o:LastPrinted>" +
" <o:Created>" + sDate + "</o:Created>" +
" <o:LastSaved>" + sDate + "</o:LastSaved>" +
" <o:Version>11.6408</o:Version>" +
" </o:DocumentProperties>" +
"</xml><![endif]-->" +
"<style>" +
"<!--table" +
" {mso-displayed-decimal-separator:\"\\,\";" +
" mso-displayed-thousand-separator:\"\\.\";}" +
"@page" +
" {margin:.98in .79in .98in .79in;" +
" mso-header-margin:0in;" +
" mso-footer-margin:0in;" +
" mso-page-orientation:landscape;}" +
"tr" +
" {mso-height-source:auto;}" +
"col" +
" {mso-width-source:auto;}" +
"br" +
" {mso-data-placement:same-cell;}" +
".style0" +
//" {mso-number-format:General;" +
" {text-align:general;" +
" vertical-align:middle;" +
" white-space:normal;" +
" mso-rotate:0;" +
" mso-background-source:auto;" +
" mso-pattern:auto;" +
" color:windowtext;" +
" font-size:10.0pt;" +
" font-weight:400;" +
" font-style:normal;" +
" text-decoration:none;" +
" font-family:Arial;" +
" mso-generic-font-family:auto;" +
" mso-font-charset:0;" +
" border:none;" +
" mso-protection:locked visible;" +
" mso-style-name:Normal;" +
" mso-style-id:0;}" +
"td" +
" {mso-style-parent:style0;" +
" padding:0px;" +
" mso-ignore:padding;" +
" color:windowtext;" +
" font-size:10.0pt;" +
" font-weight:400;" +
" font-style:normal;" +
" text-decoration:none;" +
" font-family:Arial;" +
" mso-generic-font-family:auto;" +
" mso-font-charset:0;" +
" mso-number-format:General;" +
" text-align:general;" +
" vertical-align:middle;" +
" border:none;" +
" mso-background-source:auto;" +
" mso-pattern:auto;" +
" mso-protection:locked visible;" +
" white-space:normal;" +
" mso-rotate:0;}" +
".titulo" +
" {mso-style-parent:style0;" +
" font-size:14.0pt;" +
" font-weight:700;" +
" text-align:left;" +
" border-top: none;" +
" font-family:Arial, sans-serif;" +
" mso-font-charset:0;}" +
".datos" +
" {mso-style-parent:style0;" +
" font-size:11.0pt;" +
" font-family:Arial, sans-serif;" +
" mso-font-charset:0;}" +
".celda" +
" {border: .1pt solid windowtext;" +
" font-size:11.0pt;" +
" font-family:Arial, sans-serif;}" +
".detCelda" +
" {border: .1pt solid windowtext;" +
" font-size:8.0pt;" +
" font-family:Arial, sans-serif;}" +
".detCeldaNum" +
" {border: .1pt solid windowtext;" +
" mso-number-format:0;" +
" font-size:8.0pt;" +
" font-family:Arial, sans-serif;}" +
".celdaR" +
" {border: .1pt solid windowtext;" +
" font-size:9.0pt;" +
" text-align:right;" +
" font-family:Arial, sans-serif;}" +
".celdaL" +
" {border: .1pt solid windowtext;" +
" font-size:9.0pt;" +
" text-align:left;" +
" font-family:Arial, sans-serif;}" +
".atributo" +
" {border: .1pt solid windowtext;" +
" font-size:11.0pt;" +
" font-weight:700;" +
" text-align:right;" +
" font-family:Arial, sans-serif;}" +
".cabecera2" +
" {border: .1pt solid windowtext;" +
" font-size:11.0pt;" +
" font-weight:700;" +
" font-family:Arial, sans-serif;}" +
".cabecera {mso-style-parent:style0;" +
" border: .1pt solid windowtext;" +
" color:white;" +
" font-size:11.0pt;" +
" font-weight:700;" +
" font-family:Arial, sans-serif;" +
" background:black;" +
" text-align:center;" +
" mso-pattern:auto none;}" +
".detCabecera {mso-style-parent:style0;" +
" border: .1pt solid windowtext;" +
" color:Black;" +
" font-size:8.0pt;" +
" font-weight:700;" +
" font-family:Arial, sans-serif;" +
" background:#FFBC36;" +
" text-align:center;" +
" mso-pattern:auto none;}" +
".detCeldaEnvase {mso-style-parent:style0;" +
" border: .0pt solid windowtext;" +
" color:Black;" +
" font-size:8.0pt;" +
" font-weight:500;" +
" font-family:Arial, sans-serif;" +
" background:#FFCC99;" +
" text-align:center;" +
" mso-pattern:auto none;}" +
"-->" +
"</style>" +
"<!--[if gte mso 9]><xml>" +
" <x:ExcelWorkbook>" +
" <x:ExcelWorksheets>" +
" <x:ExcelWorksheet>" +
" <x:Name>" + nombreExcel + "</x:Name>" +
" <x:WorksheetOptions>" +
" <x:DefaultColWidth>10</x:DefaultColWidth>" +
" <x:StandardWidth>2925</x:StandardWidth>" +
" <x:Print>" +
" <x:ValidPrinterInfo/>" +
" <x:PaperSizeIndex>9</x:PaperSizeIndex>" +
" <x:HorizontalResolution>600</x:HorizontalResolution>" +
" <x:VerticalResolution>600</x:VerticalResolution>" +
" </x:Print>" +
" <x:Selected/>" +
" <x:Panes>" +
" <x:Pane>" +
" <x:Number>3</x:Number>" +
" <x:ActiveRow>5</x:ActiveRow>" +
" </x:Pane>" +
" </x:Panes>" +
" <x:ProtectContents>False</x:ProtectContents>" +
" <x:ProtectObjects>False</x:ProtectObjects>" +
" <x:ProtectScenarios>False</x:ProtectScenarios>" +
" </x:WorksheetOptions>" +
strPageBreaks +
" </x:ExcelWorksheet>" +
" </x:ExcelWorksheets>" +
" <x:WindowHeight>12780</x:WindowHeight>" +
" <x:WindowWidth>18555</x:WindowWidth>" +
" <x:WindowTopX>360</x:WindowTopX>" +
" <x:WindowTopY>315</x:WindowTopY>" +
" <x:ProtectStructure>False</x:ProtectStructure>" +
" <x:ProtectWindows>False</x:ProtectWindows>" +
" </x:ExcelWorkbook>" +
" <x:ExcelName>" +
" <x:Name>_FilterDatabase</x:Name>" +
" <x:Hidden/>" +
" <x:SheetIndex>1</x:SheetIndex>" +
" <x:Formula>=Hoja1!$A$1:$F$1</x:Formula>" +
" </x:ExcelName>" +
"</xml><![endif]--><!--[if gte mso 9]><xml>" +
" <o:shapedefaults v:ext=\"edit\" spidmax=\"1031\"/>" +
"</xml><![endif]--><!--[if gte mso 9]><xml>" +
" <o:shapelayout v:ext=\"edit\">" +
" <o:idmap v:ext=\"edit\" data=\"1\"/>" +
" </o:shapelayout></xml><![endif]-->" +
" <title></title>" +
"</head>" +
"<body>" +
" <table x:str border=0 cellpadding=0 cellspacing=0 width8
style='border-collapse:" +
" collapse;table-layout:fixed;width:674pt'>" +
strListadoExcel +
" </table>" +
"</body>" +
"</html>");
}
}
}
Leer las respuestas