Más

Cree una hoja de Excel dinámica vinculada a los atributos de un shapefile

Cree una hoja de Excel dinámica vinculada a los atributos de un shapefile


Estoy tratando de crear una hoja de Excel que se actualiza automáticamente cuando se actualizan las características de un shapefile. Sé que el archivo .dbf del shapefile está actualizado, pero necesito que la hoja de Excel esté formateada para fines de presentación (bordes, encabezados de columna significativos en negrita, etc.). En este momento, he creado una hoja de Excel que se vincula a los campos de dbf pero eso no es perfecto ... es decir, cuando se elimina una característica del shapefile, ahora tiene campos de Excel vinculados a campos de dbf que ya no existen. Alguien tiene mejores ideas?


Sé que esta es una pregunta muy antigua, pero quería compartir una forma bastante indirecta de cargar dbfs de shapefile en Excel usando ODBC y Power Query.

La información a continuación es muy pesada en Excel y tal vez no sea tan adecuada para GIS.SE, pero creo que todavía hay muchas personas que necesitan presentar datos de shapefile vinculados con formato y características de Excel. Lo encuentro poderoso (aunque hacky) porque puede hacer todas sus consultas basadas en el espacio y la edición de datos en un programa GIS y actualizar inmediatamente los resultados en una hoja de cálculo bien formateada con columnas renombradas listas para su presentación o análisis adicional, sin necesidad de preocuparse si usted tengo los últimos valores, etc.

En esencia, es como cargar una VISTA SQL en Excel.


Pros

  • Datos dinámicos de shapefile (presione Alt-F5 para actualizar)
  • Le permite tener shapefile simultáneamente abierto / editado en otro programa
  • Conserve los alias "descriptivos" para los nombres de columna de los archivos de formas de 10 caracteres sin necesidad de cambiar el nombre cada vez
  • Filtrar previamente los datos para que solo cargue las columnas relevantes
  • Mantenga las funciones de Excel incluso si la fuente de datos o los valores cambian (por ejemplo, tablas dinámicas, formato condicional, gráficos)
  • Usar datos con otras funciones de Power Query o Excel
  • Reutilizable en una plantilla junto con otras funciones de Excel
  • No se requiere codificación para muchas implementaciones, GUI familiar

Contras

  • Puede ser bastante lento (mucho más rápido de usar sqlite)
  • Shapefile debe estar en la unidad local / montada
  • ¿Restricciones de nombre de archivo Shapefile?
  • Solo lectura (que es como debería ser)
  • Requiere una solución complicada para la ruta del archivo si el archivo se comparte entre diferentes usuarios en OneDrive
  • Sin datos de geometría (no almacenados en dbf)

Requisitos

  • Office 2016 en adelante u Office 365 (que es en lo que lo he probado).
  • El nombre del archivo Shapefile no debe comenzar con un dígito numérico
  • Shapefile debe estar en una unidad local o montada, es decir, C: , T: - sin rutas de red o sintaxis UNC ( nombre-servidor…)
  • En algunos casos, los nombres de archivo Shapefile deben tener 8 caracteres o menos (especificación 8.3) ^

^ Este ha sido mi caso en los 2 años o más que he estado usando este método, pero probándolo ahora, el controlador ODBC en O365 versión 2001 en W10 1909 parece funcionar con nombres de archivo más largos.


Método

  1. Ficha Datos> Obtener datos> De otras fuentes> De ODBC
  2. Seleccione 'Archivos dBase' en Nombre de fuente de datos (DSN)
  3. En Cadena de conexión, escriba dbq = seguido de la ruta del archivo (SIN comillas) y asegúrese de que haya una barra invertida al final
  4. Seleccione Predeterminado o Personalizado cuando aparezca la ventana de autenticación, deje en blanco las propiedades de la cadena de conexión de la credencial y haga clic en Conectar
  5. Expanda su carpeta en la ventana del navegador que aparece y seleccione su shapefile relevante; si se carga en Vista previa, haga clic en Cargar
  6. Sus datos dbf de shapefile ahora se cargan como una tabla de una consulta en una nueva hoja de Excel.

Actualización de datos

  • Para obtener los datos más recientes, haga clic en la tabla y presione alt-F5 para actualizar la consulta. (verá cualquier progreso en la barra de herramientas inferior de la ventana principal de Excel)
  • Si ha modificado el contenido de la celda o los nombres de las columnas, el orden, etc. en Excel y no en Power Query, se sobrescribirán con los datos del shapefile. Debe editar los nombres de las columnas, el orden de las columnas en PQ para que se mantengan (consulte Transformación de datos a continuación), como definir una VISTA en SQL
  • Para deshabilitar el ajuste automático de los anchos de columna en la recarga, haga clic en la tabla, vaya a la pestaña Diseño de tabla> Propiedades y desmarque Ajustar ancho de columna

Exportación de datos estáticos

  • Si elimina la hoja, los datos todavía están en la consulta como una conexión y puede volver a cargarla (o combinarla con otras consultas)
  • Para deshabilitar permanentemente la conexión de datos de la hoja y convertirla en una hoja de cálculo de Excel normal y editable, haga clic en la tabla, vaya a la pestaña Diseño de tabla y haga clic en Desvincular. Sin embargo, la conexión de datos todavía está en segundo plano y puede mostrar mensajes de error / advertencia si otras personas intentan cargar el libro de trabajo
  • Para eliminar la conexión de datos de todo el libro de trabajo, vaya a la pestaña Datos> Consultas y conexiones y elimine la consulta de la barra lateral que aparece (clic derecho> Eliminar)

Transformando datos

Puede filtrar, cambiar el nombre o transformar columnas en la consulta de forma persistente (para que los cambios se mantengan incluso después de la actualización) en el editor de Power Query.

  1. Vaya a Datos> Consultas y conexiones
  2. Haga clic con el botón derecho en su tabla de datos en la barra lateral Consultas y conexiones que aparece en la ventana principal y haga clic en Editar. Esto abrirá Power Query Editor.
  3. Ahora puede eliminar, cambiar el nombre o reorganizar las columnas utilizando la GUI. También puede transformar algunos valores (por ejemplo, buscar y reemplazar, reemplazar NULL, concatenar o dividir columnas). Cada paso se registra en la lista Pasos aplicados en el lado derecho de la ventana principal; puede deshacer los pasos haciendo clic en la X
  4. También puede unir o agregar múltiples shapefiles importando cada shapefile como una consulta separada, luego usando la GUI de Power Query (Inicio> Combinar) para unir / agregar esas consultas.

Cambiar las fuentes de datos

  1. Abra la consulta en Power Query Editor (consulte los pasos 1 y 2 anteriores)
  2. Haga clic en la rueda dentada junto a Fuente
  3. Cambie la ruta del archivo después de 'dbq =' ... recordando dejar una barra invertida al final de la ruta del archivo
  4. Vuelva a realizar los pasos de navegación de archivos, pero los demás pasos de su consulta (cambiar el nombre de las columnas, etc.) permanecerán. Si esas columnas ya no existen, obtendrá un error y deberá modificar sus pasos.

Consejos

  • Para cambiar su consulta mediante programación, vaya al Editor avanzado en el Editor de Power Query y modifique en consecuencia (utilizando el lenguaje de fórmulas de Power Query M)
  • Considere usar parámetros en Power Query para que sea más fácil para los usuarios ingresar, p. Ej. el nombre del archivo o la ruta sin tener que editar los pasos de la consulta usando el engranaje de Configuración
  • Si usa carpetas compartidas en OneDrive donde la ruta del archivo hace referencia al nombre de usuario de Windows, tendrá que editar manualmente la Consulta M (consulte los ejemplos aquí) que ya no le permitirá editar rutas de archivos, etc. utilizando el engranaje de Configuración
  • Si su consulta tarda mucho en actualizarse, considere eliminar cualquier filtro en Excel o, si otro programa está utilizando el shapefile, desactive el modo de edición (por ejemplo, lápiz amarillo en QGIS)
  • Si desea actualizar las tablas dinámicas para leer los últimos datos de consulta cargados, primero actualice la tabla de consulta antes de actualizar las tablas dinámicas.

Hice una tarea similar para presentar los horarios de densidad de viviendas, seleccioné todos los campos y entradas en excell y luego usé otra hoja en el mismo libro con declaraciones if para resumir los datos, esto evita referencias a celdas específicas.


Necesita crear dinámicamente controladores de código / eventos para cada botón.

Una mejor manera podría ser crear un montón de botones en el formulario (tantos como crea que necesitará) con anticipación. Cree también el código del controlador de eventos. Hazlos todos ocultos inicialmente.

Luego, cuando se abre su formulario, puede cambiar dinámicamente los títulos de los botones, hacerlos visibles y moverlos. El código de evento que creó inicialmente se vinculará a los botones activados como se esperaba.

Para agregar un evento de control de forma dinámica en un formulario de Excel, primero debe agregar el (los) evento (s) en un módulo de clase. Para mi ejemplo, voy a agregar un módulo de clase llamado clsTEST con un evento, btn_click ()

Como puede ver, lo único que hará es establecer el título del botón en el número de veces que hizo clic en él. A continuación, en el código del formulario ingrese lo siguiente:

Cuando active el formulario, creará un botón. cada vez que haga clic en el botón, el título cambiará.


1 respuesta 1

Creo que el problema con el que se está encontrando es cómo PowerPoint y Excel almacenan números de diapositiva y números de hojas de trabajo. PowerPoint al menos 3 atributos diferentes con diapositivas, incluidos "ID de diapositiva", "índices de diapositiva" y "números de diapositiva". Todos son diferentes y hacen que las cosas sean un fastidio cuando intentas hacer referencia a ellos. Lo que me gusta hacer es establecer la referencia de la diapositiva justo cuando estoy creando la diapositiva:

De esta manera, justo cuando crea la diapositiva, ahora tiene una referencia a ella.

Además, encuentro que usar un número como referencia de la hoja de trabajo también puede causar problemas, ya que si hace referencia a la quinta hoja de trabajo, puede que no sea la quinta hoja de trabajo en absoluto. Debe buscar en el editor VBA de Excel para ver qué hoja obtiene qué referencia. Sin embargo, si puede hacer referencia al nombre de la hoja de trabajo, como "Hoja1", "Hoja2", "Otra hoja de trabajo", etc., puede hacer las cosas mucho más fáciles. Para poner esto un poco más en perspectiva, si crea una hoja llamada "5" y luego llama a la hoja de trabajo con.

No funcionará. Necesitarías usar

Ojalá tenga sentido. Esta parte no es necesaria, pero facilita mucho la depuración si tiene problemas. La forma en que recomendaría hacer esto no está en mi código a continuación porque no tengo su libro de trabajo.

Reescribí algunas líneas de su código y pude hacer que funcionara. Sin embargo, no tengo una copia de su libro de trabajo, por lo que no estoy 100% seguro de que esto funcione. Considere cambiar los nombres de las hojas de trabajo en su libro de trabajo si aún tiene problemas para hacer referencia a la hoja de trabajo desde el índice de diapositivas.


Supongo que recorrerá los registros después de que la consulta haya llenado su tabla, busque una condición y "haga cosas". Por lo tanto, asumo que la ubicación donde desea colocar el botón está representada por un objeto Range () dentro de ActiveSheet ()

Así que creemos un botón ActiveX dinámico en esa ubicación:

Si, de antemano, ha creado la siguiente rutina dentro de la hoja activa

luego aparecerá un cuadro de mensaje agradable una vez que presione el botón creado anteriormente (probado en XP / SP2 + Excel 2003).

La rutina Crear no pregunta si existe un botón con el mismo nombre, debe tomar medidas para crearlo solo una vez con el mismo nombre. Si llama a la rutina dos veces, .Name = "." Fallará silenciosamente y comenzará a nombrar el botón "CommandButton1" y más.

Por lo tanto, debe tener todos los ingredientes ahora para crear sus botones. Cada uno de ellos deberá tener un procedimiento precodificado si necesitan actuar de manera diferente. Debo mencionar que no puede depurar (recorrer) la rutina Create después de la creación del objeto OLE, porque el control se transfiere fuera de Excel - "¡no es un error, es una característica!"

Tengo que admitir que para mí suena un poco inusual y probablemente preferiría no instalar botones dinámicos que actúen en Sub precodificados, en su lugar, haría un diálogo inicial antes de la consulta dando opciones a través de casillas de verificación como "Truncar después de X filas (Y / N) "y cosas por el estilo, pero tendrás buenas razones para hacerlo a tu manera.


2. Pasos para agregar un mapa de calor en una tabla dinámica

También puede usar calor en una tabla dinámica aplicando formato condicional. Estos son los pasos a seguir:

  • Seleccione cualquiera de las celdas de la tabla dinámica.
  • Vaya a la pestaña Inicio ➜ Estilos ➜ Formato condicional.
  • En las opciones de formato condicional, selecciona escalas de color.

Además, si desea ocultar números (que no recomiendo), siga estos sencillos pasos.

  • Seleccione cualquiera de las celdas de la tabla dinámica.
  • Vaya a la pestaña Analizar ➜ Campo activo ➜ Configuración de campo.

  • Haga clic en formato de número.
  • En la pestaña de números, vaya a personalizado e ingrese el tipo.
  • Haga clic en Aceptar.

Puede descargar este archivo de muestra desde aquí para aprender cómo podemos usar una cortadora con un pivote de mapa de calor.


Instalación del complemento Power Map para Excel 2013

Tenga en cuenta que Power Map está preinstalado en 2016 (también conocido como Mapas 3D en 2016), pero que se requiere una instalación manual para Excel 2013. Las versiones anteriores de Excel no son compatibles con Power Map. Desafortunadamente, Power Map solo es compatible con los sistemas operativos Windows.

Si está utilizando 2013 y, por lo tanto, necesita instalar el complemento:

  1. Descargue el complemento del sitio web de Microsoft. Si la URL no le funciona, busque "Power Map Preview for Excel 2013" en Google, vaya al enlace del sitio web de Microsoft y descárguelo allí.
  1. Asegúrese de descargar la versión correcta, es decir, la versión de 32 bits o de 64 bits, según el sistema operativo que esté utilizando.
  1. Una vez que se haya descargado, inicie la instalación y siga las instrucciones en pantalla para completar la configuración.
  1. Una vez completada la instalación, compruebe el Insertar pestaña para Mapa de poder sección con un Mapa Debería ser como se muestra en la captura de pantalla a continuación.

Para hacer esto, vaya a Opciones de archivo & gt & complementos de gt. Debería ver una ventana emergente como la siguiente. En el menú desplegable en la parte inferior de la ventana, seleccione Complementos COM y haga clic en VAMOS:

Ahora ha configurado el complemento Power Map si está utilizando Excel 2013.


Consulte la última sección de la página siguiente:

Para importar una lista desde SharePoint:

Para actualizar las modificaciones en la lista de SharePoint:

Puede sincronizar Excel 2007/2010 y SharePoint 2007/2010 con una descarga e instalación de un complemento de Microsoft Excel.

Aquí hay algunos artículos que tienen una descripción detallada sobre cómo se hace esto: de Excel a SharePoint y de SharePoint a Excel

O puede usar el sincronizador de listas de Sharepoint que lo hará por usted con un solo clic: Sincronizador de listas de SharePoint Creo que hay una versión gratuita que puede probar.

Solo es posible crear una nueva lista basada en una hoja de Excel, pero no puede actualizar una lista existente con Excel.

Para guardar una hoja de Excel en una lista, haga clic en Acciones del sitio-> Más opciones-> Importar hoja de cálculo

  1. Servicios de Excel - directo en el sitio - requiere licencia Enterprise
  2. Excel PowerPivot (complemento GRATUITO) admite REST, lo que permitiría la conexión a datos y editarlos directamente, consulte aquí http://www.microsoft.com/en-us/bi/powerpivot.aspx
  3. Servicios de conectividad empresarial: conéctese a una gran cantidad de tipos de datos (bases de datos SQL, archivos Excel y CSV de amp, etc.) y proporcione acceso a los datos en tiempo real

Esto debe hacerse por código si actualiza / agrega los elementos de la lista usando Excel. Utilizo la acción del flujo de trabajo personalizado para actualizar los elementos de la lista desde el archivo de Excel, el archivo de Excel que almacena la información de la lista y todos los elementos. Entonces, primero cargue el archivo de Excel en la biblioteca de documentos y luego ejecute el flujo de trabajo para actualizar / Agregar el elemento en Excel a la lista. La acción del flujo de trabajo leerá los datos de Excel y luego los copiará en los elementos de la lista. Puede escribir la clase Convert para convertir los datos en los datos de campo correspondientes de SharePoint. Luego haz la copia. Lo más feliz es que también podría ejecutarse en una solución de caja de arena. :)

Estos deben declararse y llamar a la matriz en la función ImportSharepoint como,

Si corrige esto. Este error no ocurrirá. Continuará con los pasos siguientes.

He hecho lo mismo con el complemento de Excel 2013. Puede hacer lo mismo con el complemento escribiendo el código de CSOM para la lista de SharePoint. Puede utilizar este evento en Addin:

Y en este caso, puede agregar un código como el siguiente:

Este código actualiza las columnas cuyos registros tienen el mismo EmployeeCode en List y en Excel.
Esto se actualizará hasta que se encuentre un registro en Excel.

Abra en la vista de hoja de datos, elimine las columnas existentes y pegue las nuevas. Lleva un tiempo para datos grandes, pero aún puede evitar la codificación. Asegúrese de hacer coincidir las columnas, ya que el archivo adjunto es el primer campo y tendrá que excluir.


Gráfico de barras radiales

Este tutorial le muestra cómo crear una plantilla de gráfico de barras radiales en Excel para comparar el rendimiento de las ventas. Es bueno saber que un gráfico de barras radiales es una de las mejores soluciones para crear gráficos de estilo infográfico. ¡La visualización de datos es el rey! El gráfico de columnas verticales evolucionó a partir del gráfico de barras clásico. Toda la diferencia entre ellos es solo que uno de ellos usa sistemas de coordenadas polares y el otro cartesiano.

Si bien se ven geniales, debe prestar atención al gráfico de barras radiales. Es bueno saber que cada barra en el exterior se alarga casi hasta la última, incluso si representan el mismo valor. Por lo tanto, siga nuestro tutorial y descargue las últimas plantillas de gráficos de Excel gratuitas.


Funcionalidad de hipervínculo avanzada

Los hipervínculos basados ​​en campos admiten las variables de entorno de Windows de nivel de usuario actual y del sistema cuando se utilizan con la herramienta Identificar. Las variables de entorno deben configurarse en la computadora antes de utilizarlas para hipervínculos. Para usar variables de entorno dentro de ArcGIS, deberá calificar la variable con un signo de dólar, no con signos de porcentaje. Por ejemplo, use $ VARTEST en lugar de% VARTEST%.

Puede crear un objeto de envío dentro de su código de secuencia de comandos para llamar a funciones desde una biblioteca personalizada. Esto le permite acceder a ArcObjects a través de su secuencia de comandos de hipervínculo a través del objeto de envío. Además, puede pasar IFeature y / o IFeatureLayer al objeto de envío para que inicie el hipervínculo para la función adecuada.

Un ejemplo de una secuencia de comandos de hipervínculo que crea un objeto de envío

acceder a ArcObjects a través de su secuencia de comandos de hipervínculo a través del objeto de envío

Este ejemplo llamará a la función Launch desde una biblioteca Hyperlink_Lib que ha creado.


Los shapefiles son una forma de almacenar las características del mapa, las características y los atributos asociados, el formato predeterminado para el programa ArcView de ESRI. En realidad, la información se almacena en una colección de cuatro o más archivos, todos con la misma primera parte del nombre pero con diferentes extensiones de nombre de archivo. Debido a que todos los archivos se necesitan como un grupo, a menudo se ofrecen juntos en un formato de archivo comprimido que termina con una extensión de nombre de archivo & quotzip & quot. Esto requiere Winzip o algún programa de descompresión similar para "descomprimir" el archivo en sus archivos originales separados. Todos deben colocarse en la misma carpeta. Tenga en cuenta que el archivo que termina en la extensión & quotdbf & quot contiene información de atributos para los objetos en el shapefile. Este es un archivo dBase ordinario y puede ser leído por la mayoría de programas de hojas de cálculo o bases de datos.

"Coberturas" se refiere a otra forma de almacenar información de mapas, esta favorecida por los programas ArcINFO y ArcGIS de ESRI. El formato es incluso más complicado que los shapefiles. La información se almacena en varios archivos todos almacenados en una carpeta cuyo nombre corresponde al nombre de la cobertura. Lo que complica este formato es que la información esencial para utilizar esta cobertura también se almacena en archivos en una carpeta llamada & quotinfo & quot que se almacena junto a la carpeta de cobertura, y que puede contener información sobre otras coberturas en ese mismo espacio de trabajo. Para transportar toda la información necesaria, ArcGIS o ArcINFO proporcionan utilidades para crear un archivo & quotexport & quot. Necesitará una copia de ArcGIS o ArcInfo, o algún otro programa GIS (sistema de información geográfica) para traducir la información a su forma utilizable como cobertura.