Re: pyExcelerator question
Thanks!!! Looks great. Works for me. I'll try to submit the patch. Gerry -- http://mail.python.org/mailman/listinfo/python-list
pyExcelerator question
I'd like to word wrap some cells, but not others, in an Excel spreadsheet, using pyExcelerator and Excel 2003, SP1, under XP. The code below creates the spreadsheet, but both cells are word-wrapped. As far as I can tell, the second call to XFStyle() overwrites a GLOBAL wrap setting, and affects even cells written before the call to XFStyle. Can anyone shed any light? Thanks, Gerry from pyExcelerator import * w = Workbook() ws = w.add_sheet(alpha) style = XFStyle() style.alignment.wrap= Alignment.NOT_WRAP_AT_RIGHT ws.write(1,1,Not wrapped + - * 50, style) style2 = XFStyle() style2.alignment.wrap= Alignment.WRAP_AT_RIGHT ws.write(2,1,Wrapped + - * 50, style2) w.save(test.xls) -- http://mail.python.org/mailman/listinfo/python-list
Re: pyExcelerator question
Gerry wrote: I'd like to word wrap some cells, but not others, in an Excel spreadsheet, using pyExcelerator and Excel 2003, SP1, under XP. The code below creates the spreadsheet, but both cells are word-wrapped. As far as I can tell, the second call to XFStyle() overwrites a GLOBAL wrap setting, and affects even cells written before the call to XFStyle. You are mostly correct. In Style.py, each style gets initialised to refer to a module-global bunch of default objects. No copying is done. Have a look at the patched code down the bottom of this posting -- it appears to work. Can anyone shed any light? Thanks, Gerry from pyExcelerator import * w = Workbook() ws = w.add_sheet(alpha) style = XFStyle() style.alignment.wrap= Alignment.NOT_WRAP_AT_RIGHT ws.write(1,1,Not wrapped + - * 50, style) style2 = XFStyle() style2.alignment.wrap= Alignment.WRAP_AT_RIGHT ws.write(2,1,Wrapped + - * 50, style2) w.save(test.xls) if 0: # original _default_num_format = 'general' _default_font = Formatting.Font() _default_alignment = Formatting.Alignment() _default_borders = Formatting.Borders() _default_pattern = Formatting.Pattern() _default_protection = Formatting.Protection() class XFStyle(object): def __init__(self): self.num_format_str = _default_num_format self.font= _default_font self.alignment = _default_alignment self.borders = _default_borders self.pattern = _default_pattern self.protection = _default_protection else: # patch class XFStyle(object): def __init__(self): self.num_format_str = 'general' self.font= Formatting.Font() self.alignment = Formatting.Alignment() self.borders = Formatting.Borders() self.pattern = Formatting.Pattern() self.protection = Formatting.Protection() If this works for you, you might like to submit a patch to http://sourceforge.net/tracker/?func=browsegroup_id=134081atid=730645 HTH, John -- http://mail.python.org/mailman/listinfo/python-list
Re: pyExcelerator question - dates map to floats?
John Check out my xlrd package. John http://cheeseshop.python.org/pypi/xlrd/0.5.2 ... John, Thank you. I wasn't aware of it. I'd seen mention of pyExcelerator a few times recently. All I need is to read Excel spreadsheets anyway. I will check it out. I'm up for reading a good rant. ;-) Skip -- http://mail.python.org/mailman/listinfo/python-list
Re: pyExcelerator question - dates map to floats?
John Check out my xlrd package. John http://cheeseshop.python.org/pypi/xlrd/0.5.2 Very nice. Thanks for the pointer. I threw away about 75% of the xls-to-csv converter I wrote using pyExcelerator. And it worked with Python 2.3 without having to comment out all the decorators. Skip -- http://mail.python.org/mailman/listinfo/python-list
pyExcelerator question - dates map to floats?
I'm experimenting with pyExcelerator and am reading an XLS file which contains dates. In Excel on my Mac they look like 09/13/06. After parsing them out of the .XLS file they are floats, e.g. 38973.0. I assume that's an offset in days. Doing a little date math I come up with a base date of approximately (though not quite) 1900-01-01: import datetime dt = datetime.timedelta(days=-38973) datetime.date(2006, 9, 13) + dt datetime.date(1899, 12, 30) datetime.date(1900, 1, 1) - dt datetime.date(2006, 9, 15) Is there some way to get pyExcelerator from doing this conversion and instead return dates as strings? If I'm reading an arbitrary worksheet and don't know which columnn might be a date, it's kind of hard to guess. Skip -- http://mail.python.org/mailman/listinfo/python-list
Re: pyExcelerator question - dates map to floats?
skip Doing a little date math I come up with a base date of skip approximately (though not quite) 1900-01-01: ... Reading the code in BIFFRecords.py I saw this docstring: This record specifies the base date for displaying date values. All dates are stored as count of days past this base date. In BIFF2-BIFF4 this record is part of the Calculation Settings Block. In BIFF5-BIFF8 it is stored in the Workbook Globals Substream. Record DATEMODE, BIFF2-BIFF8: Offset SizeContents 0 2 0 = Base is 1899-Dec-31 (the cell = 1 represents 1900-Jan-01) 1 = Base is 1904-Jan-01 (the cell = 1 represents 1904-Jan-02) Shifting my base date from 1900-01-01 to 1899-12-31 leaves me off by one. I take it then that count of days past this base date must include that date. It's too late for me to do any more poking around. Does anyone know if I might get date floats which are offset from 1904-01-01 (I think that was the traditional Mac start-of-epoch) or are they normalized to all be offsets from 1899-12-31? I noticed that there are two items on the pyExcelerator to-do list. Number one is documentation. Skip -- http://mail.python.org/mailman/listinfo/python-list
Re: pyExcelerator question - dates map to floats?
[EMAIL PROTECTED] wrote: skip Doing a little date math I come up with a base date of skip approximately (though not quite) 1900-01-01: ... Reading the code in BIFFRecords.py I saw this docstring: This record specifies the base date for displaying date values. All dates are stored as count of days past this base date. In BIFF2-BIFF4 this record is part of the Calculation Settings Block. In BIFF5-BIFF8 it is stored in the Workbook Globals Substream. Record DATEMODE, BIFF2-BIFF8: Offset SizeContents 0 2 0 = Base is 1899-Dec-31 (the cell = 1 represents 1900-Jan-01) 1 = Base is 1904-Jan-01 (the cell = 1 represents 1904-Jan-02) Shifting my base date from 1900-01-01 to 1899-12-31 leaves me off by one. You have found Microsoft's What do you mean, 1900 was not a leap year? bug. I take it then that count of days past this base date must include that date. It's too late for me to do any more poking around. Does anyone know if I might get date floats which are offset from 1904-01-01 (I think that was the traditional Mac start-of-epoch) or are they normalized to all be offsets from 1899-12-31? I noticed that there are two items on the pyExcelerator to-do list. Number one is documentation. Check out my xlrd package. http://cheeseshop.python.org/pypi/xlrd/0.5.2 (1) It has docs, including an extensive rant about Excel dates which will answer all of your above questions. (2) It has an helper functions for converting between Excel dates and tuples (which can then be converted into datetime, time, and mxDateTime dates). Python 2.4.3 (#69, Mar 29 2006, 17:35:34) [MSC v.1310 32 bit (Intel)] on win32 Type help, copyright, credits or license for more information. import xlrd xlrd.xldate_as_tuple(38973.0, datemode=0) (2006, 9, 13, 0, 0, 0) (3) It tracks the number formats applied to floats, and automatically classifies them as number and date. HTH, John -- http://mail.python.org/mailman/listinfo/python-list
