-1

I am making a Python code with geopandas and xlwings, what I do is colsulse .shp files from Excel, it works fine but when it reaches the last line of the code wb.save (in_file) where it should be saved The Excel workbook, Excel restarts or closes unexpectedly, I don't know what I'm doing wrong, besides that if I remove that line and when trying to save the Excel workbook manually, the same thing happens.

The code is:

import geopandas as gpd
import pandas as pd
import xlwings as xw
from pathlib import Path

#VINCULACION_S
in_file = str(Path(__file__).parent / Path("VINCULACION_S.xlsm"))
wb = xw.Book(in_file)

#VINCULACION_SH_NUE
sht1 = wb.sheets['VINCULACION_SH_NUE']
in_features1 = str(Path(__file__).parent / Path("INSUMOS/VH_NUE.shp"))
shp1 = gpd.GeoDataFrame.from_file(in_features1, ignore_geometry=True, index=False)
shp1 = shp1[['COLOR', 'INTERNO_DE', 'CLASE_DEMA', 'COUNT_AREA', 'SUM_AREA', 'SUM_LENGTH']]
shp1.insert(3, "CIV", '', allow_duplicates=False)

urow1 = sht1.range('B2').end('down').last_cell.row
sht1.range("5" + ":" + str(urow1)).clear_contents()
sht1.range('B2').options(pd.DataFrame, index=False).value = shp1

#VINCULACION_SH_ACT
sht2 = wb.sheets['VINCULACION_SH_ACT']
in_features2 = str(Path(__file__).parent / Path("INSUMOS/VH_ACT.shp"))
shp2 = gpd.GeoDataFrame.from_file(in_features2, ignore_geometry=True, index=False)
shp2 = shp2[['COLOR', 'INTERNO_DE', 'CLASE_DEMA', 'COUNT_AREA', 'SUM_AREA', 'SUM_LENGTH']]

urow2 = sht2.range('C2').end('down').last_cell.row
sht2.range("5" + ":" + str(urow2)).clear_contents()
sht2.range('C2').options(pd.DataFrame, index=False).value = shp2

#VINCULACION_SH_CIV
sht3 = wb.sheets['VINCULACION_SH_CIV']
in_features3 = str(Path(__file__).parent / Path("INSUMOS/VH_CIV.shp"))
shp3 = gpd.GeoDataFrame.from_file(in_features3, ignore_geometry=True, index=False)
shp3 = shp3[['INTERNO_DE', 'CLASE_DEMA', 'MVICIV']]

urow3 = sht3.range('B2').end('down').last_cell.row
sht3.range("5" + ":" + str(urow3)).clear_contents()
sht3.range('B2').options(pd.DataFrame, index=False).value = shp3

#VINCULACION_SV_NUE
sht4 = wb.sheets['VINCULACION_SV_NUE']
in_features4 = str(Path(__file__).parent / Path("INSUMOS/VV.shp"))
shp4 = gpd.GeoDataFrame.from_file(in_features4, ignore_geometry=True, index=False)
shp4 = shp4[['INTERNO_SE', 'TIPO_SENAL', 'FASE_ACCIO', 'CONTENIDO1', 'CONTENIDO2', 'VELOCIDAD']]
shp4.insert(2, "CIV", '', allow_duplicates=False)

urow4 = sht4.range('B2').end('down').last_cell.row
sht4.range("5" + ":" + str(urow4)).clear_contents()
sht4.range('B2').options(pd.DataFrame, index=False).value = shp4

#VINCULACION_SV_ACT
sht5 = wb.sheets['VINCULACION_SV_ACT']
in_features5 = str(Path(__file__).parent / Path("INSUMOS/VV_ACT.shp"))
shp5 = gpd.GeoDataFrame.from_file(in_features5, ignore_geometry=True, index=False)
shp5 = shp5[['INTERNO_SE', 'TIPO_SENAL', 'FASE_ACCIO']]

urow5 = sht5.range('C2').end('down').last_cell.row
sht5.range("5" + ":" + str(urow5)).clear_contents()
sht5.range('C2').options(pd.DataFrame, index=False).value = shp5

#VINCULACION_SV_CIV
sht6 = wb.sheets['VINCULACION_SV_CIV']
in_features6 = str(Path(__file__).parent / Path("INSUMOS/VV_CIV.shp"))
shp6 = gpd.GeoDataFrame.from_file(in_features6, ignore_geometry=True, index=False)
shp6 = shp6[['INTERNO_SE', 'TIPO_SENAL', 'MVICIV']]

urow6 = sht6.range('B2').end('down').last_cell.row
sht6.range("5" + ":" + str(urow6)).clear_contents()
sht6.range('B2').options(pd.DataFrame, index=False).value = shp6

wb.save(in_file)

It throws me this error, after having unexpectedly restarted or closed excel

Traceback (most recent call last):
  File "C:\Users\Hp\Desktop\Ejemplo\VINCULACION_S.py", line 72, in <module>
    wb.save(in_file)
  File "C:\Users\Hp\AppData\Local\Programs\Python\Python39\lib\site-packages\xlwings\main.py", line 740, in save
    self.impl.save(path)
  File "C:\Users\Hp\AppData\Local\Programs\Python\Python39\lib\site-packages\xlwings\_xlwindows.py", line 552, in save
    self.xl.SaveAs(os.path.realpath(path), FileFormat=file_format)
  File "C:\Users\Hp\AppData\Local\Programs\Python\Python39\lib\site-packages\xlwings\_xlwindows.py", line 66, in __call__
    v = self.__method(*args, **kwargs)
  File "C:\Users\Hp\AppData\Local\Temp\gen_py\3.9\00020813-0000-0000-C000-000000000046x0x1x9.py", line 46533, in SaveAs
    return self._oleobj_.InvokeTypes(3174, LCID, 1, (24, 0), ((12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (3, 49), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)),Filename
pywintypes.com_error: (-2147023170, 'Error en la llamada a procedimiento remoto.', None, None)

Update: What I have tried so far and it works is to separate the code for each procedure of shp and sht, that is, I execute in a different .py selecting the excel sheet where the result will go and in this way it does it well, I am trying to add an instruction to the python code where in each # ... select the excel sheet to process and continue until saving.

3
  • 2
    You should add the error to your question as well. Jun 15 '21 at 22:30
  • 2
    Need to wait between calls (xlswings do remote calls to Excel API) according stackoverflow.com/questions/3718037/… (TLDR: use time.sleep to solve issue) Not sure why you introduce xlwings & associated issues as you can achieve the same with only pandas/geopandas. You can write each element to a particular dataframe e.g xlsxwriter.readthedocs.io/example_pandas_multiple.html or element to a same dataframe & setting content position stackoverflow.com/questions/32957441/…
    – ThomasG77
    Jun 15 '21 at 23:51
  • @ThomasG77 I tried adding and using time.sleep in the code I show in the question body with no good results; I also have a code where I use only geopandas as pandas.DataFrame and result of shp.to_excel (xlpath) works well for me but it does not do it in real time like xlwings does, I will keep trying based on the links that you gave me, trying to adapt it to time.sleep. Jun 16 '21 at 3:19
0

Apparently the error occurred because two or more sheets with rank in the code were activated, I solved it by adding the code in each # ... sht = wb.sheets ['name_of_the_sheet_to_process']. activate ()

I share the resulting and functional code, I hope someone will find this post useful

import geopandas as gpd
import pandas as pd
import xlwings as xw
from pathlib import Path

#VINCULACION_S
in_file = str(Path(__file__).parent / Path("VINCULACION_S.xlsm"))
wb = xw.Book(in_file)

#VINCULACION_SH_NUE
sht1 = wb.sheets['VINCULACION_SH_NUE'].activate()
sht1 = wb.sheets['VINCULACION_SH_NUE']
in_features1 = str(Path(__file__).parent / Path("INSUMOS/VH_NUE.shp"))
shp1 = gpd.GeoDataFrame.from_file(in_features1, ignore_geometry=True, index=False)
shp1 = shp1[['COLOR', 'INTERNO_DE', 'CLASE_DEMA', 'COUNT_AREA', 'SUM_AREA', 'SUM_LENGTH']]
shp1.insert(3, "CIV", '', allow_duplicates=False)

urow1 = sht1.range('B2').end('down').last_cell.row
sht1.range("5" + ":" + str(urow1)).clear_contents()
sht1.range('B2').options(pd.DataFrame, index=False).value = shp1

#VINCULACION_SH_ACT
sht2 = wb.sheets['VINCULACION_SH_ACT'].activate()
sht2 = wb.sheets['VINCULACION_SH_ACT']
in_features2 = str(Path(__file__).parent / Path("INSUMOS/VH_ACT.shp"))
shp2 = gpd.GeoDataFrame.from_file(in_features2, ignore_geometry=True, index=False)
shp2 = shp2[['COLOR', 'INTERNO_DE', 'CLASE_DEMA', 'COUNT_AREA', 'SUM_AREA', 'SUM_LENGTH']]

urow2 = sht2.range('C2').end('down').last_cell.row
sht2.range("5" + ":" + str(urow2)).clear_contents()
sht2.range('C2').options(pd.DataFrame, index=False).value = shp2

#VINCULACION_SH_CIV
sht3 = wb.sheets['VINCULACION_SH_CIV'].activate()
sht3 = wb.sheets['VINCULACION_SH_CIV']
in_features3 = str(Path(__file__).parent / Path("INSUMOS/VH_CIV.shp"))
shp3 = gpd.GeoDataFrame.from_file(in_features3, ignore_geometry=True, index=False)
shp3 = shp3[['INTERNO_DE', 'CLASE_DEMA', 'MVICIV']]

urow3 = sht3.range('B2').end('down').last_cell.row
sht3.range("5" + ":" + str(urow3)).clear_contents()
sht3.range('B2').options(pd.DataFrame, index=False).value = shp3

#VINCULACION_SV_NUE
sht4 = wb.sheets['VINCULACION_SV_NUE'].activate()
sht4 = wb.sheets['VINCULACION_SV_NUE']
in_features4 = str(Path(__file__).parent / Path("INSUMOS/VV.shp"))
shp4 = gpd.GeoDataFrame.from_file(in_features4, ignore_geometry=True, index=False)
shp4 = shp4[['INTERNO_SE', 'TIPO_SENAL', 'FASE_ACCIO', 'CONTENIDO1', 'CONTENIDO2', 'VELOCIDAD']]
shp4.insert(2, "CIV", '', allow_duplicates=False)

urow4 = sht4.range('B2').end('down').last_cell.row
sht4.range("5" + ":" + str(urow4)).clear_contents()
sht4.range('B2').options(pd.DataFrame, index=False).value = shp4

#VINCULACION_SV_ACT
sht5 = wb.sheets['VINCULACION_SV_ACT'].activate()
sht5 = wb.sheets['VINCULACION_SV_ACT']
in_features5 = str(Path(__file__).parent / Path("INSUMOS/VV_ACT.shp"))
shp5 = gpd.GeoDataFrame.from_file(in_features5, ignore_geometry=True, index=False)
shp5 = shp5[['INTERNO_SE', 'TIPO_SENAL', 'FASE_ACCIO']]

urow5 = sht5.range('C2').end('down').last_cell.row
sht5.range("5" + ":" + str(urow5)).clear_contents()
sht5.range('C2').options(pd.DataFrame, index=False).value = shp5

#VINCULACION_SV_CIV
sht6 = wb.sheets['VINCULACION_SV_CIV'].activate()
sht6 = wb.sheets['VINCULACION_SV_CIV']
in_features6 = str(Path(__file__).parent / Path("INSUMOS/VV_CIV.shp"))
shp6 = gpd.GeoDataFrame.from_file(in_features6, ignore_geometry=True, index=False)
shp6 = shp6[['INTERNO_SE', 'TIPO_SENAL', 'MVICIV']]

urow6 = sht6.range('B2').end('down').last_cell.row
sht6.range("5" + ":" + str(urow6)).clear_contents()
sht6.range('B2').options(pd.DataFrame, index=False).value = shp6

wb.save(in_file)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.