SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
FastExcel
Topic Author
Posts: 50
Joined: December 2nd, 2003, 8:10 am

Excel Binary Names

February 17th, 2012, 3:12 pm

I need to persist an unordered map in a saved Excel workbook. One possibility is to use Excel's binary names (xltypeBigData) but there seem to be a lot of limitations (active sheet only, bugs in Excel 2007 and 2010, ...).Before I invest the time in trying to work through the problems, has anyone successfully persisted binary names with a reasonable amount of data (megabytes), or is this a really bad idea?
 
User avatar
adcockj
Posts: 161
Joined: July 14th, 2002, 3:00 am

Excel Binary Names

February 21st, 2012, 8:57 am

Is this for maintaining the map state within you fast match/lookup functions?I'm going to go with really bad idea, I think working around the scope issues will break any multithreading support as the functions will end up having to be command mode.John
 
User avatar
FastExcel
Topic Author
Posts: 50
Joined: December 2nd, 2003, 8:10 am

Excel Binary Names

February 22nd, 2012, 7:24 pm

The unordered map works well with Boost locking so that multithreading works. I am just trying to work out how to persist the Map at Save and Open Time. I could use a hidden sheet, but that won't work if the structure is protected, or store externally to the workbook, but that has problems if the user moves the workbook, or use Binary Names, but I get the feeling that Binary Names are uncharted territory with lurking dragons.
 
User avatar
Edgey
Posts: 219
Joined: March 23rd, 2005, 11:01 am

Excel Binary Names

February 23rd, 2012, 2:38 pm

Could you convert to string format (e.g. base64) and store the data as text? If you do invest some time, let us know how you get on.
Last edited by Edgey on February 22nd, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
tomodachi
Posts: 17
Joined: February 11th, 2012, 8:37 pm

Excel Binary Names

February 24th, 2012, 4:31 am

I've been using the library at xll.codeplex.com. It seems to have some functionality for creating xlltypeBigData, but I haven't used that and it does not seem to be well documented.Maybe the handle stuff can do what you need. Write a C++ class to save and store what you need and hook it into that?
 
User avatar
adcockj
Posts: 161
Joined: July 14th, 2002, 3:00 am

Excel Binary Names

February 24th, 2012, 2:09 pm

QuoteOriginally posted by: FastExcel but I get the feeling that Binary Names are uncharted territory with lurking dragons.Yep, has always seemed like a solution looking for a problem.John
 
User avatar
DominicConnor
Posts: 11684
Joined: July 14th, 2002, 3:00 am

Excel Binary Names

March 7th, 2012, 5:14 pm

Last edited by DominicConnor on March 6th, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
DominicConnor
Posts: 11684
Joined: July 14th, 2002, 3:00 am

Excel Binary Names

March 7th, 2012, 5:14 pm

I had to do this a while back, my solution is either elegant or a candidate for the scariest piece of Excel crap in the history of the world, depending on how you see it.A workbook is just a file...So save it there.A thing you need to know is the default standard file header style user in most Microsoft file formats....It basically saysstruct OCli {uint magic_cookie;uint something, otherhing...;unsigned int uloff;unsigned int ulItemCt;size_t ulThingSize;data_type things[1];} Header;ulOff is where the data actually begins and things is not usually an array with one element, ulItemCt determines how many of these items are actually present (oh the fun you can have changing it)ulThingSize is how big each element is. You might think this is superfluous, but if you come to the Random Walkers this Thursday, for the price of a beer I will tell you a tail of pain, loathing, greed and why my team called it the Bug Of Death.What is not there is a size of the file, actually there often it is, but in the last 34 years of my programming using MS products I've never seen it enforced, so you can shove any old shit at the end of most MS files.In the generating program open the XLS fileSeek to the end of the file, capturing the offset from the start.Write the data to the the workbook fileclose itRename the file from Filename.xlsx to Filename.XXXX.xls where XXXX is the offset from the start of the file.VBA and C++ code in Excel can access the filename and the file itself may be read whilst open, so it's a simple matter or parsing the filename to know where to start reading the fileThere's a slightly whackier solution that involves a C++ .EXE that thinks it's a spreadsheet, but you probably don't need to go there.
Last edited by DominicConnor on March 6th, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
FastExcel
Topic Author
Posts: 50
Joined: December 2nd, 2003, 8:10 am

Excel Binary Names

March 10th, 2012, 5:15 pm

Hmmm... Can you append to an XLS file whilst its open? I thought it was locked.Currently investigating xmlCustomParts, works OK except that its not available directly from the XLL api and also only XL 2007 onwards.Interestingly you can write to xmlCustomParts from within an automation UDF which opens up a few possibilities.
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On