Manually create Excel workbook with structures

User 74b72c099e

17-01-2011 12:52:52


The function JChemExcel.AddStructuresToColumn(...) requires an open instance of Excel  this is a bit unfortunate since we don't install Office on the server - besides opening Excel instances from the server process is - well, not what we want to do.

So, I want to be able to generate an Excel workbook solely in code (using OpenXml SDK), put "=JCSYSStructure(Hashcode)" in the appropriate cells and be able to open said workbook on any "Office 2010 + JChem for Excel"-enabled workstation.

The documentation for this scenario is a bit sparse, so my questions are these:

- How does JCSYSStructure(Hash) work? Is it as simple as: Lookup hash in SharedStrings, add 1 to index, do stuff on the next entry? 

- Does the Hash have any kind of significance? Ie. would any hashvalue work, or does JCSYSStructure perform some sort of validation? And in that case: How do I go about generating the correct hash value?


ChemAxon bd13b5bd77

17-01-2011 17:19:35


first of all please read this documentation since there is also an External API available, which does not require Excel instance in open state.

Extrernal API is typically designed for document generation.


However External API also requires Excel installed on the document server since External API is part of JChemExcel physical product (JChemExcel is running in an Excel instance).
Though I do not see any big problem with a server and an Office Excel installation only together.

Yes, we planned .NET based API for binary Excel file generation or using the new Open XML format, but not now, in the future scheduled.

Hash is just a link based on the molecule string, but this is a just pointer to a special sheet containing the full length molecule. We will publish the structure sheet  description for you if you want to generate everything on your own.

However I would vote for the External API and Excel installation, it is much simpler at the moment.





User 74b72c099e

18-01-2011 08:48:32

Hi Viktor

Thanks for the feedback,

I tried the example code (C#) which btw. could do with a quick update to make it work in VS2010 against the latest binaries  (got it working after a bit - the COM interop references need to be updated, untick "embed interop types")

I'm still not sure, we can go that way, so if you would give me the structure sheet description, I'd appreciate that very much


User 74b72c099e

18-01-2011 13:40:55

It's getting closer now

I've finally figured out  the structure of the Excel-workbook, so all that's missing is a description of the format of the structure:

JChemExcel0QIAAB+LCAAAAAAABADtvQdgHEmWJSYvbcp7f0r1StfgdKEIgGATJNiQQBDswYjN5.... etc.

and maybe the instructions on how to produce one of those bad boys :o)


ChemAxon 0e37943a96

18-01-2011 14:03:34

Hi Mikkel,

I attach a preliminary documentation of the structure sheet. We planned to release this a while ago.

We also want to create code samples for both in .NET and Java, but you will probably figure this out earlier. :).



 PS: Later we might change the compression and encoding algorithm (maybe leaving the extra 4 bytes for lenght info), but of course, we will be backwards compatible.


User 74b72c099e

19-01-2011 07:25:34

Thank you very much for your help!

I have a working prototype now. (Actually I almost had the compression-thing figured out, but the 4 byte length-prefix had me foiled )

As for changes to the algorithm, I'll just stick to ChemAxon.Net.Base.Helpers.GZipCompressor and not worry  


ChemAxon 0e37943a96

20-01-2011 15:37:50

Hi Mikkel,

I am glad we could help. Actually that 4 byte got there by "accident". :)