Hacker News Reader

Comment by pmontra | Hacker News Reader

> Make a special library that can be called from the language and which only works with Excel

There are plenty of those libraries without the "only works with Excel" part, if you mean to have Excel running on the machine. As a Python example, a customer of mine is using XlsxWriter and openpyxl for .xlsx files, xlrd for .xls plus python-docx for dealing with docx files.

I don't remember why the two modules for xlsx files and not just one. My customer runs that software on Linux inside a Django app. I expect that Microsoft is interested only in Excel on Windows and in running Python inside Excel. What I expected was a VBA editor for Python and maybe a library for Windows to access Excel objects from a Python script in a cmd or powershell prompt.

View on HN

> What I expected was a VBA editor for Python and maybe a library for Windows to access Excel objects from a Python script in a cmd or powershell prompt.

This is exactly it. The killer feature for including any modern scripting language in Excel. Both XlxsWriter and openpyxl can r/w from Excel files but I have to manipulate the data using another library like `pandas`. Instead if MSFT gave a library which I can import into a Python script and use like

  import msft_excel_lib as xl
  data = xl.get('A1:A3')
  sum = xl.sum(data)
  xl.write("B3", sum)

would be much better than whatever it is they have shipped today without having to make much changes to anything else. I wouldn't even grudge them if they say that this library can run under some weird virtual environment found only within Excel to maintain product retention.

You can do this through COM. I forgot which Python library I used but once you have an Excel application object you can do

Data = xl.range('a1:a3')

Sum = xl.worksheetfunction.sum(data)

Xl.range('b3').value = sum

Any library enabling the COM link will do.

(sorry, typing this on my phone so formatting and capitalization are screwy)

> I don't remember why the two modules for xlsx files and not just one.

Last time I checked OpenPyXl doesn't deal correctly with .xlsm files - there's a parameter for that but I believe it's still experimental. In my case this meant that, on a Mac, Excel would complain that a file generated with OpenPyXl was corrupt and then successfully "recover" every generated file.

My wild guess is: your customer reads the files with OpenPyXl, processes the data with Pandas, and then uses XlsxWriter as the custom Excel writing engine.

Reply on news.ycombinator.com