Mailing List Archive

On Tue, 7 Sep 2021 22:05:58 -0700 (PDT), "hongy...@gmail.com"
<hongyi.zhao@gmail.com> declaimed the following:

>I've some xlsx files which include dropdown columns in them. I want to know whether I can combine all the lines into one xlsx file. Any hints for doing this job with python programmatically will be highly appreciated.

I don't recall ANY Python Excel reader/writer that can handle embedded
scripting. They all only read the last value active in a cell, not the code
behind the cell. They mostly just bypass the need to convert to/from
CSV/TSV.

Presuming you are doing this on Windows, you might be able to use
either the pywin32/win32py extension library, or ctypes, to invoke the
Excel COM interface -- which may allow you to read the underlying script
and/or form objects.

If this is a one-time operation, it may be faster to just open the
files and use cut&paste <G> Or write a large VBA script in Excel.

This appears to be the current "favored" package from Python:
https://openpyxl.readthedocs.io/en/stable/

You may still have problems:
"""
keep_vba controls whether any Visual Basic elements are preserved or not
(default). If they are preserved they are still not editable.
"""
If not editable, you won't be able to copy from one file to another.
Also:
"""
openpyxl does currently not read all possible items in an Excel file so
images and charts will be lost from existing files if they are opened and
saved with the same name.
"""
It does, however, grant access to the formulas in cells, rather than
just last value; along with style information...



--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com http://wlfraed.microdiversity.freeddns.org/

--
https://mail.python.org/mailman/listinfo/python-list

On Tue, Sep 07 2021 at 10:05:58 PM, "hongy...@gmail.com" <hongyi.zhao@gmail.com> wrote:
> I've some xlsx files which include dropdown columns in them. I want to
> know whether I can combine all the lines into one xlsx file. Any hints
> for doing this job with python programmatically will be highly
> appreciated.
>

The dropdown is *probably* implemented using Excel's data validation
mechanism. openpyxl's documentation says it can read[1]/write Excel
files and also mentions data validation[2]. You might be able to use it
to read the existing files, combine the rows and write to a different
file.

[1] https://openpyxl.readthedocs.io/en/stable/usage.html#read-an-existing-workbook
[2] https://openpyxl.readthedocs.io/en/stable/validation.html

--
regards,
kushal
--
https://mail.python.org/mailman/listinfo/python-list

On Thursday, September 9, 2021 at 9:15:23 AM UTC+8, Kushal Kumaran wrote:
> On Tue, Sep 07 2021 at 10:05:58 PM, "hongy...@gmail.com" <hongy...@gmail.com> wrote:
> > I've some xlsx files which include dropdown columns in them. I want to
> > know whether I can combine all the lines into one xlsx file. Any hints
> > for doing this job with python programmatically will be highly
> > appreciated.
> >
> The dropdown is *probably* implemented using Excel's data validation
> mechanism. openpyxl's documentation says it can read[1]/write Excel
> files and also mentions data validation[2]. You might be able to use it
> to read the existing files, combine the rows and write to a different
> file.
>
> [1] https://openpyxl.readthedocs.io/en/stable/usage.html#read-an-existing-workbook
> [2] https://openpyxl.readthedocs.io/en/stable/validation.html

Thank you for your comments. For a related discussion on this topic, please refer to [1].

[1] https://groups.google.com/g/openpyxl-users/c/k2xnPZS2kbo/m/m2YmEO9ZBgAJ

Regards,
HY

--
https://mail.python.org/mailman/listinfo/python-list