Reducir Archivo de Excel Pesado II

Cuando tenemos un archivo de Excel demasiado pesado, es decir cuando hablamos de más de 20 megabytes el manejo del archivo se vuelve casi imposible por lo cual el código de la entrada anterior que encuentra la hoja pesada del archivo para poder saber en qué hoja hay que quitar formulas y formatos inservibles para reducir el tamaño no nos sirve de mucho, pero hay otra forma de reducir el tamaño el archivo sin necesidad de abrir el archivo.
Los pasos a seguir son los siguientes (ver en video)

    1. Hacemos una copia del archivo pesado
      1
      2
    2. Cambiamos la extensión del archivo a “.zip”
      3
    3. Descomprimimos el archivo zip
      4
    4. Abrimos la carpeta descomprimida y abrimos la carpeta “xl”
      4a
    5. Abrimos un bloc de notas y arrastramos el archivo workbook.xml para abrirlo
      4b
      4c
    6. Abrimos la carpeta “worksheets”
      5
    7. Cada archivo xml que hay en la carpeta es una hoja en el archivo de Excel, así que buscamos los más pesados, en la imagen se observa que la hoja sheet7.xml y sheet8.xml son los más pesados
      6
    8. En el archivo workbook.xml estan entre otras cosas los nombres de las hojas de la siguiente manera <sheet name=”NombreHoja” sheetId=”131″ r:id=”rId1″/> y cómo podemos ver nos dice el nombre de la hoja, entonces si queremos ver el nombre de la hoja pesada tenemos que buscar el texto “<sheet name=” y presionar la tecla Enter el número de veces que sea la hoja, para la hoja sheet7.xml presionamos 7 veces Enter, y para la hoja sheet8.xml presionamos 8 veces Enter, esto hará que nos posicionemos en la sección indicada para ver el nombre de la hoja.
      7
      8
    9. Si no sabemos el rango del área de trabajo de la hoja debemos abrir el archivo de Excel y ver el rango utilizado de la hoja, para sheet7.xml es “A1:M14” y para sheet8.xml es “A1:M9”, esto nos servirá para saber que filas y que columnas vamos a eliminar
      910
    10. Cada archivo xml (sheet1.xml, sheet2.xml, etc.) que está en la carpeta “worksheets” tiene información del contenido de la hoja, y a partir del texto <sheetData> la información se especifica de la siguiente manera
      • Una fila se escribe como <row r=”1″></row> donde r es el número de fila
      • Una columna se escribe como <col r=”A1″ t=”s” s=”1″></col> donde r es el nombre de la celda, t es el tipo de contenido y solo aparece cuando el contenido es texto(string) cuando no aparece es numérico, s es el estilo (style) del número (porcentaje, fecha, moneda, etc.) solo aparece cuando el contenido es numérico.
      • Un valor se escribe como <v></v> donde lo que está en medio de las etiquetas es el valor de la celda y siempre es un número, cuando el contenido es un texto (t=”s”) el número que aparece es el índice del texto, todos los textos escritos en las celdas del archivo de Excel se almacenan en un archivo aparte y se acceden a ellos mediante su indice.
      • Una formula se escribe como <f></f> donde lo que está en medio de las etiquetas es la fórmula de la celda, después de la formula debe haber un valor con el resultado de la formula

Si tuviéramos <row r=1><col r=”A1″ s=”1″><f>A1+B2</f><v>1000</v></col></row> indicaría que en la celda A1 esta la formula “=A1+B2” y que el resultado es “1000” y se muestra como moneda.
En base a esta información y sabiendo cual es el área de trabajo en la hoja, podemos eliminar las filas y columnas que estén de más y para ello utilizamos la aplicación TextCrawler.

A la aplicación le debemos de proporcionar las siguientes expresiones regulares para eliminar las filas y las columnas de más.

      • <row r=”1[^>]*>(.*?)</sheetData> se reemplaza con </sheetData>, el numero 1 indica la primera fila que se va a eliminar, para la hoja sheet7.xml cuyo área de trabajo es “A1:M14” el 1 se cambiara por 15 y para la hoja sheet8.xml cuyo área de trabajo es “A1:M9” se cambiara el 1 por 10
      • <c r=”A[^>]*>(.*?)</row>se reemplaza con </row>, la letra A indica la primera columna que se va a eliminar, para la hoja sheet7.xml cuya área de trabajo es “A1:M14” se cambiara la A por N, y para la hoja sheet8.xml cuya área de trabajo es “A1:M9” se cambiara la A por N

Las expresiones ya modificadas para la hoja sheet7.xml son

      • <row r=”15[^>]*>(.*?)</sheetData>
      • <c r=”N[^>]*>(.*?)</row>

Y para la hoja sheet8.xml son

    • <row r=”10[^>]*>(.*?)</sheetData>
    • <c r=”N[^>]*>(.*?)</row>
  1. En la aplicación TextCrawler hacemos lo siguiente
    • En la primera sección Seleccionamos la pestaña “Input File”
    • Seleccionamos el archivo pesado
      12
    • En la segunda sección seleccionamos la pestaña “Regular Expresion”
    • En el campo “Reg Ex” escribimos la expresión regular para eliminar filas
    • En el campo “Replace” escribimos el reemplazo
    • Hacemos clic en el botón “Replace”
      11
    • La aplicación tardara un tiempo en hacer la búsqueda y después nos pedirá confirmar el reemplazo, hacemos clic en el botón “Sí”
      13
    • La aplicación tardara un tiempo más en hacer el reemplazo y después mostrara un mensaje de los cambios que se hicieron, hacemos clic en el botón “Aceptar”
      14
    • Al final La aplicación muestra los detalles de lo que se hizo
      <15
    • Repetimos los pasos para eliminar las columnas
      16<
      18
      19
    • Repetimos los pasos para las demás hojas
      2021
  2. Abrimos el archivo zip y nos posicionamos en la carpeta “worksheets”
    22
    23
  3. Nos posicionamos en la carpeta “worksheets” de la carpeta descomprimida y arrastramos las hojas ya reducidas al archivo zip
    24
    25
    26
  4. Cerramos el archivo zip y le cambiamos la extensión al que tenía inicialmente
    27
  5. Podemos comparar los dos archivos y ver la diferencia de tamaño que hay entre ellos.
    28

    En el siguiente video se ven los pasos descritos.

Anuncios

4 pensamientos en “Reducir Archivo de Excel Pesado II

  1. Pingback: Reducir Archivo de Excel Pesado I | Código Excel

  2. He realizado el procedimiento, he intentado con filas y columnas que sé que contienen datos y hay un mensaje que no ha encontrado datos. Por qué?

  3. Excelente aporte, pude solucionar mi problema con tus recomendaciones. Borré directamente de excel las filas y columnas sin uso y se redujo dramáticamente de 41MB a 1.5MB, me fue muy útil la identificación del tamaño de cada hoja del libro de excel. Lo único distinto es que al cambiar el nombre de las hojas debí hacer una búsqueda manual para localizarla. Gracias.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s