Setting a doc to be shared?
Jules
I'm currently using xlwt to create xls files, but I need these to be
set to be shared workbooks, and possibly tweak some of the shared
values in the advanced section [in the gui inside excel], from my not-
very-good understanding of the xlwt source, it dosen't seem this is
possible. Are there other ways to achieve this?
Many thanks for any help.
Jules
John Machin
> Hi All,
>
> I'm currently using xlwt to create xls files, but I need these to be
> set to be shared workbooks, and possibly tweak some of the shared
> values in the advanced section [in the gui inside excel], from my not-
> very-good understanding of the xlwt source, it dosen't seem this is
> possible.
That's correct; xlwt doesn't support creating shared workbooks. The
problem is to do with encrypting the passwords and data. There is no
documentation that I'm aware of the BIFF8 way of doing that, and no
open-source code to borrow from -- last time I looked (about 2 months
ago), AFAICT none of OOO Calc, Gnumeric and apache.jakarta.poi.etc will
write an encrypted xls file.
> Are there other ways to achieve this?
Perhaps you could use COM to open your xlwt-created file and save it as
a shared workbook.
Cheers,
John
Jules Stevenson
Thanks for getting back to me.
> That's correct; xlwt doesn't support creating shared workbooks. The
> problem is to do with encrypting the passwords and data. There is no
> documentation that I'm aware of the BIFF8 way of doing that, and no
> open-source code to borrow from -- last time I looked (about 2 months
> ago), AFAICT none of OOO Calc, Gnumeric and apache.jakarta.poi.etc will
> write an encrypted xls file.
Sorry for the naivety, but I presume excel automatically encrypts as soon as
sharing is turned on? If this is not the case however would it make it
possible - encryption is not needed by us if it is an option.
> > Are there other ways to achieve this?
>
> Perhaps you could use COM to open your xlwt-created file and save it as
> a shared workbook.
Do you have any links, or introductory resources regarding using COM in
python / excel. I have next to no understanding of this, so any information
would be a bonus.
Slightly off topic, but does anyone have any suggestions of alternative ways
in which I could approach this:
We have a web intranet app, and it pulls data from these excel files, using
them very much like a database [bad]. However, we very much like the ease of
use of editing the files that you get with excel [good]. We don't have the
technical expertise to build a web interface that gives us the kind of
functionality we get from entering / modifying data in an excel sheet. Does
anyone have any other suggestions? We thought about interfacing with google
docs, but don't like the reliance of an out-of-house 3rd party. Any thoughts
much appreciated.
Jules
John Machin
> Hi John,
>
> Thanks for getting back to me.
>
>> That's correct; xlwt doesn't support creating shared workbooks. The
>> problem is to do with encrypting the passwords and data. There is no
>> documentation that I'm aware of the BIFF8 way of doing that, and no
>> open-source code to borrow from -- last time I looked (about 2 months
>> ago), AFAICT none of OOO Calc, Gnumeric and apache.jakarta.poi.etc will
>> write an encrypted xls file.
>
> Sorry for the naivety, but I presume excel automatically encrypts as soon as
> sharing is turned on?
But you wanted to create the file with xlwt. How does Excel get into the
act? Are you presuming that Excel wil automatically encrypt the file the
first time it is opened?
Let's start with an existing non-shared file. You open it with Excel and
you want to save it as shared. Have you actually ever tried to save any
file as shared? There are (1) a read password (2) a modify password (3)
an Advanced button that takes you to 3 levels of encryption (4) a
tick-box for recommending read-only. There is no "shared" button or
tick-box. Whether you want file sharing is inferred from the
presence/absence of the the modify password. If you specify a read
password, all but the first few records are encrypted. If you specify a
modify password but not a read password, only the modify password is
encrypted. So you can't get file sharing without some encryption.
> If this is not the case however would it make it
> possible - encryption is not needed by us if it is an option.
>
>>> Are there other ways to achieve this?
What exactly is it that you are trying to achieve? Restricting
read/write file (or directory) access to a subset of users? Your
operating system may be able to help you with that :-)
>> Perhaps you could use COM to open your xlwt-created file and save it as
>> a shared workbook.
>
> Do you have any links, or introductory resources regarding using COM in
> python / excel. I have next to no understanding of this, so any information
> would be a bonus.
COM would be the least preferred option. Let's explore other options first.
>
> Slightly off topic, but does anyone have any suggestions of alternative ways
> in which I could approach this:
>
> We have a web intranet app, and it pulls data from these excel files, using
> them very much like a database [bad]. However, we very much like the ease of
> use of editing the files that you get with excel [good].
What about validation of the data that has been entered?
> We don't have the
> technical expertise to build a web interface that gives us the kind of
> functionality we get from entering / modifying data in an excel sheet. Does
> anyone have any other suggestions? We thought about interfacing with google
> docs, but don't like the reliance of an out-of-house 3rd party. Any thoughts
> much appreciated.
Back to the beginning: you said "I need these to be
set to be shared workbooks". Consider that you have some underlying
functionality requirement, and you regarded setting your workbooks to
"shared" as a solution. What is the underlying requirement?
Cheers,
John
Jules Stevenson
> > Sorry for the naivety, but I presume excel automatically encrypts as
> soon as
> > sharing is turned on?
>
> But you wanted to create the file with xlwt. How does Excel get into
> the
> act? Are you presuming that Excel wil automatically encrypt the file
> the
> first time it is opened?
>
> Let's start with an existing non-shared file. You open it with Excel
> and
> you want to save it as shared. Have you actually ever tried to save any
> file as shared? There are (1) a read password (2) a modify password (3)
> an Advanced button that takes you to 3 levels of encryption (4) a
> tick-box for recommending read-only. There is no "shared" button or
> tick-box. Whether you want file sharing is inferred from the
> presence/absence of the the modify password. If you specify a read
> password, all but the first few records are encrypted. If you specify a
> modify password but not a read password, only the modify password is
> encrypted. So you can't get file sharing without some encryption.
Hmm. When I open a pre created [xlwt] excel file in excel 2007, change the
ribbon to review, click 'sharing' and then click 'share with more than one
user'. I also turn off track changes in the advanced tab [for 'fun' at the
moment, this may or may not be relevant]. Clicking ok prompts me to save the
document. There is never any request for entering a password or setting any
level of encryption etc?
> > If this is not the case however would it make it
> > possible - encryption is not needed by us if it is an option.
> >
> >>> Are there other ways to achieve this?
>
> What exactly is it that you are trying to achieve? Restricting
> read/write file (or directory) access to a subset of users? Your
> operating system may be able to help you with that :-)
We just want the doc shared - anyone can access it. I need a system where
any user can modify the excel file directly, or also through a web app
intranet. Ie. If you're doing heavy mods to the excel file you open it
directly, if it's just setting a column to 'True' you can do it from the web
app.
> Back to the beginning: you said "I need these to be
> set to be shared workbooks". Consider that you have some underlying
> functionality requirement, and you regarded setting your workbooks to
> "shared" as a solution. What is the underlying requirement?
The excel docs contain lists of 3D assets in a 3d application [Softimage
XSI]. The excel files are generated automatically by parsing the 3d file.
These excel docs are then used for controlling which elements get rendered
out, what frame ranges they need to be, there output formats and on disk
destinations etc. Excel is good for us because all parties know how to use
it, it's very quick to do edits and update / insert etc. Obviously
validation is an issue, but there are actually only a few columns that the
user ever has to manually update, and the payoff in production time taken to
update these things outweighs the occasional typo.
Jules Stevenson
> where
> any user can modify the excel file directly, or also through a web app
> intranet. Ie. If you're doing heavy mods to the excel file you open it
> directly, if it's just setting a column to 'True' you can do it from
> the web
> app.
Sorry, to clarify - I'm talking about excel workbook sharing, not OS level
file sharing.
John Machin
Again, you're talking solutions instead of requirements.
John Machin
xlwt writes .xls files, compatible with Excel 2003. It was the Excel
2003 UI that I was describing, with the back-door method that I first
stumbled on: File / Save as / Tools / General Options (which needs a
password to have any effect at all). Now that you've tipped me off to
the existence of a saner front-door method (in Excel 2003: Tools / Share
Workbook), I find that it uses 3 record types that are not documented by
OOo and have unclear documentation in the MS docs:
0x1a9 USERBVIEW (i.e. USER Book VIEW)
0x1aa USERSVIEWBEGIN (i.e. USER Sheet VIEW ...)
# between BEGIN and END are known record types defining the user's view
0x1ab USERSVIEWEND
which are for custom views, presumably one for each user who has had a
go at the file.
I suspect that this view stuff would have to be set up for the first
(creating) user. I have not yet found a record/field that explicitly
defines whether the file is shared or not.
User names in shared workbooks are kept in a bag on the side: a "User
Names" OLE2 stream. If you enable change tracking, that goes into a
"Revision Log" OLE2 stream.
Do you get the impression that I'm not seeing a good cost-benefit ratio
here?
>
>>> If this is not the case however would it make it
>>> possible - encryption is not needed by us if it is an option.
>>>
>>>>> Are there other ways to achieve this?
>> What exactly is it that you are trying to achieve? Restricting
>> read/write file (or directory) access to a subset of users? Your
>> operating system may be able to help you with that :-)
>
> We just want the doc shared - anyone can access it. I need a system where
> any user can modify the excel file directly, or also through a web app
> intranet. Ie. If you're doing heavy mods to the excel file you open it
> directly, if it's just setting a column to 'True' you can do it from the web
> app.
What happens if person A has the file open directly and persion B fires
up the web app? How are potential conflicts resolved? How does the web
app work i.e. what software is it using to read/write/update the xls file?
>> Back to the beginning: you said "I need these to be
>> set to be shared workbooks". Consider that you have some underlying
>> functionality requirement, and you regarded setting your workbooks to
>> "shared" as a solution. What is the underlying requirement?
>
> The excel docs contain lists of 3D assets in a 3d application [Softimage
> XSI].
[snip]
I meant the perceived need for having workbooks shared Excel-fashion as
opposed to not shared Excel-fashion is actually a possible solution what
what underlying sharing requirement(s). What does it give you (apart
from change tracking) that the operating system can't?
Cheers,
John
Jules Stevenson
> level
> > file sharing.
>
> Again, you're talking solutions instead of requirements.
Then I guess I'm not educated enough in spec docs / requirement listing /
whatever else it is called. FWIW I'm not a full time programmer, just the
most technical person in the company so it falls to me to do these things,
so apologies if I'm not clearly getting across what I need to do, I'll try
again:
I need a way for multiple people to access and change data in an end user
friendly way as possible [hence excel]. The data needs to be accessible for
manipulation via python scripts, and re-displayable and possible editable
from a web app [pylons based].
I hope that is more concise.
Jules
Jules Stevenson
> xlwt writes .xls files, compatible with Excel 2003. It was the Excel
> 2003 UI that I was describing, with the back-door method that I first
> stumbled on: File / Save as / Tools / General Options (which needs a
> password to have any effect at all). Now that you've tipped me off to
> the existence of a saner front-door method (in Excel 2003: Tools /
> Share
> Workbook), I find that it uses 3 record types that are not documented
> by
> OOo and have unclear documentation in the MS docs:
> 0x1a9 USERBVIEW (i.e. USER Book VIEW)
> 0x1aa USERSVIEWBEGIN (i.e. USER Sheet VIEW ...)
> # between BEGIN and END are known record types defining the user's view
> 0x1ab USERSVIEWEND
> which are for custom views, presumably one for each user who has had a
> go at the file.
>
> I suspect that this view stuff would have to be set up for the first
> (creating) user. I have not yet found a record/field that explicitly
> defines whether the file is shared or not.
>
> User names in shared workbooks are kept in a bag on the side: a "User
> Names" OLE2 stream. If you enable change tracking, that goes into a
> "Revision Log" OLE2 stream.
>
> Do you get the impression that I'm not seeing a good cost-benefit ratio
> here?
Heh, yes.
> What happens if person A has the file open directly and persion B fires
> up the web app? How are potential conflicts resolved? How does the web
> app work i.e. what software is it using to read/write/update the xls
> file?
The theory is we'd rely on excels handling of this, ideally it would be if
users are editing the same doc via excel, relying on the user choosing who
'wins' on save. If the web app is writing it out, it always wins.
> I meant the perceived need for having workbooks shared Excel-fashion as
> opposed to not shared Excel-fashion is actually a possible solution
> what
> what underlying sharing requirement(s). What does it give you (apart
> from change tracking) that the operating system can't?
Multiple users editing the same spreadsheet at the same time. I think.
Jules
John Machin
>>> Sorry, to clarify - I'm talking about excel workbook sharing, not OS
>> level
>>> file sharing.
>> Again, you're talking solutions instead of requirements.
>
> Then I guess I'm not educated enough in spec docs / requirement listing /
> whatever else it is called. FWIW I'm not a full time programmer, just the
> most technical person in the company so it falls to me to do these things,
> so apologies if I'm not clearly getting across what I need to do, I'll try
> again:
example:
Requirement: You want to be in the city by 3 p.m.
Solution 1: You drive the truck.
Solution 2: You leave home earlier and catch the train.
> I need a way for multiple people to access and change data in an end user
> friendly way as possible [hence excel].
change tracking) do you want to set each workbook to shared? I.e. what
goes wrong if you don't set it? Can multiple people access/update the
workbook without clashes?
> The data needs to be accessible for
> manipulation via python scripts, and re-displayable and possible editable
> from a web app [pylons based].
Excel-wielding users and the web-app-wielding users, and now you've
introduced "manipulation via python scripts" ...
Bottom line from me: xlwt doesn't support setting workbooks to shared,
and the effort of making it do it doesn't seem worthwhile.
Cheers,
John
Chris Withers
> The theory is we'd rely on excels handling of this, ideally it would be if
> users are editing the same doc via excel, relying on the user choosing who
> 'wins' on save. If the web app is writing it out, it always wins.
Why don't you just use Sharepoint?
If you need to have a web app generating workbooks into the middle of
the process (ie: updating an existing workbook) you're in for a world of
pain...
Chris
--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
Jules Stevenson
> change tracking) do you want to set each workbook to shared? I.e. what
> goes wrong if you don't set it? Can multiple people access/update the
> workbook without clashes?
Because you can't have two users modify the same excel file at the same time
without sharing it.
> Bottom line from me: xlwt doesn't support setting workbooks to shared,
> and the effort of making it do it doesn't seem worthwhile.
Fine, I wasn't necessarily asking it to :). I just needed to know of other
ways to achieve what I'm trying to do, but it looks like excel is probably
the wrong way to go. Going to have a look at Google docs API, since this is
already geared up for multiuser goodness, or see if we can split / change
the data so it doesn't have to be shared.
Thanks all for your help.
Jules
Web Store
such as django would be worth the time - http://www.djangoproject.com/
Sheridan George