Tema: Exportar datos a Excel
Ver Mensaje Individual
  #1 (permalink)  
Antiguo 03/06/2010, 11:05
Avatar de DanX03
DanX03
 
Fecha de Ingreso: septiembre-2007
Ubicación: México D.F.
Mensajes: 148
Antigüedad: 17 años, 3 meses
Puntos: 3
Exportar datos a Excel
Hola que tal tengo el siguiente codigo en asp

Código ASP: 
Ver original
  1. fechaini=request.Form("fechaini")
  2. fechafin=request.Form("fechafin")
  3.  
  4. Response.Clear()
  5. Response.ContentType = "application/vnd.ms-excel" 
  6. Response.Addheader "Content-Disposition", "atachment; filename= " & FileName
  7. Response.write "<?xml version='1.0'encoding='ISO-8859-1'?>" 
  8. Response.write "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'>" 
  9. Response.write "<ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'/>" 
  10. Response.write "xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'"
  11.  
  12. Response.write "<Styles>" 
  13.  
  14. Response.write "<Style ss:ID='s21'>"
  15. Response.write "<Alignment ss:Horizontal='Center' ss:Vertical='Bottom' ss:WrapText='1'/>"
  16. Response.write "<Borders>"
  17. Response.write "<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1' "
  18. Response.write "ss:Color='#000000'/>"
  19. Response.write "<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1' "
  20. Response.write "ss:Color='#000000'/>"
  21. Response.write "<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1' "
  22. Response.write "ss:Color='#000000'/>"
  23. Response.write "<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1' "
  24. Response.write "ss:Color='#000000'/>"
  25. Response.write "</Borders>"
  26. Response.write "<Font ss:Color='#FFFFFF' ss:Bold='1'/>"
  27. Response.write "<Interior ss:Color='#800000' ss:Pattern='Solid'/>"
  28. Response.write "</Style>"
  29.  
  30. Response.write " <Style ss:ID='s22'>"
  31. Response.write "<Alignment ss:Horizontal='Center' ss:Vertical='Bottom' ss:WrapText='1'/>"
  32. Response.write "<Borders>"
  33. Response.write "<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1' "
  34. Response.write "ss:Color='#000000'/>"
  35. Response.write "<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1' "
  36. Response.write "ss:Color='#000000'/>"
  37. Response.write "<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1' "
  38. Response.write "ss:Color='#000000'/>"
  39. Response.write "<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1' "
  40. Response.write "ss:Color='#000000'/>"
  41. Response.write "</Borders>"
  42. Response.write "<Font ss:Color='#FFFFFF' ss:Bold='1'/>"
  43. Response.write "<Interior ss:Color='#008000' ss:Pattern='Solid'/>"
  44. Response.write "</Style>"
  45.  
  46. Response.write "</Styles>"
  47.  
  48. Response.write "<Worksheet ss:Name='Activacion Tarjeta'>" 
  49. Response.write "<Table>" 
  50. Response.write "<Column  ss:AutoFitWidth='0' ss:Width='120' ss:Span='250'/>"
  51.  
  52.  
  53. conectar()
  54.  
  55.  
  56. sql="select a.no_tarjeta,a.nombre,a.edad,a.calle,a.no_exterior,a.no_interior,a.colonia,a.codpos,a.municipio,a.estado,a.lada,a.telefono,"&_
  57. "a.correoe,a.fecha_captura,b.usuario from (select no_tarjeta,nombre,edad,calle,no_exterior,no_interior,colonia,codpos,municipio,estado,"&_
  58. "lada,telefono,correoe,convert(varchar,fecha_actualizacion,103)fecha_captura,usuario from politicos.dbo.prospectos_oaxaca_progreso where "&_
  59. "md_status=243 and fecha_actualizacion between convert(datetime,'"&fechaini&"',103) and convert(datetime,'"&fechafin&"',103)+1) a left join "&_
  60. "(select idusuario,'['+idusuario+'] - '+ltrim(rtrim(nombre))+' '+ltrim(rtrim(paterno))+' '+ltrim(rtrim(materno))as usuario from prod.rh.usuarios)b on a.usuario=b.idusuario"
  61. 'response.Write(sql)
  62. 'response.End()
  63. rs.Open sql,conn
  64.  
  65. Response.write "<Row>"
  66. Response.write "<Cell ss:StyleID='s21'><Data ss:Type='String'>No. Tarjeta</Data></Cell>"
  67. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Nombre</Data></Cell>"
  68. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Edad</Data></Cell>"
  69. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Calle</Data></Cell>"
  70. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>No. Exterior</Data></Cell>"
  71. Response.write "<Cell ss:StyleID='s21'><Data ss:Type='String'>No. Interior</Data></Cell>"
  72. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Colonia</Data></Cell>"
  73. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>C. P.</Data></Cell>"
  74. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Municipio</Data></Cell>"
  75. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Estado</Data></Cell>"
  76. Response.write "<Cell ss:StyleID='s21'><Data ss:Type='String'>Lada</Data></Cell>"
  77. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Telefono</Data></Cell>"
  78. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Correo-e</Data></Cell>"
  79. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Fecha Captura</Data></Cell>"
  80. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>[Login] - Usuario</Data></Cell>"
  81. Response.write "</Row>"
  82.  
  83.  
  84. do while not rs.EOF
  85.  
  86. Response.write "<Row>"
  87. Response.write "<Cell><Data ss:Type='String'>"&rs(0)&"</Data></Cell>"
  88. Response.write "<Cell><Data ss:Type='String'>"&rs(1)&"</Data></Cell>"
  89. Response.write "<Cell><Data ss:Type='String'>"&rs(2)&"</Data></Cell>"
  90. Response.write "<Cell><Data ss:Type='String'>"&rs(3)&"</Data></Cell>"
  91. Response.write "<Cell><Data ss:Type='String'>"&rs(4)&"</Data></Cell>"
  92. Response.write "<Cell><Data ss:Type='String'>"&rs(5)&"</Data></Cell>"
  93. Response.write "<Cell><Data ss:Type='String'>"&rs(6)&"</Data></Cell>"
  94. Response.write "<Cell><Data ss:Type='String'>"&rs(7)&"</Data></Cell>"
  95. Response.write "<Cell><Data ss:Type='String'>"&rs(8)&"</Data></Cell>"
  96. Response.write "<Cell><Data ss:Type='String'>"&rs(9)&"</Data></Cell>"
  97. Response.write "<Cell><Data ss:Type='String'>"&rs(10)&"</Data></Cell>"
  98. Response.write "<Cell><Data ss:Type='String'>"&rs(11)&"</Data></Cell>"
  99. Response.write "<Cell><Data ss:Type='String'>"&rs(12)&"</Data></Cell>"
  100. Response.write "<Cell><Data ss:Type='String'>"&rs(13)&"</Data></Cell>"
  101. Response.write "<Cell><Data ss:Type='String'>"&rs(14)&"</Data></Cell>"
  102.  
  103. Response.write "</Row>"
  104.  
  105. rs.Movenext
  106. loop
  107. desconectar()
  108.  
  109. Response.write "</Table>" 
  110. Response.write "</Worksheet>" 
  111.  
  112. '***************************************************************************
  113.  
  114. Response.write "<Worksheet ss:Name='Nuevas Tarjetas'>" 
  115. Response.write "<Table>" 
  116. Response.write "<Column  ss:AutoFitWidth='0' ss:Width='120' ss:Span='250'/>"
  117.  
  118.  
  119. conectar()
  120.  
  121.  
  122. sql="select a.no_tarjeta,a.nombre,a.edad,a.calle,a.no_exterior,a.no_interior,a.colonia,a.codpos,a.municipio,a.estado,a.lada,a.telefono,"&_
  123. "a.correoe,a.fecha_captura,b.usuario from (select no_tarjeta,nombre,edad,calle,no_exterior,no_interior,colonia,codpos,municipio,estado,"&_
  124. "lada,telefono,correoe,convert(varchar,fecha_actualizacion,103)fecha_captura,usuario from politicos.dbo.prospectos_oaxaca_progreso where "&_
  125. "md_status=100 and fecha_actualizacion between convert(datetime,'"&fechaini&"',103) and convert(datetime,'"&fechafin&"',103)+1) a left join "&_
  126. "(select idusuario,'['+idusuario+'] - '+ltrim(rtrim(nombre))+' '+ltrim(rtrim(paterno))+' '+ltrim(rtrim(materno))as usuario from prod.rh.usuarios)b on a.usuario=b.idusuario"
  127. rs.Open sql,conn
  128.  
  129. Response.write "<Row>"
  130. Response.write "<Cell ss:StyleID='s21'><Data ss:Type='String'>No. Tarjeta</Data></Cell>"
  131. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Nombre</Data></Cell>"
  132. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Edad</Data></Cell>"
  133. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Calle</Data></Cell>"
  134. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>No. Exterior</Data></Cell>"
  135. Response.write "<Cell ss:StyleID='s21'><Data ss:Type='String'>No. Interior</Data></Cell>"
  136. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Colonia</Data></Cell>"
  137. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>C. P.</Data></Cell>"
  138. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Municipio</Data></Cell>"
  139. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Estado</Data></Cell>"
  140. Response.write "<Cell ss:StyleID='s21'><Data ss:Type='String'>Lada</Data></Cell>"
  141. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Telefono</Data></Cell>"
  142. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Correo-e</Data></Cell>"
  143. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>Fecha Captura</Data></Cell>"
  144. Response.write "<Cell ss:StyleID='s22'><Data ss:Type='String'>[Login] - Usuario</Data></Cell>"
  145. Response.write "</Row>"
  146.  
  147.  
  148. do while not rs.EOF
  149.  
  150. Response.write "<Row>"
  151. Response.write "<Cell><Data ss:Type='String'>"&rs(0)&"</Data></Cell>"
  152. Response.write "<Cell><Data ss:Type='String'>"&rs(1)&"</Data></Cell>"
  153. Response.write "<Cell><Data ss:Type='String'>"&rs(2)&"</Data></Cell>"
  154. Response.write "<Cell><Data ss:Type='String'>"&rs(3)&"</Data></Cell>"
  155. Response.write "<Cell><Data ss:Type='String'>"&rs(4)&"</Data></Cell>"
  156. Response.write "<Cell><Data ss:Type='String'>"&rs(5)&"</Data></Cell>"
  157. Response.write "<Cell><Data ss:Type='String'>"&rs(6)&"</Data></Cell>"
  158. Response.write "<Cell><Data ss:Type='String'>"&rs(7)&"</Data></Cell>"
  159. Response.write "<Cell><Data ss:Type='String'>"&rs(8)&"</Data></Cell>"
  160. Response.write "<Cell><Data ss:Type='String'>"&rs(9)&"</Data></Cell>"
  161. Response.write "<Cell><Data ss:Type='String'>"&rs(10)&"</Data></Cell>"
  162. Response.write "<Cell><Data ss:Type='String'>"&rs(11)&"</Data></Cell>"
  163. Response.write "<Cell><Data ss:Type='String'>"&rs(12)&"</Data></Cell>"
  164. Response.write "<Cell><Data ss:Type='String'>"&rs(13)&"</Data></Cell>"
  165. Response.write "<Cell><Data ss:Type='String'>"&rs(14)&"</Data></Cell>"
  166.  
  167. rs.Movenext
  168. loop
  169. desconectar()
  170.  
  171. Response.write "</Table>" 
  172. Response.write "</Worksheet>" 
  173.  
  174. Response.write "</Workbook>"

Y me marca un error la hora de generar la tabla, he buscado en toda la pagina y no encuentro el error.