1  import datetime
   2  
   3  import adodbapi
   4  
   5  try:
   6      import adodbapi.is64bit as is64bit
   7  
   8      is64 = is64bit.Python()
   9  except ImportError:
  10      is64 = False  # in case the user has an old version of adodbapi
  11  if is64:
  12      driver = "Microsoft.ACE.OLEDB.12.0"
  13  else:
  14      driver = "Microsoft.Jet.OLEDB.4.0"
  15  filename = "xx.xls"  # file will be created if it does not exist
  16  extended = 'Extended Properties="Excel 8.0;Readonly=False;"'
  17  
  18  constr = "Provider=%s;Data Source=%s;%s" % (driver, filename, extended)
  19  
  20  conn = adodbapi.connect(constr)
  21  with conn:  # will auto commit if no errors
  22      with conn.cursor() as crsr:
  23          try:
  24              crsr.execute("drop table SheetOne")
  25          except:
  26              pass  # just is case there is one already there
  27  
  28          # create the sheet and the header row and set the types for the columns
  29          crsr.execute(
  30              "create table SheetOne (Name varchar, Rank varchar, SrvcNum integer, Weight float,  Birth date)"
  31          )
  32  
  33          sql = "INSERT INTO SheetOne (name, rank , srvcnum, weight, birth) values (?,?,?,?,?)"
  34  
  35          data = ("Mike Murphy", "SSG", 123456789, 167.8, datetime.date(1922, 12, 27))
  36          crsr.execute(sql, data)  # write the first row of data
  37          crsr.execute(
  38              sql, ["John Jones", "Pvt", 987654321, 140.0, datetime.date(1921, 7, 4)]
  39          )  # another row of data
  40  conn.close()
  41  print("Created spreadsheet=%s worksheet=%s" % (filename, "SheetOne"))
  42