1

Been trying to get the set_column to work still. Having problems getting Pandas to work, so have been doing it just in xlsxwriter. Right now am using: 'worksheet.set_column('D:D',None,format4)' - this only seems to work when I go into the xlsx file and actually activate each cell in the "D" column. Is there some way of activating each cell so that I wouldn't have to do it manually?

Thanks in advance.

import xlsxwriter,os,sys,datetime
now=datetime.datetime.now()
def main():
    platform=sys.platform
    if platform.find('win')>=0:
        TheSlash='\\'
    else:
        TheSlash='/'
    output = '%s-%s.xlsx' % ('XlsxSample',now.strftime("%m%d%Y-%H%M"))
    workbook = xlsxwriter.Workbook(output, {'strings_to_numbers':True,'default_date_format':'mm/dd/yy hh:mm'})
    worksheet = workbook.add_worksheet()
    count=0
    counter=0
    format=workbook.add_format({'font_size':'8','border':True})
    formatdict={'num_format':'mm/dd/yy hh:mm'}
    format4=workbook.add_format(formatdict)
    cur =('Pole1','33.62283963','-90.54639967','4/20/16 11:43','-90.54640226','33.62116957','5207069','25-04','50','3','PRIMARY','PGC')
    for name in cur:
        worksheet.write(counter, count, name,format)
        count+=1
    counter+=1
    worksheet.set_column('D:D',None,format4)
    workbook.close()
if __name__ == "__main__":
    main()

as stated above - date format only seems to activate if you get into the "D" cell itself with the cursor.

Slihthinden
  • 103
  • 1
  • 2
  • 7
  • There is no known issue with `set_column()`. Can you post a small complete working example that demonstrates the issue. – jmcnamara May 09 '16 at 20:53
  • Working Sample to follow – Slihthinden May 10 '16 at 14:18
  • Side note: You do a little test to assign `TheSlash`, which isn't used at all in your sample code. But I am guessing that in your full-blown program (which you have not included here), you use `TheSlash` in path names. Generally, you do not have to know the separator; in most contexts, you can just use a regular slash, even on Windows. If you REALLY, REALLY must know the primary path separator for your platform, then just use `os.sep` instead of your rigamarole. – John Y May 10 '16 at 21:21

3 Answers3

3

The reason that the column date format isn't showing up in the column cells is that the program is overwriting it with a cell format here:

for name in cur:
    worksheet.write(counter, count, name,format)
    count+=1

In XlsxWriter, as in Excel, a cell format overrides a column format.

If you want to have a cell or column format that is the result of 2 combined formats you will need to create a new format that combines those formats and apply it to the cells or the column.

Update: Also, I just noticed that you are writing a string in column D. Dates in Excel are formatted numbers. This is probably why you see the cell data change when you hit return. Excel is converting the date-like string into a formatted number displayed as a date. In XlsxWriter you will need to do the conversion. See the Working with Dates and Time section of the XlsxWriter docs.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • So, would it work then if I did an if/else statement such as `if count==3: `` worksheet.write(counter,count,name,format4) ``else: `` worksheet.write(counter,count,name,format) ` – Slihthinden May 10 '16 at 17:34
  • In theory yes but you will also need to convert the strings in column D to Excel dates. See my update. – jmcnamara May 10 '16 at 19:27
  • Got it working, although it took me a while to figure out I needed to do a `datetime.datetime.strptime()` call. Helps me a lot as I have a ton of reports with dates to pull out of a db. Thanks for the help - figured I would add the note about the datetime setup also. – Slihthinden May 10 '16 at 22:04
3

You need change format using datetime.datetime.strptime()

Example

import datetime
datetime_result = datetime.dateime.strptime('04/20/16 11:43', '%m/%d/%Y %H:%M')
format5 = workbook.add_format({'num_format':'mm/dd/yy hh:mm'})
worksheet.write('A5', datetime_result, format5)

Refer to Working with Dates and Time in XlsxWriter docs.

Sungjin Kim
  • 51
  • 3
1

In VBA, Columns("D").Select does what you want. If you are running from an external script, you might be able to save a VBA macro and run it with a technique like this: How do I call an Excel macro from Python using xlwings?.

Community
  • 1
  • 1
Weasemunk
  • 455
  • 4
  • 16